Use XLRD, XLWT to manipulate Excel tables in Python _python

Recently encountered a situation, is to generate and send server usage reports on a regular basis, according to the different dimension statistics, involving the operation of the Python Excel, Internet, most of the same, and not too much to meet the demand, but after some of the source of "research" (with this word I feel quite a sense of achievement), the basic solution to the day-to-day needs. Main records the use of the process of common problems and solutions.

Python operations Excel mainly uses the XLRD and XLWT these two libraries, namely XLRD is reads EXCEL,XLWT is writes the Excel library. You can download Https://pypi.python.org/pypi from here. The following is a separate record of Python read and write Excel.

Python Read excel--xlrd

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

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

The basic operations of Python for Excel are as follows:

#-*-Coding:utf-8-*-
import xlrd
import XLWT from
datetime import date,datetime

def 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 according to 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

  # Gets the values of the entire row and column (array)
  rows = Sheet2.row_values (3) # get the fourth line of content
  cols = Sheet2.col_values (2) # Get the third column content
  print rows
  print cols

  # get cell content
  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 ()

The results of the operation are as follows:

So the problem is, the above results in the red box in the field is clearly the date of birth, can show the exact floating-point number. OK, to solve the first problem:

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

Python reads the contents of a cell in Excel with 5 types, that is, the CType in the example above:

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

That is, the ctype=3 of date, which needs to be processed in the date format using Xlrd's Xldate_as_tuple, before the ctype=3 of the table can begin. 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/22
33656.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, 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 the CType equals 3, if equal to 3, then in 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 problem comes again, above Sheet2.cell (2,4). CType returns a value of 0, indicating that the value of this cell is null, obviously the merged cell content "good friend", this is what I think this package function is not perfect place, If it is a merged cell, the contents of the cell should be merged, but it is only the value of the first cell that is merged, and the rest 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 of


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 no technique, you can only get the row and column index of the first cell in the merged cell to read the value, and the wrong read is the null value.

That is, the merged row cell reads the first index of the row, and the merged column cells read the first index of the column, as described above, reading row merging cells "good friend" and reading column merge cells "Scratch" can only be as follows:

>>> print Sheet2.col_values (4) [1]
good friend
>>> print sheet2.row_values (7) [2]
no

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

The question is again, the merged cell may appear empty, but the normal cell of the table itself may also be null, how do I get the cell's so-called "first row or column index"?

This is the first to know which cells are merged!

3. Get merged cells

You need to set the Formatting_info parameter to True when you read the file, false by default, so the merged cell array above gets 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: row 1th to 2nd (excluding 3) merges, (7, 8, 2, 5) means: 2nd to 4th Column merge.

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

>>> print Sheet2.cell_value (1,4)  # (1, 3, 4, 5)
good friend
>>> print sheet2.cell_value (3,4)  # (3, 6, 4, 5)
classmate
>>> print Sheet2.cell_value (7,2)  # (7, 8, 2, 5)
No

Have you found the rules yet? Yes, get the merge_cells return row and Col low index! So it can be 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.cel L_value (index[0],index[1])
  
no
good friends and
classmates
>>>

Python writes EXCEL--XLWT

The difficulty in writing Excel may not be to construct a workbook itself, but to populate the data, but that is not in scope. There are also difficult problems in writing Excel, such as writing merged cells is cumbersome, and writing has different styles. This depends on the source code can be studied through.

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

It's almost as complex as it seems, and it looks "very formal" and it's entirely a myth.

The code is as follows:

' Set cell style ' Def set_style (name,height,bold=false): style = XLWT. Xfstyle () # initializes the style font = XLWT.

  Font () # creates fonts for styles Font.Name = name # ' Times New Roman ' Font.Bold = Bold Font.color_index = 4 Font.height = height # borders= XLWT. Borders () # borders.left= 6 # borders.right= 6 # borders.top= 6 # borders.bottom= 6 style.font = font # style . Borders = borders return style #写excel def 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 ' status subtotal ', U ' total '] column0 = [u ' ticket ', U ' ferry ', U ' train ticket ', U ' car ticket ', U ' other '] status = [u ' pre Order ', U ' out tickets ', U ' refund ', U ' business Subtotal '] #生成第一行 for I in Range (0,len (row0)): Sheet1.write (0,i,row0[i],set_style (' New Roman '), 220,true) #生成第一列和最后一列 (merged 4 lines) I, j = 1, 0 while I < 4*len (column0) and J < Len (column0): Sheet1.write_merg E (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 (status)): Sheet1.write (J+i+1,1,sta TUS[J]) i + = 4 f.save (' demo1.xlsx ') #保存文件 if __name__ = = ' __main__ ': #generate_workbook () #read_excel () writ

 E_excel ()

The Write_merge method needs a little explanation:

Write_merge (x, x + M, y, W + N, string, sytle)
X is the row, Y represents the column, m represents the number of rows across, N is the number of spans, string represents the contents of the cell to write, and style represents the cell style. Of these, x,y,w,h are calculated 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, merge the 1th, 2 columns in line 22, the merged cell contents are total, and style is set.

If you need to create multiple sheet, you can just f.add_sheet.

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

The code is really easy to do:

'
  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 ' little fat ', U ' Xiao Ming ', u ' great god ', U ' great fairy ', U ' min ', U ' nameless ']

  #生成第一行
  For I in range (0,len (row0)):
    sheet2.write (0,i,row0[i],set_style (' New Roman ', 220,true))

  #生成第一列
  For I in range (0,len (column0)):
    sheet2.write (I+1,0,column0[i],set_style (' New Roman ',

  ) Sheet2.write (1,2, ' 1991/11/11 ')
  sheet2.write_merge (7,7,2,4,u ' temporary absence ') #合并列单元格
  sheet2.write_merge (1,2,4,4, U ' good friend ' #合并行单元格
  
  f.save (' demo1.xlsx ') #保存文件