python遍历sheet并将处理结果存储到当前工作表中

1、背景

刚开始的时候，领导给了我一张excel表，她说每周都不定期更新，每次更新都是复制粘贴，不但容易出错，而且要花费2个多小时，然后她问我，你在下班时间可以考虑怎么用脚本如python，或VBA把这个东西做批量化处理，提高一下工作效率，我说好，然后就开始构思怎么去处理这样一种格式的数据，捡起我3个月都没写脚本的热情，就展开了。刚开始也没想着一下就能完全实现，一步一步来，快一点是一点，然后写了一个不太完美的初级版本，后面在别人的刺激下对脚本进行了改善，完全做到了自动化脚本，目前已经正常无误运转了3次，打算再测4次，后面就正式投入使用了，哈哈！！！！

2、需求实现

大致的需求如下图，就是如何把原始的数据格式变成目标格式（实际上就是把区域1,2,3根据字段名做一个纵向连接），大家可以先思考一下如何去做，下面一一为大家揭晓：
原始数据：

目标样式：

相信有很多小伙伴们都能想到，就是先把每一个汇报人所属区域的起始位置和区域间隔识别出来，然后结合for用concat做个纵向拼接，不就ok了么，能想到这一步，那就离成功不远了，哈哈，下面将逐步分析怎么去是实现这个功能，即如何实现这个功能呢？？？？。

3、分析及代码实现

（1）废话不多说，先把文件读进来再说；

（2）获取区域的起始位置：大家有没有发现，1,2,3（第一行合并单元格的数据）在excel中的数据表现和python不一致，excel占据了多个单元格，python只占用了一个单元格。从python输出来看，第一行中不为nan的数据是不是就是每一个区域的开始位置对应的值，那么是不是可以使用argwhere函数来获取它所对应的索引，也就是位置呢，下面继续，这里注意不加astype将其转化成float类型会报错，因为isnan只适用于float类型；

（3）得到区域间隔：区域间隔，不就是后项减去前项吗，这么简单的操作，构造一个新列就行，哈哈；确定了每个区域的起始位置和间隔，用个循环是不是就搞定了呢？？？

（4）for循环遍历区域：这里有几个注意的小点，说明一下：
1、from numpy import * 是为了使用tile函数，方便对第一列进行填充；
2、注意iloc每次取数的位置
3、为什么要使用dropna函数？？？这个读者可以自己去探索，去掉和不去掉有什么区别
4、concat的时候记得吧df重置索引，也就是用reset_index函数，

（5）如果是普通需求，将empty_df的数据直接用to_csv、to_excel、to_sql写入就行，是不是就ok了，哈哈，大功告成！！！

附加想法

（1）如果要遍历所有sheet，怎么弄？？

file_path = "D:\\test.xlsx"  # 获取文件路径
    
file = pd.ExcelFile(file_path)
sh_names = file.sheet_names  # 获取sheet名称
    
# 写个循环遍历所有sheet
for sh_name in sh_names:
    raw_data = pd.read_excel(file_path, sheet_name = sh_name, header = None)

（2）如何将数据写入到原始的工作表中，并且不覆盖原来的数据，怎么操作（在实际工作中其实是有这种需求的，即原始表给我留着，过程表我也要，结果表我也要，统一放在一个工作簿中）？？反正直接使用to_excel是行不通的，往下看，不加engine='openpyxl’参数就会覆盖原表，不知道为啥，有兴趣的可以去探索一下：

# 将数据写入到excel文件中，过程表，在写入的时候不覆盖原表
writer = pd.ExcelWriter(file_path,engine='openpyxl')  # 方便存储原表
book = load_workbook(file_path)
writer.book = book
empty_df.to_excel(excel_writer = writer, sheet_name = '过程表')
writer.save()
writer.close()

源代码

# -*- coding: utf-8 -*-
"""
遍历整个excel工作簿，对所有sheet的每个区域进行同样操作，实际上就是并到一块，做自动化处理
This is a temporary script file.
"""

import pandas as pd
from numpy import *
import numpy as np
from openpyxl import load_workbook

'''遍历sheet，对不同区域数据进行拼接'''
def deal_data(file_path, sh_name, empty_df):
    
    raw_data = pd.read_excel(file_path, sheet_name = sh_name, header = None)
    No1_row = np.array(raw_data.iloc[0, :]).astype(float) # 获取第一行的数据，方便按照区域进行划分，不加astype会报错，isnan只适用于float类型
    
    # 得到起始位置
    No1_index = np.ravel(np.argwhere(~np.isnan(No1_row)))  # 获取第一行的索引，方便进行区域划分
    # 求出间隔
    col_num = raw_data.shape[1]
    No1_index_mk = np.append(No1_index[1:], col_num) # 构造列，实现错位相减
    interval = No1_index_mk - No1_index  # 每次取数的间隔，也就是步长
    
    user_col_names= ['序号']
    
    # 将每个区域的数据拼接
    length = len(No1_index)
    for i in range(0, length):
    
        begin_position = No1_index[i]
        begin_interval = interval[i]
        # 获取每一块区域的数据
        part_data = raw_data.iloc[:, begin_position:begin_position + begin_interval]
        data = part_data.dropna(how="all")  # 去重掉该区域的空值
      
        # 获取列名
        input_col_names = np.array(data.iloc[1, 0:begin_interval])
        # 获取实际信息
        input_info = data.iloc[2:, :].reset_index(drop=True)
        input_info.columns = input_col_names
        row_num = len(input_info)  # 获取行数，方便进行填充      
        # 获取序号信息并填充
        user_info = np.array(data.iloc[0:1, 0])   # 获取投放用户信息
        user_info_fill = pd.DataFrame(tile(user_info,(row_num,1)), columns = user_col_names).reset_index(drop=True)
        
        # 横向拼接
        data_1 = pd.concat([user_info_fill, input_info], axis = 1)
       
        # 纵向拼接
        empty_df = pd.concat([empty_df, data_1], axis = 0, sort = False)
        
    return empty_df
    

if __name__ == '__main__':
    
    file_path = "D:\\test.xlsx"  # 获取文件路径
    
    file = pd.ExcelFile(file_path)
    sh_names = file.sheet_names  # 获取sheet名称
    empty_df = pd.DataFrame()  # 创建一个空df，用来存储数据
    
    # 将多个sheet中的数据整合到一个sheet中
    for sh_name in sh_names:
        empty_df = deal_data(file_path, sh_name, empty_df)
    
    
    # 将数据写入到excel文件中，过程表，在写入的时候不覆盖原表
    writer = pd.ExcelWriter(file_path,engine='openpyxl')  # 方便存储原表
    book = load_workbook(file_path)
    writer.book = book
    empty_df.to_excel(excel_writer = writer, sheet_name = '过程表')
    writer.save()
    writer.close()