devbox
神奇cpp
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

python 操作Excel入门到进阶_python excel文件操作

作者：神奇cpp | 2024-07-10 04:26:11

赞

踩

python excel文件操作

目录

openpyxl操作Excel

读取工作簿（公式）

保存工作簿

写入内容

表（sheet）

创建表

选择表（sheet）

查看表名

删除工作表

修改表名称

复制工作表

单元格（cell)

获取表格尺寸大小

单元格访问

多单元格访问

写入单元格

append写入一行或多行数据

写入一列

插入一行或多行

插入一列或多列

删除多行/列

移动范围数据

获取单元格的行数、列数、坐标

获取最大行/列号

合并和拆分单元格

指定删除单元格（行/列）

写入图片

获取整行数据

单元格写入公式

openpyxl样式调整

设置字体样式

设置对齐样式

设置边框样式

设置单元格填充样式

设置行高和列宽

设置格式（常规，文本，自定义，日期）

设置日期格式

没啥用的方法

根据数据得到字母，根据字母得到数字

openpyxl案例：

1、拆分表格

2、提取信息

3、查找字符串

4、汇总

xlwings操作Excel

打开Excel程序

保存、退出、关闭

新建Excel文件

获取表

读取内容

写入数据

获取数据范围

输出并修改行高列宽

获取及设置公式

获取、设置及清除颜色格式

xlrd读取Excel

打开Excel文件

查看工作簿中所有工作表

指定工作表三种方式

获取表格最大行数和列数

获取整行或整列的值

循环读取所有行

表操作(待试验)

获取单元格的值

xlrd案例

1、同层级文件夹中查找字符串

2、多层级文件夹中查找

 3、匹配数据

4、汇总多个excel

xls转xlsx

openpyxl：不需要 打开Excel 进程，仅支持xlsx、xlsm文件进行读、写操作

xlwings：需要安装并打开 Excel 进程，支持 xls 和 xlsx 格式；可以调用 Excel 文件中 VBA 写好的程序；和 matplotlib 以及 pandas 的兼容性强

xlrd：可以对xlsx、xls、xlsm文件进行读操作且效率高

xlwt：主要对xls文件进行写操作且效率高，但是不能执行xlsx文件

openpyxl操作Excel

注意：openpyxl索引从1开始

读取工作簿（公式）

  1. import openpyxl
  2. # 实例化，此步可忽略
  3. wb = openpyxl.Workbook()
  4. # 读取指定文件
  5. wb = openpyxl.load_workbook('新工作簿.xlsx')
  6.  
  7. # 读取的是公式（data_only默认为False，可不写）：
  8. wb = openpyxl.load_workbook(filename, data_only=False)
  9.  
  10. #data_only=True读取的是公式计算值：
  11. wb = openpyxl.load_workbook(filename, data_only=True)

保存工作簿

wb.save('新工作簿.xlsx')

写入内容

  1. from  openpyxl  import  load_workbook
  2. wb=load_workbook(r"D:\data\新工作簿.xlsx")
  3. ws=wb.active
  4. ws.cell(1,2).value="写入内容"
  5. wb.save("新工作簿.xlsx")
  6.  
  7. #ws=wb["表名字"]

表（sheet）

创建表

  1. # 插入到最后
  2. ws1 = wb.create_sheet('工作簿1')
  3. # 插入到最开始的位置 create_sheet(表名,位置)
  4. ws2 = wb.create_sheet('新工作表',0)

选择表（sheet）

  1. # 当前活动的工作表
  2. sh1 = wb.active
  3. sh2 = wb['新工作表']
  4. sh3 = wb.get_sheet_by_name("新工作表")

查看表名、工作簿名

  1. # 显示所有表名
  2. print(wb.sheetnames)
  3. # 打印指定表名
  4. print(wb.sheetnames[0])
  5. # 遍历所有表
  6. for sheet in wb:
  7.     print(sheet.title)
  8.  
  9. # 查看当前工作簿名
  10. ws.title

查看

删除工作表

  1. # 方法一
  2. sh.remove(sheet)
  3. # 方法二
  4. del sh['新工作表']

修改表名称（重命名）

sh2.title="new_sheet_name"

复制工作表

Workbook.copy_worksheet(表名) 方法：复制整个表作为副本，包含单元格（包括值、样式、超链接和注释）和某些工作表属性（包括尺寸、格式和属性）。不复制所有其他工作簿/工作表属性-例如图像、图表。

  1. wb = openpyxl.load_workbook("原文件.xlsx")
  2. ws = wb.active
  3. target = wb.copy_worksheet(ws)  # 复制整个工作簿 + 1（ws）
  4. wb.save("新文件.xlsx")

单元格（cell)

获取表格尺寸大小

返回excel表格中数据有几行几列

sh.dimensions   # 返回A1:G10

单元格访问

  1. cell = sh["A1"]   # 返回<Cell '新工作表'.A1>
  2.  
  3. # 获取单元格的数据
  4. sh["A1"].value
  5. sh.cell(row=1,column=1).value

多单元格访问

  1. # 指定坐标范围的值
  2. r1 = sh["A1:C3"]
  3.  
  4. # 指定列的值
  5. c1 = sh['B']
  6. c2 = sh['A:C']
  7.  
  8. # 指定行的值
  9. r2 = sh[10]
  10. r3 = sh[1:10]
  11.  
  12. # 输出每个单元格的值
  13. for cell in r1:
  14.     print(cell.value)
  15.  
  16. # 通过指定范围
  17. # 方法一：（获取A1:B2的值）
  18. for row in sh.iter_rows(min_row=1,max_row=2,min_col=1,max_col=2):
  19.     print(row)  # 返回 (<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>)
  20.     for cell in row:
  21.         print(cell.value)  
  22. # 方法二：（获取A2:A~的值）
  23. for row in range(2,sh.max_row+1):
  24.     for col in range(1,2):
  25.         cell = sh.cell(row=row,column=col)
  26.         print(cell.value)
  27.  
  28. # 简写获取矩阵值
  29. r = ws["B2:N18"]
  30. values = [[cell.value for cell in row] for row in r]
  31.  
  32. # 读取所有的行
  33. for row in sh.rows:
  34.     print(row)  # 返回迭代器
  35.  
  36. # sh.rows/sh.columns 所有行/列的迭代器，可通过循环获取
  37. # 读取所有的行的值
  38. for row in sh.rows:
  39.     print(row)  # 返回迭代器
  40.     for cell in row:
  41.         print(cell.value) # 返回单元格的值
  42. # 读取所有的列的值
  43. for column in sh.columns:
  44.     for cell in column:
  45.         print(cell.value)

写入单元格

  1. # 方法一
  2. sh.cell(row, colum).value = 新的值  # 修改单元格的数据
  3.  
  4. # 方法二：row 行；column 列 value 值
  5. sh.cell(row=2,column=3,value=10)
  6.  
  7. sh['B10'] = '=SUM(B1:B9)'  #  将公式写入单元格

根据特定文本查找行列号

  1. from openpyxl import load_workbook
  2.  
  3. def find_row_by_char(file_path,sheet_name,char):
  4.     # 加载工作簿和工作表
  5.     workbook = load_workbook(file_path)
  6.     sheet = workbook[sheet_name]
  7.  
  8.     # 遍历工作表中的所有行
  9.     for row in sheet.iter_rows():
  10.         for cell in row:
  11.             if char in cell.value:
  12.                 return cell.row   # cell.column返回列号
  13.     return None  # 如果没有找到字符，返回None
  14.  
  15. # # 使用示例
  16. file_path = 'example.xlsx'  # Excel文件路径
  17. sheet_name = 'Sheet1'  # 工作表名称
  18. char_to_find = '撤销原因'  # 要查找的字符
  19. row_number = find_row_by_char(ds_低保,char_to_find)
  20. if row_number:
  21.     print(f'字符找到在第{row_number}行')
  22. else:
  23.     print('字符未找到')

append写入一行或多行数据

1、append是按行向Excel中追加数据，从当前行的下一行开始追加，默认从第一行开始

2、append的数据类型必须是list，tuple，dict

sheet.append(列表) ：追加写入

  1. sh = wb['sheet']
  2. data1 = ['王俊凯',23,'歌手']  # 一行写入
  3. sh.append(data1)

 批量写入需循环

  1. sh = wb['sheet']
  2. data1 = [['王俊凯',23,'歌手'],
  3.          ['杨幂',33,'演员'],
  4.          ['杨紫',29,'演员']]
  5. for i in data1:
  6.     sh.append(i)

指定位置插入：表._current_row=行数

  1. ws._current_row=20 # 将当前行指定在20行
  2. ws.append([1,2,3])  # 1,2,3将插入21行的A,B,C列

append的数据类型（list，tuple，dict）

如果数据是dict，那keys所对应的value只能是一个str/number，不能是list/tuple/dict

  1. x = [1,2,3,4,5]
  2. y = (6,7,8,9,10)
  3. z = {'A':11,'B':12,'C':13,'F':"aaa"}
  4. m = {'A':['a','b','c'],'B':['c','d','e'],'C':['f','g','h']}  # 无效操作
  5. ws.append(x)
  6. ws.append(y)
  7. ws.append(z)
  8. # ws.append(m)  # 此处报错

指定行列插入，利用dict（作用不大相当于ws.cell(行,列).value=）

  1. ws._current_row = 2
  2. z = {'A':11,'C':12,'E':13,'G':"aaa"}
  3. ws.append(z)

Python 处理数据（Excel）—— openpyxl 之append - 知乎

写入一列

将列表写入excel第一列

  1. l = ['张三', '李四', '王五', '赵六']
  2. for each in l:
  3.     print(l.index(each))
  4.     # 通过row关键字指定行，colunm关键字指定列，均从1开始
  5.     # 列表从0开始，单元格从1开始
  6.     ws.cell(row=l.index(each) + 1, column=1, value=each)

插入一行或多行

在idx行上面插入一行

  1. sh = wb['sheet']
  2. sh.insert_rows(idx=2)

在idx行上面插入多行（amount=4插入4行）

  1. import openpyxl
  2. wb = openpyxl.load_workbook(r'E:\测试表.xlsx')
  3. sh = wb['sheet']
  4. sh.insert_rows(idx=2,amount=4)
  5. wb.save(r'E:\测试表.xlsx')

插入一列或多列

在idx列左边插入多列（amount=3插入3列）

  1. import openpyxl
  2. wb = openpyxl.load_workbook(r'E:\测试表.xlsx')
  3. sh = wb['sheet']
  4. sh.insert_cols(idx=2)
  5. sh.insert_cols(idx=4,amount=3)
  6. wb.save(r'E:\测试表.xlsx')

删除多行/列

  1. sh = wb['sheet']
  2. sh.delete_rows(idx=2)
  3. sh.delete_rows(idx=2,amount=2) # 从第二行开始删除两行
  4. sh.delete_cols(idx=2,amount=3) # 删除三列

移动范围数据

向下移动两行，向左移动两列

  1. sh = wb['sheet']
  2. sh.move_range('C1:D1',rows=2,cols=-2)

获取单元格的行数、列数、坐标

  1. # 获取A1单元格的行、列、坐标
  2. cell = sh['A1']
  3. print(cell.row, cell.column, cell.coordinate) #返回 1 1 A1

获取最大行/列号

  1. max_row = ws.max_row #行数
  2. max_col = ws.max_column#列数
  3.  
  4. # 求A列最大行数
  5. max_row_A = max((a.row for a in day_book['A'] if a.value))

合并和拆分单元格

  1. sh.merge_cells("A1:C3")  #合并一个矩形区域中的单元格
  2. sheet.merge_cells(start_row=1, start_column=3, 
  3.                   end_row=2, end_column=4)  # 合并c1:d2
  4.  
  5. sh.unmerge_cells("A1:C3")  # 拆分单元格
  6. sheet.unmerge_cells(start_row=1, start_column=3, 
  7.                     end_row=2, end_column=4)

指定删除单元格（行/列）

  1. sh.delete_cols(3,5)  # 删除从3~5列的内容
  2. sh.delete_rows(2,7)  # 删除从2~7行的内容

写入图片

  1. from openpyxl.drawing.image import Image
  2. # 实例化图片对象 Image(接收图片)
  3. img = Image('图片1.jpg')
  4. # 指定单元格添加图片
  5. sh.add_image(img,"B3")
  6. wb.save('新工作簿.xlsx')

获取整行数据

values获取该表所有值，通过行索引获取整行

  1. # 获取所有表
  2. for sh in wb.sheetnames:
  3.     max_row = wb[sh].max_row # 最大行数
  4.     max_col = wb[sh].max_column # 最大列数
  5. # 方法一
  6.     values = list(wb[sh].values) # 获取该表所有值
  7. # 方法二
  8.     # 循环每行
  9.     for i in range(2,max_row+1):
  10.         for j in range(1,max_col+1):
  11.             # 获取单元格的值
  12.             value = wb[sh].cell(i, j).value
  13.             # 符合条件整行添加到新表中 
  14.             if value == filename:
  15.                 wb1['查找'].append(values[i-1])

单元格写入公式

大部分公式可直接复制

  1. # 获取B列最大的行号
  2. max_row_B = max((b.row for b in ws['B'] if b.value))
  3. # 循环写入公式
  4. for i in range(3,max_row_B+1):
  5.     # 售
  6.     sum_formula = f"=SUM(J{i}:AN{i})"
  7.     #sum_formula2 = f"=SUM(D{i}:I{i},-AO{i})"
  8.     ws.cell(row=i,column=41,value=sum_formula)
  9.  
  10.     #  公式引用F3:F最大行号
  11.     ws.cell(max_row+1,6).value = f"=SUM(F{3}:F{i})"

openpyxl样式调整

可指定单元格范围设置样式，需循环两次（第一次迭代，第二次单元格）

设置字体样式

Font(name字体名称,size大小,bold粗体,italic斜体,color颜色)

  1. import openpyxl
  2. from openpyxl.styles import Font
  3. wb = openpyxl.load_workbook(r'E:\测试表.xlsx')
  4. sh = wb['sheet']
  5. cell = sh['B1']
  6. font = Font(name='微软雅黑',size=12,bold=True,italic=True,color='FF0000')
  7. cell.font = font
  8. wb.save(r'E:\测试表.xlsx')

设置对齐样式

Alignment(horizontal水平对齐,vertical垂直对齐,text_rotation字体倾斜度,wrap_text自动换行)

  1. import openpyxl
  2. from openpyxl.styles import Alignment
  3. wb = openpyxl.load_workbook(r'E:\测试表.xlsx')
  4. sh = wb['sheet']
  5. cell = sh['B1']
  6. alignment = Alignment(horizontal='center',vertical='center',
  7.                       text_rotation=45,wrap_text=True)   # 倾斜可删text_rotation
  8. cell.alignment = alignment
  9. wb.save(r'E:\测试表.xlsx')

  • 水平对齐:distributed, justify, center, left, fill, centerContinuous, right, general

  • 垂直对齐:bottom, distributed, justify, center, top

设置边框样式

  1. import openpyxl
  2. from openpyxl.styles import Side,Border
  3. wb = openpyxl.load_workbook(r'E:\测试表.xlsx')
  4. sh = wb['sheet']
  5. cell = sh['A1:D4']
  6. side = Side(style='thin',color='FF000000')
  7. border = Border(left=side,right=side,top=side,bottom=side)
  8. for i in cell:
  9.     for j in i:
  10.         j.border = border
  11. wb.save(r'E:\测试表.xlsx')

  • 边线样式:double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick

设置单元格填充样式

  1. import openpyxl
  2. from openpyxl.styles import PatternFill,GradientFill
  3. wb = openpyxl.load_workbook(r'E:\测试表.xlsx')
  4. sh = wb['sheet']
  5. cell1 = sh['A1']
  6. cell2 = sh['B1']
  7. # 单色填充
  8. pattern_fill = PatternFill(fill_type='solid',fgColor="99ccff")
  9. cell1.fill = pattern_fill
  10. # 渐变填充
  11. gradient_fill = GradientFill(stop=('FFFFFF',"99ccff","000000"))
  12. cell2.fill = gradient_fill
  13. wb.save(r'E:\测试表.xlsx')

设置行高和列宽

  1. import openpyxl
  2. # from openpyxl.styles import PatternFill,GradientFill
  3. wb = openpyxl.load_workbook(r'E:\测试表.xlsx')
  4. sh = wb['sheet']
  5. # 可循环
  6. sh.row_dimensions[1].height = 30
  7. sh.column_dimensions["C"].width = 20
  8. wb.save(r'E:\测试表.xlsx')

设置格式（常规，文本，自定义，日期）

  1. from openpyxl.styles import numbers
  2.  
  3. # 设置为”常规“格式
  4. ws.cell(1, 4).number_format = 'General'
  5. # 设置为“文本”格式
  6. ws.cell(1, 4).number_format = '@'
  7. # 设置为“自定义”格式中的“0.00”   比如：100 转换为100.00
  8. ws.cell(1, 4).number_format = '0.00'
  9. # 设置为“自定义”格式中的“0”
  10. ws.cell(1, 4).number_format = '0'
  11. # 一列设置“日期”格式
  12. for cell in ws["A"]:
  13.     cell.number_format = 'yyyy-mm-dd'

具体可参考：openpyxl.styles.numbers — openpyxl 3.1.2 documentation

设置日期格式

  1. from openpyxl.styles import numbers
  2.  
  3. ws['A1'] = datetime.date(2021, 2, 13)
  4. ws['A1'].number_format = 'yyyy/m/d'   
  5.  
  6. # 设置一列
  7. for cell in ws["A"]:
  8.     cell.number_format = numbers.FORMAT_DATE_YYYYMMDD2
  9.  
  10. # 在B列写入并设置日期格式
  11. dates = ["2023-11-29","2023-11-30","2023-12-01"]
  12. for i,date_str in enumerate(dates,start=1):
  13.     ws.cell(row=i,column=2,value=datetime.strptime(date_str,"%Y-%m-%d"))
  14.

没啥用的方法

根据数据得到字母，根据字母得到数字

  1. from openpyxl.utils import get_column_letter,column_index_from_string
  2. # 未知用处
  3. # 根据列的数字返回字母
  4. print(get_column_letter(2))  # B
  5. # 根据字母返回列的数字
  6. print(column_index_from_string("d"))  # 4

openpyxl案例：

 评论区自取文件

1、拆分表格

 将人员以各部门进行区分（J列）单独提取出来的信息（人员所有信息）插入新的sheet页

  1. import openpyxl
  2. wb = openpyxl.load_workbook(r'E:\课件\人员信息表待筛选.xlsx')
  3. sheetnames = wb.sheetnames   # 返回列表
  4.  
  5. department_list = []
  6. # 筛选部门
  7. for sheetname in sheetnames:
  8.     ws = wb[sheetname]
  9.     max_row = ws.max_row # 最大行数
  10.     # range不包含最后一位数
  11.     for row in range(2,max_row+1):
  12.         department = ws.cell(row,10).value
  13.         if "[" in department:
  14.             department = department.replace('[','_')
  15.         if "]" in department:
  16.             department = department.replace(']','')
  17.         if department not in department_list:
  18.             department_list.append(department)
  19. # 获取标题
  20. title = list(wb['Sheet1'].values)[0]
  21.  
  22. # 创建工作簿
  23. wb2 = openpyxl.load_workbook(r'E:\课件\建表测试.xlsx')
  24. for table in department_list:
  25.     # 创建工作簿
  26.     wb2.create_sheet(table)
  27.     # 添加标题
  28.     wb2[table].append(title)
  29.  
  30. # 拆分数据
  31. for sheetname in sheetnames:
  32.     ws = wb[sheetname]
  33.     max_row = ws.max_row
  34.     values = list(ws.values)
  35.     for row in range(2,max_row+1):
  36.         content = ws.cell(row,10).value
  37.         if "[" in content:
  38.             content = content.replace('[','_')
  39.         if "]" in content:
  40.             content = content.replace(']','')
  41.         wb2[content].append(values[row-1])
  42. wb2.save(r'E:\课件\建表测试.xlsx')

2、提取信息

将工作证（O列）包含字母的证件号人员信息单独提取插入新的sheet页

  1. import string
  2. import openpyxl
  3. wb = openpyxl.load_workbook(r'D:\星越\办公自动化课件\人员信息表.xlsx')
  4. # 当前工作簿中创建工作表
  5. wb_card = wb.create_sheet("工作证")
  6. # print(wb.sheetnames)
  7. # 添加标题
  8. wb_card.append(list(wb['Sheet1'].values)[0])
  9. # 循环所有表
  10. for sheetname in wb.sheetnames:
  11.     # 该表所有值
  12.     value = list(wb[sheetname].values)
  13.     # 最大行号
  14.     max_row = wb[sheetname].max_row
  15.     # range不包含max_row得+1
  16.     for row in range(2,max_row+1):
  17.         cell = str(wb[sheetname].cell(row, 15).value)
  18.         # 循环获得每一个字符
  19.         for letter in cell:
  20.             # 得到的字符判断是否包含英文，包含添加并跳出循环
  21.             if letter in string.ascii_lowercase:
  22.                 # value为列表，当row为2时，需要取得是value的1，所以需减1
  23.                 wb_card.append(value[row-1])
  24.                 # 当循环遇到字母就跳出
  25.                 break
  26. wb.save(r'D:\星越\办公自动化课件\人员信息表.xlsx')

3、查找字符串

多层级查找包含杨幂字符串的excel表格返回路径和单元格（openpyxl只支持xlsx,xlsm)

  1. import glob
  2. import openpyxl
  3. path = r"C:\Users\Administrator\Desktop\办公自动化"
  4. for file in glob.glob(path + "/**",recursive=True):
  5.     suffix = file.split('.')[-1]
  6.     if suffix == 'xlsx' or suffix == 'xlsm':
  7.         wb = openpyxl.load_workbook(file)
  8.         # 获取所有表
  9.         for sh in wb.sheetnames:
  10.             max_row = wb[sh].max_row # 最大行数
  11.             max_col = wb[sh].max_column # 最大列数
  12.             values = list(wb[sh].values) # 获取该表所有值,返回list
  13.             # 方法一
  14.             # if "杨幂" in str(values):
  15.             #     print(file)
  16.             # 方法二
  17.             # 循环每行
  18.             for i in range(1,max_row+1):
  19.                 for j in range(1,max_col+1):
  20.                     # 获取单元格的值
  21.                     value = wb[sh].cell(i, j).value
  22.                     if "杨幂" in str(value):
  23.                         print('{}.cell({},{})；路径：{}'.format(sh,i,j,file))
  24.         wb.close() # 必须关闭

4、汇总

汇总表仅有5行标题，多表数据写入汇总表

  1. import openpyxl
  2. wb = openpyxl.load_workbook('汇总数据.xlsx')
  3.  
  4. for sheet in wb:
  5.     sh=wb[sheet.title]
  6.     max_row_B = max((b.row for b in sh['B'] if b.value))
  7.     for i in range(5,max_row_B+1):
  8.         l = [] # 存储行数据
  9.         if sh.cell(i,2).value is None and sh.cell(i,3).value is None:
  10.             continue
  11.         else:
  12.             for j in range(1,23):
  13.                 data = sh.cell(i,j).value
  14.                 l.append(str(data))
  15.         wb['汇总'].append(l)
  16.  
  17. wb.save('text.xlsx')
  18. wb.close()

xlwings操作Excel

pip install xlwings

打开Excel程序

使用app打开Excel进程

  1. import xlwings as xw
  2. app = xw.App(visible=True,add_book=False)  # 程序可见，只打开不新建工作簿
  3. app.display_alerts = False # 警告关闭
  4. app.screen_updating = False # 屏幕更新关闭

保存、退出、关闭

xlwings依赖于(app)Excel进程，结束需要关闭(app)

  1. import xlwings as xw
  2. app = xw.App()  
  3. wb = app.books.open(r'E:\测试表.xlsx')
  4. wb.save(r'E:\测试表.xlsx')  # 保存文件
  5. wb.close()                 # 关闭文件
  6. app.quit()                 # 关闭程序

新建Excel文件

  1. wb = app.books.add()
  2. wb.save((r'E:\新建工作簿.xlsx') 
  3. wb.close() 
  4. app.quit()

获取表

  1. for s in wb.sheets:
  2.     print(wb.sheets[s])

读取内容

方法一

  1. # 获取单个单元格的值
  2. A1 = sh.range("A1").value
  3. print(A1)
  4. # 获取横向或纵向多个单元格的值，返回列表
  5. A1_A3 = sh.range("A1:A3").value
  6. print(A1_A3)
  7. # 获取指定范围的值，返回嵌套列表，按行为列表
  8. A1_B3 = sh.range("A1:B3").value
  9. print(A1_B3)

方法二

  1. # 获取单个单元格的值
  2. A1 = sh["A1"].value
  3. print(A1)
  4. # 获取横向或纵向多个单元格的值，返回列表
  5. A1_A3 = sh["A1:A3"].value
  6. print(A1_A3)
  7. # 获取指定范围的值，返回嵌套列表，按行为列表
  8. A1_B3 = sh["A1:B3"].value
  9. print(A1_B3)

方法三：

  1. A1_B2 = sh[:2,:2].value
  2. print(A1_B2)

写入数据

  1. # 写入1个单元格
  2. sh.range("A5").value = "杨迪"
  3. # 横向写入A6:B6
  4. sh.range('A6').value = ['杨洋',32]
  5. # 纵向写入C2:C5
  6. sh.range("C2").options(transpose=True).value = ['歌手','演员','演员','搞笑艺人']
  7. # 写入范围内A7:C8
  8. sh.range("A7").options(expand="table").value=[[1,2,3],[4,5,6]]

获取数据范围

  1. shape = sh.used_range.shape
  2. print(shape)
  1. # 最大行数列数
  2. max_row = wb.sheets[s].used_range.last_cell.row
  3. max_col = wb.sheets[s].used_range.last_cell.column

输出并修改行高列宽

  1. # 输出
  2. print(sh.range("A1:A2").row_height)
  3. print(sh.range("A1:A2").column_width)
  4. # 修改
  5. sh.range("A1:A2").row_height = 30
  6. sh.range('A1:A2').column_width = 50

获取及设置公式

可以调用Excel公式

  1. # 获取公式
  2. print(sh.range("B7").formula_array)
  3. # 写入公式
  4. sh.range("F2").formula = "=VLOOKUP(E2,A:B,2,0)"

获取、设置及清除颜色格式

  1. # 获取颜色
  2. print(sh.range("A1:A7").color)
  3. # 设置颜色
  4. sh.range("C:C").color = (255,0,120)
  5. # 清除颜色
  6. sh.range("E2:F2").color = None

xlrd读取Excel

注意：xlrd索引从0开始，仅读取，不可写入。

新版本只支持xls文件，版本降级支持(xlsx和xls)：pip install xlrd==1.2.0

pip install Xlrd # 安装

打开Excel文件

  1. import xlrd
  2. file = r"D:\demo\A.xlsx"
  3. df = xlrd.open_workbook(file)

查看工作簿中所有工作表

df.sheet_names()

指定工作表三种方式

  1. table = df.sheets()[0]
  2. table = df.sheet_by_index(0)
  3. table = df.sheet_by_name("工作表1")

获取表格最大行数和列数

  1. table.nrows
  2. table.ncols

获取整行或整列的值

  1. table.row_values(0)  # 获取第一行
  2. table.col_values(0)  # 获取第一列

循环读取所有行

  1. for i in range(table.nrows):
  2.     print(table.row_values(i))  # 返回整行

表操作(待试验)

ds.row_values(0,6,10)      #取第一行，第6-9列
ds.col_values(0,0,5)         #取第一列，第0-4行
ds.row_slice(2,0,2)           #获取单元格值类型和内容，同sheet1.row()
sheet1.row_types(1,0,2)   #获取单元格数据类型
 

table.row_values(0,6

获取单元格的值

  1. print(table.cell_value(3,0))  # 第四行第一列的值
  2. print(table.row_values(1,0))  # 第二行第一列的值
  3. # col_values(self,colx,start_rowx,end_rowx)
  4. print(table.col_values(0,0))  # 第一列的值

xlrd案例

1、同层级文件夹中查找字符串

在单元格中找含有杨幂的字符串

  1. # 同层级，遍历所有表格内的所有表的所有单元格包含字符串的返回路径和该行单元格值
  2. import glob
  3. import xlrd
  4. path = r"D:\demo\一层级"
  5. for file in (glob.glob(path+"/*/*"):
  6.     # 获取文件后缀
  7.     file_suffix = file.split(".")[-1]
  8.     # 后缀名为xls
  9.     if file_suffix == "xls" or file_suffix == "xlsx": # xlrd修改版本后可操作xlsx文件
  10.         # 读取Excel文件
  11.         df = xlrd.open_workbook(file)
  12.         # 获取表格内所有表名
  13.         for sheet in df.sheet_names():
  14.             # 表名
  15.             table = df.sheet_by_name(sheet)
  16.             # 最大行列数
  17.             max_row = table.nrows
  18.             max_col = table.ncols
  19.             # xlrd索引从0开始
  20.             for i in range(max_row):
  21.                 # 循环某表每行的值
  22.                 for h in table.row_values(i):
  23.                     # 查找含有杨幂字符串的单元格
  24.                     if "杨幂" in str(h):
  25.                         print(f"路径：{file},{h}")

1、pip install xlrd==1.2.0   #此版本支持xlsx和xls

2、如果报错:'ElementTree' object has no attribute 'getiterator' ,则需要进行如下更改：

3、先pip show xlrd根据Location找到并进入xlrd文件夹，打开xlsx.py

4、然后查询里面的 getiterator()，把里面两处getiterator替换为iter 即可。

2、多层级文件夹中查找

使用glob的recursive=True实现多层级查找

  1. import glob
  2. import xlrd
  3. path = r"D:\demo\层级"
  4. for file in glob.glob(path + "/**",recursive=True):
  5.     # 获取文件后缀
  6.     file_suffix = file.split(".")[-1]
  7.     '''
  8.     同层级相同步骤
  9.     '''

 3、匹配数据

类似VLOOKUP，但是查找整个文件夹的文件

  1. import glob
  2. import xlrd
  3. import openpyxl
  4. # openpyxl 从1开始，xlrd从0开始
  5. path = r"查找的文件夹路径"
  6. match_path = r"待匹配的数据表.xlsx"
  7. data = openpyxl.load_workbook(match_path)
  8. match_table = data['Sheet1']
  9. Match_max_row = match_table.max_row #待匹配表的最大行号
  10. # for m in range(2, Match_max_row + 1) 如果待匹配的表格数量小放在此处
  11. for file in glob.glob(path+"/*"):
  12.     # 获取文件后缀
  13.     file_suffix = file.split(".")[-1]
  14.     # 后缀名为xls
  15.     if file_suffix == "xls" or file_suffix == "xlsx":
  16.         # 读取Excel文件
  17.         df = xlrd.open_workbook(file)
  18.         # 获取表格内所有表名
  19.         for sheet in df.sheet_names():
  20.             # 表名
  21.             table = df.sheet_by_name(sheet)
  22.             # 最大行列数
  23.             max_row = table.nrows
  24.             max_col = table.ncols
  25.             # xlrd索引从0开始
  26.             for i in range(1,max_row+1):
  27.                 try:
  28.                     # 循环某表每行的值
  29.                     for h in table.row_values(i):
  30.                         # 循环待匹配表的每行
  31.                         for m in range(2, Match_max_row + 1):
  32.                         # for m in range(2, Match_max_row + 1)如果待匹配的表数量大放在此处
  33.                             # 判断是否存在
  34.                             if str(match_table.cell(m, 7).value) in str(h):
  35.                                 # 存在写入表名
  36.                                 match_table.cell(m, 8).value = sheet
  37.                                 continue
  38.                 except:
  39.                     continue
  40. data.save(r'待匹配的数据表(副本).xlsx')
  41. data.close()

4、汇总多个excel

  1. import os
  2. import openpyxl
  3. import datetime
  4. import xlrd
  5. date = datetime.datetime.now().date()
  6. Year = date.year
  7. Month = date.month
  8. # 待汇总的表
  9. wb = openpyxl.load_workbook(路径)
  10. ws = wb['汇总']
  11.  
  12. path = r"C:\Users\HP\Desktop\Text"
  13. # 多层级搜索
  14. for file in os.scandir(path):
  15.     # 是文件夹
  16.     if file.is_dir():
  17.         # 读取文件夹
  18.         for f in os.scandir(file.path):
  19.             # 如果文件名包含“ xxx.xlsx”
  20.             if "text.xlsx" in f.name:
  21.                 # 打开excel文件（文件路径+文件名）
  22.                 sheets = xlrd.open_workbook(file.path + "/" + f.name)
  23.                 # sheets.sheet_by_index(0) 根据索引获取表
  24.                 # 指定读取excel工作表
  25.                 sheet = sheets.sheet_by_name("汇总")
  26.                 # 获取最大的行号
  27.                 rows = sheet.nrows
  28.                 # 获取最大的列号
  29.                 cols = sheet.ncols
  30.                 # 除去表头，开始汇总的行数
  31.                 startrow = 1   # 从第二行开始
  32.                 for row in range(startrow,rows):
  33.                     l = sheet.row_values(row)
  34.                     ws.append(l)
  35.     wb.save(路径)

5、复制工作簿

  1. import xlrd
  2. import openpyxl
  3. wb = xlrd.open_workbook("原表.xlsx")
  4. ws = wb.sheet_by_name("2024")  # 待复制的工作表
  5. maxrow = ws.nrows
  6.  
  7. df = openpyxl.load_workbook(r"新表.xlsx")
  8. ds = df.create_sheet("2024年-copy",0)  # 创建表名
  9. for i in range(0,maxrow):
  10.     ds.append(ws.row_values(i))  # ws.row_values(i)整行 ，整行数据追加到新表
  11.  
  12. df.save("新表.xlsx")

xls转xlsx

  1. import win32com.client as win32
  2.  
  3. fname = r"C:\Users\HP\Desktop\text.xlsx"
  4. excel = win32.gencache.EnsureDispatch('Excel.Application')
  5. wb = excel.Workbooks.Open(fname)
  6.  
  7. ## 转换为xls
  8. # xls_f = fname.split('.')[0]+"xls"
  9. # wb.SaveAs(xls_f, FileFormat=56)
  10.  
  11. wb.SaveAs(fname + "x", FileFormat=51)    #FileFormat = 51 is for .xlsx extension
  12. wb.Close()                               #FileFormat = 56 is for .xls extension
  13. excel.Application.Quit()

知识扩展：glog()通配符搜索

python glob通配符方式单层搜索文件/文件夹_Bianca427的博客-CSDN博客

持续更新中

部分来源：

精心整理了40个Python办公自动化真实案例，一口一个，高效办公！_刘早起的博客-CSDN博客

本文内容由网友自发贡献，转载请注明出处：https://www.wpsshop.cn/w/神奇cpp/article/detail/804806
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号