python openpyxl读取execl
需要安装openpyxl模块 1 获取execl文件中所有的工作表的sheet表名 import openpyxl wb = openpyxl.load_workbook(file) print (wb.active) #当前活动的工作表,当前工作的 mysheet1=wb['Sheet1'] #获取某张工作表 print (list(mysheet1.values)) #mysheet1.values 生成器,以列表list的形式,获取到Sheet1所有的数据 读取单元格的数据: 1>通过单元名称访问: sheet=wb.active x2=sheet['A2'].value 2>通过cell()函数访问(指定行数,列数): celldata1=mysheet1.cell[1,2].value #读取sheet中(第1行,第2列)的数据 celldata1=mysheet1.cell(row=1,column=2) print (celldata1.value) # 获取workbook中所有的表格sheet sheets=wb.get_sheet_names() print (sheets,type(sheets)) sheets = wb.sheetnames print (sheets) #结果 ['Sheet1', 'Sheet2', 'Sheet3'] #获取表格内容所在的范围: print (sheet.dimensions) -->A1:G14 获取某个单元格的行,列,坐标: print (cell.row,cell.column,cell.coordinate) 读取指定范围的单元格数据: print (sheet['A1:B4']) need=sheet['A1:B4'] for row in need: for cell in row: print (cell.value) # 循环遍历所有sheet for i in range(len(sheets)): mysheet = wb[sheets[i]] print (mysheet.max_row) #行数 print (mysheet.max_column) #列数 print (mysheet.cell(1,2).value #读取sheet1表中的第1行,第2列的数据 print (mysheet) #结果:<Worksheet "Sheet1"> <Worksheet "Sheet2"> <Worksheet "Sheet3"> print('第' + str(i + 1) + '个sheet Name: ' + sheet.title) 运行结果: 第1个sheet Name: Sheet1 第2个sheet Name: Sheet2 第3个sheet Name: Sheet3 openpyxl遍历行: openpyxl 提供了iter_row()函数, 用于读取行对应的数据 按行读取工作表的数据: mysheet1=wb['Sheet1'] max_r=mysheet.max_row #最大的行数 max_c=mysheet.max_column #最大的列数 for row in sheet.iter_rows(min_row=1, min_col=1, max_col=max_c, max_row=max_r): print (row)): # 读取标题行 for row in sheet.iter_rows(max_row=1): title_row = [cell.value for cell in row] print(title_row) # 读取标题行以外数据 for row in sheet.iter_rows(min_row=2): row_data = [cell.value for cell in row] print(row_data) Openpyxl 遍历列 openpyxl 提供iter_col()函数,枚举表单的所有列。 for row in sheet.iter_cols(min_row=1, min_col=1, max_row=6, max_col=3): for cell in row: print(cell.value, end=" ") print() 2 写入数据到新的execl里: 将data数据写入至execl表格中: from openpyxl import Workbook wb = Workbook() sheet = wb.active data = ((11, 48, 50),(81, 30, 82),(20, 51, 72),(21, 14, 60),(28, 41, 49),(74, 65, 53),("Peter", 'Andrew',45.63)) for i in data: sheet.append(i) wb.save('appending_values.xlsx') 将原x.xlsx表里的数据重新写入至新的new.xlsx表格中 from openpyxl import Workbook wb = openpyxl.load_workbook(file) sheets = wb.sheetnames sheet=wb.active sheet=wb[sheets[0]] new_wb=Workbook() new_sheet=new_wb.active for row in sheet.iter_rows(min_row=1): #获取原表里的所有的数据 row_data = [cell.value for cell in row] #print(row_data) new_sheet.append(row_data) new_wb.save('new.xlsx') 遍历execl中的多个sheet表数据: import xlrd import openpyxl #mport pyexcel from xlrd import open_workbook #file='C:\Users\50774\Desktop\pyscript\\x.xlsx' file="x.xlsx" print (file) book=xlrd.open_workbook(file) sname=openpyxl.load_workbook(file).sheetnames #print (sname) wb = openpyxl.load_workbook(file) # 获取workbook中所有的表格 sheets = wb.sheetnames print (sheets) for i in range(len(sheets)): print("*********************") mysheet = wb[sheets[i]] print (mysheet.max_row) #行数 print (mysheet.max_column) #列数 data = [] for r in range(1,mysheet.max_row+1): ln = [] for c in range(1,mysheet.max_column+1): celldata=mysheet.cell(r,c).value #print (celldata) ln.append(celldata) #print (ln) data.append(ln) print (data) 3 Openpyxl 筛选和排序数据 auto_filter属性用于设置筛选和排序条件。如下代码: from openpyxl import Workbook wb = Workbook() sheet = wb.active sheet['A3'] = 40 sheet['B3'] = 26 row_count = [ (93,45), (23,54), (80,43), (21,12), (63,29)] for row in row_count: sheet.append(row) print(sheet.dimensions) for a1,a2 in sheet[sheet.dimensions]: print(a1.value, a2.value) sheet.auto_filter.add_sort_condition('B2:B8') sheet.auto_filter.add_filter_column(1, ['40', '26']) wb.save('dimension_1.xlsx') 4 Openpyxl 合并单元格cell 用merge_cells() 方法合并单元格。当合并单元格时,左上角的单元格将从工作表中移除。openpyxl还提供unmerged_cells()方法来拆分单元格。 from openpyxl.styles import Alignment wb = Workbook() sheet = wb.active sheet.merge_cells('A1:B2') #将A1到B2合并成一个单元格 cell = sheet.cell(row=1, column=1) cell.value = 'Devansh Sharma' cell.alignment = Alignment(horizontal='center', vertical='center') wb.save('merging.xlsx')