Python write to Excel (Xlswriter)--Generate chart

First, line chart:

#-*-Coding:utf-8-*-import xlsxwriter# Create a excelworkbook = Xlsxwriter. Workbook ("Chart_line.xlsx") # Creates a sheetworksheet = Workbook.add_worksheet () # worksheet = Workbook.add_worksheet ("Bug_ Analysis ") # Custom style, Bold bold = Workbook.add_format ({' Bold ': 1}) #--------1, prepare data and write to Excel---------------# write data to Excel Create icon to use headings = [' Number ', ' TestA ', ' testb ']data = [' 2017-9-1 ', ' 2017-9-2 ', ' 2017-9-3 ', ' 2017-9-4 ', ' 2017-9-5 ', ' 2 017-9-6 '], [10, 40, 50, 20, 10, 50], [30, 60, 70, 50, 40, 30],]# write the table header Worksheet.write_row (' A1 ', headings, bold) # write data Worksheet.write_column (' A2 ', data[0]) worksheet.write_column (' B2 ', data[1]) worksheet.write_column (' C2 ', data[2]) #- -------2, generate a chart and insert into Excel---------------# Create a bar chart (line chart) Chart_col = Workbook.add_chart ({' type ': ' line '}) # Configure the first series of data chart_col.add_series ({# Sheet1 here is the default value, because we did not specify a sheet name when we created the sheet # if we set the sheet name when we created the sheet, this is set to the corresponding value ' Nam E ': ' =sheet1! $B ', ' categories ': ' =sheet1! $A $: $A $7 ', ' values ': ' =sheet1! $B $: $B $7 ', ' line ': {' color': ' Red '},}) # Configure the second series of data chart_col.add_series ({' name ': ' =sheet1! $C ', ' categories ': ' =sheet1! $A $: $A $7 ', ' values ': ' =sheet1! $C $: $C $7 ', ' line ': {' color ': ' Yellow '}, ') # Configure the second series of data (using another syntax) # chart_col.add_series ({# ' name ': [' She Et1 ', 0, 2],# ' categories ': [' Sheet1 ', 1, 0, 6, 0],# ' values ': [' Sheet1 ', 1, 2, 6, 2],# ' line ': {' color ': ' Yel Low '},#} # Sets the title of the chart and the x, Y axis information chart_col.set_title ({' Name ': ' The XXX site Bug Analysis '}) Chart_col.set_x_axis ({' Name ': ' Test number '}) Chart_col.set_y_axis ({' name ': ' Sample length (mm) '}) # set the style of the chart Chart_col.set_style (1) # Insert the chart into the worksheet and set the offset Worksheet.insert_chart (' A10 ', Chart_col, {' X_offset ': +, ' Y_offset ': Ten}) Workbook.close ()  

：

Second, the bar chart:

#-*-Coding:utf-8-*-import xlsxwriter# Create a excelworkbook = Xlsxwriter. Workbook ("Chart_column.xlsx") # Creates a sheetworksheet = Workbook.add_worksheet () # worksheet = Workbook.add_worksheet (" Bug_analysis ") # Custom style, Bold bold = Workbook.add_format ({' Bold ': 1}) #--------1, prepare data and write to Excel---------------# write data to Excel Create icon to use headings = [' Number ', ' TestA ', ' testb ']data = [' 2017-9-1 ', ' 2017-9-2 ', ' 2017-9-3 ', ' 2017-9-4 ', ' 2017-9-5 ', ' 2 017-9-6 '], [10, 40, 50, 20, 10, 50], [30, 60, 70, 50, 40, 30],]# write the table header Worksheet.write_row (' A1 ', headings, bold) # write data Worksheet.write_column (' A2 ', data[0]) worksheet.write_column (' B2 ', data[1]) worksheet.write_column (' C2 ', data[2]) #- -------2, generate a chart and insert into Excel---------------# Create a Histogram (column chart) Chart_col = Workbook.add_chart ({' type ': ' column '}) # Configure the first series of data chart_col.add_series ({# Sheet1 here is the default value, because we did not specify a sheet name when we created the sheet # if we set the sheet name when we created the sheet, this is set to the corresponding value ' Nam E ': ' =sheet1! $B ', ' categories ': ' =sheet1! $A $: $A $7 ', ' values ': ' =sheet1! $B $: $B $7 ', ' line ': {' Color ': ' Red '}, # Configure the second series of data (using another syntax) chart_col.add_series ({' name ': ' =sheet1! $C $ ', ' categories ': ' =sheet1! $A $: $A     $7 ', ' values ': ' =sheet1! $C $ $: $C $7 ', ' line ': {' color ': ' Yellow '}, ') # Configure the second series of data (using another syntax) # chart_col.add_series ({#  ' Name ': [' Sheet1 ', 0, 2],# ' categories ': [' Sheet1 ', 1, 0, 6, 0],# ' values ': [' Sheet1 ', 1, 2, 6, 2],# ' line ': {' Color ': ' Yellow '},#} # Sets the title of the chart and the x, Y axis information chart_col.set_title ({' Name ': ' The XXX site Bug Analysis '}) chart_col.set_x_a XIs ({' name ': ' Test number '}) Chart_col.set_y_axis ({' name ': ' Sample length (mm) '}) # set the style of the chart Chart_col.set_style (1) # Insert the chart into the worksheet and offset Worksheet.insert_chart (' A10 ', Chart_col, {' X_offset ': +, ' Y_offset ': Ten}) Workbook.close ()

：

Ps:

In fact, the first two charts only change one point: The line character is column

Chart_col = Workbook.add_chart ({' type ': ' Column '})

Three, pie chart:

#-*-Coding:utf-8-*-import xlsxwriter# Create a excelworkbook = Xlsxwriter. Workbook ("Chart_pie.xlsx") # Creates a sheetworksheet = Workbook.add_worksheet () # Custom style, Bold bold = Workbook.add_format ({' Bold ': 1}) #--------1, prepare the data and write to Excel---------------# Write data to Excel, create an icon using data = [[' Closed ', ' active ', ' reopen ', ' NT '], [1 012, 109, 123, 131],]# write Data worksheet.write_row (' A1 ', data[0], bold) worksheet.write_row (' A2 ', data[1]) #--------2, Generate a chart and insert into Excel---------------# Create a histogram (pie chart) Chart_col = Workbook.add_chart ({' type ': ' Pie '}) # Configure the first series of data Chart_ Col.add_series ({' name ': ' Bug analysis ', ' categories ': ' =sheet1! $A ': ' $D ', ' values ': ' =sheet1! $A $: $D ', ' PO        INTs ': [{' Fill ': {' color ': ' #00CD00 '}}, {' Fill ': {' color ': ' Red '}}, {' Fill ': {' color ': ' Yellow '}}, {' Fill ': {' color ': ' Gray '},],}) # Set the title of the chart and the x, Y axis information chart_col.set_title ({' name ': ' Bug Analysis '}) # Set Chart style charts _col.set_style (10) # Insert the chart into the worksheet as well as the offset worksheet.insert_chart (' B10 ', Chart_col, {' X_offset ': +, ' Y_offset ': Ten}) Workbook.close () 

：

Resources:

Http://xlsxwriter.readthedocs.io/chart_examples.html

Http://xlsxwriter.readthedocs.io/chart.html

Python write to Excel (Xlswriter)--Generate chart