Forums Home / Spread / Spread for WinForms
Posted by: edinsf on 8 September 2017, 5:10 am EST
edinsf
Posted 8 September 2017, 5:10 am EST
I have a cell formula that references another cell on a different sheet. When I initially add the formula it works fine. The formula looks like this “Sheet1!R4C122”. However, when I save the sheet and reopen it the value in the cell that contains the formula is empty and the formula doesn’t work anymore. I’ve done a getformula on the cell and the formula is still there but the formula doesn’t work.
edinsf
Posted 8 September 2017, 5:10 am EST
We're saving using the fpSpread1.Save(filename) and .Open(filename) methods. And are saving in xml format. We don't want to save the file in an "xls" format.
There are quite a number of formulas and data that we need to save and load when we open a file. All the data and formulas on all of the sheets are being saved and continue to work after reloading except for the cross sheet formulas.
As I mentioned previously, when I look at the cells containing the cross sheet formulas the formulas are still there but they don't work. Further if I try to replace the formulas after re-opening the file, the cell seems to store the formula but it won't work.
Would it help if I emailed you the file?
reeva
Posted 8 September 2017, 5:10 am EST
Hello Edinsf,
The problem statement you have given is not clear. Can you please tell us how are you trying to save your sheet and opening it in spread? Here if I assume that you are saving and re-opening sheet using SaveExcel and OpenExcel method. Then to preserve the calculated values, you can set the ExcelSaveFlags to DataOnly while using SaveExcel method :
fpSpread1.SaveExcel("D:\\ReferencedFormula.xls",FarPoint.Excel.ExcelSaveFlags.DataOnly);
And then if you will use OpenExcel method to open an Excel sheet, all the calculated values will be there in their respective cells:
fpSpread1.Sheets[2].OpenExcel("D:\\ReferencedFormula.xls", "Sheet2");
Hope this will help. Else please share your observations on the same. Thanks.
edinsf
Posted 8 September 2017, 5:10 am EST
I’ve done some additional work trying to diagnose the problem. The save function we’re using is saving the formula and when I re-open the file the formula is still there. But it appears after re-opening that formula is not referencing the right cell in the second sheet. Furthermore it seems that it has something to do with the sheet name. If I try to rewrite the formulas without changing the sheet name the formulas still don’t work. However, if I change the sheet name and rewrite the formulas the formulas work. Until I save the file and reopen it.
scotts-fp
Posted 8 September 2017, 5:10 am EST
Hello,
If you could email the file for us to try to open and see the issue you are seeing, that would help. Also make sure you are using the latest maintenance relesae since there was a bug in a previous release of Spread for Windows Forms 4 that caused the issue you are seieng. If after making sure you are using the latest release and it is not working, try calling LoadFormulas method after opening the spreadsheet.
edinsf
Posted 8 September 2017, 5:10 am EST
Hi Scott,
The version of my spead.dll is 4.0.3512.2008 with a date of 8/14. Based on the downloads page that looks like the latest version. I also tried the loadformulas method with a command of
For intCtr = 0 To Fo frmMain.spdProj.Sheets.Count - 1
frmMain.spdProj.Sheets(intCtr).LoadFormulas(True)
Next
That didn't make any difference. How do I send the file to you?
Ed
scotts-fp
Posted 8 September 2017, 5:10 am EST
Ed,
When you reply to the post, you can click on the Options tab and click the button to attach the zipped file of the Excel workbook.
edinsf
Posted 8 September 2017, 5:10 am EST
Scott,
Attached is my file with the cross sheet formulas. When I saved the file the cross sheet formulas were working.
Thanks, Ed
scotts-fp
Posted 8 September 2017, 5:10 am EST
Ed,
This XML file only had one sheet saved in it. Did you send the wrong XML file?
edinsf
Posted 8 September 2017, 5:10 am EST
Scott,
I don't if the problem was you weren't seeing the second sheet because I was hiding the tabs. Never the less, attached is another file with the tabs visible. When I open it in the designer I see both sheets and in the designer the amounts from "Schedule 1" sheet in the cross-sheet formula cells are appearing. However, when I open the file from my application the amounts from the second sheet don't appear.
Ed
scotts-fp
Posted 8 September 2017, 5:10 am EST
Ed,
I was able to reproduce this issue. This is an already reported bug that will be fixed in the next maintenance release of Spread for Windows Forms 4.
edinsf
Posted 8 September 2017, 5:10 am EST
Scott,
Any time frames for the maintenance release?
Ed
sureshd
Posted 8 September 2017, 5:10 am EST
Ed,
We do not have an exact date yet, however next maintenance release Spread4(Windows) is expected in the mid of January.
Thanks
edinsf
Posted 8 September 2017, 5:10 am EST
Suresh,
Do you have a revised estimate of when the next maintenance release for Spread4 for Windows that will fix my problem will be released?
Thanks, Ed
sureshd
Posted 8 September 2017, 5:10 am EST
Ed,
I do not have the exact dates for the next maintenance release as yet, however we are expecting the same to be available by end of this month or early next month.I will keep you posted about the same.
brittf
Posted 8 September 2017, 5:10 am EST
We are seeing simiral behavior where our formulas after saving show #VALUE! when referencing another sheet. The formulas are valid, if you click inside a cell and back out the formula starts working. We our on product version 5.0.2003.2008. Is this the same issue and if so do you have a hotfix ready yet?
My save method:
fpsFpSpread.SaveExcel("C:\VBApps\SpreadSheetEval\Out.xls", FarPoint.Excel.ExcelSaveFlags.DocumentCaching)
Thanks!
2010/02/Out.rar
scotts-fp
Posted 8 September 2017, 5:10 am EST
Hello,
This is the same issue as the reported bug (#27196). The fix was not put into the source until after the release of version 5, so it will be in the next maintenance release.
zlt1
Posted 8 September 2017, 5:10 am EST
Hi,
If I open an existing Excel worksheet I get errors on the formulas. It appears to be the same issue as the one in this thread.
My formuluas are in the form of =ROWS($b$5:b6)-1
I am using version 5.0.3505.2008
Is this issue addressed in this release, if so I guess I have found another issue.
Please advise on what my next steps should be
Thanks
Nick
deepaksharma14
Posted 8 September 2017, 5:10 am EST
Hello Nick,
I am able to replicate this issue. I have reported this as a bug (#99917242). This should be fixed with in our next maintenance release of Spread.
Thanks,
scotts-fp
Posted 8 September 2017, 5:10 am EST
Hello,
This is not a bug but as design of the calculation enginge of Spread. Spread formulas does not support ranges where start row (or start column) and end row (or end column) have mixed absolute/relative settings. For example, SUM(A1:B2) and SUM($A$1:$B$2) are supported but SUM($A$1:B2) and SUM(A1:$B$2) are not supported. This restriction was due to the fact that Spread supports row and column formulas (something that Excel does not support) and it would have been difficult to support some mixes of absolute/relative settings for row and column formulas.
krishna.vajja
Posted 20 September 2019, 1:20 am EST
Is this solved? because we are also facing a similar issue.
Currently, we are using V12.45.20191.0.
While investigating we found the below exception for one of the cells contains a formula.
GrapeCity.Spreadsheet.API.Range.get_Summary(), Specified method is not supported.
A quick update is highly appreciated.
Thanks,
Krishna.
