Python Learning Notes (13)-python Read and write modifications to Excel

Excel reading and writing problems are encountered in daily work. We can use the XLWT module to write data to an Excel table, use the XLRD module to read data from Excel, and use the Xlutils module with the XLRD module to modify the Excel data. Here's how to implement read and write modifications to Excel using Python.
1. Write operations to Excel:
Import XLWT
Book = XLWT. Workbook () #新建一个excel
Sheet = book.add_sheet (' Sheet1 ') #添加一个sheet页
Sheet.write (0,0, ' name ') #Excel第一行第一列写入姓名
Sheet.write (0,1, ' sex ') #Excel第一行第二列写入性别
Sheet.write (0,2, ' age ') #Excel第一行第三列写入年龄
Book.save (' Stu.xls ') #微软的office不能用xlsx结尾的, WPS Casual

Example: Read data from a two-dimensional list and write to Excel.
Stus = [
[' Name ', ' age ', ' gender ', ' score '],
[' Mary ', 20, ' female ', 89.9],
[' Mary ', 20, ' female ', 89.9],
[' Mary ', 20, ' female ', 89.9],
[' Mary ', 20, ' female ', 89.9]]
Book = XLWT. Workbook () #新建一个excel
Sheet = book.add_sheet (' Sheet1 ') #添加一个sheet页
Raw = 0# control line
For Stu in Stus:
Col = 0 #控制列
For S in Stu:
Sheet.write (Raw,col,s)
Col+=1
Raw+=1
Book.save (' Kkk.xls ')

2. Read operations on Excel
Import xlrd
Book = Xlrd.open_workbook (' Stu.xls ') #打开一个excel
Sheet = book.sheet_by_index (0) #根据顺序获取sheet
Sheet2 = Book.sheet_by_name (' Sheet1 ') #根据sheet页名字获取sheet
Print (Sheet.cell (0,0). Value) #指定行和列获取数据
Print (Sheet.ncols) #获取excel里面有多少列
Print (sheet.nrows) #获取excel里面有多少行
Sheet.row_values (1) #取第几行的数据
Print (Sheet.col_values (1)) #取第几列的数据
For I in Range (sheet.nrows): # 0 1 2 3 4 5
Print (Sheet.row_values (i)) #取第几行的数据

3. Modify operations on Excel
From xlutils.copy import Copy #从xlutils模块导入copy
Import xlrd
Book1 = Xlrd.open_workbook (' Stu.xls ') #得到Excel文件的book对象, instantiating the object
Book2 = Copy (Book1) #拷贝一份原来的excel
Sheet = book2.get_sheet (0) #获取第几个sheet页
Sheet.write (1,3,0) #对拷贝的excel第2行, the 4th column data is 0
Sheet.write (1, 0, ' Little black ') #对拷贝的excel第2行, 1th column data for small black
Book2.save (' Stu.xls ') #保存修改后excel

Python Learning Notes (13)-python Read and write modifications to Excel