def _convert_to_style(cls, style_dict):
"""
converts a style_dict to an openpyxl style object
Parameters
----------
style_dict: style dictionary to convert
"""
from openpyxl.style import Style
xls_style = Style()
for key, value in style_dict.items():
for nk, nv in value.items():
if key == "borders":
(xls_style.borders.__getattribute__(nk)
.__setattr__('border_style', nv))
else:
xls_style.__getattribute__(key).__setattr__(nk, nv)
return xls_style
def __init__(self, optimized_write=False):
self.worksheets = []
self._active_sheet_index = 0
self._named_ranges = []
self.properties = DocumentProperties()
self.style = Style()
self.security = DocumentSecurity()
self.__optimized_write = optimized_write
self.__optimized_read = False
self.strings_table_builder = StringTableBuilder()
if not optimized_write:
self.worksheets.append(Worksheet(self))
def test_read_standalone_worksheet():
class DummyWb(object):
def get_sheet_by_name(self, value):
return None
path = os.path.join(DATADIR, 'reader', 'sheet2.xml')
with open(path) as handle:
ws = read_worksheet(handle.read(), DummyWb(), 'Sheet 2', {1: 'hello'},
{1: Style()})
assert isinstance(ws, Worksheet)
eq_(ws.cell('G5').value, 'hello')
eq_(ws.cell('D30').value, 30)
eq_(ws.cell('K9').value, 0.09)
def _convert_to_style(cls, style_dict):
"""
Converts a style_dict to an openpyxl style object.
Parameters
----------
style_dict : style dictionary to convert
"""
from openpyxl.style import Style
xls_style = Style()
for key, value in style_dict.items():
for nk, nv in value.items():
if key == "borders":
(
xls_style.borders.__getattribute__(nk).__setattr__(
"border_style", nv
)
)
else:
xls_style.__getattribute__(key).__setattr__(nk, nv)
return xls_style
def __init__(self, optimized_write=False, encoding='utf-8'):
self.worksheets = []
self._active_sheet_index = 0
self._named_ranges = []
self.properties = DocumentProperties()
self.style = Style()
self.security = DocumentSecurity()
self.__optimized_write = optimized_write
self.__optimized_read = False
self.__thread_local_data = threading.local()
self.strings_table_builder = StringTableBuilder()
self.loaded_theme = None
self.encoding = encoding
if not optimized_write:
self.worksheets.append(Worksheet(self))
def read_style_table(xml_source):
"""Read styles from the shared style table"""
table = {}
xmlns = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
root = fromstring(xml_source)
custom_num_formats = parse_custom_num_formats(root, xmlns)
builtin_formats = NumberFormat._BUILTIN_FORMATS
cell_xfs = root.find(QName(xmlns, 'cellXfs').text)
cell_xfs_nodes = cell_xfs.findall(QName(xmlns, 'xf').text)
for index, cell_xfs_node in enumerate(cell_xfs_nodes):
new_style = Style()
number_format_id = int(cell_xfs_node.get('numFmtId'))
if number_format_id < 164:
new_style.number_format.format_code = \
builtin_formats[number_format_id]
else:
new_style.number_format.format_code = \
custom_num_formats[number_format_id]
table[index] = new_style
return table
def test_read_standalone_worksheet():
class DummyWb(object):
encoding = 'utf-8'
excel_base_date = CALENDAR_WINDOWS_1900
def get_sheet_by_name(self, value):
return None
path = os.path.join(DATADIR, 'reader', 'sheet2.xml')
ws = None
handle = open(path)
try:
ws = read_worksheet(handle.read(), DummyWb(), 'Sheet 2', {1: 'hello'},
{1: Style()})
finally:
handle.close()
assert isinstance(ws, Worksheet)
eq_(ws.cell('G5').value, 'hello')
eq_(ws.cell('D30').value, 30)
eq_(ws.cell('K9').value, 0.09)
def read_style_table(xml_source):
"""Read styles from the shared style table"""
table = {}
xmlns = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
root = fromstring(xml_source)
custom_num_formats = parse_custom_num_formats(root, xmlns)
color_index = parse_color_index(root, xmlns)
font_list = parse_fonts(root, xmlns, color_index)
fill_list = parse_fills(root, xmlns, color_index)
border_list = parse_borders(root, xmlns, color_index)
builtin_formats = NumberFormat._BUILTIN_FORMATS
cell_xfs = root.find(QName(xmlns, 'cellXfs').text)
if cell_xfs is not None: # can happen on bad OOXML writers (e.g. Gnumeric)
cell_xfs_nodes = cell_xfs.findall(QName(xmlns, 'xf').text)
for index, cell_xfs_node in enumerate(cell_xfs_nodes):
new_style = Style()
number_format_id = int(cell_xfs_node.get('numFmtId'))
if number_format_id < 164:
new_style.number_format.format_code = \
builtin_formats.get(number_format_id, 'General')
else:
if number_format_id in custom_num_formats:
new_style.number_format.format_code = \
custom_num_formats[number_format_id]
else:
raise MissingNumberFormat('%s' % number_format_id)
if cell_xfs_node.get('applyAlignment') == '1':
alignment = cell_xfs_node.find(QName(xmlns, 'alignment').text)
if alignment is not None:
if alignment.get('horizontal') is not None:
new_style.alignment.horizontal = alignment.get(
'horizontal')
if alignment.get('vertical') is not None:
new_style.alignment.vertical = alignment.get(
'vertical')
if alignment.get('wrapText'):
new_style.alignment.wrap_text = True
if alignment.get('shrinkToFit'):
new_style.alignment.shrink_to_fit = True
if alignment.get('indent') is not None:
new_style.alignment.ident = int(
alignment.get('indent'))
if alignment.get('textRotation') is not None:
new_style.alignment.text_rotation = int(
alignment.get('textRotation'))
# ignore justifyLastLine option when horizontal = distributed
if cell_xfs_node.get('applyFont') == '1':
new_style.font = deepcopy(font_list[int(
cell_xfs_node.get('fontId'))])
new_style.font.color = deepcopy(font_list[int(
cell_xfs_node.get('fontId'))].color)
if cell_xfs_node.get('applyFill') == '1':
new_style.fill = deepcopy(fill_list[int(
cell_xfs_node.get('fillId'))])
new_style.fill.start_color = deepcopy(fill_list[int(
cell_xfs_node.get('fillId'))].start_color)
new_style.fill.end_color = deepcopy(fill_list[int(
cell_xfs_node.get('fillId'))].end_color)
if cell_xfs_node.get('applyBorder') == '1':
new_style.borders = deepcopy(border_list[int(
cell_xfs_node.get('borderId'))])
new_style.borders.left = deepcopy(border_list[int(
cell_xfs_node.get('borderId'))].left)
new_style.borders.left.color = deepcopy(border_list[int(
cell_xfs_node.get('borderId'))].left.color)
new_style.borders.right = deepcopy(border_list[int(
cell_xfs_node.get('borderId'))].right)
new_style.borders.right.color = deepcopy(border_list[int(
cell_xfs_node.get('borderId'))].right.color)
new_style.borders.top = deepcopy(border_list[int(
cell_xfs_node.get('borderId'))].top)
new_style.borders.top.color = deepcopy(border_list[int(
cell_xfs_node.get('borderId'))].top.color)
new_style.borders.bottom = deepcopy(border_list[int(
cell_xfs_node.get('borderId'))].bottom)
new_style.borders.bottom.color = deepcopy(border_list[int(
cell_xfs_node.get('borderId'))].bottom.color)
new_style.borders.diagonal = deepcopy(border_list[int(
cell_xfs_node.get('borderId'))].diagonal)
new_style.borders.diagonal.color = deepcopy(border_list[int(
cell_xfs_node.get('borderId'))].diagonal.color)
if cell_xfs_node.get('applyProtection') == '1':
protection = cell_xfs_node.find(
QName(xmlns, 'protection').text)
# Ignore if there are no protection sub-nodes
if protection is not None:
if protection.get('locked') is not None:
if protection.get('locked') == '1':
new_style.protection.locked = Protection.PROTECTION_PROTECTED
else:
new_style.protection.locked = Protection.PROTECTION_UNPROTECTED
if protection.get('hidden') is not None:
if protection.get('hidden') == '1':
new_style.protection.hidden = Protection.PROTECTION_PROTECTED
else:
new_style.protection.hidden = Protection.PROTECTION_UNPROTECTED
table[index] = new_style
return table
'style': '0'
},
'formula': {
'type': Cell.TYPE_FORMULA,
'style': '0'
},
'boolean': {
'type': Cell.TYPE_BOOL,
'style': '0'
},
}
DESCRIPTORS_CACHE_SIZE = 50
DESCRIPTORS_CACHE = OrderedDict()
DATETIME_STYLE = Style()
DATETIME_STYLE.number_format.format_code = 'yyyy-mm-dd h:mm'
DATE_STYLE = Style()
DATE_STYLE.number_format.format_code = 'yyyy-mm-dd'
BOUNDING_BOX_PLACEHOLDER = 'A1:%s%d' % (get_column_letter(MAX_COLUMN), MAX_ROW)
def create_temporary_file(suffix=''):
fobj = NamedTemporaryFile(mode='w+',
suffix=suffix,
prefix='openpyxl.',
delete=False)
filename = fobj.name
def get_style(self, coordinate):
"""Return the style object for the specified cell."""
if not coordinate in self._styles:
self._styles[coordinate] = Style()
return self._styles[coordinate]
def read_style_table(xml_source):
"""Read styles from the shared style table"""
table = {}
xmlns = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
root = fromstring(xml_source)
custom_num_formats = parse_custom_num_formats(root, xmlns)
color_index = parse_color_index(root, xmlns)
font_list = parse_fonts(root, xmlns, color_index)
fill_list = parse_fills(root, xmlns, color_index)
border_list = parse_borders(root, xmlns, color_index)
builtin_formats = NumberFormat._BUILTIN_FORMATS
cell_xfs = root.find(QName(xmlns, 'cellXfs').text)
if cell_xfs is not None: # can happen on bad OOXML writers (e.g. Gnumeric)
cell_xfs_nodes = cell_xfs.findall(QName(xmlns, 'xf').text)
for index, cell_xfs_node in enumerate(cell_xfs_nodes):
new_style = Style()
number_format_id = int(cell_xfs_node.get('numFmtId'))
if number_format_id < 164:
new_style.number_format.format_code = \
builtin_formats.get(number_format_id, 'General')
else:
if number_format_id in custom_num_formats:
new_style.number_format.format_code = \
custom_num_formats[number_format_id]
else:
raise MissingNumberFormat('%s' % number_format_id)
if cell_xfs_node.get('applyAlignment') == '1':
alignment = cell_xfs_node.find(QName(xmlns, 'alignment').text)
if alignment is not None:
if alignment.get('horizontal') is not None:
new_style.alignment.horizontal = alignment.get('horizontal')
if alignment.get('vertical') is not None:
new_style.alignment.vertical = alignment.get('vertical')
if alignment.get('wrapText'):
new_style.alignment.wrap_text = True
if alignment.get('shrinkToFit'):
new_style.alignment.shrink_to_fit = True
if alignment.get('indent') is not None:
new_style.alignment.ident = int(alignment.get('indent'))
if alignment.get('textRotation') is not None:
new_style.alignment.text_rotation = int(alignment.get('textRotation'))
# ignore justifyLastLine option when horizontal = distributed
if cell_xfs_node.get('applyFont') == '1':
new_style.font = deepcopy(font_list[int(cell_xfs_node.get('fontId'))])
new_style.font.color = deepcopy(font_list[int(cell_xfs_node.get('fontId'))].color)
if cell_xfs_node.get('applyFill') == '1':
new_style.fill = deepcopy(fill_list[int(cell_xfs_node.get('fillId'))])
new_style.fill.start_color = deepcopy(fill_list[int(cell_xfs_node.get('fillId'))].start_color)
new_style.fill.end_color = deepcopy(fill_list[int(cell_xfs_node.get('fillId'))].end_color)
if cell_xfs_node.get('applyBorder') == '1':
new_style.borders = deepcopy(border_list[int(cell_xfs_node.get('borderId'))])
new_style.borders.left = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].left)
new_style.borders.left.color = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].left.color)
new_style.borders.right = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].right)
new_style.borders.right.color = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].right.color)
new_style.borders.top = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].top)
new_style.borders.top.color = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].top.color)
new_style.borders.bottom = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].bottom)
new_style.borders.bottom.color = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].bottom.color)
new_style.borders.diagonal = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].diagonal)
new_style.borders.diagonal.color = deepcopy(border_list[int(cell_xfs_node.get('borderId'))].diagonal.color)
if cell_xfs_node.get('applyProtection') == '1':
protection = cell_xfs_node.find(QName(xmlns, 'protection').text)
# Ignore if there are no protection sub-nodes
if protection is not None:
if protection.get('locked') is not None:
if protection.get('locked') == '1':
new_style.protection.locked = Protection.PROTECTION_PROTECTED
else:
new_style.protection.locked = Protection.PROTECTION_UNPROTECTED
if protection.get('hidden') is not None:
if protection.get('hidden') == '1':
new_style.protection.hidden = Protection.PROTECTION_PROTECTED
else:
new_style.protection.hidden = Protection.PROTECTION_UNPROTECTED
table[index] = new_style
return table
def read_style_table(xml_source):
"""Read styles from the shared style table"""
style_prop = {"table": {}}
root = fromstring(xml_source)
custom_num_formats = parse_custom_num_formats(root)
style_prop["color_index"] = parse_color_index(root)
font_list = parse_fonts(root, style_prop["color_index"])
fill_list = parse_fills(root, style_prop["color_index"])
border_list = parse_borders(root, style_prop["color_index"])
style_prop["dxf_list"] = parse_dxfs(root, style_prop["color_index"])
builtin_formats = NumberFormat._BUILTIN_FORMATS
cell_xfs = root.find("{%s}cellXfs" % SHEET_MAIN_NS)
if cell_xfs is not None: # can happen on bad OOXML writers (e.g. Gnumeric)
cell_xfs_nodes = cell_xfs.findall("{%s}xf" % SHEET_MAIN_NS)
for index, cell_xfs_node in enumerate(cell_xfs_nodes):
new_style = Style(static=True)
number_format_id = int(cell_xfs_node.get("numFmtId"))
if number_format_id < 164:
new_style.number_format.format_code = builtin_formats.get(number_format_id, "General")
else:
if number_format_id in custom_num_formats:
new_style.number_format.format_code = custom_num_formats[number_format_id]
else:
raise MissingNumberFormat("%s" % number_format_id)
if cell_xfs_node.get("applyAlignment") == "1":
alignment = cell_xfs_node.find("{%s}alignment" % SHEET_MAIN_NS)
if alignment is not None:
if alignment.get("horizontal") is not None:
new_style.alignment.horizontal = alignment.get("horizontal")
if alignment.get("vertical") is not None:
new_style.alignment.vertical = alignment.get("vertical")
if alignment.get("wrapText"):
new_style.alignment.wrap_text = True
if alignment.get("shrinkToFit"):
new_style.alignment.shrink_to_fit = True
if alignment.get("indent") is not None:
new_style.alignment.ident = int(alignment.get("indent"))
if alignment.get("textRotation") is not None:
new_style.alignment.text_rotation = int(alignment.get("textRotation"))
# ignore justifyLastLine option when horizontal = distributed
if cell_xfs_node.get("applyFont") == "1":
new_style.font = deepcopy(font_list[int(cell_xfs_node.get("fontId"))])
new_style.font.color = deepcopy(font_list[int(cell_xfs_node.get("fontId"))].color)
if cell_xfs_node.get("applyFill") == "1":
new_style.fill = deepcopy(fill_list[int(cell_xfs_node.get("fillId"))])
new_style.fill.start_color = deepcopy(fill_list[int(cell_xfs_node.get("fillId"))].start_color)
new_style.fill.end_color = deepcopy(fill_list[int(cell_xfs_node.get("fillId"))].end_color)
if cell_xfs_node.get("applyBorder") == "1":
new_style.borders = deepcopy(border_list[int(cell_xfs_node.get("borderId"))])
new_style.borders.left = deepcopy(border_list[int(cell_xfs_node.get("borderId"))].left)
new_style.borders.left.color = deepcopy(border_list[int(cell_xfs_node.get("borderId"))].left.color)
new_style.borders.right = deepcopy(border_list[int(cell_xfs_node.get("borderId"))].right)
new_style.borders.right.color = deepcopy(border_list[int(cell_xfs_node.get("borderId"))].right.color)
new_style.borders.top = deepcopy(border_list[int(cell_xfs_node.get("borderId"))].top)
new_style.borders.top.color = deepcopy(border_list[int(cell_xfs_node.get("borderId"))].top.color)
new_style.borders.bottom = deepcopy(border_list[int(cell_xfs_node.get("borderId"))].bottom)
new_style.borders.bottom.color = deepcopy(border_list[int(cell_xfs_node.get("borderId"))].bottom.color)
new_style.borders.diagonal = deepcopy(border_list[int(cell_xfs_node.get("borderId"))].diagonal)
new_style.borders.diagonal.color = deepcopy(
border_list[int(cell_xfs_node.get("borderId"))].diagonal.color
)
if cell_xfs_node.get("applyProtection") == "1":
protection = cell_xfs_node.find("{%s}protection" % SHEET_MAIN_NS)
# Ignore if there are no protection sub-nodes
if protection is not None:
if protection.get("locked") is not None:
if protection.get("locked") == "1":
new_style.protection.locked = Protection.PROTECTION_PROTECTED
else:
new_style.protection.locked = Protection.PROTECTION_UNPROTECTED
if protection.get("hidden") is not None:
if protection.get("hidden") == "1":
new_style.protection.hidden = Protection.PROTECTION_PROTECTED
else:
new_style.protection.hidden = Protection.PROTECTION_UNPROTECTED
style_prop["table"][index] = new_style
return style_prop
