Python让Excel飞起来—批量处理行、列和单元格

目录

案例01 精确调整多个工作簿的行高和列宽  

举一反三 精确调整一个工作簿中所有工作表的行高和列宽

案例02 批量更改多个工作簿的数据格式

举一反三 批量更改多个工作簿的外观格式

案例03 批量替换多个工作簿的行数据

举一反三 批量替换多个工作簿中的单元格数据

举一反三 批量修改多个工作簿中指定工作表的列数据

案例04 批量提取一个工作簿中所有工作表的特定数据

举一反三 批量提取一个工作簿中所有工作表的列数据

举一反三 在多个工作簿的指定工作表中批量追加行数据

案例05 对多个工作簿中指定工作表的数据进行分列

举一反三 批量合并多个工作簿中指定工作表的列数据

举一反三 将多个工作簿中指定工作表的列数据拆分为多行

案例06 批量提取一个工作簿中所有工作表的唯一 值

举一反三 批量提取一个工作簿中所有工作表的唯一值并汇总

---

案例01 精确调整多个工作簿的行高和列宽  

• 代码文件：精确调整多个工作簿的行高和列宽.py
• 数据文件：销售表（文件夹）    

 除了前面讲解的工作簿和工作表的批量操作，Python还可以对工作表中的行、列和单元格等元素进行批量设置。例如，要调整行高和列宽，可以使用xlwings模块的 column_width 和 row_height属性，再加上 for 语句，就可以实现批量调整了。

调整为:

 代码如下：

import os
import xlwings as xw
file_path=r'C:\Users\Administrator\Desktop\22\销售表'
file_list=os.listdir(file_path)
app=xw.App(visible=False)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths=os.path.join(file_path,i)
    workbook=app.books.open(file_paths)
    for j in workbook.sheets:
        value=j.range('A1').expand()  #在工作表中选定要调整行高列宽的区域
        value.column_width=12   #将列宽调整为可容纳12个字符的宽度
        value.row_height=20  #将行高调整为20磅
    workbook.save()
    workbook.close()
app.quit()

知识延伸

第 13 行代码中的 column_width 是 xlwings模块中用于获取和设置列宽的属性。列宽的单位是字符数，取值范围是 0 ～255。这里的字符指的是英文字符，如果字符的字体是比例字体（每个字符的宽度不同），则以字符0的宽度为准。在用该属性设置列宽时，为该属性赋值即可。在用该属性获取列宽时，如果所选单元格区域中各列的列宽不同，则根据所选单元格区域位于包含数据单元格区域的内部或外部分别返回 None或第一列的列宽。

第 14 行代码中的 row_height 是 xlwings模块中用于获取和设置行高的属性。行高的单位是磅，取值范围是 0～409.5。在用该属性设置行高时，为该属性赋值即可。在用该属性获取行高时，如果所选单元格区域中各列的行高不同，则根据所选单元格区域位于包含数据单元格区域的内部或外部分别返回 None 或第一行的行高

举一反三 精确调整一个工作簿中所有工作表的行高和列宽

• 代码文件：精确调整一个工作簿中所有工作表的行高和列宽.py
• 数据文件：采购表.xlsx

import xlwings as xw
app=xw.App(visible=False)
workbook=app.books.open(r'C:\Users\Administrator\Desktop\22\采购表.xlsx')
for i in workbook.sheets:
    value=i.range('A1').expand()
    value.column_width=12   #将列宽调整为可容纳12个字符的宽度
    value.row_height=20  #将行高调整为20磅
workbook.save()
workbook.close()
app.quit()

案例02 批量更改多个工作簿的数据格式

• 代码文件：批量更改多个工作簿的数据格式.py
• 数据文件：采购表（文件夹）

用 Excel 的功能将下左图中 A 列和 D列的数据格式更改为下图的效果。

import os
import xlwings as xw
file_path=r'C:\Users\Administrator\Desktop\22\采购表'
file_list=os.listdir(file_path)
app=xw.App(visible=False)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths=os.path.join(file_path,i)
    workbook=app.books.open(file_paths)
    for j in workbook.sheets:
        row_num=j['A1'].current_region.last_cell.row  # 获取工作表中数据区域最后一行
        j['A2:A{}'.format(row_num)].number_format='m/d'  # 将A列的“采购日期”数据全部更改为“月/日
        j['D2:D{}'.format(row_num)].number_format='¥#,##0.00' # 将D列的“采购金额”数据全部更改为带货币符号并保留2位小数
    workbook.save()
    workbook.close()
app.quit()

代码解析

第 6 ～ 16 行代码用于逐个打开文件夹 “ 采购表 ”中的工作簿，完成所需的批量操作后保存并关闭。其中第 11 ～14行代码是实现批量操作的核心部分：在打开的工作簿中逐个选择工作表，并设置指定列的数据格式。第 13 行和第 14 行代码中的 A 列和 D列可以根据实际需求修改为其他列，数据格式 “m/d” 和 “#,##0.00” 也可以根据实际需求修改为其他格式。

知识延伸

• 第13行和第14行代码使用了第4章案例08中介绍的format()函数来拼接代表单元格区域的字符串。以'A2:A{}'.format(row_num)为例，假设当前工作表中数据区域最后一行的行号row_num为16，则'A2:A{}'.format(row_num)就相当于'A2:A16'，代表单元格区域A2:A16。
• 拼接出单元格区域后，就可以使用xlwings模块中的number_format属性来设置单元格区域中数据的格式。该属性的取值为一个代表特定格式的字符串，与Excel的“设置单元格格式”对话框中“数字”选项卡下设置的格式对应。