vbのClosedXMLを使用して出力したエクセルがClosedXMLでは読めない

Question

実現したいこと

別途説明するエクセルファイルをClosedXMLを使用して読み込めるようにしたい
エクセルがインストールされていない端末でもそれが可能な事

発生している問題・分からないこと

以下のスレッドの続きです。（見なくても、ここに必要要項は書きますが。）
https://teratail.com/questions/lmfwqvnb5lx442#reply-85ukqmng7vq6ft

ClosedXMLを使用してExcelファイルを読み込む時に、式が入ったセルでエラーになるので、上記のスレッドを立てました。
しかし、ClosedXMLやVBのソースではなく、Excelファイルが壊れていました。

なぜ、Excelファイルが壊れていると言えるのか
ClosedXMLからは読めないが、本物のエクセルでは読み込む事ができるので、本物のエクセルで再保存したところ正しく処理が行えた。

ClosedXMLの読込能力の低さを残念がる事もできますが、エクセルで再保存したものは正しく処理できたので、ファイル作成者が悪いという見方もできます。

そのファイル作成者は外ならぬClosedXMLライブラリでした。
ですので、今回は書き込み側中心の話になります。

エラーメッセージ

型 'System.StackOverflowException' のハンドルされていない例外が mscorlib.dll で発生しました

埋め込みドキュメント 'System.Globalization\CompareInfo.cs' を 'C:\Users\sys_developer\AppData\Local\Temp\.vsdbgsrc\82f5c4d99137273f37e4fbe578de1a03843e6011bebc9ed0966c7a983ed26386\CompareInfo.cs' に抽出しました
プログラム '[18536] WindowsApp1.exe' はコード 4294967295 (0xffffffff) で終了しました。

該当のソースコード

Imports System.IO
Imports System.Reflection
Imports ClosedXML.Excel
Imports DocumentFormat.OpenXml.Spreadsheet

Public Class Form1
 Const Temp1 As String = "d:\work\TEMP1.xlsx"
 Const Temp2 As String = "d:\work\TEMP2.xlsx"
 Dim DataTableDGView1 As New DataTable
 Dim ExcelResourceName As String = "WindowsApp1.TEST1.xlsx"

 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  TabControl1.Dock = DockStyle.Fill
  DataGridView1.Dock = DockStyle.Fill
  DataGridView2.Dock = DockStyle.Fill
  Call DataTable1Set()
 End Sub

 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  Dim Eod As Integer
  Using ResourceStream As Stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(Me.ExcelResourceName)
   If ResourceStream Is Nothing Then
    MessageBox.Show("リソースが見つかりませんでした。")
    Exit Sub
   End If
   Using fileStream As FileStream = File.Create(Temp1)
    ResourceStream.CopyTo(fileStream)
   End Using
  End Using

  Using XLWorkbook1 = New XLWorkbook(Temp1)
   Dim WorksheetX = XLWorkbook1.Worksheet(1)
   Dim CntRow As Integer = 1
   For Each DataRow1 As DataRow In Me.DataTableDGView1.Rows
    CntRow += 1
    Dim CntCol As Integer = 1
    WorksheetX.Cell(CntRow, CntCol).Value = DataRow1("A-Item").ToString
    WorksheetX.Cell(CntRow, CntCol + 1).Value = DataRow1("B-Key").ToString
    WorksheetX.Cell(CntRow, CntCol + 2).Value = ExcelCellDate(DataRow1("C-Date").ToString)
    For For1 As Integer = 1 To 28
     WorksheetX.Cell(CntRow, CntCol + 2 + For1).Value = "Dummy" & For1.ToString(0)
    Next
    WorksheetX.Cell(CntRow, CntCol + 31).Value = DataRow1("AG-String").ToString
    WorksheetX.Cell(CntRow, CntCol + 32).Value = ExcelCellDate(DataRow1("AH-Date").ToString)
    WorksheetX.Cell(CntRow, CntCol + 33).Value = ExcelCellDate(DataRow1("AI-Date").ToString)
    WorksheetX.Cell(CntRow, CntCol + 35).Value = ExcelCellDate(DataRow1("AK-Date").ToString)
    WorksheetX.Cell(CntRow, CntCol + 36).Value = DataRow1("AL-String").ToString
    WorksheetX.Cell(CntRow, CntCol + 37).Value = DataRow1("AM-String").ToString
    WorksheetX.Cell(CntRow, CntCol + 38).Value = ExcelCellNumeric(DataRow1("AN-フラグ").ToString)
    WorksheetX.Cell(CntRow, CntCol + 41).Value = ExcelCellNumeric(DataRow1("AQ-フラグ").ToString)
    WorksheetX.Cell(CntRow, CntCol + 43).Value = ExcelCellNumeric(DataRow1("AS-フラグ").ToString)
    WorksheetX.Cell(CntRow, CntCol + 44).Value = ExcelCellDate(DataRow1("AT-Date").ToString)
   Next
   Eod = CntRow
   XLWorkbook1.Save()
  End Using

  Call ExcelRemoveRows(Temp1, Eod + 1, 1000)
  Call ExcelSaveAs(Temp1, Temp2)
  MsgBox("Excelファイルへの書き込みが正常に完了しました。")
 End Sub

 Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
  Call Put_DataGrid(Temp1)
 End Sub

 Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
  Call Put_DataGrid(Temp2)
 End Sub

 Private Sub DataTable1Set()
  Me.DataTableDGView1 = New DataTable()
  With Me.DataTableDGView1.Columns
   .Add("A-Item", GetType(String))
   .Add("B-Key", GetType(String))
   .Add("C-Date", GetType(String))
   .Add("AG-String", GetType(String))
   .Add("AH-Date", GetType(String))
   .Add("AI-Date", GetType(String))
   .Add("AK-Date", GetType(String))
   .Add("AL-String", GetType(String))
   .Add("AM-String", GetType(String))
   .Add("AN-フラグ", GetType(String))
   .Add("AQ-フラグ", GetType(String))
   .Add("AS-フラグ", GetType(String))
   .Add("AT-Date", GetType(String))
  End With

  Dim Random1 As New Random()
  For Count1 As Integer = 1 To 255
   Dim RandomNumb As Integer = Random1.Next(0, 2)
   Dim NewRow1 As DataRow = Me.DataTableDGView1.NewRow()
   NewRow1("A-Item") = "あああ"
   NewRow1("B-Key") = "KEY" & Count1.ToString("0000000000")
   NewRow1("C-Date") = DateTime.Now.AddDays(Random1.Next(-50, 61)).ToString("yyyy/MM/dd")
   NewRow1("AG-String") = If(RandomNumb = 0, String.Empty, "AG" & RandomNumb.ToString)
   NewRow1("AH-Date") = DateTime.Now.AddDays(Random1.Next(-10, 10)).ToString("yyyy/MM/dd")
   NewRow1("AI-Date") = DateTime.Now.AddDays(Random1.Next(-10, 10)).ToString("yyyy/MM/dd")
   NewRow1("AK-Date") = DateTime.Now.AddDays(Random1.Next(-10, 10)).ToString("yyyy/MM/dd")
   NewRow1("AL-String") = "AL" & (Random1.Next(-10, 10)).ToString
   NewRow1("AM-String") = "AM" & (Random1.Next(-10, 10)).ToString
   NewRow1("AN-フラグ") = Random1.Next(0, 2).ToString
   NewRow1("AQ-フラグ") = Random1.Next(0, 2).ToString
   NewRow1("AS-フラグ") = Random1.Next(0, 2).ToString
   NewRow1("AT-Date") = DateTime.Now.AddDays(Random1.Next(-10, 10)).ToString("yyyy/MM/dd")
   Me.DataTableDGView1.Rows.Add(NewRow1)
  Next
  DataGridView1.DataSource = Me.DataTableDGView1
 End Sub

 Private Function ExcelCellDate(inputDateTime As String) As String
  Try
   Return DateTime.Parse(inputDateTime).ToString("yyyy-MM-dd")
  Catch ex As Exception
   Debug.WriteLine($"エラー: {ex.Message}")
   Return String.Empty
  End Try
 End Function

 Private Function ExcelCellNumeric(numericValue As String) As Double
  Try
   If String.IsNullOrWhiteSpace(numericValue) Then Return 0.0
   Dim result As Double
   If Double.TryParse(numericValue, result) Then Return result
   Debug.WriteLine($"数値変換失敗: 入力値 '{numericValue}' は数値ではありません。")
   Return 0.0
  Catch ex As Exception
   Debug.WriteLine($"エラー発生: {ex.Message}")
   Return 0.0
  End Try
 End Function

 Private Sub ExcelRemoveRows(filePath As String, startRow As Integer, endRow As Integer)
  Debug.WriteLine($"ファイルパス: {filePath}, 削除範囲: {startRow} から {endRow}")
  Using XLWorkbook1 As New XLWorkbook(filePath)
   Dim IXLWorksheet1 As IXLWorksheet = XLWorkbook1.Worksheet("Sheet1")
   Dim delcnt = 0
   For rowIndex As Integer = endRow To startRow Step -1
    delcnt += 1
    IXLWorksheet1.Row(rowIndex).Delete()
    If delcnt >= 50 Then
     XLWorkbook1.Save()
     Application.DoEvents()
     Debug.WriteLine($"50件削除")
     delcnt = 0
    End If
   Next
   XLWorkbook1.Save()
   Debug.WriteLine($"削除完了")
  End Using
 End Sub

 Private Sub ExcelSaveAs(filePath1 As String, filePath2 As String)
  Using XLWorkbook1 As New XLWorkbook(filePath1)
   XLWorkbook1.SaveAs(filePath2)
   Debug.WriteLine($"保存しなおし完了")
  End Using
 End Sub

 Private Sub Put_DataGrid(TempFile As String)
  Dim DataTable1 = New DataTable

  'Headerのセット
  Using XLWorkbook1 As New XLWorkbook(TempFile)
   Dim Worksheet1 = XLWorkbook1.Worksheet(1)
   Dim lastColumn As Integer = Worksheet1.LastColumnUsed().ColumnNumber()
   For col As Integer = 1 To lastColumn
    Dim IXLCell1 As IXLCell = Worksheet1.Cell(1, col)
    Dim headerValue As String = If(IXLCell1.IsEmpty(), String.Empty, IXLCell1.GetValue(Of String)())
    DataTable1.Columns.Add(headerValue)
   Next
  End Using
  DataGridView2.DataSource = DataTable1

  'Bodyのデータは割愛
  MsgBox("Excelファイルへの読み込みが正常に完了しました。")
 End Sub
End Class

ある程度負荷をかけないと落ちてくれないので、列はあまり減らしていない。
任意の場所で作成し、直下にアドしてビルドアクションを埋め込みリソースに変更する。

＜ヘッダ＞
A-Item
【式】="B-Key" & CHAR(10) & COUNTA(B2:B999) & "件"
C-Date
dummy1
dummy2
dummy3
dummy4
dummy5
dummy6
dummy7
dummy8
dummy9
dummy10
dummy11
dummy12
dummy13
dummy14
dummy15
dummy16
dummy17
dummy18
dummy19
dummy20
dummy21
dummy22
dummy23
dummy24
dummy25
dummy26
dummy27
dummy28
【式】="アラート３⇒" & CHAR(10) & COUNTIF(AF2:AF1000,"！") & "件"
AG-String
AH-Date
AI-Date
【式】="アラート１⇒" & CHAR(10) & COUNTIF(AJ2:AJ1000,"！") & "件"
AK-Date
AL-String
AM-String
AN-フラグ
AO-式
AP-式
AQ-フラグ
【式】="アラート２⇒" & CHAR(10) & COUNTIF(AR2:AR1000,"！") & "件"
AS-フラグ
AT-Date


＜ボディ＞
ボディに式があるのは下記の5項目、行2～1000まで埋まっているものとする。

アラート３⇒
=IF(C2+30<TODAY(), IF(LEN(TRIM(AG2)) < 1, TRIM("！"), TRIM("")), TRIM(""))

アラート１⇒
=IF(AK2+0<TODAY(), TRIM("！"), TRIM(""))

AO-式
=IF(TEXT(AS2, "0") <> TRIM("1"), IF(C2 + 7 * 2 < TODAY(), TRIM(1), TRIM(0)), TRIM(0))

AP-式
=IF(TRIM(AT2) = TRIM(""), IF(TRIM(AH2) = TRIM(""), 1, IF(AH2 > TODAY(), 1, 0)), IF(AT2 - 7 * 2 >= TODAY(), 1, 0))

アラート２⇒
=IF((TRIM(AP2) & TRIM(AQ2)) = TRIM("11"), TRIM(""), IF(TRIM(AO2) = "1", TRIM("！"), TRIM("")))

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

起動⇒ページ2にテストデータ
ボタン1⇒TEMP1、TEMP2が作成される
ボタン2⇒TEMP1から読み込めない
ボタン3⇒SaveAsで保存したTEMP2から読み込めない
その後
TEMP1を開きExcelで再保存するとボタン2でページ3にデータが読み込めるのを確認できる
TEMP2を開きExcelで再保存するとボタン2でページ3にデータが読み込めるのを確認できる

補足

■バージョン
vb 2022
.NET Framework 4.8
ClosedXML(0.104.2)

Comments

[t20240712]

Form1 以下を事前にForm1に配置
+-TabControl1
　+-TabPage1
　|　+-Button1 Excel作成(TEMP1:Save|TEMP2:SaveAs)
　|　+-Button2 TEMP1から表示
　|　+-Button3 TEMP2から表示
　+-TabPage2
　|　+-DataGridView1 入力データを視覚的に確認する
　+-TabPage3
　　+-DataGridView2 Excelを読み込んだ内容を表示

[KOZ6.0]

ClosedXML が計算式のすべてを網羅しているわけではありません。
最終的には Excel で開いて確認なり印刷するわけなので、計算式が入ったセルの値を参照しなくて済む方法を考えたほうがいいと思いますよ。

[KOZ6.0]

ReoGrid に読み込ませてみるのも一つの手かもしれません。
https://reogrid.net/jp/
MIT ライセンスで無償で使えます。(サポートは有料)

[t20240712]

＞最終的には Excel で開いて確認なり印刷するわけなので、計算式が入ったセルの値を参照しなくて済む方法を考えたほうがいいと思いますよ。

なるほど、ひな型なので、読んで別の場所に書くのが役割ですが、読むのはシート単位でやって、セル単位で読まないという形で逃げられなくもないです。

Answer 1

こんにちは。

ちゃんと調べていませんが、
計算式セルに対して TRIM していることで即座に文字列への変換処理が走るため、そこで無限に評価が再帰して落ちていそうです。
ぶっちゃけ ClosedXML のバグな気がしますが、計算式セルの参照に対する TRIM をやめれば回避できるかもしれません。

- =IF((TRIM(AP2) & TRIM(AQ2)) = TRIM("11"), TRIM(""), IF(TRIM(AO2) = "1", TRIM("！"), TRIM(""))
+ =IF(AP2 & TRIM(AQ2)) = TRIM("11"), TRIM(""), IF(AO2 = "1", TRIM("！"), TRIM(""))

ところで、トリム処理が不要な文字列リテラルに対する TRIM は全く無駄なので、意図的でないなら省略したほうが良いと思いました。

- =IF((TRIM(AP2) & TRIM(AQ2)) = TRIM("11"), TRIM(""), IF(TRIM(AO2) = "1", TRIM("！"), TRIM(""))
+ =IF((AP2 & TRIM(AQ2)) = "11", "", IF(AO2 = "1", "！", ""))

Comments

[t20240712]

不要なTRIMが多いのは半分わざとです。
エクセルは生のままでは表に出せないので、簡略化したりエッセンス部分だけ出しています。
しかし、シンプルに削ると事象が起こらなくなるので、ダミーフィールを間に作ったり、ｸ ｿみたいな式をあえて残しています。
ｸ ｿみたいなエクセルのひな型が沢山あって、そこに元々手入力していたものをVBを使って放り込むといった事をしようとしています。
もちろん、エクセルの式が悪い場合はエクセル式を直しますが、エクセルで開いて編集したり、エクセルで再保存するといけるレベルの事はClosedXML側で回避したいです。
でないと、ここをTRIMを除いて通しても、他で同じようになることが目に見えてますので。
あるいは、「ClosedXML なんてものは、凝ったエクセルに対応できる代物ではない」という現実であれば、そう認識する必要があります。

[tamoto]

TRIM 限定なのかは分かっていませんが、ClosedXML がそれらを扱う場合の考慮漏れと思われるので、単純に ClosedXML のバグです。
再現ファイルを添えて Issue を立ててバグ報告することをおすすめします。
開発が活発ならすぐ直ると思います。