OpenXML MS Excel Add Row

Question

User507956310 posted

Hello Guys

Below is a complete class. When running the code it fails at line sheetData.InsertBefore(newRow, refRow). With the error: Operation is not valid due to the current state of the object. Why does the cell insertion work fine but the row insertion not? Do you know of any other methods to insert rows into the MS excel spreadsheet?

Imports System.Data.SqlClient
Imports System.Net.Mail
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Imports System.IO

Public Class BKZ_Mailer
    Inherits System.Web.UI.Page
    Dim DA_Layer As New AFT_PPS_R2.DA_Layer

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Response.ClearHeaders()
        Response.ClearContent()
        Response.Clear()
        Response.Buffer = True

        'Open MS Excel template
        Dim sourceBytes() As Byte = File.ReadAllBytes(Me.MapPath("~\TemplateDocs\GF_Statistik_Excel97_2003Template.xlsx"))
        Dim ms As New MemoryStream
        ms.Write(sourceBytes, 0, sourceBytes.Length)

        Using myWorkbook As SpreadsheetDocument = SpreadsheetDocument.Open(ms, True)
            Dim wbPart As WorkbookPart = myWorkbook.WorkbookPart
            ' Dim sheet = wbPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = "Einnahmen").FirstOrDefault
            'Dim worksheetPart As WorksheetPart = DirectCast(myWorkbook.WorkbookPart.GetPartById(sheet.id), WorksheetPart)
            Dim worksheetPart As WorksheetPart = wbPart.WorksheetParts.First()


            Dim Lagerwert As Data.DataTable = DA_Layer.GesamtLagerwert()
            Dim GesamtVerkauft As Data.DataTable = DA_Layer.GesamtVerkauft()


            Dim i As Integer = 0
            For Each Row As DataRow In Lagerwert.Rows
                Dim PositionenCellBestellJahr As Cell
                PositionenCellBestellJahr = InsertCellInWorksheet("F", 10 + i, worksheetPart)
                PositionenCellBestellJahr.DataType = New EnumValue(Of CellValues)(CellValues.String)
                PositionenCellBestellJahr.CellValue = New CellValue() With {.Text = Lagerwert.Rows(0).Item("BestellJahr").ToString()}

                'The worksheet XML files contain one or more block level elements such as SheetData. 
                'sheetData represents the cell table and contains one or more Row elements. 
                'A row contains one or more Cell elements. Each cell contains a CellValue 

                Dim refRow As New Spreadsheet.Row()
                refRow.RowIndex = i
                CopyToLine(refRow, i, worksheetPart)

                '--------------------------------------------------------------------------

                i = i + 1


                '------------------------------------------------------------------------
            Next

            Dim weekNum As Integer = System.Threading.Thread.CurrentThread.CurrentCulture.Calendar.GetWeekOfYear(DateTime.Now, System.Globalization.CalendarWeekRule.FirstDay, DayOfWeek.Sunday)

            Response.ContentType = "application/vnd.ms-excel"
            Response.AddHeader("Content-Disposition", String.Format("attachment;filename={0}", "AFT_BKZ_KW_" + CStr(weekNum) + ".xlsx"))

            myWorkbook.Close()
            ms.WriteTo(Response.OutputStream)
            Response.Flush()
            Response.End()
        End Using
    End Sub





    'Copy an existing row and insert it
    'We don't need to copy styles of a refRow because a CloneNode() or Clone() methods do it for us
    Friend Shared Function CopyToLine(ByVal refRow As Row, ByVal rowIndex As UInteger, ByVal worksheetPart As WorksheetPart) As Row
        Dim worksheet As Worksheet = worksheetPart.Worksheet
        Dim sheetData As SheetData = Worksheet.GetFirstChild(Of SheetData)()
        Dim newRowIndex As UInteger
        Dim newRow = DirectCast(refRow.CloneNode(True), Row)
        ' Loop through all the rows in the worksheet with higher row 
        ' index values than the one you just added. For each one,
        ' increment the existing row index.
        Dim rows As IEnumerable(Of Row) = sheetData.Descendants(Of Row)().Where(Function(r) r.RowIndex.Value >= rowIndex)
        For Each row As Row In rows
            newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1)

            For Each cell As Cell In row.Elements(Of Cell)()
                ' Update the references for reserved cells.
                Dim cellReference As String = cell.CellReference.Value
                cell.CellReference = New StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()))
            Next
            ' Update the row index.
            row.RowIndex = New UInt32Value(newRowIndex)
        Next

        sheetData.InsertBefore(newRow, refRow)
        Return newRow
    End Function

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

End Class

Answer 1

User507956310 posted

Hi Guys,
I followed those two examples:

http://msdn.microsoft.com/en-us/library/dd452407%28v=office.12%29.aspx
using function CreateContentRow


Secondly, I used the XLDeleteRow (named it XLInsertRow and changed from remove to append) function from the official OpenXML code snippets
(amazing that there seems to be now XLInsertRow code snippet :/
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=17985

Non works.
Does someone have an idea?


Imports System.Data.SqlClient
Imports System.Net.Mail
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Imports System.IO

Public Class BKZ_Mailer
    Inherits System.Web.UI.Page
    Dim DA_Layer As New AFT_PPS_R2.DA_Layer

    Private Property deletingRow As Object

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Response.ClearHeaders()
        Response.ClearContent()
        Response.Clear()
        Response.Buffer = True

        'Open MS Excel template
        Dim sourceBytes() As Byte = File.ReadAllBytes(Me.MapPath("~\TemplateDocs\GF_Statistik_Excel97_2003Template.xlsx"))
        Dim ms As New MemoryStream
        ms.Write(sourceBytes, 0, sourceBytes.Length)

        Using myWorkbook As SpreadsheetDocument = SpreadsheetDocument.Open(ms, True)
            Dim wbPart As WorkbookPart = myWorkbook.WorkbookPart
            ' Dim sheet = wbPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = "Einnahmen").FirstOrDefault
            'Dim worksheetPart As WorksheetPart = DirectCast(myWorkbook.WorkbookPart.GetPartById(sheet.id), WorksheetPart)
            Dim worksheetPart As WorksheetPart = wbPart.WorksheetParts.First()


            Dim Lagerwert As Data.DataTable = DA_Layer.GesamtLagerwert()
            Dim GesamtVerkauft As Data.DataTable = DA_Layer.GesamtVerkauft()


            Dim i As Integer = 0
            For Each Row As DataRow In Lagerwert.Rows
              

                'The worksheet XML files contain one or more block level elements such as SheetData.
                'sheetData represents the cell table and contains one or more Row elements.
                'A row contains one or more Cell elements. Each cell contains a CellValue
                ' The SheetData object will contain all the data.
                Dim sheetData As SheetData = worksheetPart.Worksheet.GetFirstChild(Of SheetData)()
                Dim contentRow As Row = CreateContentRow(13)
                sheetData.AppendChild(contentRow)


                XLInsertRow(myWorkbook, wbPart, "GF_Statistik_Excel97_2003Template.xlsx", "Einnahmen", 13)
                '--------------------------------------------------------------------------

                i = i + 1


                '------------------------------------------------------------------------
            Next

            Dim weekNum As Integer = System.Threading.Thread.CurrentThread.CurrentCulture.Calendar.GetWeekOfYear(DateTime.Now, System.Globalization.CalendarWeekRule.FirstDay, DayOfWeek.Sunday)

            Response.ContentType = "application/vnd.ms-excel"
            Response.AddHeader("Content-Disposition", String.Format("attachment;filename={0}", "AFT_BKZ_KW_" + CStr(weekNum) + ".xlsx"))

            myWorkbook.Close()
            ms.WriteTo(Response.OutputStream)
            Response.Flush()
            Response.End()
        End Using
    End Sub



    'Private headerColumns As String() = New String() {"A", "B", "C"}
    Private Function CreateContentRow(ByVal index As Integer) As Row
        'Create the new row.
        Dim r As New Row()
        Dim newRowIndex As UInteger = CUInt(index)
        r.RowIndex = New UInt32Value(newRowIndex)
        Return r
    End Function






    Public Sub XLInsertRow(ByVal document As SpreadsheetDocument, ByVal wbPart As WorkbookPart, ByVal docName As String, ByVal worksheetName As String, ByVal rowIndex As Integer)
        ' Find the correct worksheet, given its name.
        Dim sheet = _
          wbPart.Workbook.Descendants(Of Sheet)(). _
          Where(Function(s) s.Name = worksheetName).FirstOrDefault
        If sheet Is Nothing Then
            ' The specified worksheet does not exist.
            Return
        End If

        Dim worksheetPart As WorksheetPart = CType(wbPart.GetPartById(sheet.Id), WorksheetPart)
        '' In the worksheet, retrieve a collection of rows where the
        '' row index matches the row you're trying to delete. Although you
        '' know there's only one such row, the Descendants method returns
        '' a collection.
        Dim insertingRow = _
          worksheetPart.Worksheet.Ancestors(Of Row)(). _
          Where(Function(r) r.RowIndex.Value = rowIndex).FirstOrDefault()
        If insertingRow Is Nothing Then
            ' The specified row does not exist.
            Return
        End If
        ' Dim insertingRow As New DocumentFormat.OpenXml.Spreadsheet.Row
        ' insertingRow.RowIndex = 12
        insertingRow.Append()

        ' Loop through all the rows in the worksheet with higher row
        ' index values than the one you just added. For each one,
        ' decrement the existing row index.
        For Each row As Row In worksheetPart.Worksheet.Descendants(Of Row)(). _
          Where(Function(r) r.RowIndex.Value > rowIndex)

            Dim newRowIndex As UInteger = CUInt(row.RowIndex.Value - 1)

            For Each cell As Cell In row.Elements(Of Cell)()
                ' Update the references for reserved cells.
                Dim cellReference As String = cell.CellReference.Value
                cell.CellReference = New StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()))
            Next

            ' Update the row index.
            row.RowIndex = New UInt32Value(newRowIndex)
        Next

        ' Save the worksheet part.
        worksheetPart.Worksheet.Save()

        ' Clean up the shared string table.
        ' Loop through each shared string ID in the added row,
        ' find the string in the shared string table and delete it if it's
        ' not used in any other cell.
        For Each sharedStringId As Integer In _
          insertingRow.Elements(Of Cell)(). _
          Where(Function(c) c.DataType IsNot Nothing AndAlso c.DataType.Value = CellValues.SharedString). _
          Select(Function(r) Integer.Parse(r.CellValue.Text))

            RemoveSharedStringItem(sharedStringId, document)
        Next

        ' End Using
    End Sub

    ' Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer
    ' reference the specified SharedStringItem and removes the item.
    Private Sub RemoveSharedStringItem(ByVal shareStringId As Integer, ByVal document As SpreadsheetDocument)
        Dim remove As Boolean = True
        Dim wbPart As WorkbookPart = document.WorkbookPart
        ' If the workbook part is missing, something is terribly
        ' wrong, but it can't hurt to check.
        If wbPart Is Nothing Then
            Return
        End If

        For Each part As WorksheetPart In wbPart.GetPartsOfType(Of WorksheetPart)()
            Dim worksheet As Worksheet = part.Worksheet

            For Each cell As Cell In worksheet.GetFirstChild(Of SheetData)().Descendants(Of Cell)()
                ' Verify if other cells in the document reference the item.
                If (Not (cell.DataType) Is Nothing AndAlso _
                    cell.DataType.Value = CellValues.SharedString AndAlso _
                    cell.CellValue.Text = shareStringId.ToString()) Then

                    ' Other cells in the document still reference the item. Do not remove the item.
                    remove = False
                    Exit For
                End If
            Next

            If Not remove Then
                Exit For
            End If
        Next

        ' Other cells in the document do not reference the item. Remove the item.
        If remove Then
            Dim sharedStringTablePart As SharedStringTablePart = wbPart.SharedStringTablePart
            If sharedStringTablePart Is Nothing Then
                Return
            End If

            Dim item As SharedStringItem = _
              sharedStringTablePart.SharedStringTable. _
              Elements(Of SharedStringItem)().ElementAtOrDefault(shareStringId)
            If item IsNot Nothing Then
                item.Remove()

                ' Refresh all the shared string references.
                For Each part As WorksheetPart In wbPart.GetPartsOfType(Of WorksheetPart)()
                    Dim worksheet As Worksheet = part.Worksheet

                    ' Loop through each cell. If the cell contains a shared string,
                    ' and if the index of the shared string is greater than the index
                    ' of the one you just added, decrement the index to match
                    ' the new index of the shared string.
                    For Each cell As Cell In worksheet.GetFirstChild(Of SheetData)().Descendants(Of Cell)()
                        If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
                            Dim itemIndex As Integer = Integer.Parse(cell.CellValue.Text)
                            If itemIndex > shareStringId Then
                                cell.CellValue.Text = (itemIndex - 1).ToString()
                            End If
                        End If
                    Next

                    worksheet.Save()
                Next
                sharedStringTablePart.SharedStringTable.Save()
            End If
        End If
    End Sub






    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



End Class

Answer 2

User507956310 posted

Calling the following sub every time I want to insert a row, did the trick:

 Public Shared Sub InsertRow(ByVal worksheetPart As WorksheetPart, ByVal LastRowofSection As Integer)
        Dim sheetData As SheetData = worksheetPart.Worksheet.GetFirstChild(Of SheetData)()
        Dim lastRow As Row = sheetData.Elements(Of Row)().LastOrDefault()
        sheetData.InsertAfter(New Row() With {.RowIndex = (LastRowofSection)}, lastRow)
    End Sub