This project has moved. For the latest updates, please go here.

Cell containing formula giving null values when read second time

Jan 15, 2011 at 3:53 PM
Edited Jan 15, 2011 at 4:52 PM

Hi Jan

I have a class that opens the excel sheet reads and then closes the file. (certain cells have formulas in them)

There is another process which does some computations on the obtained values.

I have another class that writes values to the excel sheet opening it again and saving it.

 

When I run the whole process again without opening the file after the first cycle on the same file, the cells containing the formula give me null values.

 

Can you tell me how to fix this?

 

Thanks

Srikanth Kambhampati

 

Excel Sheet has values

A     B C D

Item 1 1 1

Values =B1 =B2+C1 =C2+D1

 

Here is the code I used.

Program.cs

 

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using OfficeOpenXml;

namespace ExcelTestWrite
{
    using System;
    using System.Reflection;

    public class Program
    {
        public static void Main()
        {
            ReadFromExcelSheet read = new ReadFromExcelSheet(@"C:\Book1.xlsx");
            read.TestRead();

            ExcelSheetWriter write = new ExcelSheetWriter(@"C:\Book1.xlsx");
            write.WriteToExcel();
            Console.WriteLine("*******************************************************");
            read.TestRead();
            Console.ReadLine();
        }
    }
}

 

 

ExcelSheetWriter.cs

 

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OfficeOpenXml;
using System.IO;

namespace ExcelTestWrite
{
    class ExcelSheetWriter
    {
        FileInfo file;

        public ExcelSheetWriter(string path)
        {
            file = new FileInfo(path);
        }
        
        public void WriteToExcel()
        {
            using (ExcelPackage package = new ExcelPackage(file))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.First();

                worksheet.Cells[1, 6].Value = 1;
                worksheet.Cells[2, 6].Formula = "E2 + F1";
                worksheet.Cells[1, 7].Value = 1;
                worksheet.Cells[2, 7].Formula = "F2 + G1";

                package.Save();
            }
        }
    }
}

 

ReadFromExcelSheet.cs

 

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using OfficeOpenXml;

namespace ExcelTestWrite
{
    public class ReadFromExcelSheet
    {
        FileInfo file;

        public ReadFromExcelSheet(string path)
        {
            file = new FileInfo(path);
        }

        public void TestRead()
        {
            using (ExcelPackage package = new ExcelPackage(file))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.First();
                Console.WriteLine(worksheet.Cells[1, 1].Value);
                Console.WriteLine(worksheet.Cells[1, 2].Value);
                Console.WriteLine(worksheet.Cells[1, 3].Value);
                Console.WriteLine(worksheet.Cells[1, 4].Value);
                Console.WriteLine(worksheet.Cells[1, 5].Value);
                Console.WriteLine(worksheet.Cells[2, 1].Value);
                Console.WriteLine(worksheet.Cells[2, 2].Value);
                Console.WriteLine(worksheet.Cells[2, 3].Value);
                Console.WriteLine(worksheet.Cells[2, 4].Value);
                Console.WriteLine(worksheet.Cells[2, 5].Value);
                package.Save();
            }
        }
    }
}

 

Jan 15, 2011 at 7:20 PM

I figured out something like

 

when you say package.Save() the excel will no longer have the formulas retained in it.

 

How do I need to save the file in order to make the sheets still have the formulas in them?

 

Thanks

Srikanth Kambhampati

Coordinator
Jan 16, 2011 at 4:41 PM

Hi,

There is no calculation engine in EPPlus, so you have to open the files in Excel to get the values of the formulas.

Also, make sure you have the Calc mode set to automatic in all your workbooks.

Jan

Jan 17, 2011 at 3:32 AM

Is there a way to open/save the excel sheet programmatically for this to work? (without using COM)

 

Thanks

Srikanth Kambhampati

Coordinator
Jan 17, 2011 at 8:07 PM

The calc chains are removed when the package is opened in EPPlus, so you have to recalculate the formulas in Excel.

Developer
Mar 24, 2014 at 7:48 AM
Since there are external links to this thread, we should just add that there will be support for formula calculation from EPPlus 4.0 (which is currently in beta-state).