Python-Xlsx-Study

Posted on Edited on In Symbols count in article: 9.1k Reading time ≈ 8 mins.

Python里面涉及xlsx文件的相关操作。

记录一下。

#Python/xlsx

Reference: Python3之excel操作–xlsxwriter模块_python 3 xlswriter-CSDN博客

相关库安装

1
pip install xlsxwriter

常用操作

创建工作簿、工作表

1
2
3
4
5
6
7
8
9
10
11
12
13
# encoding=gbk
import xlsxwriter
 
# 创建工作簿
workbook = xlsxwriter.Workbook('test.xlsx')#创建一个excel文件
 
# 创建工作表
worksheet = workbook.add_worksheet('test-sheet1')#在文件中创建一个名为test-sheet1的sheet,不加名字默认为sheet1
worksheet2 = workbook.add_worksheet()   #  Sheet2
worksheet3 = workbook.add_worksheet('test-sheet2')
worksheet4 = workbook.add_worksheet()  #  Sheet4
 
workbook.close()

单元格填值

单个单元格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
# encoding=gbk
import xlsxwriter
 
# 一：创建工作簿
workbook = xlsxwriter.Workbook('test.xlsx')#创建一个excel文件
 
# 二：创建工作表
worksheet1 = workbook.add_worksheet('test-sheet1')#在文件中创建一个名为test-sheet1的sheet,不加名字默认为sheet1
worksheet2 = workbook.add_worksheet()   #  Sheet2
worksheet3 = workbook.add_worksheet('test-sheet2')
worksheet4 = workbook.add_worksheet()  #  Sheet4
 
# 三：设置单元格的值
worksheet1.write('A1', 'hello123')  # 在A1单元格写上HELLO
worksheet1.write('A2', '123测试456')  # 在B2上写上中文加粗
 
# 使用行列方式,下标从0开始
worksheet1.write(2, 0, 100) #  第3行第1列（即A3） 写入100
worksheet1.write(3, 0, 99.8)  #  第4行第1列（即A4） 写入99.8
worksheet1.write(4, 0, '=SUM(A3:A4)')  # 写上excel公式
 
 
# 合并单元格
worksheet1.merge_range(1,2,3,4,'合并01')
worksheet1.merge_range('C6:E7','合并02')
 
 
# worksheet1.write_number：写入数字
worksheet1.write_number(5, 0, 1001)  #   Int or float
 
# worksheet1.write_blank：写入空格
worksheet1.write_blank(6, 0,'xx')  # 不管第三个参数是什么，都写入空格
# worksheet1.write_formula：写入公式
worksheet1.write_formula(7, 0, '=SUM(A3:A4)')
 
# worksheet1.write_datetime：写入时间格式
from datetime import datetime
date_format = workbook.add_format({'num_format': 'yyyy-mm-dd H:M:S'})
worksheet1.write_datetime(8, 0, datetime.today(),date_format )
 
# worksheet1.write_boolean:写入逻辑数据
worksheet1.write_boolean(9, 0,False)
 
# worksheet1.write_url:写入链接地址
worksheet1.write_url(10, 0,'www.baidu.com')
 
workbook.close()

多个单元格

以列表形式进行数值填入。
只要填充位置以及填充数值足够清晰就可以完成相关数值的写入。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# encoding=gbk
import xlsxwriter
 
# 一：创建工作簿
workbook = xlsxwriter.Workbook('test.xlsx')#创建一个excel文件
 
# 二：创建工作表
worksheet1 = workbook.add_worksheet('test-sheet1')#在文件中创建一个名为test-sheet1的sheet,不加名字默认为sheet1
# worksheet2 = workbook.add_worksheet()   #  Sheet2
# worksheet3 = workbook.add_worksheet('test-sheet2')
# worksheet4 = workbook.add_worksheet()  #  Sheet4
 
# # write_row 写入
lst=['12','34','56','78']
worksheet1.write_row(5, 0, lst)  # 把数据写到第6行的，从第1列开始到第4列上
# # write_column 写入
worksheet1.write_column(6, 0,lst) # 把数据写到第1列，从第7行开始到第10行上
 
workbook.close()

设置格式

设置单元格样式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
# encoding=gbk
import xlsxwriter
 
# 一：创建工作簿
workbook = xlsxwriter.Workbook('test.xlsx')#创建一个excel文件
 
# 二：创建工作表
worksheet1 = workbook.add_worksheet('test-sheet1')#在文件中创建一个名为test-sheet1的sheet,不加名字默认为sheet1
# worksheet2 = workbook.add_worksheet()   #  Sheet2
# worksheet3 = workbook.add_worksheet('test-sheet2')
# worksheet4 = workbook.add_worksheet()  #  Sheet4
 
# 大部分样式如下：
format = {
    'font_size': 10,  # 字体大小
    'bold': True,  # 是否粗体
    # 'bg_color': '#101010',  # 表格背景颜色
    'fg_color': '#00FF00',
    'font_color': '#0000FF',  # 字体颜色
    'align': 'center',  # 水平居中对齐
    'valign': 'vcenter',  # 垂直居中对齐
    # 'num_format': 'yyyy-mm-dd H:M:S',# 设置日期格式
    # 后面参数是线条宽度
    'border': 1, # 边框宽度
    'top': 1,  # 上边框
    'left': 1,  # 左边框
    'right': 1,  # 右边框
    'bottom': 1  # 底边框
}
 
style = workbook.add_format(format)   # 设置样式format是一个字典
 
# # write_row 写入
lst=['12','34','56','78']
worksheet1.write_row(5, 0, lst,style)  # 把数据写到第6行的，从第1列开始到第4列上
# # write_column 写入
worksheet1.write_column(6, 0,lst) # 把数据写到第1列，从第7行开始到第10行上
worksheet1.write('A1',200,style)
worksheet1.write(2,0,900,style)
workbook.close()

设置单元格行高、列宽

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# encoding=gbk
import xlsxwriter
 
# 一：创建工作簿
workbook = xlsxwriter.Workbook('test.xlsx')#创建一个excel文件
 
# 二：创建工作表
worksheet1 = workbook.add_worksheet('test-sheet1')#在文件中创建一个名为test-sheet1的sheet,不加名字默认为sheet1
 
# 设置行宽
worksheet1.set_row(0,60)
 
# 设置列宽
worksheet1.set_column(1,2,30)
worksheet1.set_column('D:F',40)
workbook.close()

设置标签颜色

1
worksheet1.set_tab_color('#0000FF')

插入图片

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# encoding=gbk
import xlsxwriter
 
# 一：创建工作簿
workbook = xlsxwriter.Workbook('test.xlsx')#创建一个excel文件
 
# 二：创建工作表
worksheet1 = workbook.add_worksheet('test-sheet1')#在文件中创建一个名为test-sheet1的sheet,不加名字默认为sheet1
 
# # 设置行宽
# worksheet1.set_row(0,60)
# # 设置列宽
# worksheet1.set_column(1,2,30)
# worksheet1.set_column('D:F',40)
# worksheet1.set_tab_color('#0000FF')
 
worksheet1.insert_image('A3', '00.jpg', {'url': 'https://blog.csdn.net/'})
workbook.close()

插入图表

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

  • area：面积样式的图表
  • bar：条形图
  • column：柱状图
  • line：线条样式的图表
  • pie：饼形图
  • scatter：散点图
  • stock：股票样式的图表
  • radar：雷达样式的图表

创建chart类

1
2
3
4
5
# 1:创建chart对象
chart = workbook.add_chart({'type':'line'})
# 2：设置chart对象相关数据，属性
# 3：然后通过Worksheet的insert_chart()方法插入到指定位置
worksheet1.insert_chart('A3',chart,{'x_offset':25,'y_offset':10})   #放置图表位置

使用方法

添加数据

以字典类型添加数据。

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

设置x轴以及y轴

1
2
3
4
5
6
7
8
9
10
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轴数字字体属性；
"""

其他方法

1
2
3
4
5
6
7
8
9
10
11
12
13
# set_size(options)方法，用于设置图表大小，示例如下：
chart.set_size({'width': 720, 'height': 576})
# width：设置宽度；
# height：设置高度；
 
# set_title(options)方法，设置图表标题，示例如下：
chart.set_title({'name': 'test'})
 
# set_style(style_id)方法，用于设置图表样式，style_id为不同数字代表不同样式
chart.set_style(37)
 
# set_table(options)方法，设置x轴为数据表格式。
chart.set_table()

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
# encoding=gbk
import xlsxwriter
 
# 一：创建工作簿
workbook = xlsxwriter.Workbook('test.xlsx')#创建一个excel文件
 
# 二：创建工作表
worksheet1 = workbook.add_worksheet('test-sheet1')#在文件中创建一个名为test-sheet1的sheet,不加名字默认为sheet1
 
# 三：数据准备
row_data=['姓名','数学','英语','语文']
col_data = [
    ['张三','李四','老王','德华','赵四'],
    [90, 85, 120, 130, 99],
    [70, 65, 120, 109, 110],
    [60, 95, 130, 120, 79]
]
 
worksheet1.write_row('A1',row_data)
worksheet1.write_column('A2',col_data[0])
worksheet1.write_column('B2',col_data[1])
worksheet1.write_column('C2',col_data[2])
worksheet1.write_column('D2',col_data[3])
 
# 四：创建chart对象
chart = workbook.add_chart({'type':'line'})        # 线条样式的图表
# chart = workbook.add_chart({'type':'column'})      # 柱状图
# chart = workbook.add_chart({'type':'area'})       # 面积样式的图表
# chart = workbook.add_chart({'type':'stock'})      # 股票样式的图表
# chart = workbook.add_chart({'type':'bar'})        # 条形图
# chart = workbook.add_chart({'type':'pie'})        # 饼形图
# chart = workbook.add_chart({'type':'scatter'})    # 散点图
# chart = workbook.add_chart({'type':'radar'})      # 雷达样式的图表
 
 
chart.add_series({
    'name':'=test-sheet1!$B$1',
    'categories': '=test-sheet1!$A$2:$A$6',
    'values': '=test-sheet1!$B$2:$B$6',
    'line': {'color': 'red'},
    'pie': {'color': 'red'},
})
 
chart.add_series({
    'name':'=test-sheet1!$C$1',
    'categories': '=test-sheet1!$A$2:$A$6',
    'values': '=test-sheet1!$C$2:$C$6',
    'line': {'color': 'yellow'},
    'pie': {'color': 'yellow'},
})
 
chart.add_series({
    'name':'=test-sheet1!$D$1',
    'categories': '=test-sheet1!$A$2:$A$6',
    'values': '=test-sheet1!$D$2:$D$6',
    'line': {'color': 'blue'},
    'pie': {'color': 'blue'},
})
 
chart.set_title({'name':'测试'})
chart.set_x_axis({'name':"x轴"})
chart.set_y_axis({'name':'y轴'})          #设置图表表头及坐标轴
 
chart.set_style(1)
# chart.set_style(37)
 
worksheet1.insert_chart('B8',chart,{'x_offset':25,'y_offset':10})   #放置图表位置
 
workbook.close()
 
 
"""
官方文档：https://xlsxwriter.readthedocs.io/chart.html
"""
-------------THE END-------------