def test_from_xml(self, Table):
src = """
<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
id="1" name="Table1" displayName="Table1" ref="A1:AA27">
</table>
"""
node = fromstring(src)
table = Table.from_tree(node)
assert table == Table(displayName="Table1", name="Table1",
ref="A1:AA27")
def test_ctor(self, Table, TableColumn):
table = Table(displayName="A_Sample_Table", ref="A1:D5")
xml = tostring(table.to_tree())
expected = """
<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
displayName="A_Sample_Table" headerRowCount="1" name="A_Sample_Table" id="1" ref="A1:D5">
</table>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_ctor(self, Table, TableColumn):
table = Table(displayName="A_Sample_Table", ref="A1:D5")
table._initialise_columns()
xml = tostring(table.to_tree())
expected = """
<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
displayName="A_Sample_Table" headerRowCount="1" name="A_Sample_Table" id="1" ref="A1:D5">
<autoFilter ref="A1:D5" />
<tableColumns count="4">
<tableColumn id="1" name="Column1" />
<tableColumn id="2" name="Column2" />
<tableColumn id="3" name="Column3" />
<tableColumn id="4" name="Column4" />
</tableColumns>
</table>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_write_tables(worksheet, write_worksheet):
from openpyxl25.worksheet.table import Table
worksheet.append(list(u"ABCDEF\xfc"))
worksheet._tables = [Table(displayName="Table1", ref="A1:G6")]
xml = write_worksheet(worksheet)
assert len(worksheet._rels) == 1
expected = """
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheetPr>
<outlinePr summaryRight="1" summaryBelow="1"/>
<pageSetUpPr/>
</sheetPr>
<dimension ref="A1:G1"/>
<sheetViews>
<sheetView workbookViewId="0">
<selection sqref="A1" activeCell="A1"/>
</sheetView>
</sheetViews>
<sheetFormatPr baseColWidth="8" defaultRowHeight="15"/>
<sheetData>
<row r="1" spans="1:7">
<c r="A1" t="s">
<v>0</v>
</c>
<c r="B1" t="s">
<v>1</v>
</c>
<c r="C1" t="s">
<v>2</v>
</c>
<c r="D1" t="s">
<v>3</v>
</c>
<c r="E1" t="s">
<v>4</v>
</c>
<c r="F1" t="s">
<v>5</v>
</c>
<c r="G1" t="s">
<v>6</v>
</c>
</row>
</sheetData>
<pageMargins left="0.75" right="0.75" top="1" bottom="1" header="0.5" footer="0.5"/>
<tableParts count="1">
<tablePart r:id="rId1" />
</tableParts>
</worksheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_table_rels(worksheet):
from openpyxl25.worksheet.table import Table
from openpyxl25.writer.worksheet import _add_table_headers
worksheet.append(list(u"ABCDEF\xfc"))
worksheet._tables = [Table(displayName="Table1", ref="A1:G6")]
_add_table_headers(worksheet)
assert worksheet._rels[
'rId1'].Type == "http://schemas.openxmlformats.org/officeDocument/2006/relationships/table"
def test_tables(ExcelWriter, archive):
wb = Workbook()
ws = wb.active
ws.append(list(ascii_letters))
ws._rels = []
t = Table(displayName="Table1", ref="A1:D10")
ws.add_table(t)
writer = ExcelWriter(wb, archive)
writer._write_worksheets()
assert t.path[1:] in archive.namelist()
assert t.path in writer.manifest.filenames
from openpyxl25 import Workbook
from openpyxl25.worksheet.table import Table, TableStyleInfo
wb = Workbook()
ws = wb.active
data = [
['Apples', 10000, 5000, 8000, 6000],
['Pears', 2000, 3000, 4000, 5000],
['Bananas', 6000, 6000, 6500, 6000],
['Oranges', 500, 300, 200, 700],
]
# add column headings. NB. these must be strings
ws.append(["Fruit", "2011", "2012", "2013", "2014"])
for row in data:
ws.append(row)
tab = Table(displayName="Table1", ref="A1:E5")
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
wb.save("table.xlsx")
def load_workbook(filename,
read_only=False,
keep_vba=KEEP_VBA,
data_only=False,
guess_types=False,
keep_links=True):
"""Open the given filename and return the workbook
:param filename: the path to open or a file-like object
:type filename: string or a file-like object open in binary mode c.f., :class:`zipfile.ZipFile`
:param read_only: optimised for reading, content cannot be edited
:type read_only: bool
:param keep_vba: preseve vba content (this does NOT mean you can use it)
:type keep_vba: bool
:param guess_types: guess cell content type and do not read it from the file
:type guess_types: bool
:param data_only: controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet
:type data_only: bool
:param keep_links: whether links to external workbooks should be preserved. The default is True
:type keep_links: bool
:rtype: :class:`openpyxl25.workbook.Workbook`
.. note::
When using lazy load, all worksheets will be :class:`openpyxl25.worksheet.iter_worksheet.IterableWorksheet`
and the returned workbook will be read-only.
"""
archive = _validate_archive(filename)
read_only = read_only
src = archive.read(ARC_CONTENT_TYPES)
root = fromstring(src)
package = Manifest.from_tree(root)
wb_part = _find_workbook_part(package)
parser = WorkbookParser(archive, wb_part.PartName[1:])
wb = parser.wb
wb._data_only = data_only
wb._read_only = read_only
wb._keep_links = keep_links
wb.guess_types = guess_types
wb.template = wb_part.ContentType in (XLTX, XLTM)
parser.parse()
wb._sheets = []
if read_only and guess_types:
warnings.warn('Data types are not guessed when using iterator reader')
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:
wb.vba_archive = ZipFile(BytesIO(), 'a', ZIP_DEFLATED)
for name in archive.namelist():
wb.vba_archive.writestr(name, archive.read(name))
if read_only:
wb._archive = ZipFile(filename)
# get workbook-level information
if ARC_CORE in valid_files:
src = fromstring(archive.read(ARC_CORE))
wb.properties = DocumentProperties.from_tree(src)
shared_strings = []
ct = package.find(SHARED_STRINGS)
if ct is not None:
strings_path = ct.PartName[1:]
shared_strings = read_string_table(archive.read(strings_path))
if ARC_THEME in valid_files:
wb.loaded_theme = archive.read(ARC_THEME)
apply_stylesheet(archive, wb) # bind styles to workbook
pivot_caches = parser.pivot_caches
# get worksheets
for sheet, rel in parser.find_sheets():
sheet_name = sheet.name
worksheet_path = rel.target
rels_path = get_rels_path(worksheet_path)
rels = []
if rels_path in valid_files:
rels = get_dependents(archive, rels_path)
if not worksheet_path in valid_files:
continue
if read_only:
ws = ReadOnlyWorksheet(wb, sheet_name, worksheet_path, None,
shared_strings)
wb._sheets.append(ws)
else:
fh = archive.open(worksheet_path)
ws = wb.create_sheet(sheet_name)
ws._rels = rels
ws_parser = WorkSheetParser(ws, fh, shared_strings)
ws_parser.parse()
if rels:
# assign any comments to cells
for r in rels.find(COMMENTS_NS):
src = archive.read(r.target)
comment_sheet = CommentSheet.from_tree(fromstring(src))
for ref, comment in comment_sheet.comments:
ws[ref].comment = comment
# preserve link to VML file if VBA
if (wb.vba_archive is not None
and ws.legacy_drawing is not None):
ws.legacy_drawing = rels[ws.legacy_drawing].target
for t in ws_parser.tables:
src = archive.read(t)
xml = fromstring(src)
table = Table.from_tree(xml)
ws.add_table(table)
drawings = rels.find(SpreadsheetDrawing._rel_type)
for rel in drawings:
for c in find_charts(archive, rel.target):
ws.add_chart(c, c.anchor)
pivot_rel = rels.find(TableDefinition.rel_type)
for r in pivot_rel:
pivot_path = r.Target
src = archive.read(pivot_path)
tree = fromstring(src)
pivot = TableDefinition.from_tree(tree)
pivot.cache = pivot_caches[pivot.cacheId]
ws.add_pivot(pivot)
ws.sheet_state = sheet.state
ws._rels = [] # reset
parser.assign_names()
#wb._differential_styles.styles = [] # tables may depened upon dxf
archive.close()
return wb
def test_write(self, Table):
out = BytesIO()
archive = ZipFile(out, "w")
table = Table(displayName="Table1", ref="B1:L10")
table._write(archive)
assert "xl/tables/table1.xml" in archive.namelist()
def test_path(self, Table):
table = Table(displayName="Table1", ref="A1:M6")
assert table.path == "/xl/tables/table1.xml"