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.
Unfortunately, activation email could not send to your email. Please try again.

Functions lost?

Thread ID:

Created:

Updated:

Platform:

Replies:

23895 Jan 23,2005 04:45 PM Jan 25,2005 06:39 PM Windows Forms 4
loading
Tags: Calculate
Daryl Smith
Asked On January 23, 2005 04:45 PM

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!

Daryl Smith
Replied On January 23, 2005 05:06 PM

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.

Administrator [Syncfusion]
Replied On January 24, 2005 03:30 AM

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;

Daryl Smith
Replied On January 25, 2005 12:39 PM

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; >

Administrator [Syncfusion]
Replied On January 25, 2005 06:39 PM

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"); }

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;