def test_lots_cols(write_cols, ColumnDimension, DummyWorksheet): from openpyxl.styles import Font ws = DummyWorksheet from openpyxl.cell import get_column_letter for i in range(1, 15): label = get_column_letter(i) cd = ColumnDimension(worksheet=ws) cd.font = Font(name=label) dict(cd) # create style_id in order for test ws.column_dimensions[label] = cd cols = write_cols(ws) xml = tostring(cols) expected = """<cols> <col max="1" min="1" style="1"></col> <col max="2" min="2" style="2"></col> <col max="3" min="3" style="3"></col> <col max="4" min="4" style="4"></col> <col max="5" min="5" style="5"></col> <col max="6" min="6" style="6"></col> <col max="7" min="7" style="7"></col> <col max="8" min="8" style="8"></col> <col max="9" min="9" style="9"></col> <col max="10" min="10" style="10"></col> <col max="11" min="11" style="11"></col> <col max="12" min="12" style="12"></col> <col max="13" min="13" style="13"></col> <col max="14" min="14" style="14"></col> </cols> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_col_style(write_cols, ColumnDimension, DummyWorksheet): ws = DummyWorksheet cd = ColumnDimension() ws.column_dimensions['A'] = cd cd._style = 1 cols = write_cols(ws) xml = tostring(cols) expected = """<cols><col max="1" min="1" style="1"></col></cols>""" diff = compare_xml(xml, expected) assert diff is None, diff
def test_col_style(write_cols, ColumnDimension, DummyWorksheet): ws = DummyWorksheet cd = ColumnDimension(worksheet=ws) ws.column_dimensions['A'] = cd cd._style = 1 cols = write_cols(ws) xml = tostring(cols) expected = """<cols><col max="1" min="1" style="1"></col></cols>""" diff = compare_xml(xml, expected) assert diff is None, diff
def test_col_style(write_cols, ColumnDimension, DummyWorksheet): from openpyxl.styles import Font ws = DummyWorksheet cd = ColumnDimension(worksheet=ws) ws.column_dimensions['A'] = cd cd.font = Font(color="FF0000") cols = write_cols(ws) xml = tostring(cols) expected = """<cols><col max="1" min="1" style="1"></col></cols>""" diff = compare_xml(xml, expected) assert diff is None, diff
def fit_width(ws): dim_holder = DimensionHolder(worksheet=ws) dim_holder[get_column_letter(1)] = ColumnDimension(ws, min=1, max=1, width=40) for col in range(ws.min_column + 1, ws.max_column + 1): dim_holder[get_column_letter(col)] = ColumnDimension(ws, min=col, max=col, collapsed=True, bestFit=True) ws.column_dimensions = dim_holder
def test_lots_cols(out, write_cols, ColumnDimension, DummyWorksheet): ws = DummyWorksheet from openpyxl.cell import get_column_letter for i in range(1, 15): label = get_column_letter(i) ws._styles[label] = i ws.column_dimensions[label] = ColumnDimension() with xmlfile(out) as xf: write_cols(xf, ws) xml = out.getvalue() expected = """<cols> <col max="1" min="1" style="1"></col> <col max="2" min="2" style="2"></col> <col max="3" min="3" style="3"></col> <col max="4" min="4" style="4"></col> <col max="5" min="5" style="5"></col> <col max="6" min="6" style="6"></col> <col max="7" min="7" style="7"></col> <col max="8" min="8" style="8"></col> <col max="9" min="9" style="9"></col> <col max="10" min="10" style="10"></col> <col max="11" min="11" style="11"></col> <col max="12" min="12" style="12"></col> <col max="13" min="13" style="13"></col> <col max="14" min="14" style="14"></col> </cols> """ diff = compare_xml(xml, expected) assert diff is None, diff
def xl_set_col_width(sheet, column, cm): """ change column width """ letter = column_to_letter(column) if column not in sheet.column_dimensions.keys(): sheet.column_dimensions[letter] = \ ColumnDimension(worksheet=sheet) sheet.column_dimensions[letter].width = xl_col_width(cm)
def test_write_lots_cols(out, doc, write_cols, ColumnDimension): worksheet = DummyWorksheet() from openpyxl.cell import get_column_letter for i in range(1, 15): label = get_column_letter(i) worksheet._styles[label] = i worksheet.column_dimensions[label] = ColumnDimension() write_cols(doc, worksheet) doc.endDocument() xml = out.getvalue() expected = """<cols> <col max="1" min="1" style="1"></col> <col max="2" min="2" style="2"></col> <col max="3" min="3" style="3"></col> <col max="4" min="4" style="4"></col> <col max="5" min="5" style="5"></col> <col max="6" min="6" style="6"></col> <col max="7" min="7" style="7"></col> <col max="8" min="8" style="8"></col> <col max="9" min="9" style="9"></col> <col max="10" min="10" style="10"></col> <col max="11" min="11" style="11"></col> <col max="12" min="12" style="12"></col> <col max="13" min="13" style="13"></col> <col max="14" min="14" style="14"></col> </cols> """ diff = compare_xml(xml, expected) assert diff is None, diff
def set_column_width(map_ws): col_width = 2 dim_holder = DimensionHolder(worksheet=map_ws) for col in range(map_ws.min_column, map_ws.max_column + 1): dim_holder[get_column_letter(col)] = ColumnDimension(map_ws, min=col, max=col, width=col_width) map_ws.column_dimensions = dim_holder
def parse_column_dimensions(self, col): attrs = dict(col.attrib) column = get_column_letter(int(attrs['min'])) attrs['index'] = column if 'style' in attrs: attrs['style'] = self.styles[int(attrs['style'])] dim = ColumnDimension(self.ws, **attrs) self.ws.column_dimensions[column] = dim
def test_col_widths(write_cols, ColumnDimension, DummyWorksheet): ws = DummyWorksheet ws.column_dimensions['A'] = ColumnDimension(worksheet=ws, width=4) cols = write_cols(ws) xml = tostring(cols) expected = """<cols><col width="4" min="1" max="1" customWidth="1"></col></cols>""" diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_col_widths(out, doc, write_cols, ColumnDimension): worksheet = DummyWorksheet() worksheet.column_dimensions['A'] = ColumnDimension(width=4) write_cols(doc, worksheet) doc.endDocument() xml = out.getvalue() expected = """<cols><col width="4" min="1" max="1" customWidth="1"></col></cols>""" diff = compare_xml(xml, expected) assert diff is None, diff
def test_outline_cols(out, write_cols, ColumnDimension, DummyWorksheet): worksheet = DummyWorksheet worksheet.column_dimensions['A'] = ColumnDimension(outline_level=1) with xmlfile(out) as xf: write_cols(xf, worksheet) xml = out.getvalue() expected = """<cols><col max="1" min="1" outlineLevel="1"/></cols>""" diff = compare_xml(expected, xml) assert diff is None, diff
def test_outline_cols(write_cols, ColumnDimension, DummyWorksheet): worksheet = DummyWorksheet worksheet.column_dimensions['A'] = ColumnDimension(worksheet=worksheet, outline_level=1) cols = write_cols(worksheet) xml = tostring(cols) expected = """<cols><col max="1" min="1" outlineLevel="1"/></cols>""" diff = compare_xml(expected, xml) assert diff is None, diff
def test_col_widths(out, write_cols, ColumnDimension, DummyWorksheet): ws = DummyWorksheet ws.column_dimensions['A'] = ColumnDimension(width=4) with xmlfile(out) as xf: write_cols(xf, ws) xml = out.getvalue() expected = """<cols><col width="4" min="1" max="1" customWidth="1"></col></cols>""" diff = compare_xml(xml, expected) assert diff is None, diff
def test_outline_format(out, write_format, ColumnDimension, DummyWorksheet): worksheet = DummyWorksheet worksheet.column_dimensions['A'] = ColumnDimension(outline_level=1) with xmlfile(out) as xf: write_format(xf, worksheet) xml = out.getvalue() expected = """<sheetFormatPr defaultRowHeight="15" baseColWidth="10" outlineLevelCol="1" />""" diff = compare_xml(expected, xml) assert diff is None, diff
def test_outline_cols(out, doc, write_cols, ColumnDimension): worksheet = DummyWorksheet() worksheet.column_dimensions['A'] = ColumnDimension(outline_level=1) write_cols(doc, worksheet) doc.endDocument() xml = out.getvalue() expected = """<cols><col max="1" min="1" outlineLevel="1"/></cols>""" diff = compare_xml(expected, xml) assert diff is None, diff
def set_column_width(self, column_width=10): """ Adjust all columns to the same specified width. """ dim_holder = DimensionHolder(worksheet=xl.ws) for col in range(xl.ws.min_column, xl.ws.max_column + 1): dim_holder[get_column_letter(col)] = ColumnDimension( xl.ws, min=col, max=col, width=column_width) xl.ws.column_dimensions = dim_holder
def test_outline_format(out, doc, write_sheet_format, ColumnDimension): worksheet = DummyWorksheet() worksheet.column_dimensions['A'] = ColumnDimension(outline_level=1) write_sheet_format(doc, worksheet) doc.endDocument() xml = out.getvalue() expected = """<sheetFormatPr defaultRowHeight="15" baseColWidth="10" outlineLevelCol="1" />""" diff = compare_xml(expected, xml) assert diff is None, diff
def test_outline_format(write_format, ColumnDimension, DummyWorksheet): worksheet = DummyWorksheet worksheet.column_dimensions['A'] = ColumnDimension(worksheet=worksheet, outline_level=1) fmt = write_format(worksheet) xml = tostring(fmt) expected = """<sheetFormatPr defaultRowHeight="15" baseColWidth="10" outlineLevelCol="1" />""" diff = compare_xml(expected, xml) assert diff is None, diff
def test_write_cols_style(out, doc, write_cols, ColumnDimension): worksheet = DummyWorksheet() worksheet.column_dimensions['A'] = ColumnDimension() worksheet._styles['A'] = 1 write_cols(doc, worksheet) doc.endDocument() xml = out.getvalue() expected = """<cols><col max="1" min="1" style="1"></col></cols>""" diff = compare_xml(xml, expected) assert diff is None, diff
def test_col_style(out, write_cols, ColumnDimension, DummyWorksheet): ws = DummyWorksheet ws.column_dimensions['A'] = ColumnDimension() ws._styles['A'] = 1 with xmlfile(out) as xf: write_cols(xf, ws) xml = out.getvalue() expected = """<cols><col max="1" min="1" style="1"></col></cols>""" diff = compare_xml(xml, expected) assert diff is None, diff
def resize_columns(ws): dim_holder = DimensionHolder(worksheet=ws) dims = {} for row in ws.rows: for cell in row: if cell.value: dims[cell.column_letter] = max( (dims.get(cell.column_letter, 0), len(str(cell.value)))) for col in range(ws.min_column, ws.max_column + 1): dim_holder[get_column_letter(col)] = ColumnDimension( ws, min=col, max=col, width=(dims[get_column_letter(col)] + 1) * 1.2) ws.column_dimensions = dim_holder
def generateExcelt(self): # TODO rushed, needs better implementation """generates an excel file of the failed tests""" steps = ["setup", "call", "teardown"] report = self.json ws = self.ws dh = self.dh ws.cell(1, 1, "Test Case Path").font = Font(b=True) ws.cell(1, 2, "Test Case Name").font = Font(b=True) ws.cell(1, 3, "Test Case Status").font = Font(b=True) ws.cell(1, 4, "Messages").font = Font(b=True) y = {"path": 1, "name": 2, "status": 3, "Messages": 4} x = 2 for test in report['tests']: if test['outcome'] == "failed": ws.cell(x, y["path"], test['nodeid']) ws.cell(x, y["name"], test['nodeid'].split("::")[-1]) ws.cell(x, y["status"], test['outcome'].upper()).font = Font(b=True, color="FF0000") messages = "" for step in steps: if "log" in test[step]: for log in test[step]["log"]: print("log type:::" + str(type(log))) if log["levelname"] == "MESSAGE": messages += log["msg"] + " |\n " ws.cell(x, y["Messages"], messages) x += 1 # setting the width of the columns for col in range(ws.min_column, ws.max_column + 1): dh[get_column_letter(col)] = ColumnDimension(ws, min=col, max=col, width=40) ws.column_dimensions = dh # saving the report try: if not (self.path[-1] == '\\'): self.path += '\\' self.wb.save(self.path + 'report.xlsx') except: print("Excel path is invalid")
def _writerow(rowvalues, header=False): row = [] _ws = _xlinfo["ws"] for i, v in enumerate(rowvalues): cell = WriteOnlyCell(_ws, value=v) if header: cell.fill = hdrCellFill cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) colLetter = chr(ord('A') + i) _ws.column_dimensions[colLetter] = ColumnDimension(_ws, customWidth=True) _ws.column_dimensions[colLetter].width = headerWidths.get(v, 20) else: cell.alignment = Alignment(horizontal="right" if isinstance(v, _NUM_TYPES) else "center" if isinstance(v, bool) else "left", vertical="top", wrap_text=isinstance(v, str)) row.append(cell) _ws.append(row)
def _save_report_file_as_excel(report_file_name, dt_of_fetching_data, header, animals_list, now): wb = Workbook() ws = wb.active row = 1 col = 1 # date ws.cell(column=1, row=row, value='Data pobrania danych: %s' % dt_of_fetching_data) # header row += 1 for header_elem in header: ws.cell(column=col, row=row, value=header_elem) col += 1 # values row += 1 cnt = 1 for animal in animals_list: col = 1 values = [ cnt, animal.herd_id, animal.animal_id, animal.process_status, animal.passport_id, animal.passport_status, animal.age_in_months(now), animal.species, animal.sex, animal.breed, animal.typ_uzyt, animal.status, animal.birth_date, animal.get_animal_events_states_text() ] for value in values: ws.cell(column=col, row=row, value=value) col += 1 row += 1 cnt += 1 for col in range(1, 10): col_letter = get_column_letter(col) ws.column_dimensions[col_letter] = ColumnDimension(ws, customWidth=True) ws.column_dimensions[col_letter].width = 24 wb.save(report_file_name)
def bind_col_dimensions(self): for col, cd in self.parser.column_dimensions.items(): if 'style' in cd: key = int(cd['style']) cd['style'] = self.ws.parent._cell_styles[key] self.ws.column_dimensions[col] = ColumnDimension(self.ws, **cd)
def append_df_to_excel(filename, df, sheet_name='Sheet1', startrow=None, truncate_sheet=False, set_width=False, **to_excel_kwargs): # Excel file doesn't exist - saving and exiting if not os.path.isfile(filename): if 'header' in to_excel_kwargs: to_excel_kwargs.pop('header') df.to_excel(filename, sheet_name=sheet_name, startrow=startrow if startrow is not None else 0, **to_excel_kwargs) return writer = pd.ExcelWriter(filename, engine='openpyxl', mode='a') # try to open an existing workbook writer.book = load_workbook(filename) # get the last row in the existing Excel sheet # if it was not specified explicitly if startrow is None and sheet_name in writer.book.sheetnames: startrow = writer.book[sheet_name].max_row # truncate sheet if truncate_sheet and sheet_name in writer.book.sheetnames: # index of [sheet_name] sheet idx = writer.book.sheetnames.index(sheet_name) # remove [sheet_name] writer.book.remove(writer.book.worksheets[idx]) # create an empty sheet [sheet_name] using old index writer.book.create_sheet(sheet_name, idx) # copy existing sheets writer.sheets = {ws.title: ws for ws in writer.book.worksheets} if set_width: size_dict = { 1: 15, 3: 15, 4: 30, 5: 15, 6: 15, 7: 40, 8: 17, 9: 17, 10: 30, 12: 30 } ws = writer.sheets["Sheet1"] dim_holder = DimensionHolder(worksheet=ws) for i, j in size_dict.items(): dim_holder[get_column_letter(i)] = ColumnDimension(ws, min=i, max=i, width=j) ws.column_dimensions = dim_holder if startrow is None: startrow = 0 # write out the new sheet df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs) # save the workbook writer.save()
def update_excel_file(excel_file_1,istenilen_adet): excel_file_1 = excel_file_1 #df_first_shift = pd.read_excel(excel_file_1, sheet_name='Sheet') #workbook = Workbook() #overvrites workbook = load_workbook ( excel_file_1 ) #JUST SAVES ws = workbook.active sayi_katsayi = 0 while sayi_katsayi < istenilen_adet: sabit_katsayi = ( 4 * sayi_katsayi) ws[f"B{1+sabit_katsayi}"] = str('Adı') ws[f"B{1+sabit_katsayi}"].font = Font(bold=True) ws[f"C{1+sabit_katsayi}"] = str('Ürün kodu') ws[f"C{1+sabit_katsayi}"].font = Font(bold=True) ws[f"D{1+sabit_katsayi}"] = str('Fiyat($)') ws[f"D{1+sabit_katsayi}"].font = Font(bold=True) ws[f"E{1+sabit_katsayi}"] = str('Deri Rengi') ws[f"E{1+sabit_katsayi}"].font = Font(bold=True) ws[f"F{1+sabit_katsayi}"] = str('Deri Rengi Opsiyonları') ws[f"F{1+sabit_katsayi}"].font = Font(bold=True) ws[f"G{1+sabit_katsayi}"] = str('Deri Türü') ws[f"G{1+sabit_katsayi}"].font = Font(bold=True) ws[f"H{1+sabit_katsayi}"] = str('Deri Oranı') ws[f"H{1+sabit_katsayi}"].font = Font(bold=True) ws[f"I{1+sabit_katsayi}"] = str('Tüy Rengi') ws[f"I{1+sabit_katsayi}"].font = Font(bold=True) ws[f"J{1+sabit_katsayi}"] = str('Tüy Rengi Opsiyonları') ws[f"J{1+sabit_katsayi}"].font = Font(bold=True) ws[f"K{1+sabit_katsayi}"] = str('Sezon') ws[f"K{1+sabit_katsayi}"].font = Font(bold=True) ws[f"L{1+sabit_katsayi}"] = str('Bakım') ws[f"L{1+sabit_katsayi}"].font = Font(bold=True) ws[f"M{1+sabit_katsayi}"] = str('Fermuar Türü') ws[f"M{1+sabit_katsayi}"].font = Font(bold=True) ws[f"N{1+sabit_katsayi}"] = str('Fermuar Materyeli') ws[f"N{1+sabit_katsayi}"].font = Font(bold=True) ws[f"O{1+sabit_katsayi}"] = str('Düğmeleri') ws[f"O{1+sabit_katsayi}"].font = Font(bold=True) ws[f"P{1+sabit_katsayi}"] = str('Çift Taraflı') ws[f"P{1+sabit_katsayi}"].font = Font(bold=True) ws[f"Q{1+sabit_katsayi}"] = str('Koleksiyon Adı') ws[f"Q{1+sabit_katsayi}"].font = Font(bold=True) ws[f"R{1+sabit_katsayi}"] = str('Cep Türü') ws[f"R{1+sabit_katsayi}"].font = Font(bold=True) ws[f"S{1+sabit_katsayi}"] = str('İsim Etiketi') ws[f"S{1+sabit_katsayi}"].font = Font(bold=True) ws[f"T{1+sabit_katsayi}"] = str('Ağırlık') ws[f"T{1+sabit_katsayi}"].font = Font(bold=True) ws[f"U{1+sabit_katsayi}"] = str('Açıklama') ws[f"U{1+sabit_katsayi}"].font = Font(bold=True) ws[f"V{1+sabit_katsayi}"] = str('Materyaller') ws[f"V{1+sabit_katsayi}"].font = Font(bold=True) ws[f"W{1+sabit_katsayi}"] = str('Ürün Ölçüleri(cm)') ws[f"W{1+sabit_katsayi}"].font = Font(bold=True) ws[f"X{1+sabit_katsayi}"] = str('İç Kısım') ws[f"X{1+sabit_katsayi}"].font = Font(bold=True) ws[f"Y{1+sabit_katsayi}"] = str('Dış Kısım') ws[f"Y{1+sabit_katsayi}"].font = Font(bold=True) ws[f"Z{1+sabit_katsayi}"] = str('Jeket Türü') ws[f"Z{1+sabit_katsayi}"].font = Font(bold=True) # ingilizce kisim ws[f"B{3 + sabit_katsayi}"] = str ( 'Name' ) ws[f"B{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"C{3 + sabit_katsayi}"] = str ( 'Product Code' ) ws[f"C{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"D{3 + sabit_katsayi}"] = str ( 'Price($)' ) ws[f"D{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"E{3 + sabit_katsayi}"] = str ( 'Colour of The Leather' ) ws[f"E{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"F{3 + sabit_katsayi}"] = str ( 'Colour Options Of Leather' ) ws[f"F{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"G{3 + sabit_katsayi}"] = str ( 'Type Of Leather' ) ws[f"G{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"H{3 + sabit_katsayi}"] = str ( 'Leather Percentage' ) ws[f"H{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"I{3 + sabit_katsayi}"] = str ( 'Feather Color' ) ws[f"I{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"J{3 + sabit_katsayi}"] = str ( 'Feather Color Options' ) ws[f"J{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"K{3 + sabit_katsayi}"] = str ( 'Seeason' ) ws[f"K{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"L{3 + sabit_katsayi}"] = str ( 'Care' ) ws[f"L{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"M{3 + sabit_katsayi}"] = str ( 'Zipper Type' ) ws[f"M{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"N{3 + sabit_katsayi}"] = str ( 'Zipper Material' ) ws[f"N{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"O{3 + sabit_katsayi}"] = str ( 'Buttons' ) ws[f"O{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"P{3 + sabit_katsayi}"] = str ( 'Reversable' ) ws[f"P{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"Q{3 + sabit_katsayi}"] = str ( 'Collection Name' ) ws[f"Q{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"R{3 + sabit_katsayi}"] = str ( 'Pocket Type' ) ws[f"R{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"S{3 + sabit_katsayi}"] = str ( 'Name Tag' ) ws[f"S{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"T{3 + sabit_katsayi}"] = str ( 'Weight' ) ws[f"T{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"U{3 + sabit_katsayi}"] = str ( 'Description' ) ws[f"U{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"V{3 + sabit_katsayi}"] = str ( 'Materials' ) ws[f"V{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"W{3 + sabit_katsayi}"] = str ( 'Product Meeasurements' ) ws[f"W{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"X{3 + sabit_katsayi}"] = str ( 'Interior' ) ws[f"X{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"Y{3 + sabit_katsayi}"] = str ( 'Exterior' ) ws[f"Y{3 + sabit_katsayi}"].font = Font ( bold=True ) ws[f"Z{3 + sabit_katsayi}"] = str ( 'Type of Jacket' ) ws[f"Z{3 + sabit_katsayi}"].font = Font ( bold=True ) sayi_katsayi += 1 ##Adjustment for excel files dim_holder = DimensionHolder(worksheet=ws) for col in range(ws.min_column, ws.max_column + 1): dim_holder[get_column_letter(col)] = ColumnDimension(ws, min=col, max=col, width=25) ws.column_dimensions = dim_holder # workbook.save(filename = excel_file_1)#hatali workbbook nerden cikti
def __format_sheet(self, ws, command): self.SVC_log.debug("Starting formating " + command + " information into Excel file") # Fix the width self.SVC_log.debug("Starting fixing width for " + command + " sheet") dim_holder = DimensionHolder(worksheet=ws) for the_col in range(ws.min_column, ws.max_column + 1): dim_holder[get_column_letter(the_col)] = ColumnDimension( ws, min=the_col, max=the_col, width=25) ws.column_dimensions = dim_holder self.SVC_log.debug("Completed fixing width for " + command + " sheet") # Set the header self.SVC_log.debug("Starting setting header for " + command + " sheet") HEADER_FONT = Font(name='Calibri', size=12, bold=True, italic=False, vertAlign=None, underline='none', strike=False, color='000000') for the_col in range(ws.min_column, ws.max_column + 1): ws.cell(row=1, column=the_col).font = HEADER_FONT ws.cell(row=1, column=the_col).fill = PatternFill(start_color='0066CC', end_color='0066CC', fill_type='darkGrid') self.SVC_log.debug("Completed setting header for " + command + " sheet") # Center all self.SVC_log.debug("Starting centering cells for " + command + " sheet") for row_cells in ws.iter_rows(): for cell in row_cells: cell.alignment = Alignment(horizontal='center', vertical='center') self.SVC_log.debug("Completed centering cells for " + command + " sheet") # Create alternate colors self.SVC_log.debug("Starting coloring alternate rows for " + command + " sheet") for the_row in range(2, ws.max_row + 1, 2): for the_col in range(1, ws.max_column + 1): ws.cell(row=the_row, column=the_col).fill = PatternFill( start_color='66cc00', end_color='66cc00', fill_type='darkGrid') for the_row in range(3, ws.max_row + 1, 2): for the_col in range(1, ws.max_column + 1): ws.cell(row=the_row, column=the_col).fill = PatternFill( start_color='b3ff66', end_color='b3ff66', fill_type='lightGrid') self.SVC_log.debug("Completed coloring alternate rows for " + command + " sheet") self.SVC_log.debug("Completed formating " + command + " information into Excel file")
def saveLoadableExcel(dts, excelFile): from arelle import ModelDocument, XmlUtil from openpyxl import Workbook, cell from openpyxl.styles import Font, PatternFill, Border, Alignment, Color, fills, Side from openpyxl.worksheet.dimensions import ColumnDimension workbook = Workbook(encoding="utf-8") # remove pre-existing worksheets while len(workbook.worksheets) > 0: workbook.remove_sheet(workbook.worksheets[0]) conceptsWs = workbook.create_sheet(title="Concepts") dtsWs = workbook.create_sheet(title="DTS") # identify type of taxonomy conceptsWsHeaders = None cellFontArgs = None for doc in dts.urlDocs.values(): if doc.type == ModelDocument.Type.SCHEMA and doc.inDTS: for i in range(len(headersStyles)): if re.match(headersStyles[i][0], doc.targetNamespace): cellFontArgs = headersStyles[i][ 1] # use as arguments to Font() conceptsWsHeaders = headersStyles[i][2] break if conceptsWsHeaders is None: dts.info( "error:saveLoadableExcel", _("Referenced taxonomy style not identified, assuming general pattern." ), modelObject=dts) cellFontArgs = headersStyles[-1][1] # use as arguments to Font() conceptsWsHeaders = headersStyles[-1][2] hdrCellFont = Font(**cellFontArgs) hdrCellFill = PatternFill( patternType=fills.FILL_SOLID, fgColor=Color("00FFBF5F")) # Excel's light orange fill color = 00FF990 cellFont = Font(**cellFontArgs) def writeCell(ws, row, col, value, fontBold=False, borders=True, indent=0, hAlign=None, vAlign=None, hdr=False): cell = ws.cell(row=row, column=col) cell.value = value if hdr: cell.font = hdrCellFont cell.fill = hdrCellFill if not hAlign: hAlign = "center" if not vAlign: vAlign = "center" else: cell.font = cellFont if not hAlign: hAlign = "left" if not vAlign: vAlign = "top" if borders: cell.border = Border(top=Side(border_style="thin"), left=Side(border_style="thin"), right=Side(border_style="thin"), bottom=Side(border_style="thin")) cell.alignment = Alignment(horizontal=hAlign, vertical=vAlign, wrap_text=True, indent=indent) # sheet 1 col widths for i, hdr in enumerate(conceptsWsHeaders): colLetter = cell.get_column_letter(i + 1) conceptsWs.column_dimensions[colLetter] = ColumnDimension( conceptsWs, customWidth=True) conceptsWs.column_dimensions[colLetter].width = headerWidths.get( hdr[1], 40) # sheet 2 headers for i, hdr in enumerate(dtsWsHeaders): colLetter = cell.get_column_letter(i + 1) dtsWs.column_dimensions[colLetter] = ColumnDimension(conceptsWs, customWidth=True) dtsWs.column_dimensions[colLetter].width = hdr[1] writeCell(dtsWs, 1, i + 1, hdr[0], hdr=True) # referenced taxonomies conceptsRow = 1 dtsRow = 3 # identify extension schema extensionSchemaDoc = None if dts.modelDocument.type == ModelDocument.Type.SCHEMA: extensionSchemaDoc = dts.modelDocument elif dts.modelDocument.type == ModelDocument.Type.INSTANCE: for doc, docReference in dts.modelDocument.referencesDocument.items(): if docReference.referenceType == "href": extensionSchemaDoc = doc break if extensionSchemaDoc is None: dts.info("error:saveLoadableExcel", _("Unable to identify extension taxonomy."), modelObject=dts) return for doc, docReference in extensionSchemaDoc.referencesDocument.items(): if docReference.referenceType == "import" and doc.targetNamespace != XbrlConst.xbrli: writeCell(dtsWs, dtsRow, 1, "import") writeCell(dtsWs, dtsRow, 2, "schema") writeCell( dtsWs, dtsRow, 3, XmlUtil.xmlnsprefix(doc.xmlRootElement, doc.targetNamespace)) writeCell(dtsWs, dtsRow, 4, doc.uri) writeCell(dtsWs, dtsRow, 5, doc.targetNamespace) dtsRow += 1 dtsRow += 1 doc = extensionSchemaDoc writeCell(dtsWs, dtsRow, 1, "extension") writeCell(dtsWs, dtsRow, 2, "schema") writeCell(dtsWs, dtsRow, 3, XmlUtil.xmlnsprefix(doc.xmlRootElement, doc.targetNamespace)) writeCell(dtsWs, dtsRow, 4, os.path.basename(doc.uri)) writeCell(dtsWs, dtsRow, 5, doc.targetNamespace) dtsRow += 1 for doc, docReference in extensionSchemaDoc.referencesDocument.items(): if docReference.referenceType == "href" and doc.type == ModelDocument.Type.LINKBASE: linkbaseType = "" role = docReference.referringModelObject.get( "{http://www.w3.org/1999/xlink}role") or "" if role.startswith("http://www.xbrl.org/2003/role/" ) and role.endswith("LinkbaseRef"): linkbaseType = os.path.basename(role)[0:-11] writeCell(dtsWs, dtsRow, 1, "extension") writeCell(dtsWs, dtsRow, 2, "linkbase") writeCell(dtsWs, dtsRow, 3, linkbaseType) writeCell(dtsWs, dtsRow, 4, os.path.basename(doc.uri)) writeCell(dtsWs, dtsRow, 5, "") dtsRow += 1 dtsRow += 1 # extended link roles defined in this document for roleURI, roleTypes in sorted( dts.roleTypes.items(), # sort on definition if any else URI key=lambda item: (item[1][0].definition or item[0])): for roleType in roleTypes: if roleType.modelDocument == extensionSchemaDoc: writeCell(dtsWs, dtsRow, 1, "extension") writeCell(dtsWs, dtsRow, 2, "role") writeCell(dtsWs, dtsRow, 3, "") writeCell(dtsWs, dtsRow, 4, roleType.definition) writeCell(dtsWs, dtsRow, 5, roleURI) dtsRow += 1 # tree walk recursive function def treeWalk(row, depth, concept, preferredLabel, arcrole, preRelSet, visited): if concept is not None: # calc parents calcRelSet = dts.relationshipSet(XbrlConst.summationItem, preRelSet.linkrole) calcRel = None for modelRel in calcRelSet.toModelObject(concept): calcRel = modelRel break for i, hdr in enumerate(conceptsWsHeaders): colType = hdr[1] value = "" if colType == "name": value = str(concept.name) elif colType == "prefix" and concept.qname is not None: value = concept.qname.prefix elif colType == "type" and concept.type is not None: value = str(concept.type.qname) elif colType == "substitutionGroup": value = str(concept.substitutionGroupQname) elif colType == "abstract": value = "true" if concept.isAbstract else "false" elif colType == "nillable": if concept.isNillable: value = "true" elif colType == "periodType": value = concept.periodType elif colType == "balance": value = concept.balance elif colType == "label": role = hdr[2] lang = hdr[3] if role == XbrlConst.standardLabel: if "indented" in hdr: roleUri = preferredLabel elif "overridePreferred" in hdr: if preferredLabel and preferredLabel != XbrlConst.standardLabel: roleUri = role else: roleUri = "**no value**" # skip putting a value in this column else: roleUri = role else: roleUri = role if roleUri != "**no value**": value = concept.label( roleUri, linkroleHint=preRelSet.linkrole, lang=lang, fallbackToQname=(role == XbrlConst.standardLabel)) elif colType == "preferredLabel" and preferredLabel: if preferredLabel.startswith( "http://www.xbrl.org/2003/role/"): value = os.path.basename(preferredLabel) else: value = preferredLabel elif colType == "calculationParent" and calcRel is not None: calcParent = calcRel.fromModelObject if calcParent is not None: value = str(calcParent.qname) elif colType == "calculationWeight" and calcRel is not None: value = calcRel.weight elif colType == "depth": value = depth if "indented" in hdr: indent = min(depth, MAXINDENT) else: indent = 0 writeCell(conceptsWs, row, i + 1, value, indent=indent) row += 1 if concept not in visited: visited.add(concept) for modelRel in preRelSet.fromModelObject(concept): if modelRel.toModelObject is not None: row = treeWalk(row, depth + 1, modelRel.toModelObject, modelRel.preferredLabel, arcrole, preRelSet, visited) visited.remove(concept) return row # use presentation relationships for conceptsWs arcrole = XbrlConst.parentChild # sort URIs by definition linkroleUris = [] relationshipSet = dts.relationshipSet(arcrole) if relationshipSet: for linkroleUri in relationshipSet.linkRoleUris: modelRoleTypes = dts.roleTypes.get(linkroleUri) if modelRoleTypes: roledefinition = (modelRoleTypes[0].genLabel(strip=True) or modelRoleTypes[0].definition or linkroleUri) else: roledefinition = linkroleUri linkroleUris.append((roledefinition, linkroleUri)) linkroleUris.sort() # for each URI in definition order for roledefinition, linkroleUri in linkroleUris: # write linkrole writeCell( conceptsWs, conceptsRow, 1, (roledefinition or linkroleUri), borders=False) # ELR has no boarders, just font specified conceptsRow += 1 # write header row for i, hdr in enumerate(conceptsWsHeaders): writeCell(conceptsWs, conceptsRow, i + 1, hdr[0], hdr=True) conceptsRow += 1 # elr relationships for tree walk linkRelationshipSet = dts.relationshipSet(arcrole, linkroleUri) for rootConcept in linkRelationshipSet.rootConcepts: conceptsRow = treeWalk(conceptsRow, 0, rootConcept, None, arcrole, linkRelationshipSet, set()) conceptsRow += 1 # double space rows between tables else: # write header row for i, hdr in enumerate(conceptsWsHeaders): writeCell(conceptsWs, conceptsRow, i, hdr[0], hdr=True) conceptsRow += 1 # get lang lang = None for i, hdr in enumerate(conceptsWsHeaders): colType = hdr[1] if colType == "label": lang = hdr[3] if colType == "label": role = hdr[2] lang = hdr[3] lbls = defaultdict(list) for concept in set(dts.qnameConcepts.values( )): # may be twice if unqualified, with and without namespace lbls[concept.label(role, lang=lang)].append(concept.objectId()) srtLbls = sorted(lbls.keys()) excludedNamespaces = XbrlConst.ixbrlAll.union( (XbrlConst.xbrli, XbrlConst.link, XbrlConst.xlink, XbrlConst.xl, XbrlConst.xbrldt, XbrlConst.xhtml)) for label in srtLbls: for objectId in lbls[label]: concept = dts.modelObject(objectId) if concept.modelDocument.targetNamespace not in excludedNamespaces: for i, hdr in enumerate(conceptsWsHeaders): colType = hdr[1] value = "" if colType == "name": value = str(concept.qname.localName) elif colType == "prefix": value = concept.qname.prefix elif colType == "type": value = str(concept.type.qname) elif colType == "substitutionGroup": value = str(concept.substitutionGroupQname) elif colType == "abstract": value = "true" if concept.isAbstract else "false" elif colType == "periodType": value = concept.periodType elif colType == "balance": value = concept.balance elif colType == "label": role = hdr[2] lang = hdr[3] value = concept.label(role, lang=lang) elif colType == "depth": value = 0 if "indented" in hdr: indent = min(0, MAXINDENT) else: indent = 0 writeCell(conceptsWs, conceptsRow, i, value, indent=indent) conceptsRow += 1 try: workbook.save(excelFile) dts.info("info:saveLoadableExcel", _("Saved Excel file: %(excelFile)s"), excelFile=os.path.basename(excelFile), modelXbrl=dts) except Exception as ex: dts.error("exception:saveLoadableExcel", _("File saving exception: %(error)s"), error=ex, modelXbrl=dts)