数据报表之Excel操作模块

根据《Python自动化运维:技术与最佳实践》  刘天斯    总结而成

1. 安装xlsxwriter:pip installXlsxWriter

2. 简单实例:

#coding: utf-8

import xlsxwriter

workbook = xlsxwriter.Workbook('demo1.xlsx') #创建一个Excel文件

worksheet = workbook.add_worksheet() #创建一个工作表对象

worksheet.set_column('A:A', 20) #设定第一列(A)宽度为20像素

bold = workbook.add_format({'bold': True}) #定义一个加粗的格式对象

worksheet.write('A1', 'Hello') #A1单元格写入'Hello'

worksheet.write('A2', 'World', bold) #A2单元格写入'World'并引用加粗格式对象bold

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

worksheet.write(2, 0, 32) #用行列表示法写入数字'32'与'35.5'

worksheet.write(3, 0, 35.5) #行列表示法的单元格下标以0作为起始值,'3,0'等价于'A3'

worksheet.write(4, 0, '=SUM(A3:A4)') #求A3:A4的和,并将结果写入'4,0',即'A5'

worksheet.insert_image('B5', 'img/python-logo.png') #在B5单元格插入图片

workbook.close() #关闭Excel文件

3. Workbook类

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

1) add_worksheet([sheetname])方法,添加一个新的工作表,参数sheetname(String类型)为可选的工作表名称,默认为Sheet1

worksheet1 = workbook.add_worksheet() # Sheet1

worksheet2 = workbook.add_worksheet('Foglio2') # Foglio2

worksheet3 = workbook.add_worksheet('Data') # Data

worksheet4 = workbook.add_worksheet() # Sheet4

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

也可以写成:bold = workbook.add_format()

bold.set_bold()

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

4) add_chartsheet([sheetname]), Add a newadd_chartsheet to a workbook.

Parameters:    sheetname(string) – Optional chartsheet name, defaults to Chart1, etc.

Return type:    A chartsheet object.

5) workbook.close()  Close the Workbook object and write the XLSXfile.

6) set_properties()  The set_properties method can beused to set the document properties of the Excel file created by XlsxWriter.These properties are visible when you use the Office Button -> Prepare -> Properties optionin Excel and are also available to external applications that read or indexwindows files.

eg:

workbook.set_properties({

    'title':   'This is an example spreadsheet',

    'subject': 'With document properties',

    'author':  'John McNamara',

    'manager': 'Dr. Heinz Doofenshmirtz',

    'company': 'of Wolves',

    'category': 'Example spreadsheets',

    'keywords': 'Sample, Example, Properties',

    'comments': 'Created with Python andXlsxWriter'})

7) define_name()  Create a defined name in the workbook to useas a variable.

Parameters:    name (string) – The defined name.

           formula (string) – The cell or rangethat the defined name refers to.

eg:workbook.define_name('Exchange_rate','=0.96')

worksheet.write('B3', '=B2*Exchange_rate')

 

4. Worksheet类,Worksheet类代表了一个Excel工作表,例如将数据写入单元格或工作表格式布局等。Worksheet对象不能直接实例化,取而代之的是通过Workbook对象调用add_worksheet()方法来创建。

1)write(row,col,*args)方法,作用是写普通数据到工作表的单元格,参数row为行坐标,col为列坐标,坐标索引起始值为0;*args无名字参数为数据内容,可以为数字、公式字符串或格式对象。为了简化不同数据类型的写入过程,write方法已经作为其他更加具数据类型方法的别名,包括:

write_string()写入字符串类型数据,worksheet.write_string(0, 0, 'Your texthere');

write_number()写入数字类型数据,如:worksheet.write_number('A2', 2.3451);

write_blank()写入空类型数据,如:worksheet.write('A2', None);

write_formula()写入公式类型数据, worksheet.write_formula(2, 0,'=SUM(B1:B5)');

write_datetime()写入日期类型数据,如:worksheet.write_datetime(7, 0,datetime.datetime.strptime('2013-01-23','%Y-%m-%d'),workbook.add_format({'num_format': 'yyyy-mm-dd'}));

write_boolean()写入逻辑类型数据,如:worksheet.write_boolean(0, 0, True);

write_url()写入超链接类型数据,如:worksheet.write_url('A1', 'ftp://www.python.org/')。

2) 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行单元格

3) set_column(first_col,last_col,width,cell_format,options)方法,作用为设置一列或多列单元格属性。参数first_col(int类型)指定开始列位置,起始下标为0;参数last_col(int类型)指定结束列位置,起始下标为0,可以设置成与first_col一样;参数width(float类型)设置列宽;参数cell_format(Format类型)指定格式对象;参数options(dict类型)设置行hidden(隐藏)、level(组合分级)、collapsed(折叠)

eg: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即(A到B) 列单元格宽度为10像素,且引用加粗格式对象

worksheet.set_column('C:D', 20) #设置C到D列单元格宽度为20像素

worksheet.set_column(5, None, None, {'hidden': 1}) #隐藏第5行单元格

4) insert_image(row,col,image[,options])方法,作用是插入图片到指定单元格,支持PNG、JPEG、BMP等图片格式。参数row为行坐标,col为列坐标,坐标索引起始值为0;参数image(string类型)为图片路径;参数options(dict类型)为可选参数,作用是指定图片的位置、比例、链接URL等信息。操作示例如下:

 

5. Chart类,Chart类实现在XlsxWriter模块中图表组件的基类,支持的图表类型包括面积、条形图、柱形图、折线图、饼图、散点图、股票和雷达等,一个图表对象是通过Workbook(工作簿)的add_chart方法创建,通过{‘type’:'图表类型'}字典参数指定图表的类型,语句如下:chart = workbook.add_chart({'type':'column'}) #创建一个column(柱形)图表

然后再通过Worksheet(工作表)的insert_chart()方法插入到指定位置,语句如下:

worksheet.insert_chart('A7', chart) #在A7单元格插入图表

更多图表类型说明:

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

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

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

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

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

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

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

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

1) chart.add_series(options)方法,作用为添加一个数据系列到图表,参数options(dict类型)设置图表系列选项的字典,操作示例如下:

chart.add_series({

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

'values': '=Sheet1!$B$1:$B$5',

'line': {'color':'red'},

})

add_series方法最常用的三个选项为categories、values、line,其中categories作为是设置图表类别标签范围;values为设置图表数据范围;line为设置图表线条属性,包括颜色、宽度等。

2) set_x_axis(options)方法,设置图表X轴选项,示例代码如下

chart.set_x_axis({

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

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

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

})

3)set_size(options)方法,设置图表大小,如chart.set_size({'width':720,'height':576}),其中width为宽度,height为高度。

4) set_title(options)方法,设置图表标题,如 chart.set_title({'name':'Year EndResults'})

5)set_style(style_id)方法,设置图表样式,style_id为不同数字则代表不同样式,如chart.set_style(37)

7)set_table(options)方法,设置X轴为数据表格形式,如chart.set_table()


eg:

import xlsxwriter


workbook=xlsxwriter.Workbook('report1.xlsx')
worksheet=workbook.add_worksheet()
chart = workbook.add_chart({'type':'column'})
title=[u'业务名称',u'星期一',u'星期二',u'星期三',u'星期四',u'星期五',u'星期六',u'星期日',u'平均流量']
buname=[u'业务官网',u'新闻中心',u'购物频道',u'体育频道',u'亲子频道']
data=[[150,152,158,149,155,145,148],
      [89,88,95,98,56,95,45],
      [201,220,231,159,155,198,222],
      [75,78,73,79,71,79,78],
      [89,98,92,81,88,99,100]
      ]
format=workbook.add_format()
format.set_border(1)
format_title=workbook.add_format()
format_title.set_border(1)
format_title.set_bg_color('#cccccc')
format_title.set_align('center')
format_title.set_bold()
format_ave=workbook.add_format()
format_ave.set_border(1)
format_ave.set_num_format('0.00')
worksheet.write_row('A1',title,format_title)
worksheet.write_column('A2',buname,format)
worksheet.write_row('B2',data[0],format)
worksheet.write_row('B3',data[1],format)
worksheet.write_row('B4',data[2],format)
worksheet.write_row('B5',data[3],format)
worksheet.write_row('B6',data[4],format)
def chart_series(cur_row):
    worksheet.write_formula('I'+cur_row,'=AVERAGE(B'+cur_row+':H'+cur_row+')',format_ave)
    chart.add_series({'categories':'=Sheet1!$B$1:$H$1',
                      'values':'=Sheet1!$B$'+cur_row+':$H$'+cur_row,
                      'line':{'color':'black'},
                      'name':'=Sheet1!$A$'+cur_row
                      })




for row in range(2,7):
    chart_series(str(row))
chart.set_size({'width':577,'height':287})
chart.set_title({'name':u'业务流量周报图表'})
chart.set_y_axis({'name':'Mb/s'})
worksheet.insert_chart('A8',chart)
workbook.close()



 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值