将DataTable添加到现有的Excel电子表格VBNET中，总是损坏

Question

更新：SheetID似乎是某种东西，现在使用使用标签时，可以在与此SheetId Finder组合中找到其他工作表。

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

这是我用来使其正常工作的代码，现在停止损坏文件：

' Given a document name, inserts a new worksheet.
Public Sub InsertWorksheet(ByVal docName As String, ByVal SQL As DataTable, ByVal sheetName As String, ByVal intSheetId As Integer)
    'Dim sheetName As String
    Dim fileName As String = docName
    ' Open an existing spreadsheet document for editing.
    Dim spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, True)
    Using (spreadSheet)
        ' Add a blank WorksheetPart.
        Dim newWorksheetPart As WorksheetPart = spreadSheet.WorkbookPart.AddNewPart(Of WorksheetPart)()
        newWorksheetPart.Worksheet = New Worksheet(New SheetData())
        ' Create a Sheets object.
        Dim sheets As Sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)()
        Dim relationshipId As String = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart)
        ' Get a unique ID for the new worksheet.
        Dim sheetId As UInteger = 1
        If (sheets.Elements(Of Sheet).Count > 0) Then
            sheetId = CUInt(sheets.Elements(Of Sheet).Select(Function(s) s.SheetId.Value).Max + 1)
        End If
        ' Append the new worksheet and associate it with the workbook.
        Dim sheet As Sheet = New Sheet
        sheet.Id = relationshipId
        sheet.SheetId = sheetId
        sheet.Name = sheetName
        sheets.Append(sheet)
        'get the sheetData object so we can add the data table to it
        Dim sheetData As SheetData = newWorksheetPart.Worksheet.GetFirstChild(Of SheetData)()
        'add the data table
        AddDataTable(SQL, sheetData)
        'save the workbook
        newWorksheetPart.Worksheet.Save()
        ' Close the document.
        spreadSheet.Close()
    End Using
End Sub

---

创建后，文件总是损坏，试图创建一个带有4个工作簿的电子表格，其中包含通过数据表加载的单独数据。文件大小看起来是有效的，创建文件时我没有任何特定的错误。创建文件后不会打开Excel表。

现有的呼叫函数的代码：

    Try
        CreateExcelFileFromDataTable(iExcelFileLoc & ExportFileName, iAGetTable)
    Catch ex As Exception
        Dim ExceptionType As Integer = Type.GetTypeCode(ex.GetType())
        LogMessage(strAppName & "  - Failure : " & iExcelFileLoc & ExportFileName & "  Error:'" & ex.Message & "'  Error Type:'" & CStr(ExceptionType) & "'  Trace:" & ex.StackTrace, TraceEventType.Error)

    End Try
    Dim iBGetTable As DataTable = GetDataTable(SQL_SELECT_DOCUMENTS_TO_FOR_DOCS_NOT_FOUND_IN_DOCNUMS)
    Dim iReportB As String = BuildReportHTML(iBGetTable)
    InsertWorksheet(iExcelFileLoc & ExportFileName, iBGetTable, "Missing Scanned Documents", 2)
    ' ======================================================================

两个功能最初创建Excel，然后一个功能，将带有DataTable的新工作表添加到现有电子表格中。

---

Public Sub InsertWorksheet(ByVal docName As String, ByVal SQL As DataTable, ByVal sheetName As String, ByVal intSheetId As Integer)
    Dim iFinalSheetName As String = ""

    Dim spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)

    Dim newWorksheetPart As WorksheetPart = spreadSheet.WorkbookPart.AddNewPart(Of WorksheetPart)()
    newWorksheetPart.Worksheet = New Worksheet(New SheetData())
    ' Add Sheets to the Workbook.
    Dim sheets As Sheets = spreadSheet.WorkbookPart.Workbook.AppendChild(Of Sheets)(New Sheets())
    Dim relationshipId As String = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart)
    ' Append a new worksheet and associate it with the workbook.
    Dim sheetId As UInteger = 1
    If (sheets.Elements(Of Sheet).Count > 0) Then
        sheetId = CUInt(sheets.Elements(Of Sheet).Select(Function(s) s.SheetId.Value).Max + 1)
    End If
    iFinalSheetName = (sheetName.ToString())
    ' Append the new worksheet and associate it with the workbook.
    Dim sheet As Sheet = New Sheet
    sheet.Id = relationshipId
    sheet.SheetId = CType(intSheetId, UInt32Value)
    sheet.Name = iFinalSheetName
    sheets.Append(sheet)
    'get the sheetData object so we can add the data table to it
    Dim sheetData As SheetData = newWorksheetPart.Worksheet.GetFirstChild(Of SheetData)()
    'add the data table
    AddDataTable(SQL, sheetData)
    'save the workbook
    newWorksheetPart.Worksheet.Save()
    ' Close the document.
    spreadSheet.Close()
End Sub

Public Sub CreateExcelFileFromDataTable(ByVal FilePath As String, myDT As DataTable)
    ' 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 = "Duplicate Document"
    sheets.Append(sheet)
    'get the sheetData object so we can add the data table to it
    Dim sheetData As SheetData = worksheetPart.Worksheet.GetFirstChild(Of SheetData)()
    'add the data table
    'AddDataTable(myDT, sheetData)
    'save the workbook
    workbookpart.Workbook.Save()
    ' Close the document.
    spreadsheetDocument.Close()
    ' -----------------------------------
End Sub

Answer 1

以下是您的选择：

1. 手动创建电子表格的示例。
2. 下载并安装打开的XML SDK工具
3. 打开工具中的电子表格
4. 打开电子表格后，您可以在其中查看.NET源代码以创建看起来像它的表格。

我经常使用该工具。与Office XML SDK一起工作可能很麻烦。