def test_get_max_cell(datadir, DummyWorkbook, ReadOnlyWorksheet, filename):
datadir.join("reader").chdir()
DummyWorkbook._archive.write(filename, "sheet1.xml")
ws = ReadOnlyWorksheet(DummyWorkbook, "Sheet", "sheet1.xml", [])
ws._shared_strings = ['A', 'B']
rows = tuple(ws.rows)
assert rows[-1][-1].coordinate == "AA30"
def test_force_dimension(datadir, DummyWorkbook, ReadOnlyWorksheet):
datadir.join("reader").chdir()
wb = DummyWorkbook
wb._archive.write("sheet2_no_dimension.xml", "sheet1.xml")
ws = ReadOnlyWorksheet(DummyWorkbook, "Sheet", "sheet1.xml", [])
ws._shared_strings = ['A', 'B']
dims = ws.calculate_dimension(True)
assert dims == "A1:AA30"
def test_ctor(datadir, DummyWorkbook, ReadOnlyWorksheet, filename, expected):
datadir.join("reader").chdir()
wb = DummyWorkbook
wb._archive.write(filename, "sheet1.xml")
with open(filename) as src:
ws = ReadOnlyWorksheet(DummyWorkbook, "Sheet", "sheet1.xml", [])
assert (ws.min_row, ws.min_column, ws.max_row, ws.max_column) == expected
def test_read_hyperlinks_read_only(datadir, DummyWorkbook, ReadOnlyWorksheet):
datadir.join("reader").chdir()
wb = DummyWorkbook
wb._archive.write("bug393-worksheet.xml", "sheet1.xml")
ws = ReadOnlyWorksheet(wb, "Sheet", "sheet1.xml", ['SOMETEXT'])
assert ws['F2'].value is None
def test_read_empty_rows(datadir, DummyWorkbook, ReadOnlyWorksheet):
datadir.join("reader").chdir()
wb = DummyWorkbook
wb._archive.write("empty_rows.xml", "sheet1.xml")
ws = ReadOnlyWorksheet(wb, "Sheet", "sheet1.xml", [])
rows = tuple(ws.rows)
assert len(rows) == 7
def test_read_with_missing_cells(datadir, DummyWorkbook, ReadOnlyWorksheet):
datadir.join("reader").chdir()
wb = DummyWorkbook
wb._archive.write("bug393-worksheet.xml", "sheet1.xml")
ws = ReadOnlyWorksheet(wb, "Sheet", "sheet1.xml", [])
rows = tuple(ws.rows)
row = rows[1] # second row
values = [c.value for c in row]
assert values == [None, None, 1, 2, 3]
row = rows[3] # fourth row
values = [c.value for c in row]
assert values == [1, 2, None, None, 3]
def read_worksheets(self):
comment_warning = """Cell '{0}':{1} is part of a merged range but has a comment which will be removed because merged cells cannot contain any data."""
for sheet, rel in self.parser.find_sheets():
if rel.target not in self.valid_files:
continue
if "chartsheet" in rel.Type:
self.read_chartsheet(sheet, rel)
continue
rels_path = get_rels_path(rel.target)
rels = RelationshipList()
if rels_path in self.valid_files:
rels = get_dependents(self.archive, rels_path)
if self.read_only:
ws = ReadOnlyWorksheet(self.wb, sheet.name, rel.target,
self.shared_strings)
self.wb._sheets.append(ws)
continue
else:
fh = self.archive.open(rel.target)
ws = self.wb.create_sheet(sheet.name)
ws._rels = rels
ws_parser = WorksheetReader(ws, fh, self.shared_strings,
self.data_only)
ws_parser.bind_all()
# assign any comments to cells
for r in rels.find(COMMENTS_NS):
src = self.archive.read(r.target)
comment_sheet = CommentSheet.from_tree(fromstring(src))
for ref, comment in comment_sheet.comments:
try:
ws[ref].comment = comment
except AttributeError:
c = ws[ref]
if isinstance(c, MergedCell):
warnings.warn(
comment_warning.format(ws.title, c.coordinate))
continue
# preserve link to VML file if VBA
if self.wb.vba_archive and ws.legacy_drawing:
ws.legacy_drawing = rels[ws.legacy_drawing].target
for t in ws_parser.tables:
src = self.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:
charts, images = find_images(self.archive, rel.target)
for c in charts:
ws.add_chart(c, c.anchor)
for im in images:
ws.add_image(im, im.anchor)
pivot_rel = rels.find(TableDefinition.rel_type)
for r in pivot_rel:
pivot_path = r.Target
src = self.archive.read(pivot_path)
tree = fromstring(src)
pivot = TableDefinition.from_tree(tree)
pivot.cache = self.parser.pivot_caches[pivot.cacheId]
ws.add_pivot(pivot)
ws.sheet_state = sheet.state
def read_worksheets(self):
for sheet, rel in self.parser.find_sheets():
if rel.target not in self.valid_files:
continue
if "chartsheet" in rel.Type:
self.read_chartsheet(sheet, rel)
continue
rels_path = get_rels_path(rel.target)
rels = RelationshipList()
if rels_path in self.valid_files:
rels = get_dependents(self.archive, rels_path)
if self.read_only:
ws = ReadOnlyWorksheet(self.wb, sheet.name, rel.target,
self.shared_strings)
self.wb._sheets.append(ws)
continue
else:
fh = self.archive.open(rel.target)
ws = self.wb.create_sheet(sheet.name)
ws._rels = rels
ws_parser = WorksheetReader(ws, fh, self.shared_strings,
self.data_only)
ws_parser.bind_all()
# assign any comments to cells
for r in rels.find(COMMENTS_NS):
src = self.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 self.wb.vba_archive and ws.legacy_drawing:
ws.legacy_drawing = rels[ws.legacy_drawing].target
for t in ws_parser.tables:
src = self.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:
charts, images = find_images(self.archive, rel.target)
for c in charts:
ws.add_chart(c, c.anchor)
for im in images:
ws.add_image(im, im.anchor)
pivot_rel = rels.find(TableDefinition.rel_type)
for r in pivot_rel:
pivot_path = r.Target
src = self.archive.read(pivot_path)
tree = fromstring(src)
pivot = TableDefinition.from_tree(tree)
pivot.cache = self.parser.pivot_caches[pivot.cacheId]
ws.add_pivot(pivot)
ws.sheet_state = sheet.state
