NPOI以相同的方式格式化所有单元格 [英] NPOI formats all cells the same way

问题描述

请看下面的代码片段。我只需打开excel文件 myfile.xlsx ，并从类型为的对象中添加行列表< Account> （其中我的帐户对象只有日期，帐户和金额属性），并存储名称为 myoutputfile.xlsx 的文件。我想要我写日期的单元格具有日期格式，我想要的单元格有数字格式。但是，如果我尝试下面的代码，所有单元格格式化为＃。00 格式（日期）。我试过一切，有人可以告诉我发生了什么事吗？我正在使用NPOI。

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

推荐答案

这是为什么它不工作：您创建的单元格默认情况下共享相同的 CellStyle 对象的引用。在循环中，您将该样式实例上的 DataFormat 设置为dd / MM / yyyy，然后设置相同的 DataFormat 到＃。00。最后一个获胜，所以最终所有的数字单元格（一个日期被认为是Excel中的一个数值）将格式化为＃。00。

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".

您需要做的是为日期单元格和数量单元格创建单独的单元格样式，在这些样式上设置 DataFormats ，然后将每个创建的单元格的 CellStyle 属性设置为适当的样式。

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.

尝试如下：

    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以相同的方式格式化所有单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！