According to the list of functions supported with the Workbook.CalculateFormulas method, Weekday is not supported. In Excel, this function returns a number from 1-7 (or 0-6) based on the weekday of a given date. Are there any plans to support this? I have 100's of worksheets that depend on this function to do some lookups, and get the error:
Unsupported function in formula calculation engine - code 70
Error in calculating cell B9 in Worksheet Weekly Data
Rick
Ok, tried to replace the weekday function, but still getting an error from doing a Worksheet.CalculateFormulas:
Invalid cell name
Error in calculating cell B9 in Worksheet Weekly Data
The formula in cell B9 of the referenced sheet:
=INDEX(INDIRECT("'Daily Data'!A9:A15"),7-(MOD(INDIRECT("'Daily Data'!A9")-DATE(2006,9,30),7)),1)
The referenced sheet has dates from 10/1/2006 through 10/7/2006 in A9 - A15. The formula should return the date of the Friday within the range, and it works in Excel (returns cell A14, 10/6/2006). According to the list of supported functions, INDEX, INDIRECT, MOD and DATE are all supported by Aspose.Cells.
Rick
Hi,
Thanks for providing us details.
We found the issue after an initial test. We will look into it and get back to you soon.
Thank you.
Any update or estimated timeframe on this?
Rick
Hi Rick,
Please try the attached version. We have supported to calculate Weekday function.
For the issue of calculating formula i.e., "=INDEX(INDIRECT("'Daily Data'!A9:A15"),7-(MOD(INDIRECT("'Daily Data'!A9")-DATE(2006,9,30),7)),1)", please simply change it to "=INDEX("'Daily Data'!A9:A15",7-(MOD(INDIRECT("'Daily Data'!A9")-DATE(2006,9,30),7)),1)", it will work fine.
Thank you.
The new version now correctly handles the calculation, thanks!
From your message, I'm not sure if you now support the Excel Weekday function, in which case I could change the formula to "=INDEX(INDIRECT("'Daily Data'!A9:A15"),WEEKDAY(INDIRECT("'Daily Data'!A9")),1)". If not, the original formula does work correctly.
I use the INDIRECT function in the formula because some rows will be deleted in the referenced sheet and I don't want Excel to change the range it looks at.
Rick
I spoke too soon (I forgot to uncomment the workbook.CalculateFormula() line). I now receive the error "Invalid cell name Error in calculating cell A9 in Worksheet Weekly Data", which is used to calculate the weeknumber (again, I had a UDF for this). I've attached the spreadsheet.
Note that this is also the sheet I'm having problems with in converting all formulas to values. It still takes about 15 seconds for the Weekly Data sheet, 4 seconds for the Monthly Data sheet. I'll post the code in my other thread.
Rick
Hi Rick,
Well, I tried your scenario and I don't find any error calling Workbook.CalculateFormula() with your template file (provided). Since your template file has a huge list of diverse formulas, so it may take some time to calculate all the formulas in the workbook, we will look into it too.
For your information, if you don't want the calculated results of the formulas at runtime and only need to replace the formulas with values, please do not call Workbook.CalculateFormula() in your code.
Also, please check your second thread: http://www.aspose.com/community/forums/172615/copy-paste-special/showthread.aspx#172615 where I have posted some updated codes if it suits your requirements.
Thank you.
The problem is that I need to add 1 (or more) rows each day on the daily sheet, and may update other rows in that same sheet. After I add those rows I need to force a recalc of the weekly and monthly numbers. With the other errors resolved, calling workbook.CalcFormula() takes over 2 minutes to recalc (130990 ms).
Rick
Hi,
Could you explain in detail your formula i.e..,
=INT((B13-SUM(MOD(DATE(YEAR(B13-MOD(B13-2,7)+3),1,2),{1E+99,7})*{1,-1})+5)/7)
And, we are working on the performance of the Aspose.Cells formula calculation engine.
Thank you.
That's one of the cases where I had a UDF and replaced it with a function. The function itself calculates the ISO week number for a given date and is discussed on several web pages (see http://www.cpearson.com/excel/weeknum.htm for example).
I'm in the process of removing that calculation (it's a carryover and isn't needed any longer) so that won't be a problem for me. I've attached an updated spreadsheet with that column removed.
I still do have a problem with cell A9 on the Weekly Data sheet. If I use the formula "=INDEX('Daily Data'!A9:A15,7-(MOD(INDIRECT("'Daily Data'!A9")-DATE(2006,9,30),7)),1)" the workbook.CalculateFormula() call does work correctly (although slowly). If I change the formula in A9 to "=INDEX(INDIRECT("'Daily Data'!A9:A15"),7-(MOD(INDIRECT("'Daily Data'!A9")-DATE(2006,9,30),7)),1)" I get the error "Invalid cell name Error in calculating cell A9 in Worksheet Weekly Data". It looks like it doesn't like the range included in the INDIRECT call, since it does work with a single cell (INDIRECT("'Daily Data'!A9")) but not with a range of cells (INDIRECT("'Daily Data'!A9:A15")).
I use the indirect in the formula as the user can delete a range of cells from the Daily Data sheet so that it starts on a later date. Without the indirect, Excel tries to adjust the range reference and I need it to always look at A9:A15.
Rick
Hi,
Thanks for providing us details with the template file.
We will look into your issue and get back to you soon.
Thank you.
Hi,
Please try the attached version. We have supported to calculate the functions i.e.., Weekday and WeekNum. And, we have improved the performance of calculating the formulas of your template file.
Kindly let us know if it works fine now.
Thank you.
First, thanks for the quick response! Your support is as good as anyone I've worked with.
Now for the bad news...
The performance has improved almost 15%, now at aboud 120 seconds down from 140 seconds. However, I have almost 200 of these files that need to be run 3 times each day over a span of 8 hours. At 2 minutes per report * 200 reports, it would take almost 7 hours just to do one update.
Currently I'm using Excel automation, which takes less than 10 seconds per report (and some of that is opening and saving), but automation has it's own set of problems (mainly the fact that I don't get any type of information back from Excel if there are errors).
I suspect part of the problem is that each cell has to perform the same calculation twice (first to get the value to test, then to get the value for the cell) and I'm trying to streamline that. But even if I can remove half the calculations, it still seems like I'll be at a minute per report, over 3 hours for 1 run.
Because there are macros in each report so we can manually update individual reports, I can't remove the calculations. However, it's posssible I could do the calculations in VB.net when running all the reports. The 2 sheets with all the calculations calculate weekly and monthly averages based on the daily numbers, but I'm not sure if it would be that much faster iterating through all the cells with for-next statements, calculating the averages on the fly, and then putting those values into the 2 sheets?
I guess the main question is, how much more improvement do you think you might be able to get from your calculation engine for my specific case?
Rick
Hi,
Thanks for providing us further details.
We will get back back to you soon.
Thank you.
Hi,
Please try the attached version. We have improved the performance for calculating the formulas of the template file. It takes about 10 seconds to calculate the formulas in our machine (Intel CPU T2400 @ 1.83GHZ,1.5G memory)
Thank you.
On my machine (Athlon 2.5Ghz X2, 1.75G memory) it still takes 2 minutes to do the recalc (124 seconds). However, I've written code to do the same thing by iterating through the daily sheet and that takes about half a second (645 ms). I'm not sure why the huge difference in recalc times between our 2 machines? I did download and copy the new dll, and it does show version 4.7.0.11.
Rick
Hi,
We appreciate if you could create a simple project and post us here to show the issue, we will check it soon and it may help us to figure out your issue. And, kindly make sure that you are using the latest version (4.7.0.11) of the component on your machine.
Thank you.
Attached is a spreadsheet, along with a text file of the code I'm using. The Excel workbook should be almost identical to the one I sent originally. I'm using Aspose.cells 4.7.1.
Rick
Hi Rick,
Thanks for providing us the template file with code.
Well, I have tested and it actually takes about 7.8 seconds to complete the calculation formulas process on my machine. It looks strange to us as you are getting extra time to process the calculation process, do you run your code in vs.net or run the exe (portable executable file) outside of vs.net?
Could you try your test case in some other machine if it takes the same time.
By the way, here is my test case code:
Workbook workbook = new Workbook();
workbook.Open("f:\\test\\TestCalc.xls");
DateTime start = DateTime.Now;
workbook.CalculateFormula();
DateTime end = DateTime.Now;
TimeSpan time = end - start;
MessageBox.Show(time.TotalSeconds.ToString()); //it takes about 7.8 seconds
workbook.Save("f:\\test\\outcalctest.xls");
We will further investigate your issue and explore why you are getting extended time to finish the process on your machine.We still appreciate if you create a sample solution in your vs.net using your template file with new version 4.7.1, zip the project and post it here, we want to check and run it on our machine.
Thank you.