python和excel相关的是什么知识点_python深入剖析操作Excel工作报表知识点

本文所讲主要内容：

1、 Excel操作

2、 模块的常用方法

3、 chart 图表类

4、 实践：定制自动化业务流量报表周报

1、Excel操作

Excel是当今最流行的电子表格处理软件，支持丰富的计算函数及图表，在系统运营方面广泛用于运营数据报表，比如业务质量、资源利用、安全扫描等报表，同时也是应用系统常见的文件导出格式，以便数据使用人员做进一步加工处理。

本节主要讲述利用Python操作Excel的模块XIsxWriter ,可以操作多个工作表的文字、数字、公式、图表等。

XIsxWriter 模块具有以下功能:100%兼容的Excel XLSX文件，支持Excel 2003、Excel 2007等版本。

支持所有Excel单元格数据格式; 单元格合并、批注、自动筛选、丰富多格式字符串等。

支持工作表PNG、JPEG图像，自定义图表。

内存优化模式支持写入大文件。

1.1、 XlsxWriter模块的安装方法如下:pip install xlsxwriterimport xlsxwriter

#创建一个ExceL 文档

workbook = xlsxwriter.Workbook('demo.xlsx')

#创建一个工作对象

worksheet = workbook.add_worksheet()

#设定第一列Adap E20像素

worksheet.set_column('A:E',20)

#定义一个加粗的格式对象

bold = workbook.add_format({'bold':True})

#在A1单元格写入‘hello’blod加粗

worksheet.write('A1','hello')

worksheet.write('A2','word',bold)

worksheet.write('B2','中文测试',bold)

#用行列表的写法写入数据‘32’余35.5

worksheet.write(2,0,32)

worksheet.write(3,0,35.5)

worksheet.write(4,0,'=SUM(A3:A4)')

#插入图片

worksheet.insert_image('B5','1.png')

workbook.close()

运行效果如下：

2 模块的常用方法

2.1、Workbook

Workbook类的定义：def __init__(self, filename=None, options=None): 实现创建一个XlsxWriter的 Worlbook对象，workbook类代表整个电子表格文件，并且存储子啊磁盘上，参数filename为字符，就是常见的excel文件存储路径。

参数options 字典类型为可选的workbook参数，一般作为初始化工作表的内容的格式，例如{‘name’:’for’}。

2.2、add_worksheet

add_worksheet([sheetname])方法，作用是添加一个新的工作表，参数sheetname为字符串类型的可选的工作表名称默认为sheet 。

小案例：import xlsxwriter

#创建workbook对象

workbook = xlsxwriter.Workbook('demo1.xlsx')

#添加工作报表

worksheet1 = workbook.add_worksheet()

worksheet2 = workbook.add_worksheet('Foglio2')

worksheet3 = workbook.add_worksheet('data')

worksheet4 = workbook.add_worksheet()

workbook.close()

运行效果如下：

2.3、add_format()

add_format(properies)方法，作用是在工作表中创建一个新的格式对象来格式化元格。参数properties (dict类型)为指定一个格式属性的字典，例如设置一个加粗格式workbook.add_format({bold: True})。

通过Format methods (格式化方式也可以实现格式的设置，等价的设置加粗格式代码如下:bold= workbook.add_format()

bold.set_bold()

2.4、add_chart()

add_chart(options)方法，作用是在工作表中创建个图表对象，内部是通过insert_chart()方法来实现,参数opions (dict类型)为图表指定一个字典属性，例如设置个线条内部的图表对象，代码为chart = workhook.add_chart({type:‘line’}) 。

2.5、close()

close()方法作用是关闭工作表文件。

3、Chart 类

Chart类实现在XlsxWriter模块中图表组件的基类，支持的图表类型包括面积、条形图、 柱形图、折线图、饼图、散点图、股票和雷达等。

它主要是workbook中的add_chart方法创建，通过type指定类型，语句如下：# 创建一个 column (柱形)图表

chart = workbook.add_chart({'type':'column'})

更多图表类型说明：area：创建一个面积样式的图表；

bar：创建一个条形样式的图表；

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

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

pie：创建一个饼图样式的图表；

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

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

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

然后再通过Worksheet (工作表)的insert_chart()方法插人到指定位置，语句如下：worksheet.insert_chart('A4',chart)

然后再通过Worksheet (工作表)的insert_chart()方法插人到指定位置，语句如下：worksheet.insert_chart('A4',chart)

下面介绍chart类的几个常用方法：chart.add_series (options)方法

它的作用为添加一个数据系列到图表，参数options (diet 类型)设置图表系列选项的字典，操作示例如下：chart.add_series({

'categories':'=Sheet1!$A$1:$G$1',

'values': '=Sheet1!$A$1:$G$1',

'line': {'color':'black'},

})

add_series方法最常用的三个选项为categories、values、line。其中categories作为是设置图表类别标签范围；

values为设置图表数据范围；

line为设置图表线条属性，包括颜色、 宽度等。

set_x_axis(options)方法

设置图表X轴选项，示例代码如下：chart.set_x_axis({

'name': 'Earnings per Quarter', # 设置 X 轴标题名称

'name_font':{ 'size':14,'bold':True}, # 设置 X 轴标题字体属性

'num_font' :{ 'italic': True }, # 设置 X 轴数字字体属性

}Set_size(options)方法

设置图表大小，其中width为宽度，height为高度，示例代码如下：chart.set_size({ 'width':577,'height':287})Set_title(options)方法

设置图表标题，代码示例如下：chart.set_title({'name':u'业务流量周报图表'})set_style(style_id)方法

设置图表样式，style_id为不同数字则代表不同样式，代码示例如下：chart.set_style(37)set_table(options)方法

设置X轴为数据表格形式，如chart.set_table()

案例展示：import xlsxwriter

#创建一个ExceL 文档

workbook = xlsxwriter.Workbook('demo.xlsx')

#创建一个工作对象

worksheet = workbook.add_worksheet()

data = [

[150,152,158,149,155,145,148],

[89, 88,95,93, 98,100, 99],

[201,200,198,175,170,198,195],

]

worksheet.write_row("A1",data[0])

worksheet.write_row("A2",data[1])

worksheet.write_row("A3",data[2])

chart = workbook.add_chart({'type':'column'})

chart.add_series({

'categories':'=Sheet1!$A$1:$G$1',

'values': '=Sheet1!$A$1:$G$1',

'line': {'color':'black'},

})

chart.set_x_axis({

'name': 'Earnings per Quarter', # 设置 X 轴标题名称

'name_font':{ 'size':14,'bold':True}, # 设置 X 轴标题字体属性

'num_font' :{ 'italic': True }, # 设置 X 轴数字字体属性

})

chart.set_size({ 'width':577,'height':287}) # 设置图表大小

chart.set_title({'name':u'业务流量周报图表'}) #设置图表(上方)大标题

chart.set_y_axis({'name':'Mb/s'})

worksheet.insert_chart('A4',chart)

workbook.close()

效果显示如下：

4、实践：定制自动化业务流量报表周报

本次实践通过定制网站5个频道的流量报表周报，通过XlsxWriter模块将流量数据写入 Excel文档，同时自动计算各频道周平均流量，再生成数据图表。

具体是通过workbook.add_chart({type: 'column”})方法指定图表类型为柱形，使用write_row、write_column方法分别以行、列方式写数据，使用add_format()方法定制表头、表体的显示风格。

使用add_series() 方法将数据添加到图表，同时使用chart.set_size、set_title、set_y_axis设置图表的大小及标 题属性，最后通过insert_chart方法将图表插入工作表中。我们可以结合2.3节的内容来实现 周报的邮件推送，本示例略去此功能。