質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.42%
openpyxl

openpyxlは、Excel2007以降のファイル(xlsx/xlsm/xltx/xltm)を読み書きするためのPythonライブラリです。

Python

Pythonは、コードの読みやすさが特徴的なプログラミング言語の1つです。 強い型付け、動的型付けに対応しており、後方互換性がないバージョン2系とバージョン3系が使用されています。 商用製品の開発にも無料で使用でき、OSだけでなく仮想環境にも対応。Unicodeによる文字列操作をサポートしているため、日本語処理も標準で可能です。

Q&A

解決済

2回答

691閲覧

【Python】openpyxlを使うと値がNoneになってしまいます

takuan_55

総合スコア13

openpyxl

openpyxlは、Excel2007以降のファイル(xlsx/xlsm/xltx/xltm)を読み書きするためのPythonライブラリです。

Python

Pythonは、コードの読みやすさが特徴的なプログラミング言語の1つです。 強い型付け、動的型付けに対応しており、後方互換性がないバージョン2系とバージョン3系が使用されています。 商用製品の開発にも無料で使用でき、OSだけでなく仮想環境にも対応。Unicodeによる文字列操作をサポートしているため、日本語処理も標準で可能です。

1グッド

1クリップ

投稿2024/05/14 08:58

編集2024/05/17 09:15

実現したいこと

イメージ説明
★関数が含まれるExcelファイル[main]の特定の列に関数が入ったまま値を入力したい
⇒そのために[main]を値コピーしたクローンファイル[work]を作成する
①openpyxlでクローンファイル[work]を作成
②[work]の特定シート、特定列を読み取る
③②で読み取ったデータを[main]の特定シート、特定列に値貼り付け

[main]の関数を壊さずに、[main]のシート"display"に出力される関数の結果を、[main]のシート"past"に移動させたい

以下は全て自動で行われていることが理想です
①[main] シート”data”にデータが入力される
②[main] シート”display”の関数が更新("data"の値がvlookupで更新されます)
③vlookupで更新された値をシート"past"の右端+1行に記述したい
※ここでは関数を壊さないで関数の値だけシート移動、貼り付けしたいです。
※シート"past"の右端、右端+1行は関数が含まれていないセルになります。

前提

言語:Python
ライブラリ:openpyxl

関数が入ったファイルを用意します

ファイルA 以下のような関数が含まれたファイルです
ファイルAはシートが3枚あります
イメージ説明

現状

イメージ説明

理想

workが以下のように値が記載されれば、Noneにならずに値が出力されることがわかりました。
しかし、ファイルAをopenpyxlでコピーするとなぜか現状の通りに関数部分が真っ白になってしまうのです。
イメージ説明

上書き保存後の該当ファイル

イメージ説明

問題箇所

関数が含まれるExcelファイル[main]の特定の列に関数が入ったまま値を入力したいのですが、
②の段階でNoneと表示され、プログラムが評価されません。

該当のソースコード

Python

1import openpyxl 2 3# Excelファイルを開く 4#excel_file_pathは関数が含まれているファイルAです 5excel_file_path = f"\\\\任意のディレクトリ\\ファイルA.xlsx" 6excel_file_copy_path = f"\\\\任意のディレクトリ\\work.xlsx" 7workbookw = openpyxl.load_workbook(excel_file_path, data_only= True) 8workbookw.save(excel_file_copy_path) 9 10# workを開く 11workbook = openpyxl.load_workbook(excel_file_copy_path, data_only=True) 12 13# 過去分シートとレイアウトシートを取得 14past_sheet = workbook['past'] 15layout_sheet = workbook['display'] 16 17# 過去分シートの行数を取得 18row_count = past_sheet.max_row 19 20# 過去分シートの行をループ 21for row in range(2, row_count + 1): 22 # A列にNEWが入っている場合 23 # ----★この下のprintがNoneになっている★----------------------- 24 print(f"past⇒{past_sheet.cell(row=row, column=1).value}") 25 #----------------------------------------------------------------- 26 if past_sheet.cell(row=row, column=1).value == "NEW": 27 # D列の値を取得 28 search_value = past_sheet.cell(row=row, column=4).value 29 print(f"sv⇒{search_value}") 30 31 # レイアウトシートでVlookupを行う 32 for layout_row in range(2, layout_sheet.max_row + 1): 33 if layout_sheet.cell(row=layout_row, column=6).value == search_value: 34 # 取得した値を過去分シートの最後のセルに出力 35 past_sheet.cell(row=row, column=past_sheet.max_column).value = layout_sheet.cell(row=layout_row, column=29).value 36 break 37 38# Excelファイル(work.xlsx)を保存 39workbook.save(excel_file_copy_path) 40 41# workファイルからmainファイルへの転送 42# 転送元ファイルの設定 43sheet_from = workbookw["display"] 44row_from = sheet_from.max_row 45column_from = sheet_from.max_column 46print(column_from) 47 48# 転送先ファイルの設定 49workbook_to= openpyxl.load_workbook(excel_file_path) 50sheet_to = workbook_to["past"] 51row_to = sheet_to.max_row 52column_to = sheet_to.max_column 53 54# 転送のしくみ 55for j in range(1, row_from): 56 print(sheet_from.cell(row=j, column=column_from).value) 57 sheet_to.cell(row=j, column=column_to+1).value = sheet_from.cell(row=j, column=column_from).value 58print("時の流れはフシギダネ")

試したこと

workbook = openpyxl.load_workbook(excel_file_path, data_only=True)の場合、
★の部分が正しく表示されます。

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

もしかして、このサイトが言及する理由が原因ですか?
https://note.com/vanaya/n/nd46b4b7fc55e

よろしくお願いします。

tatsu99👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

TakaiY

2024/05/14 09:19 編集

excelによってコピーされた、work.xlsxをExcelで開くと値はどうなっていますか?
takuan_55

2024/05/14 09:26

もともと値として入力されているものは、そのまま表示されていますが、関数として入力されているものはなにも表示されていません。 0でも、""でも、N/Aでもなく、空白です。
TakaiY

2024/05/14 10:40

> 関数として入力されているものはなにも表示されていません。 > 空白です。 ということは、そのExcelのそのセルには何も入っていないので、「Noneと表示され」るのは当然と思いますが、何が表示るべきだということでしょうか。
takuan_55

2024/05/14 11:39

関数の結果の値を表示させたいです。 ファイルAのExcelのセルは関数が入っています。 そのファイルAをopenpyxlによってコピーしたものがworkなので、★に表示されるのはworkの値になるはずです。
quickquip

2024/05/14 13:55 編集

日本語で正確に記述するのが困難なのであれば ファイルA.xlsx を Excelで開いて A2 にカーソルがある状態で数式バーの内容が読めるスクリーショット と work.xlsx を Excelで開いて A2 にカーソルがある状態で数式バーの内容が読めるスクリーショット と こうなっていてほしい理想の結果が分かるスクリーンショット を 質問に添付するのがよさそうです
TakaiY

2024/05/14 12:28

'data_only=True'を指定することで、計算結果が取得できると想定しているのだと思いますが、補足にあるリンク先にも書いてあるとおり、openpyxlにはセルの数式を計算する能力は無く、Excelが裏でファイルに保存している計算結果の値を取得しているだけなのです。 なので、コピーしたwork.xlsxに値が無いのであれば、裏で保存されている値が無いということです。 元のファイルを一度Excelで開いて、再計算するなどしてセルに値が表示されるようにしてから試してみるとどうでしょうか。
bsdfan

2024/05/14 13:15

「関数が入ったまま値を入力したい」というのが、いまいち何をしたいのか明確に理解できません。元のファイルについては言及されているので、どんな最終形にしたいのかを書けないでしょうか。(今の例だと複雑なのでシンプルな例にしてもらえるとベターです) そもそもファイルを別名で保存して開き直す必要があるのか、data_only=True がいるのかというところも疑わしいように見えます。
takuan_55

2024/05/15 01:52

ご指摘ありがとうございます。 関数が含まれているexcel、それを基にコピーしたexcelの現状と理想を画像で添付しました。 TakaiY様が仰るように、 openpyxlでいくら'data_only = True'で読み取っても 値を計算しないから値コピーは作成できないということなのでしょうか。
takuan_55

2024/05/15 02:06

bsdfan様のご指摘の通り、 究極の理想を実現したいこと、に記載しました。 つまり、関数が入ったexcelファイルのデータを関数が入ったまま、 シートを変えてコピペしたいというのが本質になります。 関数が入ったexcelファイルの'data_only = True'を実行すると、関数が消えてしまうため このように関数ありファイルのコピーとして関数なしファイルを作成することになりました。
TakaiY

2024/05/15 05:05

こちらで、数個のデータの表と、vlookupで値を取り出すようにした簡単なものを作り、pythonで data_only= True で読み込み、それを保存してみると、ちゃんと値だけコピーされていました。 ということで、そういう動作になることを想定するのは間違えていないと思います。 再度確認ですが、「関数が含まれているファイルA」を開くと値が表示されていると思いますが「それを上書き保存」してからpythonで処理しても同様の結果になりますか? 先にも書いたとおり、ファイルAにはExcelで計算した結果が保存されている必要があるのです。
bsdfan

2024/05/15 08:01

数式が入ったセルでは、Excelで保存した xlsx ファイルには「式」と計算結果の「値」が保存されていますが、openpyxl で作成した xlsx ファイルには「式」しか保存されません。(openpyxl はExcel の数式を計算できないので) data_only=True はこのうちの「値」をとってくるという意味なので、openpyxl で作成した xlsx ファイルの数式が入っているセルには「値」が入っていないので、None になってしまいます。openpyxl で作ったファイルでも、いったん Excel で開いてそのまま保存すれば値が入って保存されます。
TakaiY

2024/05/15 08:26

bsdfanさんの言うとおりなのですが、元のファイルである「関数が含まれるExcelファイル[main]」はpythonとかopenpyxlで作ったものではないと思うんですよね。 なので、原因ではないと思っているのすが、念の為、前の質問をしています。
takuan_55

2024/05/15 08:43

TakaiY様が仰る通り、 Excelファイル[main]はもともと作成しているファイルです。openpyxlを用いて作ったものではありません。
takuan_55

2024/05/15 08:46

bsdfan様、TakaiY様 ご指摘ありがとうございます。 [main]関数の値がvlookで表示されたあと、 上書き保存し、その後に上記のコードを回してみます。
takuan_55

2024/05/17 01:34

bsdfan様、TakaiY様や、提示したブログの内容の通り、 一度関数の入ったファイルを開いて関数の値が更新されたのちに、 上書き保存をすればdata_only = trueで値が読み込めることは確認しました。 やはり、一度関数の入ったファイルを開いて、上書き保存する、という 自動化できない工程が含まれてしまうのでしょうか?
TakaiY

2024/05/17 01:51

改めて確認ですが、その mainファイルに入っている数式は Excelで書いたものではないとか、入っている値をpythonで修正するなどして再計算が必要な状態ということですか?
quickquip

2024/05/17 02:34

Excelで開い(て再計算し)たらどうなってるのか Excelで開いて再計算させる必要がある は、なんどかコメントで言われているのにスルーされているので、今になってなにを聞いているのか? となっていて Python のコード以前の話として、本当は何をしたいのか? という「達成したいこと」を書いたほうがいい感じがします
bsdfan

2024/05/17 02:35

質問のコードでは保存までされていませんが、いろいろ試しているうちに、もともとの Excel で作った xlsx が openpyxl で出力したファイルと置き換わってしまっている可能性があると思ってコメントしていました。 実際のケースでは初期はExcelで作成したファイルからスタートするということが確実なら気にしなくてもいいのかもしれません。そうでないのなら、このやり方では厳しいと思います。(マクロでやるかxlwingsを使うかですかね。)
takuan_55

2024/05/17 08:54

quickquip様 ご指摘ありがとうございます。 Excelを一度開く⇒関数の更新を確認⇒上書き保存を 行なったファイルを添付しました。 displayの値をpastに移す作業は対象外ですが、 この「Excelを一度開く」~「上書き保存」の段を人の手介さずにできれば理想と考えています。 先の主題から質問の趣旨が変わってしまいました。 実現したいことを編集しておきます。
takuan_55

2024/05/17 09:00

TakaiY様 コメントをありがとうございます。 インラインで返答いたします。 >その mainファイルに入っている数式は Excelで書いたものではない →Excelに直書きしたものです。 >入っている値をpythonで修正するなどして再計算が必要な状態 →pythonで修正することはありません。 mainはすでに関数が記載されており、pythonで修正する必要はありません。 一度、コード外でExcel関数が更新され、上書き保存されないとopenpyxlでは値を読み込んでくれないものなのでしょうか?
takuan_55

2024/05/17 09:06

bsdfan様 コメントをありがとうございます。 もともとのExcelはバックアップを取っており、 コードを回すたびにバックアップデータを呼び起こして作業しております。 openpyxlで作成したデータと区別して提供しています。 もともと、スクレイピングで複数の企業データを取得することが要でしたので、 pythonで実行する必要があると判断しました。 代替案のご提示をありがとうございます。 試してみます。
quickquip

2024/05/17 09:43 編集

すみません。やっと意味が通じた気がするので以下は無視してください。(削除はしないでおきます) -- 8< -- 8< -- 8< -- Excelの設定で自動計算オフ、保存前再計算オフ とかしてますか? 今、質問者さんが何に困っているのか他の人が想像できてないです(おそらく) →Excelに直書きしたものです。 →pythonで修正することはありません。 が正しいなら、Excelの中身を書きかえたい時は必ずExcelで開いて保存するという手順が存在するはずです。 そうであれば最後に書きかえた内容で計算されてデータがxlsx内に保管されるので、特に困ることがないはずです。 「Excelに直書きした」はどういうことを言っていますか? 「Excelで開いて書いて保存する」以外の何かのはずですが、それが想像できてません。(正確には、Excelで開かずになにかしたいんだろうな、と想像していたけれど否定されたからなにか分からなくなった、です) 一方で > やはり、一度関数の入ったファイルを開いて、上書き保存する、という自動化できない工程が含まれてしまうのでしょうか? > この「Excelを一度開く」~「上書き保存」の段を人の手介さずにできれば理想と考えています。 と言っています。つまり「Excelを開かずになにかしたい」と思っているはずです。 Excelを開かずにデータや式を編集したい、という欲求がなければ、こんな考えにはならないはずです。 「人の手を介さずにしたいと思っていること」ぜんぶを書いてください。
quickquip

2024/05/17 09:30 編集

> もともとのExcelはバックアップを取っており、 > コードを回すたびにバックアップデータを呼び起こして作業しております。 > もともと、スクレイピングで複数の企業データを取得することが要でしたので、 ほら、「やりたいこと」で「書かれていないこと」が飛び出してきた いま知りたい「やりたいこと」はこちらの話なのです
takuan_55

2024/05/17 09:56

quickquip様 ご指摘ありがとうございます。 最終的にやりたいことは ・複数の企業のデータをスクレイピング ・スクレイピングしたデータを過去-1分と比較 ★比較したデータをpastに累積させる →関数が入っているのはpastの並び方を指定するためです ・displayのフォームにする →pastのデータはdisplayの関数(今回の「特定列」ではない関数です)に反映されます ・displayのフォームを.txtにする 今回の質問は上記の★になります。 スクレイピング後、前日分と比較したデータはdataシートに記載されます。
takuan_55

2024/05/17 10:01

スクレイピング、比較、displayフォーム、txtファイル出力のテストは問題ないことを確認しています。
TakaiY

2024/05/17 11:55

回答書いているうちに、コメント進みましたが、やっぱりXY問題でしたね。 この問題、そもそもExcelにする必要あります? 結果をExcelにしたいとしても、今回問題の計算の部分もpythonでやればいいだけじゃないかと思うのですが。
takuan_55

2024/05/20 02:01

TakaiY様 コメントありがとうございます。 連絡が遅くなりすみません。 絶対にExcelにする必要はないです。 担当社員はプログラムの修正はできないが関数を使えるので、関数を使いました。
guest

回答2

0

自己解決

win32comを用いた仕組みで解決することができました。
友人が作成したコードになります。

python

1import openpyxl 2import win32com.client 3 4# Excelファイルを開く 5excel_file_path = f"任意のファイルパス\\monthly_compare_main.xlsx" 6 7# win32comでExcelファイルを開く 8excel = win32com.client.Dispatch("Excel.Application") 9workbook = excel.Workbooks.Open(excel_file_path) 10 11workbook.Save() 12workbook.Close() 13excel.Quit() 14 15# 値を保持するためにdata_only=Trueで読み込む 16workbook_data_only = openpyxl.load_workbook(excel_file_path, data_only=True) 17 18# 関数を保持するために通常読み込む 19workbook = openpyxl.load_workbook(excel_file_path) 20 21# 過去分シートとレイアウトシートを取得 22past_sheet = workbook_data_only['past'] 23layout_sheet = workbook_data_only['display'] 24 25# 関数を保持したシートも取得 26past_sheet_function = workbook['past'] 27 28# 過去分シートの行数を取得 29row_count = past_sheet.max_row 30 31# 過去分シートの行をループ 32for row in range(2, row_count + 1): 33 # A列にNEWが入っている場合 34 #以下の部分に値が表示され、動作することが確認できました 35 print(f"past⇒{past_sheet.cell(row=row, column=1).value}") 36 if past_sheet.cell(row=row, column=1).value == "NEW": 37 # D列の値を取得 38 search_value = past_sheet.cell(row=row, column=4).value 39 print(f"sv⇒{search_value}") 40 41 # レイアウトシートでVlookupを行う 42 for layout_row in range(2, layout_sheet.max_row + 1): 43 if layout_sheet.cell(row=layout_row, column=6).value == search_value: 44 # 取得した値を関数を保持したシートの最後のセルに出力 45 past_sheet_function.cell(row=row, column=past_sheet_function.max_column).value = layout_sheet.cell(row=layout_row, column=29).value 46 past_sheet_function.cell(row=row, column=9).value = past_sheet_function.cell(row=row, column=past_sheet_function.max_column-1).value 47 past_sheet_function.cell(row=row, column=8).value = past_sheet_function.cell(row=row, column=past_sheet_function.max_column-2).value 48 past_sheet_function.cell(row=row, column=7).value = past_sheet_function.cell(row=row, column=past_sheet_function.max_column-3).value 49 past_sheet_function.cell(row=row, column=6).value = past_sheet_function.cell(row=row, column=past_sheet_function.max_column-4).value 50 past_sheet_function.cell(row=row, column=5).value = past_sheet_function.cell(row=row, column=past_sheet_function.max_column-5).value 51 break 52 53print("時の流れはフシギダネ") 54# Excelファイル(work.xlsx)を保存 55workbook.save(excel_file_path)

投稿2024/05/21 02:05

takuan_55

総合スコア13

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

①[main] シート”data”にデータが入力される
②[main] シート”display”の関数が更新("data"の値がvlookupで更新されます)
③vlookupで更新された値をシート"past"の右端+1行に記述したい
※ここでは関数を壊さないで関数の値だけシート移動、貼り付けしたいです。
※シート"past"の右端、右端+1行は関数が含まれていないセルになります。

1と2を自動でやることは、openpyxl ではできません。 ブックに含まれる計算式を(再)計算することができないためです。

pythonを使ってExcelブックを操作するモジュールに、xlwingsというのがあります。これは、pythonでxlsxファイルを操作するのではなく、pythonからExcelそのものを操作するモジュールです。実際にExcelを操作するので、再計算させることができます。ただし、Excelのインストールしてあるマシンでないと動きません。

投稿2024/05/17 11:49

TakaiY

総合スコア13147

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

takuan_55

2024/05/20 04:42

TakaiY様ありがとうございます。 まずはxlwingsで試しに作ってみます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.42%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問