NPOI formats all cells the same way

Question

Please, take a look at the following code snippet. I simply open the excel file `myfile.xlsx` and I add rows from an object of type `List<Account>` (where my `Account` object only has `Date`, `Account` and `Amount` properties), and store the file with the name `myoutputfile.xlsx`. I would like the cells where I write the dates to have a date format, and the cells where I have amounts to have a numeric format. However, if I try the code below, all cells are formatted with the `#.00` format (dates as well). I've tried everything, can someone please tell me what's going on? I am using NPOI. ``` XSSFWorkbook wb; var fileName = "C:/tmp/myfile.xlsx"; var outputFileName = "C:/tmp/myoutputfile.xlsx"; using (var file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite)) { wb = new XSSFWorkbook(file); } XSSFSheet sheet = (XSSFSheet) wb.GetSheetAt(0); for (int i = 0; i < accountRecs.Count(); ++i) { var rec = accountRecs[i]; var row = sheet.CreateRow(i); var dateCell = row.CreateCell(3…

Accepted Answer

Here is why it's not working: the cells you are creating share a reference to the same `CellStyle` object by default. Inside the loop you are setting the `DataFormat` on that style instance to `"dd/MM/yyyy"`, then later setting that same `DataFormat` to `"#.00"`. The last one wins, so ultimately all your numeric cells (a date is considered a numeric value in Excel) will be formatted as `"#.00"`.

What you need to do is create separate cell styles for your date cells and your amount cells, set the `DataFormats` on those styles, then set the `CellStyle` property for each created cell to the appropriate style.

Try it like this:

```
    IDataFormat format = wb.CreateDataFormat();

ICellStyle dateStyle = wb.CreateCellStyle();
    dateStyle.DataFormat = format.GetFormat("dd/MM/yyyy");

ICellStyle amountStyle = wb.CreateCellStyle();
    amountStyle.DataFormat = format.GetFormat("#.00");

XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
    for (int i = 0; i < accountRecs.Count(); ++i)
    {
        var rec = accountRecs[i];
        var row = sheet.CreateRow(i);
        var dateCell = row.CreateCell(3);
        dateCell.SetCellValue(rec.Date);
        dateCell.CellStyle = dateStyle;
        var accountCell = row.CreateCell(4);
        accountCell.SetCellValue(rec.Account);
        var totalValueCell = row.CreateCell(16);
        totalValueCell.SetCellValue(rec.Amount);
        totalValueCell.CellStyle = amountStyle;
    }
```

NPOI formats all cells the same way

Solution

Problem

Related problems