NPOI not being able to evaluate formula when excel file is generated

Question

I have created excel file from data table using NPOI library. Excel file is created but formula is not evaluated. This is first time i am using NPOI lib.

After lot of google search i found few code snippet which i used in my code to evaluate formula but no luck still.

This routine populate data table with random numeric values and dynamically composed formulas. my data table screen shot attached here data table screen shot

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

This routine generate excel file from my data table where problem lies that formula is not evaluating.

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

I evaluate my formula this way but still does not work.

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

Please advise me what to add or alter in my code as a result formula should be evaluated and will show right value when i will open excel file. thanks

T_Zacks T_Zacks · Accepted Answer · 2018-12-29T20:55:57

I have solved my issue. here is code rectified code.

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

NPOI not being able to evaluate formula when excel file is generated

1 Answers