Excel is today's most popular spreadsheet processing software, supporting a wide range of computational functions and charts, widely used in operating data reports, such as business quality, resource utilization, security scanning and other reports, but also the application system common file export format, so that data users to do further processing. This section focuses on using Python to manipulate Excel's module Xlsxwriter (https://xlsxwriter.readthedocs.org) to manipulate text, numbers, formulas, charts, and more on multiple worksheets. The Xlsxwriter module has the following features: 100% compatible Excel xlsx files, support for Excel 2003, Excel 2007 and other versions, support for all Excel cell data formats, cell merging, annotations, AutoFilter, rich multi-format strings, etc. Support Working table PNG, JPEG image, custom chart; memory-optimized mode supports writing large files. The installation method of the Xlsxwriter module is as follows: # pip install xlsxwriter #pip安装方法 # easy_install xlsxwriter #easy_install安装方法 #源码安装方法 # Curl-o-L http://github.com/jmcnamara/XlsxWriter/archive/master.tar.gz# tar zxvf master.tar.gz# cd xlsxwriter-master/# sudo Python setup.py install below with a simple function to demonstrate the example, the implementation of inserting text (Chinese and English characters), numbers (summation), pictures, cell format, etc., the code is as follows: "/home/test/xlsxwriter/simple1.py" # Coding:utf-8Import Xlsxwriter workbook = Xlsxwriter. Workbook (' demo1.xlsx ') #创建一个Excel文件worksheet = Workbook.add_worksheet () #创建一个工作表对象Worksheet.set_column (' a:a ', a) #设定第一列 (A) width is 20 pixels bold = Workbook.add_format ({' Bold ': True}) #定义一个加粗的格式对象 WORKSHEET.WR Ite (' A1 ', ' hello ') #A1单元格写入 ' hello ' worksheet.write (' A2 ', ' World ', bold) #A2单元格写入 ' world ' and refer to bold format Object Boldworksheet.write ( ' B2 ', U ' Chinese test ', bold) #B2单元格写入中文并引用加粗格式对象bold Worksheet.write (2, 0, 3) #用行列表示法写入数字 ' 32 ' with ' 35.5 ' Worksheet.write (, 0, 3 5.5) #cell subscript for row and column notation with 0 as the starting value, ' 3,0 ' equivalent to ' A4 ' Worksheet.write (4, 0, ' =sum (A3:A4) ') #求A3: A4 and writes the result to ' 4,0 ', i.e. ' A5 ' worksheet.insert_ Image (' B5 ', ' img/python-logo.png ') #在B5单元格插入图片workbook. Close () # Close the Excel file program that is generated in DEMO1.XLSX document 3-1. More reference http://book.2cto.com/201411/48255. HTML------------------------------------------------------------------------------------This practice by customizing the website 5 channels of Traffic report weekly, Through the Xlsxwriter module, traffic data is written to Excel documents, and the average weekly traffic of each channel is calculated automatically, and then the data graph is generated. Specifically, by Workbook.add_chart ({' type ': ' Column '}) method specifies that the chart type is a column, using the Write_row, Write_column methods to write data in rows and columns, using Add_format () method to customize the display style of the header and the body, use the Add_series () method to add data to the chart, and use the Chart.set_size, Set_title, Set_y_axis to set the size and caption properties of the chart, and finally through the Insert_ The chart method inserts a diagram into the worksheet. We can combine the contents of section 2.3 to achieve weekly mail push, this example omits this feature. The code is implemented as follows: "/home/test/xlsxwriter/simple2.py" #coding: Utf-8import xlsxwriter workbook = Xlsxwriter. Workbook (' chart.xlsx ') #创建一个Excel文件worksheet = Workbook.add_worksheet () #创建一个工作表对象chart = Workbook.add_chart ({' type ': ' column '}) #创建一个图表对象 # define data Header list title = [u' Business name ', U ' Monday ', U ' Tuesday ', U ' Wednesday ', U ' Thursday ', U ' Friday ', U ' Saturday ', U ' Sunday ', U ' average flow ']buname= [u ' Business official website ', U ' News Center ', u ' shopping channel ', U ' sports channel ', U ' parent Channel '] #定义频道名称 # define 5 channels 7 days a week flow data list [ [150,152,158,149,155,145,148], [ 89,88,95,93,98,100,99], [201,200,198,175,170,198,195], [75,77,78,78,74,70,79], [88,85,87,90,93,88,84],]format=workbook.add_format () #定义format格式对象format. Set_border (1) #定义format对象单元格边框加粗 (1 pixels) format format_title=workbook.add_format () #定义format_title格式对象format_ Title.set_border (1) #定义format_title对象单元格边框加粗 (1 pixels) format format_title.set_bg_color (' #cccccc ') #定义format_ Title Object cell background color is &N Bsp # ' #cccccc ' Format format_title.set_align (' center ') #定义format_ Title Object Cell Center-aligned format Format_title.set_bold () #定义format_title对象单元格内容加粗的格式 format_ave=workbook.add_format () #定义format_ave格式对象format_ave. Set_border (1) #定义format_ave对象单元格边框加粗 (1 pixels) format _ave.set_num_format (' 0.00 ') #定义format_ave对象单元格数字类别显示格式 #下面分别以行或列写入方式将标题, business name, traffic data written to the original cell, Also referencing different format objects 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) #计算 (average function) frequency & nbsp , &NB Sp #道周平均流量 chart.add_series ({ ' categories ': ' =sheet1! $B $: $H ', #将 ' Monday to Sunday ' as a chart data label (x-axis) ' values ': ' =sheet1!$ b$ ' +cur_row+ ': $H $ ' +cur_row, #频道一周所有数据作 & nbsp , &NB Sp #为数据区域 ' line ': {' Color ': ' Black '}, #线条颜色定 Meaning black (black) ' name ': ' =sheet1! $A $ ' +cur_row, #引用业务名称为图例项 }) for Row in range (2, 7): #数据域以第2 Chart data series function call chart_series (str) #chart. Set_table () & nbsp #设置X轴表格格式, this example does not enable #chart.set_style #设置图表样式, this example does not enable Chart.set_size ({' width ': 577, ' height ': 287}) #设置图表大小chart. Set_title ({' Name ': U ' Business Traffic weekly Chart '}) #设置图表 (above) headline Chart.set_y_axis ({' name ': ' MB/s '}) #设置y轴 (left) small title worksheet.insert_chart (' A8 ', chart) #在A8单元格插入图表workbook. Close () # Closing an Excel document the above example creates a worksheet that is shown in 3-11. 3.4.1 Section Xlsxwrite module Common class and Method description reference official website http://xlsxwriter.readthedocs.org
Excel action module for data reports
