creating Excel file with OpenXML, unreadable content

Question

I am new to and struggling with the OpenXML SDK 2.5, I am trying to create an Excel file from a data table. I used the answer in this example to get started:

Export DataTable to Excel with Open Xml SDK in c#

It runs without error, but the resulting file is unreadable, says "Excel found unreadable content in 'TEST.xlsx.' Do you want to recover the contents of this workbook?"

In Debug, I reviewed the contents of the data table after it was populated and it has the expected number of rows with the expected data in it.

Per some reading on the forums I've tried exporting into a ZIP just to see the structure but it doesn't look right and I'm not sure how to troubleshoot this.

My question is, are there any obvious steps I'm missing in creating the file, or are any of the steps in my code potentially causing the unreadable content?

Thank you in advance for any advice.

Here is my code, and the resulting ZIP file structure:

Imports System
Imports System.IO
Imports System.IO.File
Imports System.Data.SqlClient
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Spreadsheet
Imports DocumentFormat.OpenXml.Packaging

Private Sub CreateXLFile(fileName As String, mstrSQL As String)

    Dim mConn As SqlConnection
    Dim cmd As SqlCommand
    Dim da As SqlDataAdapter
    Dim ds As DataSet = New DataSet
    Dim spreadSheet As SpreadsheetDocument = Nothing
    Dim worksheetPart As WorksheetPart
    Dim sheets As Sheets
    Dim sheet As Sheet
    Dim table As DataTable
    Dim relationshipId As String
    Dim sheetId As UInt16
    Dim headerRow = New Row
    Dim columns = New List(Of String)

    Try
        mConn = New SqlConnection(My.Settings.SqlConnection)
        cmd = New SqlCommand(mstrSQL, mConn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandTimeout = 120
        da = New SqlDataAdapter(cmd)
        da.Fill(ds)
        table = ds.Tables(0)

        If File.Exists(fileName) Then
            File.Delete(fileName)
        End If

        ' Create the Excel workbook
        spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook, False)

        ' Create the parts and the corresponding objects
        ' Workbook
        spreadSheet.AddWorkbookPart()
        spreadSheet.WorkbookPart.Workbook = New Workbook()
        spreadSheet.WorkbookPart.Workbook.Save()

        ' Sheets collection
        spreadSheet.WorkbookPart.Workbook.Sheets = New DocumentFormat.OpenXml.Spreadsheet.Sheets()
        spreadSheet.WorkbookPart.Workbook.Save()

        ' Add the worksheetpart
        worksheetPart = spreadSheet.WorkbookPart.AddNewPart(Of WorksheetPart)()
        worksheetPart.Worksheet = New Worksheet(New SheetData())
        worksheetPart.Worksheet.Save()

        sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)()
        relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(worksheetPart)

        sheetId = 1
        If (sheets.Elements(Of Sheet).Count > 0) Then
            sheetId = sheets.Elements(Of Sheet).Select(Function(s) s.SheetId.Value).Max() + 1
        End If

        ' Add and associate the sheet
        sheet = New Sheet()
        sheet.Id = relationshipId
        sheet.SheetId = sheetId
        sheet.Name = table.TableName
        sheets.Append(sheet)

        ' Add column headers
        For Each column As DataColumn In table.Columns
            columns.Add(column.ColumnName)
            Dim cell = New Cell()
            cell.DataType = CellValues.String
            cell.CellValue = New CellValue(column.ColumnName)
            headerRow.AppendChild(cell)
        Next
        worksheetPart.Worksheet.AppendChild(headerRow)
        worksheetPart.Worksheet.Save()

        For Each dsrow As DataRow In table.Rows
            Dim newRow = New Row()
            For Each col As String In columns
                Dim cell = New Cell()
                cell.DataType = CellValues.String
                cell.CellValue = New CellValue(dsrow(col).ToString())
                newRow.AppendChild(cell)
            Next
            worksheetPart.Worksheet.AppendChild(newRow)
            worksheetPart.Worksheet.Save()
        Next

    Catch ex As Exception
        'do stuff
    Finally
        spreadSheet.Close()
        spreadSheet.Dispose()

    End Try
End Sub

Answer 1

Not sure why the above code doesn't work, but this code does, courtesy of: http://blogs.msdn.com/b/chrisquon/archive/2009/07/22/creating-an-excel-spreadsheet-from-scratch-using-openxml.aspx

Try
        mConn = New SqlConnection(My.Settings.SqlConnection)
        cmd = New SqlCommand(mstrSQL, mConn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandTimeout = 120
        da = New SqlDataAdapter(cmd)
        da.Fill(ds)
        table = ds.Tables(0)

        If File.Exists(fileName) Then
            File.Delete(fileName)
        End If

        ' create the workbook
        spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)
        spreadSheet.AddWorkbookPart()
        spreadSheet.WorkbookPart.Workbook = New Workbook()
        spreadSheet.WorkbookPart.AddNewPart(Of WorksheetPart)()
        spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet = New Worksheet()

        ' create sheet data
        spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(New SheetData())

        ' create header row
        For Each column As DataColumn In table.Columns
            columns.Add(column.ColumnName)
            Dim cell = New Cell()
            cell.DataType = CellValues.String
            cell.CellValue = New CellValue(column.ColumnName)
            headerRow.AppendChild(cell)
        Next
        spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(headerRow)

        ' create data rows
        For Each dsrow As DataRow In table.Rows
            Dim newRow = New Row()
            For Each col As String In columns
                Dim cell = New Cell()
                cell.DataType = CellValues.String
                cell.CellValue = New CellValue(dsrow(col).ToString())
                newRow.AppendChild(cell)
            Next
            spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(newRow)
        Next

        ' save worksheet
        spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.Save()

        ' create the worksheet to workbook relation
        spreadSheet.WorkbookPart.Workbook.AppendChild(New Sheets())
        Dim s = New Sheet()
        s.Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First())
        s.SheetId = 1
        s.Name = "test"
        spreadSheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().AppendChild(s)

        ' save workbook
        spreadSheet.WorkbookPart.Workbook.Save()

    Catch ex As Exception
        'do stuff
    Finally
        spreadSheet.Close()
        spreadSheet.Dispose()

    End Try

Answer 2

Thanks to Mike for the code/answer.

Based on his answer I had to change it to get it working, so wanted to put it down in case others need it.

Obviously you can add any content to the excel doc you want but for the purpose of getting the code working I just used a string().

            Dim filepath As String = My.Application.Info.DirectoryPath & "\Cust.xlsx"

            ' Create a spreadsheet document by supplying the filepath.
            ' By default, AutoSave = true, Editable = true, and Type = xlsx.
            Dim spreadsheetDocument As SpreadsheetDocument =
            spreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook)

            ' Add a WorkbookPart to the document.
            Dim workbookpart As WorkbookPart = spreadsheetDocument.AddWorkbookPart
            workbookpart.Workbook = New Workbook

            ' Add a WorksheetPart to the WorkbookPart.
            Dim worksheetPart As WorksheetPart = workbookpart.AddNewPart(Of WorksheetPart)()
            worksheetPart.Worksheet = New Worksheet(New SheetData())

            ' Add Sheets to the Workbook.
            Dim sheets As Sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(Of Sheets)(New Sheets())

            ' Append a new worksheet and associate it with the workbook.
            Dim sheet As Sheet = New Sheet
            sheet.Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart)
            sheet.SheetId = 1
            sheet.Name = "Sheet1"
            sheets.Append(sheet)

            Dim HeaderString As String = "Customer ID, Name, Address, Address2, Address3, Address4, Address5, Contact Number, EmailAddress, Alt Contact, Notes"

            ' Split string based on spaces.
            Dim HeaderWords As String() = HeaderString.Split(New Char() {","c})

            'create row
            Dim Row = New Row()

            For Each Header In HeaderWords
                'Columns.Add(Column.ColumnName)
                Dim cell = New Cell()
                cell.DataType = CellValues.String
                cell.CellValue = New CellValue(Trim(Header))
                Row.AppendChild(cell)

            Next
            spreadsheetDocument.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(Row)

            Dim DataString As String = "1,2,3,4,5,6,7,8,9,10,11"

            ' Split string based on spaces.
            Dim DataWords As String() = DataString.Split(New Char() {","c})

            ' create data rows
            For Each DataPart In DataWords
                Dim newRow = New Row()
                For Each col As String In HeaderWords
                    Dim cell = New Cell()
                    cell.DataType = CellValues.String
                    cell.CellValue = New CellValue(DataPart.ToString())
                    newRow.AppendChild(cell)
                Next
                spreadsheetDocument.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(newRow)
            Next

            WorkbookPart.Workbook.Save()

            ' Close the document.
            SpreadsheetDocument.Close()