Python Learning notes-manipulating Excel

Python operations Excel: install with Pip

One,xlwt: Write Excel

ImportXlwtbook= XLWT. Workbook ()#Create a new ExcelSheet = Book.add_sheet ('Sheet1')#Add Sheet pageSheet.write (0,0,'name')#row, column, write contentSheet.write (0,1,'Age') sheet.write (0,2,'Sex') Book.save ('Stu.xls')#The end must be used. xls

  ImportXLWT title= ['name','Age','Sex','score'] Stus= [['Mary', 20,'female', 89.9],['Mary', 20,'female', 89.9],['Mary', 20,'female', 89.9],['Mary', 20,'female', 89.9]]        #Create a new Excel objectWBK =XLWT. Workbook ()#Add a sheet page called a curriculumSheet = Wbk.add_sheet ('Stu')         forIinchRange (len (title)):#write to table headerSheet.write (0,i,title[i])#write each row, the first value is the row, the second value is the column, and the third is the value writtenrow = 1#Line         forIinchStus:col= 0#column             forJinchi:sheet.write (row,col,j)#Loop writes each row of dataCol+=1Row+=1#Save the data to the ' Test.xls ' fileWbk.save ('Szz.xls')#save Excel must use the suffix name of. xls, not can be. xlsx

Second,xlrd: Read Excel

ImportXlrdbook= Xlrd.open_workbook ('App_student.xls')#Open this Excel must exist, otherwise it will be an errorSheet = book.sheet_by_index (0)#get sheet page based on index of sheet pageSheet2 = Book.sheet_by_name ('shee1')#get the sheet page based on the name of the sheet pagePrint(Sheet.cell (0,0). Value)Print(Sheet.cell (1,0). Value)#gets the value of the specified cell, the first value is a column, the second value is a rowPrint(sheet.row_values (0))#get to the first few lines of contentPrint(Sheet.row_values (1))#get to the first few lines of contentPrint(sheet.nrows)#get the total number of lines in Excel forIinchRange (Sheet.nrows):#loop fetch to each row of data    Print(Sheet.row_values (i))#fetch data for each rowPrint(Sheet.ncols)#total number of columnsPrint(sheet.col_values (0))#take the first few columns of data

Third, xlutils: Modify Excel

Xlutils module is used to modify the contents of Excel, you can not directly modify the original Excel content, you must first copy a new Excel, and then modify the new Excel, use the following:

Importxlrd fromXlutilsImportCopy#Import the copy Excel module of the Xlutils moduleBook = Xlrd.open_workbook ('App_student.xls')#Use the XLRD module first to open an ExcelNew_book = copy.copy (book)#Copy an Excel by Xlutils the Copy method inside this moduleSheet = new_book.get_sheet (0)#Get Sheet pageLis = ['numbering','name','Sex','Age','Address','class','Phone number','Gold coins'] forCol,filedinchEnumerate (LIS): Sheet.write (0,col,filed)#write to Excel, the first value is a row, the second value is a columnNew_book.save ('App_student.xls')#To Save the new Excel, save Excel must use the suffix name of. xls, not can be. xlsx

Python Learning notes-manipulating Excel