Python XlsxWriter 创建生成 Excel 文件

$ pip install XlsxWriter

import xlsxwriter

workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()

worksheet.write('A1', 'Hello world')

workbook.close()

import xlsxwriter

# 创建新的Excel文件并添加工作表
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()

# 加宽第一列，使文字更清楚
worksheet.set_column('A:A', 20)

# 添加用于高亮显示单元格的粗体格式
bold = workbook.add_format({'bold': True})

# 写一些简单的文字
worksheet.write('A1', 'Hello')

# 带格式的文本
worksheet.write('A2', 'World', bold)

# 写一些数字，用行/列表示法
worksheet.write(2, 0, 123)
worksheet.write(3, 0, 123.456)

# 插入图像
worksheet.insert_image('B5', 'logo.png')

workbook.close()

import xlsxwriter

workbook  = xlsxwriter.Workbook('filename.xlsx')
worksheet = workbook.add_worksheet() # 标签页/工作簿

worksheet.write(0, 0, 'Hello Excel')

workbook.close()

# 减少存储在内存中的数据量，以便高效地写入大型文件
xlsxwriter.Workbook(filename, {'constant_memory': True})
# 生成最终的XLSX文件之前，XlsxWriter 将工作簿数据存储在临时文件中
xlsxwriter.Workbook(filename, {'tmpdir': '/home/user/tmp'})
# 将字符串转换为数字
xlsxwriter.Workbook(filename, {'strings_to_numbers': True})
# 将字符串转换为公式。默认值为True
xlsxwriter.Workbook(filename, {'strings_to_formulas': False})
# 将字符串转换为URL。默认值为True
xlsxwriter.Workbook(filename, {'strings_to_urls': False})
# 允许使用较新的Excel future 函数，而无需在其前面加上 _xlfn. 默认值为False
xlsxwriter.Workbook(filename, {'use_future_functions': True})
# 设置工作表中超链接的最大长度。默认值为2079，最小值为255。
xlsxwriter.Workbook(filename, {'max_url_length': 255})
# Excel不将NAN/INF作为数字处理
xlsxwriter.Workbook(filename, {'nan_inf_to_errors': True})
# 指定用于工作表的默认日期格式字符串
xlsxwriter.Workbook(filename, {'default_date_format': 'dd/mm/yy'})
# 删除时区
xlsxwriter.Workbook(filename, {'remove_timezone': True})
# 在编写xlsx文件zip容器时使用ZIP64扩展名，以允许文件大于4 GB
workbook = xlsxwriter.Workbook(filename, {'use_zip64': True})
# 同下
workbook = xlsxwriter.Workbook(filename)
workbook.use_zip64()

with xlsxwriter.Workbook('hello_world.xlsx') as workbook:
    worksheet = workbook.add_worksheet()

    worksheet.write('A1', 'Hello world')

from io import BytesIO

output = BytesIO()
workbook = xlsxwriter.Workbook(output)
worksheet = workbook.add_worksheet()

worksheet.write('A1', 'Hello')
workbook.close()

xlsx_data = output.getvalue()

worksheet1 = workbook.add_worksheet()           # Sheet1
worksheet2 = workbook.add_worksheet('Foglio2')  # Foglio2
worksheet3 = workbook.add_worksheet('Data')     # Data
worksheet4 = workbook.add_worksheet()           # Sheet4

worksheet = workbook.add_worksheet() # 更多入参方法见上

# 通用方法
worksheet.write(0, 0, 'Hello')          # write_string()
worksheet.write(1, 0, 'World')          # write_string()
worksheet.write(2, 0, 2)                # write_number()
worksheet.write(3, 0, 3.00001)          # write_number()
worksheet.write(4, 0, '=SIN(PI()/4)')   # write_formula()
worksheet.write(5, 0, '')               # write_blank()
worksheet.write(6, 0, None)             # write_blank()

# 专用方法
workbook.write_string()
workbook.write_number()
workbook.write_blank()
workbook.write_formula()
workbook.write_datetime()
workbook.write_boolean()
workbook.write_url()

(0, 0)      # Row-column notation.
('A1')      # The same cell in A1 notation.

(6, 2)      # Row-column notation.
('C7')      # The same cell in A1 notation.

# 以下相同
worksheet.write(0, 0, 'Hello')
worksheet.write('A1', 'Hello')

worksheet.print_area('A1:XFD1')      # Same as 1:1
worksheet.print_area('A1:A1048576')  # Same as A:A

worksheet.print_area(0, 0,       0, 16383)  # Same as 1:1
worksheet.print_area(0, 0, 1048575,     0)  # Same as A:A

'A1'    # Column and row are relative.
'$A1'   # Column is absolute and row is relative.
'A$1'   # Column is relative and row is absolute.
'$A$1'  # Column and row are absolute.

workbook.define_name('Exchange_rate', '=0.96')
worksheet.write('B3', '=B2*Exchange_rate')

from xlsxwriter.utility import xl_rowcol_to_cell

cell = xl_rowcol_to_cell(1, 2)  # C2
cell = xl_rowcol_to_cell(0, 0)   # A1
cell = xl_rowcol_to_cell(0, 1)   # B1
cell = xl_rowcol_to_cell(1, 0)   # A2
xl_rowcol_to_cell(0, 0, col_abs=True)                # $A1
xl_rowcol_to_cell(0, 0, row_abs=True)                # A$1
xl_rowcol_to_cell(0, 0, row_abs=True, col_abs=True)  # $A$1


from xlsxwriter.utility import xl_cell_to_rowcol

(row, col) = xl_cell_to_rowcol('A1')    # (0, 0)
(row, col) = xl_cell_to_rowcol('B1')    # (0, 1)
(row, col) = xl_cell_to_rowcol('C2')    # (1, 2)
(row, col) = xl_cell_to_rowcol('$C2')   # (1, 2)
(row, col) = xl_cell_to_rowcol('C$2')   # (1, 2)
(row, col) = xl_cell_to_rowcol('$C$2')  # (1, 2)


from xlsxwriter.utility import xl_col_to_name

column = xl_col_to_name(0)    # A
column = xl_col_to_name(1)    # B
column = xl_col_to_name(702)  # AAA

column = xl_col_to_name(0, False)  # A
column = xl_col_to_name(0, True)   # $A
column = xl_col_to_name(1, True)   # $B


from xlsxwriter.utility import xl_range

cell_range = xl_range(0, 0, 9, 0)  # A1:A10
cell_range = xl_range(1, 2, 8, 2)  # C2:C9
cell_range = xl_range(0, 0, 3, 4)  # A1:E4
cell_range = xl_range(0, 0, 0, 0)  # A1

from xlsxwriter.utility import xl_range_abs

cell_range = xl_range_abs(0, 0, 9, 0)  # $A$1:$A$10
cell_range = xl_range_abs(1, 2, 8, 2)  # $C$2:$C$9
cell_range = xl_range_abs(0, 0, 3, 4)  # $A$1:$E$4
cell_range = xl_range_abs(0, 0, 0, 0)  # $A$1

cell_format = workbook.add_format({'bold': True, 'italic': True})
# Cell is bold and italic.
worksheet.write(0, 0, 'Hello', cell_format)

# 将页面方向设置为横向
worksheet.set_landscape()
# 将工作表数据水平居中于打印页的边距之间
worksheet.center_horizontally()
# 设置页面查看模式
worksheet.set_page_view()
# 设置纸张类型
worksheet.set_paper(1)  # US Letter
worksheet.set_paper(9)  # A4
# 将工作表数据垂直居中于打印页的边距之间
worksheet.center_vertically()
# 设置打印页面的工作表边距
worksheet.set_margins(left=0.7, right=0.7)
# 设置打印页眉标题和选项
worksheet.set_header('&LHello')
worksheet.set_header('&L&G', {'image_left': 'logo.jpg'})
worksheet.set_header('&L&[Picture]&C&[Picture]&R&[Picture]',
                     {'image_left':   'red.jpg',
                      'image_center': 'blue.jpg',
                      'image_right':  'yellow.jpg'})
# 设置打印页脚标题和选项
worksheet.set_footer('&LHello')
# 设置要在每个打印页面顶部重复的行数
worksheet1.repeat_rows(0)     # Repeat the first row.
worksheet2.repeat_rows(0, 1)  # Repeat the first two rows
# 将列设置为在每个打印页的左侧重复
worksheet1.repeat_columns(0)      # Repeat the first column.
worksheet2.repeat_columns(0, 1)   # Repeat the first two columns.
worksheet3.repeat_columns('A:A')  # Repeat the first column.
worksheet4.repeat_columns('A:B')  # Repeat the first two columns.
# 设置选项以隐藏屏幕和打印页面上的网格线
worksheet.hide_gridlines()
# 设置选项以隐藏工作表中的行标题和列标题
worksheet.print_row_col_headers()

# 在当前工作表中设置打印区域
worksheet1.print_area('A1:H20')     # Cells A1 to H20.
worksheet2.print_area(0, 0, 19, 7)  # The same as above.
worksheet3.print_area('A1:H1048576')  # Same as A:H.

# 设置打印页面的顺序
worksheet.print_across()

# 将打印区域垂直和水平调整到特定数量的页面
worksheet1.fit_to_pages(1, 1)  # Fit to 1x1 pages.
worksheet2.fit_to_pages(2, 1)  # Fit to 2x1 pages.
worksheet3.fit_to_pages(1, 2)  # Fit to 1x2 pages.

# 创建格式对象
cell_format1 = workbook.add_format()       # Set properties later.
cell_format2 = workbook.add_format(props)  # Set properties at creation.

# 示例，加粗加红
cell_format = workbook.add_format()
cell_format.set_bold()
cell_format.set_font_color('red')

# 直接操作
cell_format = workbook.add_format({'bold': True, 'font_color': 'red'})

# 应用格式方法
worksheet.write       (0, 0, 'Foo', cell_format)
worksheet.write_string(1, 0, 'Bar', cell_format)
worksheet.write_number(2, 0, 3,     cell_format)
worksheet.write_blank (3, 0, '',    cell_format)

worksheet.set_row(0, 18, cell_format)
worksheet.set_column('A:D', 20, cell_format)

cell_format = workbook.add_format()

cell_format.set_bold()      # Turns bold on.
cell_format.set_bold(True)  # Also turns bold on.
cell_format.set_bold(False)  # Turns bold off.

cell_format = workbook.add_format({'bold': True, 'font_color': 'red'})
worksheet.write('A1', 'Cell A1', cell_format)

# Later...
cell_format.set_font_color('green')
worksheet.write('B1', 'Cell B1', cell_format)

workbook.add_format({'num_format': '$#,##0.00'})
workbook.add_format({'num_format': '[$$-409]#,##0.00'})
workbook.add_format({'num_format': '#,##0.00'})

cell_format1.set_num_format('d mmm yyyy')  # Format string.
cell_format2.set_num_format(0x0F)          # Format index.

cell_format01.set_num_format('0.000')
worksheet.write(1, 0, 3.1415926, cell_format01)       # -> 3.142

cell_format02.set_num_format('#,##0')
worksheet.write(2, 0, 1234.56, cell_format02)         # -> 1,235

cell_format03.set_num_format('#,##0.00')
worksheet.write(3, 0, 1234.56, cell_format03)         # -> 1,234.56

cell_format04.set_num_format('0.00')
worksheet.write(4, 0, 49.99, cell_format04)           # -> 49.99

cell_format05.set_num_format('mm/dd/yy')
worksheet.write(5, 0, 36892.521, cell_format05)       # -> 01/01/01

cell_format06.set_num_format('mmm d yyyy')
worksheet.write(6, 0, 36892.521, cell_format06)       # -> Jan 1 2001

cell_format07.set_num_format('d mmmm yyyy')
worksheet.write(7, 0, 36892.521, cell_format07)       # -> 1 January 2001

cell_format08.set_num_format('dd/mm/yyyy hh:mm AM/PM')
worksheet.write(8, 0, 36892.521, cell_format08)       # -> 01/01/2001 12:30 AM

cell_format09.set_num_format('0 "dollar and" .00 "cents"')
worksheet.write(9, 0, 1.87, cell_format09)            # -> 1 dollar and .87 cents

# Conditional numerical formatting.
cell_format10.set_num_format('[Green]General;[Red]-General;General')
worksheet.write(10, 0, 123, cell_format10)  # > 0 Green
worksheet.write(11, 0, -45, cell_format10)  # < 0 Red
worksheet.write(12, 0,   0, cell_format10)  # = 0 Default color

# Zip code.
cell_format11.set_num_format('00000')
worksheet.write(13, 0, 1209, cell_format11)

cell_format.set_font_name('Times New Roman')
cell_format.set_font_size(30)
cell_format.set_font_color('red')
cell_format.set_bold()
cell_format.set_italic()
cell_format.set_underline()
cell_format.set_font_strikeout() # 删除线
# 设置字体的上标/下标属性
cell_format.set_font_script(1)

worksheet.write('A1', '=1+2', hidden)

cell_format = workbook.add_format()
cell_format.set_align('center')
cell_format.set_align('vcenter')
worksheet.set_row(0, 70)
worksheet.set_column('A:A', 30)
worksheet.write(0, 0, 'Some Text', cell_format)

cell_format.set_pattern(1)  # This is optional when using a solid fill.
cell_format.set_bg_color('green')

cell_format.set_bottom(0)
cell_format.set_top(2)
cell_format.set_left(3)
cell_format.set_right(13)

cell_format.set_bottom_color()
cell_format.set_top_color()
cell_format.set_left_color()
cell_format.set_right_color()

import xlsxwriter

workbook = xlsxwriter.Workbook('chart_line.xlsx')
worksheet = workbook.add_worksheet()
data = [10, 40, 50, 20, 10, 50]
worksheet.write_column('A1', data)
chart = workbook.add_chart({'type': 'line'})
chart.add_series({'values': '=Sheet1!$A$1:$A$6'})
worksheet.insert_chart('C1', chart)
workbook.close()

import pandas as pd

# 从一些数据创建 Pandas DataFrame
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
# 使用 XlsxWriter 作为引擎创建 Pandas Excel writer
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')
# 将数据帧转换为XlsxWriter Excel对象。
df.to_excel(writer, sheet_name='Sheet1')
# 关闭Pandas Excel writer并输出Excel文件。
writer.save()

# 使用上下文管理器
df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])  
with pd.ExcelWriter("path_to_file.xlsx") as writer:
    df.to_excel(writer)  

# 要在单个文件中写入不同sheet
df1 = pd.DataFrame([["AAA", "BBB"]], columns=["Spam", "Egg"])  
df2 = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])  
with pd.ExcelWriter("path_to_file.xlsx") as writer:
    df1.to_excel(writer, sheet_name="Sheet1")  
    df2.to_excel(writer, sheet_name="Sheet2")