def test_unknown_parameter_exception(): msg = "Please check if there were typos in " msg += "function parameters: %s. Otherwise " msg += "unrecognized parameters were given." unknown_parameter = dict(something="else") try: pe.get_sheet(**unknown_parameter) except pe.exceptions.UnknownParameters as e: eq_(str(e), msg % unknown_parameter) try: pe.save_as(**unknown_parameter) except pe.exceptions.UnknownParameters as e: eq_(str(e), msg % unknown_parameter) try: pe.save_book_as(**unknown_parameter) except pe.exceptions.UnknownParameters as e: eq_(str(e), msg % unknown_parameter) try: pe.isave_as(**unknown_parameter) except pe.exceptions.UnknownParameters as e: eq_(str(e), msg % unknown_parameter)
def generate(self): if self.__in_file is None: raise Exception( "Input file was not detected in command and neither environment variable " + EnvConstants.IN_FILE + "\n" + "Please set option '--infile' in command line interface or set the " + EnvConstants.IN_FILE + " environment variable \n") if self.__out_file is None: raise Exception( "Output file was not detected in command and neither environment variable " + EnvConstants.OUT_FILE + "\n" + "Please set option '--outfile' in command line interface or set the " + EnvConstants.OUT_FILE + " environment variable \n") if "xls" not in self.__out_file and "xlsx" not in self.__out_file: raise Exception(f"Unsupported Excel file extension: {self.__out_file}\n") try: messages = IOUtils().read_dict_from_file(self.__in_file) except Exception as e: click.echo("Exception: {}".format(e.__str__())) return if not isinstance(messages, list): click.echo(f"The input file {self.__in_file} is not list of dicts") return if isinstance(messages, str): click.echo(f"The input file {self.__in_file} is not list of dicts or dict") return if isinstance(messages, list): if not isinstance(messages[0], dict): click.echo(f"The input file {self.__in_file} is not list of dicts or dict") return p.isave_as(records=messages, dest_file_name=self.__out_file)
def isave_to_database(self, session=None, table=None, initializer=None, mapdict=None, auto_commit=True, **keywords): """ Save large data from a sheet to database :param session: a SQLAlchemy session :param table: a database table :param initializer: a custom table initialization function if you have one :param mapdict: the explicit table column names if your excel data do not have the exact column names :param keywords: additional keywords to :meth:`pyexcel.Sheet.save_to_database` """ params = self.get_params(**keywords) params['dest_session'] = session params['dest_table'] = table params['dest_initializer'] = initializer params['dest_mapdict'] = mapdict params['dest_auto_commit'] = auto_commit pe.isave_as(**params)
def test_issue_126_isave_as(): data = [[1]] test_file = "issue_126.xls" test_name = "doyoufindme" p.isave_as(array=data, dest_file_name=test_file, dest_sheet_name=test_name) sheet = p.get_sheet(file_name=test_file) eq_(sheet.name, test_name) os.unlink(test_file)
def test_out_file_error(self): data = [[1, 2, 3], [4, 5, 6]] sheet = pe.Sheet(data) testfile = "testfile.xls" testfile2 = "testfile.xls" sheet.save_as(testfile) pe.isave_as(file_name=testfile, out_file=testfile2, colnames=["X", "Y", "Z"])
def test_save_as_invalid_params(self): data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]] sheet = pe.Sheet(data) testfile = "testfile.xls" testfile2 = "testfile2.csv" sheet.save_as(testfile) pe.isave_as(file_name=testfile, dest_file_name=testfile2, name_columns_by_row=0)
def test_save_file_as_another_one(self): data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]] sheet = pe.Sheet(data) testfile = "testfile.xls" testfile2 = "testfile2.csv" sheet.save_as(testfile) pe.isave_as(file_name=testfile, dest_file_name=testfile2) sheet = pe.get_sheet(file_name=testfile2) eq_(sheet.to_array(), data) os.unlink(testfile) os.unlink(testfile2)
def isave_to_database(self, model=None, initializer=None, mapdict=None, **keywords): """ Save data from a sheet to a nominated django model """ params = self.get_params(**keywords) params['dest_model'] = model params['dest_initializer'] = initializer params['dest_mapdict'] = mapdict pe.isave_as(**params) self.free_resources()
def test_save_file_as_texttable(self): """ test if _texttable can render generator or not """ data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]] sheet = pe.Sheet(data) testfile = "testfile.xls" testfile2 = "testfile2.texttable" sheet.save_as(testfile) pe.isave_as(file_name=testfile, dest_file_name=testfile2) os.unlink(testfile) os.unlink(testfile2)
def test_sheet_stream(): test_data = [[1, 2]] stream = p.isave_as(array=test_data, dest_file_type='rst') expected = dedent(""" pyexcel_sheet1: = = 1 2 = =""").strip('\n') eq_(stream.getvalue(), expected)
def main(argv): if len(argv) != 3 or (argv[1] != '--public' and argv[1] != '--internal'): exit('Usage: {} --public|--internal <ods file>'.format(argv[0])) if argv[1] == '--public': public = True else: public = False odsFile = argv[2] records = pe.iget_records(file_name=odsFile) if public: io = pe.isave_as(records=extract_public_data(records), dest_file_type='csv', dest_lineterminator='\n') else: io = pe.isave_as(records=extract_internal_data(records), dest_file_type='csv', dest_lineterminator='\n') print(io.getvalue())
def save_sheet(rows, filename): m = re.search(r'.*(\.\w+)$', filename) if not m: suffix = '.csv' else: suffix = m[1] if suffix in {'.csv', '.tsv'}: import csv dialect = {'.csv': csv.excel, '.tsv': csv.excel_tab}[suffix] with open(filename, 'w', encoding='utf8', newline='') as f: writer = csv.writer(f, dialect=dialect) writer.writerows(rows) else: import pyexcel pyexcel.isave_as(array=rows, dest_file_name=args.out or (','.join(args.uid) + '.ods'))
def _apply_to(self, from_file, to_file, sheet_name=None, row_filter=None, monitor_rules=False, **kwargs): """ Threadable rule processing method. .. important:: No overwrite protection is enabled for this method. If the ``from_file`` is equal to the ``to_file``, then ``from_file`` will be overwritten. :param str from_file: The input filepath :param str to_file: The output filepath :param str sheet_name: The name of the sheet to apply rules to :param callable row_filter: A callable which accepts a cleaned record and returns True if the record should be written out :param bool monitor_rules: Boolean flag that inidicates if the count of applied rules should be monitored :param dict kwargs: Any named arguments, passed to ``_apply_rules`` :returns: The rule statistics if ``monitor_rules`` is true :rtype: dict[str, int] """ try: pyexcel.isave_as( records=self._apply_rules(from_file, sheet_name=sheet_name, row_filter=row_filter, monitor_rules=monitor_rules, **kwargs), dest_file_name=to_file, dest_lineterminator=os.linesep, ) if monitor_rules: return self.__rule_stats finally: self.__rule_stats = {}
else: continue if exclude_filter and exclude_filter.intersection(muids): continue if uid not in file_uid_mapping: file_uid_mapping[uid] = {} file_uid_mapping[uid][muids_string] = file if not file_uid_mapping: print('No matches for {}'.format(",".join(args.uid)), file=sys.stderr) exit(1) muid_strings = set() for keys in file_uid_mapping.values(): muid_strings.update(keys) muid_strings = sorted(muid_strings, key=muid_sort_key) rows = yield_rows(muid_strings, file_uid_mapping) pyexcel.isave_as(array=rows, dest_file_name=args.out or (','.join(args.uid) + '.ods'))
import pyexcel as p def increase_everyones_age(generator): for row in generator: row['Age'] += 1 yield row records = p.iget_records(file_name="your_file.xlsx") io = p.isave_as(records=increase_everyones_age(records), dest_file_name="your_file2.xlsx") # p.save_as(records=a_list_of_dictionaries, dest_file_name="your_file.xlsx") # print(io.getvalue())
import pyexcel from desk_generator import generate_desk_locations, generate_desk_data from meet_generator import generate_meet_data, generate_meet_locations import constraints as con if __name__ == '__main__': desk_location = generate_desk_locations() pyexcel.isave_as(records=desk_location, dest_file_name=f'{con.survey_name}_desk_location.csv') desk_data = generate_desk_data(desk_location) pyexcel.isave_as(records=desk_data, dest_file_name=f'{con.survey_name}_desk_data.csv') meet_location = generate_meet_locations() pyexcel.isave_as(records=meet_location, dest_file_name=f'{con.survey_name}_meet_location.csv') meet_data = generate_meet_data(meet_location) pyexcel.isave_as(records=meet_data, dest_file_name=f'{con.survey_name}_meet_data.csv')
import pyexcel as p def increase_everyones_age(generator): for row in generator: row['Age'] += 1 yield row def duplicate_each_record(generator): for row in generator: yield row yield row records = p.iget_records(file_name="your_file.xlsx") io=p.isave_as(records=duplicate_each_record(increase_everyones_age(records)), dest_file_type='csv', dest_lineterminator='\n') print(io.getvalue())