none
Copy and insert Row RRS feed

  • Question

  • Hello.
    I want to copy a row and then insert it after, within SpreadSheetML using Open XML SDK 2.0 API
    Thursday, October 22, 2009 2:31 PM

Answers

  • After removing all Formulas and updating DataValidations, it's work :)

    It's look like this now:

    A main method for getting a row:

    //Get a row to edit
    //Two modes: 
    //1)Insert mode : select-->copy-->move-->Insert
    //2)Update mode : select
    public static Row GetRow(uint rowIndex, WorksheetPart wrksheetPart,ref RowMode Mod)
    {
                Worksheet worksheet = wrksheetPart.Worksheet;
                SheetData sheetData = worksheet.GetFirstChild<SheetData>();
                // If the worksheet does not contain a row with the specified row index, insert one.
                Row row = null;
                if (sheetData.Elements<Row>().Where(r => rowIndex == r.RowIndex).Count() != 0)
                {
                    Row refRow = sheetData.Elements<Row>().Where(r => rowIndex == r.RowIndex).First();
                    if ((refRow != null) && (Mod == RowMode.Insert))
                    {
                        //Copy row from refRow and insert it
                        row = CopyToLine(refRow, rowIndex, sheetData);
                        //Update dataValidation (copy drop down list)
                        DataValidations dvs = worksheet.GetFirstChild<DataValidations>();
                        foreach (DataValidation dv in dvs.Descendants<DataValidation>())
                        {
                            foreach (StringValue sv in dv.SequenceOfReferences.Items)
                            {
                                sv.Value = sv.Value.Replace(row.RowIndex.ToString(), refRow.RowIndex.ToString());
                            }
                        }
                    }
                    else if ((refRow != null) && (Mod == RowMode.Update))
                    {
                        row = refRow;
                    }
                    else
                    {
                        row = new Row() { RowIndex = rowIndex };
                        sheetData.Append(row);
                    }
                }
                else
                {
                    row = new Row() { RowIndex = rowIndex };
                    sheetData.Append(row);
                }
                return row;
    }
    A method for copying a row and insert it:

    //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
    internal static Row CopyToLine(this Row refRow, uint rowIndex, SheetData sheetData)
    {
                uint newRowIndex;
                var newRow = (Row)refRow.CloneNode(true);
                // 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.
                IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= rowIndex);
                foreach (Row row in rows)
                {
                    newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);
    
                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        // Update the references for reserved cells.
                        string cellReference = cell.CellReference.Value;
                        cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
                    }
                    // Update the row index.
                    row.RowIndex = new UInt32Value(newRowIndex);
                }
    
                sheetData.InsertBefore(newRow, refRow);
                return newRow;
    }

    Notes:

    If a refRow contains a drop down list, you have to edit DataValidations references.

    I hope this will help someone.

    Think you Dmitry a lot.
    • Marked as answer by abdelboug Sunday, October 25, 2009 6:31 PM
    Sunday, October 25, 2009 6:30 PM

All replies

  • Hello.
    I want to copy a row and then insert it after, within SpreadSheetML using Open XML SDK 2.0 API
    Hello, to copy row to another line you have to clone your exist row and change their row index. Also don't forget to copy cells of your row and change their cell references. Here is code i use to clone exist row to row with rowIndex.
    internal static Row CopyToLine (this Row row, uint rowIndex)
            {
                var worksheet = row.Ancestors<Worksheet>().First();
                var sheetData = worksheet.Descendants<SheetData>().First();
    
                var newRow = (Row)row.Clone();
    
                newRow.RowIndex = rowIndex;
                foreach (var cell in newRow.Elements<Cell>())
                {
                    cell.CellReference = cell.ColumnName() + rowIndex.ToString();
                }
    
                if (rowIndex == 1)
                {
                    sheetData.InsertAt(newRow, 0);
                }
                else
                {
                    var lastRow = sheetData.Elements<Row>().Last( r => r.RowIndex < rowIndex );
    
                    sheetData.InsertAfter(newRow, lastRow);
                }
                
                worksheet.Save();
    
                return newRow;
            }
     
    Friday, October 23, 2009 7:10 AM
  • Think you very much,
    il will try it now.
    Friday, October 23, 2009 7:18 AM
  •  

    I have change your code little, but i still have a problem with references of moved Rows.

            internal static Row CopyToLine(this Row refRow, uint rowIndex, SheetData sheetData)
            {
                var newRow = (Row)refRow.Clone();
                newRow.RowIndex = rowIndex;
                sheetData.InsertAfter(newRow, refRow);
    
                // 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.
                foreach (Row row in sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= rowIndex))// && r!=newRow))
                {
                    uint newRowIndex;
                    if (row != newRow)
                        newRowIndex = System.
                        Convert.ToUInt32(row.RowIndex.Value + 1);
                    else
                        newRowIndex = newRow.RowIndex;
    
                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        // Update the references for reserved cells.
                        string cellReference = cell.CellReference.Value;
                        cell.CellReference = new StringValue(cellReference.Replace(
                                                             row.RowIndex.Value.ToString(), newRowIndex.ToString()));
                    }
    
                    // Update the row index.
                    row.RowIndex = new UInt32Value(newRowIndex);
                }
                return newRow;
            }
    


    Can any one help me please.

    • Edited by abdelboug Friday, October 23, 2009 10:46 AM Not well formed
    Friday, October 23, 2009 8:14 AM
  • As I have understood, you first insert new row and only then move rows with higher rowINdex down in spreadsheet. I advice you first to move rows down and then paste new row.
    Also may be you have problems because you clone old row, but didn't change cellrefernces in cells in this row. And turn out that there are cells with old cellReference in row with newRowIndex - different row indexes.
    Friday, October 23, 2009 9:05 AM
  • Think you for your answer,
    Now, it's better when I move rows (references indexes) before adding:

    internal static Row CopyToLine(this Row refRow, uint rowIndex, SheetData sheetData)
            {
                uint newRowIndex;
                // 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.
                IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= rowIndex);
                foreach (Row row in rows)
                {
                        newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);
    
                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        // Update the references for reserved cells.
                        string cellReference = cell.CellReference.Value;
                        cell.CellReference = new StringValue(cellReference.Replace(
                          row.RowIndex.Value.ToString(), newRowIndex.ToString()));
                    }
                    // Update the row index.
                    row.RowIndex = new UInt32Value(newRowIndex);
                }
    
                var newRow = (Row)refRow.Clone();
                newRow.RowIndex = rowIndex;
    
                sheetData.InsertBefore(newRow, refRow);
                return newRow;
            }

    But, I still have a problem with references to formulas of the moved rows. 
    I have a problem also with style of the cloned row which has not been copied to the inserted row.

    Sorry my English is not perfect,
    Thank you in advance.
    Friday, October 23, 2009 10:42 AM
  • I'm not englishmen too :))
    I didn't work with formula and advice you to read book Open Xml by Wouter van Vugt.

    About styles. If you have a row with style formatting and you clone it down, you have to copy styles too.
     Let see this code

    //"row" is your old row
    foreach (Cell c in newRow.Descendants<Cell>()) { c.StyleIndex = row.Elements<Cell>(). First( s => s.ColumnName() == c.ColumnName() ).StyleIndex; }


    Friday, October 23, 2009 11:49 AM
  • Think you Dmitry for your answers. your english, i think is better than mine :) :)

    I have change the method like this :

            internal static Row CopyToLine(this Row refRow, uint rowIndex, SheetData sheetData)
            {
                Cell newCell;
                uint newRowIndex;
                // 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.
                IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= rowIndex);
                foreach (Row row in rows)
                {
                    newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);
    
                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        // Update the references for reserved cells.
                        string cellReference = cell.CellReference.Value;
                        cell.CellReference = new StringValue(cellReference.Replace(
                          row.RowIndex.Value.ToString(), newRowIndex.ToString()));
                    }
    
                    // Update the row index.
                    row.RowIndex = new UInt32Value(newRowIndex);
                }
    
                var newRow = (Row)refRow.Clone();
                newRow.RowIndex = rowIndex;
                //Copy indexes
                foreach (Cell c in newRow.Descendants<Cell>())
                {
                    newCell = refRow.Elements<Cell>().First(s => Helper.getAlphaFromString(s.CellReference) == Helper.getAlphaFromString(c.CellReference));
                    if (newCell==null) continue;
                    c.StyleIndex = newCell.StyleIndex;
                }
    
                sheetData.InsertBefore(newRow, refRow);
                return newRow;
            }

    But the styles still not the same between the copied row and the original row.


    Notes :

    -I found that the copied row cells have already the same "IndexStyle" that the original row cells (without the //Copy indexes).
    -The function getAlphaFromString() just get me the "ColumnName()".

    Think you in advance.

    • Edited by abdelboug Friday, October 23, 2009 1:09 PM Rectification
    Friday, October 23, 2009 1:07 PM
  • Have you done?
    worksheet.Save();
    
    Friday, October 23, 2009 1:33 PM
  • Yes I do.

    I have those errors:

    Records deleted: information in the cell /xl/worksheets/sheet3.xml
    Records deleted: Formula in part /xl/calcChain.xml (Properties calculation)


    Maybe i have to delete all formulas in the copied row cells before performing a copy.


    Think you in advance.
    Friday, October 23, 2009 1:47 PM
  • I have tried this issue :

    http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/thread/f5cf04d9-5173-4516-8cba-5ff26c7ea712 

    It's still not work for me.
    I don't understand that we don't have to specify a cell or row reference for the inserted row.

    Think you in advance.
    Friday, October 23, 2009 2:21 PM
  • May be when you move our cells down you have to edit formula element in cells. But formulas can be stored in shared formulas. You must to see the xml code of your file in reflector and find how formulas are stored. Also i can advice you to save file with errors and find this error in xml code by reflector. Now i don't why problems appears((
    Friday, October 23, 2009 7:10 PM
  • After removing all Formulas and updating DataValidations, it's work :)

    It's look like this now:

    A main method for getting a row:

    //Get a row to edit
    //Two modes: 
    //1)Insert mode : select-->copy-->move-->Insert
    //2)Update mode : select
    public static Row GetRow(uint rowIndex, WorksheetPart wrksheetPart,ref RowMode Mod)
    {
                Worksheet worksheet = wrksheetPart.Worksheet;
                SheetData sheetData = worksheet.GetFirstChild<SheetData>();
                // If the worksheet does not contain a row with the specified row index, insert one.
                Row row = null;
                if (sheetData.Elements<Row>().Where(r => rowIndex == r.RowIndex).Count() != 0)
                {
                    Row refRow = sheetData.Elements<Row>().Where(r => rowIndex == r.RowIndex).First();
                    if ((refRow != null) && (Mod == RowMode.Insert))
                    {
                        //Copy row from refRow and insert it
                        row = CopyToLine(refRow, rowIndex, sheetData);
                        //Update dataValidation (copy drop down list)
                        DataValidations dvs = worksheet.GetFirstChild<DataValidations>();
                        foreach (DataValidation dv in dvs.Descendants<DataValidation>())
                        {
                            foreach (StringValue sv in dv.SequenceOfReferences.Items)
                            {
                                sv.Value = sv.Value.Replace(row.RowIndex.ToString(), refRow.RowIndex.ToString());
                            }
                        }
                    }
                    else if ((refRow != null) && (Mod == RowMode.Update))
                    {
                        row = refRow;
                    }
                    else
                    {
                        row = new Row() { RowIndex = rowIndex };
                        sheetData.Append(row);
                    }
                }
                else
                {
                    row = new Row() { RowIndex = rowIndex };
                    sheetData.Append(row);
                }
                return row;
    }
    A method for copying a row and insert it:

    //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
    internal static Row CopyToLine(this Row refRow, uint rowIndex, SheetData sheetData)
    {
                uint newRowIndex;
                var newRow = (Row)refRow.CloneNode(true);
                // 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.
                IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= rowIndex);
                foreach (Row row in rows)
                {
                    newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);
    
                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        // Update the references for reserved cells.
                        string cellReference = cell.CellReference.Value;
                        cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
                    }
                    // Update the row index.
                    row.RowIndex = new UInt32Value(newRowIndex);
                }
    
                sheetData.InsertBefore(newRow, refRow);
                return newRow;
    }

    Notes:

    If a refRow contains a drop down list, you have to edit DataValidations references.

    I hope this will help someone.

    Think you Dmitry a lot.
    • Marked as answer by abdelboug Sunday, October 25, 2009 6:31 PM
    Sunday, October 25, 2009 6:30 PM
  • Hi Abdelboug,

    My template is like this,

    I have a header row which has column headings   => Row 1

    I have data row => Row 2

    I have a footer row in which i will calculate the sum total.   => Row 3

    Using the above template,   i need to generate output as,

    After row 1, before row 3, I have to insert data from a datatable.  (i.e) Dynamically i need to insert multiple rows (i.e) many row.

    Breaking my head to acheive this for past days,

     Please help.

    P Saravanan

    Wednesday, June 2, 2010 12:10 PM
  • dear s4sarav,

    Have you finished filling the template? I meet the same problem, the formula in sum total row should be changed  according to data lines that are dynamically inserted. I have no idea how to do this, plase help.

    Monday, July 30, 2018 4:06 PM