《从此做表不加班》Excel自动化处理
办公自动化是将现代化办公和计算机技术结合起来的一种新型的办公方式
office 家族其实都可以用 VBA 解决自动化的问题，但可能很多人不会用
python 针对 excel 有很多的第三方库可以用，比如 xlwings、xlsxwriter、xlrd、xlwt、pandas、xlsxwriter、win32com、xlutils 等等
这些库可以很方便地实现对 excel 文件的增删改写、格式修改等，当然并不推荐你全部都去学尝试一下，这样时间成本太大了
| 类型 | xlrd&xlwt&xlutils | XlsxWriter | openpyxl | Excel开放接口 |
| 读取 | 支持 | 不支持 | 支持 | 支持 |
| 写入 | 支持 | 支持 | 支持 | 支持 |
| 修改 | 支持 | 不支持 | 支持 | 支持 |
| xls | 支持 | 不支持 | 不支持 | 支持 |
| xlsx | 高版本 | 支持 | 支持 | 支持 |
| 大文件 | 不支持 | 支持 | 支持 | 不支持 |
| 效率 | 快 | 快 | 快 | 超慢 |
| 功能 | 较弱 | 强大 | 一般 | 超强大 |
xlrd：用于读取 Excel 文件；
xlwt：用于写入 Excel 文件；
xlutils：用于操作 Excel 文件的实用工具，比如复制、分割、筛选等
Excel写入数据-xlwt
安装
pip install xlwt
常用方法
| 函数名 | 含义 |
| xlwt.Workbook() | 创建一个新的Excel文件 |
| xlwt.add_sheet(sheet_name) | 创建一个新的工作薄 |
| sheet.write(row,col,data) | 向单元格中编写数据 |
| workbook.save(path) | 保存数据到文件 |
代码
# Ctrl + ~ 打开控制台 # 输入 pip install xlwt 用于安装第三方模块库 # 导入xlwt模块 import xlwt # 创建excel wb = xlwt.Workbook() # 创建一个工作薄 ws = wb.add_sheet('电影') # 往工作薄的单元格增加数据 # ws.write(row,col) 从0开始记数 # 增加第1行数据 ws.write(0,0,'影片') ws.write(0,1,'综合票房') ws.write(0,2,'票房占比') ws.write(0,3,'排片场次') # 增加第2行数据 ws.write(1,0,'如果声音记不得') ws.write(1,1,361.57) ws.write(1,2,33.3) ws.write(1,3,95371) # 保存数据 wb.save('./create_data/01_电影数据.xlsx')
Excel读取数据-xlrd
安装
pip install xlrd
常用方法与属性
| 函数名&属性 | 含义 |
| xlrd.open_workbook(path) | 打开一个excel文件 |
| workbook.nsheets | 获取excel工作薄的数量 |
| workbook.sheets() | 获取excel所有工作薄 |
| workbook.sheet_names() | 获取excel的工作薄名 |
| sheet.sheet_by_index(num) | 获取第num个工作薄 |
| sheet.sheet_by_name(name) | 根据工作薄名获取工作薄 |
| sheet.nrows | 获取工作薄总行数 |
| sheet.row_values(num) | 获取指定行数据 |
| sheet.col_values(num) | 获取指定列数据 |
| sheet.cell(row,col) | 获取指定单元格 |
| sheet.row(num)[col] | 获取指定单元格 |
| cell.value() | 获取单元格的值 |
| cell.ctype | 获取单元格内容的数据类型 |
| sheet.cell_value(row,col) | 获取制定单元格的值 |
代码
# 打开控制台 ctrl+~ 输入命令 # pip install xlrd 安装读取数据的模块 # 导入模块 import xlrd # 打开excel文件 wb = xlrd.open_workbook('./create_data/01_电影数据.xlsx') # 读取里面的数据 # 获取工作薄 print(f'excel中有{wb.nsheets}个工作薄') print(f'excel中sheets的名字：{wb.sheet_names()}') # 选中工作薄 ws1 = wb.sheet_by_index(0) ws2 = wb.sheet_by_name('电影') # print(ws1) # print(ws2) # 获取单元格的数据 print(f'sheet里面一共有{ws1.nrows}行 {ws1.ncols}列的数据') # 获取单个单元格 print(f'第1行第2列的值:{ws1.cell_value(0,1)}') print(f'第1行第2列的值:{ws1.cell(0,1).value}') print(f'第1行第2列的值:{ws1.row(0)[1].value}') # 获取多个单元格 print(f'第1行的数据是:{ws1.row_values(0)}') print(f'第1列的数据是:{ws1.col_values(0)}') # 获取所有单元格 for r in range(ws1.nrows): for c in range(ws1.ncols): print(f'第{r}行 第{c}列的数据是: {ws1.cell_value(r,c)}')
Excel更新数据-xlutils
更新数据的方案：
1、获取源数据重新写一遍
2、直接在源数据上写新的数据(推荐)
安装
pip install xlutils
常用方法与属性
| 函数名 | 含义 |
| xlutils.copy.copy(workbook) | 复制指定excel文件 |
代码
# pip install xlutils import xlrd from xlutils.copy import copy # 读取文件 read_wb = xlrd.open_workbook('./create_data/01_电影数据.xlsx') # 复制文件 wb = copy(read_wb) # 修改数据 # 获取当前excel里面的第1个工作薄 ws = wb.get_sheet(0) # 增加数据 ws.write(5,0,'保家卫国') ws.write(5,1,113) ws.write(5,2,5.1) ws.write(5,3,490) # 增加统计信息 read_ws = read_wb.sheet_by_index(0) count = 0 for r in range(1,read_ws.nrows): num = read_ws.cell_value(r,3) # 累加排场次数 count += num # 增加一个新的工作薄 new_ws = wb.add_sheet('汇总数据') new_ws.write(0,0,'总排片次数') new_ws.write(0,1,count) # 保存数据 wb.save('./create_data/02_电影数据_new.xlsx')
Excel设置样式
常用方法与属性
| 函数名&属性 | 含义 |
| xlwt.Font() | 创建字体样式 |
| font.name | 设置字体类型 |
| font.colour_index | 设置字体颜色 |
| font.height | 设置字体大小 |
| font.bold | 设置字体是否为加粗 |
| font.underline | 设置字体下划线 |
| font.italic | 设置字体斜体 |
| xlwt.Alignment() | 创建字体位置样式 |
| alignment.horz | 设置字体横向位置 #1左 2中 3右 |
| 函数名&属性 | 含义 |
| alignment.vert | 设置字体纵向位置 #0上 1中 2下 |
| row.height_mismatch | 设置是否开启行高设置 True为开启 |
| row.height | 设置单元格行高,值为256*像素点 |
| col.width | 设置单元格宽度,值为256*像素点 |
| xlwt.Borders() | 创建边框样式 |
| border.left | 设置左边框粗细 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双 线:6，细点虚线:7 |
| border.right | 设置右边框粗细 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双 线:6，细点虚线:7 |
| border.top | 设置上边框粗细 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双 线:6，细点虚线:7 |
| border.bottom | 设置下边框粗细 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双 线:6，细点虚线:7 |
| border.left_colour | 设置左边框颜色 |
| border.right_colour | 设置右边框颜色 |
| border.top_colour | 设置上边框颜色 |
| border.bottom_colour | 设置下边框颜色 |
| xlwt.Pattern() | 创建背景颜色样式 |
| pattern.pattern | 设置背景样式 |
| xlwt.XFStyle() | 设置总样式对象,用于应用到单元格 |
| xlwt.easyxf() | 设置总样式对象 |
代码
字体
import xlwt # 创建一个excel文件 wb = xlwt.Workbook() # 创建一个工作薄 ws = wb.add_sheet('数据') # 设置样式 # 创建一个字体样式 ft = xlwt.Font() ft.name = '微软雅黑' # 设置字体 ft.height = 30*20 # 设置字体大小 ft.bold = True # 设置字体加粗 ft.underline = True # 设置字体下划线 ft.italic = True # 设置字体斜体 ft.colour_index = 2 # 设置字体颜色 # 创建一个样式对象 style1 = xlwt.XFStyle() style1.font = ft # 增加内容 ws.write(1,1,'吕小布') ws.write(2,2,'吕小布',style1) # 保存数据 wb.save('./create_data/03_excel样式.xlsx')
边框
import xlwt # 创建一个excel文件 wb = xlwt.Workbook() # 创建一个工作薄 ws = wb.add_sheet('数据') # 设置样式 # 创建一个字体样式 boder = xlwt.Borders() # 线的种类 # 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7 boder.top = 1 boder.bottom = 6 boder.left = 4 boder.right = 5 # 线的颜色 boder.top_colour = 3 boder.bottom_colour = 4 boder.left_colour = 7 boder.right_colour = 6 # 创建一个样式对象 style1 = xlwt.XFStyle() style1.borders = boder # 增加内容 ws.write(1,1,'吕小布') ws.write(2,2,'吕小布',style1) # 保存数据 wb.save('./create_data/04_excel样式_边框.xlsx')
内容位置
import xlwt # 创建一个excel文件 wb = xlwt.Workbook() # 创建一个工作薄 ws = wb.add_sheet('数据') # 设置样式 # 创建一个字体样式 align = xlwt.Alignment() # 设置 上下位置 0 上 1 中 2 下 align.vert = 1 # 设置 左右位置 1左 2中 3右 align.horz = 2 # 设置单元格大小 # 如果要设置单元格高度，需要开启设置高度属性 ws.row(2).height_mismatch= True # 开启设置高度权限 ws.row(2).height = 50 *256 ws.col(2).width = 50 * 50 # 创建一个样式对象 style1 = xlwt.XFStyle() style1.alignment = align # 增加内容 ws.write(1,1,'吕小布') ws.write(2,2,'吕小布',style1) # 保存数据 wb.save('./create_data/05_excel样式_内容位置.xlsx')
背景颜色
import xlwt # 创建一个excel文件 wb = xlwt.Workbook() # 创建一个工作薄 ws = wb.add_sheet('数据') # 设置样式 # 创建一个字体样式 pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN # 实心颜色 pattern.pattern_fore_colour = 6 # 创建一个样式对象 style1 = xlwt.XFStyle() style1.pattern = pattern # 通过easyxf()设置样式 style2 = xlwt.easyxf('font:boldon,colour_index 5;align: vert center,horizcenter') # 增加内容 ws.write(1,1,'吕小布') ws.write(2,2,'吕小布',style1) ws.write(3,3,'吕小布',style2) # 保存数据 wb.save('./create_data/06_excel样式_背景颜色.xlsx')
