Use Xlrd, XLWT in Python to manipulate Excel

Python's basic operations for Excel are as follows:

#-*-Coding:utf-8-*-import xlrdimport xlwtfrom datetime import date,datetime def read_excel ():  # Open File  workbook = Xlrd.open_workbook (R ' F:\demo.xlsx ')  # gets 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 the third column of 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  print Sheet2.cell (1,0). CType If __name__ = = ' __main__ ':  read_excel ()

　　

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 : 0empty,1 string, 2 number, 3 date, 4 boolean, 5error

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/22>>> Sheet2.cell (2,1). CType #24 >>> Sheet2.cell (2,0). CType #小胖 >>> Sheet2.cell (2,4). CType #空值 (This is the reason for merging cells) >>> 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,) >>> date (*date_value[:3]). Strftime ('%y/%m/%d ') ) ' 1992/02/22 '

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):  Print sheet2.row_values (7) [i] nameless 20.0 no  >>>

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] No >>> sheet2.merged _cells # Clearly there are merged cells, why this is 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])   No good friends >>> classmates

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 = height # b Orders= XLWT. Borders () # borders.left= 6 # borders.right= 6 # borders.top= 6 # borders.bottom= 6 style.font = font # Style.borde rs = borders return style #写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 '] status = [u ' reservation ', U ' Ticketing ', U ' refund ', U ' business Subtotal '] #生成第一行 for I in Range (0,len (row0)): Sheet1.write (0,i,row0[i],set_style (' Times New Roman ', 220,true )) #生成第一列和最后一列 (merge 4 rows) 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 < Len (Column0): for J in Range (0,len (status)): 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 ') #保存文件

　　

Use Xlrd, XLWT in Python to manipulate Excel