vbからExcelの計算式が書かれたExcel帳票を出力したい。

vb.netはvisualstudio2015です。　使用しているDBはSQLserver　Excelはxltxファイルからxlsxファイルを作成したいです。

流れとしては、vbからストアドをコールして、データを取得し、vbに返してvbがExcelテンプレートに書き出して
Excel帳票を作成する流れです。

今回の質問内容としては、タイトルにもある通りvbがExcelの書き出しを行う際に、Excelの計算式（=SUM(I9,I10,I11,I12,I13,I14,I15)や=SUM(I8:N8,P8:U8)など)
がセルに入力されたEXCEL帳票の作成の仕方がわからないというものです。

手法として考えたのが、①EXCELテンプレートに直接計算式を入力する方法　②vbで数値を出力する代わりにExcelの計算式を出力する方法
の二つです。

①の手法の問題点はExcel出力時に「オブジェクト参照がオブジェクトインスタンスに設定されていません。」というエラーメッセージが出ます。
テンプレートを「計算式記入前」→「計算式記入後」に変更して、vbは「何も手を加えず変更なし」の時と、
テンプレートを「計算式記入前」→「計算式記入後」・vbを「EXCELテンプレートに計算式を入れた箇所にデータを出力する部分をコメントアウトした」時の
2パターンともに発生していました。
テンプレートを「計算式記入前」・vbを「EXCELテンプレートに計算式を入れた箇所にデータを出力する部分をコメントアウトした」ものをそのまま使用した
時には、エラーメッセージが出ず、Excel帳票を出力させることができました。

②の手法では、Excelの計算式で使用したいセルをPos(x,y)を使って座標で指定し、指定した座標を文字型で宣言した変数に代入して、
Excelに出力したい場所へデータを出力する部分をExcelの計算式を出力するように変更して実行しました。

vb.net
1                            Dim Cel1 As String = ""
2                            Dim Cel2 As String = ""
3                                .Pos(iCol, 4 + iRow * 14).Double = CDec(dr("契約売上"))
4                                .Pos(iCol, 5 + iRow * 14).Double = CDec(dr("スポット売上"))
5                                .Pos(iCol, 6 + iRow * 14).Double = CDec(dr("物販売上"))
6                                '.Pos(iCol, 7 + iRow * 14).Double = CDec(dr("売上合計"))
7                                Cel1 = CStr(.Pos(iCol, 4 + iRow * 14))
8                                Cel2 = CStr(.Pos(iCol, 6 + iRow * 14))
9                                .Pos(iCol, 7 + iRow * 14).Str = "=SUM(" & Cel1 & ":" & Cel2 & ")"
10                                .Pos(iCol, 8 + iRow * 14).Double = CDec(dr("人件費"))
11                                .Pos(iCol, 9 + iRow * 14).Double = CDec(dr("積上設定原価1"))
12                                .Pos(iCol, 10 + iRow * 14).Double = CDec(dr("積上設定原価2"))
13                                .Pos(iCol, 11 + iRow * 14).Double = CDec(dr("積上設定原価3"))
14                                .Pos(iCol, 12 + iRow * 14).Double = CDec(dr("積上設定原価4"))
15                                .Pos(iCol, 13 + iRow * 14).Double = CDec(dr("積上設定原価5"))
16                                .Pos(iCol, 14 + iRow * 14).Double = CDec(dr("積上設定原価6"))
17                                '.Pos(iCol, 15 + iRow * 14).Double = CDec(dr("積上設定原価7"))
18                                Cel1 = CStr(.Pos(iCol, 8 + iRow * 14))
19                                Cel2 = CStr(.Pos(iCol, 14 + iRow * 14))
20                                .Pos(iCol, 15 + iRow * 14).Str = "=SUM(" & Cel1 & ":" & Cel2 & ")"
21                                '.Pos(iCol, 16 + iRow * 14).Double = CDec(dr("粗利金額"))
22                                Cel1 = CStr(.Pos(iCol, 7 + iRow * 14))
23                                Cel2 = CStr(.Pos(iCol, 15 + iRow * 14))
24                                .Pos(iCol, 16 + iRow * 14).Str = "=" & Cel1 & "-" & Cel2
25

結果としては、「型'BB.XlsxCell'のオブジェクトを型'System.String'にキャストできません。」
とエラーメッセージが出力されます。

以上の結果から質問としては、
①と②の手法でそれぞれのエラーを発生させないようにするにはどうすればよいのかと、
他の手法でvbからExcelの計算式が書かれた帳票を出力する方法が聞きたいです。

よろしくお願いいたします。

YAmaGNZ

2024/04/11 12:36

どのようなライブラリを使用しどのようなコードでエラーが出るのでしょうか？

can110

2024/04/12 00:57 編集

https://teratail.com/help/question-tips は読みましたか？読んだ結果を本文に反映ください。解決のヒントを探れるかもしれません。

Moyashiwa

2024/04/12 02:35

＞YAmaGNZさん＞どのようなライブラリを使用しどのようなコードでエラーが出るのでしょうか？私が初学者故にどういったことを質問されているのかが理解できていないのですが、これらのコードで何をビルドして使用するのかを聞いているという認識でよろしいでしょうか。この認識でよいならば、解答としては、上記のコードを含めたvbファイルをビルドしてdllファイルを作成し、exeファイルからの呼び出しで実行しています。コードは②の手法で私が編集したものを追加しました。 ①の手法だと追記したコードのCel1、Cel２の記載がなくなったものを使用したと思っていただけると幸いです。

Moyashiwa

2024/04/12 02:41

＞can110さん＞https://teratail.com/help/question-tips は読みましたか？＞読んだ結果を本文に反映ください。解決のヒントを探れるかもしれません。コメントありがとうございます。質問を修正したのでご確認よろしくお願いいたします。私が新人ゆえに会社で開発するソフトのコードをどこまで載せてよいのかなどの守秘義務に関する匙加減がわからず至らぬ部分もあると思いますが、よろしくお願いいたします。

YAmaGNZ

2024/04/12 04:29

ExcelCreatorを利用されているのでしょうか？そうなのであればメーカーに問い合わせるほうが早いかもしれません。また変数等の型についての知識が足りていないようなのでこちらについても学習されたほうがいいかと思います。パッと検索した感じ .Pos(iCol, 4 + iRow * 14) はXlsxCell型を返すようです。また計算式を設定する場合はFuncメソッドを使用すると思われます。ただこちらの情報も私はこのライブラリのヘルプ等を持っているわけではないのでメーカーに問い合わせて確認したほうが確実だと思います。

YAmaGNZ

2024/04/12 08:01

＞新人ゆえに会社で開発するソフトのコードをどこまで載せてよいのかライブラリの使用の仕方や問い合わせ先など先輩に質問するほうがいいでしょう。またこういった質問を社外に出していいのかも含めて社内で質問したほうがいいと思います。

Moyashiwa

2024/04/15 01:18 編集

＞ExcelCreatorを利用されているのでしょうか？おっしゃる通りExcelCreatorを使用しています。＞.Pos(iCol, 4 + iRow * 14)はXlsxCell型を返すようです。＞また計算式を設定する場合はFuncメソッドを使用すると思われます。私も調べた限り、Posは（）の中の数字に基づきセルの座標情報を取得するメソッド、 Funcは（）内に文字型でExcelの計算式を記入して返すメソッドのようです。（参考URL：https://vbnetdb.net/xlscreator/xlscr05_index.php 　　https://www.adv.co.jp/product/product_excelcreator3_creatorcontrolprop.htm 　　https://vbnetdb.net/xlscreator/xlscr06_hanbaijisseki.php 　　http://aws.adv.co.jp/demo/excelcreator10/Page/CreateFile.aspx　　） Posで座標を指定したセルの名称（A1、I8など）を取得するメソッドがなさそうなので、上記質問の②の手法では難しいかなと思っています。

行動規範の内容に同意します

回答1件

自己解決

結果として、②の修正方法を採用しました。

Posで座標情報をもとにExcelのセルの名称を取得することはメソッドがないので不可能です。
座標取得の際に使う変数の値をもとにセルの名称のアルファベット部分を指定する変数を宣言して
名称を指定し、Funcで計算式を設定する手法で実装できました。
列がその後の処理でColumnDeleteなどで削除されても、
計算式で参照したいセルがずれることはなかったです。

vb.net
1Dim Cel As String = ""
2
3                            If iCol = 8 Then
4                                Cel = "I"
5                            ElseIf iCol = 9 Then
6                                Cel = "J"
7                            ElseIf iCol = 10 Then
8                                Cel = "K"
9                            ElseIf iCol = 11 Then
10                                Cel = "L"
11                            ElseIf iCol = 12 Then
12                                Cel = "M"
13                            ElseIf iCol = 13 Then
14                                Cel = "N"
15                            ElseIf iCol = 15 Then
16                                Cel = "P"
17                            ElseIf iCol = 16 Then
18                                Cel = "Q"
19                            ElseIf iCol = 17 Then
20                                Cel = "R"
21                            ElseIf iCol = 18 Then
22                                Cel = "S"
23                            ElseIf iCol = 19 Then
24                                Cel = "T"
25                            ElseIf iCol = 20 Then
26                                Cel = "U"
27                            ElseIf iCol = 7 Then
28                                Cel = "H"
29                            ElseIf iCol = 14 Then
30                                Cel = "O"
31                            ElseIf iCol = 21 Then
32                                Cel = "V"
33                            End If
34
35.Pos(iCol, 7 + iRow * 14).Func("=SUM(" & Cel & 1 + 4 + iRow * 14 & ":" & Cel & 1 + 6 + iRow * 14 & ")", "")
36
37 .Pos(iCol, 17 + iRow * 14).Func("=IF(" & Cel & 1 + 7 + iRow * 14 & "=0,0,ROUNDDOWN(" & Cel & 1 + 16 + iRow * 14 & "/" & Cel & 1 + 7 + iRow * 14 & ",4))", "")