Python Excel operations

Recently encountered a scenario, is to generate and send the server usage report on a regular basis, according to the statistics of different dimensions, involving the operation of Python to Excel, surfing the Internet, mostly similar, and not enough to meet the needs, but after some of the source of the "research" (with this word I feel a sense of accomplishment), the basic solution to the daily needs. The main records of the use of the process of common problems and solutions.

The Python operation Excel mainly uses the XLRD and the XLWT two libraries, namely XLRD is reads the EXCEL,XLWT is writes the Excel the library. You can download Https://pypi.python.org/pypi from here. The following records Python read and write Excel respectively.

Python Read excel--xlrd

This process has several troublesome problems, such as reading the date and reading the merged cell contents. Let's take a look at the basic operation:

First read an Excel file, there are two sheet, test with a second Sheet,sheet2 content as follows:

Python's basic operations for Excel are as follows:

#-*-Coding:utf-8-*-import xlrd
Import xlwtfrom datetime import Date,datetimedef read_excel ():    # Open File    workbook = Xlrd.open_workbook (R ' f:\ Demo.xlsx ')    # get all sheet    print workbook.sheet_names () # [U ' Sheet1 ', U ' Sheet2 ']    sheet2_name = Workbook.sheet_names () [1]    # get sheet content based on sheet index or name    Sheet2 = Workbook.sheet_by_index (1) # Sheet index starting    from 0 Sheet2 = Workbook.sheet_by_name (' Sheet2 ')    # sheet name, number of rows, number of columns    print Sheet2.name,sheet2.nrows,sheet2.ncols    # Get values for entire rows and columns (array) Rows    = Sheet2.row_values (3) # get the fourth line of content    cols = sheet2.col_values (2) # Get third column content    print Rows    print cols    # Gets the contents of the cell    print Sheet2.cell (1,0). Value.encode (' utf-8 ')    print Sheet2.cell_value (1,0). Encode (' Utf-8 ')    print sheet2.row (1) [0].value.encode (' Utf-8 ')        # Gets the data type    of the cell contents Sheet2.cell (1,0). CTypeIf __name__ = = ' __main__ ':    read_excel ()

The results of the operation are as follows:

So the problem is that the field in the red box above the running result is clearly the date of birth and the actual floating-point number that can be displayed. OK, to solve the first problem:

1. How Python reads the contents of cells in Excel as a date

Python reads the contents of a cell in Excel by returning 5 types, the CType in the example above:

Ctype:0 empty,1 String, 2 number, 3 date, 4 Boolean, 5 error

That is, the date of the ctype=3, you need to use XLRD xldate_as_tuple to process the date format, first determine the table ctype=3 when xldate to begin operation. Now look at the command line:

>>> Sheet2.cell (2,2). CType  #1990/2/223>>> Sheet2.cell (2,1). CType  #24 2>>> Sheet2.cell (2,0). CType  #小胖1>>> Sheet2.cell (2,4). CType  # Null value (This is the reason for merging cells)0>>> Sheet2.cell (2,2). Value  #1990/2/2233656.0>>> Xlrd.xldate_as_tuple (Sheet2.cell_value (2,2), Workbook.datemode)(1992, 2, 0, 0, 0)>> > date_value = Xlrd.xldate_as_tuple (Sheet2.cell_value (2,2), Workbook.datemode)>>> Date_value (1992 , 2, 22, 0, 0, 0)
Date (*date_value[:3])
Datetime.date (1992, 2, 22)
date (*date_value[:3]). Strftime ('%y/%m/%d ')

That is, you can do a simple processing, to determine whether CType equals 3, if equal to 3, then the time format processing:

if (Sheet.cell (row,col). CType = = 3):    Date_value = Xlrd.xldate_as_tuple (Sheet.cell_value (rows,3), Book.datemode)    date_tmp = Date (*date_value[:3]). Strftime ('%y/%m/%d ')

Then the question came again, above Sheet2.cell (2,4). CType The value returned is 0, indicating that the value of this cell is a null value, which is clearly the merged cell content "good friend", this is where I think this package is not perfect, If it is a merged cell then the contents of the cell should be merged, but it is only the value of the first cell that is merged, and the other is empty.

>>> sheet2.col_values (4) [u ' \U5173\U7CFB ', U ' \U597D\U670B\U53CB ', ', U ' \u540c\u5b66 ', ', ', U ' \u4e00\u4e2a \u4eba ', ']>>> for I in Range (sheet2.nrows):    print Sheet2.col_values (4) [i] relationship good friend classmate a person >>> Sheet2.row_values (7) [u ' \u65e0\u540d ', 20.0, U ' \u6682\u65e0 ', ', ']>>> for I in Range (Sheet2.ncols):    

2. Read the contents of merged cells

This is really not a trick, can only get the first cell of the merged cell row index, to read the value, read the wrong is the null value.

That is, the merged row cell reads the first index of the row, and the merged column cell reads the first index of the column, as mentioned above, reads the row merge cell "good friends" and reads the column merge cell "temporary none" only as follows:

>>> print Sheet2.col_values (4) [1] Good friends >>> print sheet2.row_values (7) [2] None

>>> Sheet2.merged_cells # Clearly there are merged cells, why is this empty
[]

Again, the merged cell may appear empty, but the normal cell of the table itself may also be null, how do you get the so-called "first row or column index" of a cell?

This is the first to know which cells are merged!

3. Get merged cells

To read the file, you need to set the Formatting_info parameter to True, the default is False, so the above gets the merged cell array is empty,

>>> Workbook = Xlrd.open_workbook (R ' F:\demo.xlsx ',formatting_info=True) >>> Sheet2 = Workbook.sheet_by_name (' Sheet2 ') >>> sheet2.merged_cells[(7, 8, 2, 5), (1, 3, 4, 5), (3, 6, 4, 5)]

The meaning of these four parameters returned by Merged_cells is: (Row,row_range,col,col_range), where [Row,row_range] includes row, not including Row_range,col, i.e. (1, 3, 4, 5) The meaning is: the 1th to 2nd line (excluding 3) merges, (7, 8, 2, 5) meaning is: 2nd to 4th Column merges.

With this, you can get the contents of the merged three cells separately:

>>> print Sheet2.cell_value (1,4)   # (1, 3, 4, 5) Good friends >>> print Sheet2.cell_value (3,4)   # (3, 6, 4, 5 ) Classmates >>> print sheet2.cell_value (7,2)   # (7, 8, 2, 5) No

Did you find the pattern? Yes, get the index of row and col lows returned by Merge_cells! So you can do this once and for all:

>>> merge = []>>> for (rlow,rhigh,clow,chigh) in Sheet2.merged_cells:    merge.append ([Rlow,clow])    >>> merge[[7, 2], [1, 4], [3, 4]]>>> for index in merge:    print Sheet2.cell_value (index[0],inde X[1])    

Python Write EXCEL--XLWT

The difficulty of writing Excel may not be to construct a workbook itself, but to populate the data, but this is not in scope. There are also tricky problems in writing Excel, such as writing merged cells that are cumbersome and have different styles for writing. These need to see the source code to study the penetration.

I "conceived" the following Sheet1, the thing to be implemented with XLWT:

Basically it looks complicated, and it looks "very formal" and it's all a myth.

The code is as follows:

' Set cell style ' ' Def set_style (name,height,bold=false): style = XLWT. Xfstyle () # Initialize style font = XLWT. Font () # Create fonts for styles Font.Name = name # ' Times New Roman ' Font.Bold = Bold Font.color_index = 4 Font.height = Hei Ght # borders= XLWT. Borders () # borders.left= 6 # borders.right= 6 # borders.top= 6 # borders.bottom= 6 style.font = font # Style.borders = borders return style# write Exceldef write_excel (): F = xlwt. Workbook () #创建工作簿 ' Create the first Sheet:sheet1 ' Sheet1 = f.add_sheet (U ' Sheet1 ', cell_overwrite_ok=true) #创建 Sheet row0 = [u ' business ', U ' state ', U ' Beijing ', U ' Shanghai ', U ' guangzhou ', U ' shenzhen ', U ' state subtotal ', U ' total '] column0 = [u ' ticket ', U ' ticket ', U ' train ticket ', U ' car ticket ', U ' other '] stat US = [u ' booking ', U ' ticket ', U ' refund ', U ' business Subtotal '] #生成第一行 for I in Range (0,len (row0)): Sheet1.write (0,i,row0[i],set_style (' Time        s New Roman ', 220,true)) #生成第一列和最后一列 (merge 4 lines) I, j = 1, 0 while I < 4*len (column0) and J < Len (column0): Sheet1.write_merge (I,i+3,0,0,column0[j],set_style (' Arial ', 220,true)) #第一列 Sheet1.write_merge (i,i+3,7,7) #最后一列 "Total" i + = 4 J + 1 Sheet1.write_merge (21,21,0,1,u ' total '), Set_style (' Times New Roman ', 220,true)) #生成第二列 i = 0 while I < 4*len (column0): for J in Range (0,len (stat    US): Sheet1.write (J+i+1,1,status[j]) i + = 4 f.save (' demo1.xlsx ') #保存文件if __name__ = = ' __main__ ': #generate_workbook () #read_excel () Write_excel ()

The Write_merge method that needs a little explanation is:

Write_merge (x, x + M, y, W + N, string, sytle)
X is the row, Y is the column, m indicates the number of spans, n is the number of spans, and string represents the cell content to write, and the style represents the cell style. Among them, x,y,w,h, are calculated starting at 0.

This is not the same as reading merged cells in XLRD.

As above: Sheet1.write_merge (21,21,0,1,u ' Total ', Set_style (' Times New Roman ', 220,true))

That is, the 1th, 2 columns are merged in 22 rows, the merged cell contents are "totals" and the style is set.

If you need to create more than one sheet, you can just f.add_sheet.

As in the above Write_excel function F.save (' demo1.xlsx ') before the sentence to create a Sheet2, the effect is as follows:

The code is really easy, too:

    "'    Create a second sheet:        sheet2    '    Sheet2 = f.add_sheet (U ' Sheet2 ', cell_overwrite_ok=true) #创建sheet2    Row0 = [u ' name ', U ' age ', U ' Birth date ', U ' hobby ', U ' relationship ']    column0 = [u ' Little Jay ', U ' chubby ', U ' Xiao Ming ', u ' big god ', U ' big fairy ', U ' xiaomin ', U ' nameless ']    #生成第一行    For I in range (0,len (row0)):        sheet2.write (0,i,row0[i],set_style (' Times New Roman ', 220,true))    #生成第一列    For I in range (0,len (column0)):        sheet2.write (I+1,0,column0[i],set_style (' Times New Roman ', $))    Sheet2.write (1991/11/11 ')    sheet2.write_merge (7,7,2,4,u ' no ') #合并列单元格    sheet2.write_merge (1,2,4,4,u ' Good friends ') #合并行单元格        f.save (' demo1.xlsx ') #保存文件

You can also add hyperlinks, such as:

# Add Hyperlink    n= "HYPERLINK"    sheet2.write_merge (9,9,2,8, XLWT. Formula (n + ' ("Http://www.cnblogs.com/zhoujie"; Jzhou\ ' s Blog "), Set_style (' Arial ', 300,true))    Sheet2.write_merge (10,10, 2, 8, XLWT. Formula (n + ' ("Mailto:[email protected]"; " Contact me "), Set_style (' Arial ', 300,true))

Full code:

View Code

From:http://www.cnblogs.com/zhoujie/p/python18.html

Reference: http://www.cnblogs.com/snake-hand/p/3153158.html

Python Excel operations