Detailed explanation of python operation with excel

Prerequisite:

The modules that python needs to use to operate excel include xlrd, xlwt, and xlutils. Perform read, write and update operations on excel. When operating excel, you need to import these modules first. The demo is 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 excel small case:

 1 import xlrd 2 ''' 3 读取excel的数据，读取数据的列固定，循环读取每行数据，读取后的数据格式如下： 4 [ 5 {'name':xxx,'sex':xxx,'id':1}, 6 {'name':xxx,'sex':xxx,'id':1}, 7 ....... 8 ] 9 '''10 def readExcel():11     try:12         #若输入的excel不存在，则打开excel报错13         book = xlrd.open_workbook('students.xlsx')14     except Exception as e:15         print('error msg:', e)16     else:17         sheet = book.sheet_by_index(0)18         #获取excel的总行数19         rows = sheet.nrows20         stu_list = []21         #循环读取每行数据，第0行是表头信息，所以从第1行读取数据22         for row in range(1, rows):23             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、sex添加到字典，若元素不存在则新增，否则是更新操作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()

excel data format is as follows:

excel - writing operation knowledge points:

 1 import xlwt 2 ''' 3 写 excel的操作步骤如下： 4 1. 打开excel，打开不存在的excel，若打开已存在的excel，进行写操作，写入的数据会覆盖以前的数据 5 2. 获取sheet对象并指定sheet的名称 6 3. 对excel进行操作： 7     写入excel、保存excel 8 ''' 9 #打开excel创建book对象10 book = xlwt.Workbook()11 #创建sheet指定sheet名称12 sheet = book.add_sheet('stu2')13 #写入excel数据，第n行第n列写入某个值，写入的数据类型为str14 sheet.write(0, 0, '编号')15 sheet.write(0, 1, '姓名')16 sheet.write(0, 2, '年龄')17 #保存excel,保存的后缀必须是xls18 book.save('studet.xls')

excel After writing a new excel, the data format is as follows:

excel operates the existing excel, and the excel format after the write operation is as follows:

---->

excel - Write a small excel case:

 1 import xlwt 2 ''' 3 将list数据： 4 [{'name': '小白', 'id': 1.0, 'sex': '男'}, 5     {'name': '小花', 'id': 2.0, 'sex': '女'}, 6     {'name': '小黑', 'id': 3.0, 'sex': '男'}, 7      {'name': '小茹', 'id': 4.0, 'sex': '女'}, 8       {'name': '小小', 'id': 5.0, 'sex': '男'}] 9 写入excel，title信息为：编号、姓名、性别10 '''11 def writeExcel():12     book = xlwt.Workbook()13     sheet = book.add_sheet('stu')14     titles = ['编号', '姓名', '性别']15     #循环读取titles的长度，col的值为：0,1,2,并将title值写入excel16     for title_col in range(len(titles)):17         #title 写入excel的第0行的第col列，写入titles[col]值18         sheet.write(0, title_col, titles[title_col])19     students_list = [{'name': '小白', 'id': 1.0, 'sex': '男'},{'name': '小花', 'id': 2.0, 'sex': '女'},{'name': '小黑', 'id': 3.0, 'sex': '男'},{'name': '小茹', 'id': 4.0, 'sex': '女'},{'name': '小小', 'id': 5.0, 'sex': '男'}]20     for stu_row in range(len(students_list)):21         #循环读取student_list的长度，从0开始，写入excel时从第1行开始写入数据22         #写入excel的数据是从list里进行取值，获取list的每个元素，返回字典，然后通过字典的key获取value23         sheet.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()

excel data format is as follows:

excel- Update operation knowledge points:

 1 import xlrd 2 from xlutils.copy import copy 3 ''' 4 更新excel操作： 5 1. 打开excel，更新的excel必须存在 6 2. 复制一个新的excel，使用xlutils模块中的copy方法 7 3. 更新excel内的数据 8 4. 保存更新后的excel数据，以前的excel数据不会更改 9 '''10 from xlutils.copy import copy11 #打开excel12 book = xlrd.open_workbook('student.xlsx')13 #复制一个新的excel14 new_book = copy(book)15 #查看某个对象下的所有方法16 #print(dir(new_book))17 #获取新excel的sheet对象18 sheet = new_book.get_sheet(0)19 #新增一列数据20 sheet.write(0, 3, '更新')21 #更新第4行第1列的值，将其修改为'郭静'，修改的数据类型为str22 sheet.write(4, 1, '郭静')23 #保存更改后的excel，以前的excel数据不更改24 new_book.save('student.xls')

The above are simple excel operations~~~~

The above is the detailed content of Detailed explanation of python operation with excel. For more information, please follow other related articles on the PHP Chinese website!