python3 xlsxwriter_Python3.x 操作Excel（写）——XlsxWriter 模块

偏执的太偏执、 2023-01-10 01:29 138阅读 0赞

XlsxWriter 模块

XlsxWriter is a Python module for creating Excel XLSX files.

XlsxWriter 是一个人基于Pytohn用于创建Excel文件的库，只能创建新文件，不支持读取和修改，所以和xlrd配合使用。

eg1：创建名为‘hello.xlsx’文件，并在第一行，第一列写入数据‘Hello， world’。

代码：

\# -\*- coding: utf-8 -\*-

\# Python操作excel表格(写)

\# 创建名为'hello.xlsx'文件，并在第一行，第一列写入数据'Hello， world'.

import xlsxwriter

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

worksheet = workbook.add\_worksheet('test')

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

workbook.close()

运行结果：

![55eafa3c096d5820cc7d9897cd963cd6.png][]

eg2：新建一个表格‘test’， 新建sheet表‘test1’， 插入数据，中国移动流量包价格大小统计表。

代码：

\# -\*- coding: utf-8 -\*-

\# 新建一个表格‘test’， 新建sheet表‘test1’， 插入数据，中国移动流量包价格大小统计表。

import xlsxwriter

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

worksheet = workbook.add\_worksheet('test1')

data = ( \# 数据格式

\['value', 'MB'\],

\['3', '10'\],

\['5', '30'\],

\['10', '70'\],

\['20', '150'\],

\['30', '500'\],

\['50', '1024'\],

\['70', '2048'\],

\['100', '3072'\],

\['130', '4096'\],

\['180', '6144'\],

\['280', '11264'\],

)

row = 0

col = 0

for facevalue, size in (data): \# 插入数据

worksheet.write(row, col, facevalue)

worksheet.write(row, col + 1, size)

row += 1

workbook.close()

运行结果：

![9459e8f806decf68e2f4e2302cb3a938.png][]

eg3：创建带格式的Excel表格

代码：

\# -\*- coding: utf-8 -\*-

\# 添加一些格式

import xlsxwriter

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

worksheet = workbook.add\_worksheet('test2')

bold = workbook.add\_format(\{'bold': True\}) \# 设置粗体，默认False

money = workbook.add\_format(\{'num\_format': '$\#,\#\#0'\}) \# 定义数字格式

worksheet.write('A1', 'Item', bold) \# 设置自定义表头加粗

worksheet.write('B1', 'Cost', bold)

expenses = (

\['Rent', 1000\],

\['Gas', 100\],

\['Food', 300\],

\['Gym', 50\],

)

row = 1

col = 0

for item, cost in (expenses):

worksheet.write(row, col, item) \# 默认格式写入

worksheet.write(row, col + 1, cost, money) \# 设置带money格式写入

row += 1

worksheet.write(row, 0, 'Total', bold)

worksheet.write(row, 1, '=SUM(B2:B5)', money)

workbook.close()

运行结果：

![702114e7509ec40cb59c79d20ae8fbb0.png][]

eg4：创建带日期的Excel表格

代码：

\# -\*- coding: utf-8 -\*-

import xlsxwriter

from datetime import datetime

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

worksheet = workbook.add\_worksheet() \# 不命名sheet名称，默认为'sheet1'

bold = workbook.add\_format(\{'bold': 1\})

money\_format = workbook.add\_format(\{'num\_format': '$\#,\#\#0'\})

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)

expenses = (

\['Rent', '2013-01-13', 1000\],

\['Gas', '2013-01-14', 100\],

\['Food', '2013-01-16', 300\],

\['Gym', '2013-01-20', 50\],

)

row = 1

col = 0

for item, date\_str, cost in (expenses):

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

worksheet.write(row, 0, 'Total', bold)

worksheet.write(row, 2, '=SUM(C2:C5)', money\_format)

workbook.close()

运行结果：

[55eafa3c096d5820cc7d9897cd963cd6.png]: /images/20221119/08867dec4cc54461974e1e23e78227e3.png
[9459e8f806decf68e2f4e2302cb3a938.png]: https://img-blog.csdnimg.cn/img_convert/9459e8f806decf68e2f4e2302cb3a938.png
[702114e7509ec40cb59c79d20ae8fbb0.png]: https://img-blog.csdnimg.cn/img_convert/702114e7509ec40cb59c79d20ae8fbb0.png