Inserting Rows

Dec 2, 2009 at 7:09 PM

I created an Excel file to be used as a template when generating a report.  In the middle of the report is a section that can be 0 to 100+ rows.  I want to insert rows into this section and move down the reset of the rows.  The create row option clears the row that being processed and does not move rest of the content down. Is there a way to insert rows?

Dec 13, 2009 at 6:03 PM

I agree this would be a great feature; i.e.

ws.InsertRows(int startingRowNumber, int numberOfRowsToInsert)

You might want to add it as a feature request on the Issue Tracker page

Feb 5, 2010 at 8:59 AM

I think you can use ShiftRows method to move rest of the content down, then set style of new row insert like this code:

static void InsertRows(ref HSSFSheet sheet1, int fromRowIndex, int rowCount)
        {
            sheet1.ShiftRows(fromRowIndex, sheet1.LastRowNum, rowCount, true, false, true);

            for (int rowIndex = fromRowIndex; rowIndex < fromRowIndex + rowCount; rowIndex++)
            {
                HSSFRow rowSource = sheet1.GetRow(rowIndex + rowCount);
                HSSFRow rowInsert = sheet1.CreateRow(rowIndex);
                rowInsert.Height = rowSource.Height;
                for (int colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
                {
                    HSSFCell cellSource = rowSource.GetCell(colIndex);
                    HSSFCell cellInsert = rowInsert.CreateCell(colIndex);
                    if (cellSource != null)
                    {
                        cellInsert.CellStyle = cellSource.CellStyle;
                    }
                }
            }
        }

Aug 19, 2010 at 2:48 AM

Thanks, that's exactly what I want.