Python Excelファイル内複数シート 範囲コピーを新規Excelファイルに貼り付けたい

Question

Python Excelファイル内複数シート 範囲コピーを新規Excelファイルに貼り付けたい

現状、会社でExcelでの部署別予算管理をしています。
各Excelファイル(課単位=約30)を別ファイル内シート別に値を代入して集計予算を作成しています。
単純作業を自動化できないかとPythonを勉強中です。
様々なサイトから参考になりそうなソースをコピーして工夫していますが中々、思い通りの動きをしてくれません。

発生している問題・エラーメッセージ

特定のフォルダ内に格納されているExcelファイルは全て同じフォーマット形式になっています。
入力部分のセル範囲(例:U6:AF163)をコピーして、リスト化する事はできましたが、そのリストを新規Excelに
貼り付けするところでエラーがでてしまいます。

一時リストを新規Excelに張り付ける良い方法はありませんでしょうか？

該当のソースコード

from openpyxl import load_workbook
import pandas as pd
import glob
**エクセルファイルのロード
**特定フォルダ内にある.xlsxファイルを全て読取る
export_folder_path = 'C:\Users\xxxxxxxxx_経理案件_各CC予算自動集計\①.CC格納場所'
**ファイルのパスを作成 該当フォルダ内の全てのxlsxファイルを全て読み込む
ex_path = export_folder_path + '/' + '*.xlsx'
file_path = glob.glob(ex_path)

for x in file_path:
print("x =",x)
wb = load_workbook(filename = x, data_only=True)
**対象シートのロード
ws = wb['④入力sheet【予算集計】']
cell_cc_chk = ws.cell(column=2, row=2).value
print ("cell_cc_chk = ",cell_cc_chk)
for row in ws["U6:AF163"]:
values =[]
for col in row:
values.append(col.value)
print(values)

ソースコード

試したこと

ここに問題に対して試したことを記載してください。

補足情報（FW/ツールのバージョンなど）

・python3.6
・jupyter lab

初めての質問投稿になります。
詳しい皆様どうかよろしくお願いいたします。

Answer 1

試してみたら以下のエラーが出ました。

raise ValueError("Cells cannot be copied from other worksheets")

あるシートから別のシートへのセル丸ごとのコピーは出来ないようです。
一つずつ地道に値を設定するしかなさそうです。
インターネットで調べてみても各々独自にコピー処理を組んでいるようでした。

以下range_copyという関数をつくってみました。

from openpyxl import load_workbook,Workbook

# 第１引数：入力シートのオブジェクト
# 第２引数：コピー元の始点（左上のセル）のアドレス（A1形式）
# 第３引数：コピー元の終点（右下のセル）のアドレス（A1形式）
# 第４引数：出力シートのオブジェクト
# 第５引数：コピー先の始点（左上のセル）のアドレス（A1形式）
def range_copy(source_worksheet, source_initial_cell_address, source_terminal_cell_address, target_worksheet, target_initial_cell_address):
        target_initial_cell = target_worksheet[target_initial_cell_address]
        target_initial_row = target_initial_cell.row
        target_initial_column = target_initial_cell.column
        for row in source_worksheet[source_initial_cell_address + ':' + source_terminal_cell_address]:
            for cell in row:
                target_worksheet.cell(target_initial_row + cell.row - 1, target_initial_column + cell.column - 1).value = cell.value
        


filename1 = 'C:～～～～\sample1.xlsx'
wb1 = load_workbook(filename = filename1, data_only=True)
ws1 = wb1['Sheet1']

filename2 = 'C:～～～～\sample2.xlsx'
wb2 = Workbook()
ws2 = wb2.create_sheet()


range_copy(ws1, 'a1', 'c5', ws2, 'a1')

wb2.save(filename=filename2)

実行例

Comments

[bonito]

DEVOURHOPEさん >> 返信が遅くなりまして申し訳ありません。また丁寧なご対応いただきありがとうございました。未確認状態で自力で途中までは作る事ができました。ただ、、、複数ファイルを1つのファイル別シートに繰り返し処理する箇所で詰まっている状態です。以下の自己解決では最終ファイルのみが1シート作成されている状態です。複数シート対応のノウハウをお持ちであればご教授いただければ幸いです。。。何卒宜しくお願い致します。

[退会済みユーザー]

> 複数ファイルを1つのファイル別シートに繰り返し処理する箇所で詰まっている状態です。
どのような処理をしたいのか分からないので詳しく記載をお願いします。

[bonito]

DEVOURHOPEさん >>失礼しました。上手く説明できているか分かりませんが整理の意味も含めてまとめてみました。以下ご確認下さい。

・★各課の予算を入力するExcelファイル [A,B,C…]を部単位で集計する。
①A(営業本部営業一課) + ①B(営業本部営業二課) + ①C(営業本部営業三課)… = ①'(営業本部)
②A(管理本部経理課) + ②B(管理本部総務課) + ②C(管理本部人事課) + ②D(管理本部財務課) = ②'(管理本部)
③A(技術本部一課) +③B(技術本部二課) = ③'(技術本部)

・☆各部単位の予算を集計するExcelファイル[①',②',③'…]をまとめ会社の年度予算とする。

★についての詳細
・全てのExcelファイルは統一されたフォーマットで、「シート名 : ④入力sheet【予算集計】」の範囲 [U6:AF163]をコピーして集計シートに各課毎張り付けたい。
・各課のExcelファイルは、フォルダ単位で格納されているため該当するフォルダ内にあるExcelファイルを全て読み込む事とする。
・フォルダに格納されている課が例えば4課あれば自動で繰り返し4課分を部を集計する。
・各課のExcelファイル内シート「④入力sheet【予算集計】」のB2セルは課を表すコードが入力されている。例 : 111001　左記コードを②のシート名称にしたい。

以上

Answer 2

【自己解決済】各課予算データ(同一ファイル内にある別xlsxファイルを集計xlsxファイルシート別)をまとめる

from pathlib import Path
from openpyxl import load_workbook,Workbook
import os
import openpyxl as px
import pprint

Path_is = Path('C:～～～～\①.CC格納場所')
print('Path_is_a=',Path_is)

fill = px.styles.PatternFill(patternType='solid',fgColor ='D1FE7B',bgColor='D1FE7B')
#セル書式変更
wb_new = Workbook()
ws_new = wb_new.active
ws_new.title = 'tmp_cc_summary'
ws_new['B1'].fill = fill ; ws_new['B1']= '4月'
ws_new['C1'].fill = fill ; ws_new['C1'] = '5月'
ws_new['D1'].fill = fill ; ws_new['D1'] = '6月'
ws_new['E1'].fill = fill ; ws_new['E1'] = '7月'
ws_new['F1'].fill = fill ; ws_new['F1'] = '8月'
ws_new['G1'].fill = fill ; ws_new['G1'] = '9月'
ws_new['H1'].fill = fill ; ws_new['H1'] = '10月'
ws_new['I1'].fill = fill ; ws_new['I1'] = '11月'
ws_new['J1'].fill = fill ; ws_new['J1'] = '12月'
ws_new['K1'].fill = fill ; ws_new['K1'] = '1月'
ws_new['L1'].fill = fill ; ws_new['L1'] = '2月'
ws_new['M1'].fill = fill ; ws_new['M1'] = '3月'

z = 0
for i , file in enumerate(Path_is.glob('*.xlsx')):
    for ws in enumerate(Path_is.glob('*.xlsx')):
        wb = load_workbook(file, data_only=True)
        wb.active
        ws = wb['④入力sheet【予算集計】']
        row_no = i + 3
        def get_value_list(t_2d):
            return([[cell.value for cell in row] for row in t_2d])
        l_2d = get_value_list(ws['U6:AF163'])
        # 対象シート内セルの値取得_CC番号を取得して後ほどSetする側の特定に紐づける
    cell_cc_chk = ws.cell(column=2, row=2).value
    ws_new = wb_new.worksheets[z]
    ws_new.title = str(cell_cc_chk)  #get_value_listよりCCを指すB2セル値をシートにセットする
    def write_list_2d(ws_new, l_2d, start_row,start_col):
        for y, row in enumerate(l_2d):
            for x, cell in enumerate(row):
                ws_new.cell(row=start_row + y,
                            column=start_col + x,
                            value=l_2d[y][x])
    write_list_2d(ws_new, l_2d, 2, 2) #row=2, col=2を始点に書き込み
    ws_cc = wb_new.copy_worksheet(ws_new)
    ws_cc.title = 'copy_test'
    z = z + 1

    print ("★cell_cc_chk = ",cell_cc_chk)
    print('★l_2d = ', l_2d)
    wb_new.save('C:～～～～\②.部門格納場所\tmp_cc_summary.xlsx')

Answer 3

(自己解決済)各課予算データ(同一ファイル内にある別xlsxファイルを集計xlsxファイルシート別)をまとめる

from pathlib import Path
from openpyxl import load_workbook,Workbook
import os
import openpyxl as px
import pprint

Path_is = Path('C:～～～～\①.CC格納場所')
print('Path_is_a=',Path_is)

fill = px.styles.PatternFill(patternType='solid',fgColor ='D1FE7B',bgColor='D1FE7B')
#セル書式変更
wb_new = Workbook()
ws_new = wb_new.active
ws_new.title = 'tmp_cc_summary'
ws_new['B1'].fill = fill ; ws_new['B1']= '4月'
ws_new['C1'].fill = fill ; ws_new['C1'] = '5月'
ws_new['D1'].fill = fill ; ws_new['D1'] = '6月'
ws_new['E1'].fill = fill ; ws_new['E1'] = '7月'
ws_new['F1'].fill = fill ; ws_new['F1'] = '8月'
ws_new['G1'].fill = fill ; ws_new['G1'] = '9月'
ws_new['H1'].fill = fill ; ws_new['H1'] = '10月'
ws_new['I1'].fill = fill ; ws_new['I1'] = '11月'
ws_new['J1'].fill = fill ; ws_new['J1'] = '12月'
ws_new['K1'].fill = fill ; ws_new['K1'] = '1月'
ws_new['L1'].fill = fill ; ws_new['L1'] = '2月'
ws_new['M1'].fill = fill ; ws_new['M1'] = '3月'

z = 0
for i , file in enumerate(Path_is.glob('*.xlsx')):
    for ws in enumerate(Path_is.glob('*.xlsx')):
        wb = load_workbook(file, data_only=True)
        wb.active
        ws = wb['④入力sheet【予算集計】']
        row_no = i + 3
        def get_value_list(t_2d):
            return([[cell.value for cell in row] for row in t_2d])
        l_2d = get_value_list(ws['U6:AF163'])
        # 対象シート内セルの値取得_CC番号を取得して後ほどSetする側の特定に紐づける
    cell_cc_chk = ws.cell(column=2, row=2).value
    ws_new = wb_new.worksheets[z]
    ws_new.title = str(cell_cc_chk)  #get_value_listよりCCを指すB2セル値をシートにセットする
    def write_list_2d(ws_new, l_2d, start_row,start_col):
        for y, row in enumerate(l_2d):
            for x, cell in enumerate(row):
                ws_new.cell(row=start_row + y,
                            column=start_col + x,
                            value=l_2d[y][x])
    write_list_2d(ws_new, l_2d, 2, 2) #row=2, col=2を始点に書き込み
    ws_cc = wb_new.copy_worksheet(ws_new)
    ws_cc.title = 'copy_test'
    z = z + 1

    print ("★cell_cc_chk = ",cell_cc_chk)
    print('★l_2d = ', l_2d)
    wb_new.save('C:～～～～\②.部門格納場所\tmp_cc_summary.xlsx')

Answer 4

from pathlib import Path
from openpyxl import load_workbook,Workbook
import os
import openpyxl as px
import pprint

Path_is = Path(('C:～～～～\①.CC格納場所'))
print('Path_is_a=',Path_is)

for i , file in enumerate(Path_is.glob('*.xlsx')):
    fill = px.styles.PatternFill(patternType='solid',fgColor ='D1FE7B',bgColor='D1FE7B')#セル書式変更
    wb_new = Workbook()
    ws_new = wb_new.active
    ws_new.title = 'tmp_cc_summary'
    ws_new['B1'].fill = fill ; ws_new['B1']= '4月'
    ws_new['C1'].fill = fill ; ws_new['C1'] = '5月'
    ws_new['D1'].fill = fill ; ws_new['D1'] = '6月'
    ws_new['E1'].fill = fill ; ws_new['E1'] = '7月'
    ws_new['F1'].fill = fill ; ws_new['F1'] = '8月'
    ws_new['G1'].fill = fill ; ws_new['G1'] = '9月'
    ws_new['H1'].fill = fill ; ws_new['H1'] = '10月'
    ws_new['I1'].fill = fill ; ws_new['I1'] = '11月'
    ws_new['J1'].fill = fill ; ws_new['J1'] = '12月'
    ws_new['K1'].fill = fill ; ws_new['K1'] = '1月'
    ws_new['L1'].fill = fill ; ws_new['L1'] = '2月'
    ws_new['M1'].fill = fill ; ws_new['M1'] = '3月'
    wb = load_workbook(file, data_only=True)
    wb.active
    ws = wb['④入力sheet【予算集計】']
    row_no = i +3
    def get_value_list(t_2d):
        return([[cell.value for cell in row] for row in t_2d])
    l_2d = get_value_list(ws['U6:AF163'])# 対象シート内セルの値取得_CC番号を取得して後ほどSetする側の特定に紐づける
    cell_cc_chk = ws.cell(column=2, row=2).value
    
    for ws in enumerate(Path_is.glob('*.xlsx')):
        ws_new.title = str(cell_cc_chk)  #get_value_listよりCCを指すB2セル値をシートにセットする
        def write_list_2d(ws_new, l_2d, start_row,start_col):
            for y, row in enumerate(l_2d):
                for x, cell in enumerate(row):
                    ws_new.cell(row=start_row + y,
                                column=start_col + x,
                                value=l_2d[y][x])
        write_list_2d(ws_new, l_2d, 2, 2) #row=2, col=2を始点に書き込み
        ws_cc = wb_new.copy_worksheet(ws_new)
        ws_cc.title = 'copy_test'
    print ("★cell_cc_chk = ",cell_cc_chk)
    print('★l_2d = ', l_2d)


    wb_new.save(('C:～～～～\②.部門格納場所\tmp_cc_summary.xlsx')

Comments

[退会済みユーザー]

課を表すコードのシートは作成されていますか？

[bonito]

はい。直接「☆各部単位の予算を集計するExcelファイル」に書き込みたいのですが、私のレベルでは容易ではないのでtmp_cc_summary.xlsxという新規Excelファイルに書き込んでいます。最後の課だけの値がコピーペースト及びシート名を編集される状況です。
上記欄に現段階のソースに更新しました。何度も申し訳ございません。。。

[退会済みユーザー]

インデントがなくて見辛いのでmarkdown記法を活用してください。
https://teratail.com/help#about-markdown
「対応しているMarkdownの記法を知りたい」の 「コードを入力」を参照お願いします。
write_list_2d関数にws_newを渡していますがこのws_newはどこで生成しているのでしょうか？

[bonito]

参照URLよりmarkdown記法を活用させていただきました。不慣れなため失礼しました。
>write_list_2d関数にws_newを渡していますがこのws_newはどこで生成しているのでしょうか
　 wb_new = Workbook()
ws_new = wb_new.active
ws_new.title = 'tmp_cc_summary'
12行目以降、上文で新規のExcel workbook(wb_new)を作成しアクティブにしている(つもりです。。)
　ws_new.title = str(cell_cc_chk)
38行目、上文で'tmp_cc_summary'を各課のコードに置換えています。

[bonito]

DEVOURHOPEさん >> 色々アドバイスいただきありがとうございます。仕様を整理した事でソースを見直し、For文とインデントの位置を調整した事で狙った動作ができました。引き続き各課の集計を特定のxlsxファイル(部単位)集計にまとめる自動化を進めていきますが一旦、本件は区切りが良いのでクローズにしたいと思います。

[退会済みユーザー]

了解です