def test_simple(self):
book = XLSXBook()
sheet1 = book.add_sheet("People")
sheet1.append_row("Name", "Email")
sheet1.append_row("Jim", "*****@*****.**")
sheet1.append_row("Bob", "*****@*****.**")
book.add_sheet("Empty")
# insert a new sheet at a specific index
book.add_sheet("New first", index=0)
book.finalize(to_file="_tests/simple.xlsx")
book = load_workbook(filename="_tests/simple.xlsx")
assert len(book.worksheets) == 3
sheet1, sheet2, sheet3 = book.worksheets
assert sheet1.title == "New first"
assert sheet2.title == "People"
assert sheet3.title == "Empty"
self.assertExcelSheet(sheet1, [()])
self.assertExcelSheet(sheet2, [("Name", "Email"),
("Jim", "*****@*****.**"),
("Bob", "*****@*****.**")])
self.assertExcelSheet(sheet3, [()])
def export_results(self):
now = datetime.now().strftime('%Y%m%d%H%M%S')
options = {}
options['defaultextension'] = '.xlsx'
options['filetypes'] = [('XLSX file', '.xlsx')]
options['initialdir'] = os.path.expanduser("~") + "/Downloads/"
options['initialfile'] = 'RegistreerLopers'
options['title'] = 'Bewaar database in Excel bestand'
filename = filedialog.asksaveasfilename(**options)
self.log.info('exporteer naar : {}'.format(filename))
try:
f = open(filename, 'w', newline="")
data = self.database.get_students_sorted_on_time_ran()
book = XLSXBook()
sheet = book.add_sheet('uitslag')
sheet.append_row('NAAM', 'VOORNAAM', 'KLAS', 'TIJD', 'STARTTIJD')
for r in data:
if r['time_ran']:
time_ran = r['time_ran']
starttime = r['starttime']
else:
time_ran = starttime = 0
t_min = int(time_ran / 60000)
t_sec = int((time_ran - 60000 * t_min)/ 1000)
t_msec = time_ran - 60000 * t_min - 1000 * t_sec
sheet.append_row(r['last_name'], r['first_name'], r['classgroup'], \
'{:02d}:{:02d}.{:03d}'.format(t_min, t_sec, t_msec), starttime)
book.finalize(to_file=filename)
messagebox.showinfo('Export', 'Tijden zijn geëxporteerd')
self.log.info('Resultes are exported')
except Exception as e:
messagebox.showinfo('Export', 'Kon niet exporteren : {}'.format(e))
self.log.error('Could not export : {}'.format(e))
def test_escaping(self):
book = XLSXBook()
sheet1 = book.add_sheet("Test")
sheet1.append_row('< & > " ! =')
book.finalize(to_file="_tests/escaped.xlsx")
book = load_workbook(filename="_tests/escaped.xlsx")
self.assertExcelSheet(book.worksheets[0], [('< & > " ! =', )])
class TableExporter(object):
"""
Class that abstracts out writing a table of data to a CSV or Excel file. This only works for exports that
have a single sheet (as CSV's don't have sheets) but takes care of writing to a CSV in the case
where there are more than 16384 columns, which Excel doesn't support.
When writing to an Excel sheet, this also takes care of creating different sheets every 1048576
rows, as again, Excel file only support that many per sheet.
"""
def __init__(self, task, sheet_name, columns):
self.task = task
self.columns = columns
self.sheet_name = sheet_name
self.current_sheet = 0
self.current_row = 0
self.workbook = XLSXBook()
self.sheet_number = 0
self._add_sheet()
def _add_sheet(self):
self.sheet_number += 1
# add our sheet
self.sheet = self.workbook.add_sheet(
"%s %d" % (self.sheet_name, self.sheet_number))
self.sheet.append_row(*self.columns)
self.sheet_row = 2
def write_row(self, values):
"""
Writes the passed in row to our exporter, taking care of creating new sheets if necessary
"""
# time for a new sheet? do it
if self.sheet_row > BaseExportTask.MAX_EXCEL_ROWS:
self._add_sheet()
self.sheet.append_row(*values)
self.sheet_row += 1
def save_file(self):
"""
Saves our data to a file, returning the file saved to and the extension
"""
gc.collect() # force garbage collection
print("Writing Excel workbook...")
temp_file = NamedTemporaryFile(delete=False,
suffix=".xlsx",
mode="wb+")
self.workbook.finalize(to_file=temp_file)
temp_file.flush()
return temp_file, "xlsx"
def xlsx_export(output, search_predicate, changeset_id):
with connection.cursor() as cur:
cur.execute("""select * from core_utils.export_all(%s, %s)""", (search_predicate, changeset_id, ))
query = cur.fetchone()[0]
data_buffer = StringIO()
cur.copy_expert(query, data_buffer)
cur.execute("""
SELECT attributes_attribute.key, attributes_attribute.result_type FROM public.attributes_attribute
""")
key_result_type = cur.fetchall()
data_buffer.seek(0)
csv_reader = csv.reader(data_buffer, dialect='excel')
book = XLSXBook()
sheet1 = book.add_sheet('waterpoints')
header = next(csv_reader)
sheet1.append_row(*header)
keys = []
result_types = []
for item in key_result_type:
keys.append(item[0])
result_types.append(item[1])
header_type = []
for item in header:
if item in keys:
Type = result_types[keys.index(item)]
if Type == 'DropDown' or Type == 'Text':
header_type.append('str')
elif Type == 'Decimal':
header_type.append('dec')
elif Type == 'Integer':
header_type.append('int')
else:
header_type.append('str')
else:
header_type.append('str')
for row in csv_reader:
for ind, cell in enumerate(row):
if header_type[ind] == 'int' and cell != '':
row[ind] = int(cell)
elif header_type[ind] == 'dec' and cell != '':
row[ind] = float(cell)
sheet1.append_row(*row)
filename = f'waterpoints_{time.strftime("%Y%m%d_%H%M%S", time.gmtime())}.xlsx'
book.finalize(to_file=output)
return filename, output
def test_xlxslite():
book = XLSXBook()
sheet1 = book.add_sheet("Sheet1")
for r in range(NUM_ROWS):
row = [DATA[(r * c) % len(DATA)] for c in range(NUM_COLS)]
sheet1.append_row(*row)
book.finalize(to_file="_tests/test.xlsx")
class TableExporter(object):
"""
Class that abstracts out writing a table of data to a CSV or Excel file. This only works for exports that
have a single sheet (as CSV's don't have sheets) but takes care of writing to a CSV in the case
where there are more than 16384 columns, which Excel doesn't support.
When writing to an Excel sheet, this also takes care of creating different sheets every 1048576
rows, as again, Excel file only support that many per sheet.
"""
def __init__(self, task, sheet_name, columns):
self.task = task
self.columns = columns
self.sheet_name = sheet_name
self.current_sheet = 0
self.current_row = 0
self.workbook = XLSXBook()
self.sheet_number = 0
self._add_sheet()
def _add_sheet(self):
self.sheet_number += 1
# add our sheet
self.sheet = self.workbook.add_sheet("%s %d" % (self.sheet_name, self.sheet_number))
self.sheet.append_row(*self.columns)
self.sheet_row = 2
def write_row(self, values):
"""
Writes the passed in row to our exporter, taking care of creating new sheets if necessary
"""
# time for a new sheet? do it
if self.sheet_row > BaseExportTask.MAX_EXCEL_ROWS:
self._add_sheet()
self.sheet.append_row(*values)
self.sheet_row += 1
def save_file(self):
"""
Saves our data to a file, returning the file saved to and the extension
"""
gc.collect() # force garbage collection
print("Writing Excel workbook...")
temp_file = NamedTemporaryFile(delete=False, suffix=".xlsx", mode="wb+")
self.workbook.finalize(to_file=temp_file)
temp_file.flush()
return temp_file, "xlsx"
def test_cell_types(self):
d1 = datetime(2013, 1, 1, 12, 0, 0)
book = XLSXBook()
sheet1 = book.add_sheet("Test")
sheet1.append_row("str", True, False, 3, 1.23, d1)
# try to write a cell value with an unsupported type
with pytest.raises(ValueError):
sheet1.append_row(timedelta(days=1))
book.finalize(to_file="_tests/types.xlsx")
book = load_workbook(filename="_tests/types.xlsx")
self.assertExcelSheet(book.worksheets[0],
[("str", True, False, 3, 1.23, d1)])
def test_sheet_limits(self):
book = XLSXBook()
sheet1 = book.add_sheet("Sheet1")
# try to add row with too many columns
column = ["x"] * 20000
with pytest.raises(ValueError):
sheet1.append_row(*column)
# try to add more rows than allowed
with patch("xlsxlite.writer.XLSXSheet.MAX_ROWS", 3):
sheet1.append_row("x")
sheet1.append_row("x")
sheet1.append_row("x")
with pytest.raises(ValueError):
sheet1.append_row("x")