"Python" Excel read-write operations Xlrd & XLWT

Xlrd

Xlrd

XLRD module for reading the contents of an Excel file

Basic usage:

Workbook = Xlrd.open_workbook (' file path ') workbook.sheet_names ()    # returns the list of all sheet workbook.sheet_by_index (...)    # Index to obtain a sheet object, index starting from 0 to calculate the Workbook.sheet_by_name (...)    # get the corresponding sheet object according to the sheet name

After you get the sheet object, you can use some of its methods and variables to get the data:

Sheet.name Sheet's name

The number of rows sheet.nrows sheet

Number of columns Sheet.ncols sheet

Sheet.get_rows () returns an iterator that iterates through all the rows, giving a list of values for each row

Sheet.row_values (index) Returns a list of values for a row

Sheet.row (index) Returns a row object, which can be obtained by row[index] to get the cells cell object in this row

Sheet.col_values (index) Returns a list of values for a column

Sheet.cell (row,col) Gets a Cell object (both row and Col are counted from 0)

The Cell object mentioned above is an abstraction of a cell, and the Cell object has a value variable to get its values. *value are stored in Unicode format, if the content is Chinese, remember to encode

There are several ways to get the value of a particular cell object, such as

Sheet.cell (x, y). Value

Sheet.cell_value (x, y)

Sheet.row (x) [Y].value

In addition to the value variable, the cell has some other variables and methods:

. CType returns the code for the Cell data type (0 for empty, 1 for string,2 means number,3 for date,4 to indicate boolean,5 for error). When CType = = 3 o'clock, although the date, but then Python is handled by float, it is necessary to use the Xldate_as_tuple method to convert it to a date format, the use of this method is Xlrd.xldate_as_tuple (Xldate, Datemode), xldate indicates that a CType is a value of 3, and Datemode is a property that belongs to workbook.

* About reading merged cells

By default, merged cells can read to values only in the leftmost upper-left cell, and others are empty. To resolve this issue, add the parameter Formatting_info = True ( This only supports the excel97-03 xls file ) when Open_workbook

In this way, Sheet.merged_cells returns information for all merged cells in the current table, formatted like [(7,8,2,5), (1,3,4,5) ...] Such a list. Each of these items is a cell, for example (7,8,2,5) means that the seventh row of the sheet in the 第2-4 row merge, and the sequence of the Shard operation, is the head is not counted, so 7,8 refers to the combined seventh row only (this 7 is not index but index+ 1), 2,5 indicates the second column to the fourth column, excluding the fifth column. This "no-tail" approach is different from the Merge cell processing in the XLWT module.

That way, if you want to know the value information for a merged cell, just focus on the first and third items of each tuple.

　　

Xlwt

XLWT is used to write to Excel, and the basic creation method is similar to XLRD:

wk == Wk.add_sheet ('sheetname') st.write (x, y,...,    style)#  meaning to be content ... Write to the index (x, y) in the cell, the style can be customized, the details below st.write (x,x+m,y,y+n,..., style)    # can be directly written to a merged cell, X is similar to the index,y of the row that starts with row index,x+m is the end. Note: This is included in the X+m row, and the Xlrd read Merge cell setting is not the same  Wk.save (' path ')    # Save File

* About style You can define a single def_style function to unify processing

Like what:

defDef_style (): Style=XLWT. Xfstyle ()

######### #这部分设置字体 ######### Font=XLWT. Font () Font.Name='Times New Roman' #or change the parameters that come in from outside, so that a function can define all the styleFont.Bold ='True'Font.height='...'font.size='...'Font.colour_index ('...') Style.font=Font
####### #这部分设置居中格式 ####### Alignment=XLWT. Alignment () Alignment.horz= XLWT. Alignment.horz_center#Center HorizontallyAlignment.vert = XLWT. Alignment.vert_center#Center VerticallyStyle.alignment =Alignment
######## #还可以添加几个设置颜色, part of the border ##########
    returnstyle#######################################    #The core meaning is to use this function to set the properties of some style    #such as font, center format, etc.    #eventually return to a style    #######################################

#这样在写入的时候就可以通过def_style () to return a style object to set the style
Xlwt.write (0,0, ' Test ', Def_style ())

* Cell size does not automatically adjust depending on the size and number of content

Sheet.row (x). Height = ...

Sheet.col (x). Width = ...

To adjust the cell size

* To set the cell's background color, border, etc., you can set other properties of the style, such as

Background color to set Style.pattern, such as:

PTN = XLWT. Pattern ()

Ptn.pattern = XLWT. Pattern.solid_pattern

Ptn.pattern_fore_colour = color code

Style.pattern = PTN

border = XLWT. Borders ()

Border.left = XLWT. Borders.thick

Border.top/right/bottom, wait.

"Python" Excel read-write operations Xlrd & XLWT