This project has moved. For the latest updates, please go here.

Strange Export Formula

Jul 24, 2011 at 4:24 PM

I was exporting and it does very well but there is one formula which I can't make it work. Formula its simple:

const string col = "ABCDEF";

foreach ( char columna in col )
{
    i++;
    worksheet.Cell ( cont, i ).FormulaR1C1 = String.Format ( "=SUM({0}{1}:{0}{2})", columna, ( temp + 2 ), ( cont - 1 ) );
}

Column A good, B good too, C bad, D, E and F good.

Excel shows something like: 

=SUMA(A19:A19)
=SUMA(B19:B19)
=SUMA($S:$S) <-- This is C
=SUMA(D19:D19)
=SUMA(E19:E19)
=SUMA(F19:F19)

What could be the problem?

Sorry for my english.

Coordinator
Jul 24, 2011 at 4:41 PM

Now that's odd. I'll work on it...

Coordinator
Jul 24, 2011 at 10:49 PM

The library is doing the right thing. You're passing an A1 formula ("Sum(A19:A19)") to the property .FormulaR1C1 when you should be passing it to the corresponding .FormulaA1

In the case of Sum(A19:A19) the library doesn't know what to do with the A19 so it leaves it alone. In the case of Sum(C19:C19) it assumes you're referring to [C]olumn 19 = S (once again because you're using R1C1).

Hope this clarifies the issue.

Jul 24, 2011 at 11:15 PM

Hmmm never tried that way... thanks :) and by the way great library, I used Interop but this is much better and faster to create xlsx files. I made an article about this library, hope more people use it.