Python: Manipulating Excel

you need pip to install the Xlrd,xlwt,xlutils module, which is to read Excel, write to Excel, modify the Excel

xlrd module:
Import xlrd
Book=xlrd.open_workbook (R ' students.xlsx ') #打开一个Excel文件; The file in parentheses does not specify an absolute path, meaning the Excel file in the current directory
Print (Book.sheet_names ()) #获取所有sheet的名字
Sheet=book.sheet_by_index (0) #根据sheet页的第几个位置去取sheet
Sheet=book.sheet_by_name (' Sheet2 ') #根据sheet页的名字获取sheet页
Print (sheet.nrows) #获取该指定sheet页的所有行数, get a number
Print (Sheet.ncols) #获取该指定sheet页的所有列数, get a number
Print (sheet.row_values (0)) #根据行号获取整行的数据, get a list
Print (sheet.col_values (0)) #根据列号获取整列的数据, get a list
Print (Sheet.cell (2,1). Value) #获取第3行第2列单元格里的内容, plus value gets the string type

EG1: Read the table, and put the read data in the list, the table of elements is a dictionary, the dictionary key is Id,name,sex
Import xlrd
Book=xlrd.open_workbook (R ' students.xlsx ')
Sheet=book.sheet_by_index (0) #打开第一个sheet表
N=sheet.nrows #求该sheet表中所有的行数
All_list=[] #定义一个空列表, put out the data
For I in Range (1,n): #按表格里所有的行数循环
DIC = {}
Lis=sheet.row_values (i)
dic[' id ']=lis[0]
dic[' name ']=lis[1]
dic[' sex ']=lis[2]
All_list.append (DIC)
Print (all_list)

XLWT Module
Import XLWT
BOOK=XLWT. Workbook () #新建一个excel对象
Sheet=book.add_sheet (' student ') #增加一个sheet页, named student
Sheet.write (0,0, ' number ') #给第一行第一列的单元格写上 "number"
Book.save (' Stu.xls ') #保存, the file name is saved as Stu.xls. Save in current directory (or write absolute path)
#PS: When you read Excel, the XLS xlsx is readable, but when you write Excel, the file name must be xls.

EG2: Give Lis and title, write a table
Import XLWT
lis=[{' name ': ' nickname ', ' sex ': ' female ', ' ID ': 1.0}, {' name ': ' Xiao Li ', ' Gender ': ' Medium ', ' ID ': 2.0}, {' name ': ' Xiao Wang ', ' gender ': ' Male ', ' ID ': 3.0},
{' name ': ' Headmaster ', ' gender ': ' Female ', ' ID ': 4.0}]
title=[' number ', ' name ', ' gender '
BOOK=XLWT. Workbook ()
Sheet=book.add_sheet (' Test ')
For I in range (len title): #循环列表的长度
Sheet.write (0,i,title[i]) #写表头
For I in range (len (LIS)):
Sheet.write (i+1,0,lis[i][' id ')
Sheet.write (i+1,1,lis[i][' name '])
Sheet.write (i+1,2,lis[i][' sex ') #写每一个元素
Book.save (' Test.xls ')

xlutils Module
Import Xlrd,xlutils
From xlutils.copy import Copy #引用xlutils模块里的copy
Book=xlrd.open_workbook (R ' Stu.xls ') #打开原来的excel
New_book=copy (book) #通过xlutils里面的copy复制一个excel对象
Sheet=new_book.get_sheet (0) #获取第一个sheet页; The copied new object has no Sheet_by_index () method, only the Get_sheet () method
Sheet.write (0,0, ' id ') #把第一行第一列的单元格修改掉
New_book.save (' Stu_1.xls ') #保存为一个新文件

Python: Manipulating Excel