def test_read_complex_style(datadir): datadir.chdir() wb = load_workbook("complex-styles.xlsx") ws = wb.active assert ws.column_dimensions['A'].width == 31.1640625 assert ws.column_dimensions['I'].font == Font(name="Calibri", sz=12.0, color='FF3300FF', scheme='minor') assert ws.column_dimensions['I'].fill == PatternFill(patternType='solid', fgColor='FF006600', bgColor=Color(indexed=64)) assert ws['A2'].font == Font(sz=10, name='Arial', color=Color(theme=1)) assert ws['A3'].font == Font(sz=12, name='Arial', bold=True, color=Color(theme=1)) assert ws['A4'].font == Font(sz=14, name='Arial', italic=True, color=Color(theme=1)) assert ws['A5'].font.color.value == 'FF3300FF' assert ws['A6'].font.color.value == 9 assert ws['A7'].fill.start_color.value == 'FFFFFF66' assert ws['A8'].fill.start_color.value == 8 assert ws['A9'].alignment.horizontal == 'left' assert ws['A10'].alignment.horizontal == 'right' assert ws['A11'].alignment.horizontal == 'center' assert ws['A12'].alignment.vertical == 'top' assert ws['A13'].alignment.vertical == 'center' assert ws['A15'].number_format == '0.00' assert ws['A16'].number_format == 'mm-dd-yy' assert ws['A17'].number_format == '0.00%' assert 'A18:B18' in ws.merged_cells assert ws['A19'].border == Border( left=Side(style='thin', color='FF006600'), top=Side(style='thin', color='FF006600'), right=Side(style='thin', color='FF006600'), bottom=Side(style='thin', color='FF006600'), ) assert ws['A21'].border == Border( left=Side(style='double', color=Color(theme=7)), top=Side(style='double', color=Color(theme=7)), right=Side(style='double', color=Color(theme=7)), bottom=Side(style='double', color=Color(theme=7)), ) assert ws['A23'].fill == PatternFill(patternType='solid', start_color='FFCCCCFF', end_color=(Color(indexed=64))) assert ws['A23'].border.top == Side(style='mediumDashed', color=Color(theme=6)) assert 'A23:B24' in ws.merged_cells assert ws['A25'].alignment == Alignment(wrapText=True) assert ws['A26'].alignment == Alignment(shrinkToFit=True)
def test_write_conditional_formatting(self): ws = self.ws cf = ConditionalFormattingList() ws.conditional_formatting = cf fill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType=fills.FILL_SOLID) font = Font(name='Arial', size=12, bold=True, underline=Font.UNDERLINE_SINGLE) border = Border(top=Side(border_style=borders.BORDER_THIN, color=Color(colors.DARKYELLOW)), bottom=Side(border_style=borders.BORDER_THIN, color=Color(colors.BLACK))) cf.add( 'C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=border, fill=fill)) cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill)) from openpyexcel.writer.worksheet import write_conditional_formatting for _ in write_conditional_formatting(ws): pass # exhaust generator wb = ws.parent assert len(wb._differential_styles.styles) == 2 ft1, ft2 = wb._differential_styles.styles assert ft1.font == font assert ft1.border == border assert ft1.fill == fill assert ft2.fill == fill
def test_conditional_font(worksheet_with_cf, write_conditional_formatting): """Test to verify font style written correctly.""" # Create cf rule from openpyexcel.styles import PatternFill, Font, Color from openpyexcel.formatting.rule import CellIsRule redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType='solid') whiteFont = Font(color=Color("FFFFFFFF")) ws = worksheet_with_cf ws.conditional_formatting.add( 'A1:A3', CellIsRule(operator='equal', formula=['"Fail"'], stopIfTrue=False, font=whiteFont, fill=redFill)) cfs = write_conditional_formatting(ws) xml = b"" for cf in cfs: xml += tostring(cf) diff = compare_xml( xml, """ <conditionalFormatting sqref="A1:A3"> <cfRule operator="equal" priority="1" type="cellIs" dxfId="0" stopIfTrue="0"> <formula>"Fail"</formula> </cfRule> </conditionalFormatting> """) assert diff is None, diff
def test_fill(DummyWorksheet, Cell): from openpyexcel.styles import PatternFill fill = PatternFill(patternType="solid", fgColor="FF0000") ws = DummyWorksheet ws.parent._fills.add(fill) cell = Cell(ws, column='A', row=1) assert cell.fill == fill
def test_cellis_rule(): from ..rule import CellIsRule from openpyexcel.styles import PatternFill red_fill = PatternFill(start_color='FFEE1111', end_color='FFEE1111', fill_type='solid') rule = CellIsRule(operator='<', formula=['C$1'], stopIfTrue=True, fill=red_fill) assert dict(rule) == {'operator': 'lessThan', 'priority': '0', 'type': 'cellIs', 'stopIfTrue':'1'} assert rule.formula == ['C$1'] assert rule.dxf.fill == red_fill
def test_parse(DifferentialStyle, datadir): datadir.chdir() with open("dxf_style.xml") as content: src = content.read() xml = fromstring(src) formats = [] for node in xml.findall("{%s}dxfs/{%s}dxf" % (SHEET_MAIN_NS, SHEET_MAIN_NS)): formats.append(DifferentialStyle.from_tree(node)) assert len(formats) == 164 cond = formats[1] assert cond.font == Font(underline="double", b=False, color=Color(auto=1), strikethrough=True, italic=True) assert cond.fill == PatternFill(end_color='FFFFC7CE')
def test_serialise(DifferentialStyle): cond = DifferentialStyle() cond.font = Font(name="Calibri", family=2, sz=11) cond.fill = PatternFill() xml = tostring(cond.to_tree()) expected = """ <dxf> <font> <name val="Calibri"></name> <family val="2"></family> <sz val="11"></sz> </font> <fill> <patternFill /> </fill> </dxf> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_conditional_font(self): """Test to verify font style written correctly.""" ws = self.ws cf = ConditionalFormattingList() ws.conditional_formatting = cf # Create cf rule redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType=fills.FILL_SOLID) whiteFont = Font(color=Color("FFFFFFFF")) ws.conditional_formatting.add( 'A1:A3', CellIsRule(operator='equal', formula=['"Fail"'], stopIfTrue=False, font=whiteFont, fill=redFill)) from openpyexcel.writer.worksheet import write_conditional_formatting # First, verify conditional formatting xml cfs = write_conditional_formatting(ws) xml = b"" for cf in cfs: xml += tostring(cf) diff = compare_xml( xml, """ <conditionalFormatting sqref="A1:A3"> <cfRule dxfId="0" operator="equal" priority="1" type="cellIs" stopIfTrue="0"> <formula>"Fail"</formula> </cfRule> </conditionalFormatting> """) assert diff is None, diff
def writer(self): wb = openpyexcel.Workbook() ws = wb.active # Название листа ws.title = 'Свежая сборка статей' # Заголовки head = ['№', 'Название', 'Дата', 'Описание', 'Ссылка'] ws.append(head) # Закрашиваем заголовки fill = PatternFill(fill_type='solid', start_color='FF0000', end_color='FF0000') ws['A1'].fill = fill ws['B1'].fill = fill ws['C1'].fill = fill ws['D1'].fill = fill ws['E1'].fill = fill # Формирование базы данных из словаря и запись в csv count = 1 for i in range(len(self.title)): data = [ count, self.title[i], self.date[i], self.descriptions[i], self.links[i] ] count += 1 ws.append(data) # Выравнивание align_left = Alignment(horizontal='left', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) for row in ws.rows: for cell in row: ws[cell.coordinate].alignment = align_left # Растягивание столбцов dims = {} for row in ws.rows: for cell in row: if cell.value: dims[cell.column] = max( (dims.get(cell.column, 0), len(str(cell.value)))) for col, value in dims.items(): if col == 4: ws.column_dimensions[col].width = value / 2 else: ws.column_dimensions[col].width = value filepath = "linuxoid.xlsx" wb.save(filepath) return filepath
def test_conditional_formatting_read(datadir): datadir.chdir() reference_file = 'conditional-formatting.xlsx' wb = load_workbook(reference_file) ws = wb.active rules = ws.conditional_formatting assert len(rules) == 30 # First test the conditional formatting rules read rule = rules['A1:A1048576'][0] assert dict(rule) == { 'priority': '30', 'type': 'colorScale', } rule = rules['B1:B10'][0] assert dict(rule) == {'priority': '29', 'type': 'colorScale'} rule = rules['C1:C10'][0] assert dict(rule) == {'priority': '28', 'type': 'colorScale'} rule = rules['D1:D10'][0] assert dict(rule) == { 'priority': '27', 'type': 'colorScale', } rule = rules['E1:E10'][0] assert dict(rule) == { 'priority': '26', 'type': 'colorScale', } rule = rules['F1:F10'][0] assert dict(rule) == { 'priority': '25', 'type': 'colorScale', } rule = rules['G1:G10'][0] assert dict(rule) == { 'priority': '24', 'type': 'colorScale', } rule = rules['H1:H10'][0] assert dict(rule) == { 'priority': '23', 'type': 'colorScale', } rule = rules['I1:I10'][0] assert dict(rule) == { 'priority': '22', 'type': 'colorScale', } rule = rules['J1:J10'][0] assert dict(rule) == { 'priority': '21', 'type': 'colorScale', } rule = rules['K1:K10'][0] assert dict(rule) == {'priority': '20', 'type': 'dataBar'} rule = rules['L1:L10'][0] assert dict(rule) == {'priority': '19', 'type': 'dataBar'} rule = rules['M1:M10'][0] assert dict(rule) == {'priority': '18', 'type': 'dataBar'} rule = rules['N1:N10'][0] assert dict(rule) == {'priority': '17', 'type': 'iconSet'} rule = rules['O1:O10'][0] assert dict(rule) == {'priority': '16', 'type': 'iconSet'} rule = rules['P1:P10'][0] assert dict(rule) == {'priority': '15', 'type': 'iconSet'} rule = rules['Q1:Q10'][0] assert dict(rule) == { 'text': '3', 'priority': '14', 'dxfId': '27', 'operator': 'containsText', 'type': 'containsText' } assert rule.dxf == DifferentialStyle(font=Font(color='FF9C0006'), fill=PatternFill(bgColor='FFFFC7CE')) rule = rules['R1:R10'][0] assert dict(rule) == { 'operator': 'between', 'dxfId': '26', 'type': 'cellIs', 'priority': '13' } assert rule.dxf == DifferentialStyle(font=Font(color='FF9C6500'), fill=PatternFill(bgColor='FFFFEB9C')) rule = rules['S1:S10'][0] assert dict(rule) == { 'priority': '12', 'dxfId': '25', 'percent': '1', 'type': 'top10', 'rank': '10' } rule = rules['T1:T10'][0] assert dict(rule) == { 'priority': '11', 'dxfId': '24', 'type': 'top10', 'rank': '4', 'bottom': '1' } rule = rules['U1:U10'][0] assert dict(rule) == { 'priority': '10', 'dxfId': '23', 'type': 'aboveAverage' } rule = rules['V1:V10'][0] assert dict(rule) == { 'aboveAverage': '0', 'dxfId': '22', 'type': 'aboveAverage', 'priority': '9' } rule = rules['W1:W10'][0] assert dict(rule) == { 'priority': '8', 'dxfId': '21', 'type': 'aboveAverage', 'equalAverage': '1' } rule = rules['X1:X10'][0] assert dict(rule) == { 'aboveAverage': '0', 'dxfId': '20', 'priority': '7', 'type': 'aboveAverage', 'equalAverage': '1' } rule = rules['Y1:Y10'][0] assert dict(rule) == { 'priority': '6', 'dxfId': '19', 'type': 'aboveAverage', 'stdDev': '1' } rule = rules['Z1:Z10'][0] assert dict(rule) == { 'aboveAverage': '0', 'dxfId': '18', 'type': 'aboveAverage', 'stdDev': '1', 'priority': '5' } assert rule.dxf == DifferentialStyle( font=Font(b=True, i=True, color='FF9C0006'), fill=PatternFill(bgColor='FFFFC7CE'), border=Border(left=Side(style='thin', color=Color(theme=5)), right=Side(style='thin', color=Color(theme=5)), top=Side(style='thin', color=Color(theme=5)), bottom=Side(style='thin', color=Color(theme=5)))) rule = rules['AA1:AA10'][0] assert dict(rule) == { 'priority': '4', 'dxfId': '17', 'type': 'aboveAverage', 'stdDev': '2' } rule = rules['AB1:AB10'][0] assert dict(rule) == { 'priority': '3', 'dxfId': '16', 'type': 'duplicateValues' } rule = rules['AC1:AC10'][0] assert dict(rule) == { 'priority': '2', 'dxfId': '15', 'type': 'uniqueValues' } rule = rules['AD1:AD10'][0] assert dict(rule) == { 'priority': '1', 'dxfId': '14', 'type': 'expression', }