Okay, I'm generating an Excel file from a DataTable. I generate the file and save it, no compilation or run-time errors. When I open the file in Excel though, it pops up the message:
We found a problem with some content in 'filename.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.
So I click Yes
. After a second or two, it comes up with the following message:
Excel was able to open the file by repairing or removing the unreadable content.
Repaired Records: Format from /xl/styles.xml (Styles)
Click to view log file listing repairs:C:\file\path\filename.xml
but if you click through to open the log, it just says the same thing basically, with no additional details.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error336080_01.xml</logFileName>
<summary>Errors were detected in file 'C:\my\file\path\data.xlsx'</summary>
<repairedRecords>
<repairedRecord>Repaired Records: Format from /xl/styles.xml part (Styles)</repairedRecord>
</repairedRecords>
</recoveryLog>
So, I figured Okay... I just found Microsoft's nifty little OOXML SDK Validator/Compare tool. So I open up the "bad" file in that, and run a Validate
. It comes back with full success, and indicates that there are no errors in the file. So I'm not really sure what Excel is complaining about.
Additionally, after allowing Excel to "repair", and then finish opening the file, the worksheet is all styled and shown properly, and all the data filled in, and looks exactly as it is expected to look.
Here is my code that I am using to generate the OOXML stylesheet...
(Yes, it's VB.NET, it's a legacy app.)
Private Function ConstructStyleSheet() As Stylesheet
Dim rv As Stylesheet = New Stylesheet()
rv.AppendChild(New NumberingFormats(
New NumberingFormat() With {.NumberFormatId = 0, .FormatCode = "General"},
New NumberingFormat() With {.NumberFormatId = 5, .FormatCode = "MM/dd/yyyy HH:mm:ss"}
))
rv.AppendChild(New Fonts(
New Font(),
New Font(New Bold())
))
rv.AppendChild(New Borders(
New Border(),
New Border(New BottomBorder(New Color() With {.Auto = True}) With {.Style = BorderStyleValues.Thin})
))
'// COMMENTING OUT THIS BLOCK (BUT LEAVING ALL ABOVE) YIELDS AN XLSX WITH NO ERRORS
'// BUT OF COURSE, NO STYLING ON ANY CELLS, EITHER
rv.AppendChild(New CellFormats(
New CellFormat() With {.FontId = 0, .ApplyFont = True},
New CellFormat() With {.FontId = 1, .BorderId = 1, .ApplyFont = True, .ApplyBorder = True},
New CellFormat() With {.FontId = 0, .ApplyFont = True, .NumberFormatId = 5, .ApplyNumberFormat = True}
))
Return rv
End Function
And here is the content of the /xl/styles.xml
stylesheet...
<?xml version="1.0" encoding="utf-8"?>
<x:styleSheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:numFmts>
<x:numFmt numFmtId="0" formatCode="General" />
<x:numFmt numFmtId="5" formatCode="MM/dd/yyyy HH:mm:ss" />
</x:numFmts>
<x:fonts>
<x:font />
<x:font>
<x:b />
</x:font>
</x:fonts>
<x:borders>
<x:border />
<x:border>
<x:bottom style="thin">
<x:color auto="1" />
</x:bottom>
</x:border>
</x:borders>
<x:cellXfs>
<x:xf fontId="0" applyFont="1" />
<x:xf fontId="1" borderId="1" applyFont="1" applyBorder="1" />
<x:xf numFmtId="5" fontId="0" applyNumberFormat="1" applyFont="1" />
</x:cellXfs>
</x:styleSheet>
Tracked this one down after some work too, and posting the answer here for posterity.
Turns out Excel NEEDS you to put Fill Styles in the output file, even if you aren't using any fill styles in any of your cells.
Private Function ConstructStyleSheet() As Stylesheet
Dim rv As Stylesheet = New Stylesheet()
rv.AppendChild(New NumberingFormats(
New NumberingFormat() With {.NumberFormatId = 5, .FormatCode = "mm/dd/yyyy hh:mm:ss"}
) With {.Count = 1})
rv.AppendChild(New Fonts(
New Font(),
New Font(New Bold())
) With {.Count = 2})
'// ===== NEW SECTION =====
rv.AppendChild(New Fills(
New Fill(New PatternFill() With {.PatternType = PatternValues.None}),
New Fill(New PatternFill() With {.PatternType = PatternValues.Gray125})
) With {.Count = 2})
'\\ =======================
rv.AppendChild(New Borders(
New Border(),
New Border(New BottomBorder(New Color() With {.Auto = True}) With {.Style = BorderStyleValues.Thin})
) With {.Count = 2})
'// ===== THEN ALSO ADD THE .FillId = 0 ON ALL OF THE CellFormats
rv.AppendChild(New CellFormats(
New CellFormat() With {.FillId = 0, .BorderId = 0, .FontId = 0, .NumberFormatId = 0},
New CellFormat() With {.FillId = 0, .ApplyBorder = True, .ApplyFont = True, .BorderId = 1, .FontId = 1, .NumberFormatId = 0},
New CellFormat() With {.FillId = 0, .ApplyNumberFormat = True, .BorderId = 0, .NumberFormatId = 5, .FontId = 0, .ApplyFont = True}
) With {.Count = 3})
Return rv
End Function
After these two questions, clear moral of the story is that Excel (and I'm guessing the other Office apps too?) is EXTREMELY picky about the XML put out in the XLSX file, and you need to just spend a LOT of time debugging and tracking down stupid little things like this, even if they are things you don't actually need in your file.