Xlwt:python's Write Excel module

Recently work encountered the export of data, generate an Excel table, some of the data in the statistics, and to some of the eligible data to add the corresponding background color of the functional requirements, so, the Python write Excel Module XLWT Research, in the work process found that, Want to achieve data statistics, XLWT does not have a direct formula, but to a formula in the format to write to Excel method, so the formula for the Excel of what the study, but also finally realized the desired function, now think of the use of relatively large record, So that you can pick them up when you use them later.

Installation of XLWT

Simple, can be installed directly using PIP

Pip Install XLWT

Import of XLWT

Very simple, direct import can

Import XLWT

In the simplest case (the coordinates used by XLTW are different from the coordinates of the Excel table, Excel has two coordinate representations, but all start with 1, for example, the first cell in the upper-left corner is A1 (the letter is the horizontal axis, the number is the ordinate) or the R1C1 (R1 indicates the ordinate, the C1 represents the horizontal axis), In the XLWT module, the number of coordinates is the coordinates of the first cell in the upper-left corner starting at 0 (0,0), where one digit is the ordinate and the second is the horizontal axis.

ImportXlwt
XLS = XLWT. Workbook (encoding="Utf-8")#Create an XLS object to hold operations on Excel tablesSheet1 = Xls.add_sheet ('Score Table', cell_overwrite_ok=true)#Add a table to the XLS ' score table 'Sheet1.write (0, 0,'Student Name' # Add content to cells with coordinates 0,0 ' student name 'sheet1.write (0,1,'Achievements' # Add content to a cell with a coordinate of "score ' sheet1.write (0,2,'Reviews' # Add content ' comments 'xls.save (' Test table ') to a cell with coordinates of 0,2 # Save XLS as ' Test table ' 

You can generate Excel tables as shown in

Set font properties

ImportXlwtworkbook= XLWT. Workbook (encoding ='Utf-8') Worksheet= Workbook.add_sheet ('Test Table') Font= XLWT. Font ()#Create a Font object to hold the action on the fontFont.Name ='Microsoft Ya-Black'    #set the font to ' Microsoft Ya Black 'Font.Bold = True#Set Font boldFont.underline = True#set the font to underlineFont.Italic = True#Set Font skewstyle = XLWT. Xfstyle ()#Create a Style object that holds the styles for ExcelStyle.font = Font#Save font information to a style objectWorksheet.write (0, 0,'No font format') Worksheet.write (1, 0,'have font formatting', style)#add content ' font format ' to a cell with a coordinate of 1,0, with font propertiesWorkbook.save ('test table. xlsx')

Generate an Excel table as shown

Set background pattern

ImportXlwtworkbook=XLWT. Workbook () worksheet= Workbook.add_sheet ('Test Table') Pattern= XLWT. Pattern ()#Create a Pattern object to hold the style of the cell backgroundPattern.pattern = 0x01#set the background pattern style for a cell (0x01-0x12 18 styles)Pattern.pattern_fore_colour = XLWT. style.colour_map['Yellow']#set the background color of a cellstyle =XLWT. Xfstyle () Style.pattern= Pattern#Save background color information to the Styke objectWorksheet.write (0, 0,'with background color', style)#add content ' background ' to a cell with coordinates 0,0, with background colorWorkbook.save ('test table. xlsx')

Generate an Excel table as shown

Merge cells

ImportXlwtworkbook=XLWT. Workbook () worksheet= Workbook.add_sheet ('Test Table') worksheet.write_merge (0, 0, 0,1,'Merge two columns')#merge two columns with coordinates of 0, 0, and 0,1, and add the content ' merge two columns 'Worksheet.write_merge (1, 2, 0, 0,'merge two rows')#merge two rows with coordinates of 1, 0, and 2,0, and add the contents ' merge two rows 'Worksheet.write_merge (3, 5, 0, 2,'merge Three rows three columns')#merge three rows and three columns with ordinate 3-5 horizontal axis 0-2 and add content ' merge three rows three columns 'Workbook.save ('test table. xlsx')

Generate an Excel table as shown

Set the column width of the cell (XLWT does not have a special way to set the height of the row, you can adjust the height of the row by setting the size of the cell) (0 of the width of the column in the XLWT is the default font (10th Word) of 1/256)

Import= xlwt. Workbook (encoding='utf-8'= Book.add_sheet (' test table ' ) ) first_col=sheet.col (0)    #  get the column with a horizontal axis of 0 in the test table first_col.width=256*20    # the width of the column or columns is set to 256*20 units, which is 20 10th-character 0 book.save (' test table. xlsx')

Generate an Excel table as shown

Insert formula in cell then cell

ImportXlwtworkbook=XLWT. Workbook () worksheet= Workbook.add_sheet ('Test Table') worksheet.write (0, 0,1) worksheet.write (0,1, 2) worksheet.write (0,2, XLWT. Formula ('A1*B1'))#Add a formula to the cell cell with the coordinate of 0,2, the formula content is ' =A1*B1 ', the value shown is A1 and B1 two cell is worth the product: 2Worksheet.write (0, 3, XLWT. Formula ('SUM (A1,B1)'))#Add a formula to the cell cell with the coordinate of 0,3, the formula content is ' =sum (A1,B1) ', the value shown is A1 and the B1 two cell is worth and: 3Workbook.save ('test table. xlsx')

Generate an Excel table as shown

Another part of the Excel formula (OK, I admit I used in the work)

Number of statistics (COUNTIF (region, condition))

Example 1: Number of occurrences of statistics 1

Example 2: Count the number of occurrences of a value other than 1

Sum of products (sumproduct (array 1, array 2,) ）：

Example: Calculating the sum of the product of the number of corresponding positions of array 1 and Arrays 2

Conditional summation (SUMIF (conditional area, judging condition, operational area) (if the area of operation is omitted, then the condition area is the area of operation))

Example: A cell with a value of 1 in the range of A1-A6, the value of the corresponding cell within the B1-b6 range, and

Multi-Conditional summation (sumifs (op area, condition 1, Condition 1, Condition area 2, judging condition 2, ··· ））

Example: A value of 1 in the range of A1-A6, a cell with a value of 1 within the range of B1-B6, and a value of the corresponding cell within the C1-C6 range

、

Xlwt:python's Write Excel module