none
.net OpenXML MS Excel Add Row RRS feed

  • Question

  • 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
    


    Sunday, September 18, 2011 8:37 AM

Answers

  • Hi Polynaux2010,

    After doing some research about your problem, I found it difficult to reproduce the scenario on my side. I have no idea about how to simulate your data and the Excel template.

    I tested the following code on my side which can insert row into Excel sheet:

    using System;
    using System.Linq;
    using System.IO;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
     
     
    namespace OpenXmlExcelFillTablePivotTable
    {
        class Program
        {
            static void Main(string[] args)
            {
                File.Copy("template.xlsx""generated.xlsx"true);
                //Open the copied template workbook. 
                using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open("generated.xlsx"true))
                {
     
                    // Access the main Workbook part, which contains all references.
                    WorkbookPart workbookPart = myWorkbook.WorkbookPart;
                    // get sheet by name
                    Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").FirstOrDefault();
                    
                    if (sheet != null)
                    {
                        // get worksheetpart by sheet id
                        WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id.Value) as WorksheetPart;
                        // get table in Sheet1
                        if (worksheetPart.TableDefinitionParts.Count() != 0)
                        {
                            // The SheetData object will contain all the data.
                            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
     
                            //Connect to database.
                            DataClasses1DataContext db = new DataClasses1DataContext();
     
                            //The data starts at row 2. 
                            int index = 2;
     
                            //Select all rows from SalesTerritory table.
                            var territoryQuery = from t in db.SalesTerritories select t;
     
                            //For each row in the database, add a row to they spreadsheet.
                            foreach (var item in territoryQuery)
                            {
                                string territoryName = item.Name;
                                decimal salesLastYear = Math.Round(item.SalesLastYear, 2);
                                decimal salesThisYear = Math.Round(item.SalesYTD, 2);
                                //Add a new row.
                                Row contentRow = CreateContentRow(index, territoryName, salesLastYear, salesThisYear);
                                index++;
                                //Append new row to sheet data.
                                sheetData.AppendChild(contentRow);
                            }
                        }
                    }
                }
            }
     
            public static string[] headerColumns = new string[] { "A""B""C" };
     
            private static Row CreateContentRow(int index, string territory, decimal salesLastYear, decimal salesThisYear)
            {
                //Create the new row.
                Row r = new Row();
                r.RowIndex = (UInt32)index;
                //First cell is a text cell, so create it and append it.
                Cell firstCell = CreateTextCell(headerColumns[0], territory, index);
                r.AppendChild(firstCell);
                //Create the cells that contain the data.
                for (int i = 1; i < headerColumns.Length; i++)
                {
                    Cell c = new Cell();
                    c.CellReference = headerColumns[i] + index;
                    CellValue v = new CellValue();
                    if (i == 1)
                        v.Text = salesLastYear.ToString();
                    else
                        v.Text = salesThisYear.ToString();
                    c.AppendChild(v);
                    r.AppendChild(c);
                }
                return r;
            }
     
            public static Cell CreateTextCell(string header, string text, int index)
            {
                //Create a new inline string cell.
                Cell c = new Cell();
                c.DataType = CellValues.InlineString;
                c.CellReference = header + index;
                //Add text to the text cell.
                InlineString inlineString = new InlineString();
                Text t = new Text();
                t.Text = text;
                inlineString.AppendChild(t);
                c.AppendChild(inlineString);
                return c;
            }
     
        }
    }
    

    which uses sheetData.AppendChild(contentRow); to append the row data.

    See whether this can help you and try to change it to meet your requirement, just feel free to follow up  after you have tried.

    Wish you a nice day.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by polynaux2010 Monday, September 26, 2011 5:28 PM
    Thursday, September 22, 2011 7:39 AM
  • Thanks a lot for your effort bruce. 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 Bruce Song Wednesday, September 28, 2011 1:20 AM
    Monday, September 26, 2011 5:29 PM

All replies

  • 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 10:23 AM
  • Hi Polynaux2010,

    I am doing the research about your problem.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, September 22, 2011 4:10 AM
  • Hi Polynaux2010,

    After doing some research about your problem, I found it difficult to reproduce the scenario on my side. I have no idea about how to simulate your data and the Excel template.

    I tested the following code on my side which can insert row into Excel sheet:

    using System;
    using System.Linq;
    using System.IO;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
     
     
    namespace OpenXmlExcelFillTablePivotTable
    {
        class Program
        {
            static void Main(string[] args)
            {
                File.Copy("template.xlsx""generated.xlsx"true);
                //Open the copied template workbook. 
                using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open("generated.xlsx"true))
                {
     
                    // Access the main Workbook part, which contains all references.
                    WorkbookPart workbookPart = myWorkbook.WorkbookPart;
                    // get sheet by name
                    Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").FirstOrDefault();
                    
                    if (sheet != null)
                    {
                        // get worksheetpart by sheet id
                        WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id.Value) as WorksheetPart;
                        // get table in Sheet1
                        if (worksheetPart.TableDefinitionParts.Count() != 0)
                        {
                            // The SheetData object will contain all the data.
                            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
     
                            //Connect to database.
                            DataClasses1DataContext db = new DataClasses1DataContext();
     
                            //The data starts at row 2. 
                            int index = 2;
     
                            //Select all rows from SalesTerritory table.
                            var territoryQuery = from t in db.SalesTerritories select t;
     
                            //For each row in the database, add a row to they spreadsheet.
                            foreach (var item in territoryQuery)
                            {
                                string territoryName = item.Name;
                                decimal salesLastYear = Math.Round(item.SalesLastYear, 2);
                                decimal salesThisYear = Math.Round(item.SalesYTD, 2);
                                //Add a new row.
                                Row contentRow = CreateContentRow(index, territoryName, salesLastYear, salesThisYear);
                                index++;
                                //Append new row to sheet data.
                                sheetData.AppendChild(contentRow);
                            }
                        }
                    }
                }
            }
     
            public static string[] headerColumns = new string[] { "A""B""C" };
     
            private static Row CreateContentRow(int index, string territory, decimal salesLastYear, decimal salesThisYear)
            {
                //Create the new row.
                Row r = new Row();
                r.RowIndex = (UInt32)index;
                //First cell is a text cell, so create it and append it.
                Cell firstCell = CreateTextCell(headerColumns[0], territory, index);
                r.AppendChild(firstCell);
                //Create the cells that contain the data.
                for (int i = 1; i < headerColumns.Length; i++)
                {
                    Cell c = new Cell();
                    c.CellReference = headerColumns[i] + index;
                    CellValue v = new CellValue();
                    if (i == 1)
                        v.Text = salesLastYear.ToString();
                    else
                        v.Text = salesThisYear.ToString();
                    c.AppendChild(v);
                    r.AppendChild(c);
                }
                return r;
            }
     
            public static Cell CreateTextCell(string header, string text, int index)
            {
                //Create a new inline string cell.
                Cell c = new Cell();
                c.DataType = CellValues.InlineString;
                c.CellReference = header + index;
                //Add text to the text cell.
                InlineString inlineString = new InlineString();
                Text t = new Text();
                t.Text = text;
                inlineString.AppendChild(t);
                c.AppendChild(inlineString);
                return c;
            }
     
        }
    }
    

    which uses sheetData.AppendChild(contentRow); to append the row data.

    See whether this can help you and try to change it to meet your requirement, just feel free to follow up  after you have tried.

    Wish you a nice day.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by polynaux2010 Monday, September 26, 2011 5:28 PM
    Thursday, September 22, 2011 7:39 AM
  • Thanks a lot for your effort bruce. 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 Bruce Song Wednesday, September 28, 2011 1:20 AM
    Monday, September 26, 2011 5:29 PM