title: XLSX file via OpenXml SDK Both Valid and Invalid
body: <p>I have a program which exports a System.Data.DataTable to an XLSX / OpenXml Spreadsheet. Finally have it mostly working. However when opening the Spreadsheet in Excel, Excel complains about the file being invalid, and needing repair, giving this message... </p>
  
  <blockquote>
    <p>We found a problem with some content in . Do you want us to
    try to recover as much as we can? If you trust the source of the
    workbook, clik Yes.</p>
  </blockquote>
  
  <p>If I click Yes, it comes back with this message...</p>
  
  <p><a href="https://i.stack.imgur.com/E3RaF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/E3RaF.png" alt="enter image description here"></a></p>
  
  <p>Clicking the log file and opening that, just shows this... </p>
  
  <pre class="lang-xml prettyprint-override"><code>    &lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?&gt;
      &lt;recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"&gt;
          &lt;logFileName&gt;error268360_01.xml&lt;/logFileName&gt;
          &lt;summary&gt;Errors were detected in file 'C:\Users\aabdi\AppData\Local\Temp\data.20190814.152538.xlsx'&lt;/summary&gt;
          &lt;repairedRecords&gt;
              &lt;repairedRecord&gt;Repaired Records: Cell information from /xl/worksheets/sheet1.xml part&lt;/repairedRecord&gt;
          &lt;/repairedRecords&gt;
      &lt;/recoveryLog&gt; 
  </code></pre>
  
  <p>Obviously, we don't want to deploy this into a production environment like this. So I've been trying to figure out how to fix this. I threw together a quick little sample to validate the XML and show the errors, based on <a href="https://blogs.msdn.microsoft.com/ericwhite/2010/03/04/validate-open-xml-documents-using-the-open-xml-sdk-2-0/" rel="nofollow noreferrer">this link from MSDN</a>. But when I run the program and load the exact same XLSX document that Excel complains about, the Validator comes back saying that the file is perfectly Valid. So I'm not sure where else to go from there.</p>
  
  <p>Any better tools for trying to validate my XLSX XML? Following is the complete code I'm using to generate the XLSX file. (Yes, it's in VB.NET, it's a legacy app.)</p>
  
  <p>If I comment out the line in the <code>For Each dr As DataRow</code> loop, then the XLSX file opens fine in Excel, (just without any data). So it's something with the individual cells, but I'm not really DOING much with them. Setting a value and data type, and that's it.</p>
  
  <p>I also tried replacing the <code>For Each</code> loop in <code>ConstructDataRow</code> with the following, but it still outputs the same "bad" XML...</p>
  
  <pre class="lang-vb prettyprint-override"><code>        rv.Append(
              (From dc In dr.Table.Columns
               Select ConstructCell(
                   NVL(dr(dc.Ordinal), String.Empty),
                   MapSystemTypeToCellType(dc.DataType)
               )
              ).ToArray()
          )
  </code></pre>
  
  <p>Also tried replacing the call to <code>Append</code> with <code>AppendChild</code> for each cell too, but that didn't help either.</p>
  
  <p>The zipped up XLSX file (erroring, with dummy data) is available here:<br>
  <a href="https://drive.google.com/open?id=1KVVWEqH7VHMxwbRA-Pn807SXHZ32oJWR" rel="nofollow noreferrer">https://drive.google.com/open?id=1KVVWEqH7VHMxwbRA-Pn807SXHZ32oJWR</a>  </p>
  
  <p><strong>Full DataTable to Excel XLSX Code</strong>  </p>
  
  <hr>
  
  <pre class="lang-vb prettyprint-override"><code>    #Region " ToExcel "
      &lt;Extension&gt;
      Public Function ToExcel(ByVal target As DataTable) As Attachment
          Dim filename = Path.GetTempFileName()
          Using doc As SpreadsheetDocument = SpreadsheetDocument.Create(filename, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)
              Dim data = New SheetData()
  
              Dim wbp = doc.AddWorkbookPart()
              wbp.Workbook = New Workbook()
              Dim wsp = wbp.AddNewPart(Of WorksheetPart)()
              wsp.Worksheet = New Worksheet(data)
  
              Dim sheets = wbp.Workbook.AppendChild(New Sheets())
              Dim sheet = New Sheet() With {.Id = wbp.GetIdOfPart(wsp), .SheetId = 1, .Name = "Data"}
              sheets.Append(sheet)
  
              data.AppendChild(ConstructHeaderRow(target))
              For Each dr As DataRow In target.Rows
                  data.AppendChild(ConstructDataRow(dr)) '// THIS LINE YIELDS THE BAD PARTS
              Next
  
              wbp.Workbook.Save()
          End Using
  
          Dim attachmentname As String = Path.Combine(Path.GetDirectoryName(filename), $"data.{Now.ToString("yyyyMMdd.HHmmss")}.xlsx")
          File.Move(filename, attachmentname)
          Return New Attachment(attachmentname, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
      End Function
  
      Private Function ConstructHeaderRow(dt As DataTable) As Row
          Dim rv = New Row()
          For Each dc As DataColumn In dt.Columns
              rv.Append(ConstructCell(dc.ColumnName, CellValues.String))
          Next
          Return rv
      End Function
  
      Private Function ConstructDataRow(dr As DataRow) As Row
          Dim rv = New Row()
          For Each dc As DataColumn In dr.Table.Columns
              rv.Append(ConstructCell(NVL(dr(dc.Ordinal), String.Empty), MapSystemTypeToCellType(dc.DataType)))
          Next
          Return rv
      End Function
  
      Private Function ConstructCell(value As String, datatype As CellValues) As Cell
          Return New Cell() With {
          .CellValue = New CellValue(value),
          .DataType = datatype
          }
      End Function
  
      Private Function MapSystemTypeToCellType(t As System.Type) As CellValues
          Dim rv As CellValues
          Select Case True
              Case t Is GetType(String)
                  rv = CellValues.String
              Case t Is GetType(Date)
                  rv = CellValues.Date
              Case t Is GetType(Boolean)
                  rv = CellValues.Boolean
              Case IsNumericType(t)
                  rv = CellValues.Number
              Case Else
                  rv = CellValues.String
          End Select
  
          Return rv
      End Function
      #End Region
  </code></pre>