def parse_font(self, font_node): """Read individual font""" font = Font() fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS) if fontSizeEl is not None: font.size = fontSizeEl.get('val') fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS) if fontNameEl is not None: font.name = fontNameEl.get('val') bold = font_node.find('{%s}b' % SHEET_MAIN_NS) if bold is not None: font.bold = bool(bold.get('val', True)) italic = font_node.find('{%s}i' % SHEET_MAIN_NS) if italic is not None: font.italic = bool(italic.get('val', True)) underline = font_node.find('{%s}u' % SHEET_MAIN_NS) if underline is not None: font.underline = underline.get('val', 'single') strikethrough = font_node.find('{%s}strike' % SHEET_MAIN_NS) if strikethrough is not None: font.strikethrough = True color = font_node.find('{%s}color' % SHEET_MAIN_NS) if color is not None: font.color.index = self._get_relevant_color(color) return font
def parse_fonts(root, color_index): """Read in the fonts""" font_list = [] fonts = root.find('{%s}fonts' % SHEET_MAIN_NS) if fonts is not None: font_nodes = fonts.findall('{%s}font' % SHEET_MAIN_NS) for font_node in font_nodes: font = Font() fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS) if fontSizeEl is not None: font.size = fontSizeEl.get('val') fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS) if fontNameEl is not None: font.name = fontNameEl.get('val') font.bold = True if len(font_node.findall('{%s}b' % SHEET_MAIN_NS)) else False font.italic = True if len(font_node.findall('{%s}i' % SHEET_MAIN_NS)) else False if len(font_node.findall('{%s}u' % SHEET_MAIN_NS)): underline = font_node.find('{%s}u' % SHEET_MAIN_NS).get('val') font.underline = underline if underline else 'single' color = font_node.find('{%s}color' % SHEET_MAIN_NS) if color is not None: if color.get('indexed') is not None and 0 <= int(color.get('indexed')) < len(color_index): font.color.index = color_index[int(color.get('indexed'))] elif color.get('theme') is not None: if color.get('tint') is not None: font.color.index = 'theme:%s:%s' % (color.get('theme'), color.get('tint')) else: font.color.index = 'theme:%s:' % color.get('theme') # prefix color with theme elif color.get('rgb'): font.color.index = color.get('rgb') font_list.append(font) return font_list
def test_conditional_formatting_setDxfStyle(self): cf = ConditionalFormatting() fill = Fill() fill.start_color.index = 'FFEE1111' fill.end_color.index = 'FFEE1111' fill.fill_type = Fill.FILL_SOLID font = Font() font.name = 'Arial' font.size = 12 font.bold = True font.underline = Font.UNDERLINE_SINGLE borders = Borders() borders.top.border_style = Border.BORDER_THIN borders.top.color.index = Color.DARKYELLOW borders.bottom.border_style = Border.BORDER_THIN borders.bottom.color.index = Color.BLACK cf.add( 'C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=borders, fill=fill)) cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 2 assert self.workbook.style_properties['dxf_list'][0] == { 'font': font, 'border': borders, 'fill': fill } assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def styled_sheet(): from openpyxl import Workbook from openpyxl.styles import Font, Style, Fill, Color, colors wb = Workbook() ws = wb.active ws.title = 'Test 1' red_fill = Fill() red_fill.fill_type = 'solid' red_fill.start_color = Color(Color.RED), red_fill.end_color = Color(Color.RED) empty_fill = Fill() styles = [] # pregenerate relevant styles for row in range(ROWS): _row = [] for col in range(COLUMNS): cell = ws.cell(row=row + 1, column=col + 1) cell.value = 1 font = Font() fill = Fill() if formatData[row][col] & BOLD: font.bold = True if formatData[row][col] & ITALIC: font.italic = True if formatData[row][col] & UNDERLINE: font.underline = 'single' if formatData[row][col] & RED_BG: fill = red_fill style = Style() style.font = font style.fill = fill ws._styles[cell.address] = style
def test_conditional_formatting_addDxfStyle(self): cf = ConditionalFormatting() fill = Fill() fill.start_color.index = 'FFEE1111' fill.end_color.index = 'FFEE1111' fill.fill_type = Fill.FILL_SOLID font = Font() font.name = 'Arial' font.size = 12 font.bold = True font.underline = Font.UNDERLINE_SINGLE borders = Borders() borders.top.border_style = Border.BORDER_THIN borders.top.color.index = Color.DARKYELLOW borders.bottom.border_style = Border.BORDER_THIN borders.bottom.color.index = Color.BLACK dxfId = cf.addDxfStyle(self.workbook, font, borders, fill) assert dxfId == 0 dxfId = cf.addDxfStyle(self.workbook, None, None, fill) assert dxfId == 1 assert len(self.workbook.style_properties['dxf_list']) == 2
def test_conditional_formatting_setDxfStyle(self): cf = ConditionalFormatting() fill = Fill() fill.start_color.index = 'FFEE1111' fill.end_color.index = 'FFEE1111' fill.fill_type = Fill.FILL_SOLID font = Font() font.name = 'Arial' font.size = 12 font.bold = True font.underline = Font.UNDERLINE_SINGLE borders = Borders() borders.top.border_style = Border.BORDER_THIN borders.top.color.index = Color.DARKYELLOW borders.bottom.border_style = Border.BORDER_THIN borders.bottom.color.index = Color.BLACK cf.add('C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=borders, fill=fill)) cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 2 assert self.workbook.style_properties['dxf_list'][0] == {'font': font, 'border': borders, 'fill': fill} assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def exportTable(matrix, fname, format, title=None): """Save data to a file Inputs matrix: array with data to save fname: name of file to save format: name of format to save csv | ods | xls | xlsx title: column title array, optional """ sheetTitle = unicode(QApplication.translate("pychemqt", "Table")) if fname.split(".")[-1] != format: fname+=".%s" % format # Format title if title: header = [] for ttl in title: line = unicode(ttl).split(os.linesep) if line[-1] != "[-]": line[-1] = "["+line[-1]+"]" header.append(" ".join(line)) c_newline=maketrans(os.linesep, " ") if format == "csv": import csv with open(fname, "w") as archivo: writer = csv.writer(archivo, delimiter='\t', quotechar='"', quoting=csv.QUOTE_NONE) # Add Data if title: writer.writerow([ttl.translate(c_newline) for ttl in header]) c_float=maketrans(".", ",") for row in matrix: writer.writerow([str(data).translate(c_float) for data in row]) elif format == "ods": import ezodf spreadsheet = ezodf.newdoc("ods", fname) sheets = spreadsheet.sheets sheet=ezodf.Table(sheetTitle) sheets+=sheet sheet.reset(size=(len(matrix)+1, len(matrix[0]))) # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i"%(spreadsheetColumn(i), 1)].set_value(ttl) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i"%(spreadsheetColumn(i), j+2)].set_value(data) spreadsheet.save() elif format == "xls": import xlwt spreadsheet = xlwt.Workbook() sheet = spreadsheet.add_sheet(sheetTitle) font = xlwt.Font() font.bold = True style = xlwt.XFStyle() style.font = font # Add Data if title: for i, ttl in enumerate(header): sheet.write(0, i, ttl, style) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet.write(j+1, i, data) spreadsheet.save(fname) elif format == "xlsx": import openpyxl from openpyxl.styles import Style, Font spreadsheet = openpyxl.Workbook() sheet = spreadsheet.active sheet.title = sheetTitle font1 = Font() font1.size = 9 font1.bold = True font2 = Font() font2.size = 9 # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i"%(spreadsheetColumn(i), 1)] = ttl sheet["%s%i"%(spreadsheetColumn(i), 1)].style.font= font1 for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i"%(spreadsheetColumn(i), j+2)] = data sheet["%s%i"%(spreadsheetColumn(i), j+2)].style.font = font2 spreadsheet.save(filename=fname) else: raise ValueError(QApplication.translate( "pychemqt", "Unsopported format") + " " + format)
def exportTable(matrix, fname, ext, title=None): """Save data to a file Inputs matrix: array with data to save fname: name of file to save ext: name of format to save csv | ods | xls | xlsx title: column title array, optional """ sheetTitle = QApplication.translate("pychemqt", "Table") if fname.split(".")[-1] != ext: fname += ".%s" % ext # Format title header = [] if title: for ttl in title: line = str(ttl).split(os.linesep) if line[-1] != "[-]": line[-1] = "[" + line[-1] + "]" header.append(" ".join(line)) if ext == "csv": import csv with open(fname, 'w') as csvfile: writer = csv.DictWriter(csvfile, fieldnames=header) writer.writeheader() for row in matrix: kw = {} for ttl, value in zip(header, row): kw[ttl] = value writer.writerow(kw) elif ext == "ods": import ezodf spreadsheet = ezodf.newdoc("ods", fname) sheets = spreadsheet.sheets sheet = ezodf.Table(sheetTitle) sheets += sheet sheet.reset(size=(len(matrix) + 1, len(matrix[0]))) # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i" % (spreadsheetColumn(i), 1)].set_value(ttl) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i" % (spreadsheetColumn(i), j + 2)].set_value(data) spreadsheet.save() elif ext == "xls": import xlwt spreadsheet = xlwt.Workbook() sheet = spreadsheet.add_sheet(sheetTitle) font = xlwt.Font() font.bold = True style = xlwt.XFStyle() style.font = font # Add Data if title: for i, ttl in enumerate(header): sheet.write(0, i, ttl, style) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet.write(j + 1, i, data) spreadsheet.save(fname) elif ext == "xlsx": import openpyxl from openpyxl.styles import Font spreadsheet = openpyxl.Workbook() sheet = spreadsheet.active sheet.title = sheetTitle font1 = Font() font1.size = 9 font1.bold = True font2 = Font() font2.size = 9 # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i" % (spreadsheetColumn(i), 1)] = ttl sheet["%s%i" % (spreadsheetColumn(i), 1)].style.font = font1 for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i" % (spreadsheetColumn(i), j + 2)] = data sheet["%s%i" % (spreadsheetColumn(i), j + 2)].style.font = font2 spreadsheet.save(filename=fname) else: raise ValueError( QApplication.translate("pychemqt", "Unsopported format") + " " + ext)
def do_comparison_report(ct: pd.DataFrame) -> Workbook: statuses = Status.objects.all().values_list('test_status', flat=True) wb = Workbook() ws = wb.active year, work_week, weekday = intel_calendar.ww_date() rdate = f'Validations comparison report: {year} ww{work_week}.{weekday}' ws['A2'] = rdate ws['A2'].font = Font(bold=True, size=14) ws.merge_cells('A2:G2') c_row = 3 # Starting table creation table_row_start, table_columns = c_row + 1, 1 table_row_end = c_row + 1 # DataFrame to cells rows = dataframe_to_rows(ct, index=True, header=True) for r_id, row in enumerate(rows, 1): table_columns = len(row) c_row += 1 table_row_end = c_row for c_id, value in enumerate(row, 1): ws.cell(row=c_row, column=c_id, value=value) if r_id == table_row_start: ws.cell(row=c_row, column=c_id).alignment = Alignment(wrap_text=True) font = Font() # coloring statuses if value in statuses: if value == 'Passed': font = Font(color='2E7D32') elif value == 'Failed': font = Font(color='B71C1C') elif value == 'Error': font = Font(color='D84315') elif value == 'Blocked': font = Font(color='616161') elif value == 'Skipped': font = Font(color='00838F') elif value == 'Canceled': font = Font(color='3E2723') font.bold = True ws.cell(row=c_row, column=c_id).alignment = Alignment(horizontal='center') ws.cell(row=c_row, column=c_id).font = font # Insert first column header name ws.cell(row=table_row_start, column=1, value='Item name') # First column max_first_column_len = max(map(len, list(ct.index))) # Format first and last row in "table" for c_id in range(1, table_columns + 1): ws.cell(row=table_row_start, column=c_id).font = Font(color=WHITE) ws.column_dimensions[to_letter(c_id)].width = max_first_column_len table = Table( ref= f'{to_letter(1)}{table_row_start}:{to_letter(table_columns)}{table_row_end - 1}', displayName='Results', tableStyleInfo=MEDIUM_STYLE) ws.add_table(table) # Remove empty row where DataFrame index name should be located ws.delete_rows(table_row_start + 1, 1) return wb
def exportTable(matrix, fname, ext, title=None): """Save data to a file Inputs matrix: array with data to save fname: name of file to save ext: name of format to save csv | ods | xls | xlsx title: column title array, optional """ sheetTitle = QApplication.translate("pychemqt", "Table") if fname.split(".")[-1] != ext: fname += ".%s" % ext # Format title header = [] if title: for ttl in title: line = str(ttl).split(os.linesep) if line[-1] != "[-]": line[-1] = "["+line[-1]+"]" header.append(" ".join(line)) if ext == "csv": import csv with open(fname, 'w') as csvfile: writer = csv.DictWriter(csvfile, fieldnames=header) writer.writeheader() for row in matrix: kw = {} for ttl, value in zip(header, row): kw[ttl] = value writer.writerow(kw) elif ext == "ods": import ezodf spreadsheet = ezodf.newdoc("ods", fname) sheets = spreadsheet.sheets sheet = ezodf.Table(sheetTitle) sheets += sheet sheet.reset(size=(len(matrix)+1, len(matrix[0]))) # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i" % (spreadsheetColumn(i), 1)].set_value(ttl) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i" % (spreadsheetColumn(i), j+2)].set_value(data) spreadsheet.save() elif ext == "xls": import xlwt spreadsheet = xlwt.Workbook() sheet = spreadsheet.add_sheet(sheetTitle) font = xlwt.Font() font.bold = True style = xlwt.XFStyle() style.font = font # Add Data if title: for i, ttl in enumerate(header): sheet.write(0, i, ttl, style) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet.write(j+1, i, data) spreadsheet.save(fname) elif ext == "xlsx": import openpyxl from openpyxl.styles import Font spreadsheet = openpyxl.Workbook() sheet = spreadsheet.active sheet.title = sheetTitle font1 = Font() font1.size = 9 font1.bold = True font2 = Font() font2.size = 9 # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i" % (spreadsheetColumn(i), 1)] = ttl sheet["%s%i" % (spreadsheetColumn(i), 1)].style.font = font1 for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i" % (spreadsheetColumn(i), j+2)] = data sheet["%s%i" % (spreadsheetColumn(i), j+2)].style.font = font2 spreadsheet.save(filename=fname) else: raise ValueError(QApplication.translate( "pychemqt", "Unsopported format") + " " + ext)
def test_write_dxf(self): redFill = Fill() redFill.start_color.index = 'FFEE1111' redFill.end_color.index = 'FFEE1111' redFill.fill_type = Fill.FILL_SOLID whiteFont = Font() whiteFont.color.index = "FFFFFFFF" whiteFont.bold = True whiteFont.italic = True whiteFont.underline = 'single' whiteFont.strikethrough = True blueBorder = Borders() blueBorder.left.border_style = 'medium' blueBorder.left.color = Color(Color.BLUE) blueBorder.right.border_style = 'medium' blueBorder.right.color = Color(Color.BLUE) blueBorder.top.border_style = 'medium' blueBorder.top.color = Color(Color.BLUE) blueBorder.bottom.border_style = 'medium' blueBorder.bottom.color = Color(Color.BLUE) cf = ConditionalFormatting() cf.add('A1:A2', FormulaRule(formula="[A1=1]", font=whiteFont, border=blueBorder, fill=redFill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 1 assert 'font' in self.workbook.style_properties['dxf_list'][0] assert 'border' in self.workbook.style_properties['dxf_list'][0] assert 'fill' in self.workbook.style_properties['dxf_list'][0] w = StyleWriter(self.workbook) w._write_dxfs() xml = get_xml(w._root) diff = compare_xml(xml, """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <dxfs count="1"> <dxf> <font> <color rgb="FFFFFFFF" /> <b val="1" /> <i val="1" /> <u val="single" /> <strike /> </font> <fill> <patternFill patternType="solid"> <fgColor rgb="FFEE1111" /> <bgColor rgb="FFEE1111" /> </patternFill> </fill> <border> <left style="medium"> <color rgb="FF0000FF"></color> </left> <right style="medium"> <color rgb="FF0000FF"></color> </right> <top style="medium"> <color rgb="FF0000FF"></color> </top> <bottom style="medium"> <color rgb="FF0000FF"></color> </bottom> </border> </dxf> </dxfs> </styleSheet> """) assert diff is None, diff
def exportTable(matrix, fname, format, title=None): """Save data to a file Inputs matrix: array with data to save fname: name of file to save format: name of format to save csv | ods | xls | xlsx title: column title array, optional """ sheetTitle = unicode(QApplication.translate("pychemqt", "Table")) if fname.split(".")[-1] != format: fname += ".%s" % format # Format title if title: header = [] for ttl in title: line = unicode(ttl).split(os.linesep) if line[-1] != "[-]": line[-1] = "[" + line[-1] + "]" header.append(" ".join(line)) c_newline = maketrans(os.linesep, " ") if format == "csv": import csv with open(fname, "w") as archivo: writer = csv.writer(archivo, delimiter='\t', quotechar='"', quoting=csv.QUOTE_NONE) # Add Data if title: writer.writerow([ttl.translate(c_newline) for ttl in header]) c_float = maketrans(".", ",") for row in matrix: writer.writerow([str(data).translate(c_float) for data in row]) elif format == "ods": import ezodf spreadsheet = ezodf.newdoc("ods", fname) sheets = spreadsheet.sheets sheet = ezodf.Table(sheetTitle) sheets += sheet sheet.reset(size=(len(matrix) + 1, len(matrix[0]))) # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i" % (spreadsheetColumn(i), 1)].set_value(ttl) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i" % (spreadsheetColumn(i), j + 2)].set_value(data) spreadsheet.save() elif format == "xls": import xlwt spreadsheet = xlwt.Workbook() sheet = spreadsheet.add_sheet(sheetTitle) font = xlwt.Font() font.bold = True style = xlwt.XFStyle() style.font = font # Add Data if title: for i, ttl in enumerate(header): sheet.write(0, i, ttl, style) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet.write(j + 1, i, data) spreadsheet.save(fname) elif format == "xlsx": import openpyxl from openpyxl.styles import Style, Font spreadsheet = openpyxl.Workbook() sheet = spreadsheet.active sheet.title = sheetTitle font1 = Font() font1.size = 9 font1.bold = True font2 = Font() font2.size = 9 # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i" % (spreadsheetColumn(i), 1)] = ttl sheet["%s%i" % (spreadsheetColumn(i), 1)].style.font = font1 for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i" % (spreadsheetColumn(i), j + 2)] = data sheet["%s%i" % (spreadsheetColumn(i), j + 2)].style.font = font2 spreadsheet.save(filename=fname) else: raise ValueError( QApplication.translate("pychemqt", "Unsopported format") + " " + format)
def __init__(self, filename, sheet_name= None, font_name = "Calibri", font_size = 10, header_font_size = 14, header_bold = True, froze_first_row=True, add_fliter=True, header_height=25, add_border=True, alignment_center=True, specific_alignment_left = [], auto_fit_column_width=True, wrap_width=[], fill_color_header='95B3DF', hide_columns=[], column_width= {}, output_filename=None, home_page_bottom= None, whiten_nontable_area= False, zoom_level = 85, alignment_center_horizon = [], alignment_center_vertical = [] ): # load excel file and focus on one sheet if column_width is None: ## if sheet name is not defined, the app is default to apply format function to all sheets column_width = {} wb = load_workbook(filename) if sheet_name is None: sheet_name = wb.sheetnames elif type(sheet_name) == str: sheet_name = [sheet_name] # defind table, header, body for sheet_name_item in sheet_name: try: ws = wb[sheet_name_item] table = ws[ws.min_row:ws.max_row] header = ws[ws.min_row] if table == header: ## if there is only header in that sheet table = (table,) body = ws[ws.min_row + 1:ws.max_row] #font and font size font = Font(name=font_name, size=font_size, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') for tr in table: for td in tr: td.font = font font = Font(name=font_name, size=header_font_size, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') #font for header for td in header: td.font = font if header_bold: for td in header: font = copy(td.font) font.bold = True td.font = font ## frozen first row if froze_first_row: freeze_position = ws['A2'] ws.freeze_panes = freeze_position ## add filter on header if add_fliter: ws.auto_filter.ref = ws.dimensions ## set header height header_obj = ws.row_dimensions[1] header_obj.height = header_height ## add_border if add_border: border = Border(left=Side(border_style='thin', color='FF000000'), right=Side(border_style='thin', color='FF000000'), top=Side(border_style='thin', color='FF000000'), bottom=Side(border_style='thin', color='FF000000'), diagonal=Side(border_style=None, color='FF000000'), diagonal_direction=0, outline=Side(border_style=None, color='FF000000'), vertical=Side(border_style=None, color='FF000000'), horizontal=Side(border_style=None, color='FF000000')) for tr in table: for td in tr: td.border = border ## alignment_center if alignment_center: alignment = Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) for tr in table: for td in tr: td.alignment = alignment ## for those who have left top alignment for col in specific_alignment_left: column = ws[col] for td in column: alignment = copy(td.alignment) alignment.horizontal = 'left' alignment.vertical = 'top' td.alignment = alignment ## for those who have horizontal center alignment for col in alignment_center_horizon: column = ws[col] for td in column: alignment = copy(td.alignment) alignment.horizontal = 'center' td.alignment = alignment ## for those who have vertical center alignment for col in alignment_center_vertical: column = ws[col] for td in column: alignment = copy(td.alignment) alignment.vertical = 'center' td.alignment = alignment ###header do not change if alignment_center: for cell in header: alignment = copy(cell.alignment) alignment.horizontal = 'center' alignment.vertical = 'center' cell.alignment = alignment ## auto fit column width if auto_fit_column_width: column_widths = [] for i, cell in enumerate(header): try: column_widths[i] = len(cell.value) except IndexError: column_widths.append(len(cell.value)) for i, width in enumerate(column_widths): ws.column_dimensions[get_column_letter(i + 1)].width = width * 1.3 + 5 ##choose column width (specific) for col in column_width: ws.column_dimensions[col].width = column_width[col] ## wrap for col in wrap_width: column = ws[col] for td in column: alignment = copy(td.alignment) #alignment.horizontal = 'left' #alignment.vertical = 'top' alignment.wrap_text = True td.alignment = alignment #ws.column_dimensions[col].width = 55 ## header do not change alignment.horizontal ='center' alignment.vertical = 'center' ws[col + "1"].alignment=alignment ##fill header color if fill_color_header is not None: fill = PatternFill(start_color=fill_color_header, end_color=fill_color_header, fill_type="solid") for cell in header: cell.fill = fill ##hide columns for col in hide_columns: ws.column_dimensions[col].hidden = True if home_page_bottom is not None: offset = 4 No_row = ws.max_row + offset Home_Bottom = ws[get_column_letter(1) + str(No_row - 1)] Home_Bottom.value = f'=HYPERLINK("#{home_page_bottom}!A1","Home Page")' Home_Bottom.font = Font(size=15, bold=True, color='95B3DF') ##whiten_nontable_area if whiten_nontable_area: ### define area to whiten whiten_col = ws.max_column + 2 whiten_row = ws.max_row + 2 whiten_area1 = ws[get_column_letter(whiten_col) + str(1):"AJ200"] whiten_area2 = ws['A' + str(whiten_row): get_column_letter(whiten_col - 1) + str(200)] whiten_area_col = ws[get_column_letter(whiten_col - 1) + str(1):get_column_letter(whiten_col - 1) + str(200)] whiten_area_row = ws['A' + str(whiten_row - 1): get_column_letter(whiten_col - 1) + str(whiten_row - 1)] color = 'FFFFFFFF' font = Font(bold=False, italic=False, vertAlign=None, underline='none', strike=False, color=color) fill = PatternFill(start_color=color, end_color=color, fill_type="solid") for tr in whiten_area1: for td in tr: td.font = font td.border = None td.fill = fill for tr in whiten_area2: for td in tr: td.font = font td.border = None td.fill = fill for tr in whiten_area_col: for td in tr: td.font = font # td.border = None td.fill = fill for tr in whiten_area_row: for td in tr: td.font = font # td.border = None td.fill = fill ## adjust zoom level for ws in wb.worksheets: ws.sheet_view.zoomScale = zoom_level except: pass if output_filename is None: wb.save(filename=filename) else: wb.save(filename=output_filename) #formatter('test.xlsx', sheet_name='raw_data', wrap_width=['J'],column_width={'J':50},header_height=36,output_filename='dsad.xlsx',home_page_bottom='Summary',whiten_nontable_area=True ) #formatter(filename="test.xlsx", hide_columns=["C","D","F","D","S","U","T","W"]) #excel_file_name = 'test.xlsx' #formatter('test.xlsx',sheet_name = ['Sheet1','Sheet2','Sheet3'],output_filename='dsad.xlsx') #formatter('test.xlsx',output_filename='dsad.xlsx') #formatter('test.xlsx',sheet_name = 'Sheet1',output_filename='dsad1.xlsx')
def test_write_dxf(self): redFill = Fill() redFill.start_color.index = 'FFEE1111' redFill.end_color.index = 'FFEE1111' redFill.fill_type = Fill.FILL_SOLID whiteFont = Font() whiteFont.color.index = "FFFFFFFF" whiteFont.bold = True whiteFont.italic = True whiteFont.underline = 'single' whiteFont.strikethrough = True blueBorder = Borders() blueBorder.left.border_style = 'medium' blueBorder.left.color = Color(Color.BLUE) blueBorder.right.border_style = 'medium' blueBorder.right.color = Color(Color.BLUE) blueBorder.top.border_style = 'medium' blueBorder.top.color = Color(Color.BLUE) blueBorder.bottom.border_style = 'medium' blueBorder.bottom.color = Color(Color.BLUE) cf = ConditionalFormatting() cf.add( 'A1:A2', FormulaRule(formula="[A1=1]", font=whiteFont, border=blueBorder, fill=redFill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 1 assert 'font' in self.workbook.style_properties['dxf_list'][0] assert 'border' in self.workbook.style_properties['dxf_list'][0] assert 'fill' in self.workbook.style_properties['dxf_list'][0] w = StyleWriter(self.workbook) w._write_dxfs() xml = get_xml(w._root) diff = compare_xml( xml, """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <dxfs count="1"> <dxf> <font> <color rgb="FFFFFFFF" /> <b val="1" /> <i val="1" /> <u val="single" /> <strike /> </font> <fill> <patternFill patternType="solid"> <fgColor rgb="FFEE1111" /> <bgColor rgb="FFEE1111" /> </patternFill> </fill> <border> <left style="medium"> <color rgb="FF0000FF"></color> </left> <right style="medium"> <color rgb="FF0000FF"></color> </right> <top style="medium"> <color rgb="FF0000FF"></color> </top> <bottom style="medium"> <color rgb="FF0000FF"></color> </bottom> </border> </dxf> </dxfs> </styleSheet> """) assert diff is None, diff