5

How can I use NPOI to insert a row like excel? The excel insert command copy the format for the upper row

Thanks!

Rodrigo Juarez
  • 1,745
  • 1
  • 23
  • 38

2 Answers2

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

maybe you can look here for some inspiration

  • (+1 for linking the source and not trying to take all the credit). I am trying to use the exact same method line for line, but I am getting errors. Seemingly namespace conflicts on rowSource, rowInsert, and cellSource, cellInsert lines. When I hover them it says "Cannot implicitly convert type 'NPOI.SS.UserModel.IRow' to 'NPOI.HSSF.UserModel.HSSFRow'. An explicit conversion exists (are you missing a cast?)". Itwould be cool if you could add a comment on how to fix, cheers. – Francis Rodgers Nov 26 '14 at 15:38
5
 HSSFRow newRow = worksheet.GetRow(destinationRowNum);
HSSFRow sourceRow = worksheet.GetRow(sourceRowNum);

// If the row exist in destination, push down all rows by 1 else create a new row
if (newRow != null)
{
    worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1);
}
else
{
    newRow = worksheet.CreateRow(destinationRowNum);
}

I am using this code to create a new row. This might come handy to you.

Sasuke Uchiha
  • 91
  • 1
  • 10