def get_xlsx(self, data, book, sheet): try: wb = load_workbook(r'./' + book + '.xlsx') except: wb = Workbook() try: wb.remove_sheet(wb.get_sheet_by_name("Sheet")) except: pass try: ws = wb.create_sheet(sheet) except: self.text.insert(1.0, '工作表名不能重复') try: rows = len(data) cols = len(data[0]) i = 1 for rx in range(rows): for cx in range(cols): ws.cell(row=rx + 1, column=cx + 1).value = data[rx][cx] wb.save(filename=r'./' + book + '.xlsx') self.text.insert( 1.0, '\n' + book + '.' + sheet + '(收到' + str(rows - 1) + '条数据)') except: wb.save(filename=r'./' + book + '.xlsx') self.text.insert(1.0, '\n' + book + '.' + sheet + '(收到0条数据)') return 0
def open_xls_as_xlsx(filename): # first open using xlrd book_input = xlrd.open_workbook(filename) book_output = Workbook() book_output.remove_sheet(book_output.active) for sheet_in in book_input.sheets(): nrows = sheet_in.nrows ncols = sheet_in.ncols name = sheet_in.name sheet_out = book_output.create_sheet() sheet_out.title = name for row in xrange(0, nrows): for col in xrange(0, ncols): colLetter = chr(ord('A')+col) currCell = colLetter + repr(row+1) sheet_out[currCell] = sheet_in.cell_value(row, col) return book_output
class XlReport: """ Wrapper for openpyxl Using xlsx because xls has limitations and ods has no good python library """ def __init__(self, file_name="Report"): """ file_name does not need an extention """ if file_name.endswith('.xls'): file_name = file_name[:-4] elif file_name.endswith('xlsx'): file_name = file_name[:-5] file_name = file_name.replace( ' ', '_') # Some browsers don't deal well with spaces in downloads self.workbook = Workbook() self.workbook.remove_sheet(self.workbook.get_active_sheet()) self.file_name = file_name def add_sheet(self, data, title=None, header_row=None, heading=None, auto_width=False, max_auto_width=50): """ Add a sheet with data to workbook title: sheet name header_row: List - Column header (bold with bottom border) heading: Sheet heading (very top of sheet) auto_width: will ESTIMATE the width of each column by counting max chars in each column. It will not work with a formula. max_auto_width: is the max number of characters a column to be """ sheet = self.workbook.create_sheet() if title: sheet.title = unicode(title) if heading: sheet.append([unicode(heading)]) if header_row: header_row = map(unicode, header_row) sheet.append(header_row) row = sheet.get_highest_row() for i, header_cell in enumerate(header_row): cell = sheet.cell(row=row - 1, column=i) cell.style.font.bold = True cell.style.borders.bottom.border_style = openpyxl.style.Border.BORDER_THIN for row in data: row = map(unicode, row) sheet.append(row) if auto_width: column_widths = [] for row in data: row = map(unicode, row) for i, cell in enumerate(row): if len(column_widths) > i: if len(cell) > column_widths[i]: column_widths[i] = len(cell) else: column_widths += [len(cell)] for i, column_width in enumerate(column_widths): if column_width > 3: if column_width < max_auto_width: # * 0.9 estimates a typical variable width font sheet.column_dimensions[get_column_letter( i + 1)].width = column_width * 0.9 else: sheet.column_dimensions[get_column_letter( i + 1)].width = max_auto_width def save(self, filename): self.workbook.save(settings.MEDIA_ROOT + filename) def as_download(self): """ Returns a django HttpResponse with the xlsx file """ myfile = StringIO.StringIO() myfile.write(save_virtual_workbook(self.workbook)) response = HttpResponse( myfile.getvalue(), content_type= 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response[ 'Content-Disposition'] = 'attachment; filename=%s.xlsx' % self.file_name response['Content-Length'] = myfile.tell() return response
class _OpenpyxlWriter(ExcelWriter): engine = 'openpyxl' supported_extensions = ('.xlsx', '.xlsm') def __init__(self, path, **engine_kwargs): # Use the openpyxl module as the Excel writer. from openpyxl.workbook import Workbook super(_OpenpyxlWriter, self).__init__(path, **engine_kwargs) # Create workbook object with default optimized_write=True. self.book = Workbook() # Openpyxl 1.6.1 adds a dummy sheet. We remove it. if self.book.worksheets: self.book.remove_sheet(self.book.worksheets[0]) def save(self): """ Save workbook to disk. """ return self.book.save(self.path) def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0): # Write the frame cells using openpyxl. from openpyxl.cell import get_column_letter sheet_name = self._get_sheet_name(sheet_name) if sheet_name in self.sheets: wks = self.sheets[sheet_name] else: wks = self.book.create_sheet() wks.title = sheet_name self.sheets[sheet_name] = wks for cell in cells: colletter = get_column_letter(startcol + cell.col + 1) xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1)) xcell.value = _conv_value(cell.val) if cell.style: style = self._convert_to_style(cell.style) for field in style.__fields__: xcell.style.__setattr__(field, style.__getattribute__(field)) if isinstance(cell.val, datetime.datetime): xcell.style.number_format.format_code = "YYYY-MM-DD HH:MM:SS" elif isinstance(cell.val, datetime.date): xcell.style.number_format.format_code = "YYYY-MM-DD" # merging requires openpyxl latest (works on 1.6.1) # todo add version check if cell.mergestart is not None and cell.mergeend is not None: cletterstart = get_column_letter(startcol + cell.col + 1) cletterend = get_column_letter(startcol + cell.mergeend + 1) wks.merge_cells('%s%s:%s%s' % (cletterstart, startrow + cell.row + 1, cletterend, startrow + cell.mergestart + 1)) @classmethod 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
class _OpenpyxlWriter(ExcelWriter): engine = "openpyxl" supported_extensions = (".xlsx", ".xlsm") def __init__(self, path, engine=None, mode="w", **engine_kwargs): # Use the openpyxl module as the Excel writer. from openpyxl.workbook import Workbook super().__init__(path, mode=mode, **engine_kwargs) if self.mode == "a": # Load from existing workbook from openpyxl import load_workbook book = load_workbook(self.path) self.book = book else: # Create workbook object with default optimized_write=True. self.book = Workbook() if self.book.worksheets: try: self.book.remove(self.book.worksheets[0]) except AttributeError: # compat - for openpyxl <= 2.4 self.book.remove_sheet(self.book.worksheets[0]) def save(self): """ Save workbook to disk. """ return self.book.save(self.path) @classmethod 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 @classmethod def _convert_to_style_kwargs(cls, style_dict): """ Convert a style_dict to a set of kwargs suitable for initializing or updating-on-copy an openpyxl v2 style object. Parameters ---------- style_dict : dict A dict with zero or more of the following keys (or their synonyms). 'font' 'fill' 'border' ('borders') 'alignment' 'number_format' 'protection' Returns ------- style_kwargs : dict A dict with the same, normalized keys as ``style_dict`` but each value has been replaced with a native openpyxl style object of the appropriate class. """ _style_key_map = {"borders": "border"} style_kwargs = {} for k, v in style_dict.items(): if k in _style_key_map: k = _style_key_map[k] _conv_to_x = getattr(cls, f"_convert_to_{k}", lambda x: None) new_v = _conv_to_x(v) if new_v: style_kwargs[k] = new_v return style_kwargs @classmethod def _convert_to_color(cls, color_spec): """ Convert ``color_spec`` to an openpyxl v2 Color object. Parameters ---------- color_spec : str, dict A 32-bit ARGB hex string, or a dict with zero or more of the following keys. 'rgb' 'indexed' 'auto' 'theme' 'tint' 'index' 'type' Returns ------- color : openpyxl.styles.Color """ from openpyxl.styles import Color if isinstance(color_spec, str): return Color(color_spec) else: return Color(**color_spec) @classmethod def _convert_to_font(cls, font_dict): """ Convert ``font_dict`` to an openpyxl v2 Font object. Parameters ---------- font_dict : dict A dict with zero or more of the following keys (or their synonyms). 'name' 'size' ('sz') 'bold' ('b') 'italic' ('i') 'underline' ('u') 'strikethrough' ('strike') 'color' 'vertAlign' ('vertalign') 'charset' 'scheme' 'family' 'outline' 'shadow' 'condense' Returns ------- font : openpyxl.styles.Font """ from openpyxl.styles import Font _font_key_map = { "sz": "size", "b": "bold", "i": "italic", "u": "underline", "strike": "strikethrough", "vertalign": "vertAlign", } font_kwargs = {} for k, v in font_dict.items(): if k in _font_key_map: k = _font_key_map[k] if k == "color": v = cls._convert_to_color(v) font_kwargs[k] = v return Font(**font_kwargs) @classmethod def _convert_to_stop(cls, stop_seq): """ Convert ``stop_seq`` to a list of openpyxl v2 Color objects, suitable for initializing the ``GradientFill`` ``stop`` parameter. Parameters ---------- stop_seq : iterable An iterable that yields objects suitable for consumption by ``_convert_to_color``. Returns ------- stop : list of openpyxl.styles.Color """ return map(cls._convert_to_color, stop_seq) @classmethod def _convert_to_fill(cls, fill_dict): """ Convert ``fill_dict`` to an openpyxl v2 Fill object. Parameters ---------- fill_dict : dict A dict with one or more of the following keys (or their synonyms), 'fill_type' ('patternType', 'patterntype') 'start_color' ('fgColor', 'fgcolor') 'end_color' ('bgColor', 'bgcolor') or one or more of the following keys (or their synonyms). 'type' ('fill_type') 'degree' 'left' 'right' 'top' 'bottom' 'stop' Returns ------- fill : openpyxl.styles.Fill """ from openpyxl.styles import PatternFill, GradientFill _pattern_fill_key_map = { "patternType": "fill_type", "patterntype": "fill_type", "fgColor": "start_color", "fgcolor": "start_color", "bgColor": "end_color", "bgcolor": "end_color", } _gradient_fill_key_map = {"fill_type": "type"} pfill_kwargs = {} gfill_kwargs = {} for k, v in fill_dict.items(): pk = gk = None if k in _pattern_fill_key_map: pk = _pattern_fill_key_map[k] if k in _gradient_fill_key_map: gk = _gradient_fill_key_map[k] if pk in ["start_color", "end_color"]: v = cls._convert_to_color(v) if gk == "stop": v = cls._convert_to_stop(v) if pk: pfill_kwargs[pk] = v elif gk: gfill_kwargs[gk] = v else: pfill_kwargs[k] = v gfill_kwargs[k] = v try: return PatternFill(**pfill_kwargs) except TypeError: return GradientFill(**gfill_kwargs) @classmethod def _convert_to_side(cls, side_spec): """ Convert ``side_spec`` to an openpyxl v2 Side object. Parameters ---------- side_spec : str, dict A string specifying the border style, or a dict with zero or more of the following keys (or their synonyms). 'style' ('border_style') 'color' Returns ------- side : openpyxl.styles.Side """ from openpyxl.styles import Side _side_key_map = {"border_style": "style"} if isinstance(side_spec, str): return Side(style=side_spec) side_kwargs = {} for k, v in side_spec.items(): if k in _side_key_map: k = _side_key_map[k] if k == "color": v = cls._convert_to_color(v) side_kwargs[k] = v return Side(**side_kwargs) @classmethod def _convert_to_border(cls, border_dict): """ Convert ``border_dict`` to an openpyxl v2 Border object. Parameters ---------- border_dict : dict A dict with zero or more of the following keys (or their synonyms). 'left' 'right' 'top' 'bottom' 'diagonal' 'diagonal_direction' 'vertical' 'horizontal' 'diagonalUp' ('diagonalup') 'diagonalDown' ('diagonaldown') 'outline' Returns ------- border : openpyxl.styles.Border """ from openpyxl.styles import Border _border_key_map = { "diagonalup": "diagonalUp", "diagonaldown": "diagonalDown" } border_kwargs = {} for k, v in border_dict.items(): if k in _border_key_map: k = _border_key_map[k] if k == "color": v = cls._convert_to_color(v) if k in ["left", "right", "top", "bottom", "diagonal"]: v = cls._convert_to_side(v) border_kwargs[k] = v return Border(**border_kwargs) @classmethod def _convert_to_alignment(cls, alignment_dict): """ Convert ``alignment_dict`` to an openpyxl v2 Alignment object. Parameters ---------- alignment_dict : dict A dict with zero or more of the following keys (or their synonyms). 'horizontal' 'vertical' 'text_rotation' 'wrap_text' 'shrink_to_fit' 'indent' Returns ------- alignment : openpyxl.styles.Alignment """ from openpyxl.styles import Alignment return Alignment(**alignment_dict) @classmethod def _convert_to_number_format(cls, number_format_dict): """ Convert ``number_format_dict`` to an openpyxl v2.1.0 number format initializer. Parameters ---------- number_format_dict : dict A dict with zero or more of the following keys. 'format_code' : str Returns ------- number_format : str """ return number_format_dict["format_code"] @classmethod def _convert_to_protection(cls, protection_dict): """ Convert ``protection_dict`` to an openpyxl v2 Protection object. Parameters ---------- protection_dict : dict A dict with zero or more of the following keys. 'locked' 'hidden' Returns ------- """ from openpyxl.styles import Protection return Protection(**protection_dict) def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0, freeze_panes=None): # Write the frame cells using openpyxl. sheet_name = self._get_sheet_name(sheet_name) _style_cache = {} if sheet_name in self.sheets: wks = self.sheets[sheet_name] else: wks = self.book.create_sheet() wks.title = sheet_name self.sheets[sheet_name] = wks if _validate_freeze_panes(freeze_panes): wks.freeze_panes = wks.cell(row=freeze_panes[0] + 1, column=freeze_panes[1] + 1) for cell in cells: xcell = wks.cell(row=startrow + cell.row + 1, column=startcol + cell.col + 1) xcell.value, fmt = self._value_with_fmt(cell.val) if fmt: xcell.number_format = fmt style_kwargs = {} if cell.style: key = str(cell.style) style_kwargs = _style_cache.get(key) if style_kwargs is None: style_kwargs = self._convert_to_style_kwargs(cell.style) _style_cache[key] = style_kwargs if style_kwargs: for k, v in style_kwargs.items(): setattr(xcell, k, v) if cell.mergestart is not None and cell.mergeend is not None: wks.merge_cells( start_row=startrow + cell.row + 1, start_column=startcol + cell.col + 1, end_column=startcol + cell.mergeend + 1, end_row=startrow + cell.mergestart + 1, ) # When cells are merged only the top-left cell is preserved # The behaviour of the other cells in a merged range is # undefined if style_kwargs: first_row = startrow + cell.row + 1 last_row = startrow + cell.mergestart + 1 first_col = startcol + cell.col + 1 last_col = startcol + cell.mergeend + 1 for row in range(first_row, last_row + 1): for col in range(first_col, last_col + 1): if row == first_row and col == first_col: # Ignore first cell. It is already handled. continue xcell = wks.cell(column=col, row=row) for k, v in style_kwargs.items(): setattr(xcell, k, v)
def print_mat_desag(MATA, MATB, incertitude, UP_list_desag, EF_list): from openpyxl.workbook import Workbook from openpyxl.worksheet import Worksheet from copy import copy from time import time from scipy.sparse import lil_matrix, find filename = "MAT.xlsx" wb = Workbook(encoding="mac_roman") Ws = wb.active wb.remove_sheet(Ws) # creation de la matrice A avec sa matrice d'incertitude Ws = Worksheet(wb, title="Processus") for UP in UP_list_desag: line = [UP, UP_list_desag[UP]["origine"]] line.append(UP_list_desag[UP]["process"]) line.append(str(UP_list_desag[UP]["enfants"])) Ws.append(line) wb.add_sheet(Ws) Ws = Worksheet(wb, title="A") Ws.freeze_panes = "B2" mat = MATA.toarray() header = ["A"] for UP in UP_list_desag: header.append(UP_list_desag[UP]["process"]) Ws.append(header) for row in range(len(mat)): line = [row] for col in range(len(mat[row])): line.append(mat[row][col]) Ws.append(line) wb.add_sheet(Ws) Ws = Worksheet(wb, title="incertitude A") Ws.freeze_panes = "B2" mat = incertitude["technology"] header = ["incertitude A"] for UP in UP_list_desag: header.append(UP_list_desag[UP]["process"]) Ws.append(header) for row in range(len(mat)): line = [row] for col in range(len(mat)): line.append(mat[col][row]) Ws.append(line) wb.add_sheet(Ws) # Creation de B avec sa matrice d'incertitude Ws = Worksheet(wb, title="Flux elem") Ws.freeze_panes = "A2" header = ["id", "compartment", "substance", "subcompartment"] Ws.append(header) for EF in EF_list: L = [EF_list.index(EF)] L.append(EF) Ws.append(L) wb.add_sheet(Ws) Ws = Worksheet(wb, title="B") Ws.freeze_panes = "B2" mat = MATB.toarray() header = ["B"] for UP in UP_list_desag: header.append(UP_list_desag[UP]["process"]) Ws.append(header) for row in range(len(mat)): line = [row] for col in range(len(mat[row])): line.append(mat[row][col]) Ws.append(line) wb.add_sheet(Ws) Ws = Worksheet(wb, title="incertitude B") Ws.freeze_panes = "B2" mat = incertitude["intervention"] header = ["incertitude B"] for UP in UP_list_desag: header.append(UP_list_desag[UP]["process"]) Ws.append(header) for row in range(len(mat[0])): line = [row] for col in range(len(mat)): line.append(mat[col][row]) Ws.append(line) wb.add_sheet(Ws) wb.save(filename) print "fichier enregistre : MAT.xlsx"
def test_remove_sheet(): wb = Workbook() new_sheet = wb.create_sheet(0) wb.remove_sheet(new_sheet) assert new_sheet not in wb.worksheets
ws4.cell(row=ws_line, column=ws_col).value = curSheet_delphi.cell( row=row_line, column=6).value if curSheet_delphi.cell( row=row_line, column=2).value == curSheet_delphi.cell( row=row_line + 1, column=2).value: #same item? ws_line += 1 # print(str(currentDir+'\\delphi\\'+tableName)) else: #save the current item table for delphi tableName = curSheet_delphi.cell(row=row_line, column=2).value #print(str(currentDir+'\\delphi\\'+tableName)) #wb.save(filename=str(currentDir+'\\delphi\\'+tableName)) wb.remove_sheet(wb.get_sheet_by_name('Sheet')) #wb.remove_sheet( ) wb.save(filename=str(currentDir + '\\' + tableName + '.xlsx')) #create wb = Workbook() ws1 = wb.create_sheet(0) ws1.title = u'功能匹配度' ws1.cell(row=1, column=1).value = 'No.' ws1.cell(row=1, column=2).value = '需求类别' ws1.cell(row=1, column=3).value = '客户需求' ws1.cell(row=1, column=4).value = '第' + str(ws_col - 3) + '位专家' ws1['A1'].style = 'Accent3' ws1['B1'].style = 'Accent3' ws1['C1'].style = 'Accent3'
def export_to_spreadsheet(qs, save_to=None): # colors black = 'FF000000' dark_gray = 'FFA6A6A6' # styles header_font = Font( name='Calibri', size=12, bold=True, italic=False, vertAlign=None, underline='none', strike=False, color=black) std_font = Font( name='Calibri', size=12, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color=black) header_fill = PatternFill(fill_type=FILL_SOLID, start_color=dark_gray) thin_black_side = Side(style='thin', color='FF000000') std_border = Border( left=thin_black_side, right=thin_black_side, top=thin_black_side, bottom=thin_black_side, ) centered_alignment = Alignment( horizontal='center', vertical='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) number_format = '# ### ### ##0' header_style = { 'font': header_font, 'fill': header_fill, 'border': std_border, 'alignment': centered_alignment, } std_style = { 'font': std_font, 'border': std_border, 'alignment': centered_alignment, 'number_format': number_format, } empty = "" col_year = 1 col_month = 2 col_dps = 3 col_zs = 4 col_numerator = 5 col_denominator = 6 col_value = 7 col_human = 8 def apply_style(target, style): for key, value in style.items(): setattr(target, key, value) wb = Workbook() wb.remove_sheet(wb.active) logger.info("exporting {} records".format(qs.count())) # one sheet per indicator for indicator in Indicator.objects.all(): ws = wb.create_sheet() ws.title = "#{}".format(indicator.number) def std_write(row, column, value, style=std_style): cell = ws.cell(row=row, column=column) cell.value = value apply_style(cell, style) row = 1 # write header std_write(row, col_year, "Année", header_style) std_write(row, col_month, "Mois", header_style) std_write(row, col_dps, "DPS", header_style) xl_set_col_width(ws, col_dps, 7) std_write(row, col_zs, "ZS", header_style) xl_set_col_width(ws, col_zs, 7) std_write(row, col_numerator, "Numérateur", header_style) xl_set_col_width(ws, col_numerator, 3) std_write(row, col_denominator, "Dénominateur", header_style) xl_set_col_width(ws, col_denominator, 3) std_write(row, col_value, "Valeur", header_style) std_write(row, col_human, "Valeur (affich.)", header_style) xl_set_col_width(ws, col_human, 3) row += 1 for record in qs.filter(indicator=indicator).iterator(): std_write(row, col_year, record.period.year, std_style) std_write(row, col_month, record.period.month, std_style) std_write(row, col_dps, getattr(record.entity.get_dps(), 'short_name', empty), std_style) std_write(row, col_zs, getattr(record.entity.get_zs(), 'short_name', empty), std_style) std_write(row, col_numerator, record.numerator, std_style) std_write(row, col_denominator, record.denominator, std_style) std_write(row, col_value, record.value, std_style) std_write(row, col_human, record.human(), std_style) row += 1 if save_to: logger.info("saving to {}".format(save_to)) wb.save(save_to) return stream = StringIO.StringIO() wb.save(stream) return stream
def export_to_spreadsheet(qs, save_to=None): # colors black = 'FF000000' dark_gray = 'FFA6A6A6' # styles header_font = Font(name='Calibri', size=12, bold=True, italic=False, vertAlign=None, underline='none', strike=False, color=black) std_font = Font(name='Calibri', size=12, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color=black) header_fill = PatternFill(fill_type=FILL_SOLID, start_color=dark_gray) thin_black_side = Side(style='thin', color='FF000000') std_border = Border( left=thin_black_side, right=thin_black_side, top=thin_black_side, bottom=thin_black_side, ) centered_alignment = Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) number_format = '# ### ### ##0' header_style = { 'font': header_font, 'fill': header_fill, 'border': std_border, 'alignment': centered_alignment, } std_style = { 'font': std_font, 'border': std_border, 'alignment': centered_alignment, 'number_format': number_format, } empty = "" col_year = 1 col_month = 2 col_dps = 3 col_zs = 4 col_numerator = 5 col_denominator = 6 col_value = 7 col_human = 8 def apply_style(target, style): for key, value in style.items(): setattr(target, key, value) wb = Workbook() wb.remove_sheet(wb.active) logger.info("exporting {} records".format(qs.count())) # one sheet per indicator for indicator in Indicator.objects.all(): ws = wb.create_sheet() ws.title = "#{}".format(indicator.number) def std_write(row, column, value, style=std_style): cell = ws.cell(row=row, column=column) cell.value = value apply_style(cell, style) row = 1 # write header std_write(row, col_year, "Année", header_style) std_write(row, col_month, "Mois", header_style) std_write(row, col_dps, "DPS", header_style) xl_set_col_width(ws, col_dps, 7) std_write(row, col_zs, "ZS", header_style) xl_set_col_width(ws, col_zs, 7) std_write(row, col_numerator, "Numérateur", header_style) xl_set_col_width(ws, col_numerator, 3) std_write(row, col_denominator, "Dénominateur", header_style) xl_set_col_width(ws, col_denominator, 3) std_write(row, col_value, "Valeur", header_style) std_write(row, col_human, "Valeur (affich.)", header_style) xl_set_col_width(ws, col_human, 3) row += 1 for record in qs.filter(indicator=indicator).iterator(): std_write(row, col_year, record.period.year, std_style) std_write(row, col_month, record.period.month, std_style) std_write(row, col_dps, getattr(record.entity.get_dps(), 'short_name', empty), std_style) std_write(row, col_zs, getattr(record.entity.get_zs(), 'short_name', empty), std_style) std_write(row, col_numerator, record.numerator, std_style) std_write(row, col_denominator, record.denominator, std_style) std_write(row, col_value, record.value, std_style) std_write(row, col_human, record.human(), std_style) row += 1 if save_to: logger.info("saving to {}".format(save_to)) wb.save(save_to) return stream = StringIO.StringIO() wb.save(stream) return stream
class XlWorkBook: def __init__(self): self.wsByName = {} self.wsByPyWs = {} self.wsByXlWs = {} self.wb = Workbook() pyws = self.wb.active name = pyws.title xlws = XlWorkSheet(self.wb,pyws,name) self.wsByName[name] = (pyws,xlws) self.wsByPyWs[pyws] = (xlws,name) self.wsByXlWs[xlws] = (pyws,name) self.activeSheet = name #-------------------------------------------------------------------- def Read(self,filename): self.wsByName = {} self.wsByPyWs = {} self.wsByXlWs = {} self.wb = load_workbook(filename) for item in self.wb.worksheets: pyws = item name = item.title xlws = XlWorkSheet(self.wb,pyws,name) self.wsByName[name] = (pyws,xlws) self.wsByPyWs[pyws] = (xlws,name) self.wsByXlWs[xlws] = (pyws,name) self.activeSheet = self.wb.active.title #-------------------------------------------------------------------- def CreateXlWorkSheet(self,name): if (name not in self.wsByName): self.wb.create_sheet(name) pyws = self.wb.get_sheet_by_name(name) xlws = XlWorkSheet(self.wb,pyws,name) self.wsByName[name] = (pyws,xlws) self.wsByPyWs[pyws] = (xlws,name) self.wsByXlWs[xlws] = (pyws,name) return self.wsByName[name][1] else: logging.error('Attempting to create duplicate name in workbook: ' + name) return None #-------------------------------------------------------------------- def CreateXlChrtSheet(self,name): if (name not in self.wsByName): pycs = self.wb.create_chartsheet(name) #pycs = self.wb.get_sheet_by_name(name) xlcs = XlChrtSheet(self.wb,pycs,name) self.wsByName[name] = (pycs,xlcs) #self.wsByPyWs[pycs] = (xlcs,name) self.wsByXlWs[xlcs] = (pycs,name) return self.wsByName[name][1] else: logging.debug.error('Attempting to create duplicate name in workbook: ' + name) return None #-------------------------------------------------------------------- # def CreateSheet(self,name): # if (name not in self.wsByName): # self.wb.create_sheet(name) # pyws = self.wb.get_sheet_by_name(name) # xlws = XlWorkSheet(self.wb,pyws,name) # # self.wsByName[name] = (pyws,xlws) # self.wsByPyWs[pyws] = (xlws,name) # self.wsByXlWs[xlws] = (pyws,name) # # return self.wsByName[name][1] # # else: # debug.error('Attempting to create duplicate name in workbook: ' + name) # return None #-------------------------------------------------------------------- def GetActiveSheet(self): pyws = self.wb.active name = pyws.title if (name in self.wsByName): if (name == self.activeSheet): return self.wsByName[name][1] else: logging.debug('XlWorkBook data corrupt') else: logging.debug('XlWorkBook data corrupt') #-------------------------------------------------------------------- def SetName(self,ws,name): if (name not in self.wsByName): xlws = ws pyws,orig = self.wsByXlWs[xlws] pyws.title = name xlws.name = name self.wsByPyWs[pyws] = (xlws,name) self.wsByXlWs[xlws] = (pyws,name) if (self.activeSheet == orig): self.activeSheet = name del self.wsByName[orig] self.wsByName[name] = (pyws,xlws) else: debug.error('Attempting to create duplicate name in workbook: ' + name) return self.wsByName[name][1] #-------------------------------------------------------------------- def GetSheetByName(self,name): return self.wsByName[name][1] #-------------------------------------------------------------------- def RemoveSheetByName(self,name): self.wb.remove_sheet(self.wb.get_sheet_by_name(name)) #-------------------------------------------------------------------- def Save(self,name): self.wb.save(name)
class ExcelGenerator(object): def __init__(self, publication_extracts: List[PubExtract], language_pack, excel_config): self.publication_extracts = publication_extracts self.language_pack = language_pack self.__CURRENT_ROW = 3 self.__LEFT_COLUMNS = ['B', 'C', 'D', 'E'] self.__RIGHT_COLUMNS = ['G', 'H', 'I', 'J'] self.__ACTIVE_COLUMNS = self.__LEFT_COLUMNS self.excel_config = excel_config # a new sheet is created for each publication so the program won't # use the first sheet (it will be empty). Hence, it it removed self.workbook = Workbook() self.workbook.remove_sheet(worksheet=self.workbook.get_active_sheet()) def create_excel_doc(self): if len(self.publication_extracts) == 0: return print('creating excel document...') for publication_extract in self.publication_extracts: print(f"preparing mwb({self.language_pack.lang_key}) {publication_extract.pub_month}/{publication_extract.pub_year}", end=' ') self._add_populated_sheet(publication_extract) print('[\033[92mOK\033[0m]') file_name = 'WREX_{}_{}.xlsx'.format(datetime.now().strftime("%m-%d-%Y_%H:%M"), self.language_pack.lang_key) self.workbook.save(file_name) print('done...', f"saved to '{file_name}'") def _add_populated_sheet(self, publication_extract: PubExtract): sheet = self.workbook.create_sheet(publication_extract.pub_name) meetings_count = 0 self._insert_sheet_title( sheet, self.language_pack.get_month_name(publication_extract.pub_month) + ' ' + publication_extract.pub_year) for meeting in publication_extract.meetings: self._insert_header_content(meeting.week_span, sheet) self._insert_section(meeting.treasures_section, sheet) self._insert_section(meeting.ministry_section, sheet) self._insert_section(meeting.christian_section, sheet) self._insert_footer_content(sheet) meetings_count += 1 if meetings_count == 3: self.__CURRENT_ROW = 5 self.__ACTIVE_COLUMNS = self.__RIGHT_COLUMNS self._finalize_styling(sheet) # reset indexes and make them ready for the next sheet self.__CURRENT_ROW = 3 self.__ACTIVE_COLUMNS = self.__LEFT_COLUMNS def _insert_sheet_title(self, sheet: Worksheet, month_name_and_year: str): current_row = str(self.__CURRENT_ROW) start_cell = self.__LEFT_COLUMNS[0] + str(current_row) end_cell = self.__RIGHT_COLUMNS[3] + str(current_row) sheet[start_cell] = self.language_pack.meeting_name + ' – ' + month_name_and_year sheet.merge_cells(start_cell + ':' + end_cell) self.__CURRENT_ROW += 2 self._style_sheet_title(sheet) def _style_sheet_title(self, sheet: Worksheet): cell = self.__ACTIVE_COLUMNS[0] + str(3) sheet[cell].font = Font(bold=True, size=self.excel_config.SHEET_TITLE_FONT_SIZE) sheet[cell].alignment = Alignment(horizontal='center', vertical='center') def _insert_header_content(self, week_span: str, sheet: Worksheet): # week span week_span_row = self.__CURRENT_ROW current_row = str(self.__CURRENT_ROW) start_cell = self.__ACTIVE_COLUMNS[0] + current_row end_cell = self.__ACTIVE_COLUMNS[2] + current_row sheet[start_cell] = re.sub('[-|–]', ' – ', week_span) sheet.merge_cells(start_cell + ':' + end_cell) self.__CURRENT_ROW += 1 # chairman current_row = str(self.__CURRENT_ROW) start_cell = self.__ACTIVE_COLUMNS[0] + current_row end_cell = self.__ACTIVE_COLUMNS[2] + current_row sheet[start_cell] = self.language_pack.chairman sheet.merge_cells(start_cell + ':' + end_cell) self.__CURRENT_ROW += 1 # opening prayer current_row = str(self.__CURRENT_ROW) start_cell = self.__ACTIVE_COLUMNS[2] + current_row sheet[start_cell] = self.language_pack.opening_prayer self.__CURRENT_ROW += 1 self._style_header_content(week_span_row, sheet) def _style_header_content(self, week_span_row: int, sheet: Worksheet): alignment = Alignment(horizontal='left', vertical='center') border = Border(bottom=Side(border_style='thin')) # week span cell = self.__ACTIVE_COLUMNS[0] + str(week_span_row) sheet[cell].font = Font(bold=True, size=self.excel_config.LARGE_FONT_SIZE) sheet[cell].alignment = alignment # chairman cell = self.__ACTIVE_COLUMNS[0] + str(week_span_row + 1) sheet[cell].font = Font(bold=True, size=self.excel_config.LARGE_FONT_SIZE) sheet[cell].alignment = alignment sheet[cell].border = border cell = self.__ACTIVE_COLUMNS[3] + str(week_span_row + 1) # last cell sheet[cell].border = border # opening prayer cell = self.__ACTIVE_COLUMNS[2] + str(week_span_row + 2) sheet[cell].font = Font(size=self.excel_config.SMALL_FONT_SIZE) sheet[cell].alignment = alignment cell = self.__ACTIVE_COLUMNS[3] + str(week_span_row + 2) # last cell sheet[cell].border = border def _insert_section_title(self, meeting_section: MeetingSection, sheet: Worksheet): current_row = str(self.__CURRENT_ROW) start_cell = self.__ACTIVE_COLUMNS[0] + current_row sheet[start_cell] = meeting_section.title # by default the right most cell at which merging cells ends is on the 5th column (including the offset) end_cell = self.__ACTIVE_COLUMNS[3] + current_row # but if the section is 'IMPROVE_IN_MINISTRY' and the user requests hall-dividing # rows then it changes to the 3rd row if (meeting_section.section_kind == SectionKind.IMPROVE_IN_MINISTRY) and \ self.excel_config.INSERT_HALL_DIVISION_LABELS: end_cell = self.__ACTIVE_COLUMNS[1] + current_row self._insert_hall_divider(sheet, meeting_section.section_kind) sheet.merge_cells(start_cell + ':' + end_cell) self._style_section_title(self.__CURRENT_ROW, sheet, meeting_section.section_kind) self.__CURRENT_ROW += 1 def _style_section_title(self, title_row: int, sheet: Worksheet, section_kind: SectionKind): if section_kind == SectionKind.TREASURES: bg_color = self.excel_config.TREASURES_SECTION_TITLE_BG_COLOR elif section_kind == SectionKind.IMPROVE_IN_MINISTRY: bg_color = self.excel_config.MINISTRY_SECTION_TITLE_BG_COLOR else: bg_color = self.excel_config.CHRISTIAN_LIFE_SECTION_TITLE_BG_COLOR cell = self.__ACTIVE_COLUMNS[0] + str(title_row) sheet[cell].font = Font(bold=True, size=self.excel_config.LARGE_FONT_SIZE) sheet[cell].alignment = Alignment(horizontal='left', vertical='center') sheet[cell].fill = PatternFill(patternType='solid', fgColor=bg_color) sheet[cell].border = Border(top=Side(border_style='thin')) def _insert_hall_divider(self, sheet, section_kind: SectionKind): current_row = str(self.__CURRENT_ROW) main_hall_cell = self.__ACTIVE_COLUMNS[2] + current_row second_hall_cell = self.__ACTIVE_COLUMNS[3] + current_row sheet[main_hall_cell] = self.language_pack.main_hall sheet[second_hall_cell] = self.language_pack.second_hall self._style_hall_divider(self.__CURRENT_ROW, sheet, section_kind) def _style_hall_divider(self, divider_row: int, sheet: Worksheet, section_kind: SectionKind): main_hall_cell = self.__ACTIVE_COLUMNS[2] + str(divider_row) second_hall_cell = self.__ACTIVE_COLUMNS[3] + str(divider_row) font = Font(bold=False, size=self.excel_config.SMALL_FONT_SIZE) alignment = Alignment(horizontal='center', vertical='center') if section_kind == SectionKind.IMPROVE_IN_MINISTRY: fg_color = self.excel_config.MINISTRY_SECTION_TITLE_BG_COLOR else: fg_color = self.excel_config.DEFAULT_BG_COLOR fill = PatternFill(patternType='solid', fgColor=fg_color) border = Border(top=Side(border_style='thin')) sheet[main_hall_cell].font = font sheet[main_hall_cell].alignment = alignment sheet[main_hall_cell].fill = fill sheet[main_hall_cell].border = border sheet[second_hall_cell].font = font sheet[second_hall_cell].alignment = alignment sheet[second_hall_cell].fill = fill sheet[second_hall_cell].border = border def _insert_section(self, meeting_section: MeetingSection, sheet: Worksheet): self._insert_section_title(meeting_section, sheet) bible_reading = self.language_pack.bible_reading for presentation in meeting_section.presentations: if meeting_section.section_kind == SectionKind.TREASURES: if (bible_reading in presentation) and self.excel_config.INSERT_HALL_DIVISION_LABELS: self._insert_hall_divider(sheet, meeting_section.section_kind) self.__CURRENT_ROW += 1 current_row = str(self.__CURRENT_ROW) start_cell = self.__ACTIVE_COLUMNS[1] + current_row if (meeting_section.section_kind == SectionKind.IMPROVE_IN_MINISTRY) and\ self.excel_config.INSERT_HALL_DIVISION_LABELS: end_cell = self.__ACTIVE_COLUMNS[1] + current_row else: end_cell = self.__ACTIVE_COLUMNS[2] + current_row sheet[start_cell] = presentation if (bible_reading not in presentation) or \ (bible_reading in presentation and not self.excel_config.INSERT_HALL_DIVISION_LABELS): sheet.merge_cells(start_cell + ':' + end_cell) self._style_section(self.__CURRENT_ROW, sheet) self.__CURRENT_ROW += 1 def _style_section(self, presentation_row: int, sheet: Worksheet): cell = self.__ACTIVE_COLUMNS[1] + str(presentation_row) border = Border(bottom=Side(border_style='thin')) alignment = Alignment(horizontal='center', vertical='center') sheet[cell].font = Font(size=self.excel_config.LARGE_FONT_SIZE) sheet[cell].alignment = Alignment(horizontal='left', vertical='center') sheet[cell].border = border # underline and center the last two cells of a presentation row (that is where presenter names go) cell = self.__ACTIVE_COLUMNS[2] + str(presentation_row) sheet[cell].border = border sheet[cell].alignment = alignment cell = self.__ACTIVE_COLUMNS[3] + str(presentation_row) sheet[cell].border = border sheet[cell].alignment = alignment def _insert_footer_content(self, sheet: Worksheet): footer_row = self.__CURRENT_ROW current_row = str(self.__CURRENT_ROW) cell = self.__ACTIVE_COLUMNS[2] + current_row sheet[cell] = self.language_pack.reader self.__CURRENT_ROW += 1 current_row = str(self.__CURRENT_ROW) cell = self.__ACTIVE_COLUMNS[2] + current_row sheet[cell] = self.language_pack.concluding_prayer self.__CURRENT_ROW += 3 self._style_footer_content(footer_row, sheet) def _style_footer_content(self, footer_row: int, sheet: Worksheet): font = Font(size=self.excel_config.SMALL_FONT_SIZE) alignment = Alignment(horizontal='left', vertical='center') border = Border(bottom=Side(border_style='thin')) cell = self.__ACTIVE_COLUMNS[2] + str(footer_row) sheet[cell].font = font sheet[cell].alignment = alignment sheet[cell].border = border cell = self.__ACTIVE_COLUMNS[3] + str(footer_row) sheet[cell].border = border cell = self.__ACTIVE_COLUMNS[2] + str(footer_row + 1) sheet[cell].font = font sheet[cell].alignment = alignment sheet[cell].border = border cell = self.__ACTIVE_COLUMNS[3] + str(footer_row + 1) sheet[cell].border = border def _finalize_styling(self, sheet: Worksheet): sheet.page_setup.paperSize = Worksheet.PAPERSIZE_A4 sheet.sheet_properties.pageSetUpPr.fitToPage = True sheet.page_margins = PageMargins(left=self.excel_config.MARGIN_LENGTH, right=self.excel_config.MARGIN_LENGTH) sheet.column_dimensions[self.__LEFT_COLUMNS[0]].width = 4 sheet.column_dimensions[self.__LEFT_COLUMNS[1]].width = 42 sheet.column_dimensions[self.__RIGHT_COLUMNS[0]].width = 4 sheet.column_dimensions[self.__RIGHT_COLUMNS[1]].width = 42 for rows in sheet.iter_rows(min_row=3): for row in rows: sheet.row_dimensions[row.row].height = self.excel_config.ROW_HEIGHT
def saveTableToExelle(rFilesDir): # get reports from FilingSummary reports = [] try: fsdoc = etree.parse(os.path.join(rFilesDir, "FilingSummary.xml")) for rElt in fsdoc.iter(tag="Report"): reports.append(Report(rElt.findtext("LongName"), rElt.findtext("ShortName"), rElt.findtext("HtmlFileName"))) except (EnvironmentError, etree.LxmlError) as err: print("FilingSummary.xml: directory {0} error: {1}".format(rFilesDir, err)) wb = Workbook(encoding='utf-8') # remove predefined sheets for sheetName in wb.get_sheet_names(): ws = wb.get_sheet_by_name(sheetName) if ws is not None: wb.remove_sheet(ws) sheetNames = set() # prevent duplicates for reportNum, report in enumerate(reports): sheetName = report.shortName[:31] # max length 31 for excel title if sheetName in sheetNames: sheetName = sheetName[:31-len(str(reportNum))] + str(reportNum) sheetNames.add(sheetName) ws = wb.create_sheet(title=sheetName) try: # doesn't detect utf-8 encoding the normal way, pass it a string #htmlSource = '' #with open(os.path.join(rFilesDir, report.htmlFileName), 'rt', encoding='utf-8') as fh: # htmlSource = fh.read() #rdoc = html.document_fromstring(htmlSource) rdoc = html.parse(os.path.join(rFilesDir, report.htmlFileName)) row = -1 mergedAreas = {} # colNumber: (colspan,lastrow) for tableElt in rdoc.iter(tag="table"): # skip pop up tables if tableElt.get("class") == "authRefData": continue if tableElt.getparent().tag == "div": style = tableElt.getparent().get("style") if style and displayNonePattern.match(style): continue colWidths = {} for rowNum, trElt in enumerate(tableElt.iter(tag="tr")): # remove passed mergedAreas for mergeCol in [col for col, mergedArea in mergedAreas.items() if mergedArea[1] > rowNum]: del mergedAreas[mergeCol] col = 0 for coltag in ("th", "td"): for cellElt in trElt.iter(tag=coltag): if col == 0: row += 1 # new row if col in mergedAreas: col += mergedAreas[col][0] - 1 text = cellElt.text_content() colspan = intCol(cellElt, "colspan", 1) rowspan = intCol(cellElt, "rowspan", 1) #if col not in colWidths: # colWidths[col] = 10.0 # some kind of default width for elt in cellElt.iter(): style = elt.get("style") if style and "width:" in style: try: kw, sep, width = style.partition("width:") if "px" in width: width, sep, kw = width.partition("px") width = float(width) * 0.67777777 else: width = float(width) colWidths[col] = width except ValueError: pass if rowspan > 1: mergedAreas[col] = (colspan, row + rowspan - 1) cell = ws.cell(row=row,column=col) if text: cell.value = text if numberPattern.match(text): cell.style.alignment.horizontal = Alignment.HORIZONTAL_RIGHT else: cell.style.alignment.wrap_text = True if colspan > 1 or rowspan > 1: ws.merge_cells(start_row=row, end_row=row+rowspan-1, start_column=col, end_column=col+colspan-1) cell.style.alignment.vertical = Alignment.VERTICAL_TOP if coltag == "th": cell.style.alignment.horizontal = Alignment.HORIZONTAL_CENTER cell.style.font.bold = True cell.style.font.size = 9 # some kind of default size col += colspan for col, width in colWidths.items(): ws.column_dimensions[get_column_letter(col+1)].width = width except (EnvironmentError, etree.LxmlError) as err: print("{0}: directory {1} error: {2}".format(report.htmlFileName, rFilesDir, err)) wb.save(os.path.join(rFilesDir, "exelleOut.xlsx"))
def write_excel(excel_name, result_dicts): from openpyxl.workbook import Workbook from openpyxl.styles import Alignment alignment = Alignment( wrap_text=True, # 自动换行 ) #ExcelWriter,里面封装好了对Excel的写操作 from openpyxl.writer.excel import ExcelWriter #get_column_letter函数将数字转换为相应的字母,如1-->A,2-->B from openpyxl.utils import get_column_letter from openpyxl.reader.excel import load_workbook if os.path.isfile(excel_name): # #读取excel2007文件 wb = load_workbook(excel_name) else: #新建一个workbook wb = Workbook() #设置文件输出路径与名称 dest_filename = excel_name # # 获取第一个sheet ws = wb.get_active_sheet() if ws != None: wb.remove_sheet(ws) ws = wb.create_sheet('Sheet1') #第一个sheet是ws # ws = wb.worksheets[0] # #设置ws的名称 # ws.title = "sheet1" line = 1 print(u'定位写入坐标') while ws.cell(line, 1).value: # print(ws.cell("A%s" % line).value) line += 1 print(u'从第%s行开始写入' % line) #Title col = 1 ws.cell(line, col).value = u'Chapter' col = col + 1 ws.cell(line, col).value = u'ReqId' col = col + 1 ws.cell(line, col).value = u'Title' col = col + 1 ws.cell(line, col).value = u'Content' ws.column_dimensions['D'].width = 50.0 line += 1 for i, result in enumerate(result_dicts): #print(u'正在写入第%s条数据到excel' % (i+1)) try: print(u'正在写入 %s' % result['ReqId']) except UnicodeEncodeError: clr.print_red_textprint("UnicodeEncodeError") ws.cell(line, 2).value = result['ReqId'] ws.cell(line, 3).value = result['Title'] if (len(result['Content']) > 300): ws.cell(line, 4).value = result['Content'][:300] + " ... " else: ws.cell(line, 4).value = result['Content'] ws.cell(line, 4).alignment = alignment line += 1 #最后保存文件 wb.save(filename=dest_filename)
def saveTableToExelle(rFilesDir): # get reports from FilingSummary reports = [] try: fsdoc = etree.parse(os.path.join(rFilesDir, "FilingSummary.xml")) for rElt in fsdoc.iter(tag="Report"): reports.append( Report(rElt.findtext("LongName"), rElt.findtext("ShortName"), rElt.findtext("HtmlFileName"))) except (EnvironmentError, etree.LxmlError) as err: print("FilingSummary.xml: directory {0} error: {1}".format( rFilesDir, err)) wb = Workbook(encoding='utf-8') # remove predefined sheets for sheetName in wb.get_sheet_names(): ws = wb.get_sheet_by_name(sheetName) if ws is not None: wb.remove_sheet(ws) sheetNames = set() # prevent duplicates for reportNum, report in enumerate(reports): sheetName = report.shortName[:31] # max length 31 for excel title if sheetName in sheetNames: sheetName = sheetName[:31 - len(str(reportNum))] + str(reportNum) sheetNames.add(sheetName) ws = wb.create_sheet(title=sheetName) try: # doesn't detect utf-8 encoding the normal way, pass it a string #htmlSource = '' #with open(os.path.join(rFilesDir, report.htmlFileName), 'rt', encoding='utf-8') as fh: # htmlSource = fh.read() #rdoc = html.document_fromstring(htmlSource) rdoc = html.parse(os.path.join(rFilesDir, report.htmlFileName)) row = -1 mergedAreas = {} # colNumber: (colspan,lastrow) for tableElt in rdoc.iter(tag="table"): # skip pop up tables if tableElt.get("class") == "authRefData": continue if tableElt.getparent().tag == "div": style = tableElt.getparent().get("style") if style and displayNonePattern.match(style): continue colWidths = {} for rowNum, trElt in enumerate(tableElt.iter(tag="tr")): # remove passed mergedAreas for mergeCol in [ col for col, mergedArea in mergedAreas.items() if mergedArea[1] > rowNum ]: del mergedAreas[mergeCol] col = 0 for coltag in ("th", "td"): for cellElt in trElt.iter(tag=coltag): if col == 0: row += 1 # new row if col in mergedAreas: col += mergedAreas[col][0] - 1 text = cellElt.text_content() colspan = intCol(cellElt, "colspan", 1) rowspan = intCol(cellElt, "rowspan", 1) #if col not in colWidths: # colWidths[col] = 10.0 # some kind of default width for elt in cellElt.iter(): style = elt.get("style") if style and "width:" in style: try: kw, sep, width = style.partition( "width:") if "px" in width: width, sep, kw = width.partition( "px") width = float(width) * 0.67777777 else: width = float(width) colWidths[col] = width except ValueError: pass if rowspan > 1: mergedAreas[col] = (colspan, row + rowspan - 1) cell = ws.cell(row=row, column=col) if text: cell.value = text if numberPattern.match(text): cell.style.alignment.horizontal = Alignment.HORIZONTAL_RIGHT else: cell.style.alignment.wrap_text = True if colspan > 1 or rowspan > 1: ws.merge_cells(start_row=row, end_row=row + rowspan - 1, start_column=col, end_column=col + colspan - 1) cell.style.alignment.vertical = Alignment.VERTICAL_TOP if coltag == "th": cell.style.alignment.horizontal = Alignment.HORIZONTAL_CENTER cell.style.font.bold = True cell.style.font.size = 9 # some kind of default size col += colspan for col, width in colWidths.items(): ws.column_dimensions[get_column_letter(col + 1)].width = width except (EnvironmentError, etree.LxmlError) as err: print("{0}: directory {1} error: {2}".format( report.htmlFileName, rFilesDir, err)) wb.save(os.path.join(rFilesDir, "exelleOut.xlsx"))
class XlReport: """ Wrapper for openpyxl Using xlsx because xls has limitations and ods has no good python library """ def __init__(self, file_name="Report"): """ file_name does not need an extention """ if file_name.endswith('.xls'): file_name = file_name[:-4] elif file_name.endswith('xlsx'): file_name = file_name[:-5] file_name = file_name.replace(' ', '_') # Some browsers don't deal well with spaces in downloads self.workbook = Workbook() self.workbook.remove_sheet(self.workbook.get_active_sheet()) self.file_name = file_name def add_sheet(self, data, title=None, header_row=None, heading=None, auto_width=False, max_auto_width=50): """ Add a sheet with data to workbook title: sheet name header_row: List - Column header (bold with bottom border) heading: Sheet heading (very top of sheet) auto_width: will ESTIMATE the width of each column by counting max chars in each column. It will not work with a formula. max_auto_width: is the max number of characters a column to be """ sheet = self.workbook.create_sheet() if title: sheet.title = unicode(title) if heading: sheet.append([unicode(heading)]) if header_row: header_row = map(unicode, header_row) sheet.append(header_row) row = sheet.get_highest_row() for i, header_cell in enumerate(header_row): cell = sheet.cell(row=row-1, column=i) cell.style.font.bold = True cell.style.borders.bottom.border_style = openpyxl.style.Border.BORDER_THIN for row in data: row = map(unicode, row) sheet.append(row) if auto_width: column_widths = [] for row in data: row = map(unicode, row) for i, cell in enumerate(row): if len(column_widths) > i: if len(cell) > column_widths[i]: column_widths[i] = len(cell) else: column_widths += [len(cell)] for i, column_width in enumerate(column_widths): if column_width > 3: if column_width < max_auto_width: # * 0.9 estimates a typical variable width font sheet.column_dimensions[get_column_letter(i+1)].width = column_width * 0.9 else: sheet.column_dimensions[get_column_letter(i+1)].width = max_auto_width def save(self, filename): self.workbook.save(settings.MEDIA_ROOT + filename) def as_download(self): """ Returns a django HttpResponse with the xlsx file """ myfile = StringIO.StringIO() myfile.write(save_virtual_workbook(self.workbook)) response = HttpResponse( myfile.getvalue(), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = 'attachment; filename=%s.xlsx' % self.file_name response['Content-Length'] = myfile.tell() return response
try: fsdoc = etree.parse(os.path.join(rFilesDir, u"FilingSummary.xml")) for rElt in fsdoc.iter(tag=u"Report"): reports.append(Report(rElt.findtext(u"LongName"), rElt.findtext(u"ShortName"), rElt.findtext(u"HtmlFileName"))) except (EnvironmentError, etree.LxmlError), err: print u"FilingSummary.xml: directory {0} error: {1}".format(rFilesDir, err) wb = Workbook(encoding=u'utf-8') # remove predefined sheets for sheetName in wb.get_sheet_names(): ws = wb.get_sheet_by_name(sheetName) if ws is not None: wb.remove_sheet(ws) sheetNames = set() # prevent duplicates for reportNum, report in enumerate(reports): sheetName = report.shortName[:31] # max length 31 for excel title if sheetName in sheetNames: sheetName = sheetName[:31-len(unicode(reportNum))] + unicode(reportNum) sheetNames.add(sheetName) ws = wb.create_sheet(title=sheetName) try: # doesn't detect utf-8 encoding the normal way, pass it a string #htmlSource = '' #with open(os.path.join(rFilesDir, report.htmlFileName), 'rt', encoding='utf-8') as fh: # htmlSource = fh.read()
class TSSummary: colName = \ [ 'METRICS', ' Code Based', ' Key Accounts', ' Ericsson', ' Nokia', ' ALU', ' All Others', ' Regional Key Accounts', ' Qualcomm', ' AT&T', ' Sprint', ' Carriers', ' AT&T', ' Sprint', ' T-Mobile', ' Small Cell', ' Qualcomm', ' Intel', ' Parallel', ' SpiderCloud', ' Modular Instruments', ' Qor', ' Ter', ' Semiconductor', ' Air', ' Van', ' Others', ' OTH', ' COB', ' TTT', ' Overhead', ' X4x', ' X1x', ' Team Based', ' DMR', ' MI', ' Total Hours', ' All', ' Permenent', ' Contract', ' Labour', ' Travel', ' Standby', ' FAEs' ] def __init__(self,fname): self.filename = fname self.sslist = [] # list of spreadsheets by week self.tsdict = {} # dict of timesheet data by week self.week = None # current week number self.year = None # year of query self.swb = None # summary workbook self.metrics = {} # summary metrics #--------------------------------------------------------------------- def Process(self,tsdata,year,week,region,team): self.year = year self.week = week self.Sort(tsdata,week) for i,week in enumerate(self.sslist): wsDate = Calendar.week[i+1] self.tsdict[wsDate] = [] for j,ssdata in enumerate(week): ts = Timesheet(ssdata, wsDate) ts.ReadFile() self.tsdict[wsDate].append(ts) self.createWorkbook(region) self.writeRawDataSheet(region,team) self.saveWorkbook() #--------------------------------------------------------------------- def createWorkbook(self,region): self.swb = Workbook() self.swb.create_sheet(region + ' Charts') self.swb.create_sheet(region + ' Tables') self.swb.create_sheet(region + ' Metrics') self.swb.create_sheet(region + ' Data') self.swb.remove_sheet(self.swb.get_sheet_by_name('Sheet')) #--------------------------------------------------------------------- def saveWorkbook(self): self.swb.save(self.filename) #--------------------------------------------------------------------- def setCell(self,cell,align,fmt,value): if (align == 'C'): align = Alignment(horizontal='center',vertical='center') elif (align == 'L'): align = Alignment(horizontal='left',vertical='center') elif (align == 'R'): align = Alignment(horizontal='right',vertical='center') else: align = Alignment(horizontal='right',vertical='center') side = Side(style='thin') border = Border(left=side,right=side,top=side,bottom=side) #style = Style(border=border,alignment=align,number_format=fmt) #cell.style = style if (fmt == 'F'): fmt = '0.00' cell.number_format = fmt cell.alignment = align.copy() cell.border = border.copy() cell.value = value #--------------------------------------------------------------------- def flagCell(self,cell): side = Side(style='medium',color=RED) border = Border(left=side,right=side,top=side,bottom=side) #style = Style(border=border) #cell.style = style cell.border = border.copy() #--------------------------------------------------------------------- def writeRawDataSheet(self,region,team): ws_name = region + ' Data' ws = self.swb.get_sheet_by_name(ws_name) ws.column_dimensions['B'].width = 20 ws.column_dimensions['C'].width = 3 ws.column_dimensions['D'].width = 5 ws.column_dimensions['E'].width = 3 ws.column_dimensions['F'].width = 11 ws.column_dimensions['G'].width = 11 ws.column_dimensions['H'].width = 7 ws.column_dimensions['I'].width = 5 ws.column_dimensions['J'].width = 5 ws.column_dimensions['K'].width = 5 ws.column_dimensions['L'].width = 8 ws.column_dimensions['M'].width = 10 ws.column_dimensions['N'].width = 80 wsRow = 2 wsCol = 2 #------------------------------------------------------------------- # Process each week #------------------------------------------------------------------- for weekIndex in range(1,self.week+1): wsDate = Calendar.week[weekIndex] tslist = self.tsdict[wsDate] metrics = Metrics(wsDate) #----------------------------------------------------------------- # Process timesheet specific informaton #----------------------------------------------------------------- for ts in tslist: fae = ts.ssdata.fae #--------------------------------------------------------------- # Process each entry in the timesheet #--------------------------------------------------------------- for entry in ts.entries: self.setCell(ws.cell(row=wsRow,column=wsCol+ 0),'L','G',fae.fullname.GetWSVal()) self.setCell(ws.cell(row=wsRow,column=wsCol+ 1),'C','G',fae.laborType.GetWSVal()) self.setCell(ws.cell(row=wsRow,column=wsCol+ 2),'C','G',fae.team.GetWSVal()) self.setCell(ws.cell(row=wsRow,column=wsCol+ 3),'C','G',fae.loc.GetWSVal()) self.setCell(ws.cell(row=wsRow,column=wsCol+ 4),'C','G',ts.ssdata.wsDate.GetWSVal()) self.setCell(ws.cell(row=wsRow,column=wsCol+ 5),'C','D',entry.date.GetWSVal()) self.setCell(ws.cell(row=wsRow,column=wsCol+ 6),'C','G',entry.code.GetWSVal()) self.setCell(ws.cell(row=wsRow,column=wsCol+ 7),'C','G',entry.location.GetWSVal()) self.setCell(ws.cell(row=wsRow,column=wsCol+ 8),'C','G',entry.activity.GetWSVal()) self.setCell(ws.cell(row=wsRow,column=wsCol+ 9),'C','G',entry.product.GetWSVal()) self.setCell(ws.cell(row=wsRow,column=wsCol+10),'R','F',entry.hours.GetWSVal()) self.setCell(ws.cell(row=wsRow,column=wsCol+11),'C','G',entry.workType.GetWSVal()) # Flag as red things to check if (entry.code.GetVal() == 'OTH'): self.flagCell(ws.cell(row=wsRow,column=wsCol+ 6)) self.setCell(ws.cell(row=wsRow,column=wsCol+12),'L','G',entry.note.GetWSVal()) self.flagCell(ws.cell(row=wsRow,column=wsCol+12)) if (entry.code.GetVal() == 5): if (entry.location.GetVal() != None): self.flagCell(ws.cell(row=wsRow,column=wsCol+ 7)) if (entry.activity.GetVal() != None): self.flagCell(ws.cell(row=wsRow,column=wsCol+ 8)) if (entry.product.GetVal() != None): self.flagCell(ws.cell(row=wsRow,column=wsCol+ 9)) if (entry.location.GetVal() == 128): self.flagCell(ws.cell(row=wsRow,column=wsCol+ 7)) self.setCell(ws.cell(row=wsRow,column=wsCol+12),'L','G',entry.note.GetWSVal()) self.flagCell(ws.cell(row=wsRow,column=wsCol+12)) if (entry.product.GetVal() == 22): self.flagCell(ws.cell(row=wsRow,column=wsCol+ 9)) self.setCell(ws.cell(row=wsRow,column=wsCol+12),'L','G',entry.note.GetWSVal()) self.flagCell(ws.cell(row=wsRow,column=wsCol+12)) if (entry.hours.GetVal() == None): self.flagCell(ws.cell(row=wsRow,column=wsCol+10)) if (entry.workType.GetVal() == None): self.flagCell(ws.cell(row=wsRow,column=wsCol+11)) # TODO: if hours == 0 or hours > 12 FLAG # TODO: Blue Strips # TODO: Check Cust vs Loc # TODO: Add Local/Remote # TODO: Add Local Text (AM-NE, AM-BA, etc) metrics.Update(fae,entry) # end of entry wsRow += 1 #--------------------------------------------------------------- # end of a week self.metrics[wsDate] = metrics wsRow += 2 #----------------------------------------------------------------- # Write out raw metrics #----------------------------------------------------------------- #for i in range(1,self.week+1): # wsDate = Calendar.week[i] # metrics = self.metrics[wsDate] # metrics.Log() wsRow = 2 wsCol = 2 ws_name = region + ' Metrics' ws = self.swb.get_sheet_by_name(ws_name) ws.column_dimensions['B'].width = 30 ws.column_dimensions['C'].width = 10 i = 1 while (i < len(TSSummary.colName)): self.setCell(ws.cell(row=i+3,column=wsCol+0),'L','G',TSSummary.colName[i]) i += 1 for fae in team.list: name = fae.fullname.GetVal() self.setCell(ws.cell(row=i+3,column=wsCol+0),'L','G',' ' + name) i += 1 for i in range(1,self.week+1): wsDate = Calendar.week[i] metrics = self.metrics[wsDate] self.setCell(ws.cell(row= 2,column=wsCol+0+i),'C','G',str(wsDate)) self.setCell(ws.cell(row= 3,column=wsCol+0+i),'C','G',str(i)) self.setCell(ws.cell(row= 6,column=wsCol+0+i),'R','F',metrics.codes.kam.erc) self.setCell(ws.cell(row= 7,column=wsCol+0+i),'R','F',metrics.codes.kam.nok) self.setCell(ws.cell(row= 8,column=wsCol+0+i),'R','F',metrics.codes.kam.alu) self.setCell(ws.cell(row= 9,column=wsCol+0+i),'R','F',metrics.codes.kam.oth) self.setCell(ws.cell(row=11,column=wsCol+0+i),'R','F',metrics.codes.rka.qcm) self.setCell(ws.cell(row=12,column=wsCol+0+i),'R','F',metrics.codes.rka.att) self.setCell(ws.cell(row=13,column=wsCol+0+i),'R','F',metrics.codes.rka.spr) self.setCell(ws.cell(row=15,column=wsCol+0+i),'R','F',metrics.codes.car.att) self.setCell(ws.cell(row=16,column=wsCol+0+i),'R','F',metrics.codes.car.spr) self.setCell(ws.cell(row=17,column=wsCol+0+i),'R','F',metrics.codes.car.tmo) self.setCell(ws.cell(row=19,column=wsCol+0+i),'R','F',metrics.codes.smc.qcm) self.setCell(ws.cell(row=20,column=wsCol+0+i),'R','F',metrics.codes.smc.itl) self.setCell(ws.cell(row=21,column=wsCol+0+i),'R','F',metrics.codes.smc.prw) self.setCell(ws.cell(row=22,column=wsCol+0+i),'R','F',metrics.codes.smc.spd) self.setCell(ws.cell(row=24,column=wsCol+0+i),'R','F',metrics.codes.mod.qor) self.setCell(ws.cell(row=25,column=wsCol+0+i),'R','F',metrics.codes.mod.ter) self.setCell(ws.cell(row=27,column=wsCol+0+i),'R','F',metrics.codes.sem.air) self.setCell(ws.cell(row=28,column=wsCol+0+i),'R','F',metrics.codes.sem.van) self.setCell(ws.cell(row=30,column=wsCol+0+i),'R','F',metrics.codes.oth.oth) self.setCell(ws.cell(row=31,column=wsCol+0+i),'R','F',metrics.codes.oth.cob) self.setCell(ws.cell(row=32,column=wsCol+0+i),'R','F',metrics.codes.oth.ttt) self.setCell(ws.cell(row=34,column=wsCol+0+i),'R','F',metrics.codes.ovr.x4x) self.setCell(ws.cell(row=35,column=wsCol+0+i),'R','F',metrics.codes.ovr.x1x) self.setCell(ws.cell(row=37,column=wsCol+0+i),'R','F',metrics.team.dmr) self.setCell(ws.cell(row=38,column=wsCol+0+i),'R','F',metrics.team.mi) self.setCell(ws.cell(row=40,column=wsCol+0+i),'R','F',metrics.total.tot) self.setCell(ws.cell(row=41,column=wsCol+0+i),'R','F',metrics.total.prm) self.setCell(ws.cell(row=42,column=wsCol+0+i),'R','F',metrics.total.con) self.setCell(ws.cell(row=43,column=wsCol+0+i),'R','F',metrics.total.lbr) self.setCell(ws.cell(row=44,column=wsCol+0+i),'R','F',metrics.total.trv) self.setCell(ws.cell(row=45,column=wsCol+0+i),'R','F',metrics.total.sby) rowoffs = 0 for fae in team.list: name = fae.fullname.GetVal() if (name in metrics.fae.dict): hours = metrics.fae.dict[name].hours else: hours = None self.setCell(ws.cell(row=47+rowoffs,column=wsCol+0+i),'R','F',hours) if (hours == None): weDate = wsDate + datetime.timedelta(days=4) sDate = team.dict[name].startDate.GetVal() tDate = team.dict[name].endDate.GetVal() if (wsDate >= sDate and weDate <= tDate): self.flagCell(ws.cell(row=27+rowoffs,column=wsCol+0+i)) rowoffs += 1 #----------------------------------------------------------------------- def Sort(self,tsdata,week): self.sslist = [] for i in range(1,week+1): list = [] dict = {} wsDate = Calendar.week[i] dict = tsdata.weeks[wsDate] for key,value in dict.items(): list.append(value) self.sslist.append(sorted(list))
def print_mat_desag(MATA, MATB,incertitude, UP_list_desag,EF_list): from openpyxl.workbook import Workbook from openpyxl.worksheet import Worksheet from copy import copy from time import time from scipy.sparse import lil_matrix, find filename = 'MAT.xlsx' wb = Workbook(encoding = 'mac_roman') Ws=wb.active wb.remove_sheet(Ws) #creation de la matrice A avec sa matrice d'incertitude Ws = Worksheet(wb, title = 'Processus') for UP in UP_list_desag: line=[UP,UP_list_desag[UP]['origine']] line.append(UP_list_desag[UP]['process']) line.append(str(UP_list_desag[UP]['enfants'])) Ws.append(line) wb.add_sheet(Ws) Ws = Worksheet(wb, title = 'A') Ws.freeze_panes = 'B2' mat=MATA.toarray() header = ['A'] for UP in UP_list_desag: header.append(UP_list_desag[UP]['process']) Ws.append(header) for row in range(len(mat)): line=[row] for col in range(len(mat[row])): line.append(mat[row][col]) Ws.append(line) wb.add_sheet(Ws) Ws = Worksheet(wb, title = 'incertitude A') Ws.freeze_panes = 'B2' mat=incertitude['technology'] header = ['incertitude A'] for UP in UP_list_desag: header.append(UP_list_desag[UP]['process']) Ws.append(header) for row in range(len(mat)): line=[row] for col in range(len(mat)): line.append(mat[col][row]) Ws.append(line) wb.add_sheet(Ws) #Creation de B avec sa matrice d'incertitude Ws = Worksheet(wb, title = 'Flux elem') Ws.freeze_panes = 'A2' header = ['id','compartment', 'substance', 'subcompartment'] Ws.append(header) for EF in EF_list: L=[EF_list.index(EF)] L.append(EF) Ws.append(L) wb.add_sheet(Ws) Ws = Worksheet(wb, title = 'B') Ws.freeze_panes = 'B2' mat=MATB.toarray() header = ['B'] for UP in UP_list_desag: header.append(UP_list_desag[UP]['process']) Ws.append(header) for row in range(len(mat)): line=[row] for col in range(len(mat[row])): line.append(mat[row][col]) Ws.append(line) wb.add_sheet(Ws) Ws = Worksheet(wb, title = 'incertitude B') Ws.freeze_panes = 'B2' mat=incertitude['intervention'] header = ['incertitude B'] for UP in UP_list_desag: header.append(UP_list_desag[UP]['process']) Ws.append(header) for row in range(len(mat[0])): line=[row] for col in range(len(mat)): line.append(mat[col][row]) Ws.append(line) wb.add_sheet(Ws) wb.save(filename) print 'fichier enregistre : MAT.xlsx'
def generate_validation_tally_for(entity, period, save_to=None): from dmd.models.DataRecords import DataRecord # colors black = 'FF000000' dark_gray = 'FFA6A6A6' light_gray = 'FFDEDEDE' # styles header_font = Font( name='Calibri', size=12, bold=True, italic=False, vertAlign=None, underline='none', strike=False, color=black) std_font = Font( name='Calibri', size=12, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color=black) header_fill = PatternFill(fill_type=FILL_SOLID, start_color=dark_gray) thin_black_side = Side(style='thin', color='FF000000') std_border = Border( left=thin_black_side, right=thin_black_side, top=thin_black_side, bottom=thin_black_side, ) centered_alignment = Alignment( horizontal='center', vertical='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) left_alignment = Alignment( horizontal='left', vertical='center') number_format = '# ### ### ##0' header_style = { 'font': header_font, 'fill': header_fill, 'border': std_border, 'alignment': centered_alignment, } std_style = { 'font': std_font, 'border': std_border, 'alignment': centered_alignment, 'number_format': number_format, } name_style = { 'font': std_font, 'border': std_border, 'alignment': left_alignment, 'number_format': number_format, } odd_fill = PatternFill(fill_type=FILL_SOLID, start_color=light_gray) col_number = 1 col_indicator = 2 col_numerator = 3 col_denominator = 4 col_human = 5 col_valid = 6 col_new_numerator = 7 col_new_denominator = 8 last_col = col_new_denominator def apply_style(target, style): for key, value in style.items(): setattr(target, key, value) title = "Validation {dps} {period}".format( dps=entity.short_name, period=period.strid) logger.info(title) wb = Workbook() wb.remove_sheet(wb.active) # write a sheet for each ZS for zs in entity.get_children(): ws = wb.create_sheet() ws.title = zs.short_name def std_write(row, column, value, style=std_style): cell = ws.cell(row=row, column=column) cell.value = value apply_style(cell, style) # write header row = 1 std_write(row, col_number, "#", header_style) xl_set_col_width(ws, col_number, 1.6) std_write(row, col_indicator, "Indicateur", header_style) xl_set_col_width(ws, col_indicator, 37.3) std_write(row, col_numerator, "Numérateur", header_style) xl_set_col_width(ws, col_numerator, 2.8) std_write(row, col_denominator, "Dénominateur", header_style) xl_set_col_width(ws, col_denominator, 3) std_write(row, col_human, "Valeur.", header_style) std_write(row, col_valid, "OK ?.", header_style) std_write(row, col_new_numerator, "Numérateur.", header_style) xl_set_col_width(ws, col_new_numerator, 2.8) std_write(row, col_new_denominator, "Dénominateur", header_style) xl_set_col_width(ws, col_new_denominator, 3) row += 1 # one row per indicator for indicator in Indicator.objects.all(): dr = DataRecord.get_or_none( period=period, entity=zs, indicator=indicator, only_validated=False) std_write(row, col_number, indicator.number, std_style) std_write(row, col_indicator, indicator.name, name_style) std_write(row, col_numerator, "" if dr is None else dr.numerator, std_style) std_write(row, col_denominator, "" if dr is None else dr.denominator, std_style) std_write(row, col_human, "" if dr is None else dr.human(), std_style) std_write(row, col_valid, "", std_style) std_write(row, col_new_numerator, "", std_style) std_write(row, col_new_denominator, "", std_style) row += 1 # apply even/odd style for r in range(1, row): if r % 2 == 0: for c in range(1, last_col + 1): ws.cell(row=r, column=c).fill = odd_fill if save_to: logger.info("saving to {}".format(save_to)) wb.save(save_to) return stream = StringIO.StringIO() wb.save(stream) return stream
class _OpenpyxlWriter(ExcelWriter): engine = 'openpyxl' supported_extensions = ('.xlsx', '.xlsm') def __init__(self, path, engine=None, mode='w', **engine_kwargs): # Use the openpyxl module as the Excel writer. from openpyxl.workbook import Workbook super().__init__(path, mode=mode, **engine_kwargs) if self.mode == 'a': # Load from existing workbook from openpyxl import load_workbook book = load_workbook(self.path) self.book = book else: # Create workbook object with default optimized_write=True. self.book = Workbook() if self.book.worksheets: try: self.book.remove(self.book.worksheets[0]) except AttributeError: # compat - for openpyxl <= 2.4 self.book.remove_sheet(self.book.worksheets[0]) def save(self): """ Save workbook to disk. """ return self.book.save(self.path) @classmethod 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 @classmethod def _convert_to_style_kwargs(cls, style_dict): """ Convert a style_dict to a set of kwargs suitable for initializing or updating-on-copy an openpyxl v2 style object Parameters ---------- style_dict : dict A dict with zero or more of the following keys (or their synonyms). 'font' 'fill' 'border' ('borders') 'alignment' 'number_format' 'protection' Returns ------- style_kwargs : dict A dict with the same, normalized keys as ``style_dict`` but each value has been replaced with a native openpyxl style object of the appropriate class. """ _style_key_map = { 'borders': 'border', } style_kwargs = {} for k, v in style_dict.items(): if k in _style_key_map: k = _style_key_map[k] _conv_to_x = getattr(cls, '_convert_to_{k}'.format(k=k), lambda x: None) new_v = _conv_to_x(v) if new_v: style_kwargs[k] = new_v return style_kwargs @classmethod def _convert_to_color(cls, color_spec): """ Convert ``color_spec`` to an openpyxl v2 Color object Parameters ---------- color_spec : str, dict A 32-bit ARGB hex string, or a dict with zero or more of the following keys. 'rgb' 'indexed' 'auto' 'theme' 'tint' 'index' 'type' Returns ------- color : openpyxl.styles.Color """ from openpyxl.styles import Color if isinstance(color_spec, str): return Color(color_spec) else: return Color(**color_spec) @classmethod def _convert_to_font(cls, font_dict): """ Convert ``font_dict`` to an openpyxl v2 Font object Parameters ---------- font_dict : dict A dict with zero or more of the following keys (or their synonyms). 'name' 'size' ('sz') 'bold' ('b') 'italic' ('i') 'underline' ('u') 'strikethrough' ('strike') 'color' 'vertAlign' ('vertalign') 'charset' 'scheme' 'family' 'outline' 'shadow' 'condense' Returns ------- font : openpyxl.styles.Font """ from openpyxl.styles import Font _font_key_map = { 'sz': 'size', 'b': 'bold', 'i': 'italic', 'u': 'underline', 'strike': 'strikethrough', 'vertalign': 'vertAlign', } font_kwargs = {} for k, v in font_dict.items(): if k in _font_key_map: k = _font_key_map[k] if k == 'color': v = cls._convert_to_color(v) font_kwargs[k] = v return Font(**font_kwargs) @classmethod def _convert_to_stop(cls, stop_seq): """ Convert ``stop_seq`` to a list of openpyxl v2 Color objects, suitable for initializing the ``GradientFill`` ``stop`` parameter. Parameters ---------- stop_seq : iterable An iterable that yields objects suitable for consumption by ``_convert_to_color``. Returns ------- stop : list of openpyxl.styles.Color """ return map(cls._convert_to_color, stop_seq) @classmethod def _convert_to_fill(cls, fill_dict): """ Convert ``fill_dict`` to an openpyxl v2 Fill object Parameters ---------- fill_dict : dict A dict with one or more of the following keys (or their synonyms), 'fill_type' ('patternType', 'patterntype') 'start_color' ('fgColor', 'fgcolor') 'end_color' ('bgColor', 'bgcolor') or one or more of the following keys (or their synonyms). 'type' ('fill_type') 'degree' 'left' 'right' 'top' 'bottom' 'stop' Returns ------- fill : openpyxl.styles.Fill """ from openpyxl.styles import PatternFill, GradientFill _pattern_fill_key_map = { 'patternType': 'fill_type', 'patterntype': 'fill_type', 'fgColor': 'start_color', 'fgcolor': 'start_color', 'bgColor': 'end_color', 'bgcolor': 'end_color', } _gradient_fill_key_map = { 'fill_type': 'type', } pfill_kwargs = {} gfill_kwargs = {} for k, v in fill_dict.items(): pk = gk = None if k in _pattern_fill_key_map: pk = _pattern_fill_key_map[k] if k in _gradient_fill_key_map: gk = _gradient_fill_key_map[k] if pk in ['start_color', 'end_color']: v = cls._convert_to_color(v) if gk == 'stop': v = cls._convert_to_stop(v) if pk: pfill_kwargs[pk] = v elif gk: gfill_kwargs[gk] = v else: pfill_kwargs[k] = v gfill_kwargs[k] = v try: return PatternFill(**pfill_kwargs) except TypeError: return GradientFill(**gfill_kwargs) @classmethod def _convert_to_side(cls, side_spec): """ Convert ``side_spec`` to an openpyxl v2 Side object Parameters ---------- side_spec : str, dict A string specifying the border style, or a dict with zero or more of the following keys (or their synonyms). 'style' ('border_style') 'color' Returns ------- side : openpyxl.styles.Side """ from openpyxl.styles import Side _side_key_map = { 'border_style': 'style', } if isinstance(side_spec, str): return Side(style=side_spec) side_kwargs = {} for k, v in side_spec.items(): if k in _side_key_map: k = _side_key_map[k] if k == 'color': v = cls._convert_to_color(v) side_kwargs[k] = v return Side(**side_kwargs) @classmethod def _convert_to_border(cls, border_dict): """ Convert ``border_dict`` to an openpyxl v2 Border object Parameters ---------- border_dict : dict A dict with zero or more of the following keys (or their synonyms). 'left' 'right' 'top' 'bottom' 'diagonal' 'diagonal_direction' 'vertical' 'horizontal' 'diagonalUp' ('diagonalup') 'diagonalDown' ('diagonaldown') 'outline' Returns ------- border : openpyxl.styles.Border """ from openpyxl.styles import Border _border_key_map = { 'diagonalup': 'diagonalUp', 'diagonaldown': 'diagonalDown', } border_kwargs = {} for k, v in border_dict.items(): if k in _border_key_map: k = _border_key_map[k] if k == 'color': v = cls._convert_to_color(v) if k in ['left', 'right', 'top', 'bottom', 'diagonal']: v = cls._convert_to_side(v) border_kwargs[k] = v return Border(**border_kwargs) @classmethod def _convert_to_alignment(cls, alignment_dict): """ Convert ``alignment_dict`` to an openpyxl v2 Alignment object Parameters ---------- alignment_dict : dict A dict with zero or more of the following keys (or their synonyms). 'horizontal' 'vertical' 'text_rotation' 'wrap_text' 'shrink_to_fit' 'indent' Returns ------- alignment : openpyxl.styles.Alignment """ from openpyxl.styles import Alignment return Alignment(**alignment_dict) @classmethod def _convert_to_number_format(cls, number_format_dict): """ Convert ``number_format_dict`` to an openpyxl v2.1.0 number format initializer. Parameters ---------- number_format_dict : dict A dict with zero or more of the following keys. 'format_code' : str Returns ------- number_format : str """ return number_format_dict['format_code'] @classmethod def _convert_to_protection(cls, protection_dict): """ Convert ``protection_dict`` to an openpyxl v2 Protection object. Parameters ---------- protection_dict : dict A dict with zero or more of the following keys. 'locked' 'hidden' Returns ------- """ from openpyxl.styles import Protection return Protection(**protection_dict) def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0, freeze_panes=None): # Write the frame cells using openpyxl. sheet_name = self._get_sheet_name(sheet_name) _style_cache = {} if sheet_name in self.sheets: wks = self.sheets[sheet_name] else: wks = self.book.create_sheet() wks.title = sheet_name self.sheets[sheet_name] = wks if _validate_freeze_panes(freeze_panes): wks.freeze_panes = wks.cell(row=freeze_panes[0] + 1, column=freeze_panes[1] + 1) for cell in cells: xcell = wks.cell( row=startrow + cell.row + 1, column=startcol + cell.col + 1 ) xcell.value, fmt = self._value_with_fmt(cell.val) if fmt: xcell.number_format = fmt style_kwargs = {} if cell.style: key = str(cell.style) style_kwargs = _style_cache.get(key) if style_kwargs is None: style_kwargs = self._convert_to_style_kwargs(cell.style) _style_cache[key] = style_kwargs if style_kwargs: for k, v in style_kwargs.items(): setattr(xcell, k, v) if cell.mergestart is not None and cell.mergeend is not None: wks.merge_cells( start_row=startrow + cell.row + 1, start_column=startcol + cell.col + 1, end_column=startcol + cell.mergeend + 1, end_row=startrow + cell.mergestart + 1 ) # When cells are merged only the top-left cell is preserved # The behaviour of the other cells in a merged range is # undefined if style_kwargs: first_row = startrow + cell.row + 1 last_row = startrow + cell.mergestart + 1 first_col = startcol + cell.col + 1 last_col = startcol + cell.mergeend + 1 for row in range(first_row, last_row + 1): for col in range(first_col, last_col + 1): if row == first_row and col == first_col: # Ignore first cell. It is already handled. continue xcell = wks.cell(column=col, row=row) for k, v in style_kwargs.items(): setattr(xcell, k, v)
def generate_validation_tally_for(entity, period, save_to=None): from dmd.models.DataRecords import DataRecord # colors black = 'FF000000' dark_gray = 'FFA6A6A6' light_gray = 'FFDEDEDE' # styles header_font = Font(name='Calibri', size=12, bold=True, italic=False, vertAlign=None, underline='none', strike=False, color=black) std_font = Font(name='Calibri', size=12, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color=black) header_fill = PatternFill(fill_type=FILL_SOLID, start_color=dark_gray) thin_black_side = Side(style='thin', color='FF000000') std_border = Border( left=thin_black_side, right=thin_black_side, top=thin_black_side, bottom=thin_black_side, ) centered_alignment = Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) left_alignment = Alignment(horizontal='left', vertical='center') number_format = '# ### ### ##0' header_style = { 'font': header_font, 'fill': header_fill, 'border': std_border, 'alignment': centered_alignment, } std_style = { 'font': std_font, 'border': std_border, 'alignment': centered_alignment, 'number_format': number_format, } name_style = { 'font': std_font, 'border': std_border, 'alignment': left_alignment, 'number_format': number_format, } odd_fill = PatternFill(fill_type=FILL_SOLID, start_color=light_gray) col_number = 1 col_indicator = 2 col_numerator = 3 col_denominator = 4 col_human = 5 col_valid = 6 col_new_numerator = 7 col_new_denominator = 8 last_col = col_new_denominator def apply_style(target, style): for key, value in style.items(): setattr(target, key, value) title = "Validation {dps} {period}".format(dps=entity.short_name, period=period.strid) logger.info(title) wb = Workbook() wb.remove_sheet(wb.active) # write a sheet for each ZS for zs in entity.get_children(): ws = wb.create_sheet() ws.title = zs.short_name def std_write(row, column, value, style=std_style): cell = ws.cell(row=row, column=column) cell.value = value apply_style(cell, style) # write header row = 1 std_write(row, col_number, "#", header_style) xl_set_col_width(ws, col_number, 1.6) std_write(row, col_indicator, "Indicateur", header_style) xl_set_col_width(ws, col_indicator, 37.3) std_write(row, col_numerator, "Numérateur", header_style) xl_set_col_width(ws, col_numerator, 2.8) std_write(row, col_denominator, "Dénominateur", header_style) xl_set_col_width(ws, col_denominator, 3) std_write(row, col_human, "Valeur.", header_style) std_write(row, col_valid, "OK ?.", header_style) std_write(row, col_new_numerator, "Numérateur.", header_style) xl_set_col_width(ws, col_new_numerator, 2.8) std_write(row, col_new_denominator, "Dénominateur", header_style) xl_set_col_width(ws, col_new_denominator, 3) row += 1 # one row per indicator for indicator in Indicator.objects.all(): dr = DataRecord.get_or_none(period=period, entity=zs, indicator=indicator, only_validated=False) std_write(row, col_number, indicator.number, std_style) std_write(row, col_indicator, indicator.name, name_style) std_write(row, col_numerator, "" if dr is None else dr.numerator, std_style) std_write(row, col_denominator, "" if dr is None else dr.denominator, std_style) std_write(row, col_human, "" if dr is None else dr.human(), std_style) std_write(row, col_valid, "", std_style) std_write(row, col_new_numerator, "", std_style) std_write(row, col_new_denominator, "", std_style) row += 1 # apply even/odd style for r in range(1, row): if r % 2 == 0: for c in range(1, last_col + 1): ws.cell(row=r, column=c).fill = odd_fill if save_to: logger.info("saving to {}".format(save_to)) wb.save(save_to) return stream = StringIO.StringIO() wb.save(stream) return stream
time2 = time.time() print("Creation Time for all Functional Tables was ", time2-time1, "seconds.") ##Function 2: create_tabs time1 = time.time() for key in sorted(list(sheet_dict.keys()), reverse = True): create_tabs(sheet_dict[key], key) create_tabs(fullTable, 'Headcount Summary Sorted') time2 = time.time() #print "Length of all tables is", len(subsTable + estSTable + prjCTable + infoTable + procTable + legaTable + engiTable + humaTable + prjMTable + accoTable + ethiTable + hsesTable + qualTable) print("Creation Time for ALL tabs was ", time2-time1, "seconds.") #remove 'Sheet' worksheet, that gets created by default target.remove_sheet(target.get_sheet_by_name("Sheet")) #the .remove_sheet() function seems to REQUIRE a worksheet object, not just a name #Make headers bold; format will probably be the later home of functions for number & alignment formatting import format format.bold_headers_footers(target) ##Writing that worksheet to a file ##Moved later in file, so Exceptions sheet could be written end = time.time() dur = end - start print("Total processing time", dur) def funcSheets_check_figures(d, ft): '''
class _Openpyxl1Writer(ExcelWriter): engine = 'openpyxl1' supported_extensions = ('.xlsx', '.xlsm') openpyxl_majorver = 1 def __init__(self, path, engine=None, **engine_kwargs): if not openpyxl_compat.is_compat(major_ver=self.openpyxl_majorver): raise ValueError('Installed openpyxl is not supported at this ' 'time. Use {0}.x.y.'.format( self.openpyxl_majorver)) # Use the openpyxl module as the Excel writer. from openpyxl.workbook import Workbook super(_Openpyxl1Writer, self).__init__(path, **engine_kwargs) # Create workbook object with default optimized_write=True. self.book = Workbook() # Openpyxl 1.6.1 adds a dummy sheet. We remove it. if self.book.worksheets: self.book.remove_sheet(self.book.worksheets[0]) def save(self): """ Save workbook to disk. """ return self.book.save(self.path) def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0): # Write the frame cells using openpyxl. from openpyxl.cell import get_column_letter sheet_name = self._get_sheet_name(sheet_name) if sheet_name in self.sheets: wks = self.sheets[sheet_name] else: wks = self.book.create_sheet() wks.title = sheet_name self.sheets[sheet_name] = wks for cell in cells: colletter = get_column_letter(startcol + cell.col + 1) xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1)) xcell.value = _conv_value(cell.val) style = None if cell.style: style = self._convert_to_style(cell.style) for field in style.__fields__: xcell.style.__setattr__(field, style.__getattribute__(field)) if isinstance(cell.val, datetime.datetime): xcell.style.number_format.format_code = self.datetime_format elif isinstance(cell.val, datetime.date): xcell.style.number_format.format_code = self.date_format if cell.mergestart is not None and cell.mergeend is not None: cletterstart = get_column_letter(startcol + cell.col + 1) cletterend = get_column_letter(startcol + cell.mergeend + 1) wks.merge_cells('%s%s:%s%s' % (cletterstart, startrow + cell.row + 1, cletterend, startrow + cell.mergestart + 1)) # Excel requires that the format of the first cell in a merged # range is repeated in the rest of the merged range. if style: first_row = startrow + cell.row + 1 last_row = startrow + cell.mergestart + 1 first_col = startcol + cell.col + 1 last_col = startcol + cell.mergeend + 1 for row in range(first_row, last_row + 1): for col in range(first_col, last_col + 1): if row == first_row and col == first_col: # Ignore first cell. It is already handled. continue colletter = get_column_letter(col) xcell = wks.cell("%s%s" % (colletter, row)) for field in style.__fields__: xcell.style.__setattr__( field, style.__getattribute__(field)) @classmethod 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
class ExcelWriter(object): """ Class for writing DataFrame objects into excel sheets, uses xlwt for xls, openpyxl for xlsx. See DataFrame.to_excel for typical usage. Parameters ---------- path : string Path to xls file """ def __init__(self, path): self.use_xlsx = True if path.endswith('.xls'): self.use_xlsx = False import xlwt self.book = xlwt.Workbook() self.fm_datetime = xlwt.easyxf( num_format_str='YYYY-MM-DD HH:MM:SS') self.fm_date = xlwt.easyxf(num_format_str='YYYY-MM-DD') else: from openpyxl.workbook import Workbook self.book = Workbook() # optimized_write=True) # open pyxl 1.6.1 adds a dummy sheet remove it if self.book.worksheets: self.book.remove_sheet(self.book.worksheets[0]) self.path = path self.sheets = {} self.cur_sheet = None def save(self): """ Save workbook to disk """ self.book.save(self.path) def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0): """ Write given formated cells into Excel an excel sheet Parameters ---------- cells : generator cell of formated data to save to Excel sheet sheet_name : string, default None Name of Excel sheet, if None, then use self.cur_sheet startrow: upper left cell row to dump data frame startcol: upper left cell column to dump data frame """ if sheet_name is None: sheet_name = self.cur_sheet if sheet_name is None: # pragma: no cover raise Exception('Must pass explicit sheet_name or set ' 'cur_sheet property') if self.use_xlsx: self._writecells_xlsx(cells, sheet_name, startrow, startcol) else: self._writecells_xls(cells, sheet_name, startrow, startcol) def _writecells_xlsx(self, cells, sheet_name, startrow, startcol): from openpyxl.cell import get_column_letter if sheet_name in self.sheets: wks = self.sheets[sheet_name] else: wks = self.book.create_sheet() wks.title = sheet_name self.sheets[sheet_name] = wks for cell in cells: colletter = get_column_letter(startcol + cell.col + 1) xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1)) xcell.value = _conv_value(cell.val) if cell.style: style = CellStyleConverter.to_xlsx(cell.style) for field in style.__fields__: xcell.style.__setattr__(field, style.__getattribute__(field)) if isinstance(cell.val, datetime.datetime): xcell.style.number_format.format_code = "YYYY-MM-DD HH:MM:SS" elif isinstance(cell.val, datetime.date): xcell.style.number_format.format_code = "YYYY-MM-DD" # merging requires openpyxl latest (works on 1.6.1) # todo add version check if cell.mergestart is not None and cell.mergeend is not None: cletterstart = get_column_letter(startcol + cell.col + 1) cletterend = get_column_letter(startcol + cell.mergeend + 1) wks.merge_cells('%s%s:%s%s' % (cletterstart, startrow + cell.row + 1, cletterend, startrow + cell.mergestart + 1)) def _writecells_xls(self, cells, sheet_name, startrow, startcol): if sheet_name in self.sheets: wks = self.sheets[sheet_name] else: wks = self.book.add_sheet(sheet_name) self.sheets[sheet_name] = wks style_dict = {} for cell in cells: val = _conv_value(cell.val) num_format_str = None if isinstance(cell.val, datetime.datetime): num_format_str = "YYYY-MM-DD HH:MM:SS" if isinstance(cell.val, datetime.date): num_format_str = "YYYY-MM-DD" stylekey = json.dumps(cell.style) if num_format_str: stylekey += num_format_str if stylekey in style_dict: style = style_dict[stylekey] else: style = CellStyleConverter.to_xls(cell.style, num_format_str) style_dict[stylekey] = style if cell.mergestart is not None and cell.mergeend is not None: wks.write_merge(startrow + cell.row, startrow + cell.mergestart, startcol + cell.col, startcol + cell.mergeend, val, style) else: wks.write(startrow + cell.row, startcol + cell.col, val, style)
print("Creation Time for all Functional Tables was ", time2 - time1, "seconds.") ##Function 2: create_tabs time1 = time.time() for key in sorted(sheet_dict.keys(), reverse=True): create_tabs(sheet_dict[key], key) create_tabs(fullTable, 'Headcount Summary Sorted') time2 = time.time() #print "Length of all tables is", len(subsTable + estSTable + prjCTable + infoTable + procTable + legaTable + engiTable + humaTable + prjMTable + accoTable + ethiTable + hsesTable + qualTable) print("Creation Time for ALL tabs was ", time2 - time1, "seconds.") #remove 'Sheet' worksheet, that gets created by default target.remove_sheet( target.get_sheet_by_name("Sheet") ) #the .remove_sheet() function seems to REQUIRE a worksheet object, not just a name #Make headers bold; format will probably be the later home of functions for number & alignment formatting # import format # format.bold_headers_footers(target) ##Writing that worksheet to a file ##Moved later in file, so Exceptions sheet could be written end = time.time() dur = end - start print("Total processing time", dur) def funcSheets_check_figures(d, ft):
class _OpenpyxlWriter(ExcelWriter): engine = 'openpyxl' supported_extensions = ('.xlsx', '.xlsm') def __init__(self, path, engine=None, **engine_kwargs): # Use the openpyxl module as the Excel writer. from openpyxl.workbook import Workbook super(_OpenpyxlWriter, self).__init__(path, **engine_kwargs) # Create workbook object with default optimized_write=True. self.book = Workbook() # Openpyxl 1.6.1 adds a dummy sheet. We remove it. if self.book.worksheets: self.book.remove_sheet(self.book.worksheets[0]) def save(self): """ Save workbook to disk. """ return self.book.save(self.path) def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0): # Write the frame cells using openpyxl. from openpyxl.cell import get_column_letter sheet_name = self._get_sheet_name(sheet_name) if sheet_name in self.sheets: wks = self.sheets[sheet_name] else: wks = self.book.create_sheet() wks.title = sheet_name self.sheets[sheet_name] = wks for cell in cells: colletter = get_column_letter(startcol + cell.col + 1) xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1)) xcell.value = _conv_value(cell.val) style = None if cell.style: style = self._convert_to_style(cell.style) for field in style.__fields__: xcell.style.__setattr__(field, style.__getattribute__(field)) if isinstance(cell.val, datetime.datetime): xcell.style.number_format.format_code = self.datetime_format elif isinstance(cell.val, datetime.date): xcell.style.number_format.format_code = self.date_format if cell.mergestart is not None and cell.mergeend is not None: cletterstart = get_column_letter(startcol + cell.col + 1) cletterend = get_column_letter(startcol + cell.mergeend + 1) wks.merge_cells('%s%s:%s%s' % (cletterstart, startrow + cell.row + 1, cletterend, startrow + cell.mergestart + 1)) # Excel requires that the format of the first cell in a merged # range is repeated in the rest of the merged range. if style: first_row = startrow + cell.row + 1 last_row = startrow + cell.mergestart + 1 first_col = startcol + cell.col + 1 last_col = startcol + cell.mergeend + 1 for row in range(first_row, last_row + 1): for col in range(first_col, last_col + 1): if row == first_row and col == first_col: # Ignore first cell. It is already handled. continue colletter = get_column_letter(col) xcell = wks.cell("%s%s" % (colletter, row)) for field in style.__fields__: xcell.style.__setattr__( field, style.__getattribute__(field)) @classmethod 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