c# - 生成 excel 文件时 NPOI 无法评估公式

Question

我使用NPOI 库从数据表创建了 excel 文件。已创建 Excel 文件，但未计算公式。这是我第一次使用 NPOI lib。

经过大量谷歌搜索后，我发现我在代码中用于评估公式的代码片段很少，但仍然没有运气。

此例程使用随机数值和动态组合的公式填充数据表。我的数据表屏幕截图附在此处数据表屏幕截图

public DataTable GetDataTable()
{
    string strSum = "", strColName, strImmediateOneUp = "", strImmediateTwoUp = "";

    int startsum = 0;
    int currow = 0;
    bool firstTimeSum = true;

    int NumRows = 6;
    int NumColumns = 5;

    DataTable dt = new DataTable();

    for (int col = 0; col < NumColumns; col++)
    {
        strColName = GenerateColumnText(col);
        DataColumn datacol = new DataColumn(strColName, typeof(object));
        dt.Columns.Add(datacol);
    }


    for (int row = 0; row < NumRows; row++)
    {
        dt.Rows.Add();

        for (int col = 0; col < NumColumns; col++)
        {
            if (row < 2)
            {
                dt.Rows[row][col] = Convert.ToInt32(new Random().Next(1, NumRows));
            }
            else
            {
                if (firstTimeSum)
                {
                    if (row - currow == 2)
                    {
                        currow = row;
                        startsum = 0;
                        firstTimeSum = false;
                    }
                    else
                    {
                        startsum = 1;
                    }
                }
                else
                {
                    if (row - currow == 3)
                    {
                        currow = row;
                        startsum = 0;
                    }
                }


                if (startsum == 0)
                {
                    strColName = GenerateColumnText(col);
                    strImmediateOneUp = strColName + ((row + 1) - 1).ToString();
                    strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();
                    strSum = string.Format("=SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp);
                    dt.Rows[row][col] = strSum;
                }
                else
                {
                    dt.Rows[row][col] = Convert.ToInt32(new Random().Next(1, NumRows));
                }
            }

        }

        startsum = 1;
    }
    return dt;
}

此例程从我的数据表生成 excel 文件，问题在于公式未评估。

public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
{
    int i = 0;
    int j = 0;
    int count = 0;
    ISheet sheet = null;
    IWorkbook workbook=null;
    double d;

    string fileName = @"d:\SpreadsheetLight_npoi.xlsx";

    FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
    if (fileName.IndexOf(".xlsx") > 0) // 2007
        workbook = new XSSFWorkbook();
    else if (fileName.IndexOf(".xls") > 0) // 2003
        workbook = new HSSFWorkbook();

    try
    {
        if (workbook != null)
        {
            sheet = workbook.CreateSheet(sheetName);
        }
        else
        {
            return -1;
        }

        if (isColumnWritten == true)
        {
            IRow row = sheet.CreateRow(0);
            for (j = 0; j < data.Columns.Count; ++j)
            {
                row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
            }
            count = 1;
        }
        else
        {
            count = 0;
        }

        for (i = 0; i < data.Rows.Count; ++i)
        {
            IRow row = sheet.CreateRow(count);
            for (j = 0; j < data.Columns.Count; ++j)
            {
                if (Double.TryParse(data.Rows[i][j].ToString(), out d))
                {
                    row.CreateCell(j).SetCellValue(d);
                    //row.CreateCell(j).CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
                }
                else
                {
                    row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString().Replace("=",string.Empty));
                    //row.CreateCell(j).SetCellFormula(data.Rows[i][j].ToString().Replace("=",string.Empty));
                }

            }
            ++count;
        }

        if (workbook is XSSFWorkbook)
        {
            XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
        }
        else
        {
            HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
        }
        workbook.Write(fs);
        return count;
    }
    catch (Exception ex)
    {
        Console.WriteLine("Exception: " + ex.Message);
        return -1;
    }
}

我以这种方式评估我的公式，但仍然不起作用。

if (workbook is XSSFWorkbook)
{
    XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
}
else
{
    HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
}

请告诉我在我的代码中添加或更改的内容，因为应该评估结果公式，并在我打开 excel 文件时显示正确的值。谢谢

标签： c#excelnpoispreadsheetlight

Answer 1

我已经解决了我的问题。这是代码纠正代码。

for (i = 0; i < data.Rows.Count; ++i)
{
    IRow row = sheet.CreateRow(count);
    for (j = 0; j < data.Columns.Count; ++j)
    {
    if (Double.TryParse(data.Rows[i][j].ToString(), out d))
    {
        row.CreateCell(j).SetCellValue(d);
    }
    else
    {
       ICell cell = row.CreateCell(j);
        cell.SetCellType(CellType.Formula);
        cell.SetCellFormula(data.Rows[i][j].ToString().Replace("=", string.Empty));
    }

    }
    ++count;
}

if (workbook is XSSFWorkbook)
{
    XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
}
else
{
    HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
}
workbook.Write(fs);