ExcelPackage save clears formulas of cells

Topics: Developer Forum, Project Management Forum, User Forum
Jan 15, 2011 at 7:36 PM

Hi,

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

 

I figured out something like when you say excelPackage.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