openxml - inserting a row, moving others

Question

I am using openxml to create an excel report. The openxml operates on a template excel file using named ranges.

The client requires a totals row at the end of the list of rows. Sounds like a reasonable request!!

However, the data table I'm returning from the db can contain any number of rows. Using template rows and 'InsertBeforeSelf', my totals row is getting overridden.

My question is, using openxml, how can I insert rows into the spreadsheet, causing the totals row to be be moved down each time a row is inserted?

Regards ...

Sapphism

Answer 1

Assuming you're using the SDK 2.0, I did something similiar by using this function:

private static Row CreateRow(Row refRow, SheetData sheetData)
    {
        uint rowIndex = refRow.RowIndex.Value;
        uint newRowIndex;
        var newRow = (Row)refRow.Clone();

        /*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>())
            {
                string cellReference = cell.CellReference.Value;
                cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
            }

            row.RowIndex = new UInt32Value(newRowIndex);
        }*/

        sheetData.InsertBefore(newRow, refRow);
        return newRow;
    }

I'm not sure how you were doing it with InsertBeforeSelf before, so maybe this isn't much of an improvement, but this has worked for me. I was thinking you could just use your totals row as the reference row. (The commented out part is for if you had rows after your reference row that you wanted to maintain. I made some modifications, but it mostly comes from this thread: http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/thread/65c9ca1c-25d4-482d-8eb3-91a3512bb0ac)

Since it returns the new row, you can use that object then to edit the cell values with the data from the database. I hope this is at least somewhat helpful to anyone trying to do this...

Teressaterete

Be aware, that the merge cells are kept in separate object. If you want to keep the correct merge format you should update their references. Please check https://mcmap.net/q/1249152/-inserting-new-rows-and-moving-exsisting-ones-with-openxml-sdk-2-0 – Rabid

Answer 2

[Can someone with more points please put this text as a comment for the M_R_H's Answer.]

The solution that M_R_H gave helped me, but introduces a new bug to the problem. If you use the given CreateRow method as-is, if any of the rows being moved/re-referenced have formulas the CalcChain.xml (in the package) will be broken. I added the following code to the proposed CreateRow solution. It still doesn't fix the problem, because, I think this code is only fixing the currently-being-copied row reference:

if (cell.CellFormula != null) {
     string cellFormula = cell.CellFormula.Text;
     cell.CellFormula = new CellFormula(cellFormula.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
}

What is the proper way to fix/update CalcChain.xml?

PS: SheetData can be gotten from your worksheet as:

worksheet.GetFirstChild<SheetData>();

Overwork

Answer 3

You have to loop all rows and cells under the inserted row,change its rowindex and cellreference. I guess OpenXml not so smart that help you change index automatically.

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;
    }

Above solution got from:How to insert the row in exisiting template in open xml. There is a clear explanation might help you a lot.

Hemangioma