Using Python to read and write operations with the same Excel (Xlrd,xlwt,xlutils)

Recently began to learn Python, want to do simple automated test, need to read and write Excel, and then found XLRD to read the Excel file, using XLWT to generate Excel files (you can control the format of cells in Excel), it is important to note that Reading Excel with XLRD cannot operate on it: the Xlrd.open_workbook () method returns XLRD. Book type, which is read-only and cannot be manipulated. and XLWT. Workbook () returns the XLWT. The workbook type of Save (filepath) method can save an Excel file.

So it's easy to handle reading and generating Excel files, but making changes to an existing Excel file can be tricky. However, there is also a xlutils (dependent on xlrd and XLWT) that provides the ability to copy the contents of an Excel file and modify the file. Its reality is only in xlrd. There was a pipeline between book and Xlwt.workbook.

The following is the sample code:

Import XLWT

Import xlrd

From xlutils.copy Import copy

#使用xlrd读取指定excel工作中的指定表格的值并返回

def excel_read (doc,table,x,y):

data = Xlrd.open_workbook (DOC)

Table = data.sheet_by_name (table)

return Table.cell (x, y). Value

#使用xlwt创建指定excel工作中的指定表格的值并保存

def excel_create (Sheet,value):

data = XLWT. Workbook ()

Table = Data.add_sheet (sheet)

Table.write (1,4,value)

Data.save (' Demo.xls ')

#三个结合操作同一个excel

RB = Xlrd.open_workbook (' Demo.xls ')

#管道作用

WB = Copy (RB)

#通过get_sheet () Gets the sheet has the write () method

WS = Wb.get_sheet (1) #1代表是写到第几个工作表里, starting from 0 is the first one.

Ws.write (1, 6, ' changed! ')

Wb.save (' Demo.xls ')

Use Python to read and write operations to the same Excel (Xlrd,xlwt,xlutils)