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