Search code examples
.netvb.netopenxmlexport-to-excelepplus

LoadFromDataTable With TableStyles creates an Error upon re-opening Excel Workbook

I've gone looking online for this solution and can't find anything that explains my problem.

I am using EPPLus for vb.net (using OfficeOpenXml) and my code looks something like this:

Dim Package As New ExcelPackage
Dim wks As ExcelWorksheet
Dim dt As DataTable

wks.Cells("A1").LoadFromDataTable(dt, True, TableStyles.Medium9)

Dim SaveAs As New FileInfo([...somelocation.xlsx])
Package.SaveAs(SaveAs)

Now, when I run this, it works perfectly well and saves the .xlsx file as wanted / expected. HOWEVER, when I try and open it in Excel, I get an error saying:
"Excel found unreadable content in... Do you want to recover the contents of this workbook?"

If I then click "Yes", It opens it perfectly fine and gives me an error report saying: "Excel was able to open the file by repairing or removing the unreadable content" with lots of erros (one for each datatable loaded): "Repaired Records: Table from /xl/tables/table1.xml part (Table)"

NOW... If I change the line of code from:

wks.Cells("A1").LoadFromDataTable(dt, True, TableStyles.Medium9)

To:

wks.Cells("A1").LoadFromDataTable(dt, True)

Then everything work perfectly well with no errors...

What am I doing wrong?

Solution

  • ... So I figured out what MY problem was and I'm just putting it in here in case anyone is as stupid as I was...

    In my case, I created the Datatable with custom columns names that were split up over 2 lines as follows:

    dt.Columns.Add("First Line's Text" & vbCrLf & "Second Line")

    The problem was the vbCrLf - I changed it to:

    dt.Columns.Add("First Line's Text" & vbLf & "Second Line")

    And the error went away :)

    Hope this saves someone else all the annoyance I went through!!