[Python知识库]python xlwt生成Excel

?代码示例

# -*- coding: UTF-8 -*-
import xlwt

def write_excel():
    # 创建excel对象
    f = xlwt.Workbook()
    # 添加sheet页
    sheet1 = f.add_sheet(u'sheet1', cell_overwrite_ok=True)

    # 定义标题单元格格式
    style_title = xlwt.XFStyle()
    # 设置单元格内容自动换行
    style_title.alignment.wrap = 1
    # 设置标题单元格字体
    font = style_title.font
    # 设置标题单元格字体加粗
    font.bold = True
    # 设置填充颜色
    pat = style_title.pattern
    pat.pattern = 1
    pat.pattern_fore_colour = 0x28
    pat.pattern_back_colour = 0x28
    # 设置单元格内容格式
    style_title.num_format_str = 'general'
    # 设置单元格边框
    borders_title = style_title.borders
    # 设置边框线的样式
    borders_title.left = 2
    borders_title.right = 2
    borders_title.top = 2
    borders_title.bottom = 2
    # 设置单元格线的颜色
    borders_title.left_colour = 0x08
    borders_title.right_colour = 0x08
    borders_title.top_colour = 0x08
    borders_title.bottom_colour = 0x08

    # 设置内容单元格的样式
    style_content = xlwt.XFStyle()
    borders_content = style_content.borders
    borders_content.left = 1
    borders_content.right = 1
    borders_content.top = 1
    borders_content.bottom = 1
    borders_content.left_colour = 0x08
    borders_content.right_colour = 0x08
    borders_content.top_colour = 0x08
    borders_content.bottom_colour = 0x08

    # 标题名称
    title = [
        'Title A',
        'Title B',
        'Title C'
        ]
    # 内容开始行号
    row_num = 0
    # excel写入标题
    for i in range(0, len(title)):
        # 设置单元格宽度
        sheet1.col(i).width = 4000
        # 单元格写入内容
        sheet1.write(row_num, i, title[i], style_title)
    # excel写入内容
    for i in range(0, 100):
        row_num += 1
        for j in range(0, len(title)):
            sheet1.write(row_num, j, i+j, style_content)
    # 求一列的平均值
    row_num += 1
    for i in range(0, len(title)):
        # 数字转为字母
        chr_i = chr(i+65)
        # 开始行号
        start_col = chr_i + '1'
        # 结束行号
        end_col = chr_i + str(row_num-1)
        sheet1.write(row_num , i, label=xlwt.Formula(f'AVERAGE({start_col}:{end_col})'), style=style_content)

    # 求一列的最大值
    row_num += 1
    for i in range(0, len(title)):
        # 数字转为字母
        chr_i = chr(i+65)
        # 开始行号
        start_col = chr_i + '1'
        # 结束行号
        end_col = chr_i + str(row_num-1)
        sheet1.write(1, i, label=xlwt.Formula(f'MAX({start_col}:{end_col})'), style=style_content)

    # 生成excel
    
    f.save(f'test_data.xlsx')



if __name__ == '__main__':
    write_excel()
    

单元格填写公式

sheet1.write(‘行号’,‘列号’, label=xlwt.Formula(公式, style=样式)

sheet1.write(0, 'A', label=xlwt.Formula(f'AVERAGE(A1:A100)'), style=style_average)

?可设置的颜色

aqua 0x31
black 0x08
blue 0x0C
blue_gray 0x36
bright_green 0x0B
brown 0x3C
coral 0x1D
cyan_ega 0x0F
dark_blue 0x12
dark_blue_ega 0x12
dark_green 0x3A
dark_green_ega 0x11
dark_purple 0x1C
dark_red 0x10
dark_red_ega 0x10
dark_teal 0x38
dark_yellow 0x13
gold 0x33
gray_ega 0x17
gray25 0x16
gray40 0x37
gray50 0x17
gray80 0x3F
green 0x11
ice_blue 0x1F
indigo 0x3E
ivory 0x1A
lavender 0x2E
light_blue 0x30
light_green 0x2A
light_orange 0x34
light_turquoise 0x29
light_yellow 0x2B
lime 0x32
magenta_ega 0x0E
ocean_blue 0x1E
olive_ega 0x13
olive_green 0x3B
orange 0x35
pale_blue 0x2C
periwinkle 0x18
pink 0x0E
plum 0x3D
purple_ega 0x14
red 0x0A
rose 0x2D
sea_green 0x39
silver_ega 0x16
sky_blue 0x28
tan 0x2F
teal 0x15
teal_ega 0x15
turquoise 0x0F
violet 0x14
white 0x09
yellow 0x0D

可设置单元格数据样式

'general',
'0',
'0.00',
'#,##0',
'#,##0.00',
'"$"#,##0_);("$"#,##0)',
'"$"#,##0_);[Red]("$"#,##0)',
'"$"#,##0.00_);("$"#,##0.00)',
'"$"#,##0.00_);[Red]("$"#,##0.00)',
'0%',
'0.00%',
'0.00E+00',
'# ?/?',
'# ??/??',
'M/D/YY',
'D-MMM-YY',
'D-MMM',
'MMM-YY',
'h:mm AM/PM',
'h:mm:ss AM/PM',
'h:mm',
'h:mm:ss',
'M/D/YY h:mm',
'_(#,##0_);(#,##0)',
'_(#,##0_);[Red](#,##0)',
'_(#,##0.00_);(#,##0.00)',
'_(#,##0.00_);[Red](#,##0.00)',
'_("$"* #,##0_);_("$"* (#,##0);_("$"* "-"_);_(@_)',
'_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)',
'_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)',
'_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)',
'mm:ss',
'[h]:mm:ss',
'mm:ss.0',
'##0.0E+0',
'@'?