CODEWITHSUNDEEP
c#.netexcel-formulaspreadsheetepplus
Al2110
Al2110

Reputation: 576

Applying formula to multiply values in two columns for each row using EPPlus

I have a program which writes a number of rows to an Excel spreadsheet:

int row = HEADER_ROW + 1;
foreach (var i in items)
{
    worksheet.Cells[row, 1].Value = i.description;
    worksheet.Cells[row, 2].Style.Numberformat.Format = "$0.00";
    worksheet.Cells[row, 2].Value = i.amount; // this is a decimal
    worksheet.Cells[row, 3].Value = i.quantity; // this is an int

    // multiply the values in cols 2 and 3 and display the result in col 4
    //worksheet.Cells[row, 4].Formula = ?

    row++;
}
worksheet.Calculate();

How can I ask EPPlus to create a formula in column 4 of each row being written to, which multiplies the values in columns 2 and 3? That is, achieve the same as manually typing in the Excel spreadsheet itself the formula "=B6*C6" (or whichever the first row is), and then dragging it down the column.

Upvotes: 2

Views: 821

Answers (1)

VDWWD
VDWWD

Reputation: 35514

You can use a Range for that.

var range = worksheet.Cells[1, 4, worksheet.Dimension.End.Row, 4];
range.Formula = string.Format("{0}*{1}", worksheet.Cells[1, 2].Address, worksheet.Cells[1, 3].Address);

Upvotes: 2

Related Questions