## 一、安装 XlsxWriter

`pip3 install xlsxwriter`

## 二、验证是否安装成功

```import xlsxwriter

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

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

workbook.close()```

## 三、创建一个简单的 XLSX 文件

```expenses = (
['Rent', 1000],
['Gas',   100],
['Food',  300],
['Gym',    50],
)```

```import xlsxwriter

# Create a workbook and add a worksheet.
# 创建一个工作簿并添加一张工作表
workbook = xlsxwriter.Workbook('Expenses01.xlsx')

# Some data we want to write to the worksheet.
# 想写入工作表的数据
expenses = (
['Rent', 1000],
['Gas',   100],
['Food',  300],
['Gym',    50],
)

# Start from the first cell. Rows and columns are zero indexed.
# 从第一个单元格开始，行和列的索引均为 0
row = 0
col = 0

# Iterate over the data and write it out row by row.
# 迭代数据并逐行写入
for item, cost in (expenses):
worksheet.write(row, col,     item)
worksheet.write(row, col + 1, cost)
row += 1

# Write a total using a formula.
# 写一个计算出总和的公式
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')

workbook.close()```

## 四、代码详解

`import xlsxwriter`

`workbook = xlsxwriter.Workbook('Expenses01.xlsx')`

`worksheet = workbook.add_worksheet()`

```worksheet1 = workbook.add_worksheet()        # Defaults to Sheet1.
worksheet3 = workbook.add_worksheet()        # Defaults to Sheet3.```

`worksheet.write(row, col, some_data)`

```# Iterate over the data and write it out row by row.
# 迭代数据并逐行写入
for item, cost in (expenses):
worksheet.write(row, col,     item)
worksheet.write(row, col + 1, cost)
row += 1```

`worksheet.write(row, 1, '=SUM(B1:B4)')`

`workbook.close()`

## 五、将不同类型的数据写入 XLSX 文件

```expenses = (
['Rent', '2013-01-13', 1000],
['Gas',  '2013-01-14',  100],
['Food', '2013-01-16',  300],
['Gym',  '2013-01-20',   50],
)```

```from datetime import datetime
import xlsxwriter

# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('Expenses03.xlsx')

# Add a bold format to use to highlight cells.
# 添加用于突出显示单元格的粗体格式

# Add a number format for cells with money.
# 为显式钱的单元格添加数字格式

# Add an Excel date format.
# 添加 Excel 日期格式
date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})

# 调整列宽
worksheet.set_column(1, 1, 15)

worksheet.write('A1', 'Item', bold)
worksheet.write('B1', 'Date', bold)
worksheet.write('C1', 'Cost', bold)

# Some data we want to write to the worksheet.
expenses = (
['Rent', '2013-01-13', 1000],
['Gas',  '2013-01-14',  100],
['Food', '2013-01-16',  300],
['Gym',  '2013-01-20',   50],
)

# Start from the first cell below the headers.
row = 1
col = 0

for item, date_str, cost in (expenses):
# Convert the date string into a datetime object.
date = datetime.strptime(date_str, "%Y-%m-%d")

worksheet.write_string  (row, col,     item              )
worksheet.write_datetime(row, col + 1, date, date_format )
worksheet.write_number  (row, col + 2, cost, money_format)
row += 1

# Write a total using a formula.
worksheet.write(row, 0, 'Total', bold)
worksheet.write(row, 2, '=SUM(C2:C5)', money_format)

workbook.close()```

Excel 对待不同类型的输入数据，例如字符串和数字，处理通常不同，虽然通常对于用户是透明的。XlsxWriter 视图用 `worksheet.write()` 方法模拟这一点，通过将 Python 数据类型映射到 Excel 支持的类型上。

`write()` 方法作为几个更具体方法的通用别名：

• write_string()

• write_number()

• write_blank()

• write_formula()

• write_datetime()

• write_boolean()

• write_url()

```worksheet.write_string  (row, col,     item              )
worksheet.write_datetime(row, col + 1, date, date_format )
worksheet.write_number  (row, col + 2, cost, money_format)```

Excel 中的日期和时间是应用了数字格式的浮点数，方便以正确的格式显示它们。如果日期和时间是 Python datetime 对象，那么 XlsxWriter 会自动进行所需的数字转换。但是，我们还需要添加数字格式来确保 Excel 将其显示为日期：

```from datetime import datetime
...

date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})
...

for item, date_str, cost in (expenses):
# Convert the date string into a datetime object.
date = datetime.strptime(date_str, "%Y-%m-%d")
...
worksheet.write_datetime(row, col + 1, date, date_format )
...```

```# Adjust the column width.
worksheet.set_column('B:B', 15)```

## 六、参考教程

Tutorial 1: Create a simple XLSX file — XlsxWriter Documentation

Tutorial 3: Writing different types of data to the XLSX File — XlsxWriter Documentation

Python中的模块--XlsxWriter - 简书 (jianshu.com)