使用OpenXML创建Excel文件，无法读取的内容
我刚刚接触OpenXML SDK 2.5并且努力工作，我试图从数据表创建一个Excel文件。 我在这个例子中使用了答案开始：
使用c＃中的Open Xml SDK将DataTable导出到Excel
它运行时没有错误，但生成的文件不可读，“Excel发现'TEST.xlsx中的内容不可读。 你想恢复这本工作手册的内容吗？“
在调试中，我在填充数据表后检查了数据表的内容，它具有预期数量的行以及预期的数据。
根据论坛上的一些阅读材料，我尝试将其导出到ZIP中以查看结构，但它看起来不正确，我不确定如何对此进行故障排除。
我的问题是，在创建文件时是否存在任何明显的步骤，或者我的代码中的任何步骤是否可能导致无法读取的内容？
提前感谢您的任何建议。
这是我的代码，以及生成的ZIP文件结构：
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
感谢Mike提供的代码/答案。
根据他的回答，我不得不改变它以使其工作，所以想要把它放在别人需要的情况下。
很明显，你可以添加任何内容到你想要的excel文档，但为了让代码工作，我只使用了一个字符串（）。
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()
上一篇: creating Excel file with OpenXML, unreadable content
下一篇: Error while importing a text file and deleting the Range name that start with