CalculateAllValue is extremely slow

[Supermonster]

Hello!

I'm trying to fill a big worksheet with data and formulas. Next I need to read values, calculated by formulas, from this workshhet. Before reading I call CalculateAllValue() to get formula values. But if worksheet is about 10 formula columns (each ~700 rows) CalculateAllValue takes 30+ minutes or something like that. Is it known trouble and is there any workarounds?

[amy.zhao]

Hello,

Thanks for your inquiry.
Would you please provide us your excel file and your code to help us do a research? It will help us to reproduce quickly and resolve it soon.
Thank you. You could send your file to support@e-iceblue.com.

Best regards,
Amy
E-iceblue support team

[Benjamin Du]

Hello,

Could you please provide us your source files to help us fix the issue.
You could send your files to support@e-iceblue.com.
If you have any questions, welcome to get it back to us.

Regards,
Benjamin
E-iceblue support team

[fvaz]

Hi,

I have the same problem, the process take 20 sec, doing the calculation.
I've tried with pro but is the same thing.

Regards.
fvaz

[Gary.zhang]

Hello,

Thanks for your inquiry.
Please list the below information in your message, it may help us to reproduce your issue quickly and offer the solution to you.

1. Dll file list (name + version)
for example:
Spire.Doc.dll v4.9.32.3040
Spire.License.dll v4.9.32.3040
Spire.Pdf.dll v2.8.86.3040
2. Operation System
for example:
Windows 7 Enterprise Edition SP1 x64
Regional and Language Options
3. Project can reproduce the issue
All code about this problem, a simple project which can reproduce this problem will be better.
4. Test Document or Test data
Please provide your test document to us if convenient, it may help us reproduce your issue quickly. We promise to keep your document confidential and we will not use it for any other purpose. Besides, you could also remove the security data of your document and then send it to us.

Thanks in advance,
Gary
E-iceblue support team

[fvaz]

1. Dll file list (name + version)
for example:
Spire.XLS.dll v7.5.0.7020
Spire.License.dll v1.3.2.20

2. Operation System
Windows 7 Professional x64
Portuguese-PT
3. Project can reproduce the issue

Code: Select all

CultureInfo cc = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;

            Workbook workbook = new Workbook();

            workbook.LoadFromFile(ssFilePath,ExcelVersion.Version2013);
            //Initailize worksheet
            Worksheet sheet = workbook.Worksheets[ssWorksheetName];

            foreach (RCCellRangeRecord CellRange in ssInRange)
            {
                sheet.Range[CellRange.ssSTCellRange.ssCellCoordinate].Value = CellRange.ssSTCellRange.ssValue.ToString();
            }

            workbook.CalculateAllValue();
           
            RCCellRangeRecord AuxCellRange = new RCCellRangeRecord();
            RLCellRangeRecordList AuxCellRangeList = new RLCellRangeRecordList();

            foreach (RCCellRangeRecord CellRange in ssOutRange)
            {
                AuxCellRange.ssSTCellRange.ssCellCoordinate = CellRange.ssSTCellRange.ssCellCoordinate;
                AuxCellRange.ssSTCellRange.ssValue = Convert.ToDecimal(sheet.Range[CellRange.ssSTCellRange.ssCellCoordinate].FormulaStringValue.ToString());
                AuxCellRangeList.Append(AuxCellRange);
            }

            ssOutValues = AuxCellRangeList;
            Thread.CurrentThread.CurrentCulture = cc;

4. Test Document or Test data
Can't add attachment(Sorry, the board attachment quota has been reached.)



And i discovered a new problem (unknown formula IFERROR) maybe already exists a topic with that..

Regards,
FVAZ

[Gary.zhang]

Hello,

Sorry for late reply as weekend.
Thanks for your response. Please send your document to our Email:Support@e-iceblue.com
Thanks in advance,
Gary
E-iceblue support team

[fvaz]

Hello,

Thanks for reply, i already sent you the file.

Thanks,
Regards,
fvaz

[Gary.zhang]

Hello,

We have received the file and we will investigate the issue, then let you know ASAP.
Thanks,
Gary
E-iceblue support team

[Gary.zhang]

Hello,

We have posted the issue(CalculateAllValue is extremely slow) to our Dev team, and they will improve it, once there are any new update, we will let you know ASAP.
Regarding the issue(unknown formula IFERROR) you mentioned, before there is the same topic exists, which is caused by the culture, please confirm if it is in the InvariantCulture, if so, please provide the detailed information.
Thanks,
Gary
E-iceblue support team

[fvaz]

Hi,
has you can see in the above topic (the partial code) the culture is defined like "invariant".


Regards,
Fvaz

[Gary.zhang]

Hello,

Thanks for your response.
Yes, we noticed the above codes. And we have also tested the document you provided with Spire.XLS Pack Hotfix Version:7.5.3, but there is no the issue(unknown formula IFERROR), please test the hotfix, if the issue persists, please provide us a simple project, which doesn't include the useless codes for us. It would be help to reproduce the issue and work out the solution for you ASAP.
Thanks in advance,
Gary
E-iceblue support team

[fvaz]

Hello,

Thanks for the quick reply, i tried with the HOTFIX but still the same problem.

I send you a simple project.

Regards.
fvaz

[Gary.zhang]

Hello,

Thanks for your response.
We will investigate it and let you know ASAP.
Thanks,
Gary
E-iceblue support team

[fvaz]

Hi again,

New problem now: (unknown formula ACCRINT) maybe here is the problem for IFERROR, because the field reference have ACCRINT formula.

Regards.
fvaz