Mike Mike - 9 days ago 7
Vb.net Question

creating Excel file with OpenXML, unreadable content

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


enter image description here

Answer

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