python3写excel之xlsxwriter

pip install xlsxwriter

 xlsxwriter 生成的文件后缀名为.xlsx，最大能够支持 1048576 行数据，16384 列数据

import os
import xlsxwriter
import datetime

if os.path.exists('./gen-files/demo.xlsx'):
    os.remove('./gen-files/demo.xlsx')

workbook = xlsxwriter.Workbook('./gen-files/demo.xlsx')
sheet = workbook.add_worksheet('sheet1')
# 设置工作表标签颜色
sheet.set_tab_color('#FF00FF')
# 设置行高
sheet.set_row(0, 40)
# 设置列宽
sheet.set_column('A:P', 20)
# 单元格样式
style_cell = workbook.add_format({
    'font_name': 'Microsoft Yahei',  # 字体
    'font_size': 14,  # 字体大小
    'font_color': '#000000',  # 字体颜色
    'fg_color': '#00FF00',  # 背景颜色
    'bold': False,  # 是否粗体
    'align': 'center',  # 水平对齐方式
    'valign': 'vcenter',  # 垂直对齐方式
    'border': 1,  # 边框宽度
    'left': 1,
    'right': 1,
    'top': 1,
    'bottom': 1,
    'text_wrap': False  # 是否自动换行
})
# 日期格式
datetime_format = workbook.add_format({'num_format': 'yyyy-mm-dd HH:MM:SS'})

for i in range(5):
    for j in range(4):
        sheet.write_number(i, j, i+j, style_cell)
    # 公式
    sheet.write_formula(i, 4, "=sum(A"+str(i+1)+":D"+str(i+1)+")", style_cell)
    # 空白
    sheet.write_blank(i, 5, None, style_cell)
    # 字符串
    sheet.write_string(i, 6, 'Baidu', style_cell)
    # 日期格式
    sheet.write_datetime(i, 7, datetime.datetime.now(), datetime_format)
    # Boolean格式
    sheet.write_boolean(i, 8, True, style_cell)
    # url
    sheet.write_url(i, 9, 'https://www.baidu.com/', style_cell)


sheet = workbook.add_worksheet('sheet2')
header = ['name', 'age', 'score']
# 写入整行
sheet.write_row('A1', header)
# 写入整列
names = ['lily', 'rose', 'susan', 'john', 'jack']
sheet.write_column('A2', names)
sheet.write_column('B2', range(6, 11, 1))
sheet.write_column('C2', range(75, 100, 5))
chart = workbook.add_chart({'type': 'line'})
chart.add_series({
    'name': '=sheet2!$B$1',
    'categories': '=sheet2!$A$2:$A$6',
    'values': '=sheet2!$B$2:$B$6',
    'line': {'color': 'green'}
})
chart.add_series({
    'name': '=sheet2!$C$1',
    'categories': '=sheet2!$A$2:$A$6',
    'values': '=sheet2!$C$2:$C$6',
    'line': {'color': 'blue'},
    'data_labels': {'value': True}  # 设置图表上显示数字
})
chart.set_size({'width': 720, 'height': 600})
# 设置图表样式
chart.set_style(1)
# 设置图标上显示数据表格
chart.set_table()
chart.set_title({'name': '折线图'})
chart.set_x_axis({'name': 'xAxis'})
chart.set_y_axis({'name': 'yAxis'})
sheet.insert_chart('A8', chart, {'x_offset': 25, 'y_offset': 25})

# Insert an image.
sheet.insert_image('G5', './source-files/img1.png', {'x_offset': 15, 'y_offset': 10})


sheet = workbook.add_worksheet('sheet3')
# 合并单元格
sheet.merge_range('A1:D1', 'TEST')
sheet.write('E1', 4)
sheet.write(0, 5, 4)

names = ['lily', 'rose', 'susan', 'john', 'jack']
sheet.write_row('G1', names)

# 数据验证
sheet.data_validation('A2', {'validate': 'integer',
                             'criteria': 'between',
                             'minimum': 1,
                             'maximum': 100,
                             'input_title': 'Enter an integer:',
                             'input_message': 'between 1 and 100'})
sheet.data_validation('B2', {'validate': 'integer',
                             'criteria': '>',
                             'value': 100})
sheet.data_validation('D2', {'validate': 'decimal', 'criteria': 'between',
                             'minimum': 0.1, 'maximum': 0.5})
sheet.data_validation('E2', {'validate': 'list',   'source': '=$G$1:$K$1'})
sheet.data_validation('F2', {'validate': 'list', 'source': ['open',  'close']})

# 公式
sheet.write_array_formula('A3:B3', '{=SUM(B1:C1*B2:C2)}')
# url
sheet.write_url('A4',  'https://www.baidu.com/', string='Baidu')
sheet.write_url('B4',  'https://www.baidu.com/',  tip='Click here')
sheet.write_url('C4', 'mailto:aa@a.com', string='Mail me')
# 金钱格式
format_money = workbook.add_format({'num_format':  '￥#,##0.00'})
sheet.write('A5', 690000, format_money)

# rich text
italic = workbook.add_format({'italic': True})
red = workbook.add_format({'color': 'red'})
blue = workbook.add_format({'color': 'blue'})
center = workbook.add_format({'align': 'center'})
superscript = workbook.add_format({'font_script': 1})
sheet.write_rich_string('A6', 'This is ', red, 'red', ' and this is ', blue, 'blue')
sheet.write_rich_string('B6', italic, 'j = k', superscript, '(n-1)', center)


workbook.close()