Python自动化办公之操作Excel文件的教程

模块导入 import openpyxl 读取excel文件打开excel文件 workbook = openpyxl.load_workbook(test.xlsx) 输出表单名字 # 输出工作簿中所有表单名字print(workbook.sheetnames)# 遍历所有表单并输出其名字for sheet in workbook: print(sheet.title) 创建表单 newsheet = workbook.create_sheet(newsheetname) 获取表单对象 # 根据表单名获取表单sheet3 = workbook.get_sheet_by_name(sheet3)sheet4 = workbook[newsheetname]# 获取当前活跃的表单worksheet = workbook.active 获取当前表单数据行列数 # 获取当前表单数据行数row_count = worksheet.max_row# 获取当前表单数据列数row_count = worksheet.max_column 获取单元格对象 selectcell = worksheet[a1]selectcell = worksheet.cell(row=1, column=2) # 行列号从1开始输出单元格信息单元格所在的行、列 print(selectcell.row, selectcell.column) 单元格的坐标 print(selectcell.coordinate) 单元格的值 print(selectcell.value) 获取列对象 selectcol = worksheet[c] 获取列对象中某一单元格 selectcell = selectcol[2] 获取连续多列组成的对象 selectcols = worksheet[b:c] 获取所有列组成的对象 allcol = worksheet.cols 获取行对象 selectrow = worksheet[2] 获取行对象中某一单元格 selectcell = selectcol[c] 获取连续多行组成的对象 selectcols = worksheet[1:4] 获取所有行组成的对象 allrow = worksheet.rows 获取一定范围内的单元格组成的对象 cell_range = worksheet[a1:c3] 遍历一定范围内的单元格 for row_range in cell_range: # 先行再列 for cell in row_range: print(cell.value) 行的字母表示与数字换算 from openpyxl.utils import get_column_letter, column_index_from_stringprint(get_column_letter(2))) # 2 => bprint(column_index_from_string(c)) # c => 3 编辑excel文件创建workbook对象 workbook = openpyxl.workbook() 创建表单 workbook.create_sheet(index=1, titl) 删除表单 workbook.remove_sheet(workbook.get_sheet_by_name(sheet3)) 获取当前活跃的表单 sheet = workbook.active 修改表单名称 sheet.title = 设置的表单名称修改单元格的值 sheet[a1] = new value 批量写入数据方法一 import openpyxlfrom openpyxl.utils import get_column_letter, column_index_from_stringworkbook = openpyxl.workbook()ws1 = workbook.create_sheet(第一页)for row in range(40): ws1.append(range(17)) 方法二 import openpyxlfrom openpyxl.utils import get_column_letter, column_index_from_stringworkbook = openpyxl.workbook()ws2 = workbook.create_sheet(第二页)rows = [ [number, batch1, batch2], [2,40,30],[3,50,25], [4,30,30],[5,60,10]]for row in rows: ws2.append(row) 方法三 import openpyxlfrom openpyxl.utils import get_column_letter, column_index_from_stringworkbook = openpyxl.workbook()ws3 = workbook.create_sheet(第三页)for row in range(5,30): for col in range(15,24): ws3.cell(column=col, row=row, value=123) 修改完毕保存到文件 workbook.save(created.xlsx) 想进一步了解编程开发相关知识，与我一同成长进步，请关注我的公众号“松果仓库”，共同分享宅&程序员的各类资源，谢谢！！！