Python notes 8 and python notes

Python notes 8 and python notes
Prerequisites:

Python excel requires xlrd, xlwt, and xlutils. Read, write, and update excel files. Import these modules before using excel. The demo is as follows:

Excel-read operation knowledge point:

1 import xlrd 2 ''' 3 the procedure for reading an excel file is as follows: 4 1. open excel. The opened excel file must exist. get sheet object 6 3. perform operations on excel: 7. Obtain the total number of rows, total number of columns, read data from each row of excel, read data from each column of excel, and obtain the value of a cell by using 8 ''' 9 # Open excel, the opened excel file must exist. The returned book object 10 book = xlrd.open_workbook('students.xlsx') 11 # obtain the sheet object 12 sheet = book through the index. sheet_by_index (0) 13 # When multiple sheet pages exist, you can use the sheet name to obtain the sheet object 14 sheet1 = book. sheet_by_name ('sheet1') 15 16 # obtain the total number of rows in excel, 17 rows = sheet. nrows18 # obtain the total number of columns in excel 19 cols = sheet. ncols20 # obtain the data of excel 2nd rows. The returned result is list: [2.0, 'B', 'women'] 21 row_value = sheet. row_values (2) 22 # obtain the data in the excel 1st column. The returned results are list: ['name', 'A', 'B', 'C', 'D ', 'E', 'F', 'G', 'White ', 'black black'] 23 col_values = sheet. col_values (1) 24 # obtain the data in column 8th of row 1st of the cell. The returned result is text: 'White '25 cell_value = sheet. cell (8, 1) 26 # convert the result of the text type to the str type: Xiaobai 27 cell_str = sheet. cell (8, 1 ). value
Note: When obtaining the values of each row, column, and cell, note that the values of rows and columns must exist. Otherwise, the following error occurs: list index out of range.

Excel-small case of reading excel:

1 import xlrd 2 ''' 3 reads excel data. The columns read data are fixed and each row of data is read cyclically. The format of the data read is as follows: 4 [5 {'name ': xxx, 'sex': xxx, 'id': 1}, 6 {'name': xxx, 'sex ': xxx, 'id': 1 }, 7 ....... 8] 9 '''10 def readExcel (): 11 try: 12 # If the input excel does not exist, open the excel and report 13 book = xlrd.open_workbook('students.xlsx') 14 blank t Exception as e: 15 print ('error msg: ', e) 16 else: 17 sheet = book. sheet_by_index (0) 18 # obtain the total number of rows in excel 19 rows = sheet. nrows20 stu_list = [] 21 # Read data in each row cyclically, and 0th rows are header information. Therefore, 22 for row in range (1, rows) is read from 1st rows ): 23 stu = {} 24 # obtain all data in column 0th of row 25 id = sheet. cell (row, 0 ). value26 name = sheet. cell (row, 1 ). value27 sex = sheet. cell (row, 2 ). value28 # Add id, name, and sex to the dictionary. If the element does not exist, it is added, otherwise, the update operation 29 stu ['id'] = id30 stu ['name'] = name31 stu ['sex'] = sex32 stu_list.append (stu) 33 print (stu_list) 34 35 if _ name _ = '_ main _': 36 readExcel ()

The excel data format is as follows:

Excel-write operation knowledge point:

1 import xlwt 2 ''' 3 the procedure for writing an excel file is as follows: 4 1. open excel and open non-existing excel. If you open an existing excel file and perform a write operation, the written data will overwrite the previous data. get the sheet object and specify the sheet name 6 3. perform operations on excel: 7. Write and save excel 8 ''' 9. # Open excel and create book object 10 book = xlwt. workbook () 11 # create a sheet and specify the sheet Name 12 sheet = book. add_sheet ('stu2') 13 # Write excel Data, write a value in column n of row n, and write the data type as str14 sheet. write (0, 0, 'number') 15 sheet. write (0, 1, 'name') 16 sheet. write (0, 2, 'age') 17 # Save the excel file with the suffix xls18 book.save('studet.xls ')

After an excel file is written into a new excel file, the data format is as follows:

If an excel file already exists in an excel file, the excel file format after the write operation is as follows:

---->

Excel-excel writing case:

1 import xlwt 2 ''' 3: list data: 4 [{'name': 'White ', 'id': 1.0, 'sex': 'male '}, 5 {'name': 'floret ', 'id': 2.0, 'sex': 'female'}, 6 {'name': 'blacklist ', 'id ': 3.0, 'sex': 'male'}, 7 {'name': 'laru', 'id': 4.0, 'sex': 'female '}, 8 {'name': '小', 'id': 5.0, 'sex': 'male'}] 9. Write the excel file with the title information: number, name, gender 10''' 11 def writeExcel (): 12 book = xlwt. workbook () 13 sheet = book. add_sheet ('stu') 14 titles = ['number', 'name', 'Gender '] 15 # Read the length of titles cyclically. The col value is 0, 1, 2, and write the title value to excel16 for title_col in range (len (titles): 17 # Write the title to the col column of row 0th in excel, and write the titles [col] value to 18 sheet. write (0, title_col, titles [title_col]) 19 students_list = [{'name': 'White ', 'id': 1.0, 'sex': 'male '}, {'name': 'floret ', 'id': 2.0, 'sex': 'female}, {'name': 'black', 'id': 3.0, 'sex': 'male'}, {'name': 'laru', 'id': 4.0, 'sex': 'female}, {'name ': '小', 'id': 5.0, 'sex': 'mal'}] 20 for stu_row in range (len (students_list): 21 # Read the length of student_list cyclically, starting from 0 and writing data to excel from 1st rows. 22 # The data written to excel takes values from list to obtain each element of list and returns the dictionary, then obtain the value23 sheet Using the dictionary key. write (stu_row + 1, 0, students_list [stu_row] ['id']) 24 sheet. write (stu_row + 1, 1, students_list [stu_row] ['name']) 25 sheet. write (stu_row + 1, 2, students_list [stu_row] ['sex']) 26 book.save('student.xls ') 27 if _ name _ =' _ main __': 28 writeExcel ()

The excel data format is as follows:

Excel-update operation knowledge point:

1 import xlrd 2 from xlutils. copy import copy 3 ''' 4 Update excel: 5 1. open excel. The updated excel file must exist. copy a new excel file and use the copy method in the xlutils module. update Data in excel 8. 4. save the updated excel data. The previous excel data will not be changed to 9''' 10 from xlutils. copy import copy11 # Open excel12 book = xlrd.open_workbook('student.xlsx') 13 # copy a new excel14 new_book = copy (book) 15 # view all methods of an object 16 # print (dir (new_book) 17 # obtain the sheet object of the new excel 18 sheet = new_book.get_sheet (0) 19 # Add a column of Data 20 sheet. write (0, 3, 'update') 21 # update the value of column 4th in row 1st, change it to 'Guo jing', and change the data type to str22 sheet. write (4, 1, 'Guo jing') 23 # Save the changed excel file. The previous excel file does not change 24 new_book.save('student.xls ')

The above is a simple excel operation ~~~~