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文件完成')
