Python Excel操作模块XlsxWriter

1 workbook类

定义：Workbook(filename[,options])

该类创建一个XlsxWriter的Workbook对象。Workbook代表整个电子表格文件，并且存储在磁盘上。filename为字符串类型，表示创建的excel文件存储路径；

options：为dict类型，是可选参数，一般作为初始化工作表内容格式

1.2 add_worksheet

add_worksheet([sheetname])方法，用于添加一个新的工作表

参数说明：

• sheetname为工作表名称，默认是sheet1

例如：

worksheet1 = workbook.add_worksheet()   #sheet1

worksheet2 = workbook.add_worksheet('test')     #test

worksheet3 = workbook.add_worksheet('data')     #data

worksheet4 = workbook.add_worksheet()   #sheet4

1.3 add_format

add_format([properties])方法，用于在工作表中创建一个新的格式对象来格式化单元格

参数说明：

• properties：为dict类型，为指定一个格式属性的字典

例如设置一个加粗的格式对象如下：

bold = workbook.add_format({'bold': True})

等价的语句如下：

bold = workbook.add_format()     bold.set_bold()

1.4 add_chart

add_chart(options)方法，用于在工作表中创建一个图表对象，内部是通过insert_chart()方法来实现的

参数说明：

• options：参数为dict类型，是为图标指定一个字典属性。

例如设置一个线条行的图表对象，代码如下：

chart = workbook.add_chart({'type': 'line'})

1.5 close

close()方法，作用是关闭工作表文件，如：workbook.close()

2 Worksheet类

代表一个excel工作表，是比较核心的一个类。Worksheet对象不能直接实例化，而是通过Workbook对象调用add_worksheet()方法来创建

如：worksheet = workbook.add_worksheet() #  创建一个sheet

常见的方法如下：

2.1 write

write(row, col, *args)方法：是用来将普通数据写入单元格中。

参数说明：

• row：行坐标；

• col：列坐标； 坐标索引起始值为0

• *args：无名字参数为数据内容，可为数字，公式，字符串或格式对象

为了简化不同数据类型的写入过程，write方法已作为其他更加具体的数据类型方法的别名，如：

1. write.string()：写入字符串类型数据

2. wirte_number()：写入数字型数据

3. write_blank()：写入空类型数据

4. write_formula()：写入公式型数据

5. write_datetime()：写入日期型数据

6. wirte_boolean()：写入逻辑型数据

7. write_url()：写入超链接型数据

2.2 set_row

set_row(row, height, cell_format, options)方法，用于设定行单元格的属性。

参数说明：

• row：指定行位置，起始下标为0；

• height：为float类型，设定行高，单位像素；

• cell_format：format类型，指定对象格式；

• options，字典类型，设置行hidden（隐藏）、level（组合分级）、collpsed（折叠）

示例如下：

worksheet2.write('A1', 'hello')
bold = workbook.add_format({'bold': True})
# 设定A1行高40，加粗
worksheet2.set_row(0, 40, bold)
# 隐藏第二行
worksheet2.set_row(1, None, None, {'hidden': True})

2.3 set_column

set_column(first_col, last_col, width, cell_format, options)方法，用于设置一列或多列单元格的属性

参数说明：

• first_col：整型，指定开始列位置，起始下标为0；

• last_col：整型，指定结束列位置，起始下标为0；

• width：float类型，设置列宽；

• cell_format：format类型，指定格式对象；

• options：dict类型，设置hidden（隐藏）、level（组合分级）、collpsed（折叠）；

示例如下：

worksheet2.write('A1', 'hello')
worksheet2.write('B1', 'world')
bold = workbook.add_format({'bold': True})
# 设定列A到B单元格宽度10像素，加粗
worksheet2.set_column(0, 1, 10, bold)
# 设置C到D单元格宽度20像素
worksheet2.set_column('C:D', 20)
# 隐藏E到G单元格
worksheet2.set_column('E:G', None, None, {'hidden': 1})

2.4 insert_image

insert_image(row, col, image[, options])方法，用于插入图片到指定的单元格，支持PNG，JPEG，BMP等多种格式。

参数说明：

• row：行坐标，起始索引值为0；

• col：列坐标，起始索引值为0；

• image：string类型，是图片路径；

• options：dict类型，是可选参数，用于指定图片位置，如URL等信息；

示例如下：

# 在B5单元格插入python-logo.png图片，超链接为http://python.org
worksheet1.insert_image('B5', 'img/python-logo.png', {'url': 'http://python.org'})

3 chart类

chart类是图表组件，支持包括面积、条形图、柱状图、折线图、散点图等，一个图表对象是通过Workbook的add_chart方法创建，通过{type, ‘图表类型’}字典来制定图表类型，示例如下：

# 创建一个column（柱形）图表
chart = workbook.add_chart({type, 'column'})

常见的图表样式如下：

1. area：面积样式的图表

2. bar：条形图

3. column：柱状图

4. line：线条样式的图表

5. pie：饼形图

6. scatter：散点图

7. stock：股票样式的图表

8. radar：雷达样式的图表

然后通过Worksheet的insert_chart()方法插入到指定位置，示例如下：

worksheet.insert_chart（'A7'， chart） # 在A7单元格插入图表

常见的方法如下： 

3.1 add_series

chart.add_series(options)方法：用于添加一个数据系列的图表，参数options为字典类型，用于设置图表系列选项的字典

示例如下：

chart.add_series({
    'categories': '=Sheet1!$A$1:$A$5',
    'values': '=Sheet1!$B$1:$B$5',
    'line': {'color': 'red'},
})

add_series的常用三个选项：

• categories：设置图表类别标签范围；

• values：设置图表数据范围；

• line：设置图表线条属性，包括宽度、颜色等；

3.2 set_x_axis

set_x_axis(options)方法，设置图表X轴选项

示例如下：

chart.set_x_axis({
    'name': 'x name',
    'name_font': {'size': 14, 'bold': True}
    'num_font': {'italic': True}
})

参数说明：

• name：设置x轴名称；
• name_font：设置x轴字体；
• num_font：设置x轴数字字体属性； 

3.3 set_size

set_size(options)方法，用于设置图表大小

示例如下：

chart.set_size({'width': 720, 'height': 576})

参数说明： 

• width：设置宽度；

• height：设置高度；

3.4 set_title

set_title(options)方法，设置图表标题

示例如下：

chart.set_title({'name': 'test'})

3.5 set_style

set_style(style_id)方法，用于设置图表样式，style_id为不同数字代表不同样式

示例如下：

chart.set_style(37)

3.6 set_table

set_table(options)方法，设置x轴为数据表格式。

示例如下：

chart.set_table()

4 简单实例

# coding: utf-8
import xlsxwriter

workbook = xlsxwriter.Workbook('demo1.xlsx')  # 创建一个Excel文件
worksheet = workbook.add_worksheet()  # 创建一个工作表对象
worksheet.set_column('A:A', 20)  # 设定第一列(A)宽度为20像素
bold = workbook.add_format({'bold': True})  # 定义一个加粗的格式对象
worksheet.write('A1', 'Hello')  # A1单元格写入'Hello'
worksheet.write('A2', 'World', bold)  # A2单元格写入'World'并引用加粗格式对象bold
worksheet.write('B2', u'中文测试', bold)  # B2单元格写入中文并引用加粗格式对象bold
worksheet.write(2, 0, 32)  # 用行列表示法写入数字'32'与'35.5'
worksheet.write(3, 0, 35.5)  # 行列表示法的单元格下标以0作为起始值,'3,0'等价于'A3'
worksheet.write(4, 0, '=SUM(A3:A4)')  # 求A3:A4的和,并将结果写入'4,0',即'A5'
worksheet.insert_image('B5', 'img/python-logo.png')  # 在B5单元格插入图片
workbook.close()  # 关闭Excel文件

5 操作Excel的案例

# -*- coding: UTF-8 -*-
import datetime
import time

import xlsxwriter

startTime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')  # 现在
print(startTime)
startTime1 = time.time()
print(startTime1)

workbook = xlsxwriter.Workbook('kami1.xlsx')  # 创建一个Excel文件
worksheet = workbook.add_worksheet()  # 创建一个sheet

title = ['姓名', '学号']  # 表格title
worksheet.write_row('A1', title)  # title 写入Excel

for i in range(1, 10):
    row_num = str(i + 1)
    row = 'A' + row_num
    num = str(i)
    data = ['学生' + num, num, ]
    worksheet.write_row(row, data)
    i += 1

workbook.close()

endTime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')  # 结束
print(endTime)

endTime1 = time.time()
print(endTime1)