004. Python Xlsxwriter Module

Simple usage Demo

#!/usr/bin/python#Coding:utf-8#basic usage of XlsxwriterImportXlsxwriter#1. Create an Excel fileWorkbook = Xlsxwriter. Workbook ('demo1.xlsx')#2. Create a worksheet sheet objectWorksheet =Workbook.add_worksheet ()#3. Set the first column (A) width to 20 pixelsWorksheet.set_column ('a:a', 20)#4. Define a bold format objectBold = Workbook.add_format ({'Bold': True})#5. Writing data to cells#5.1 write ' Hello ' to cell A1Worksheet.write ('A1','Hello')#5.2 write ' world ' to A2 cell and use bold formatWorksheet.write ('A2',' World', Bold)#5.3 Write Chinese to B2 cells and use bold formattingWorksheet.write ('B2', u'Chinese characters', Bold)#5.4 Write numbers to row 2nd, No. 0 column (i.e. A3 cell) and 3rd row, No. 0 column (i.e. A4 cell) by using the column notation (all rows and columns are starting from 0)Worksheet.write (2,0,10) Worksheet.write (3,0,20)#5.5 A3, A4 cell and write to A5 cell, this shows that the formula can be directly usedWorksheet.write (4,0,'=sum (A3:A4)')#5.6 Inserting a picture in cell B5Worksheet.insert_image ('B5','./demo.png')#5.7 Close and save the fileWorkbook.close ()

The Excel document generated after running has the following effect:

Comprehensive Example Demo: Charting the business traffic data of a website

#!/usr/bin/python#Coding:utf-8ImportXlsxwriter#Create an Excel fileWorkbook = Xlsxwriter. Workbook ('chart.xlsx')#Create a worksheet sheet object, using the default name: "Sheet1"Worksheet =Workbook.add_worksheet ()#Create a Chart objectChart = Workbook.add_chart ({'type':'column'})#Defining the data table headertitle = [u'Business Name', u'Monday', u'Tuesday', u'Wednesday', u'Thursday', u'Friday', u'Saturday', u'Sunday', u'Average Flow']#define a list of business namesBuname = [u'Business Website', u'News Center', u'Shopping Channel', u'Sports channel', u'Parent-Child channel']#define a data list of 5 channels seven days a weekdata = [    [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]]#define data Formatter format object, set border bold 1 pixelsFormatter =Workbook.add_format () Formatter.set_border (1)#define title bar formatting objects: Border bold 1 pixels, background color gray, cell content centered, boldTitle_formatter =Workbook.add_format () Title_formatter.set_border (1) Title_formatter.set_bg_color ('#cccccc') Title_formatter.set_align ('Center') Title_formatter.set_bold ()#define the average column data format object: Border bold 1 pixels, numbers are displayed by 2 decimal placesAve_formatter =Workbook.add_format () Ave_formatter.set_border (1) Ave_formatter.set_num_format ('0.00')#Defining chart data series functionsdefchart_series (Cur_row): Chart.add_series ({'Categories':'=sheet1! $B $: $H $',        'Values':'=sheet1! $B ${}: $H ${}'. Format (cur_row,cur_row),' Line':{'Color':'Black'},        'name':'=sheet1! $A ${}'. Format (Cur_row)}) #Note: Where categories represents the x-axis, values represent the y-axis, line represents the lines style, and name denotes the legend item#The title bar, business name, traffic data are written to the cell in rows and columns, and different format objects are referencedWorksheet.write_row ('A1', Title,title_formatter) Worksheet.write_column ('A2', Buname,formatter)#Write data in rows 2nd through 6th and add the 2nd to 6th row of data to the chart series forIinchRange (2,7): Worksheet.write_row ('b{}'. Format (i), data[i-2],formatter)#calculates the average traffic bar data and writesWorksheet.write_formula ('i{}'. Format (i),'=average (b{}:h{})'. Format (i,i), Ave_formatter)#add each row of data to a chart sequencechart_series (str (i))#Set Chart SizeChart.set_size ({'width': 577,'Height': 287})#set up a chart big titleChart.set_title ({'name': U'Business Flow Weekly Report'})#Set the y-axis captionChart.set_y_axis ({'name':'MB/s'})#Insert a chart in cell A8Worksheet.insert_chart ('A8', chart)#close an Excel documentWorkbook.close ()

The Excel document generated after running has the following effect:

Reprinted from: https://www.cnblogs.com/jiayongji/p/7119059.html

004. Python Xlsxwriter Module