Copying ranges in Excel worksheet formulas are not refreshed on run time

Aspose.Cells Product Family
1

Hi Team,

This is my sample code to refresh my workbook and also refresh formulas. But this is not refreshing my formula when i download my data into excel except i copy and past the same data. Attached is the sample template. it is empty but if you copy and paste the same data, it will refresh the formula and plot my data.

protected void btnDownload_Click(object sender, EventArgs e)
{

Workbook wrk = new Workbook(Server.MapPath("Template/Technical_plot_TemplateV4.xlsx"));
Worksheet wrksht = wrk.Worksheets["Technical Limit Data"];

if ((DataTable)Session["dtModules2"] != null)
{
DataTable dt = (DataTable)Session["dtModules2"];
wrksht.Cells.ImportDataTable(dt, true, "A1");
XlsSaveOptions cell = new XlsSaveOptions();
Range range1 = wrksht.Cells.CreateRange("A1", "E" + wrksht.Cells.EndCellInColumn(0).Name);
int cellidx = wrksht.Cells.EndCellInColumn(0).Row;
int fcellidx = cellidx + 10 + 1;

Range range2 = wrksht.Cells.CreateRange("A1", "E" + fcellidx.ToString());
range2.Copy(range1);
wrk.CalculateFormula();
wrksht.Workbook.Settings.ReCalculateOnOpen = true;
wrksht.Workbook.Settings.CalcMode = Aspose.Cells.CalcModeType.Automatic;

wrk.Save(this.Response, "TechnicalLimitData.xlsm", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Xlsm));

}

I will be glad if you guys can assit me.

2

Hi,


Please provide us your template Excel file “Technical_plot_TemplateV4.xlsx” to evaluate your issue properly. Also attach some screen shots taken in MS Excel to differentiate your issue. Moreover, please make sure that your source and destination ranges are same and properly specified in your code (you should debug your code a bit I think, check this line: Range range1 = wrksht.Cells.CreateRange(“A1”, “E” + wrksht.Cells.EndCellInColumn(0).Name), check this range, it will be much bigger). Also, your code segment should be updated accordingly, e.g
change your line of code i.e…,:
wrk.Save(this.Response, “TechnicalLimitData.xlsm”, ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Xlsm));
to:

wrk.Save(this.Response, “TechnicalLimitData.xlsm”, ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsm));

We also recommend you to kindly try to use our latest version/fix: Aspose.Cells for .NET v7.5.1.2


Thank you.