Python xlsxwriter module Create aexcel table

This article mainly introduces the Python xlsxwriter module to create a aexcel form, has a certain reference value, now share to everyone, the need for friends can refer to

Installation using PIP install Xlsxwriter to install, Xlsxwriter used to create Excel table, the function is very powerful, the following details:

1. Simply use an instance of Excel:

#coding: Utf-8import xlsxwriterworkbook = Xlsxwriter. Workbook (' d:\\suq\\test\\demo1.xlsx ') #创建一个excel文件worksheet = workbook.add_worksheet (' Test ') #在文件中创建一个名为TEST的sheet , the default name is Sheet1 worksheet.set_column (' a:a ', ') #设置第一列宽度为20像素bold = Workbook.add_format ({' Bold ': True}) #设置一个加粗的格式对象 Worksheet.write (' A1 ', ' HELLO ') #在A1单元格写上HELLOworksheet. Write (' A2 ', ' World ', bold) #在A2上写上WORLD, and set to Bold Worksheet.write (' B2 ', U ' Chinese test ', bold) #在B2上写上中文加粗 Worksheet.write (2,0,32) #使用行列的方式写上数字32, 35,5worksheet.write ( 3,0,35.5) #使用行列的时候第一行起始为0, so 2, 0 represents the first column of the third row, equivalent to A4worksheet.write (4,0, ' =sum (A3:A4) ') #写上excel公式worksheet. Insert_ Image (' B5 ', ' f:\\1.jpg ') #插入一张图片 workbook.close ()

2. Common Method Description

1.Workbook class

The workbook class creates a Xlsxwriter workbook object that represents the entire spreadsheet file and is stored on disk.

Add_worksheet (): Used to create worksheets, default to Sheet1

Add_format (): Creates a new format object to format the cell, such as Bold=workbook.add_format ({' Bold ': True})

You can also use Set_bold, for example: Bold=workbook.add_format () Bold.set_bold ()

#border: Border, align: alignment, Bg_color: Background color, font_size: Font size, bold: font Bold top = Workbook.add_format ({' Border ': 1, ' align ': ' Center ', ' bg_color ': ' CCCCCC ', ' font_size ': +, ' bold ': True})

Add_chart: Create a Chart object, which is implemented internally using the Insert_chart () method, Options (dict type) specify a dictionary property for the chart

Close (): Close file

2.Worksheet class

Worksheet represents an Excel worksheet, which is the core of Xlsxwriter, and here are a few core methods

Write (Row,col,*args): writes normal data to the worksheet cell, row row coordinates, col column coordinates, starting at 0, *args for writing, can be string, text, formula, etc., the writer method has been used as an alias for other more specific data type methods

Write_string (): Write String type, worksheet.write_string (0,0, ' your text ')

Write_number (): Write number type, Worksheet.write_number (' A2 ', 1.1)

Write_blank (): Write null type data, Worksheet.write_blank (' A2 ', None)

Wirte_formula (): Write formula type, Worksheet.write_formula (2,0, ' =sum (B1:B5))

Write_datetime (): Write date type data, Worksheet.write_datetime (7,0,datetime.datetime.strptime (' 2014-01-02 ', '%Y-%m-%d '), Workbook.add_format ({' Num_format ': ' Yyyy-mm-dd '));

Write_boolean (): Write logical class data, Worksheet.write_boolean (0,0,true)

Write_url (): Write hyperlink type data, Worksheet.write_url (' A1 ', ' ftp://www.python.org ')

Write_column (): Writes to a column followed by an array

Wirte_row (): writes to a line followed by an array

Set_row (row,height,cell_format,options): This method sets the properties of the row cell, row specifies the row position, height specifies the height, the unit is pixel, Cell_format

Specify format object, parameter options set Hiddeen (hidden), level (combined rating), collapsed (folding, for example:

Cell_format=workbook.add_format ({' Bold ': True})

Worksheet.set_row (0,40,cell_format) set the first line height 40, bold

Set_column (first_col,last_col,width,cell_format,options):

Sets the properties of the column cell, as described above. Worksheet.set_column (0,1,10) worksheet.set_column (' C:d ', 20)

insert_image (Row,col,image[,options]): This method is to insert a picture into the specified cell

For example, insert a picture with a hyperlink of www.python.org

Worksheet.insert_image (' B5 ', ' f:\\1.jpg ', {' url ': ' http://www.python.org '})

3.Chart class

The chart class implements the base class for the icon component in the Xlsxwriter module, which includes area, bar chart, column chart, folding chart, pie chart, scatter chart, stock and radar. A chart object is created through the workbook Add_chart method, specifying the type of the chart through the {type, ' chart type '} dictionary parameter, with the following statement:

Chart = Workbook.add_chart ({type, ' column '}) #创建一个column图表

More Chart Type descriptions:

Area: Create a chart of the space style;

Bar: Creates a bar-style chart;

Column: Create a column-style chart;

Line: Create a chart of the lines style

Pie: Create a chart with a pie chart style

Scatter: Creating a scatter-style chart

Stock: Create a stock-style chart;

Radar: Create a radar CCTV chart

The Insert_chart () method is then inserted into the specified position, with the following statement:

Worksheet.insert_chart (' A7 ', chart)

The Chart.add_series (options) method, which is to add a data series to a chart, parameter options (dict type) sets the dictionary of chart series options, with the following examples:

Chart.add_series ({' Categories ': ' =sheet1! $A $$1: $A $ ', ' values ': ' =sheet1! $A $$1: $A $ ', ' line ': ={' color ': ' Red '}})

Categories,values,line is most commonly used, the categories function is to set the Chart category label range, the values is to set the chart data range, line to set the chart lines properties, including color width and so on.

Set_x_axis (Options): Set the chart x axis options, for example:

Chart.set_x_axis ({' name ': ' Earning per quarter ', ' Name_font ': {' size ': +, ' bold ': true}, ' Num_font ': {' italic ': true}})

Set_size (Options): Sets the size of the chart, such as

Chart.set_size ({' Width ': 720, ' Height ': 576})

Set_title (Options): Set the title, such as Chart.set_title ({' name ': ' TEST title '}

Set_style (style_id): Set chart style,

Set_table (Options): Set the x-axis to data table format

Here is an example of creating a file system usage:

#coding: Utf-8 import xlsxwriter workbook = Xlsxwriter. Workbook (' d:\\suq\\test\\demo1.xlsx ') #创建一个excel文件worksheet = Workbook.add_worksheet (' Sheet1 ') #在文件中创建一个sheet # border: Border, align: alignment, Bg_color: Background color, font_size: Font size, bold: Font Bold Top=workbook.add_format ({' Border ': 6, ' align ': ' Center ', ' bg_color ': ' CCCCCC ', ' font_size ': ' Bold ': True}) #设置单元格格式title =[u ' file system ', U ' total capacity ', U ' use size ', U ' remaining size '] # Set the first row header information buname=['/dev/mapper/vg_basic-lv_root ', ' tmpfs ', '/dev/sda1 '] #设置左边第一排信息 worksheet.write_row (' A1 ', title, Top) Worksheet.write_column (' A2 ', buname,top) worksheet.set_column (' A:d ', max) #A到D列设置宽度, the width setting must be the whole column setting, the height must be the whole line setting # Worksheet.set_row (0,40) #设置第一行高度为40像素format_data =workbook.add_format ({' Align ': ' Center ', ' font_size ': ') #设置单元格格式 DATA=[[17678,4393,12388],[9768,8900,868],[24285,2715,21000]] #模拟文件系统的数据, data[0],[1],[2] represent all spaces, use space, The remaining space Worksheet.write_row (' B2 ', data[0],format_data) #将数据写入, where the entire line is installed to write Worksheet.write_row (' B3 ', data[1],format_data) Worksheet.write_row (' B4 ', Data[2],format_data) chart=workbook.add_chart ({' type ': ' ColumN '}) #创建表格, the table type is Columnchart.set_title ({' Name ': U ' file system usage '}) #设置表格的titlefor i in [' B ', ' C ', ' D ']:chart.add_series ({' Categories ': ' sheet1! $A $: $A $4 ', ' values ': ' sheet1!$ ' +i+ ' $2:$ ' +i+ ' $4 ', ' name ': ' =sheet1!$ ' +i+ ' $ ') #注意上面的表格中, The data for each row is shown together in the diagram, meaning that values are b2:b4 chart.set_size ({' width ': $, ' height ': $}) #设置表格的大小chart. Set_y_axis ({' Name ': ' MB '}) #设置表格y轴信息 #chart.set_style #设置表格的样式worksheet. Insert_chart (' A8 ', chart) #插入表格workbook. Close ()

The displayed picture results are as follows: