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 _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 _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 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 _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_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 test_save_vba(datadir):
path = str(datadir.join('reader').join('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 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 test_save_without_vba(datadir):
path = str(datadir.join('reader').join('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 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 _load_workbook(wb, archive, filename, use_iterators, keep_vba):
valid_files = archive.namelist()
# If are going to preserve the vba then attach a copy of the archive to the
# workbook so that is available for the save.
if keep_vba:
try:
f = open(filename, 'rb')
s = f.read()
f.close()
except:
pos = filename.tell()
filename.seek(0)
s = filename.read()
filename.seek(pos)
wb.vba_archive = ZipFile(BytesIO(s), 'r')
if use_iterators:
wb._archive = ZipFile(filename)
# get workbook-level information
try:
wb.properties = read_properties_core(archive.read(ARC_CORE))
wb.read_workbook_settings(archive.read(ARC_WORKBOOK))
except KeyError:
wb.properties = DocumentProperties()
try:
string_table = read_string_table(archive.read(ARC_SHARED_STRINGS))
except KeyError:
string_table = {}
try:
wb.loaded_theme = archive.read(
ARC_THEME
) # some writers don't output a theme, live with it (fixes #160)
except KeyError:
assert wb.loaded_theme == None, "even though the theme information is missing there is a theme object ?"
style_properties = read_style_table(archive.read(ARC_STYLE))
style_table = style_properties.pop('table')
wb.style_properties = style_properties
wb.properties.excel_base_date = read_excel_base_date(
xml_source=archive.read(ARC_WORKBOOK))
# get worksheets
wb.worksheets = [] # remove preset worksheet
for sheet in detect_worksheets(archive):
sheet_name = sheet['title']
worksheet_path = '%s/%s' % (PACKAGE_XL, sheet['path'])
if not worksheet_path in valid_files:
continue
if not use_iterators:
new_ws = read_worksheet(
archive.read(worksheet_path),
wb,
sheet_name,
string_table,
style_table,
color_index=style_properties['color_index'],
keep_vba=keep_vba)
else:
new_ws = read_worksheet(
None,
wb,
sheet_name,
string_table,
style_table,
color_index=style_properties['color_index'],
worksheet_path=worksheet_path)
wb.add_sheet(new_ws)
if not use_iterators:
# load comments into the worksheet cells
comments_file = get_comments_file(worksheet_path, archive,
valid_files)
if comments_file is not None:
read_comments(new_ws, archive.read(comments_file))
wb._named_ranges = read_named_ranges(archive.read(ARC_WORKBOOK), wb)
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
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.get_style('I').fill.start_color.index = 'FF442200'
ws.get_style('I').font.color.index = 'FF002244'
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()
assert ws.column_dimensions['A'].width == 20.0
style = partial(ws.get_style, read_only=True)
assert ws.get_style('I').fill.start_color.index == 'FF442200'
assert ws.get_style('I').font.color.index == 'FF002244'
assert style('A2').font.name == 'Times New Roman'
assert style('A2').font.size == '12'
assert style('A2').font.bold
assert style('A2').font.italic
assert style('A3').font.name == 'Times New Roman'
assert style('A3').font.size == '14'
assert not style('A3').font.bold
assert style('A3').font.italic
assert style('A4').font.name == 'Times New Roman'
assert style('A4').font.size == '16'
assert style('A4').font.bold
assert not style('A4').font.italic
assert style('A5').font.color.index == 'FF66FF66'
assert style('A6').font.color.index == 'theme:1:'
assert style('A7').fill.start_color.index == 'FF330066'
assert style('A8').fill.start_color.index == 'theme:2:'
assert style('A9').alignment.horizontal == 'center'
assert style('A10').alignment.horizontal == 'left'
assert style('A11').alignment.horizontal == 'right'
assert style('A12').alignment.vertical == 'bottom'
assert style('A13').alignment.vertical == 'top'
assert style('A14').alignment.vertical == 'center'
assert style('A15').number_format == '0.00%'
assert style('A16').number_format == '0.00'
assert style('A17').number_format == 'mm-dd-yy'
assert 'A18:B18' not in ws._merged_cells
assert not ws.cell('B18').merged
assert style('A19').borders.top.color.index == 'FF006600'
assert style('A19').borders.bottom.color.index == 'FF006600'
assert style('A19').borders.left.color.index == 'FF006600'
assert style('A19').borders.right.color.index == 'FF006600'
assert style('A21').borders.top.color.index == 'theme:7:'
assert style('A21').borders.bottom.color.index == 'theme:7:'
assert style('A21').borders.left.color.index == 'theme:7:'
assert style('A21').borders.right.color.index == 'theme:7:'
assert style('A23').fill.start_color.index == 'FFCCCCFF'
assert style('A23').borders.top.color.index == 'theme:6:'
assert 'A23:B24' not in ws._merged_cells
assert not ws.cell('A24').merged
assert not ws.cell('B23').merged
assert not ws.cell('B24').merged
assert not style('A25').alignment.wrap_text
assert not style('A26').alignment.shrink_to_fit
# Verify that previously duplicate styles remain the same
assert ws.column_dimensions['C'].width == 31.1640625
assert style('C2').font.name == 'Arial'
assert style('C2').font.size == '10'
assert not style('C2').font.bold
assert not style('C2').font.italic
assert style('C3').font.name == 'Arial'
assert style('C3').font.size == '12'
assert style('C3').font.bold
assert not style('C3').font.italic
assert style('C4').font.name == 'Arial'
assert style('C4').font.size == '14'
assert not style('C4').font.bold
assert style('C4').font.italic
assert style('C5').font.color.index == 'FF3300FF'
assert style('C6').font.color.index == 'theme:9:'
assert style('C7').fill.start_color.index == 'FFFFFF66'
assert style('C8').fill.start_color.index == 'theme:8:'
assert style('C9').alignment.horizontal == 'left'
assert style('C10').alignment.horizontal == 'right'
assert style('C11').alignment.horizontal == 'center'
assert style('C12').alignment.vertical == 'top'
assert style('C13').alignment.vertical == 'center'
assert style('C14').alignment.vertical == 'bottom'
assert style('C15').number_format == '0.00'
assert style('C16').number_format == 'mm-dd-yy'
assert style('C17').number_format == '0.00%'
assert 'C18:D18' in ws._merged_cells
assert ws.cell('D18').merged
assert style('C19').borders.top.color.index == 'FF006600'
assert style('C19').borders.bottom.color.index == 'FF006600'
assert style('C19').borders.left.color.index == 'FF006600'
assert style('C19').borders.right.color.index == 'FF006600'
assert style('C21').borders.top.color.index == 'theme:7:'
assert style('C21').borders.bottom.color.index == 'theme:7:'
assert style('C21').borders.left.color.index == 'theme:7:'
assert style('C21').borders.right.color.index == 'theme:7:'
assert style('C23').fill.start_color.index == 'FFCCCCFF'
assert style('C23').borders.top.color.index == 'theme:6:'
assert 'C23:D24' in ws._merged_cells
assert ws.cell('C24').merged
assert ws.cell('D23').merged
assert ws.cell('D24').merged
assert style('C25').alignment.wrap_text
assert style('C26').alignment.shrink_to_fit