python模块之xlsxwriter
官网Tutorial:http://xlsxwriter.readthedocs.io/tutorial
Xlsx是python用来构造xlsx文件的模块,可以向excel2007+中写text,numbers,formulas 公式以及hyperlinks超链接。
可以完成xlsx文件的自动化构造,包括:
合并单元格,制作excel图表等功能:
1,Introduction:
xlsxWriter支持多种excle功能;与excel完美兼容;写大文件,速度快且只占用很小的内存空间
不支持读或者改现有的excel文件
2, Installing:
sudo pip install XlsxWriter;
sudo easy_install XlsxWriter;
或者源码安装:http://github.com/jmcnamara/XlsxWriter/archive/master.tar.gz
3,使用:
1
2
3
4
5
|
import xlsxwriter
workbook = xlsxwriter.Workbook( 'hello.xlsx' ) # 建立文件
worksheet = workbook.add_worksheet() # 建立sheet, 可以work.add_worksheet('employee')来指定sheet名,但中文名会报UnicodeDecodeErro的错误
worksheet.write( 'A1' , 'Hello world' ) # 向A1写入
workbook.close() |
excel公式计算
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
# 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开始的,按绝对位置'A1'写入是从1开始的 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)' ) # 调用excel的公式表达式
workbook.close() |
excel自定义格式:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
import xlsxwriter # 建文件及sheet.
workbook = xlsxwriter.Workbook( 'Expenses02.xlsx' )
worksheet = workbook.add_worksheet() # Add a bold format to use to highlight cells. 设置粗体,默认是False
bold = workbook.add_format({ 'bold' : True }) # Add a number format for cells with money. 定义数字格式
money = workbook.add_format({ 'num_format' : '$#,##0' }) # Write some data headers. 带自定义粗体blod格式写表头
worksheet.write( 'A1' , 'Item' , bold)
worksheet.write( 'B1' , 'Cost' , bold) # Some data we want to write to the worksheet.
expenses = (
[ 'Rent' , 1000 ],
[ 'Gas' , 100 ],
[ 'Food' , 300 ],
[ 'Gym' , 50 ],
) # Start from the first cell below the headers.
row = 1
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, money) # 带自定义money格式写入
row + = 1 # Write a total using a formula.
worksheet.write(row, 0 , 'Total' , bold)
worksheet.write(row, 1 , '=SUM(B2:B5)' , money)
workbook.close()
|
excel写入时间格式
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
|
from datetime import datetime
import xlsxwriter
# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook( 'Expenses03.xlsx' )
worksheet = workbook.add_worksheet()
# Add a bold format to use to highlight cells.
bold = workbook.add_format({ 'bold' : 1 })
# Add a number format for cells with money.
money_format = workbook.add_format({ 'num_format' : '$#,##0' })
# Add an Excel date format.
date_format = workbook.add_format({ 'num_format' : 'mmmm d yyyy' })
# Adjust the column width.
worksheet.set_column( 1 , 1 , 15 )
# Write some data headers.
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()
|
@@@ write方法提供了python类型到excel类型的转换, XlsxWriter支持excel工作表最大1048576行记录,16384条列记录,超出可以选择再建新sheet
1
2
3
4
5
6
7
|
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()
|
关于更多字符串、数字、颜色及位置等excel格式:http://xlsxwriter.readthedocs.io/format.html
4, 图标
这个是我比较关注的利用excel工具进行图标统计的功能
相比较python的matplotlib的画图模块,excel的图标更加漂亮灵活一些
Chart: Area, Bar, Column, Doughnut, Line, Pie, Scatter, Stock, Radar
workbook = xlswriter.Workbook('chart.xls')
worksheet = workbook.add_sheet('First_example') # 普通工作表
建立Chart对象: chart = workbook.add_chart({type, 'column'})
将图插入到sheet中: worksheet.insert_chart('A7', chart)
或者可以建立图表工作表chartsheet
chartsheet = workbook.add_charsheet()
chartsheet.set_char(chart)
柱状图:
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
|
import xlsxwriter
workbook = xlsxwriter.Workbook( 'chart.xlsx' )
worksheet = workbook.add_worksheet()
# Create a new Chart object. chart = workbook.add_chart({ 'type' : 'column' })
# Write some data to add to plot on the chart. data = [
[ 1 , 2 , 3 , 4 , 5 ],
[ 2 , 4 , 6 , 8 , 10 ],
[ 3 , 6 , 9 , 12 , 15 ],
] worksheet.write_column( 'A1' , data[ 0 ]) # 按列插入
worksheet.write_column( 'B1' , data[ 1 ])
worksheet.write_column( 'C1' , data[ 2 ])
# Configure the chart. In simplest case we add one or more data series. chart.add_series({ 'values' : '=Sheet1!$A$1:$A$5' })
chart.add_series({ 'values' : '=Sheet1!$B$1:$B$5' })
chart.add_series({ 'values' : '=Sheet1!$C$1:$C$5' })
# Insert the chart into the worksheet. worksheet.insert_chart( 'A7' , chart)
workbook.close() |
workbook.add_chart({'type':'column'}) # 默认格式
workbook.add_chart({'type':'column', 'substyle':'percent_stacked'}) # 按百分比展示
workbook.add_chart({'type':'column', 'substyle':'stacked'})
其他类型chart也是这样:
本文转自 326647452 51CTO博客,原文链接:http://blog.51cto.com/svsky/1944131,如需转载请自行联系原作者