def test_no_title_multiple_sheets(self):
adict = {'sheet 1': [[1, 2], [3, 4]], 'sheet 2': [[5, 6], [7, 8]]}
pe.save_book_as(bookdict=adict,
dest_file_name=self.testfile,
dest_write_title=False)
self._check_test_file('no_title_multiple_sheets')
def test_model_save_to_models(self):
model = FakeDjangoModel("Sheet1")
data = {
"Sheet1": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]
}
pe.save_book_as(dest_models=[model, None, None], bookdict=data)
assert model.objects.objs == self.result1
def output(self, export_path):
"""
Method to export the Clinv inventory to ods.
It generates the information needed to fill up a spreadsheet for a
selected resource.
Parameters:
export_path (str): Path to export the inventory.
(Default: ~/.local/share/clinv/inventory.ods)
Returns:
list: First row are the headers of the spreadsheet, followed
by lines of data.
"""
book = OrderedDict()
book.update({"Projects": self._export_projects()})
book.update({"Services": self._export_services()})
book.update({"Informations": self._export_informations()})
book.update({"EC2": self._export_ec2()})
book.update({"RDS": self._export_rds()})
book.update({"Route53": self._export_route53()})
book.update({"S3": self._export_s3()})
book.update({"People": self._export_people()})
pyexcel.save_book_as(
bookdict=book, dest_file_name=os.path.expanduser(export_path),
)
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 diff(source_file_type, file_type, source, dest):
"""
Find difference in plain data format
Both files are transcoded into a file stream of a common file type.
Then a diff operation is run on them.
\b
SOURCE: a file name or '-'. '-' tells the command to use stdin
DEST: a file name
"""
params = {}
if source == '-':
params['file_content'] = get_input_content(source_file_type)
params['file_type'] = source_file_type
else:
params['file_name'] = source
params['dest_file_type'] = file_type
source_lines = pe.save_book_as(**params)
dest_lines = pe.save_book_as(file_name=dest,
dest_file_type=file_type)
result = unified_diff(source_lines.getvalue().splitlines(),
dest_lines.getvalue().splitlines(),
source, dest)
has_difference = 0
for difference in result:
has_difference = 1
click.echo(difference)
sys.exit(has_difference)
def convert(inputExcel):
To_xlsx.clean()
fileName = str(inputExcel)
splitName = inputExcel.split('.')
splitName[1] = inputExcel[-3:]
if (splitName[1] == 'xls'):
print('Its a xls file. Converting to .xlsx file..')
p.save_book_as(file_name=fileName,
dest_file_name=splitName[0] + '.xlsx')
opFile = splitName[0] + '.xlsx'
destpath = 'uploads'
srcpath = opFile
shutil.move(srcpath, destpath)
print('File moved!')
return opFile
elif (splitName[1] == 'lsx'):
print('Its already a xlsx file')
opFile = inputExcel
destpath = 'uploads'
srcpath = opFile
shutil.move(srcpath, destpath)
return opFile
else:
print('Invalid file format! Upload .xls files')
sys.exit()
def create_xls_template():
d = {
'Sheet 1': [[
'DATE', 'Dimanche', 'Lundi', 'Mardi', 'Mercredi', 'Jeudi',
'Vendredi', 'Samedi'
]]
}
employe_sorted = users.sort_employe("poste", is_ascending=False)
caisse = []
cuisine = []
respo = []
for i, e in enumerate(employe_sorted):
if e['poste'] == 'caisse':
caisse.append(employe_sorted.pop(i))
elif e['poste'] == 'cuisine':
cuisine.append(employe_sorted.pop(i))
elif e['poste'] == 'respo':
respo.app
d['Sheet 1'].append(
["{} {}".format(e.get('nom'), e.get("nom de famille"))])
try:
pyexcel.save_book_as(
bookdict=d,
dest_file_name=schedule_maker.get_full_path_name("template.xls"))
except Exception as x:
print(UI_String.UNHANDLED_EXCEPTION.format(type(x), x))
def test_save_book_as_file_from_sql_compactibility(self):
test_file = "book_from_sql.xls"
pe.save_book_as(
out_file=test_file,
session=Session(),
tables=[Signature, Signature2],
)
def __init__(self, filename, naming_convention = "father_surname", language = "en"):
'''
This contructor reads the output file from FS in xlsx format
''' #TODO: include further naming conventions
self.correct_execution = True
self.language = language
if os.path.exists(filename):
if (not ".xlsx" in filename):
#This file is not in xlsx format, we change it:
pyexcel.save_book_as(file_name=filename, dest_file_name=filename + "x")
filename = filename + "x"
else:
#The file does not exists, we create an error!
logging.error(NOT_EXISTING_FILE + filename)
self.correct_execution = False
if (not naming_convention in naming_conventions):
logging.error(NO_VALID_NAMING_CONVENTION)
self.correct_execution = False
self.naming_convention = naming_convention
if self.correct_execution:
self.loaded_data = load_workbook(filename, data_only=True)
if (self.__locate_key_fields__()):
self.profiles = []
self.geni_profiles = []
self.related_profiles = {}
self.related_geni_profiles = []
self.parents_profiles = {}
self.parents_geni_profiles = []
self.__get_profiles__()
else:
#If no we do not locate the key files we continue
self.correct_execution = False
logging.error(NO_VALID_FILE)
def test_save_book_as_to_database(self):
data = [
["X", "Y", "Z"],
[1, 2, 3],
[4, 5, 6]
]
data1 = [
["A", "B", "C"],
[1, 2, 3],
[4, 5, 6]
]
sheet_dict = {
Signature.__tablename__: data,
Signature2.__tablename__: data1
}
pe.save_book_as(bookdict=sheet_dict,
dest_session=self.session,
dest_tables=[Signature, Signature2])
result = pe.get_dict(session=self.session, table=Signature)
assert result == {
"X": [1, 4],
"Y": [2, 5],
"Z": [3, 6]
}
result = pe.get_dict(session=self.session, table=Signature2)
assert result == {
"A": [1, 4],
"B": [2, 5],
"C": [3, 6]
}
def save_book_to_database(self,
session=None,
tables=None,
initializers=None,
mapdicts=None,
auto_commit=True,
**keywords):
"""
Save a book into database
:param session: a SQLAlchemy session
:param tables: a list of database tables
:param initializers: a list of model initialization functions.
:param mapdicts: a list of explicit table column names
if your excel data sheets do not have the exact column names
:param keywords: additional keywords to :meth:`pyexcel.Book.save_to_database`
"""
params = self.get_params(**keywords)
params['dest_session'] = session
params['dest_tables'] = tables
params['dest_initializers'] = initializers
params['dest_mapdicts'] = mapdicts
params['dest_auto_commit'] = auto_commit
pe.save_book_as(**params)
def Open(self):
try:
if not os.path.isfile(self.v_filename):
raise Spartacus.Utils.Exception('File {0} does not exist or is not a file.'.format(self.v_filename))
if self.v_extension == 'csv':
self.v_file = open(self.v_filename, encoding=self.v_encoding)
v_sample = self.v_file.read(1024)
self.v_file.seek(0)
v_sniffer = csv.Sniffer()
if not v_sniffer.has_header(v_sample):
raise Spartacus.Utils.Exception('CSV file {0} does not have a header.'.format(self.v_filename))
v_dialect = v_sniffer.sniff(v_sample)
if self.v_delimiter is not None:
v_dialect.delimiter = self.v_delimiter
self.v_object = csv.DictReader(self.v_file, self.v_header, None, None, v_dialect)
self.v_open = True
elif self.v_extension == 'xlsx':
self.v_object = openpyxl.load_workbook(self.v_filename, read_only=True)
self.v_open = True
elif self.v_extension == 'xls':
v_tmp_file = tempfile.NamedTemporaryFile(suffix='.xlsx')
v_tmp_file.file.close()
pyexcel.save_book_as(file_name=self.v_filename, dest_file_name=v_tmp_file.name)
self.v_object = openpyxl.load_workbook(v_tmp_file.name, read_only=True)
self.v_open = True
else:
raise Spartacus.Utils.Exception('File extension "{0}" not supported.'.format(self.v_extension))
except Spartacus.Utils.Exception as exc:
raise exc
except Exception as exc:
raise Spartacus.Utils.Exception(str(exc))
def setUp(self):
self.testfile = "multiple1.xls"
self.content = OrderedDict()
self.content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]})
self.content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]})
self.content.update({"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
pe.save_book_as(dest_file_name=self.testfile, bookdict=self.content)
def save():
filename_path = Path(os.environ['FILENAME'])
content = request.get_json()
if filename_path.suffix in ('.csv', '.tsv'):
pyexcel.save_as(array=list(content['data'].values())[0],
dest_file_name=str(filename_path))
else:
try:
pyexcel_export.save_data(str(filename_path), content['data'])
except NameError:
pyexcel.save_book_as(bookdict=content['data'],
dest_file_name=str(filename_path))
try:
config = json.loads(os.environ.get('CONFIG', ''))
assert isinstance(config, dict)
except (json.decoder.JSONDecodeError, AssertionError):
if 'CONFIG' in os.environ:
config_path = Path(os.environ['CONFIG'])
if config_path.exists():
config = yaml.safe_load(config_path.read_text())
else:
config = dict()
config['simplecel'] = content['config']
config_path.write_text(yaml.safe_dump(config, allow_unicode=True))
return Response(status=200)
def test_save_book_as_to_database(self):
data = [
["X", "Y", "Z"],
[1, 2, 3],
[4, 5, 6]
]
data1 = [
["A", "B", "C"],
[1, 2, 3],
[4, 5, 6]
]
sheet_dict = {
Signature.__tablename__: data,
Signature2.__tablename__: data1
}
pe.save_book_as(bookdict=sheet_dict,
dest_session=self.session,
dest_tables=[Signature, Signature2])
result = pe.get_dict(session=self.session, table=Signature)
assert result == {
"X": [1, 4],
"Y": [2, 5],
"Z": [3, 6]
}
result = pe.get_dict(session=self.session, table=Signature2)
assert result == {
"A": [1, 4],
"B": [2, 5],
"C": [3, 6]
}
def setUp(self):
self.testfile4 = "multiple_sheets.xls"
self.content4 = {
"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]],
"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]],
"Sheet3": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]],
}
pe.save_book_as(dest_file_name=self.testfile4, bookdict=self.content4)
def test_dict(self):
adict = {
'sheet 1': [[1,2],[3,4]],
'sheet 2': [[5,6],[7,8]]
}
pe.save_book_as(bookdict=adict, dest_file_name=self.testfile)
self._check_test_file('dict')
def convert_xls_to_xlsx(xls_file):
xlsx_file_name = tempfile.mkstemp(suffix='.xlsx')[1]
try:
pyexcel.save_book_as(file_name=xls_file, dest_file_name=xlsx_file_name)
except Exception as e:
logger.info(u'Error converting {} from .xls to .xlsx: {}, {}'.format(
xls_file, type(e), e.message))
return None
return xlsx_file_name
def handson():
# return request.args.get('tmp')
tmp = request.args.get('tmp')
tmpname = re.search("^.*tmp(.*)$", tmp)[1]
pe.save_book_as(
file_name='{}/input.xlsx'.format(tmp),
dest_file_name='{0}/templates/{1}.handsontable.html'.format(
base_dir, tmpname))
return render_template('{}.handsontable.html'.format(tmpname))
def _write_test_file(self, filename, content):
"""
Make a test file as:
1,1,1,1
2,2,2,2
3,3,3,3
"""
pe.save_book_as(dest_file_name=filename, bookdict=content)
def changeFileToXlsx(originalName, resultName):
stOriExcel = config.ST_LOGIN['excelPath'] + originalName
stResultExcel = config.ST_LOGIN['excelPath'] + resultName + now + '.xls'
stResultXlsx = config.ST_LOGIN['excelPath'] + resultName + now + '.xlsx'
os.rename(stOriExcel, stResultExcel)
p.save_book_as(file_name=stResultExcel, dest_file_name=stResultXlsx)
return stResultXlsx
def test_no_title_multiple_sheets(self):
adict = {
'sheet 1': [[1,2],[3,4]],
'sheet 2': [[5,6],[7,8]]
}
pe.save_book_as(bookdict=adict, dest_file_name=self.testfile,
dest_write_title=False)
self._check_test_file('no_title_multiple_sheets')
def export_sheets_test():
data = {
"Sheet 1": [[1, 2, 3], [4, 5, 6], [7, 8, 9]],
"Sheet 2": [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]],
"Sheet 3": [['O', 'P', 'Q'], [3, 2, 1], [4, 3, 2]]
}
pyexcel.save_book_as(bookdict=data,
dest_file_name=PROJECT_PATH +
"//file_location//multiple-sheets1.xls")
def test_save_book_as_file_from_sql(self):
test_file = "book_from_sql.xls"
pe.save_book_as(out_file=test_file, session=Session(), tables=[Signature, Signature2])
book_dict = pe.get_book_dict(file_name=test_file)
expected = OrderedDict()
expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]})
expected.update({"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
os.unlink(test_file)
def _write_test_file(self, filename, content):
"""
Make a test file as:
1,1,1,1
2,2,2,2
3,3,3,3
"""
pe.save_book_as(dest_file_name=filename, bookdict=content)
def setUp(self):
self.testfile4 = "multiple_sheets.xls"
self.content4 = {
"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]],
"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]],
"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]
}
pe.save_book_as(dest_file_name=self.testfile4,
bookdict=self.content4)
def test_dict(self):
adict = {
'sheet 1': [[1,2],[3,4]],
'sheet 2': [[5,6],[7,8]]
}
pe.save_book_as(bookdict=adict, dest_file_name=self.testfile)
with open(self.testfile, "r") as f:
written_content = json.load(f)
assert written_content == adict
def main():
"""
主函数
"""
print("**********欢迎使用招聘信息爬取软件**********")
global sheetDict
filename = "招聘信息搜集.xlsx"
threads = []
# 信息输入
city = input("请输入城市(如北京)：")
position = input("请输入职位（如测试）：")
# 智联招聘
getZL = input("是否获取智联招聘上的信息Y/N:")
if getZL.upper() == 'Y':
salaryDict = {
"1": "8001,10000",
"2": "10001,15000",
"3": "15001,25000"
} # 薪资范围
salaryRange = input(
"请进行月薪范围选择(1:8K-10K 2:10K-15K 3:15K-25K, 选择多项时以空格分隔)：")
salary = salaryRangeProcess(
salaryRange, salaryDict) # 月薪，如["10001,15000", "15001,2000"]
logging.info("选择的月薪为：{}".format(salary))
for i in salary:
sheetName = "智联招聘_{}_{}_{}".format(city, position, i)
t = threading.Thread(target=crawling,
args=[Zhaopin, sheetName, city, position, i])
threads.append(t)
# 猎聘
getLP = input("是否获取猎聘网上的信息Y/N：")
if getLP.upper() == "Y":
salaryLPDict = {"1": "10$15", "2": "15$20", "3": "20$30"} # 猎聘薪资范围
salaryLPRange = input(
"请进行年薪范围选择（1：10-15万 2：15-20万 3：20-30万，选择多项时以空格分隔）：")
salaryLP = salaryRangeProcess(
salaryLPRange, salaryLPDict) # 选择的年薪，格式如["10$15", "15$20"]
logging.info("选择的年薪为：{}".format(salaryLP))
for i in salaryLP:
sheetName = "猎聘网_{}_{}_{}".format(city, position, i)
t = threading.Thread(target=crawling,
args=[Liepin, sheetName, city, position, i])
threads.append(t)
# 启动多线程
for i in threads:
i.start()
for i in threads:
i.join()
logging.info("所有线程均已返回，开始写入excel......")
pyexcel.save_book_as(bookdict=sheetDict, dest_file_name=filename)
def _write_test_file(self, file):
"""
Make a test file as:
1,1,1,1
2,2,2,2
3,3,3,3
"""
self.rows = 3
pyexcel.save_book_as(bookdict=self.content,dest_file_name=file)
def convert_file_to_xlsx(self):
if self.file.name.endswith('xls'):
# xls to xlsx
p.save_book_as(file_name=self.file.name,
dest_file_name=self.file.name + 'x')
self.file.name += 'x'
self.save()
elif not str(self.file).endswith('xlsx'):
raise ValueError('Not Excel file')
def xls_to_xlsx_converter(filename):
# fname = "C:\Users\Kiot\Documents\python_gui\input.xls"
# excel = win32.gencache.EnsureDispatch('Excel.Application')
# wb = excel.Workbooks.Open(fname)
# wb.SaveAs(fname+"x", FileFormat = 51) #FileFormat = 51 is for .xlsx extension
# wb.Close() #FileFormat = 56 is for .xls extension
# excel.Application.Quit()
p.save_book_as(file_name=filename, dest_file_name=filename + 'x')
def change_file_to_xlsx(original_name, result_name):
st_ori_excel = config.ST_LOGIN['excelPath'] + original_name
st_result_excel = config.ST_LOGIN['excelPath'] + result_name + now + '.xls'
st_result_xlsx = config.ST_LOGIN['excelPath'] + result_name + now + '.xlsx'
os.rename(st_ori_excel, st_result_excel)
p.save_book_as(file_name=st_result_excel, dest_file_name=st_result_xlsx)
os.remove(st_result_excel)
return st_result_xlsx
def _write_test_file(self, file):
"""
Make a test file as:
1,1,1,1
2,2,2,2
3,3,3,3
"""
self.rows = 3
pyexcel.save_book_as(bookdict=self.content, dest_file_name=file)
def setUp(self):
self.testfile = "multiple1.xls"
self.content = OrderedDict()
self.content.update(
{"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]})
self.content.update(
{"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]})
self.content.update(
{"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
pe.save_book_as(dest_file_name=self.testfile, bookdict=self.content)
def test_save_book_as_file_from_sql(self):
test_file="book_from_sql.xls"
pe.save_book_as(out_file=test_file,
session=Session(),
tables=[Signature, Signature2])
book_dict = pe.get_book_dict(file_name=test_file)
expected = OrderedDict()
expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]})
expected.update({'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
os.unlink(test_file)
def save_book_to_database(self, models=None, initializers=None,
mapdicts=None, batch_size=None,
**keywords):
"""
Save data from a book to a nominated django models
"""
params = self.get_params(**keywords)
params['dest_models'] = models
params['dest_initializers']=initializers
params['dest_mapdicts'] = mapdicts
params['dest_batch_size'] = batch_size
pe.save_book_as(**params)
def save_book_to_database(self, models=None, initializers=None,
mapdicts=None, batch_size=None,
**keywords):
"""
Save data from a book to a nominated django models
"""
params = self.get_params(**keywords)
params['dest_models'] = models
params['dest_initializers'] = initializers
params['dest_mapdicts'] = mapdicts
params['dest_batch_size'] = batch_size
pe.save_book_as(**params)
def test_dict(self):
adict = {
'sheet 1': [[1,2],[3,4]],
'sheet 2': [[5,6],[7,8]]
}
pe.save_book_as(bookdict=adict, dest_file_name=self.testfile)
f = open(self.testfile, "r")
written_content = f.read()
f.close()
content = dedent("""
{"sheet 1": [[1, 2], [3, 4]], "sheet 2": [[5, 6], [7, 8]]}""").strip('\n')
assert written_content == content
def test_histogram():
data = {
'Wide bars': [(5, 0, 10), (4, 5, 13), (2, 0, 15)],
'Narrow bars': [(10, 1, 2), (12, 4, 4.5), (8, 11, 13)]
}
pe.save_book_as(
bookdict=data,
dest_chart_type='histogram',
dest_file_name='histogram.svg',
dest_no_prefix=True
)
_validate_and_remove('histogram.svg')
def test_save_book_as_file_from_sql(self):
test_file = "book_from_sql.xls"
pe.save_book_as(dest_file_name=test_file,
session=Session(),
tables=[Signature, Signature2])
book_dict = pe.get_book_dict(file_name=test_file)
expected = OrderedDict()
expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]})
expected.update(
{'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
os.unlink(test_file)
def test_write_texttable_book():
content = {"Sheet": [[1,2]]}
test_file = "test.texttable"
expected = dedent("""
Sheet:
+---+---+
| 1 | 2 |
+---+---+""").strip('\n')
pe.save_book_as(bookdict=content, dest_file_name=test_file)
with open(test_file, 'r') as f:
written = f.read()
eq_(written, expected)
os.unlink(test_file)
def test_write_texttable_book():
content = {"Sheet": [[1, 2]]}
test_file = "test.texttable"
expected = dedent("""
Sheet:
+---+---+
| 1 | 2 |
+---+---+""").strip("\n")
pe.save_book_as(bookdict=content, dest_file_name=test_file)
with open(test_file, "r") as f:
written = f.read()
eq_(written, expected)
os.unlink(test_file)
def Open(self):
try:
if not os.path.isfile(self.v_filename):
raise Spartacus.Utils.Exception(
"File {0} does not exist or is not a file.".format(
self.v_filename))
if self.v_extension == "csv":
self.v_file = open(self.v_filename, encoding=self.v_encoding)
v_sample = self.v_file.read(1024)
self.v_file.seek(0)
v_sniffer = csv.Sniffer()
if not v_sniffer.has_header(v_sample):
raise Spartacus.Utils.Exception(
"CSV file {0} does not have a header.".format(
self.v_filename))
v_dialect = v_sniffer.sniff(v_sample)
if self.v_delimiter is not None:
v_dialect.delimiter = self.v_delimiter
self.v_object = csv.DictReader(self.v_file, self.v_header,
None, None, v_dialect)
self.v_open = True
elif self.v_extension == "xlsx":
if "xlsx" in v_supported_file_formats:
self.v_object = openpyxl.load_workbook(self.v_filename,
read_only=True)
self.v_open = True
else:
raise Spartacus.Utils.Exception(
"XLSX is not supported. Please install it with 'pip install Spartacus[xlsx]'."
)
elif self.v_extension == "xls":
if "xls" in v_supported_file_formats:
v_tmp_file = tempfile.NamedTemporaryFile(suffix=".xlsx")
v_tmp_file.file.close()
pyexcel.save_book_as(file_name=self.v_filename,
dest_file_name=v_tmp_file.name)
self.v_object = openpyxl.load_workbook(v_tmp_file.name,
read_only=True)
self.v_open = True
else:
raise Spartacus.Utils.Exception(
"XLS is not supported. Please install it with 'pip install Spartacus[xls]'."
)
else:
raise Spartacus.Utils.Exception(
'File extension "{0}" not supported.'.format(
self.v_extension))
except Spartacus.Utils.Exception as exc:
raise exc
except Exception as exc:
raise Spartacus.Utils.Exception(str(exc))
def new_day_ow(row):
global output_file
add = False
#sheet = get_data(output_file)#another way to load a sheet this time in an ordered dictionary
#checking if rows have already been added
date = datetime.datetime.strftime(datetime.datetime.now(), "%d/%m/%Y")
sheet = get_data(output_file) #this is rather a book than a sheet
list_date = get_string_coord_column(
sheet["Sheet1"], 0,
date) #note that the searching procedure takes a sheet not a book
print "today's date offsets:", list_date
if list_date == []:
add = True
print "no today's date occurence found, adding row"
else:
print list_date
print "inserting cells"
print "this is the long procedure, adding", row
i = 0
occurences = list_date
for cell in row:
i += 1
#print "inserting at x",occurences[0][0]+i
#print "type",type(cell)
y = int(occurences[len(occurences) - 1][1])
x = int(occurences[0][0]) + i
sheet["Sheet1"][y - 1][x - 1] = cell
# the following could be used to convert str to unicode
#if (type(cell)==unicode or type(cell)==str):
# print "inserting unicode"
# sheet[y-1][x-1]=unicode(cell)
#Insert_cell(occurences[0][0]+i,occurences[len(occurences)-1][1],unicode(cell))#inserting at the last occurence of the date
#if (type(cell)==int):
# print "inserting int"
# sheet[y-1][x-1]=int(cell)
#Insert_cell(occurences[0][0]+i,occurences[len(occurences)-1][1],int(cell))
pyexcel.save_book_as(bookdict=sheet,
dest_file_name=output_file) #saves a sheet
print "Book recorded"
if add:
book = pyexcel.get_book(
file_name=output_file
) #loads a sheet in a sheet object that can be modified
book.Sheet1.row += row
print "saving xls"
book.save_as(output_file)
def test_save_book_to_memory_from_sql(self):
test_file = pe.save_book_as(dest_file_type="xls", session=Session(), tables=[Signature, Signature2])
book_dict = pe.get_book_dict(file_content=test_file.getvalue(), file_type="xls")
expected = OrderedDict()
expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]})
expected.update({"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
def setUp(self):
"""
Make a test csv file as:
1,2,3,4
5,6,7,8
9,10,11,12
"""
self.testfile = "testcsv.xls"
self.content = {
"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]],
"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]],
"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]
}
pe.save_book_as(bookdict=self.content,
dest_file_name=self.testfile)
def test_book_file(self):
data = {
"Category":[
["id", "name"],
[1, "News"],
[2, "Sports"]
],
"Post":[
["id", "title", "body", "pub_date", "category"],
[1, "Title A", "formal", datetime(2015,1,20,23,28,29), "News"],
[2, "Title B", "informal", datetime(2015,1,20,23,28,30), "Sports"]
]
}
for upload_file_type in ['xls']:
with app.app_context():
db.drop_all()
db.create_all()
print("Uploading %s" % upload_file_type)
file_name = "test.%s" % upload_file_type
io = pe.save_book_as(bookdict=data, dest_file_type=upload_file_type)
response = self.app.post('/upload/all',
buffered=True,
data={"file": (io, file_name)},
content_type="multipart/form-data")
ret = pe.get_book_dict(file_type="xls", file_content=response.data)
assert data['Category'] == ret['category']
sheet = pe.Sheet(data['Post'], name_columns_by_row=0)
sheet.column.format("pub_date", lambda d: d.isoformat())
sheet2 = pe.Sheet(ret['post'], name_columns_by_row=0)
for key in sheet.colnames:
if key == "category":
continue
assert sheet.column[key] == sheet2.column[key]
assert sheet2.column['category_id'] == [1, 2]
def test_book_file(self):
data = {
"Category": [["id", "name"], [1, "News"], [2, "Sports"]],
"Post": [
["id", "title", "body", "pub_date", "category"],
[1, "Title A", "formal", datetime(2015, 1, 20, 23, 28, 29), "News"],
[2, "Title B", "informal", datetime(2015, 1, 20, 23, 28, 30), "Sports"],
],
}
for upload_file_type in ["xls"]:
print("Uploading %s" % upload_file_type)
file_name = "test.%s" % upload_file_type
io = pe.save_book_as(bookdict=data, dest_file_type=upload_file_type)
if not PY2:
if isinstance(io, BytesIO):
content = io.getvalue()
else:
content = io.getvalue().encode("utf-8")
else:
content = io.getvalue()
response = self.app.post("/upload/all", upload_files=[("file", file_name, content)])
ret = pe.get_book_dict(file_type="xls", file_content=response.body)
assert data["Category"] == ret["category"]
sheet = pe.Sheet(data["Post"], name_columns_by_row=0)
sheet.column.format("pub_date", lambda d: d.isoformat())
sheet2 = pe.Sheet(ret["post"], name_columns_by_row=0)
for key in sheet.colnames:
if key == "category":
continue
assert sheet.column[key] == sheet2.column[key]
assert sheet2.column["category_id"] == [1, 2]
def test_get_book_from_memory_compatibility(self):
content = OrderedDict()
content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]})
content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]})
content.update({"Sheet3": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
io = pe.save_book_as(dest_file_type="xls", bookdict=content)
book2 = pe.get_book(content=io.getvalue(), file_type="xls")
assert book2.to_dict() == content
def test_get_book_from_file_stream(self):
content = OrderedDict()
content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]})
content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]})
content.update({"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
io = pe.save_book_as(dest_file_type="xls", bookdict=content)
book2 = pe.get_book(file_stream=io, file_type="xls")
assert book2.to_dict() == content
def test_get_book_dict(self):
content = OrderedDict()
content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]})
content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]})
content.update({"Sheet3": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
io = pe.save_book_as(dest_file_type="xls", bookdict=content)
adict = pe.get_book_dict(file_content=io.getvalue(), file_type="xls")
assert adict == content
def test_book(self):
for struct_type in ["book", "book_dict"]:
io = pe.save_book_as(bookdict=self.content, dest_file_type="xls")
response = self.app.post('/exchange/%s' % struct_type,
buffered=True,
data={"file": (io, "test.xls")},
content_type="multipart/form-data")
assert response.content_type == "application/vnd.ms-excel"
book2 = pe.get_book(file_type='xls', file_content=response.data)
assert book2.to_dict() == self.content
def test_book_output_stringio(self):
data = {
"Sheet 1": [
[1, 2, 3],
[4, 5, 6]
]
}
io = pe.save_book_as(dest_file_type="xlsm",bookdict=data)
b = pe.load_book_from_memory("xlsm", io.getvalue())
result=[1, 2, 3, 4, 5, 6]
actual = pe.utils.to_array(b[0].enumerate())
assert result == actual
def save_book_to_database(self, session=None, tables=None,
initializers=None, mapdicts=None,
auto_commit=True, **keywords):
"""
Save a book into database
:param session: a SQLAlchemy session
:param tables: a list of database tables
:param initializers: a list of model initialization functions.
:param mapdicts: a list of explicit table column names
if your excel data sheets do not have the exact column names
:param keywords: additional keywords to :meth:`pyexcel.Book.save_to_database`
"""
params = self.get_params(**keywords)
params['dest_session'] = session
params['dest_tables'] = tables
params['dest_initializers'] = initializers
params['dest_mapdicts'] = mapdicts
params['dest_auto_commit'] = auto_commit
pe.save_book_as(**params)
def test_save_book_to_memory_from_sql(self):
test_file = pe.save_book_as(dest_file_type="xls",
session=Session(),
tables=[Signature, Signature2])
book_dict = pe.get_book_dict(
file_content=test_file.getvalue(),
file_type="xls"
)
expected = OrderedDict()
expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]})
expected.update({'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
def test_dict(self):
adict = {
'sheet 1': [[1,2],[3,4]],
'sheet 2': [[5,6],[7,8]]
}
pe.save_book_as(bookdict=adict, dest_file_name=self.testfile)
f = open(self.testfile, "r")
written_content = f.read()
f.close()
content = dedent("""
Sheet Name: sheet 1
= =
1 2
3 4
= =
Sheet Name: sheet 2
= =
5 6
7 8
= =""").strip('\n')
assert written_content.strip('\n') == content
def setUp(self):
"""
Make a test csv file as:
1,1,1,1
2,2,2,2
3,3,3,3
"""
self.testfile = "test1.xls"
self.content = {
"X": [1, 2, 3, 4, 5],
"Y": [6, 7, 8, 9, 10],
"Z": [11, 12, 13, 14, 15],
}
pe.save_as(dest_file_name=self.testfile,
adict=self.content)
self.testfile2 = "test.csv"
self.content2 = {
"O": [1, 2, 3, 4, 5],
"P": [6, 7, 8, 9, 10],
"Q": [11, 12, 13, 14, 15],
}
pe.save_as(dest_file_name=self.testfile2,
adict=self.content2)
self.testfile3 = "test.xls"
self.content3 = {
"R": [1, 2, 3, 4, 5],
"S": [6, 7, 8, 9, 10],
"T": [11, 12, 13, 14, 15],
}
pe.save_as(dest_file_name=self.testfile3,
adict=self.content3)
self.testfile4 = "multiple_sheets.xls"
self.content4 = {
"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]],
"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]],
"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]
}
pe.save_book_as(dest_file_name=self.testfile4,
bookdict=self.content4)
def make_response_from_tables(session, tables,
file_type, status=200, file_name=None,
**keywords):
"""
Make a http response from sqlalchmy tables
:param session: SQLAlchemy session
:param tables: SQLAlchemy tables
:param file_type: same as :meth:`~pyexcel_webio.make_response`
:param status: same as :meth:`~pyexcel_webio.make_response`
:returns: a http response
"""
io = pe.save_book_as(session=session, tables=tables, dest_file_type=file_type, **keywords)
return _make_response(io, file_type, status, file_name)
def make_response_from_book_dict(adict,
file_type, status=200, file_name=None,
**keywords):
"""
Make a http response from a dictionary of two dimensional
arrays
:param book_dict: a dictionary of two dimensional arrays
:param file_type: same as :meth:`~pyexcel_webio.make_response`
:param status: same as :meth:`~pyexcel_webio.make_response`
:returns: http response
"""
io = pe.save_book_as(bookdict=adict, dest_file_type=file_type, **keywords)
return _make_response(io, file_type, status, file_name)