#Recursive reference! and Value problem

In a workbook I want to copy a sheet to an other workbook and replace all formulas with the value.

I have this code to do that:
foreach (Cell cell in srcSheet.Cells)
if (cell.IsFormula)
cell.PutValue(cell.Value)

On some cells I get an #Recursive reference! result and on other cells the value is 0 although it has a value.

In the attached document the problems occured. I try to copy the sheet named ‘Rap Nederlands’ to a new workbook.
The #Rec ref! result is in cells C13 to C28.
The value is always 0 in cells L13 to L28 (but some are actually 1). While debugging I noticed the cell.DoubleValue is always 0.0 while cell.FloatValue is 1.0.

Hope you can help me. I’m using the latest version of Aspose.Cells 4.0.1.0.

Stief

Hi Stief,

Is this file your source file or output file? Which sheet do you want to copy to another workbook? Could you please post more of your code here? Thank you very much.

Laurence

The previously attached files was the source file. From this file I want to copy the sheet named 'Rap Nederlands' to a new workbook.

My complete code is:

Workbook srcExcel = new Workbook();
srcExcel.Open(srcFileName);

Workbook destExcel = new Workbook();
destExcel.Open(destFileName);

Worksheet srcSheet = srcExcel.Worksheets[srcSheetNumber];
srcExcel.CalculateFormula(true);

foreach(Cell cell in srcSheet.Cells)
{
if(cell.IsFormula)
{
cell.PutValue(cell.Value);
}
}

Worksheet destSheet = destExcel.Worksheets[destSheetNumber];
destSheet.Copy(srcSheet);

destExcel.Save(destFileName, FileFormatType.Default);

Hope this helps.

Stief

Aspose.Cells formula calculation engine doesn’t support some Excel functions in your file. I will make it soon.

Please try this attached version.

Hi Laurence

You saved the day (agian).

The Excel is now rendered perfectly. I also had an other Excel file where not all formulas where replaced by text correctly, but your updated version also solved those problems.

Stief.

Laurence

I don’t want to spoil the fun but I noticed one small mistake in the copied sheet.

Results between 0 and 1 lose there leading zero and the number is rounded to 3 digits. So 0,48 becomes ,480. I noticed it in the cells C13 and below.

Stief.


I don't find this problem. Please check this attached output file.

Could you please post your output file here?

And what's the language and regional setting in your machine?

Laurence

Your output looks perfect, but mine is not.

You can find my version attached to this message. Also the graph’s source data is no longer valid (it gives ‘#REF’ for the X and Y values).

I have an English version of Win XP. My regional settings are Dutch (Belgium). It’s not the default for this region to drop leading zeros.

Stief

Hi Stief,

When I opened your output.xls, it seems that the file is crashed. Could you please post your whole project here? I will check it ASAP.

Laurence

Hopefully you can open this output file.

Unfortunately it’s not possible to send you the project. The project is to complex to just zip it and send it. (5 C# projects, several external components, couple of thousands files).

Stief

My output file is generated with following piece of code:

string srcFileName = "d:\\test\\7295_R04_026_1.xls";
string destFileName = "d:\\test\\abc.xls";
Workbook srcExcel = new Workbook();
srcExcel.Open(srcFileName);

Workbook destExcel = new Workbook();
Worksheet srcSheet = srcExcel.Worksheets["Rap Nederlands"];

srcExcel.CalculateFormula(true);

foreach(Cell cell in srcSheet.Cells)
{
if(cell.IsFormula)
{
cell.PutValue(cell.Value);
}
}


Worksheet destSheet = destExcel.Worksheets[0];
destSheet.Copy(srcSheet);

destExcel.Save(destFileName, FileFormatType.Default);

Could you please run it in your machine to see if it works fine? And what happens if you change your language and region settings to English(US)? Thank you.

Hi Laurence

Sorry for my late response but I have been working on an other project for a few days.

My code is exactly the same as yours, but leading zero’s are still missing after copying the sheet. Even changing the regional settings to en-US didn’t change anything.

I also tried to specify the cell using numbers (in the cell properties window) in the source document. But also no change here.

While debugging the code I can see the cell has these values:
cell.StringValue = "-,820"
cell.DoubleValue = 0.0
cell.IntValue = 0
cell.FloatValue = <error: an exception of type: {System.Exception}occured>
Finnaly the StringValue is used by the PutValue method.

Maybe you still have something up your sleeve to help me?

Stief

1. Please create a simple console or winform application with my sample code. Otherwise, the regional settings won't take effect.

2. It seems that decimal point in your machine is ",", not ".". Please change it to default settings with en-US.

3. If it still doesn't work, please zip and post your whole project with my sample code here. Thank you.