Comparing a formula column with value column in excel

Question

I have two columns in a excel sheet. I am populating the first column with a formula and I have some values in second column. Now I want to compare these two columns and need to display True/false in third column. But when I use 'IF' condition all I am getting is FALSE. Here is my code.

Formulating the column

using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{
    ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[GetConfigValue("Reconsheet")];
    int totalRows = worksheet.Dimension.End.Row;


    for (int row = startupRow; row <= totalRows; row++)
    {
        //Formula
        string vlookforH = "IF(ISNA(VLOOKUP(C" + row + ",PWA!A:B,2,FALSE)),0,VLOOKUP(C" + row + ",PWA!A:B,2,FALSE))";
        worksheet.Cells[row, 8].Formula = vlookforH;

    }
    xlPackage.Save();
    MessageBox.Show("PWA hours received");
}

Comparing Formula column and Normal value column:

for (int row = startupRow; row <= totalRows; row++)
{
    if (Convert.ToInt32(worksheet.Cells[row, 18].Value) != 0)
    {

        decimal hvalue = (worksheet.Cells[row, 8].Value) != null ? Convert.ToDecimal(worksheet.Cells[row, 8].Value.ToString()) : 0;
        decimal rvalue = (worksheet.Cells[row, 18].Value) != null ? Convert.ToDecimal(worksheet.Cells[row, 18].Value.ToString()) : 0;

        if (hvalue == rvalue)
        {
            worksheet.Cells[row, 31].Value = "True";
        }
        else
        {
            worksheet.Cells[row,31].Value = "False";
            Count = Count + 1;
        }

    }

}

When I am debugging the application, I realized hvalue is always zero because it's a formula column.

I've tried in different ways, but unable to find the solution. Can anyone help me? What am I doing wrong?

Answer 1

You will have to call worksheet.Calculate(); after writing the formulas into the cells to actually calculate the values. Calling it once after your first for-loop is enough.

I verified that on a test-project.

EDIT: If worksheet.Calculate() does not work you can try xlPackage.Workbook.Calculate();

Here is a link to the documentation: EPPlus Calculate Documentation