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())
