def test_read_cell_style(datadir, StyleReader):
datadir.chdir()
with open("empty-workbook-styles.xml") as content:
reader = StyleReader(content.read())
reader.parse()
styles = reader.cell_styles
assert len(styles) == 2
assert reader.cell_styles[0] == StyleArray()
assert reader.cell_styles[1] == StyleArray([0,0,0,9,0,0,0,0,1])
def WorkSheetParser():
"""Setup a parser instance with an empty source"""
from .._reader import WorkSheetParser
styles = IndexedList()
for i in range(29):
styles.add((StyleArray([i]*9)))
styles.add(StyleArray([0,4,6,14,0,1,0,0,0])) #fillId=4, borderId=6, number_format=14 alignmentId=1))
date_formats = set([1, 29])
return WorkSheetParser(None, {0:'a'}, date_formats=date_formats)
def test_read_xf_no_number_format(datadir, StyleReader):
datadir.chdir()
with open("no_number_format.xml") as src:
reader = StyleReader(src.read())
from openpyxl.styles import Font
reader.font_list = [Font(), Font()]
reader.parse_cell_styles()
styles = reader.cell_styles
assert len(styles) == 3
assert styles[0] == StyleArray()
assert styles[1] == StyleArray([1,0,1,0,0,0,0,0,0])
assert styles[2] == StyleArray([0,0,0,14,0,0,0,0,0])
def test_unprotected_cell(StyleReader, datadir):
datadir.chdir()
with open ("worksheet_unprotected_style.xml") as src:
reader = StyleReader(src.read())
from openpyxl.styles import Font
reader.font_list = IndexedList([Font(), Font(), Font(), Font(), Font()])
reader.protections = IndexedList([Protection()])
reader.parse_cell_styles()
styles = reader.cell_styles
assert len(styles) == 3
# default is cells are locked
assert styles[0] == StyleArray()
assert styles[1] == StyleArray([4,0,0,0,0,0,0,0,0])
assert styles[2] == StyleArray([3,0,0,0,1,0,0,0,0])
def _setup_styles(self):
"""Bootstrap styles"""
from openpyxl.styles.alignment import Alignment
from openpyxl.styles.borders import DEFAULT_BORDER
from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL
from openpyxl.styles.fonts import DEFAULT_FONT
from openpyxl.styles.protection import Protection
from openpyxl.styles.colors import COLOR_INDEX
self._fonts = IndexedList()
self._fonts.add(DEFAULT_FONT)
self._alignments = IndexedList([Alignment()])
self._borders = IndexedList()
self._borders.add(DEFAULT_BORDER)
self._fills = IndexedList()
self._fills.add(DEFAULT_EMPTY_FILL)
self._fills.add(DEFAULT_GRAY_FILL)
self._number_formats = IndexedList()
self._protections = IndexedList([Protection()])
self._colors = COLOR_INDEX
self._cell_styles = IndexedList([StyleArray()])
self._named_styles = {'Normal': NamedStyle(font=DEFAULT_FONT)}
def __init__(self):
self._differential_styles = []
self.shared_strings = IndexedList()
self.shared_strings.add("hello world")
self._fonts = IndexedList()
self._fills = IndexedList()
self._number_formats = IndexedList()
self._borders = IndexedList()
self._alignments = IndexedList()
self._protections = IndexedList()
self._cell_styles = IndexedList()
self.vba_archive = None
for i in range(29):
self._cell_styles.add((StyleArray([i]*9)))
self._cell_styles.add(StyleArray([0,4,6,0,0,1,0,0,0])) #fillId=4, borderId=6, alignmentId=1))
self.sheetnames = []
def _parse_xfs(self, node):
"""Read styles from the shared style table"""
_style_ids = []
xfs = safe_iterator(node, '{%s}xf' % SHEET_MAIN_NS)
for xf in xfs:
style = StyleArray.from_tree(xf)
al = xf.find('{%s}alignment' % SHEET_MAIN_NS)
if al is not None:
alignment = Alignment(**al.attrib)
style.alignmentId = self.alignments.add(alignment)
prot = xf.find('{%s}protection' % SHEET_MAIN_NS)
if prot is not None:
protection = Protection(**prot.attrib)
style.protectionId = self.protections.add(protection)
numFmtId = int(xf.get("numFmtId", 0))
# check for custom formats and normalise indices
if numFmtId in self.custom_number_formats:
format_code = self.custom_number_formats[numFmtId]
style.numFmtId = self.number_formats.add(format_code) + 164
_style_ids.append(style)
return IndexedList(_style_ids)
def DummyCell(dummy_sheet):
dummy_sheet.parent._number_formats.add('d-mmm-yy')
style = StyleArray([0, 0, 0, 164, 0, 0, 0, 0, 0])
dummy_sheet.parent._cell_styles.add(style)
cell = ReadOnlyCell(dummy_sheet, None, None, "23596", 'n', 1)
return cell
def _parse_xfs(self, node):
"""Read styles from the shared style table"""
_style_ids = []
xfs = safe_iterator(node, '{%s}xf' % SHEET_MAIN_NS)
for xf in xfs:
style = StyleArray.from_tree(xf)
al = xf.find('{%s}alignment' % SHEET_MAIN_NS)
if al is not None:
alignment = Alignment(**al.attrib)
style.alignmentId = self.alignments.add(alignment)
prot = xf.find('{%s}protection' % SHEET_MAIN_NS)
if prot is not None:
protection = Protection(**prot.attrib)
style.protectionId = self.protections.add(protection)
numFmtId = int(xf.get("numFmtId", 0))
# check for custom formats and normalise indices
if numFmtId in self.custom_number_formats:
format_code = self.custom_number_formats[numFmtId]
style.numFmtId = self.number_formats.add(format_code) + 164
_style_ids.append(style)
return IndexedList(_style_ids)
def test_read_complex_style_mappings(datadir, StyleReader):
datadir.chdir()
with open("complex-styles.xml") as content:
reader = StyleReader(content.read())
reader.parse()
styles = reader.cell_styles
assert len(styles) == 29
assert styles[-1] == StyleArray([6,5,0,0,0,0,0,0,0])
def __init__(self):
self.shared_strings = IndexedList()
self._cell_styles = IndexedList(
[StyleArray([0, 0, 0, 0, 0, 0, 0, 0, 0])])
self._number_formats = IndexedList()
self.encoding = "UTF-8"
self.excel_base_date = CALENDAR_WINDOWS_1900
self.sheetnames = []
class DummyWorkbook(object):
shared_styles = IndexedList()
style = StyleArray()
shared_styles.add(style) # Workbooks always have a default style
_cell_styles = IndexedList()
_cell_styles.add(style)
_number_formats = IndexedList()
_fonts = IndexedList()
_fonts.add(None)
class Workbook:
epoch = None
_cell_styles = [StyleArray([0, 0, 0, 0, 0, 0, 0, 0, 0])]
data_only = False
def __init__(self):
self.sheetnames = []
self._archive = ZipFile(BytesIO(), "w")
self._date_formats = set()
def __init__(self):
self._differential_styles = [DifferentialStyle()] * 5
self.shared_strings = ['a'] * 30
self._fonts = IndexedList()
self._fills = IndexedList()
self._number_formats = IndexedList()
self._borders = IndexedList([DEFAULT_BORDER] * 30)
self._alignments = IndexedList()
self._protections = IndexedList()
self._cell_styles = IndexedList()
self.vba_archive = None
for i in range(29):
self._cell_styles.add((StyleArray([i] * 9)))
self._cell_styles.add(StyleArray(
[0, 4, 6, 0, 0, 1, 0, 0,
0])) #fillId=4, borderId=6, alignmentId=1))
self.sheetnames = []
self._date_formats = set()
def __init__(self):
self._differential_styles = []
self.shared_strings = IndexedList()
self._fonts = IndexedList()
self._fills = IndexedList()
self._number_formats = IndexedList()
self._borders = IndexedList()
self._alignments = IndexedList()
self._protections = IndexedList()
self._cell_styles = IndexedList()
self.vba_archive = None
self._cell_styles.add(StyleArray([0, 0, 0, 14, 0, 0, 0, 0, 0]))
self.sheetnames = []
def test_alignment(datadir, StyleReader):
datadir.chdir()
with open("alignment_styles.xml") as src:
reader = StyleReader(src.read())
reader.parse_cell_styles()
styles = reader.cell_styles
assert len(styles) == 3
assert styles[2] == StyleArray([0,0,0,0,0,2,0,0,0])
assert reader.alignments == [
Alignment(),
Alignment(textRotation=180),
Alignment(vertical='top', textRotation=255),
]
def test_assign_number_formats(StyleReader):
reader = StyleReader("<root />")
reader.custom_number_formats = {43:'_ * #,##0.00_ ;_ * \-#,##0.00_ ;_ * "-"??_ ;_ @_ '}
reader.number_formats = IndexedList(['_ * #,##0.00_ ;_ * \-#,##0.00_ ;_ * "-"??_ ;_ @_ '])
node = fromstring("""
<xf xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
numFmtId="43" fontId="2" fillId="0" borderId="0"
applyFont="0" applyFill="0" applyBorder="0" applyAlignment="0" applyProtection="0">
<alignment vertical="center"/>
</xf>
""")
styles = reader._parse_xfs(node)
assert styles[0] == StyleArray([2, 0, 0, 164, 0, 1, 0, 0, 0]) #StyleId(numFmtId=164, fontId=2, alignmentId=1)
def __init__(self):
self.shared_styles = IndexedList()
self._cell_styles = IndexedList()
self._cell_styles.add(StyleArray())
self._cell_styles.add(StyleArray([10, 0, 0, 0, 0, 0, 0, 0, 0, 0]))
self.sheetnames = []
class Workbook:
excel_base_date = None
_cell_styles = [StyleArray([0, 0, 0, 0, 0, 0, 0, 0, 0])]
def __init__(self):
self.sheetnames = []
def test_style_array(self, dummy_sheet):
cell = ReadOnlyCell(dummy_sheet, None, None, None)
assert cell.style_array == StyleArray()
class Workbook:
epoch = None
_cell_styles = [StyleArray([0, 0, 0, 0, 0, 0, 0, 0, 0])]
def __init__(self):
self.sheetnames = []
import time
from openpyxl.styles.styleable import StyleArray
from memory_profiler import memory_usage
ids = []
start = time.clock()
for i in range(1000000):
if i % 100000 == 0:
print(i)
ids.append(StyleArray())
use = memory_usage(proc=-1, interval=1)[0]
print("Memory use %s" % use)
print("Time = %d seconds" % (time.clock() - start))