locked
OpenXML MS Excel Add Row RRS feed

  • 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
    Saturday, September 17, 2011 3:02 AM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 26, 2011 1:31 PM

All replies

  • 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

    Monday, September 19, 2011 6:25 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 26, 2011 1:31 PM