分享一個用python帶着原有格式拆分Excel表格到多張工作表的思路

#暑期創作大賽#

案例：需要把下面的表格，在不破壞原有格式，帶着原有格式按門店拆分到多張工作表。

要帶着原有格式拆分這張表，用pandas是沒戲了，隻能用xlwings庫，批量建工作表，先把标題用代碼複制粘貼進去，然後用算法，周遊門店這列，删選出每個門店行的有效範圍，在通過篩選出來的結果，用xlwings庫的代碼整行複制到建立的表格内。思路有點像VBA，後來發現，小表還行，大表拆分因為要周遊表，運作速度感人，而且要拆分的資料行還在一起還好弄，像上面的表有的都不在一起，寫算法太麻煩，一路的循環判斷。

後來想到了用pandas輔助xlwings來判斷每個門店行的所在位置，在用xlwings整行複制到新表。

說幹就幹，第一步先用xlwings打開這張工作表，做個測試，先拆分一号店的資料。

import pandas as pd
import xlwings as xl
filepath=r"D:\樣表.xlsx"
wb=xl.Book(filepath)
ws=wb.sheets('Sheet1')           

第二步看下要用那列為依據來拆分工作表，用pandas讀取這列

df=pd.DataFrame(ws.range('B4:B26').value,columns=['門店'])
#因為要以門店這列為依據拆分工作表，門店這一列有效資料在'B4:B26'這個範圍，
#用xlwings讀取這範圍單元格資料，并交給pandas，并建構一個叫門店的資料。
print(df)
#輸出結果如下圖           

看下輸出結果，合并的單元格都被拆分了，沒有資料的内容都被填充了None。用pandas填充缺失值的功能進行填充

df['門店'].fillna(method='ffill',inplace=True)
print(df)
#           

到了重點了，看下上面的輸出，pandas資料的行索引，和Excel表格的行索引，有什麼關聯，要拆分的工作表表頭有3行，pandas第一行索引是0，excel第一行索引是1，是以他們相差1，是以上面的輸出的資料，每個索引的值加3在加1就是在Excel中所在行的索引。

在看一下上面輸出，想一下，假如我要把一号店拆分出去，我隻要把一号店篩選出來，他的索引加3再加1不就是要複制到新表的行嗎！試過後不行，能複制過去但是合并單元格被拆分開來了，最後是通過用pandas擷取一号店的資料起始行和結束行，實作的。

先取得，一号店索引的範圍。

screen=df[df['門店']=='一号店']
screen.groupby(screen.index.to_series().diff().ne(1).cumsum()).groups

#輸出結果
{1: [0, 1, 2, 3, 4], 2: [19, 20, 21, 22]}           

再取得每個範圍的最小值和最大值。

for v in screen.groupby(screen.index.to_series().diff().ne(1).cumsum()).groups.values():
    print(min(v)+4,max(v)+4)
#輸出結果
4 8
23 26           

以上正确輸出了Excel表格中一号店的資料行的索引範圍。可以直接上xlwing的複制粘貼方法了。直接上完整代碼。

import pandas as pd
import xlwings as xl
filepath=r"D:\樣表.xlsx"
wb=xl.Book(filepath)#打開要拆分的Excel檔案
ws=wb.sheets('Sheet1')#打開要拆分的工作表
df=pd.DataFrame(ws.range('B4:B26').value,columns=['門店'])
#用xlwings讀取拆分依據列的資料交給pandas建立一個叫門店的DataFrame資料結構
df['門店'].fillna(method='ffill',inplace=True)
swb=xl.Book()#在開一張excel檔案
head=3#拆分表表頭的行數
for i in df['門店'].unique(): #對填充後的表進行去重，并周遊。
#去重後的結果 一号店 二号店 三号店，在進行周遊，以下會以周遊到一号店為例
    screen=df[df['門店']==i]#篩選出一号店
    swb.sheets.add(i)#在新Excel檔案中建立一個名字叫一号店的工作表
    sws=swb.sheets(i)#選擇這張表
    ws.range('1:'+str(head)).copy()#複制源表表頭
    sws.range('A1').paste()#把表頭粘貼到一号店工作表
    row=head#用于定位每次粘貼後，在向那行粘貼，用于定位。
    for v in screen.groupby(screen.index.to_series().diff().ne(1).cumsum()).groups.values():
    #周遊，一号店每個資料行的位置
        ws.range(str(v.min()+head+1)+':'+str(v.max()+head+1)).copy()#複制一号店的資料
        sws.range('A'+str(row+1)).paste()#粘貼到新表
        row+=len(v)#定位用的           

文章到此結束，寫着寫着，思維有點亂，可能有很多不足，請見諒。