Create a new xlsx file and add multiple sheets

Jul 19, 2011 at 8:13 PM

I have OpenXML procedure that will load and save a single sheet by using the Excel Data Reader.AsDataSet. But when I try and have it loop through multiple sheets it acts like it is working.

But when you try an open Excel file it says it is corrupted.

Vb.Net Code "You will need to download OpenXML 2.0 and set it up in your Visual studio.

    Public Sub CreateNEWSpreadsheetWorkbook(ByVal filepath As String, ByVal ds As DataSet) '<-- ds as the incoming data from Excel Data Reader.AsDataSet
        ' Create a spreadsheet document by supplying the filepath.
        ' By default, AutoSave = true, Editable = true, and Type = xlsx.
        Dim f As New FileInfo(filepath)
        If f.Exists Then
            f.Delete()
        End If

        Dim sheetID As Integer = 1
        Dim cnt As Integer = ds.Tables.Count

        Dim spreadsheetDocument__1 As SpreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook)

        ' Add a WorkbookPart to the document.
        Dim workbookpart As WorkbookPart = spreadsheetDocument__1.AddWorkbookPart()
        workbookpart.Workbook = New Workbook()

        For Each dt As DataTable In ds.Tables
            ' 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__1.WorkbookPart.Workbook.AppendChild(Of Sheets)(New Sheets())

            ' Append a new worksheet and associate it with the workbook.
            Dim sheet As New Sheet() With {.Id = spreadsheetDocument__1.WorkbookPart.GetIdOfPart(worksheetPart), .SheetId = sheetID, .Name = dt.TableName} '<-- Id and Name of incoming sheets

            sheets.Append(sheet)
            Dim cl As String = String.Empty
            Dim row As UInteger = 2
            Dim index As Integer = 0
            Dim cell As Cell = Nothing

            For Each dr As DataRow In dt.Rows
                For idx As Integer = 0 To dt.Columns.Count - 1
                    If idx >= 26 Then
                        cl = "A" & Convert.ToString(Convert.ToChar(65 + idx - 26))
                    Else
                        cl = Convert.ToString(Convert.ToChar(65 + idx))
                    End If
                    Dim shareStringPart As SharedStringTablePart
                    If spreadsheetDocument__1.WorkbookPart.GetPartsOfType(Of SharedStringTablePart)().Count() > 0 Then
                        shareStringPart = spreadsheetDocument__1.WorkbookPart.GetPartsOfType(Of SharedStringTablePart)().First()
                    Else
                        shareStringPart = spreadsheetDocument__1.WorkbookPart.AddNewPart(Of SharedStringTablePart)()
                    End If
                    If row = 2 Then
                        index = InsertSharedStringItem(dt.Columns(idx).ColumnName, shareStringPart)
                        cell = InsertCellInWorksheet(cl, row - 1, worksheetPart)
                        cell.CellValue = New CellValue(index.ToString())
                        cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)
                    End If

                    ' Insert the text into the SharedStringTablePart.
                    index = InsertSharedStringItem(Convert.ToString(dr(idx)), shareStringPart)
                    cell = InsertCellInWorksheet(cl, row, worksheetPart)
                    cell.CellValue = New CellValue(index.ToString())
                    cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)
                Next
                row += 1
            Next
            workbookpart.Workbook.Save()
            sheetID += 1
        Next

        ' Close the document.
        spreadsheetDocument__1.Close()
    End Sub
    Private Function InsertSharedStringItem(ByVal text As String, ByVal shareStringPart As SharedStringTablePart) As Integer

        ' If the part does not contain a SharedStringTable, create one.
        If shareStringPart.SharedStringTable Is Nothing Then
            shareStringPart.SharedStringTable = New SharedStringTable()
        End If
        Dim i As Integer = 0

        ' Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
        For Each item As SharedStringItem In shareStringPart.SharedStringTable.Elements(Of SharedStringItem)()
            If item.InnerText = text Then
                Return i
            End If
            i += 1
        Next

        ' The text does not exist in the part. Create the SharedStringItem and return its index.
        shareStringPart.SharedStringTable.AppendChild(New SharedStringItem(New DocumentFormat.OpenXml.Spreadsheet.Text(text)))
        shareStringPart.SharedStringTable.Save()
        Return i
    End Function

    ''' <summary>
    ''' Given a column name, a row index, and a WorksheetPart,
    ''' Inserts a cell into the worksheet. If the cell already exists, return it.
    ''' </summary>
    ''' <param name="columnName"></param>
    ''' <param name="rowIndex"></param>
    ''' <param name="worksheetPart"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function InsertCellInWorksheet(ByVal columnName As String, ByVal rowIndex As UInteger, ByVal worksheetPart As WorksheetPart) As Cell
        Dim worksheet As Worksheet = worksheetPart.Worksheet
        Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()
        Dim cellReference As String = (columnName + rowIndex.ToString())

        ' If the worksheet does not contain a row with the specified row index, insert one.
        Dim row As Row
        If (sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).Count() <> 0) Then
            row = sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).First()
        Else
            row = New Row()
            row.RowIndex = rowIndex
            sheetData.Append(row)
        End If

        ' If there is not a cell with the specified column name, insert one. 
        If (row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = columnName + rowIndex.ToString()).Count() > 0) Then
            Return row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = cellReference).First()
        Else
            ' Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
            Dim refCell As Cell = Nothing
            For Each cell As Cell In row.Elements(Of Cell)()
                If (String.Compare(cell.CellReference.Value, cellReference, True) > 0) Then
                    refCell = cell
                    Exit For
                End If
            Next

            Dim newCell As Cell = New Cell
            newCell.CellReference = cellReference

            row.InsertBefore(newCell, refCell)
            worksheet.Save()

            Return newCell
        End If
    End Function

Jul 21, 2011 at 4:27 PM

Found the fix to add sheets to a newly created xlsx file from a xls file.
'Global vars
    Private bfirst As Boolean = False

'Use the two functions from above code

'New design of the above Sub and added additional Sub (AddSheet)

   Private Sub CreateNEWSpreadsheetWorkbook(ByVal filepath As String, ByVal ds As DataSet)
        ' Create a spreadsheet document by supplying the filepath.
        ' By default, AutoSave = true, Editable = true, and Type = xlsx.
        Dim f As New FileInfo(filepath)
        If f.Exists Then
            f.Delete()
        End If

        Dim sheetID As UInteger = 1
        Dim spreadsheetDocument As SpreadsheetDocument = Nothing

        ' Add a WorkbookPart to the document.
        Dim workbookpart As WorkbookPart = Nothing
        Dim worksheetPart As WorksheetPart = Nothing
        Dim sheets As Sheets = Nothing
        Dim sheet As New Sheet()

        For Each dt As DataTable In ds.Tables
            If bfirst = False Then
                ' Open the document for editing.
                spreadsheetDocument = spreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook)
                ' Add a WorkbookPart to the document.
                workbookpart = spreadsheetDocument.AddWorkbookPart()
                workbookpart.Workbook = New Workbook()

                ' Add a WorksheetPart to the WorkbookPart.
                ' Dim worksheetPart As WorksheetPart = workbookpart.AddNewPart(Of WorksheetPart)()
                worksheetPart = workbookpart.AddNewPart(Of WorksheetPart)()
                worksheetPart.Worksheet = New Worksheet(New SheetData())
                ' Add Sheets to the Workbook.
                sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(Of Sheets)(New Sheets())
                ' Append a new worksheet and associate it with the workbook.
                sheet = New Sheet() With {.Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), .SheetId = sheetID, .Name = dt.TableName}
                bfirst = True

                sheets.Append(sheet)

                Dim cl As String = String.Empty
                Dim row As UInteger = 2
                Dim index As Integer = 0
                Dim cell As Cell = Nothing

                For Each dr As DataRow In dt.Rows
                    For idx As Integer = 0 To dt.Columns.Count - 1
                        If idx >= 26 Then
                            cl = "A" & Convert.ToString(Convert.ToChar(65 + idx - 26))
                        Else
                            cl = Convert.ToString(Convert.ToChar(65 + idx))
                        End If
                        Dim shareStringPart As SharedStringTablePart
                        If spreadsheetDocument.WorkbookPart.GetPartsOfType(Of SharedStringTablePart)().Count() > 0 Then
                            shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType(Of SharedStringTablePart)().First
                        Else
                            shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart(Of SharedStringTablePart)()
                        End If
                        If row = 2 Then
                            index = InsertSharedStringItem(dt.Columns(idx).ColumnName, shareStringPart)
                            cell = InsertCellInWorksheet(cl, row - 1, worksheetPart)
                            cell.CellValue = New CellValue(index.ToString())
                            cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)
                        End If

                        ' Insert the text into the SharedStringTablePart.
                        index = InsertSharedStringItem(Convert.ToString(dr(idx)), shareStringPart)
                        cell = InsertCellInWorksheet(cl, row, worksheetPart)
                        cell.CellValue = New CellValue(index.ToString())
                        cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)
                    Next
                    row += 1
                Next
                workbookpart.Workbook.Save()
                If bfirst = True Then
                    spreadsheetDocument.Close()
                End If
            Else
                'Add a New Sheet to the newly created .xlsx file
                AddSheet(LinkedDir & pNewFilename, ds, dt)
            End If
        Next
    End Sub

   ' Insert a new worksheet in a spreadsheet document.
    Private Sub AddSheet(ByVal filepath As String, ByVal ds As DataSet, ByVal dt As DataTable)
        ' Open the document for editing.
        Using spreadsheetDocument As SpreadsheetDocument = spreadsheetDocument.Open(filepath, True)
            ' Add a blank WorksheetPart.
            Dim WorksheetPart As WorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart(Of WorksheetPart)()
            WorksheetPart.Worksheet = New Worksheet(New SheetData())

            Dim sheets As Sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild(Of Sheets)()
            Dim relationshipId As String = spreadsheetDocument.WorkbookPart.GetIdOfPart(WorksheetPart)

            ' Get a unique ID for the new worksheet.
            Dim sheetId As UInteger = 1
            If sheets.Elements(Of Sheet)().Count() > 0 Then
                sheetId = 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 New Sheet() With {.Id = relationshipId, .SheetId = sheetId, .Name = dt.TableName}

            sheets.Append(sheet)

            Dim cl As String = String.Empty
            Dim row As UInteger = 2
            Dim index As Integer = 0
            Dim cell As Cell = Nothing

            For Each dr As DataRow In dt.Rows
                For idx As Integer = 0 To dt.Columns.Count - 1
                    If idx >= 26 Then
                        cl = "A" & Convert.ToString(Convert.ToChar(65 + idx - 26))
                    Else
                        cl = Convert.ToString(Convert.ToChar(65 + idx))
                    End If
                    Dim shareStringPart As SharedStringTablePart
                    If spreadsheetDocument.WorkbookPart.GetPartsOfType(Of SharedStringTablePart)().Count() > 0 Then
                        shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType(Of SharedStringTablePart)().First
                    Else
                        shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart(Of SharedStringTablePart)()
                    End If
                    If row = 2 Then
                        index = InsertSharedStringItem(dt.Columns(idx).ColumnName, shareStringPart)
                        cell = InsertCellInWorksheet(cl, row - 1, WorksheetPart)
                        cell.CellValue = New CellValue(index.ToString())
                        cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)
                    End If

                    ' Insert the text into the SharedStringTablePart.
                    index = InsertSharedStringItem(Convert.ToString(dr(idx)), shareStringPart)
                    cell = InsertCellInWorksheet(cl, row, WorksheetPart)
                    cell.CellValue = New CellValue(index.ToString())
                    cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)
                Next
                row += 1
            Next
            ' Close the document.
            spreadsheetDocument.Close()
        End Using
    End Sub