Excel Formula Calculations

Technical support for Spire.XLS

Moderators: iceblue support, Flash, Manager

Excel Formula Calculations

Postby Talsbard » Thu Aug 03, 2017 1:16 pm

I have a multi tab excel spreadsheet that I access using a C# application. I update fields on the spreadsheet that triggers formulas that need to be run. I tried using CalculateAllValue but it takes anywhere between 10-20 seconds to process the formulas. Is there a way to trigger specific formulas or a reason why CalculateAllValue would take so long to run?

Thank you
Talsbard
 
Posts: 14
Joined: Wed Oct 12, 2016 9:27 pm

Re: Excel Formula Calculations

Postby Jane.Bai » Fri Aug 04, 2017 3:53 am

Hello,

If you were using the old or free version, we suggest you first using the latest version(Spire.XLS Pack Hotfix Version:7.12.55) to have a try. Also, please refer to the follwing guide to calculate specific formulas.
https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/How-to-Calculate-Formulas-in-XLS-Document-with-C-Code.html
If the solution does not make any sense or the CalculateAllValue still takes such a long time after using the hotfix, please send your sample file and the ralated code to us via email(support@e-iceblue.com), and then we will look into it.

Sincerely,
Jane
E-iceblue support team
User avatar
Jane.Bai
 
Posts: 953
Joined: Tue Nov 29, 2016 1:47 am

Re: Excel Formula Calculations

Postby Talsbard » Fri Aug 04, 2017 6:58 pm

I am still having problems getting the results I am looking for. I am using a licensed version of the product and the CalculateAllValue is still too slow. Here is my setup.

Excel spreadsheet with 2 tabs (It has more but all I am using for testing). Using C# I have a visual interface that basically represents the main spreadsheet. I want to change a value in the application that updates the field on the spreadsheet. I have tried copying in the main formula into memory by getting the formula from the spreadsheet in code. That formula depends on other formulas on the second sheet to list the proper value in the field. The problem is that the value for the formula resides in the same cell as the formula itself. Your example does not show how to fire a formula already there without running CalculateAllValue or without loading it into memory first.

For example I have two fields: A1 = 1, A2 = 1, A3 (=A1 + A2) <- Formula which shows value of 2 in cell A3

If in code I change A1 to 2 then I want to be able to tell the spreadsheet to give me the result of A3 without running the possible hundreds of other formulas in the spreadsheet. Right now the only thing I can see to do is to copy the formula into memory and then run the formula and assign the value to A3 which deletes the formula and just leaves the value. That means in the future any further changes will not reflect on the spreadsheet because the formula is gone.

Here is an example of the code I am trying to use.

Worksheet wsMainSheet = wb.Worksheets[1];
Worksheet wsNewCarryCost = wb.Worksheets[2];

wsMainSheet.Rows[14].Columns[3].Value = tbPropertyValue.Text; --Populates the spreadsheet with the value entered

wsMainSheet.Rows[22].Columns[7].Value = wb.CaculateFormulaValue(formulas[0]).ToString(); -- Populates value in worksheet with the formula in memory. In a previous function I copied each of the formulas from their cells into a list to be run. But as you know the formula will be gone from the field once I do this since I have copied the value.

//wb.CalculateAllValue(); -- I try running this here but it takes over 10 seconds to run.

RefreshExcel(wsMainSheet); -- this refreshes my windows app with new values in excel sheet.


Technically the code above does work and when I initially change the field and run the formula it works correctly. But if I change the field again and run the code again it no longer changes the value in the field even though it is called exactly the same. I do not know if this is a bug or not but I am stumped why it happens.

The excel spreadsheet I have has sensitive information on it so I can not send it to you for testing. I appreciate any help you can give me in this.

Also do you have any API documentation that explains the methods and properties in detail? I have the help file and I appreciate the demos but many things I am doing do not seem to be covered in detail in either place but your product seems to be capable of doing it.

Thank you
Talsbard
 
Posts: 14
Joined: Wed Oct 12, 2016 9:27 pm

Re: Excel Formula Calculations

Postby Jane.Bai » Mon Aug 07, 2017 9:48 am

Hello,

Thanks for your inquiry.
According to the description, I made an test and got the correct result even when changing the field and runnning the code for several times.
See the code snippet.
Code: Select all
Workbook book = new Workbook();
            book.LoadFromFile("C:\\Book2.xlsx");
            string formula = book.Worksheets[1].Range["A1"].Formula;
            book.Worksheets[0].Range["A1"].Value = "4";
            book.Worksheets[1].Range["A1"].Value = book.CaculateFormulaValue(formula).ToString();
            book.Worksheets[0].Range["A1"].Value = "7";
            book.Worksheets[1].Range["A1"].Value = book.CaculateFormulaValue(formula).ToString();
            book.SaveToFile("C:\\1.xlsx", ExcelVersion.Version2013);

If I misunderstand your meaning, please share a sample project with simulant files, as long as the issue can be reproduced.
Besides, here is the link for Spire.Xls API

Sincerely,
Jane
E-iceblue support team
User avatar
Jane.Bai
 
Posts: 953
Joined: Tue Nov 29, 2016 1:47 am

Re: Excel Formula Calculations

Postby Talsbard » Mon Aug 07, 2017 2:41 pm

I am adding a mock up that is emulating my current problem plus the mock up excel spreadsheet.

The form has one Textbox (txtTest) and one label (lblValue). When I change the Textbox and leave it should run the formula and change the label.

As before I change the textbox amount and it works fine. I change it a second time and it does not work anymore.

Code: Select all
namespace TestexcelApp
{
    public partial class Form1 : Form
    {
        Workbook wb = new Workbook();
        string formula;
        public Form1()
        {
            InitializeComponent();
        }

        private void txtTest_Leave(object sender, EventArgs e)
        {           
            Worksheet Sheet1 = wb.Worksheets[0];

            Sheet1.Rows[0].Columns[2].Value = txtTest.Text;

            Sheet1.Rows[5].Columns[4].Value = wb.CaculateFormulaValue(formula).ToString();
            lblValue.Text = Sheet1.Rows[5].Columns[4].Value.ToString();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            wb.LoadFromFile(@"c:\TestExcel.xlsx");
            Worksheet Sheet1 = wb.Worksheets[0];

            txtTest.Text = Sheet1.Rows[0].Columns[2].Value.ToString();
            lblValue.Text = Sheet1.Rows[5].Columns[4].FormulaValue.ToString();
            formula = Sheet1.Range["E6"].Formula.ToString();
        }
    }
}
Attachments
TestExcel.zip
Mock up Spread sheet
(7.11 KiB) Downloaded 46 times
Talsbard
 
Posts: 14
Joined: Wed Oct 12, 2016 9:27 pm

Re: Excel Formula Calculations

Postby Jane.Bai » Tue Aug 08, 2017 7:36 am

Hello,

Thank you for the details.
I reproduced the issue you mentioned with your code. Since there is more than one formula and some of them are related to each other. If you only acquire the one formula you need without paying attention to the related formulas, some unexpected issue might occur. Therefore, we suggest you calculating all the formulas. I do understand your sitation that it takes such a long time, but if there are a number of formulas, the calculation process will indeed cost some time!

Sincerely,
Jane
E-iceblue support team
User avatar
Jane.Bai
 
Posts: 953
Joined: Tue Nov 29, 2016 1:47 am

Re: Excel Formula Calculations

Postby Talsbard » Tue Aug 08, 2017 1:44 pm

Thank you for your help. We are trying to figure out how to eliminate the need for excel on servers and we went with E-ICEBLUE as we found it to be one of the best products for accomplishing this goal. I have done several projects already using the product and have been happy with the results. Do you have any plans of going back and looking at the efficiency of your call to calculate all formulas? I have a large need for this functionality for a current project and am hoping that you can help in accomplishing this goal. Excel does the same functionality in less than a second, updating the spreadsheet with the new value in that time and my customers would expect something similar if excel is not in use.

Again thank you for your help
Talsbard
 
Posts: 14
Joined: Wed Oct 12, 2016 9:27 pm

Re: Excel Formula Calculations

Postby Jane.Bai » Wed Aug 09, 2017 10:59 am

Hello,

Thanks for your feedback.
I have referred the issue to our dev team, but it might not be implemented in a short time due to other prior issues.

Sincerely,
Jane
E-iceblue support team
User avatar
Jane.Bai
 
Posts: 953
Joined: Tue Nov 29, 2016 1:47 am

Re: Excel Formula Calculations

Postby Talsbard » Mon Aug 21, 2017 3:11 pm

I have done some more research on this and am wondering if you can help me with this. I have come to determine that the main problem with speed in the instance does not have anything to do with formulas at all but is still effecting the CalculateAllValue. One of the sheets I have has over 40000 rows of data but no formulas at all. I am attaching my sample sheet and the code I used for the test. On my computer it takes around 4 seconds to run the function.

Code: Select all
public partial class Form1 : Form
    {
        Workbook wb = new Workbook();

        public Form1()
        {
            InitializeComponent();
            wb.LoadFromFile(@"c:\Test.xlsx", ExcelVersion.Version2013);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Stopwatch sw = new Stopwatch();
           
            Worksheet wsMainSheet = wb.Worksheets[0];
           
            sw.Start();
            wb.CalculateAllValue();
            sw.Stop();
            TimeSpan ts = sw.Elapsed;

            MessageBox.Show(String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10));

        }
    }
Attachments
Test.zip
One sheet with over 40000 rows
(508.83 KiB) Downloaded 49 times
Talsbard
 
Posts: 14
Joined: Wed Oct 12, 2016 9:27 pm

Re: Excel Formula Calculations

Postby Jane.Bai » Tue Aug 22, 2017 6:27 am

Hello Talsbard,

Thanks for sharing your research.
Kindly note the principle of the CalculateAllValue() is to traverse all the content first to detect the formulas and calculate them. The process will never know whether there's any formulas in the excel beforehand, so even there's no formulas at all, this method will still traverse the content and it will absolutely cost time!

Sincerely,
Jane
E-iceblue support team
User avatar
Jane.Bai
 
Posts: 953
Joined: Tue Nov 29, 2016 1:47 am

Re: Excel Formula Calculations

Postby Talsbard » Thu Aug 24, 2017 7:33 pm

Thank you for your response but something I found on your own web page is

Developers can easily manipulate Excel cells and Evaluate formula value in C#, VB.NET or ASP.NET at runtime. Super-fast, scalable excel calculation engine is compatible with the 97-2003/2007/2010 Excel. Cell Styles are supported by this Excel .NET component, such as cell merging/unmerging, text wrapping/unwrapping, text alignment, rotation, interior, borders, lock/unlock and etc. Font formats, like setting font type, size, color, bold, italic, strikeout and underlining etc. is also fully supported. Conditional formatting, text search and replace, filter and data validation can be applied to cells as easily as you expect.


I have bolded the actual statement made. I can understand having a real fast Excel calculation engine but can it be said to actually be fast if you can't reach the calculations in an efficient manner? I am only being given one choice on how to run my calculations on a spreadsheet I am not allowed to edit. That same spreadsheet in Excel itself does not suffer the same penalties that you are saying your API has.

So for example, couldn't you have a background thread created when a workbook is spun up that inventories all of the formulas and stores them for easy access. That way they could be fired from that point instead of having to inventory them every time you run the app.

I am just searching for a reasonable resolution here. This API is sold as an Enterprise solution. I am trying to write an enterprise application which has demands on it that go beyond what is being offered. I understand you stated you have more important things to work on than making this more efficient but that statement was said before when I had a broad problem I could not prove. I put together the test to show you where the failure is. I was hoping for more of a statement than sorry, you are out of luck because we wrote it the way we did.

Thank you for your honest evaluation of this post
Talsbard
 
Posts: 14
Joined: Wed Oct 12, 2016 9:27 pm

Re: Excel Formula Calculations

Postby Jane.Bai » Fri Aug 25, 2017 6:08 am

Hello Eric,

We apologize so much for that.
When calculating all formulas, our product needs to search all the formulas in the document first, and then do the calculation. If the document has a lot of data, Spire.Xls needs to entirely unfold the data, and then manipulate the data. The whole process is different from the mechanism in Excel, this is why Spre.Xls make it slow. We will try our best to find some approach to do optimization towards this issue, but since it is extremely complex, I 'm afraid it might take a relatively long time. Once I got any news from our dev team, I will inform you.

Sincerely,
Jane
E-iceblue support team
Last edited by Jane.Bai on Mon Aug 28, 2017 12:51 am, edited 1 time in total.
User avatar
Jane.Bai
 
Posts: 953
Joined: Tue Nov 29, 2016 1:47 am

Re: Excel Formula Calculations

Postby Talsbard » Fri Aug 25, 2017 1:12 pm

Thank you, that is what I am looking for. Hopefully this can get the attention that is needed. In my opinion this is the most important part of excel and what makes excel what it is. Otherwise it is just a giant grid with text in it.

I look forward to your solution.
Talsbard
 
Posts: 14
Joined: Wed Oct 12, 2016 9:27 pm

Re: Excel Formula Calculations

Postby Jane.Bai » Mon Aug 28, 2017 1:13 am

Hi Eric,

Thanks for your understanding.
Once there's any update, I will let you know.

Sincerely,
Jane
E-iceblue support team
User avatar
Jane.Bai
 
Posts: 953
Joined: Tue Nov 29, 2016 1:47 am

Re: Excel Formula Calculations

Postby Talsbard » Wed Oct 25, 2017 8:27 pm

Its been 2 months since this was last asked about. Any progress on this issue? I am being asked to seriously look at other products that claim the same functionality and have found at least one that has absolutely no lag in formula calculation on the same exact spreadsheet.

Thank you

Eric
Talsbard
 
Posts: 14
Joined: Wed Oct 12, 2016 9:27 pm

Next

Return to Spire.XLS

Who is online

Users browsing this forum: No registered users and 0 guests

cron