Issue setting integer value in Excel with NPOI

Question

I have an Excel row where I need to write 5 using NPOI in the first column and 7.0 in the second column. Writing those values is quite simple, but when I need to write both in the same row, the 5 is formatted as 5.0.

To write 5, I use:

public static void SetCellValue(IRow row)
{
    var cell = row.CreateCell(1); // column 1
    int value = 5;
    cell.SetCellType(CellType.Numeric);
    cell.SetCellValue(value); 

    // 5 is written
}

To write 7.0, I use:

public static void SetCellValue(IRow row)
{
    var cell = row.CreateCell(2); // column 2
    int value = 7;
    cell.SetCellType(CellType.Numeric);
    cell.SetCellValue(value); 

    var dataFormat = cell.Sheet.Workbook.CreateDataFormat();
    var strFormat = "0.0";
    cell.CellStyle.DataFormat = dataFormat.GetFormat(strFormat); 

    // 7.0 is written
}

To write 5 and 7.0, I'm using:

public static void SetCellValue(IRow row)
{
    var cell1 = row.CreateCell(1); // column 1
    int value1 = 5;
    cell1.SetCellType(CellType.Numeric);
    cell1.SetCellValue(value1); 

    var cell2 = row.CreateCell(2); // column 2
    int value2 = 7;
    cell2.SetCellType(CellType.Numeric);
    cell2.SetCellValue(value2); 

    var dataFormat = cell2.Sheet.Workbook.CreateDataFormat();
    var strFormat = "0.0";
    cell2.CellStyle.DataFormat = dataFormat.GetFormat(strFormat); 

    // 5.0 and 7.0 is written and not 5 and 7.0
}

Any suggestions? I've tried to format the first element (number 5) using formats like var strFormat = "0"; and var strFormat = "#"; but it remains as 5.0 and not as 5

Additional note: I'm using NPOI version 2.1.1

Answer 1

I like NPOI, but sometimes I get myself in trouble with some weird bugs. Your issue seems one of that kind.

To solve this one, you have to:

1. Create a row in your Excel template and format it as you need.

2. Create a new row (where you want to write) copying from the formatted content.

int formattedRowIndex = 1;
int newRowIndex = 2;
IRow row = sheet.CopyRow(formattedRowIndex, newRowIndex);

3. Write the values without applying any other formatting.

row.CreateCell(1).setCellValue(5);
row.CreateCell(2).setCellValue(7);

// Output: 5 and 7.0

4. Remove the dummy row:

sheet.RemoveRow(formattedRowIndex);