4 python数据分析基础——批量处理行、列和单元格

import os
import xlwings as xw

file_path='e:\\table\\销售表'         # 给出工作簿所在的文件夹路径
file_list = os.listdir(file_path)    # 列出文件夹下所有文件和子文件夹的名称
# print(file_list)
app = xw.App(visible = False, add_book = False)    # 启动Excel程序

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('table')  # 在工作表中选择要调整行高和列宽的单元格区域
        value.column_width = 12     # 将列宽调整为可容纳12个字符的宽度
        value.row_height = 20       # 将行高调整为20磅
    workbook.save()
    workbook.close()
app.quit()

2、精确调整一个工作簿中所有工作表的行高和列宽

import xlwings as xw

app = xw.App(visible = False, add_book = False)
workbook = app.books.open('e:\\table\\采购表.xlsx')

for i in workbook.sheets:
    value = i.range('A1').expand('table')
    value.column_width = 12
    value.row_height = 20
workbook.save()
app.quit()

二、批量更改多个工作簿的数据格式

1、批量更改多个工作簿的数据格式

import os
import xlwings as xw
file_path = '采购表'
file_list = os.listdir(file_path)
app = xw.App(visible = False, add_book = 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'          # 更改为日期数字格式
        j['D2:D{}'.format(row_num)].number_format = '¥#,##0.00'   # 更改为货币数字格式

    workbook.save()
    workbook.close()
app.quit()

2、批量更改多个工作簿的外观格式

import os
import xlwings as xw
file_path = '销售表'
file_list = os.listdir(file_path)
app = xw.App(visible = False, add_book = 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: 
        j['A1:H1'].api.Font.Name = '宋体'  
        j['A1:H1'].api.Font.Size = 10  
        j['A1:H1'].api.Font.Bold = True		   # 加粗工作表标题行
        j['A1:H1'].api.Font.Color = xw.utils.rgb_to_int((255,255,255))    # 设置工作表标题行的字体颜色为“白色”
        j['A1:H1'].color = xw.utils.rgb_to_int((0,0,0))                   # 设置工作表标题行的单元格填充颜色为“黑色”
        j['A1:H1'].api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter     # 设置工作表标题行的水平对齐方式为“居中”
        j['A1:H1'].api.VerticalAlignment = xw.constants.VAlign.xlVAlignCenter       # 设置工作表标题行的垂直对齐方式为“居中”

        j['A2'].expand('table').api.Font.Name = '宋体'     # 设置工作表的正文字体为“宋体”
        j['A2'].expand('table').api.Font.Size = 10        # 设置工作表的正文字体的字号为“10”磅
        j['A2'].expand('table').api.HorizontalAlignment = xw.constants.HAlign.xlHAlignLeft     # 设置工作表正文的水平对齐方式为“靠左”
        j['A2'].expand('table').api.VerticalAlignment = xw.constants.VAlign.xlVAlignCenter     # 设置工作表正文的垂直对齐方式为“居中”

        for cell in j['A1'].expand('table'):       # 从单元格A1开始为工作表添加合适粗细的边框
            for b in range(7,12): 
                cell.api.Borders(b).LineStyle = 1      # 设置单元格的边框线型
                cell.api.Borders(b).Weight = 2         # 设置单元格的边框粗细

    workbook.save()
    workbook.close()
app.quit()

三、批量替换多个工作簿的行数据

1、批量替换多个工作簿的行数据

import os
import xlwings as xw

file_path = '分部信息'
file_list = os.listdir(file_path)
app = xw.App(visible = False, add_book = 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['A2'].expand('table').value   # 读取工作表数据
        for index, val in enumerate(value):     # 按行遍历工作表数据
            if val == ['背包', 16, 65]:         # 判断行数据是否为“背包”
                value[index] = ['双肩包', 36, 79]    # 如果是，则将该行数据替换为新的数据
        j['A2'].expand('table').value = value   # 将完成替换的数据写入工作表

    workbook.save()
    workbook.close()
app.quit()

2、批量替换多个工作簿中的单元格数据

import os
import xlwings as xw
file_path = '分部信息'
file_list = os.listdir(file_path)
app = xw.App(visible = False, add_book = 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['A2'].expand('table').value    # 读取工作表数据
        for index, val in enumerate(value):      # 按行遍历工作表数据
            if val[0] == '背包': 
                val[0] = '双肩包' 
                value[index] = val     # 替换整行数据
        j['A2'].expand('table').value = value    # 将完成替换的数据写入工作表

    workbook.save()
    workbook.close()
app.quit()

3、批量修改多个工作簿中指定工作表的列数据

import os
import xlwings as xw
file_path = '分部信息'
file_list = os.listdir(file_path)
app = xw.App(visible = False, add_book = False)

for i in file_list:
    if i.startswith('~$'):
        continue
        
    file_paths = os.path.join(file_path, i)
    workbook = app.books.open(file_paths)
    worksheet = workbook.sheets['产品分类表']   # 指定要修改的工作表
    value = worksheet['A2'].expand('table').value 
    
    for index, val in enumerate(value): 
        val[2] = val[2] * (1 + 0.05)    # 修改第3个单元格的数据，这里将销售价上调5%
        value[index] = val    # 替换整行数据
    worksheet['A2'].expand('table').value = value   # 将完成替换的数据写入工作表
    
    workbook.save()
    workbook.close()
app.quit()

四、批量提取一个工作簿中所有工作表的特定数据

1、批量提取一个工作簿中所有工作表的特定数据

import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False) 
workbook = app.books.open('采购表.xlsx')  
worksheet = workbook.sheets   # 打开工作簿中的所有工作表

# 创建一个空列表用于存放数据
data = []

for i in worksheet:   # 遍历工作簿中的工作表
    values = i.range('A1').expand().options(pd.DataFrame).value   # 读取当前工作表的所有数据
    filtered = values[values['采购物品'] == '复印纸']     # 提取“采购物品”为“复印纸”的行数据
    if not filtered.empty:        # 判断提取出的行数据是否为空
        data.append(filtered)     # 将提取出的行数据追加到列表之中

new_workbook = xw.books.add()    # 新建工作簿
new_worksheet = new_workbook.sheets.add('复印纸')    # 在新工作簿中新增一个名为“复印纸”的工作表
new_worksheet.range('A1').value = pd.concat(data, ignore_index = False)    # 将提取出的行数据写入工作表“复印纸”中
new_workbook.save('复印纸.xlsx')   # 保存新工作簿并命名为“复印纸.xlsx”

workbook.close()
app.quit()

2、批量提取一个工作簿中所有工作表的列数据

import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False) 
workbook = app.books.open('采购表.xlsx')
worksheet = workbook.sheets 

column = ['采购日期', '采购金额']   # 指定要提取的列的列标题
data = []

for i in worksheet:
    values = i.range('A1').expand().options(pd.DataFrame, index = False).value
    filtered = values[column]     # 根据前面指定的列标题提取数据
    data.append(filtered)
    
new_workbook = xw.books.add()       # 新建工作簿
new_worksheet = new_workbook.sheets.add('提取数据') 
new_worksheet.range('A1').value = pd.concat(data, ignore_index = False).set_index(column[0])
new_workbook.save('提取表.xlsx') 
workbook.close()
app.quit()

3、在多个工作簿的指定工作表中批量追加行数据

import os
import xlwings as xw

newContent = [['双肩包', '64', '110'], ['腰包', '23', '58']]   # 给出要追加的行数据
app = xw.apps.add()
file_path = '分部信息'  
file_list = os.listdir(file_path)  

for i in file_list:  
    if os.path.splitext(i)[1] == '.xlsx':  
        workbook = app.books.open(file_path + '\\' + i)  
        worksheet = workbook.sheets['产品分类表']     # 指定要追加行数据的工作表
        values = worksheet.range('A1').expand()      # 读取原有数据
        number = values.shape[0]                     # 获取原有数据的行数
        worksheet.range(number + 1, 1).value = newContent     # 将前面指定的行数据追加到原有数据的下方
        
        workbook.save()
        workbook.close()
app.quit()

五、批量拆分多个工作簿中指定工作表的列数据

1、对多个工作簿中指定工作表的数据进行分列

import os
import xlwings as xw
import pandas as pd

file_path = '产品记录表'
file_list = os.listdir(file_path)
app = xw.App(visible = False, add_book = False)

for i in file_list:
    if i.startswith('~$'):
        continue

    file_paths = os.path.join(file_path, i)   # 将文件夹路径和文件名拼接成工作簿的完整路径
    workbook = app.books.open(file_paths)     # 打开工作簿
    worksheet = workbook.sheets['规格表']     # 指定要处理的工作表
    values = worksheet.range('A1').options(pd.DataFrame, header = 1, index = False, expand = 'table').value   # 读取指定工作表中的数据
    
    new_values = values['规格'].str.split('*', expand = True)    # 根据*号拆分规格列
    values['长(mm)'] = new_values[0] 
    values['宽(mm)'] = new_values[1] 
    values['高(mm)'] = new_values[2] 
    values.drop(columns =['规格'], inplace = True)   # 删除规格列

    worksheet['A1'].options(index = False).value = values     # 用分列后的数据替换工作表中的原有数据
    worksheet.autofit()      # 根据数据内容自动调整工作表的行高和列宽
    workbook.save()
    workbook.close()
app.quit()

2、批量合并多个工作簿中指定工作表的列数据

import os
import xlwings as xw
import pandas as pd
file_path = '产品记录表'
file_list = os.listdir(file_path)
app = xw.App(visible = False, add_book = False)

for i in file_list:
    if i.startswith('~$'):
        continue   
    file_paths = os.path.join(file_path, i)
    workbook = app.books.open(file_paths)
    worksheet = workbook.sheets['规格表'] 
    values = worksheet.range('A1').options(pd.DataFrame, header = 1, index = False, expand = 'table').value 
    
    # 合并列数据
    values['规格'] =  values['长(mm)'].astype('str') + '*' + values['宽(mm)'].astype('str') + '*' + values['高(mm)'].astype('str') 
    values.drop(columns = ['长(mm)'], inplace = True) 
    values.drop(columns = ['宽(mm)'], inplace = True) 
    values.drop(columns = ['高(mm)'], inplace = True) 
    worksheet.clear()    # 清除工作表“规格表”中原有的数据
    worksheet['A1'].options(index = False).value = values    # 将处理好的数据写入工作表
    
    worksheet.autofit() 
    workbook.save()
    workbook.close()
app.quit()

3、将多个工作簿中指定工作表的列数据拆分为多行

import os
import xlwings as xw
import pandas as pd
file_path = '产品记录表'
file_list = os.listdir(file_path)
app = xw.App(visible = False, add_book = False)

for i in file_list:
    if i.startswith('~$'):
        continue
        
    file_paths = os.path.join(file_path, i)
    workbook = app.books.open(file_paths)
    worksheet = workbook.sheets['规格表'] 
    values = worksheet.range('A1').options(pd.DataFrame, header = 1, index = False, expand = 'table').value 
    new_values = values['规格'].str.split('*', expand=True) 
    values['长(mm)'] = new_values[0] 
    values['宽(mm)'] = new_values[1] 
    values['高(mm)'] = new_values[2] 
    values.drop(columns =['规格'], inplace = True) 
    
    values = values.T   # 转换数据的行列
    values.columns = values.iloc[0] 
    values.index.name = values.iloc[0].index.name 
    values.drop(values.iloc[0].index.name, inplace = True) 
    worksheet.clear()
    worksheet['A1'].value = values 
    
    worksheet.autofit() 
    workbook.save() 
    workbook.close() 
app.quit()

六、批量提取一个工作簿中所有工作表的唯一值

1、批量提取一个工作簿中所有工作表的唯一值

import xlwings as xw
app = xw.App(visible = True, add_book = False)
workbook = app.books.open('上半年销售统计表.xlsx')    # 打开指定工作簿

data = []    # 创建一个空列表用于存放书名数据

for i, worksheet in enumerate(workbook.sheets):    # 遍历工作簿的工作表
    values = worksheet['A2'].expand('down').value   # 提取当前工作表汇总的书名数据
    data = data + values    # 将提取出是书名数据添加到前面创建的列表中

data = list(set(data))           # 对列表中的书名进行去重操作
data.insert(0, '书名')           # 对去重后的书名数据前添加列标题“书名”
new_workbook = xw.books.add()    # 新建工作簿
new_worksheet = new_workbook.sheets.add('书名')   # 在新建的工作簿中新增一个名为“书名”的工作表
new_worksheet['A1'].options(transpose = True).value = data    # 将处理好的书名数据写入新工作表

new_worksheet.autofit()    # 根据数据内容自动调整新工作表的行高与列宽
new_workbook.save('书名.xlsx')
workbook.close()
app.quit()

2、批量提取一个工作簿中所有工作表的唯一值并汇总

import os
import xlwings as xw
app = xw.App(visible = True, add_book = False)
wb = app.books.open('上半年销售统计表.xlsx')

data = list()     # 创建一个空列表用于存放书名和销售的汇总数据
for i, sht in enumerate(wb.sheets):
    values = sht['A2'].expand('table').value
    data = data + values
    
sales = dict()               # 创建一个空字典用于存放书名和销售的汇总数据
for i in range(len(data)):   # 按行遍历书名和销售的明细数据
    name = data[i][0]        # 获取当前书名 
    sale = data[i][1]        # 获取当前销量
    if name not in sales:    # 判断字典中是否不存在当前书名
        sales[name] = sale   # 如果不存在，则在字典中添加此书名的销量记录
    else:
        sales[name] += sale   # 如果已存在，则计算此书名的累计销量
dictlist = list()

for key, value in sales.items():
    temp = [key, value]        # 列出书名和对应的累计销量
    dictlist.append(temp)
dictlist.insert(0, ['书名', '销量'])   # 在获取的数据前添加列标题“书名”和“销量”

new_workbook = xw.books.add()
new_worksheet = new_workbook.sheets.add('销售统计')
new_worksheet['A1'].value = dictlist
new_worksheet.autofit()
new_workbook.save('销售统计.xlsx')
wb.close()
app.quit()

参考书目：《超简单用python让Excel飞起来》

DAMO开发者矩阵

DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。

更多推荐

一只小鸭子，藏着多少电子元器件？从 Microduck 看一台AI机器人的“硬件底座”

DAMO开发者矩阵

谷粉学术课堂：AI+实验设计

DAMO开发者矩阵

VLA 微调实战指南：算法选型、数据采集与效果全景

视觉-语言-动作模型（VLA）的后训练正在形成一套清晰的工程范式。**算法上**，主流仍是行为克隆式 SFT：LoRA（rank 32）以约 1.4% 的可训参数追平全量微调（OpenVLA 实测 68.2% vs 69.7%），而"并行解码 + 动作分块 + 连续动作头"的解码侧改造收益甚至大于参数策略（OpenVLA-OFT 在 LIBERO 上 76.5%→97.1%、吞吐 ×26）；202