Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# XML Excel .NET4 , +
Hi,
 
I am facing a problem in adding/inserting new/existing rows in between a excel sheet.
Can anyone help me in achieving this ?
 
For example, i need to copy Row# 10 and paste it after Row# 20.
 
Kindly help.
 
Thanks and Regards,
YKK Reddy
Posted 30-Jan-12 2:47am
YKK Reddy1.5K
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Herez sample code:
 
private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;
 
            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }
 
            // If there is not a cell with the specified column name, insert one.
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }
 
                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);
 
                worksheet.Save();
                return newCell;
            }
        }
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

private static Row CreateRow(Row refRow, SheetData sheetData, Row refRow1)
        {
            uint newRowIndex = 0;
            var newRow = new Row() { RowIndex = refRow.RowIndex.Value };
 
            // 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 > refRow.RowIndex.Value);
            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.InsertAt(newRow, 40);
            sheetData.InsertBefore(newRow, refRow);
 
            return newRow;
        }
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 George Jonsson 215
1 Kornfeld Eliyahu Peter 169
2 OriginalGriff 120
3 PIEBALDconsult 110
4 BillWoodruff 85
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 7 Feb 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100