?代码示例
# -*- 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',
'@'?
|