python模块之XlsxWriter 详解

74 阅读 0 评论 49 点赞

我是靠谱客的博主陶醉花生，最近开发中收集的这篇文章主要介绍python模块之XlsxWriter 详解，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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，使用：

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公式计算

# 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自定义格式：

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写入时间格式

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

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)

柱状图：

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()