def _write_images(self, images, archive, image_id):
for img in images:
buf = BytesIO()
img.image.save(buf, format= 'PNG')
archive.writestr(PACKAGE_IMAGES + '/image%d.png' % image_id, buf.getvalue())
image_id += 1
return image_id
def save_virtual_workbook(workbook):
"""Return an in-memory workbook, suitable for a Django response."""
writer = ExcelWriter(workbook)
temp_buffer = BytesIO()
try:
archive = ZipFile(temp_buffer, "w", ZIP_DEFLATED)
writer.write_data(archive)
finally:
archive.close()
virtual_workbook = temp_buffer.getvalue()
temp_buffer.close()
return virtual_workbook
def get_xml(xml_node):
io = BytesIO()
if version_info[0] >= 3 and version_info[1] >= 2:
ElementTree(xml_node).write(io, encoding="UTF-8", xml_declaration=False)
ret = str(io.getvalue(), "utf-8")
ret = ret.replace("utf-8", "UTF-8", 1)
else:
ElementTree(xml_node).write(io, encoding="UTF-8")
ret = io.getvalue()
io.close()
return ret.replace("\n", "")
def save_virtual_workbook(workbook):
"""Return an in-memory workbook, suitable for a Django response."""
writer = ExcelWriter(workbook)
temp_buffer = BytesIO()
try:
archive = ZipFile(temp_buffer, 'w', ZIP_DEFLATED)
writer.write_data(archive)
finally:
archive.close()
virtual_workbook = temp_buffer.getvalue()
temp_buffer.close()
return virtual_workbook
def test_rewrite_styles(self):
"""Test to verify Bugfix # 46"""
self.worksheet['A1'].value = 'Value'
self.worksheet['B2'].value = '14%'
saved_wb = save_virtual_workbook(self.workbook)
second_wb = load_workbook(BytesIO(saved_wb))
assert isinstance(second_wb, Workbook)
ws = second_wb.get_sheet_by_name('Sheet1')
assert ws.cell('A1').value == 'Value'
ws['A2'].value = 'Bar!'
saved_wb = save_virtual_workbook(second_wb)
third_wb = load_workbook(BytesIO(saved_wb))
assert third_wb
def _get_xml_iter(xml_source):
if not hasattr(xml_source, 'read'):
if hasattr(xml_source, 'decode'):
return BytesIO(xml_source)
else:
return BytesIO(xml_source.encode('utf-8'))
else:
try:
xml_source.seek(0)
except:
pass
return xml_source
def test_save_vba():
path = os.path.join(DATADIR, 'reader', 'vba-test.xlsm')
wb = load_workbook(path, keep_vba=True)
buf = save_virtual_workbook(wb)
files1 = set(zipfile.ZipFile(path, 'r').namelist())
files2 = set(zipfile.ZipFile(BytesIO(buf), 'r').namelist())
assert files1.issubset(files2), "Missing files: %s" % ', '.join(files1 -
files2)
def test_write_images():
wb = Workbook()
ew = ExcelWriter(workbook=wb)
from openpyxl.drawing import Image
imagepath = os.path.join(DATADIR, "plain.png")
img = Image(imagepath)
buf = BytesIO()
archive = zipfile.ZipFile(buf, 'w')
ew._write_images([img], archive, 1)
archive.close()
buf.seek(0)
archive = zipfile.ZipFile(buf, 'r')
zipinfo = archive.infolist()
assert len(zipinfo) == 1
assert zipinfo[0].filename == 'xl/media/image1.png'
def get_xml(xml_node):
io = BytesIO()
if version_info[0] >= 3 and version_info[1] >= 2:
ElementTree(xml_node).write(io, encoding='UTF-8', xml_declaration=True)
ret = str(io.getvalue(), 'utf-8')
ret = ret.replace('utf-8', 'UTF-8', 1)
else:
ElementTree(xml_node).write(io, encoding='UTF-8')
ret = io.getvalue()
io.close()
return ret.replace('\n', '')
def assert_equals_file_content(reference_file, fixture, filetype = 'xml'):
if os.path.isfile(fixture):
fixture_file = open(fixture)
try:
fixture_content = fixture_file.read()
finally:
fixture_file.close()
else:
fixture_content = fixture
expected_file = open(reference_file)
try:
expected_content = expected_file.read()
finally:
expected_file.close()
if filetype == 'xml':
fixture_content = fromstring(fixture_content)
pretty_indent(fixture_content)
temp = BytesIO()
ElementTree(fixture_content).write(temp)
fixture_content = temp.getvalue()
expected_content = fromstring(expected_content)
pretty_indent(expected_content)
temp = BytesIO()
ElementTree(expected_content).write(temp)
expected_content = temp.getvalue()
fixture_lines = unicode(fixture_content).split('\n')
expected_lines = unicode(expected_content).split('\n')
differences = list(difflib.unified_diff(expected_lines, fixture_lines))
if differences:
temp = BytesIO()
pprint(differences, stream = temp)
assert False, 'Differences found : %s' % temp.getvalue()
def test_save_without_vba():
path = os.path.join(DATADIR, 'reader', 'vba-test.xlsm')
vbFiles = set([
'xl/activeX/activeX2.xml', 'xl/drawings/_rels/vmlDrawing1.vml.rels',
'xl/activeX/_rels/activeX1.xml.rels', 'xl/drawings/vmlDrawing1.vml',
'xl/activeX/activeX1.bin', 'xl/media/image1.emf', 'xl/vbaProject.bin',
'xl/activeX/_rels/activeX2.xml.rels',
'xl/worksheets/_rels/sheet1.xml.rels', 'customUI/customUI.xml',
'xl/media/image2.emf', 'xl/ctrlProps/ctrlProp1.xml',
'xl/activeX/activeX2.bin', 'xl/activeX/activeX1.xml',
'xl/ctrlProps/ctrlProp2.xml', 'xl/drawings/drawing1.xml'
])
wb = load_workbook(path, keep_vba=False)
buf = save_virtual_workbook(wb)
files1 = set(zipfile.ZipFile(path, 'r').namelist())
files2 = set(zipfile.ZipFile(BytesIO(buf), 'r').namelist())
difference = files1.difference(files2)
assert difference.issubset(
vbFiles), "Missing files: %s" % ', '.join(difference - vbFiles)
def test_change_existing_styles():
reference_file = os.path.join(DATADIR, 'reader', 'complex-styles.xlsx')
wb = load_workbook(reference_file)
ws = wb.get_active_sheet()
ws.column_dimensions['A'].width = 20
ws.cell('A2').style.font.name = 'Times New Roman'
ws.cell('A2').style.font.size = 12
ws.cell('A2').style.font.bold = True
ws.cell('A2').style.font.italic = True
ws.cell('A3').style.font.name = 'Times New Roman'
ws.cell('A3').style.font.size = 14
ws.cell('A3').style.font.bold = False
ws.cell('A3').style.font.italic = True
ws.cell('A4').style.font.name = 'Times New Roman'
ws.cell('A4').style.font.size = 16
ws.cell('A4').style.font.bold = True
ws.cell('A4').style.font.italic = False
ws.cell('A5').style.font.color.index = 'FF66FF66'
ws.cell('A6').style.font.color.index = 'theme:1:'
ws.cell('A7').style.fill.start_color.index = 'FF330066'
ws.cell('A8').style.fill.start_color.index = 'theme:2:'
ws.cell('A9').style.alignment.horizontal = 'center'
ws.cell('A10').style.alignment.horizontal = 'left'
ws.cell('A11').style.alignment.horizontal = 'right'
ws.cell('A12').style.alignment.vertical = 'bottom'
ws.cell('A13').style.alignment.vertical = 'top'
ws.cell('A14').style.alignment.vertical = 'center'
ws.cell('A15').style.number_format._format_code = '0.00%'
ws.cell('A16').style.number_format._format_code = '0.00'
ws.cell('A17').style.number_format._format_code = 'mm-dd-yy'
ws.unmerge_cells('A18:B18')
ws.cell('A19').style.borders.top.color.index = 'FF006600'
ws.cell('A19').style.borders.bottom.color.index = 'FF006600'
ws.cell('A19').style.borders.left.color.index = 'FF006600'
ws.cell('A19').style.borders.right.color.index = 'FF006600'
ws.cell('A21').style.borders.top.color.index = 'theme:7:'
ws.cell('A21').style.borders.bottom.color.index = 'theme:7:'
ws.cell('A21').style.borders.left.color.index = 'theme:7:'
ws.cell('A21').style.borders.right.color.index = 'theme:7:'
ws.cell('A23').style.fill.start_color.index = 'FFCCCCFF'
ws.cell('A23').style.borders.top.color.index = 'theme:6:'
ws.unmerge_cells('A23:B24')
ws.cell('A25').style.alignment.wrap_text = False
ws.cell('A26').style.alignment.shrink_to_fit = False
saved_wb = save_virtual_workbook(wb)
new_wb = load_workbook(BytesIO(saved_wb))
ws = new_wb.get_active_sheet()
eq_(ws.column_dimensions['A'].width, 20.0)
eq_(ws.cell('A2').style.font.name, 'Times New Roman')
eq_(ws.cell('A2').style.font.size, '12')
eq_(ws.cell('A2').style.font.bold, True)
eq_(ws.cell('A2').style.font.italic, True)
eq_(ws.cell('A3').style.font.name, 'Times New Roman')
eq_(ws.cell('A3').style.font.size, '14')
eq_(ws.cell('A3').style.font.bold, False)
eq_(ws.cell('A3').style.font.italic, True)
eq_(ws.cell('A4').style.font.name, 'Times New Roman')
eq_(ws.cell('A4').style.font.size, '16')
eq_(ws.cell('A4').style.font.bold, True)
eq_(ws.cell('A4').style.font.italic, False)
eq_(ws.cell('A5').style.font.color.index, 'FF66FF66')
eq_(ws.cell('A6').style.font.color.index, 'theme:1:')
eq_(ws.cell('A7').style.fill.start_color.index, 'FF330066')
eq_(ws.cell('A8').style.fill.start_color.index, 'theme:2:')
eq_(ws.cell('A9').style.alignment.horizontal, 'center')
eq_(ws.cell('A10').style.alignment.horizontal, 'left')
eq_(ws.cell('A11').style.alignment.horizontal, 'right')
eq_(ws.cell('A12').style.alignment.vertical, 'bottom')
eq_(ws.cell('A13').style.alignment.vertical, 'top')
eq_(ws.cell('A14').style.alignment.vertical, 'center')
eq_(ws.cell('A15').style.number_format._format_code, '0.00%')
eq_(ws.cell('A16').style.number_format._format_code, '0.00')
eq_(ws.cell('A17').style.number_format._format_code, 'mm-dd-yy')
eq_('A18:B18' in ws._merged_cells, False)
eq_(ws.cell('B18').merged, False)
eq_(ws.cell('A19').style.borders.top.color.index, 'FF006600')
eq_(ws.cell('A19').style.borders.bottom.color.index, 'FF006600')
eq_(ws.cell('A19').style.borders.left.color.index, 'FF006600')
eq_(ws.cell('A19').style.borders.right.color.index, 'FF006600')
eq_(ws.cell('A21').style.borders.top.color.index, 'theme:7:')
eq_(ws.cell('A21').style.borders.bottom.color.index, 'theme:7:')
eq_(ws.cell('A21').style.borders.left.color.index, 'theme:7:')
eq_(ws.cell('A21').style.borders.right.color.index, 'theme:7:')
eq_(ws.cell('A23').style.fill.start_color.index, 'FFCCCCFF')
eq_(ws.cell('A23').style.borders.top.color.index, 'theme:6:')
eq_('A23:B24' in ws._merged_cells, False)
eq_(ws.cell('A24').merged, False)
eq_(ws.cell('B23').merged, False)
eq_(ws.cell('B24').merged, False)
eq_(ws.cell('A25').style.alignment.wrap_text, False)
eq_(ws.cell('A26').style.alignment.shrink_to_fit, False)
# Verify that previously duplicate styles remain the same
eq_(ws.column_dimensions['C'].width, 31.1640625)
eq_(ws.cell('C2').style.font.name, 'Arial')
eq_(ws.cell('C2').style.font.size, '10')
eq_(ws.cell('C2').style.font.bold, False)
eq_(ws.cell('C2').style.font.italic, False)
eq_(ws.cell('C3').style.font.name, 'Arial')
eq_(ws.cell('C3').style.font.size, '12')
eq_(ws.cell('C3').style.font.bold, True)
eq_(ws.cell('C3').style.font.italic, False)
eq_(ws.cell('C4').style.font.name, 'Arial')
eq_(ws.cell('C4').style.font.size, '14')
eq_(ws.cell('C4').style.font.bold, False)
eq_(ws.cell('C4').style.font.italic, True)
eq_(ws.cell('C5').style.font.color.index, 'FF3300FF')
eq_(ws.cell('C6').style.font.color.index, 'theme:9:')
eq_(ws.cell('C7').style.fill.start_color.index, 'FFFFFF66')
eq_(ws.cell('C8').style.fill.start_color.index, 'theme:8:')
eq_(ws.cell('C9').style.alignment.horizontal, 'left')
eq_(ws.cell('C10').style.alignment.horizontal, 'right')
eq_(ws.cell('C11').style.alignment.horizontal, 'center')
eq_(ws.cell('C12').style.alignment.vertical, 'top')
eq_(ws.cell('C13').style.alignment.vertical, 'center')
eq_(ws.cell('C14').style.alignment.vertical, 'bottom')
eq_(ws.cell('C15').style.number_format._format_code, '0.00')
eq_(ws.cell('C16').style.number_format._format_code, 'mm-dd-yy')
eq_(ws.cell('C17').style.number_format._format_code, '0.00%')
eq_('C18:D18' in ws._merged_cells, True)
eq_(ws.cell('D18').merged, True)
eq_(ws.cell('C19').style.borders.top.color.index, 'FF006600')
eq_(ws.cell('C19').style.borders.bottom.color.index, 'FF006600')
eq_(ws.cell('C19').style.borders.left.color.index, 'FF006600')
eq_(ws.cell('C19').style.borders.right.color.index, 'FF006600')
eq_(ws.cell('C21').style.borders.top.color.index, 'theme:7:')
eq_(ws.cell('C21').style.borders.bottom.color.index, 'theme:7:')
eq_(ws.cell('C21').style.borders.left.color.index, 'theme:7:')
eq_(ws.cell('C21').style.borders.right.color.index, 'theme:7:')
eq_(ws.cell('C23').style.fill.start_color.index, 'FFCCCCFF')
eq_(ws.cell('C23').style.borders.top.color.index, 'theme:6:')
eq_('C23:D24' in ws._merged_cells, True)
eq_(ws.cell('C24').merged, True)
eq_(ws.cell('D23').merged, True)
eq_(ws.cell('D24').merged, True)
eq_(ws.cell('C25').style.alignment.wrap_text, True)
eq_(ws.cell('C26').style.alignment.shrink_to_fit, True)
def test_write_virtual_workbook():
old_wb = Workbook()
saved_wb = save_virtual_workbook(old_wb)
new_wb = load_workbook(BytesIO(saved_wb))
assert new_wb
