Python read Excel module: XLRD

Mainly from: [Python use xlrd, XLWT operation Excel table detailed]

To facilitate reading, I split the original two modules into two posts:

[Python read Excel module: XLRD]

[Python writes Excel module: XLWT]

XLRD Basic Operation

This process has several troublesome problems, such as reading the date and reading the merged cell contents.

Read an Excel file with 2 sheet and read the contents of the Sheet2 as follows:

Read in using Python

#!/usr/bin/env python#-*-Coding:utf-8-*-#-read_excel.py#ImportXlrdImportXlwt fromDatetimeImportDate,datetimedefRead_excel ():# Open FileWorkbook=Xlrd.open_workbook (R '/tmp/demo.xlsx ')# get all sheet  PrintWorkbook.sheet_names ()# [U ' Sheet1 ', U ' Sheet2 ']Sheet2_name=Workbook.sheet_names () [1]# Get sheet content based on sheet index or nameSheet2=Workbook.sheet_by_index (1)# Sheet Index starting from 0Sheet2=Workbook.sheet_by_name (' Sheet2 ')# sheet name, number of rows, number of columns  PrintSheet2.name,sheet2.nrows,sheet2.ncols# Get values for entire rows and columns (array)Rows=Sheet2.row_values (3)# Get the fourth line of contentCols=Sheet2.col_values (2)# Get third column content  PrintRowsPrintCols# Get cell contents  PrintSheet2.cell (1,0). Value.encode (' Utf-8 ')PrintSheet2.cell_value (1,0). Encode (' Utf-8 ')PrintSheet2.row (1)[0].value.encode (' Utf-8 ')# Gets the data type of the cell contents  PrintSheet2.cell (1,0). CTypeif __name__ == ' __main__ ': Read_excel ()

The results of the operation are as follows:

As you can see, the contents of the red box should be birthdays, but realistic floating-point numbers. Next solve the problem of the date.

XLRD Reading Cell Date Contents

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

0 empty1 string2 number3 date4 boolean5 error

Therefore, for the date processing, you can first determine its CType, if it is date, you need to use the xlrd xldate_as_tuple to handle.

>>> sheet2.cell(2,2#1990/2/2233656.0>>>= xlrd.xldate_as_tuple(sheet2.cell_value(2,2),workbook.datemode)>>> date_value(1992222000)

if==3):    = xlrd.xldate_as_tuple(sheet.cell_value(rows,3),book.datemode)    = date(*date_value[:3]).strftime(‘%Y/%m/%d‘)

XLRD handling the value problem of merge cells

For data that merges cells, the result of XLRD is that only the first cell has a value and the other is empty.

Getting a cell is a good idea: get the merged cells and get the contents of the merged cells.

Get merged cells

The Formatting_info parameter must be set to True when the file is read and false by default.

>>>= xlrd.open_workbook(r‘/tmp/demo.xlsx‘,formatting_info=True)>>>= workbook.sheet_by_name(‘sheet2‘)>>> sheet2.merged_cells[(7825), (1345), (3645)]　

The meaning of these four parameters returned by Merged_cells is:(row,row_range,col,col_range). The value range and the list toggle are the same, including start, which does not contain stop.

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

>>>print sheet2.cell_value(1,4)  #(1, 3, 4, 5)好朋友>>>print sheet2.cell_value(3,4)  #(3, 6, 4, 5)同学>>>print sheet2.cell_value(7,2)  #(7, 8, 2, 5)暂无　

As you can see, get the index of row and col lows returned by Merge_cells.

>>>= []>>>forin sheet2.merged_cells:  merge.append([rlow,clow])  >>> merge[[72], [14], [34]]>>>forin merge:  print sheet2.cell_value(index[0],index[1])  暂无好朋友同学

Python read Excel module: XLRD