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
不确定为什么上面的代码不起作用，但是这个代码的确如此：http://blogs.msdn.com/b/chrisquon/archive/2009/07/22/creating-an-excel-spreadsheet-from -scratch-使用-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
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()