''' Excel文件三个对象 workbook： 工作簿，一个excel文件包含多个sheet。 sheet：工作表，一个workbook有多个，表名识别，如“sheet1”,“sheet2”等。 cell： 单元格，存储数据对象 ''' import openpyxl from openpyxl import Workbook, load_workbook wb = Workbook() ws1 = wb.active # active方法得到第一个sheet ws2 = wb.create_sheet('MySheet1') # 插入到最后default ws3 = wb.create_sheet('MySheet3', 2) # 插入到制定位置，0表示最前面 ws1.title = 'newSheet1' # 创建的sheet的名称会自动创建，按照sheet，sheet1，sheet2自动增长，通过title属性可以修改其名称 ws4 = wb.create_sheet(title='Sheet4') ws4.sheet_properties.tabColor = "1072BA" # 默认的sheet的tab是白色的，可以通过 RRGGBB颜色来修改sheet_properties.tabColor属性从而修改sheet tab按钮的颜色: # 当你设置了sheet的名称，可以将其看成workbook中的一个key。也可以使用openpyxl.workbook.Workbook.get_sheet_by_name() 方法 # 单元格赋值，3种方式 ws4.cell(row=2, column=3, value=100) ws4.cell(row=2, column=4).value = 200 ws4['E2'] = 300 ws4.cell(3, 3, 666) # ws.append(iterable) # 添加一行到当前sheet的最底部（即逐行追加从第一行开始） iterable必须是list,tuple,dict,range,generator类型的。 1,如果是list,将list从头到尾顺序添加。 2，如果是dict,按照相应的键添加相应的键值 # ws.append([‘This is A1’, ‘This is B1’, ‘This is C1’]) # ws.append({‘A’: ‘This is A1’, ‘C’: ‘This is C1’}) # ws.append({1: ‘This is A1’, 3: ‘This is C1’}) tableTitle = ['age' , 'name' , 'sex'] line1 = ['18', 'John', 'M'] line2 = ['20', 'Helen', 'F'] for col in range(len(tableTitle)): c = col + 1 ws1.cell(row=1, column=c).value = tableTitle[col] excel1 = load_workbook('E:/test01.xlsx') excel2 = load_workbook('E:/test02.xlsx') # print(excel1.sheetnames) sheetname1 = excel1.sheetnames[-1] sheetname2 = excel2.sheetnames[-1] # t1 = excel1[sheetname1] t1 = excel1['sheetl'] t2 = excel2[sheetname2] t1['D2'] = 'hello' for i in range(10): t1["C%d" % (i+1)].value = i + 1 t1['E1'].value = "=SUM(C7:C8)" t1.append(line1) t1.append(line2) print(t1['E1'].value) excel1.save('E:/new01.xlsx') # print("A列：", t1['A']) # print("1行：", t1['1']) # print("B6格的值：", t1['B6'].value) # print("A4格的值：", t1.cell(row=4, column=1).value) # 可简写为 t1.cell(4,1).value # # 需写入的表，再打开的状态下是不能成功执行代码的 # 先用load_workbook读取一个excel，修改之后save成原来的文件，会报错：PermissionError: [Errno 13] # print("最大行数：", t1.max_row) # print("最大列数：", t1.max_column) # print("最小列数：", t1.min_column) # print(" ") # print("B列的值为：") # for i in t1['B']: # print(i.value, end=' ') print(" ") print("表格内容如下：") for r in range(t1.max_row): r += 1 for c in range(t1.max_column): c += 1 print(t1.cell(r, c).value, end=" ") # 使用end实现不换行输出 print(" ") # for row in t1.iter_rows(min_col=1, min_row=1, max_col=3, max_row=3, values_only=True): # for cell in row: # print(cell, end=' ')