Python operations Excel uses 3 modules, respectively, xlrd,xlwt,xlutils;;; XLRD is used to read EXCEL,XLWT to write excel,xlutils to modify Excel.
XLRD Module
Import Xlrdbook=xlrd.open_workbook (' Stu.xls ') #打开excel文件
Print (Book.sheet_names ()) #获得所有sheet页的名称sheet =book.sheet_by_index (0) #按顺序打开sheet页 # sheet=book.sheet_by_name (' Sheet1 ') #按sheet页的名称打开print (Sheet.cell (n,m). Value) #取n行第m列的数据, you need to add. Value otherwise returns text: ' Value ' Print (sheet.ncols) # Gets the number of columns print (sheet.nrows) #获取行数print (sheet.row_values (0)) #获取某一行的数据. The parameter is the first line of print (sheet.col_values (0)) #取某一列的数据for I in Range (sheet.nrows): #获取每行的数据 print (sheet.row_values (i))
XLWT Module
Import Xlwtbook = XLWT. Workbook () #新建一个excelsheet = Book.add_sheet (' Sheet1 ') #添加一个sheet页, parameter is the name of the sheet page sheet.write (n,m, ' name ') #在第n行 , column m, writes the contents ' name ' Book.save (' Stu.xls ') #微软的office不能用xlsx结尾的, WPS Random # writes the array excelstus = [[' 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 () #新建一个excelsheet = Book.add_sheet (' Sheet1 ') #添加一个sheet页raw = 0# control line for stu in Stus:col = 0 #控制列, Write the for to guarantee that each line is written, and start with the 1th column of the next line for S in Stu:sheet.write (raw,col,s) col+=1raw+=1book.save (' Kkk.xls ')
Xlutils Module
From xlutils.copy import copyimport xlrdbook1=xlrd.open_workbook (' Stu.xls ') #利用xlrd模块打开excelbook2 =copy (BOOK1) # Copy a copy of the original Excelsheet=book2.get_sheet (0) #获得第几个sheet页sheet. Write (n,m, ' string ') #将第n行第m列内容替换成 ' string ' Book2.save (' Stu.xls ') #保存excel
Python operations Excel