Re: I have a problem in calcChain.xml => Excel has encountered unreadable datas.

Hello everybody, this is my first post in this forum so I hope you will be able to help me out on this one !

It's been two weeks I have this problem and I still cannot find where it comes from.

So, I'm generation an invoice that I prepared on Excel and reflected using the productivity tools.

I changed the source code to make it dynamicly filled and everything is correctly created (by the means of 4500 lines of code >

My problem is : when I open the created file, Excel tells me that he needs to repair it, then it display this error message (It's in french so I'm gonna translate it litteraly) :
Records deleted : Formulas in the part /xl/calcChain.xml (computational properties)

So please, I really need help on this one I just don't understand WHY it displays that to me.

Thanks for everything,

v3lkin

Hi v3lkin

Have a read of this:
"A classic problem is the calcChain.xml file.  This tells Excel in what order the calculations should be processed.  So if you delete a row that is referenced by the calcChain, Excel will complain.  However, if you simply remove the calcChain.xml from the package, Excel re-creates it when the file is opened - and does not complain!  So this is an easy fix.  The ExcelPackage assembly does exactly that - deletes the calcChain.xml file from the template so that Excel simply re-creates it when the file is opened."
-http://openxmldeveloper.org/articles/Creating_Spreadsheets_Server.aspx

So I don't know exactly what your spreadsheet's complaint is about - but you could simply remove the calcChain.xml and it should work.

Hello Intergen :-)

Thank you for replying !

I already saw that post and already tried to remove the C# source code generating the calchain.xml file... and well it juste doesn't work at all :s

When I compare the results, I also see that the order of my child nodes in the calcChain.xml is different. Excel puts them in an order I don't understand.

Hi v3lkin

I haven't seen this issue with my generated spreadsheets before - is it possible to provide a concise code example that can demonstrate this?

Hopefully I can replicate the issue here and help you out :)

Well, I have over 4400 lines of source code so I don't really know how to make it concise enough to put it out here... :s

Let me tell you what I'm trying to do :
To finalize my application, I need to generation quotations, delivery sheets and invoices. All in Excel format.

So what I did is : I prepared models (samples) of what I need to generate and used the document reflector to have the C# source code I needed. Then I've put 1 week of work to understand everything that was written, to replace the parts I needed to and put my products dynamicaly (You can sell 1 or 1000 products, it need to work).

I use the sharedStrings system even though I don't need it (every strings I use are unique), and then I generated all my calculationParts in the order it should be calculated (every product's lines, then the total, etc...).

Which parts would you like to see about my code ? :-)

Thanks for filling me in..  I think everything sounds good up until the calculation components - that's where I would imagine this problem resides.  Could you possibly show how the totals and their respective calculations are added?  I would think you calculate the total yourself and then set that as the value of its cell, and then set the cell's formula (i.e. Sum(x1:x2)) to what you would like Excel to adhere to.

I Actually don't calculate myself : I let excel find the good results.

Here the code for each rows, where I multiply the quantity and the unit price of the product :

Cell cell7 = new Cell() { CellReference = String.Format("G{0}", rowRef), StyleIndex = (UInt32Value)(isEnd ? 64U : 58U) };
CellFormula cellFormula7 = new CellFormula();
cellFormula7.Text = String.Format(
"D{0}*C{0}",
rowRef
);
cell7.Append(cellFormula7);



Here's my subtotal :

int start = 21;
int end = start + 2 + _compos.Count;

cellFormula4.Text = String.Format(
"SUM(G{0}:H{1})",
start,
end
);
cell186.Append(cellFormula4);


Here's my subtotal depending on the product's VTA (1 for 5,5% and 2 for 19,6%)

CellFormula cellFormula5 = new CellFormula();
cellFormula5.Text = String.Format("SUMIF(F{0}:F{1},1,G{0}:H{1})", start, end);
cell206.Append(cellFormula5);


And I have a few other sums but I write in exactly the same way everytime.

I hope it'll help.
Regards

I can't run into the issues you've had.. With this simple piece of code there are no complaints from Excel:

SheetData sheetData = new SheetData();

for (int i = 0; i < 10; i++ )
{
    Row row = CreateSpreadsheetRow(i);
    sheetData.AppendChild(row);
}

Row formulaRow = new Row();

// Create second cell to contain the numeric value
Cell formulaCell = new Cell();
CellFormula cellFormula = new CellFormula();
cellFormula.Text = "SUM(A1:A10)";
formulaCell.Append(cellFormula);

formulaRow.AppendChild(formulaCell);

sheetData.Append(formulaRow);

return sheetData;

I think you'll need to try removing your formula cells and see if the problem still exists - hopefully it wont, and then you can re-add them one-by-one to hopefully discover the offending cell

Thanks for the advice, I have to say I didn't try that.

Should I remove the code that generates the calcChain.xml before trying ?

Yes, I think so - of course make sure back up your code so that you know you can get any of it back easily