def export_to_excel(db_data, xlsx_name): """导出到excel文件中""" _log.info('开始导出到excel文件中') border = Border( left=Side(border_style=borders.BORDER_THIN, color='FF000000'), right=Side(border_style=borders.BORDER_THIN, color='FF000000'), top=Side(border_style=borders.BORDER_THIN, color='FF000000'), bottom=Side(border_style=borders.BORDER_THIN, color='FF000000') ) alignment = Alignment(horizontal='justify', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=True, indent=0) fill = PatternFill(fill_type=None, start_color='FFFFFFFF') # 基本的样式 basic_style = Style(font=Font(name='Microsoft YaHei') , border=border, alignment=alignment , fill=fill) # header_style = basic_style.copy( # font=Font(name='Microsoft YaHei', b=True, size=15, color='00215757'), # fill=PatternFill(fill_type=fills.FILL_SOLID, start_color='00BAA87F')) header_style = basic_style.copy() common_style = basic_style.copy() wb = Workbook() ws = wb.create_sheet(index=0, title='enterprises-{}'.format(len(db_data))) ws['A1'] = '日期' ws['A1'].style = header_style ws['B1'] = '新增数量' ws['B1'].style = header_style ws.column_dimensions['A'].width = 20 ws.column_dimensions['B'].width = 20 for i, row in enumerate(db_data): ws['A{}'.format(i + 2)] = row[0] ws['A{}'.format(i + 2)].style = common_style ws['B{}'.format(i + 2)] = row[1] ws['B{}'.format(i + 2)].style = common_style wb.save(filename=xlsx_name) _log.info('导出excel文件完成')
def write_dest(xlsx_name, schema_name): border = Border( left=Side(border_style=borders.BORDER_THIN, color='FF000000'), right=Side(border_style=borders.BORDER_THIN, color='FF000000'), top=Side(border_style=borders.BORDER_THIN, color='FF000000'), bottom=Side(border_style=borders.BORDER_THIN, color='FF000000') ) alignment = Alignment(horizontal='justify', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=True, indent=0) fill = PatternFill(fill_type=None, start_color='FFFFFFFF') # 基本的样式 basic_style = Style(font=Font(name='Microsoft YaHei') , border=border, alignment=alignment , fill=fill) title_style = basic_style.copy( font=Font(name='Microsoft YaHei', b=True, size=20, color='00215757'), alignment=Alignment(horizontal='center', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=True, indent=0), fill=PatternFill(fill_type=fills.FILL_SOLID, start_color='00B2CBED')) header_style = basic_style.copy( font=Font(name='Microsoft YaHei', b=True, size=15, color='00215757'), fill=PatternFill(fill_type=fills.FILL_SOLID, start_color='00BAA87F')) common_style = basic_style.copy() link_style = basic_style.copy(font=Font( name='Microsoft YaHei', color=colors.BLUE, underline='single')) table_data = load_schema(schema_name) wb = Workbook() wb.active.title = "首页列表" for table in table_data: ws = wb.create_sheet(title=table[0]) ws.merge_cells('E3:H3') # 合并单元格 ws['E3'].style = title_style ws['F2'].style = Style(border=Border( bottom=Side(border_style=borders.BORDER_THIN, color='FF000000'))) ws['G2'].style = Style(border=Border( bottom=Side(border_style=borders.BORDER_THIN, color='FF000000'))) ws['H2'].style = Style(border=Border( bottom=Side(border_style=borders.BORDER_THIN, color='FF000000'))) ws['I3'].style = Style(border=Border( left=Side(border_style=borders.BORDER_THIN, color='FF000000'))) ws['E3'] = table[0] ws['E4'].style = header_style ws['E4'] = '列名' ws['F4'].style = header_style ws['F4'] = '类型' ws['G4'].style = header_style ws['G4'] = '空值约束' ws['H4'].style = header_style ws['H4'] = '备注' ws.column_dimensions['E'].width = 20 ws.column_dimensions['F'].width = 20 ws.column_dimensions['G'].width = 16 ws.column_dimensions['H'].width = 45 for idx, each_column in enumerate(table[2:]): ws['E{}'.format(idx + 5)].style = common_style ws['E{}'.format(idx + 5)] = each_column[0] ws['F{}'.format(idx + 5)].style = common_style ws['F{}'.format(idx + 5)] = each_column[1] ws['G{}'.format(idx + 5)].style = common_style ws['G{}'.format(idx + 5)] = each_column[2] ws['H{}'.format(idx + 5)].style = common_style ws['H{}'.format(idx + 5)] = each_column[3] ws = wb['首页列表'] ws.merge_cells('D3:F3') ws['D3'].style = title_style ws['E2'].style = Style(border=Border( bottom=Side(border_style=borders.BORDER_THIN, color='FF000000'))) ws['F2'].style = Style(border=Border( bottom=Side(border_style=borders.BORDER_THIN, color='FF000000'))) ws['G3'].style = Style(border=Border( left=Side(border_style=borders.BORDER_THIN, color='FF000000'))) ws['D3'] = '贷快发数据库系统表' ws['D4'].style = header_style ws['D4'] = '编号' ws['E4'].style = header_style ws['E4'] = '表名' ws['F4'].style = header_style ws['F4'] = '详情链接' ws.column_dimensions['D'].width = 15 ws.column_dimensions['E'].width = 25 ws.column_dimensions['F'].width = 35 for inx, val in enumerate(table_data): ws['D{}'.format(inx + 5)].style = common_style ws['D{}'.format(inx + 5)] = inx + 1 ws['E{}'.format(inx + 5)].style = common_style ws['E{}'.format(inx + 5)] = val[1] linkcell = ws['F{}'.format(inx + 5)] linkcell.style = link_style linkcell.value = val[0] linkcell.hyperlink = '#{0}!{1}'.format(val[0], 'E3') wb.save(filename=xlsx_name)
def export_to_excel(db_data, xlsx_name): """导出到excel文件中""" _log.info('开始导出到excel文件中') border = Border( left=Side(border_style=borders.BORDER_THIN, color='FF000000'), right=Side(border_style=borders.BORDER_THIN, color='FF000000'), top=Side(border_style=borders.BORDER_THIN, color='FF000000'), bottom=Side(border_style=borders.BORDER_THIN, color='FF000000') ) alignment = Alignment(horizontal='justify', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=True, indent=0) fill = PatternFill(fill_type=None, start_color='FFFFFFFF') # 基本的样式 basic_style = Style(font=Font(name='Microsoft YaHei') , border=border, alignment=alignment , fill=fill) header_style = basic_style.copy( font=Font(name='Microsoft YaHei', b=True, size=15, color='00215757'), fill=PatternFill(fill_type=fills.FILL_SOLID, start_color='00BAA87F')) common_style = basic_style.copy() wb = Workbook() ws = wb.create_sheet(index=0, title='enterprises-{}'.format(len(db_data))) ws['A1'] = 'id' ws['A1'].style = common_style ws['B1'] = 'name' ws['B1'].style = common_style ws['C1'] = 'tax_code' ws['C1'].style = common_style ws['D1'] = 'region_id' ws['D1'].style = common_style ws['E1'] = 'customer_type' ws['E1'].style = common_style ws['F1'] = 'enterprise_type' ws['F1'].style = common_style ws['G1'] = 'address' ws['G1'].style = common_style ws['H1'] = 'postcode' ws['H1'].style = common_style ws['I1'] = 'tel' ws['I1'].style = common_style ws['J1'] = 'contact' ws['J1'].style = common_style ws['K1'] = 'fax' ws['K1'].style = common_style ws['L1'] = 'mobile' ws['L1'].style = common_style ws['M1'] = 'region_code' ws['M1'].style = common_style ws['N1'] = 'regian_name' ws['N1'].style = common_style ws['O1'] = 'note' ws['O1'].style = common_style ws['P1'] = 'parent_id' ws['P1'].style = common_style ws.column_dimensions['A'].width = 20 ws.column_dimensions['B'].width = 40 ws.column_dimensions['C'].width = 20 ws.column_dimensions['D'].width = 10 ws.column_dimensions['E'].width = 20 ws.column_dimensions['F'].width = 20 ws.column_dimensions['G'].width = 80 ws.column_dimensions['H'].width = 18 ws.column_dimensions['I'].width = 40 ws.column_dimensions['J'].width = 20 ws.column_dimensions['K'].width = 20 ws.column_dimensions['L'].width = 40 ws.column_dimensions['M'].width = 20 ws.column_dimensions['N'].width = 20 for i, row in enumerate(db_data): ws['A{}'.format(i + 2)] = row[0] ws['A{}'.format(i + 2)].style = common_style ws['B{}'.format(i + 2)] = row[1] ws['B{}'.format(i + 2)].style = common_style ws['C{}'.format(i + 2)] = row[2] ws['C{}'.format(i + 2)].style = common_style ws['D{}'.format(i + 2)] = row[3] ws['D{}'.format(i + 2)].style = common_style ws['E{}'.format(i + 2)] = row[4] ws['E{}'.format(i + 2)].style = common_style ws['F{}'.format(i + 2)] = row[5] ws['F{}'.format(i + 2)].style = common_style ws['G{}'.format(i + 2)] = row[6] ws['G{}'.format(i + 2)].style = common_style ws['H{}'.format(i + 2)] = row[7] ws['H{}'.format(i + 2)].style = common_style ws['I{}'.format(i + 2)] = row[8] ws['I{}'.format(i + 2)].style = common_style ws['J{}'.format(i + 2)] = row[9] ws['J{}'.format(i + 2)].style = common_style ws['K{}'.format(i + 2)] = row[10] ws['K{}'.format(i + 2)].style = common_style ws['L{}'.format(i + 2)] = row[11] ws['L{}'.format(i + 2)].style = common_style ws['M{}'.format(i + 2)] = row[12] ws['M{}'.format(i + 2)].style = common_style ws['N{}'.format(i + 2)] = row[13] ws['N{}'.format(i + 2)].style = common_style ws['O{}'.format(i + 2)] = row[14] ws['O{}'.format(i + 2)].style = common_style ws['P{}'.format(i + 2)] = row[15] ws['P{}'.format(i + 2)].style = common_style wb.save(filename=xlsx_name) _log.info('导出excel文件完成')
def export_to_excel(db_data, xlsx_name): """导出到excel文件中""" _log.info('开始导出到excel文件中') border = Border(left=Side(border_style=borders.BORDER_THIN, color='FF000000'), right=Side(border_style=borders.BORDER_THIN, color='FF000000'), top=Side(border_style=borders.BORDER_THIN, color='FF000000'), bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')) alignment = Alignment(horizontal='justify', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=True, indent=0) fill = PatternFill(fill_type=None, start_color='FFFFFFFF') # 基本的样式 basic_style = Style(font=Font(name='Microsoft YaHei'), border=border, alignment=alignment, fill=fill) header_style = basic_style.copy(font=Font(name='Microsoft YaHei', b=True, size=15, color='00215757'), fill=PatternFill( fill_type=fills.FILL_SOLID, start_color='00BAA87F')) common_style = basic_style.copy() wb = Workbook() ws = wb.create_sheet(index=0, title='enterprises-{}'.format(len(db_data))) ws['A1'] = 'id' ws['A1'].style = common_style ws['B1'] = 'name' ws['B1'].style = common_style ws['C1'] = 'tax_code' ws['C1'].style = common_style ws['D1'] = 'region_id' ws['D1'].style = common_style ws['E1'] = 'customer_type' ws['E1'].style = common_style ws['F1'] = 'enterprise_type' ws['F1'].style = common_style ws['G1'] = 'address' ws['G1'].style = common_style ws['H1'] = 'postcode' ws['H1'].style = common_style ws['I1'] = 'tel' ws['I1'].style = common_style ws['J1'] = 'contact' ws['J1'].style = common_style ws['K1'] = 'fax' ws['K1'].style = common_style ws['L1'] = 'mobile' ws['L1'].style = common_style ws['M1'] = 'region_code' ws['M1'].style = common_style ws['N1'] = 'regian_name' ws['N1'].style = common_style ws['O1'] = 'note' ws['O1'].style = common_style ws['P1'] = 'parent_id' ws['P1'].style = common_style ws.column_dimensions['A'].width = 20 ws.column_dimensions['B'].width = 40 ws.column_dimensions['C'].width = 20 ws.column_dimensions['D'].width = 10 ws.column_dimensions['E'].width = 20 ws.column_dimensions['F'].width = 20 ws.column_dimensions['G'].width = 80 ws.column_dimensions['H'].width = 18 ws.column_dimensions['I'].width = 40 ws.column_dimensions['J'].width = 20 ws.column_dimensions['K'].width = 20 ws.column_dimensions['L'].width = 40 ws.column_dimensions['M'].width = 20 ws.column_dimensions['N'].width = 20 for i, row in enumerate(db_data): ws['A{}'.format(i + 2)] = row[0] ws['A{}'.format(i + 2)].style = common_style ws['B{}'.format(i + 2)] = row[1] ws['B{}'.format(i + 2)].style = common_style ws['C{}'.format(i + 2)] = row[2] ws['C{}'.format(i + 2)].style = common_style ws['D{}'.format(i + 2)] = row[3] ws['D{}'.format(i + 2)].style = common_style ws['E{}'.format(i + 2)] = row[4] ws['E{}'.format(i + 2)].style = common_style ws['F{}'.format(i + 2)] = row[5] ws['F{}'.format(i + 2)].style = common_style ws['G{}'.format(i + 2)] = row[6] ws['G{}'.format(i + 2)].style = common_style ws['H{}'.format(i + 2)] = row[7] ws['H{}'.format(i + 2)].style = common_style ws['I{}'.format(i + 2)] = row[8] ws['I{}'.format(i + 2)].style = common_style ws['J{}'.format(i + 2)] = row[9] ws['J{}'.format(i + 2)].style = common_style ws['K{}'.format(i + 2)] = row[10] ws['K{}'.format(i + 2)].style = common_style ws['L{}'.format(i + 2)] = row[11] ws['L{}'.format(i + 2)].style = common_style ws['M{}'.format(i + 2)] = row[12] ws['M{}'.format(i + 2)].style = common_style ws['N{}'.format(i + 2)] = row[13] ws['N{}'.format(i + 2)].style = common_style ws['O{}'.format(i + 2)] = row[14] ws['O{}'.format(i + 2)].style = common_style ws['P{}'.format(i + 2)] = row[15] ws['P{}'.format(i + 2)].style = common_style wb.save(filename=xlsx_name) _log.info('导出excel文件完成')