Python_excel file Operations

1 " "2 writing to an Excel file using the Xlrd module3 " "4 Importxlrd5Book=xlrd.open_workbook (R'/users/c2apple/desktop/Record/test report/Zhang Tao file tray/Hong Hongwen template/Color ribbon elevation/zd-qr-730114 Color test report version 00. xlsx')#Open Excel File6Sheet1=book.sheet_by_name (' First')#Open Worksheet7Row0=sheet1.row (0)#Get line No. 08 Print(row0[0])9 Print(Row0[0].value)#View the contents of a cellTen  One #reading Excel2007 and later versions of Excel files using the extension library OPENPYXL A ImportOPENPYXL -  fromOpenpyxlImportWorkbook -Fn=r'/users/c2apple/desktop/test.xlsx'    #file name theWb=workbook ()#Create a workbook -Ws=wb.create_sheet (title='Hello, World')#cell Assignment -Vs:'A1']='This is the first cell' #cell Assignment -Xst'B1']=3.1415926 +Wb.save (FN)#Save Excel File -Wb=openpyxl.load_workbook (FN)#Open an existing Excel file +WS=WB.WORKSHEETS[1]#Open the worksheet for the specified index A Print(ws['A1'].value)#reads and outputs the value of the specified cell atWs.append ([1,2,3,4,5])#add a row of data -Ws.merge_cells ('f2:f3')#Merge Cells -Vs:'F2']="=sum (a2:e2)"  #Write a formula -  forRinchRange (10,15): -      forCinchRange (3,8): -_=ws.cell (ROW=R,COLUMN=C,VALUE=R*C)#Write cell Data in Wb.save (FN) -  to " " + assuming that all classes in a school allow multiple exams per semester, students can take exams at any time and the system automatically - added to an Excel file (containing 3 columns, name, course, score), the end of the term requires that all students be counted for the highest achievement in each course.  the The following code first simulates generating random score data and then carries out statistical analysis. * " " $ ImportOPENPYXLPanax Notoginseng  fromOpenpyxlImportWorkbook - ImportRandom the  + #randomly generated data A defgeneratetrandominformation (filename): theworkbook=Workbook () +worksheett=Workbook.worksheets[0] -Worksheett.append (['name','Courses','Achievements']) $     #the first, second, and third words in Chinese names $First=tuple ('Zhao Xiansunli') -Middle=tuple ('Wei Yun He dong') -Last=tuple ('kun Yan Zhi') the     #Course Name -Subject= ('language','Mathematics','English')Wuyi     #randomly generate 200 of data the      forIinchRange (200): -Line=[] WuR=random.randint (1,100) -Name=Random.choice (first) About         #generate a Chinese name with only two words at a certain probability $         ifI>50: -name=name+random.choice (middle) -name=name+Random.choice (last) -         #generate name, course name, and score in turn A line.append (name) + Line.append (Random.choice (subject)) theLine.append (Random.randint (0,100)) - Worksheett.append (line) $    #save frost, generate files in Excel 2007 format the workbook.save (filename) the  the defGetResult (Oldfile, newfile): the     #a dictionary for storing result data -result =dict () in  the     #Open Raw Data theWorkbook =Openpyxl.load_workbook (oldfile) AboutWorksheet =Workbook.worksheets[0] the  the     #Traverse Raw Data the      forRowinchworksheet.rows: +         ifRow[0].value = ='name': -             Continue the         #Name , course name, this gradeBayiName, subject, Grade = Row[0].value, Row[1].value, row[2].value the  the         #get the course name and score information for the current name -         #returns an empty dictionary if it is not included in the result dictionary -t =Result.get (name, {}) the         #get the current student's current course score, if not present, return 0 thef =t.get (subject, 0) the         #keep only the student's highest achievement in this course the         ifGrade >F: -T[subject] =Grade theResult[name] =T the  theWorkbook1 =Workbook ()94Worksheet1 =Workbook1.worksheets[0] theWorksheet1.append (['name','Courses','Achievements']) the  the     #writes the result data in the result dictionary to an Excel file98      forName, TinchResult.items (): About         Print(name,t) -          forSubject, GradeinchT.items ():101 worksheet1.append ([name, subject, Grade])102 103 Workbook1.save (newfile)104  the if __name__=='__main__':106Oldfile=r'test1.xlsx'107Newfile=r'resultt.xlsx'108 generatetrandominformation (oldfile)109GetResult (Oldfile,newfile)

Python_excel file Operations