def test_read_sheets_titles(datadir, filename):
from openpyxl.reader.workbook import read_sheets
datadir.join("genuine").chdir()
archive = ZipFile(filename)
sheet_titles = [s['name'] for s in read_sheets(archive)]
assert sheet_titles == ['Sheet1 - Text', 'Sheet2 - Numbers', 'Sheet3 - Formulas', 'Sheet4 - Dates']
def test_hidden_sheets(datadir, DummyArchive):
from ..workbook import read_sheets
datadir.chdir()
archive = DummyArchive
with open("hidden_sheets.xml") as src:
archive.writestr(ARC_WORKBOOK, src.read())
sheets = read_sheets(archive)
assert list(sheets) == [
{
'id': 'rId1',
'name': 'Blatt1',
'sheetId': '1'
},
{
'id': 'rId2',
'sheetId': '2',
'name': 'Blatt2',
'state': 'hidden'
},
{
'id': 'rId3',
'state': 'hidden',
'sheetId': '3',
'name': 'Blatt3'
},
]
def test_read_sheets_titles(self):
from openpyxl.reader.workbook import read_sheets
sheet_titles = [s[1] for s in read_sheets(self.archive)]
assert sheet_titles == [
'Sheet1 - Text', 'Sheet2 - Numbers', 'Sheet3 - Formulas',
'Sheet4 - Dates'
]
def test_read_sheets(datadir, DummyArchive, workbook_file, expected):
from openpyxl.reader.workbook import read_sheets
datadir.chdir()
archive = DummyArchive
with open(workbook_file) as src:
archive.writestr(ARC_WORKBOOK, src.read())
assert list(read_sheets(archive)) == expected
def test_read_sheets(datadir, DummyArchive, workbook_file, expected):
from openpyxl.reader.workbook import read_sheets
datadir.chdir()
archive = DummyArchive
with open(workbook_file) as src:
archive.writestr(ARC_WORKBOOK, src.read())
assert list(read_sheets(archive)) == expected
def test_hidden_sheets(datadir, DummyArchive):
from ..workbook import read_sheets
datadir.chdir()
archive = DummyArchive
with open("hidden_sheets.xml") as src:
archive.writestr(ARC_WORKBOOK, src.read())
sheets = read_sheets(archive)
assert list(sheets) == [("rId1", "Blatt1", None), ("rId2", "Blatt2", "hidden"), ("rId3", "Blatt3", "hidden")]
def test_read_sheets_titles(datadir, filename):
from openpyxl.reader.workbook import read_sheets
datadir.join("genuine").chdir()
archive = ZipFile(filename)
sheet_titles = [s['name'] for s in read_sheets(archive)]
assert sheet_titles == [
'Sheet1 - Text', 'Sheet2 - Numbers', 'Sheet3 - Formulas',
'Sheet4 - Dates'
]
def test_hidden_sheets(datadir, DummyArchive):
from ..workbook import read_sheets
datadir.chdir()
archive = DummyArchive
with open("hidden_sheets.xml") as src:
archive.writestr(ARC_WORKBOOK, src.read())
sheets = read_sheets(archive)
assert list(sheets) == [('rId1', 'Blatt1', None),
('rId2', 'Blatt2', 'hidden'),
('rId3', 'Blatt3', 'hidden')]
def test_hidden_sheets(datadir, DummyArchive):
from .. workbook import read_sheets
datadir.chdir()
archive = DummyArchive
with open("hidden_sheets.xml") as src:
archive.writestr(ARC_WORKBOOK, src.read())
sheets = read_sheets(archive)
assert list(sheets) == [
{'id': 'rId1', 'name': 'Blatt1', 'sheetId': '1'},
{'id': 'rId2', 'sheetId': '2', 'name': 'Blatt2', 'state': 'hidden'},
{'id': 'rId3', 'state': 'hidden', 'sheetId': '3', 'name': 'Blatt3'},
]
def detect_worksheets(archive):
"""Return a list of worksheets"""
# content types has a list of paths but no titles
# workbook has a list of titles and relIds but no paths
# workbook_rels has a list of relIds and paths but no titles
# rels = {'id':{'title':'', 'path':''} }
from openpyxl.reader.workbook import read_rels, read_sheets
content_types = read_content_types(archive)
valid_sheets = dict(
(path, ct) for path, ct in content_types if ct == VALID_WORKSHEET)
rels = dict(read_rels(archive))
for rId, title in read_sheets(archive):
rel = rels[rId]
rel['title'] = title
if "/" + rel['path'] in valid_sheets:
yield rel
def detect_worksheets(archive):
"""Return a list of worksheets"""
# content types has a list of paths but no titles
# workbook has a list of titles and relIds but no paths
# workbook_rels has a list of relIds and paths but no titles
# rels = {'id':{'title':'', 'path':''} }
from openpyxl.reader.workbook import read_rels, read_sheets
content_types = list(read_content_types(archive))
rels = read_rels(archive)
sheets = read_sheets(archive)
for sheet in sheets:
rels[sheet[1]]['title'] = sheet[0]
for rId in sorted(rels):
for ct in content_types:
if ct[1] == VALID_WORKSHEET:
if '/xl/' + rels[rId]['path'] == ct[0]:
yield rels[rId]
def detect_worksheets(archive):
"""Return a list of worksheets"""
# content types has a list of paths but no titles
# workbook has a list of titles and relIds but no paths
# workbook_rels has a list of relIds and paths but no titles
# rels = {'id':{'title':'', 'path':''} }
from openpyxl.reader.workbook import read_rels, read_sheets
content_types = list(read_content_types(archive))
rels = read_rels(archive)
sheets = read_sheets(archive)
for sheet in sheets:
rels[sheet[1]]['title'] = sheet[0]
for rId in sorted(rels):
for ct in content_types:
if ct[1] == VALID_WORKSHEET:
if '/xl/' + rels[rId]['path'] == ct[0]:
yield rels[rId]
def test_missing_ids(datadir, DummyArchive):
datadir.chdir()
with open("workbook_missing_ids.xml") as src:
xml = src.read()
archive = DummyArchive
archive.writestr("xl/workbook.xml", xml)
from ..workbook import read_sheets
sheets = read_sheets(archive)
assert list(sheets) == [
{'sheetId': '1', 'id': 'rId1', 'name': '4CASTING RAP'},
{'sheetId': '11', 'id': 'rId2', 'name': '4CAST SLOPS'},
{'sheetId': '20', 'id': 'rId3', 'name': 'Chart4'},
{'sheetId': '18', 'id': 'rId4', 'name': 'Chart3'},
{'sheetId': '17', 'id': 'rId5', 'name': 'Chart2'},
{'sheetId': '16', 'id': 'rId6', 'name': 'Chart1'},
{'sheetId': '21', 'id': 'rId7', 'name': 'Sheet1'}
]
def test_missing_ids(datadir, DummyArchive):
datadir.chdir()
with open("workbook_missing_ids.xml") as src:
xml = src.read()
archive = DummyArchive
archive.writestr("xl/workbook.xml", xml)
from ..workbook import read_sheets
sheets = read_sheets(archive)
assert list(sheets) == [{
'sheetId': '1',
'id': 'rId1',
'name': '4CASTING RAP'
}, {
'sheetId': '11',
'id': 'rId2',
'name': '4CAST SLOPS'
}, {
'sheetId': '20',
'id': 'rId3',
'name': 'Chart4'
}, {
'sheetId': '18',
'id': 'rId4',
'name': 'Chart3'
}, {
'sheetId': '17',
'id': 'rId5',
'name': 'Chart2'
}, {
'sheetId': '16',
'id': 'rId6',
'name': 'Chart1'
}, {
'sheetId': '21',
'id': 'rId7',
'name': 'Sheet1'
}]
def test_read_sheets(datadir, excel_file, expected):
from openpyxl.reader.workbook import read_sheets
datadir.join("reader")
archive = zipfile.ZipFile(excel_file)
assert dict(read_sheets(archive)) == expected
def test_read_sheets():
from openpyxl.reader.workbook import read_sheets
fname = os.path.join(DATADIR, "reader", "bug137.xlsx")
archive = zipfile.ZipFile(fname)
assert list(read_sheets(archive)) == [("Chart1", 1), ("Sheet1",2)]
def test_read_sheets_titles(self):
from openpyxl.reader.workbook import read_sheets
sheet_titles = [s[0] for s in read_sheets(self.archive)]
assert sheet_titles == ['Sheet1 - Text', 'Sheet2 - Numbers', 'Sheet3 - Formulas', 'Sheet4 - Dates']