0

I am using EPPlus library to get all requirement as per our need. I am not getting how to get calculated value of the cell which has reference of another worksheet in the same .xlsm file (macro enabled).Can any one help??

After hitting Calculate(),its get calculated but i am not getting the right value, after open file it takes time to enabled macro at that temporary time i can see wrong value whatever i am getting .... but after complete open the file, the values is changes and this is right calculation,& it's heavy file,lots of formula running interlink with another worksheet.

public ExcelResultModel CreateExcelInput(string path, ExcelInputModel model)
{
        //Old excel
        //FileInfo templateFile = new FileInfo(path + @"\Calculator15-zoetis5_Embryo_FDCv1.xlsm");


        FileInfo templateFile = new FileInfo(path + @"\myfile1.xlsm");
        string CalculateValues = string.Empty;
        ExcelResultModel result = new Model.ExcelResultModel();

        //using (FileStream templateDocumentStream = File.OpenWrite(templateFile.ToString()))
        //{
            using (ExcelPackage package = new ExcelPackage(templateFile))
            {
                //Open worksheet 1

                ExcelWorksheet worksheet = package.Workbook.Worksheets[2];
                package.Workbook.CalcMode = ExcelCalcMode.Automatic;

                worksheet.Cells["I15"].Style.Numberformat.Format = "#0\\%";
                worksheet.Cells["I16"].Style.Numberformat.Format = "#0\\%";
                worksheet.Cells["I17"].Style.Numberformat.Format = "#0\\%";
                worksheet.Cells["I18"].Style.Numberformat.Format = "#0\\%";
                worksheet.Cells["I19"].Style.Numberformat.Format = "#0\\%";

                //worksheet.Cells["F14"].Style.Numberformat.Format = "#0\\.00%";


                worksheet.Workbook.FullCalcOnLoad = true;
                worksheet.Cells["I15"].Value = model.val1;
                worksheet.Cells["I16"].Value = model.val2;
                worksheet.Cells["I19"].Value = model.val3;

                //worksheet.Cells["I17"].Value = model.val4;
                //worksheet.Cells["I18"].Value = model.val5;
                //worksheet.Cells["I19"].Value = model.val6;

                worksheet.Cells["T19"].Calculate();

                //worksheet.Cells["T15"].Calculate();
                //worksheet.Cells["T16"].Calculate();
                //worksheet.Cells["T17"].Calculate();
                //worksheet.Cells["T18"].Calculate();
                //worksheet.Cells["T19"].Calculate();

                // save our new workbook and we are done!
                //package.Workbook.CalcMode = ExcelCalcMode.Manual;
                //worksheet.Cells["F14"].Calculate();

                //worksheet["mysheet"].Cells["A1"].Calculate();

                package.Save();
                //System.Diagnostics.Process.Start(templateFile.ToString());

                var calculateOptions = new ExcelCalculationOption();
                calculateOptions.AllowCirculareReferences = true;

                bool isFormulaCircularReference = false;
                try
                {
                    //package.Workbook.Calculate();
                    package.Workbook.Worksheets["Long"].Calculate();
                    package.Workbook.Worksheets["genetics"].Calculate();
                }
                catch (CircularReferenceException ex)
                {
                    //If there is a circular reference this exception will be thrown
                    isFormulaCircularReference = true;
                }


                package.Save();

                if (worksheet.Cells["T15"].Address == "T15")
                {
                    object calcval = worksheet.Cells["T15"].Value;
                    result.DonorResult = Convert.ToString(calcval);

                }

        return result;
}
Chandan Kumar
  • 4,570
  • 4
  • 42
  • 62
  • Do you expect that EPPLus will run the macro and calculations for you? If yes, then you're out of luck. EPPLus lacks that ability. – rene May 06 '17 at 08:36
  • I don't know if its supporting or not ,I thought it will work... if it is not supporting then what should i have to use to solve these functionality ?? – Vineet Kumar Rai May 06 '17 at 08:40
  • can you please tell me the solutions, it very urgent !! – Vineet Kumar Rai May 06 '17 at 08:47
  • You should use a regular Excel and use Interop to work with the worksheet. Oh, and it is Saturday here, I wouldn't expect volunteers to care about how urgent your issue is. – rene May 06 '17 at 08:52
  • Here is the condition that client has provided the excel(macro enabled) which has lot's of calculation in this .xlsm sheet i need to care about one mainsheet which has reference of another sheet as formula calculation. Is it possible from Interop ??... ha ha if work available then there is no saturday or sunday ha ha ha ;) – Vineet Kumar Rai May 06 '17 at 09:03

0 Answers0