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