Python Operations Excel detailed

Premise:

The modules that Python uses to manipulate Excel are Xlrd, XLWT, Xlutils. Read, write, and update an Excel operation. You need to import these modules first when you operate Excel, as follows:

excel-read operation knowledge points:

          Book = Xlrd.open_workbook (  sheet =  Sheet1 = book.sheet_by_name (   rows =  cols =  Row_value = Sheet.row _values (2  col_values = sheet.col_values (1  cell_value = Sheet.cell (8, 1  cell_str = Sheet.cell (8, 1). Value

Excel-Read the Excel small case:

 1 Import xlrd 2 "3 read the data of Excel, read the data column fixed, loop read each row of data, read the data format as follows: 4 [5 {' name ': xxx, ' sex ': xxx, ' id ': 1}, 6 {' name ': xxx, ' sex ': X XX, ' ID ': 1}, 7 ..... 8] 9 "" Def Readexcel (): One try:12 #若输入的excel不存在, then open Excel error book = Xlrd.open_workbook (' students.x          LSx ') except Exception as e:15 print (' Error msg: ', e) else:17 sheet = book.sheet_by_index (0) 18         #获取excel的总行数19 rows = Sheet.nrows20 stu_list = []21 #循环读取每行数据, row No. 0 is header information, so read data from line 1th 22 For row in range (1, rows): Stu = {}24 #获取第row行的第0列所有数据25 id = sheet.cell (row, 0). Value26 name = Sheet.cell (row, 1). Value27 sex = Sheet.cell (row, 2). Value28 #将id, name, S             The ex is added to the dictionary and is added if the element does not exist, otherwise it is the update operation stu[' id ' = id30 stu[' name '] = name31 stu[' sex '] = sex32 Stu_list.append (Stu) print (stu_list) if __name__ = = ' __main__ ': Readexcel ()

The Excel data format is as follows:

Excel-Write operations Knowledge points:

1 Import XLWT 2 "3 to write Excel, proceed as follows: 4 1. Open Excel, open Excel that does not exist, and if you open an existing Excel, write the data that overwrites the previous data 5 2. Gets the sheet object and specifies the name of the sheet 6 3. Work with Excel: 7     write to Excel, save Excel 8 "' 9 #打开excel创建book对象10 book = XLWT. Workbook () #创建sheet指定sheet名称12 sheet = book.add_sheet (' stu2 ') #写入excel数据, the nth column of nth Row writes a value, the data type written is Str14 sheet.write (0 , 0, ' number ') sheet.write (0, 1, ' name ') sheet.write (0, 2, ' age ') #保存excel, the saved suffix must be xls18 book.save (' Studet.xls ')

After Excel writes the new Excel, the data format is as follows:

Excel operations already exist for Excel after you write the Excel format as follows:

---->

Excel-Write Excel small case:

 1 Import XLWT 2 "3 will list data: 4 [{' Name ': ' Small white ', ' ID ': 1.0, ' sex ': ' Man '}, 5 {' name ': ' Floret ', ' ID ': 2.0, ' sex ': ' Female '}, 6 {' name ': ' Small black ', ' id ': ' 3.0 ', ' sex ': ' Male '}, 7 {' name ': ' Little ru ', ' id ': ' 4.0 ', ' sex ': ' Female '}, 8 {' name ': ' Little ', ' ID ': 5.0, ' Sex ': ' Male '}] 9 write excel,title information for: number, name, gender "one Def Writeexcel (): Book = XLWT. Workbook () sheet = book.add_sheet (' stu ') in titles = [' Number ', ' name ', ' Gender ']15 #循环读取titles的长度, col value: 0,1,2, and the title The value is written to Excel16 for Title_col in range (len (titles)): #title write to the No. 0 Col column of Excel, write Titles[col] value, Sheet.writ E (0, Title_col, titles[title_col]) students_list = [{' Name ': ' Small white ', ' ID ': 1.0, ' sex ': ' Male '},{' name ': ' Floret ', ' ID ': 2.0, ' Sex ': ' Female '},{' name ': ' Little black ', ' ID ': 3.0, ' sex ': ' Male '},{' name ': ' Little ru ', ' ID ': 4.0, ' sex ': ' Female '},{' name ': ' Little ', ' ID ': 5.0, ' Sex ': ' Male '}]20 for Stu_row in range (len (students_list)): #循环读取student_list的长度, starting with 0, writing data from line 1th when writing to Excel #写入e      The XCEL data is taken from the list, gets each element of the list, returns the dictionary, and gets the value23 from the dictionary key.   Sheet.write (stu_row+1, 0, students_list[stu_row][' id ')) sheet.write (stu_row+1, 1, students_list[stu_row][' Nam E ']) sheet.write (stu_row+1, 2, students_list[stu_row][' sex ')) book.save (' Student.xls ') if __name__ = = ' _ _main__ ': Writeexcel ()

The Excel data format is as follows:

excel-Update operation knowledge Points:

1 Import xlrd 2 from xlutils.copy import copy 3 ' 4 update Excel operation: 5 1. When you open Excel, the updated Excel must exist in 6 2. Copy a new Excel, using the Copy method in the Xlutils Module 7 3. Update the data within Excel 8 4. After you save the updated Excel data, the previous Excel data does not change 9 "from xlutils.copy import copy11 #打开excel12 book = Xlrd.open_workbook (' student.xlsx ' ) #复制一个新的excel14 New_book = Copy (book) #查看某个对象下的所有方法16 #print (dir (new_book)) #获取新excel的sheet对象18 sheet = New_ Book.get_sheet (0) #新增一列数据20 sheet.write (0, 3, ' Update ') #更新第4行第1列的值, modify it to ' Guo Jing ', modify the data type to Str22 sheet.write (4, 1, ' Guo Jing ') 23 # After you save the changed Excel, the previous Excel data does not change by the New_book.save (' Student.xls ')

The above for Excel simple operation ~ ~ ~