How to trigger Excel's automatic calculation before saving

[zhaofenglee]

Excel Type

• [ ] XLSX

MiniExcel Version

1.33.0

Description

Export Excel through Stream, Excel contains formulas, but the cells with formulas do not display content when opened.

 var memoryStream = new MemoryStream();
                await memoryStream.SaveAsByTemplateAsync(tplPath, value);
                memoryStream.Seek(0, SeekOrigin.Begin);

                return new RemoteStreamContent(memoryStream, $"{DateTime.Now.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture)}.xlsx",
	                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

[meld-cp]

Hi @zhaofenglee ,

try prefixing the formulas with $ like $=AVERAGE(D10:D119)

More info here

[zhaofenglee]

Hi @meld-cp

A dollar sign ($) has been added in front of the formula. Here are the formulas that have been added. $=AVERAGE(D{{$enumrowstart}}:D{{$enumrowend}})

If you enable automatic calculation after editing, the formula is not a problem.

[meld-cp]

Hi @zhaofenglee , so is this issue resolved? or may be I'm not understanding what the problem is 🤔

[zhaofenglee]

Hi @meld-cp

The issue of updating the program to version 1.34.0 has not been resolved yet. Currently, the usage scenario is to download Excel through API or send Excel via email. By opening Excel in this way, it will display a protected view, causing formula cells to appear blank. If editing is enabled, the display will be normal. The expected result is to display the values of cells with formulas in a protected view. Is there any solution available to solve this?

[luoxy123]

I also encountered this issue

[michelebastione]

@zhaofenglee , @luoxy123 I'm afraid this issue is not solvable. When a formula is written inside a cell it's the Excel engine that performs the calculations and caches the result. MiniExcel, being a simple lightweight data reader and writer cannot possibly do that, it's way beyond its scope.