Python模块之XlsxWriter

数据报表之Excel操作模块

数据报表之Excel操作模块

一、介绍

利用Python操作Excel的模块XlsxWriter,可以操作多个工作表的文字，数字，公式，图表等。此模板只能新建，添加数据，而不能编辑已知数据。

XlsxWriter模块支持的功能:

100%兼容的Excel XLSX文件，支持Excel2003、Excel2007等版本
支持所有Excel单元格数据格式
单元格合并、批注、自动筛选、丰富多格式字符串等
支持工作表PNG、JPEG图像，自定义图表
内存优化模式支持写入大文件

二、安装

pip install XlsxWriter #pip安装方法

三、模块常用方法说明

1.Workbook类

语法：Workbook([filename[,options])
Workbook类代表整个电子表格文件，并且存储在磁盘上。
参数解析：
filename(String类型)为创建Excel文件存储路径
option(Dict类型)为可选的Workbook参数，一般作为初始化工作表内容格式，例如{'strings_to_numbers':True}表示使用worksheet.write()方法时激活字符串转换数字

add_worksheet([sheetname])方法

作用：添加一个新的工作表
参数解析：sheetname（String类型）为可选的工作表名称，默认为Sheet1

worksheet1 = workbook.add_worksheet()      #Sheet1
worksheet2 = workbook.add_worksheet("表1")  #表1
worksheet2 = workbook.add_worksheet()  #sheet3

add_format([properties])方法

作用：在工作表中创建一个新的饿格式对象来格式化单元格
参数解析：properties(dict类型)为指定一个格式属性的字典
例如：设置加粗格式对象，workbook.add_format({"blod":True})
通过Format methods（格式化方法）也可实现与字体加粗效果

bold = workbook.add_format({"bold":True})   #方法一

bold2 = workbook.add_format()
bold2.set_bold()      #方法二

add_chart(option)方法

作用：工作表中创建一个图表对象，内部是通过insert_chart()方法来实现
参数解析：options(dict类型)为图表指定一个字典属性
例如：设置一个线条类型的图标对象,workbook.add_chart({"type":"line"})

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

close() 方法

作用：关闭工作表文件

workbook.close()

2.Worksheet类

代表一个Excel工作表，不能直接实例化，需要通过Workbook对象调用add_worksheet()方法来创建

worksheet = xlsxwriter.Workbook('test.xlsx').add_worksheet()   #创建Sheet1工作表对象

*write(row , col , args)方法

作用：写普通数据到工作表的单元格
参数解析：参数row为行坐标；col为列坐标；坐标索引起始值为0；*args为数据内容，可以为数字、公式、字符串或格式对象
write方法已经作为其他更加具体数据类型方法的别名，包括：

- write_string() 写入字符串类型数据，如

	worksheet.write_string(0,0,"Your text here")

- write_number() 写入数字类型数据，如

    worksheet.write_number('A2',2.345)

- write_blank() 写入空类型数据，如

    worksheet.write("A2",None)

- write_formula() 写入公式类型数据，如

	worksheet.write_formula(2,0,"=SUM(B1:B5)")

- write_datetime() 写入日期类型数据，如

	import datetime
	worksheet.write_datetime(7 , 0 , datetime.datetime.strptime('2018-07-31','%Y-%m-%d'),workbook.add_format({'num_format':'yyyy-m-dd'}))

- write_boolean() 写入逻辑类型数据，如

	worksheet.write_boolean(0 , 0 , True)

- write_url() 写入超链接类型数据，如

	worksheet.write_url("A1","ftp://www.python.org/")

write方法与别名对应关系

	worksheet.write(0 , 0 , 'hello')               #对应write_string()
    worksheet.write(1, 0 , 3.002)              #write_number()
    worksheet.write(2 , 0 , '=SUM(A1:A2)')    #write_formula()
    worksheet.write(3 , 0 , '')                  #write_blank()
    worksheet.write(4, 0 , None)           #write_blank()

set_row(row , height , cell_format , options)方法

作用：设置行单元格的属性
参数解析：row(int类型)行位置，起始下标为0；height(float类型)行高，单位像素；cell_format(format类型)指定格式对象;options(dict类型)设置行hidden(隐藏)、level(组合分级)、collapsed(折叠)

worksheet.write("A1","hello")     #在A1单元格写入"hello"字符串
cell_format = workbook.add_format({"bold":True})   #定义一个加粗的格式对象
worksheet.set_row(0 , 40 , cell_format)   #设置第1行单元格高度40像素，且引用加粗格式对象
worksheet.set_row(1,None,None,{"hidden":True})   #隐藏第2行单元格

set_column(first_col , last_col , width , cell_format , options) 方法

作用：设置一列或多列单元格属性
参数解析：first_col(int类型)，开始列位置，起始下标0；last_col(int类型)结束列位置，起始下标为0；width(float类型)设置列宽；cell_format(Format类型)格式对象；options(dict类型)行hidden(隐藏)、level(组合分级)、collapse(折叠）

	worksheet.write('A1' , 'hello')       #在A1单元格写入"hello"字符串
    worksheet.write('B1',"world")      #在B1单元格写入“world”字符串
    cell_format = workbook.add_format({"bold":True})    #定义一个加粗的格式对象
    
    worksheet.set_column(0 , 1 , 10 , cell_format)     #设置0到1列单元格宽度为10像素，且引用加粗格式对象
    worksheet.set_column("C:D", 20)                #设置C到D列单元格宽度20像素
    worksheet.set_column("E:G" , None , None , {"hidden":1})    #隐藏E到G列单元格

insert_image(row , col , image[, options])方法

作用：插入图片到指定单元格，支持PNG、JPEG、BMP等图片格式
参数解析：row(int类型)行坐标;col（int类型）列坐标；坐标索引值为0；image(string类型)图片路径；options(dict类型)可选参数，指定图片的位置、比例、链接URL等信息

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

3.Chart类

语句：chart = xlswriter.Workbook("a.xlsx").add_chart({type,'图表类型'})
支持图标类型包括面积、条形图、柱形图、折线图、饼图、散点图、股票和雷达等
图表类型：

area:创建一个面积样式的图表

bar:创建一个条形样式图表

column:创建一个柱形样式的图表

line:创建一个线条样式的图表

pie:创建一个饼图样式的图表

scatter:创建一个散点样式的图表

stock:创建一个股票样式的图表

radar:创建一个雷达样式的图表

chart = xlswriter.Workbook("a.xlsx").add_chart({type,'column'})
worksheet.insert_chart('A7',chart)   #在A7单元格插入图表

char.add_series(options)方法

作用：添加一个数据系列到图表
参数解析：options(dict类型)设置图表系列选项的字典

char.add_series({
   'categories':'=Sheet1!$A$1:$A$5',    #categories指图表类型标签范围
   'values':"=Sheet1!$B$1:$B$5",        #values设置图表数据范围
   'line':{'color':'red'},              #line设置图表线条属性，包括颜色，宽度等
})

set_x_axis(options)方法

作用：设置图表X轴选项

    chart.set_x_axis({
      'name':'月销售额'，     #设置x轴标题名称
      'name_font':{'size':14,'bold':True},      #设置X轴标题字体属性
      "num_font":{'italic':True},                #设置x轴数字字体属性
    })

set_size(options)方法

作用：设置图表大小

chart.set_size({"width":720,"height":576})   #width为宽度,height为高度

set_title(options)方法

作用：设置图表标题

chart.set_titile({"name":"每月销售统计"})

set_style(style_id)方法

作用：设置图表样式
参数解析：style_id为不同数字则代表不同样式

chart.set_style(37)    #37代表37样式

set_table(options)方法

作用：设置X轴为数据表格形式

chart.set_table()

四、简单案例

import xlsxwriter

workbook = xlsxwriter.Workbook("demo1.xlsx")  #创建一个Excel文件
bold = workbook.add_format({"bold":True})     #定义一个加粗的格式对象

worksheet.set_column('A:A',20)  #设定第一列（A）宽度为20像素
bold = workbook.add_format({"bold":True})   #定义一个加粗的格式对象

worksheet.write('A1','hello')
worksheet.write('A2','World',bold) #A2单元格写入"World"并引用加粗格式对象bold
worksheet.write('B2',u'中文测试',bold)  #B2单元格写入中文并引用加粗格式对象bold

worksheet.write(2,0,32)  #第2行，第0列，填入数字32
worksheet.write(3,0,35.5)  #第3行，第0 列，填入数字35.5
worksheet.write(4,0,'=SUM(A3:A4)') #在第4行，第0列，计算A3:A4的和

worksheet.insert_image('B5','img/书.jpg')    #在B5位置插入图片
workbook.close()   #关闭Excel文件，同时意味着保存数据