We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
close icon

Functions lost?

I have a very simple spreadsheet. I open it (similar to the example XLSFileUsingRW in the Calculate example). So, I open an Excel spreadsheet, use Named Ranges to set my values, and use this code to perform the calculation. _workBook.Engine.LockDependencies = false; _workBook.CalculateAll(); _workBook.Engine.LockDependencies = true; So far, so good. I call SaveAs to save the spreadsheet into a new name, and when I open it, no calculation has occured, and to make matters worse, the functions are not in the new spreadsheet. Am I missing something here (besides proper documentation?). Any help would be much appreciated!

4 Replies

DS Daryl Smith January 23, 2005 10:06 PM UTC

Just as an update, I ran the same code 4 more times. While it didn''t copy the formulas, at least it did the calculation 3 out of the 4 of those times. The reason I noticed the formulas were goin was simply becuase the calculations weren''t performed.


AD Administrator Syncfusion Team January 24, 2005 08:30 AM UTC

When you are writing out the ExcelRW workbook, are you using the exact one being used by the Calculate objects? Here is that sample modified to show a Save button. http://www.syncfusion.com/forums/Uploads/XlsFileUsingExcelRW.zip In this modified sample, if I change input values and press Compute Annual Premium to compute things, followed by pressing the save button, a test.xls file is saved in the exe folder, and when this file is opened with Excel, it shows the changed values and calculations for me. To make sure the code has access to the ExcelRW workbook object, at the bottom of ExcelRWCalcWorkbook.CreateFromXLS, a line was added to cach this object in a public field, and it is this cached object that is used to write thing out in the button handler. cwb.excelRWWB = wb; //added return cwb;


DS Daryl Smith January 25, 2005 05:39 PM UTC

I see the changes and calculation results, but the saved spreadhseet has lost everything except the results. For example, I load a spreadsheet, insert some values, perform a calculation. Then I want to save that spreadsheet somewhere else so I can continue to re-use the original spreadsheet at my template. The spreadsheet I have saved does not have any of the formulas from the original spreadsheet, just the results. Is this by design? Another way to look at it is like this: I open a spreadsheet, and save it somewhere else. Will all the formulas be gone in the newly saved spreadsheet? >When you are writing out the ExcelRW workbook, are you using the exact one being used by the Calculate objects? > >Here is that sample modified to show a Save button. > >http://www.syncfusion.com/forums/Uploads/XlsFileUsingExcelRW.zip > >In this modified sample, if I change input values and press Compute Annual Premium to compute things, followed by pressing the save button, a test.xls file is saved in the exe folder, and when this file is opened with Excel, it shows the changed values and calculations for me. > >To make sure the code has access to the ExcelRW workbook object, at the bottom of ExcelRWCalcWorkbook.CreateFromXLS, a line was added to cach this object in a public field, and it is this cached object that is used to write thing out in the button handler. > >cwb.excelRWWB = wb; //added >return cwb; >


AD Administrator Syncfusion Team January 25, 2005 11:39 PM UTC

This is by design. If you want to write out the formulas, then you will have to reset them in the worksheets. You can do this by adding this method to the ExcelRWCalcWorkbook class.
public void ResetFormulas()
{
	this.Engine.CalculatingSuspended = true;
	foreach(ExcelRWCalcSheet sheet in this.calcSheets)
	{
		for(int row = 1; row <= sheet.RowCount; ++row)
		{
			for(int col = 1; col <= sheet.ColCount; ++col)
			{
				//object o = sheet.GetValueRowCol(row, col);
				object o = this.Engine.GetFormulaRowCol(sheet, row, col);
				if(o != null)
				{
					string s2 = o.ToString();
					if(s2.Length > 0 && s2[0] == ''='')
						sheet.SetValueRowCol(s2, row, col);
				}
			}
		}
		this.Engine.CalculatingSuspended = false;
	}
}
You can then use this code to save the formulas. private void button3_Click(object sender, System.EventArgs e) { this.calcWB.ResetFormulas(); this.calcWB.excelRWWB.SaveAs("test.xls"); }

Loader.
Live Chat Icon For mobile
Up arrow icon