How to trigger Excel's automatic calculation before saving

mini-software / MiniExcel

Fast, Low-Memory, Easy Excel .NET helper to import/export/template spreadsheet (support Linux, Mac)

https://www.nuget.org/packages/MiniExcel/

Apache License 2.0

2.81k stars 342 forks source link

How to trigger Excel's automatic calculation before saving #636

Open zhaofenglee opened 4 months ago

zhaofenglee commented 4 months ago

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 commented 4 months ago

Hi @zhaofenglee ,

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

More info here

zhaofenglee commented 4 months ago

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 commented 4 months ago

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

zhaofenglee commented 4 months ago

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 commented 3 months ago

I also encountered this issue