以前ここでExcel出力のことを書いたけど
裏技的で推奨できない。
正攻法で行くことにしました。
Excel参照設定もクライアント環境で違うでしょうから依存しない実行時バインディングで・・
excelのプロセスが残らないようにするのが大変でした
まずは単純に開いて5秒待って閉じるだけ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
Public Shared Sub Excelmain2() Dim objExcel As Object = Nothing Dim objExApp As Object = Nothing Dim objWorkbook As Object = Nothing Dim objWorkbooks As Object = Nothing Dim fpath As String fpath = "xlsファイルのパス" Try objExcel = CreateObject("Excel.Application") objExApp = objExcel.Application objExApp.Visible = True objWorkbooks = objExApp.Workbooks objWorkbook = objWorkbooks.Open(fpath) System.Threading.Thread.Sleep(5000) objExcel.DisplayAlerts = False Marshal.ReleaseComObject(objWorkbook) Marshal.ReleaseComObject(objWorkbooks) objExApp.Quit() objExcel.DisplayAlerts = True Marshal.ReleaseComObject(objExApp) Marshal.ReleaseComObject(objExcel) objWorkbook = Nothing objWorkbooks = Nothing objExApp = Nothing objExcel = Nothing Catch ex As Exception Throw End Try End Sub |
開いて閉じるだけでも変数が多くて混乱します
次に開いてセルに文字代入して保存して閉じる。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
Public Shared Sub Excelmain3() Dim objExcel As Object = Nothing Dim objExApp As Object Dim objWorkbook As Object = Nothing Dim objWorkbooks As Object Dim fpath As String fpath = "xlsファイルのパス" Dim objSheets As Object = Nothing Dim objSheet As Object = Nothing 'Dim objRange As Object = Nothing Dim objCells As Object = Nothing Try objExcel = CreateObject("Excel.Application") objExApp = objExcel.Application objExApp.Visible = True objWorkbooks = objExApp.Workbooks objWorkbook = objWorkbooks.Open(fpath) System.Threading.Thread.Sleep(5000) objSheets = objExApp.Sheets objSheet = objSheets(1) objCells = objSheet.Cells objCells(2, 2) = "hogehoge:" & Now Marshal.ReleaseComObject(objCells) Marshal.ReleaseComObject(objSheet) Marshal.ReleaseComObject(objSheets) objExcel.DisplayAlerts = False objWorkbook.SaveAs(fpath) Marshal.ReleaseComObject(objWorkbook) Marshal.ReleaseComObject(objWorkbooks) objExApp.Quit() objExcel.DisplayAlerts = True Marshal.ReleaseComObject(objExApp) Marshal.ReleaseComObject(objExcel) objCells = Nothing objSheets = Nothing objSheet = Nothing objWorkbook = Nothing objWorkbooks = Nothing objExApp = Nothing objExcel = Nothing Catch ex As Exception Throw End Try End Sub |
セルに代入するだけで変数が増えました。
これを踏み台にdatatableの数値を配列を使ってExcelのセルに代入する。