none
How to insert the row in exisiting template in open xml RRS feed

  • Question

  • I have a scenario to generate the Excel sheet based on the existing template, pls refere the attached screen short, here i have two rows, but if i want to display the records in more than 2 ros then i want to insert the row dynamically, but i tried to do that, it's nto working, please any body help me to solve this problem
    public void InsertRow(string sheetName,WorkbookPart wbPart, uint rowIndex)
            {
                Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where((s) => s.Name == sheetName).FirstOrDefault();
    
                if (sheet != null)
                {
                    
                    Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
                    SheetData sheetData = ws.WorksheetPart.Worksheet.GetFirstChild<SheetData>();
                    Row refRow = GetRow(sheetData, rowIndex);
                    ++rowIndex;
                    Row newRow = new Row()
                    {
                        RowIndex = rowIndex
                    };
                    sheetData.InsertAfter(newRow, refRow);
                    //sheetData.InsertAfterSelf(refRow);
                    
                    ws.Save();
                }
            }
    
    private Row GetRow(SheetData wsData, UInt32 rowIndex)
            {
                var row = wsData.Elements<Row>().
                Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
                if (row == null)
                {
                    row = new Row();
                    row.RowIndex = rowIndex;
                    wsData.Append(row);
                }
                return row;
            }

     
    • Moved by Fred Bao Thursday, July 17, 2014 2:00 AM Office Related
    Wednesday, July 16, 2014 7:18 AM

Answers

  • Hi,

    Firstly, you need to append a cell in this new row so that this row will be available.

    Cell cell1 = new Cell() { CellReference = "A" + rowIndex };
    CellValue cellValue1 = new CellValue();
    cellValue1.Text = "";
    cell1.Append(cellValue1);
    Row newRow = new Row()
    {
        RowIndex = rowIndex
    };
    newRow.Append(cell1);

    Secondly, if you insert a new row in the middle of a worksheet, it will effect the following rows and cells since every row has rowindex and every cell has cell reference (it means where the cell is).

    So you need to loop all rows and cells under the inserted row, change its rowindex and cellreference.

    Here is the whole solution for your reference:

    static void InsertRow(string sheetName, WorkbookPart wbPart, uint rowIndex)
            {
                Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where((s) => s.Name == sheetName).FirstOrDefault();
    
                if (sheet != null)
                {
                    Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
                    SheetData sheetData = ws.WorksheetPart.Worksheet.GetFirstChild<SheetData>();
                    Row refRow = GetRow(sheetData, rowIndex);
                    ++rowIndex;
    
                    Cell cell1 = new Cell() { CellReference = "A" + rowIndex };
                    CellValue cellValue1 = new CellValue();
                    cellValue1.Text = "";
                    cell1.Append(cellValue1);
                    Row newRow = new Row()
                    {
                        RowIndex = rowIndex
                    };
                    newRow.Append(cell1);
                    for (int i = (int)rowIndex; i <= sheetData.Elements<Row>().Count(); i++)
                    {
                        var row = sheetData.Elements<Row>().Where(r => r.RowIndex.Value == i).FirstOrDefault();
                        row.RowIndex++;
                        foreach (Cell c in row.Elements<Cell>())
                        {
                            string refer = c.CellReference.Value;
                            int num = Convert.ToInt32(Regex.Replace(refer, @"[^\d]*", ""));
                            num++;
                            string letters = Regex.Replace(refer, @"[^A-Z]*", "");
                            c.CellReference.Value = letters + num;
                        }
                    }
                    sheetData.InsertAfter(newRow, refRow);
                    //ws.Save();
                }
            }
    
            static Row GetRow(SheetData wsData, UInt32 rowIndex)
            {
                var row = wsData.Elements<Row>().
                Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
                if (row == null)
                {
                    row = new Row();
                    row.RowIndex = rowIndex;
                    wsData.Append(row);
                }
                return row;
            }

    I use regular expression to separate letters and number, increase number and join them agian. Don't forget to add a namespace:

    using System.Text.RegularExpressions;

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 18, 2014 8:11 AM
    Moderator

All replies

  • Hello,

    Since you are using OpenXML sdk, I move it to the OpenXML forum for getting better help.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 17, 2014 1:59 AM
  • Hi,

    Firstly, you need to append a cell in this new row so that this row will be available.

    Cell cell1 = new Cell() { CellReference = "A" + rowIndex };
    CellValue cellValue1 = new CellValue();
    cellValue1.Text = "";
    cell1.Append(cellValue1);
    Row newRow = new Row()
    {
        RowIndex = rowIndex
    };
    newRow.Append(cell1);

    Secondly, if you insert a new row in the middle of a worksheet, it will effect the following rows and cells since every row has rowindex and every cell has cell reference (it means where the cell is).

    So you need to loop all rows and cells under the inserted row, change its rowindex and cellreference.

    Here is the whole solution for your reference:

    static void InsertRow(string sheetName, WorkbookPart wbPart, uint rowIndex)
            {
                Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where((s) => s.Name == sheetName).FirstOrDefault();
    
                if (sheet != null)
                {
                    Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
                    SheetData sheetData = ws.WorksheetPart.Worksheet.GetFirstChild<SheetData>();
                    Row refRow = GetRow(sheetData, rowIndex);
                    ++rowIndex;
    
                    Cell cell1 = new Cell() { CellReference = "A" + rowIndex };
                    CellValue cellValue1 = new CellValue();
                    cellValue1.Text = "";
                    cell1.Append(cellValue1);
                    Row newRow = new Row()
                    {
                        RowIndex = rowIndex
                    };
                    newRow.Append(cell1);
                    for (int i = (int)rowIndex; i <= sheetData.Elements<Row>().Count(); i++)
                    {
                        var row = sheetData.Elements<Row>().Where(r => r.RowIndex.Value == i).FirstOrDefault();
                        row.RowIndex++;
                        foreach (Cell c in row.Elements<Cell>())
                        {
                            string refer = c.CellReference.Value;
                            int num = Convert.ToInt32(Regex.Replace(refer, @"[^\d]*", ""));
                            num++;
                            string letters = Regex.Replace(refer, @"[^A-Z]*", "");
                            c.CellReference.Value = letters + num;
                        }
                    }
                    sheetData.InsertAfter(newRow, refRow);
                    //ws.Save();
                }
            }
    
            static Row GetRow(SheetData wsData, UInt32 rowIndex)
            {
                var row = wsData.Elements<Row>().
                Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
                if (row == null)
                {
                    row = new Row();
                    row.RowIndex = rowIndex;
                    wsData.Append(row);
                }
                return row;
            }

    I use regular expression to separate letters and number, increase number and join them agian. Don't forget to add a namespace:

    using System.Text.RegularExpressions;

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 18, 2014 8:11 AM
    Moderator