Search code examples
c#epplusformulas

EPPLUS issues with Formulas

Trying to write a simple formula using EPPLUS but cannot seem to find a way around it.

The formula is as follows: =C12-(SUM(D12:E12))

I have tried various combination but not too sure which option will allow me to define such a simple formula.

It always complains about "-" cannot be applied to string string etc.

Any ideas how to write such formula 

worksheet.Cells[this.CurrRow, CurrColumn + 1].Formula = "SUBTOTAL" + (worksheet.Cells[this.CurrRow, iStart_column - 1]) - ("SUM(" + worksheet.Cells[this.CurrRow, iStart_column, this.CurrRow, CurrColumn]);
Solution

  • If I understand what you are trying to do, you are generating the formulas dynamically. In which case, you are getting an error because you are using a math - on strings which, of course, cannot really be done. I think what you are going for is something like this:

    worksheet.Cells[this.CurrRow, CurrColumn + 1].Formula = "=SUBTOTAL("+ worksheet.Cells[this.CurrRow, iStart_column - 1].Address + "-(SUM(" + worksheet.Cells[this.CurrRow, iStart_column, this.CurrRow, CurrColumn].Address + ")))";

    Note the use of the Address properties which will give the string representation of the Cell objects.

    Note: Your implementation of SUBTOTAL is not really correct and Excel will complain about it when it opens the file. This is more an excel problem then a EPPlus so you will have to work that out or post more detail.