Functions lost?

[Daryl Smith]

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]

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]

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]

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]

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