openxml - inserting a row, moving others

Question

Hi there,

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 ...

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...