After adding formulas I get the following error while opening: "Excel found a unreadable content in 'x.xlsx'. Do you want.."

Mar 12, 2012 at 2:56 PM
Edited Mar 12, 2012 at 3:22 PM

Hi,

 I'm a newbie in ClosedXML, so, any help\advice is welcome.

 after setting a formula in a cell.

cell.FormulaA1 = string.Format("SUMIFS($C$50:$C$10000;$E$50:$E$10000;{0}$2;$D$50:$D$10000;$A{1})",Utilities.getColumnNameFromIndex(2+i),k));

 I got the error: "Excel found a unreadable content in 'x.xlsx'. Do you want..", this error prevents me from checking the file content to try to find out what is wrong with it, since all the formulas are removed after excel tries to fix it.

 If I set the cell.Value to the string:

cell.Value= string.Format("SUMIFS($C$50:$C$10000;$E$50:$E$10000;{0}$2;$D$50:$D$10000;$A{1})",Utilities.getColumnNameFromIndex(2+i),k));

and afterwards add a = before the string (already using ms excel) it works correctly and the value is calculated correctly without any problems.

I downloaded open office and tried to open the file, and all the formulas had an error, instead of ; to separate the SUMIFS arguments I found a |

 Instead of: =sumifs($C$50:$C$10000;$E$50:$E$10000;B$2;$D$50:$D$10000;$A3)

 I got a; =sumifs($C$50:$C$10000|$E$50:$E$10000|B$2|$D$50:$D$10000|$A3)

Am I doing anything wrong?

 

Thanks,

Luis Pinho

 

P.S: Log from Excel, the excel file has 4 worksheets, don't know about the last part of the error:

Removed Records: Formula from /xl/worksheets/sheet.xml part

Removed Records: Formula from /xl/worksheets/sheet2.xml part

Removed Records: Formula from /xl/worksheets/sheet3.xml part

Removed Records: Formula from /xl/worksheets/sheet4.xml part

Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)

Coordinator
Mar 12, 2012 at 3:21 PM

You probably have separate the parameters with commas. See http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx

Mar 12, 2012 at 3:26 PM
mdeleon wrote:

You probably have separate the parameters with commas. See http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx

Thanks a million, it was just that, it is strange that it converts the commas to the ;

Thanks again for your help,

Luís Pinho