Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Thu Jan 30, 2014 2:42 pm

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?

Supermonster
 
Posts: 4
Joined: Mon Aug 29, 2011 7:55 am

Fri Jan 31, 2014 8:58 am

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
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Tue Feb 04, 2014 8:47 am

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
User avatar

Benjamin Du
 
Posts: 82
Joined: Thu Jul 25, 2013 2:38 am

Fri Jul 11, 2014 8:45 am

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

fvaz
 
Posts: 8
Joined: Fri Jul 11, 2014 8:22 am

Fri Jul 11, 2014 9:05 am

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
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Fri Jul 11, 2014 11:16 am

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

fvaz
 
Posts: 8
Joined: Fri Jul 11, 2014 8:22 am

Mon Jul 14, 2014 1:31 am

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
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Jul 14, 2014 8:28 am

Hello,

Thanks for reply, i already sent you the file.

Thanks,
Regards,
fvaz

fvaz
 
Posts: 8
Joined: Fri Jul 11, 2014 8:22 am

Mon Jul 14, 2014 8:55 am

Hello,

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

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Jul 14, 2014 9:52 am

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
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Jul 14, 2014 10:03 am

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


Regards,
Fvaz

fvaz
 
Posts: 8
Joined: Fri Jul 11, 2014 8:22 am

Tue Jul 15, 2014 2:22 am

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
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Tue Jul 15, 2014 9:08 am

Hello,

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

I send you a simple project.

Regards.
fvaz

fvaz
 
Posts: 8
Joined: Fri Jul 11, 2014 8:22 am

Tue Jul 15, 2014 9:50 am

Hello,

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

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Tue Jul 15, 2014 10:06 am

Hi again,

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

Regards.
fvaz

fvaz
 
Posts: 8
Joined: Fri Jul 11, 2014 8:22 am

Return to Spire.XLS