官网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，如需转载请自行联系原作者
