Complex Formula

Sep 27, 2010 at 4:59 PM

Hi,

I want to use your library to make some complex formula like this :  

=SUM(A2:C2)

Is that possible with the 1.4.0.0 release?

For example :

            cell = new ExcelCell();
            cell.type = eCellType.Formula;
            cell.text = "=SUM(A2:C2)";
            row.AddCell(cell);

doesn't work.

After the conversion, the Excel cell contains : =SUM('A2':$B:$B)

Thanks for your help.

Best regards.

Coordinator
Sep 27, 2010 at 6:05 PM
Edited Sep 27, 2010 at 6:08 PM

You can't use A2/C2-style naming. You must use R2C1/R2C3-style naming for cells position:

cell.text = "=SUM(R2C1:R2C3)";  //will be converted into "=SUM($A$2:$C$2)"

or

cell.text = "=SUM(RC[-3]:RC[-1])";  //will be converted into "=SUM(A2:C2)"

where R - row number, C - cell number in row. If you use R[-x]C[-x]/R[x]C[x]-style, then position will be calculated from position of the current cell

Sep 27, 2010 at 6:56 PM

Ok thanks a lot!

And is that possible to make a formula in a different table?

Coordinator
Sep 28, 2010 at 2:51 AM

Yes, there is no limits :) cell position will be something like TableName!R1C1

You can even use another document for data. Something like: [DocumentName.xls]TableName!R1C1