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);
        dateCell.SetCellValue(rec.Date);
        dateCell.CellStyle.DataFormat = wb.CreateDataFormat().GetFormat("dd/MM/yyyy");
        var accountCell = row.CreateCell(4);
        accountCell.SetCellValue(rec.Account);
        var totalValueCell = row.CreateCell(16);
        totalValueCell.SetCellValue(rec.Amount);
        totalValueCell.CellStyle.DataFormat = wb.CreateDataFormat().GetFormat("#.00");
    }
    using (var file = new FileStream(outputFileName, FileMode.Create, FileAccess.Write))
    {
        wb.Write(file);
        file.Close();
    }

Answer 1

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