Python works with Excel in several ways--xlrd, XLWT, OPENPYXL

• Openpyxl
• Xlrd
• Xlwt

The limitations of XLWT when working with Excel data – you cannot write more than 65535 rows, 256 columns of data (because it supports only Excel 2003 and previous versions, there is this limit for the number of rows and columns in these versions of Excel), which is not enough for the actual application. For this, after a search found a support 07/10/13 version of Excel OPENPYXL, although the function is very powerful, but the operation feel XLWT convenient. The following are the common operations of several modules, respectively.

Xlrd

XLRD is used to read and write data from Excel, but I usually use it for reading only, and there are some problems with the writing operation. It is easy to read with XLRD, and the process is like manually working with Excel, open the workbook (Workbook), select the worksheet (sheets), and then manipulate the cell (cell). For example, to open an Excel file named "Data.xlsx" in the current directory, select the first worksheet, and then read the entire contents of the first line and print it out. The Python code is as follows:

1 2 3 4 5 6 7 8 9 10 11

#打开excel文件 Data=xlrd.open_workbook (' data.xlsx ') #获取第一张工作表 (by index) Table=data.sheets () [ #data_list用来存放数据 #将table中第一行的数据读取并添加到data_list中 Data_list.extend (Table.row_values (0)) #打印出第一行的全部数据 In Data_list: Print Item

The above code reads a row with table.row_values (number), similar to reading a column with table.column_values (number), where number is the row index, and the rows and columns in XLRD are indexed starting at 0. So the leftmost cell A1 in Excel is the No. 0 row, the No. 0 column.
Xlrd reads a cell with Table.cell (Row,col), where row and col are the corresponding rows and columns of the cell.
The following is a brief summary of the use of XLRD

XLRD Usage Summary

• Open an Excel workbook

  1	Data=xlrd.open_workbook (filename)

• View the names of all sheet in a workbook

  1	Data.sheet_names ()

• Select a sheet (by index or table name)

  1 7 8

  #获取第一个工作表 table= Data.sheets () [0] # Get the first worksheet by index table=data.sheet_by_index (0) #通过表名称选择工作表 table=data.sheet_by_name (u ' haha ')

• Get the number of rows and columns of a table

  1 2	Nrows=table.nrows Ncols=table.ncols

• Get values for entire rows and columns

  1 2	Table.row_values (number) Table.column_values (number)

• Iterate through all rows of the table

  1 2	for rownum in xrange (table.nrows): print table.row_values ( rownum)

• Get the value of a cell

  1 2 3 4 5

  Cell_a1=table.row (0) [0].value #或者像下面这样 Cell_a1=table.cell (0,0). Value #或者像下面这样通过列索引 Cell_a1=table.col (0) [0].value

Write operations are seldom used by themselves, so they are not summed up.

Xlwt

If XLRD is not a mere reader (if the last two characters in Xlrd are considered reader, then the latter two characters are similar to writer), then XLWT is a purely writer because it can only write to Excel. XLWT and xlrd are not just names, but many functions and operation formats are exactly the same. Here is a brief summary of common operations.

XLWT Common operations

Create a new Excel file (can only be written by new)

1	DATA=XLWT. Workbook ()

Create a new worksheet

1	Table=data.add_sheet (' name ')

Write data to A1 cell

1	Table.write (0,0,u ' hehe ')

Note: If you repeat the operation on the same cell, overwrite Exception is raised and you want to cancel the feature, which you need to specify as overwritten when you add the worksheet, as follows

1	Table=data.add_sheet (' name ', cell_overwrite_ok=True)

Save File

1	Data.save (' Test.xls ')

This can only be saved with an XLS extension, and the xlsx format does not support

XLWT support a certain style, operation is as follows

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

#初始化样式 STYLE=XLWT. Xfstyle () #为样式创建字体 FONT=XLWT. Font () #指定字体名字 Font.name= #字体加粗 font.bold=true #将该font设定为style的字体 style.font=font sheet.write (0,1, ' Just for test ', style)

Openpyxl

The module supports the latest version of the Excel file format, the Excel file has a response to read and write operations, there is a dedicated reader and writer two classes, easy to work with Excel files. Nevertheless, I usually use the default workbook to do this. Common operations are summarized as follows:

OPENPYXL Common operations

Reading Excel files

1 2 3

Import Load_workbook Wb=load_workbook (filename)

Display the index range of a worksheet

1	Wb.get_named_ranges ()

Show names of all worksheets

1	Wb.get_sheet_names ()

Get the first sheet

1 2	Sheetnames = Wb.get_sheet_names () WS = Wb.get_sheet_by_name (sheetnames[0])

Get table name

1

Ws.title

Get the number of rows in a table

1	Ws.get_highest_row ()

Get the number of columns in a table

1	Ws.get_highest_column ()

Cell reads, which are similar to the XLRD read, are read by the index of rows and columns

1 2	#读取B1单元格中的内容 Ws.cell (0,1). Value

It is also supported to read the data through Excel coordinates, the code is as follows

1 2	#读取B1单元格中的内容 Ws.cell ("B1"). Value

To write a file, there is only one way to do it, through coordinates. For example, to write data to cell C1, use a similar Ws.cell ("C1"). Value=something such a way.
The general recommended way is to use the writer class in OPENPYXL to achieve. The code looks like this:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23 24 25 26 27 28

From Openpyxl.workbookImport Workbook #ExcelWriter, it encapsulates the write operation to Excel. From Openpyxl.writer.excelImport Excelwriter #get_column_letter函数将数字转换为相应的字母, such as 1-->a,2-->b From Openpyxl.cellImport Get_column_letter #新建一个workbook WB = Workbook () #新建一个excelWriter ew = excelwriter (workbook = WB) #设置文件输出路径与名称 dest_filename = r ' empty_book.xlsx ' #第一个sheet是ws WS = Wb.worksheets[0] # Set the name of WS ws.title = ws.cell ( "C1"). Value=u ' haha ' #最后保存文件 ew.save (filename=dest_filename)

When you write a file to a cell, you know its number of rows and columns, and note that the number of rows is counted from 1, and the column starts with the letter a , so the first row in the first column is A1, which in effect works with excel in coordinate mode. For example, to insert a numeric value 1.2 into the first column of the third row of the table, write in XLWT is Table.write (2,0,1.2), because the row and column indexes in XLWT start at 0, and if Openpyxl writes Ws.cell ("A3"). value=1.2. Generally for a larger number of columns, you need to get the corresponding characters through the Get_column_letter function, and then call the cell function to write.
Here is a part of the code I wrote earlier that can be used to demonstrate saving a multi-bit array to an Excel file. In order to embody multidimensional arrays, numpy is used here, and there is no use of excelwriter in order to simplify the process. The code is as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

#coding: Utf-8 From OPENPYXLImport Workbook From Openpyxl.cellImport Get_column_letter Import NumPyAs NP #生成一个对角阵 A=np.diag ([1,2,3,4,5]) #新建一个工作簿 wb=workbook () #使用当前激活的工作表 (default is the first table in Excel) Span class= "line" >ws=wb.active #下面是对a的遍历, note that cell rows and columns start at 1, and the index in a is starting from 0. for row in xrange (1, A.shape[0]+1): for col span class= "keyword" >in xrange (1,a.shape[1]+1): Ws.cell ( "% s%s '% (Col_letter,row)). Value=a[row-1,col-1] Wb.save ( ' test.xlsx ')

So much for the time being, basically enough.

Summarize

When reading Excel, choose OPENPYXL and xlrd are not very different, can meet the requirements
XLWT is more convenient when writing small amounts of data and saving in XLS format files
OPENPYXL is used when writing large amounts of data (exceeding the XLS format limit) or must be saved as an xlsx format file.

In addition to the above several modules, there are win32com and other modules, but no use, do not say.

"Turn from" Http://wenqiang-china.github.io/2016/05/13/python-opetating-excel/author:wenqiang

Python works with Excel in several ways--xlrd, XLWT, OPENPYXL