ClosedXML Format cell to contain formula

Question

I'm hoping someone can help me with ClosedXML as I'm new to Excel exporting and from what I've seen of ClosedXML the documentation is fairly limited in certain areas.

At the moment I'm putting data into a Datatable, formatting rows to their correct type and exporting with the correct layout.

The problem occurs when I attempt to export one row containing a repeating formula in each cell.

I have tried to add the formula simply as a string which I can then highlight and convert when the file is exported, this is obviously not Ideal. I found a class in XML called XLFormula which has absolutely no documentation but assume I should be doing something with this.

At the moment I have (commented out is the way I was using XLFormula, was trying to pass XLFormula the formula as a string and set as total bid per unit):

dt.Columns.Add("Qty", typeof(int));
dt.Columns.Add("Bid Per Unit GBP", typeof(double));
dt.Columns.Add("Total Bid GBP"); //typeof(XLFormula)
foreach (DataRow dr in dt.Rows)
{
    //XLFormula totalBidFormula = new XLFormula();

    dr["Qty"] = 1;
    dr["Bid Per Unit GBP"] = 0.00;
    dr["Total Bid GBP"] = "=[@Qty]*[@[Bid Per Unit GBP]]";

Any Help would be greatly appreciated. If what I'm trying to do is impossible with ClosedXML please let me know and if you could suggest an alternative XML exporter (even if its paid) that would help!

Answer 1

Just in case anyone stumbles across this who is in the same position I was, formulas in closedXML are pretty straightforward.

From what I understand formulas must be applied directly to the cell after your values have been added.

I used a simple for loop to get the current cell as the sheet was processed as I needed an entire column to hold a formula

//get all rows in my datatable 
int totalRows = dt.Rows.Count;

//set the first that will be filled with data
int currentRow = 1;

//loop through each row.
for(int i = 0; i < totalRows; i++){

//the current cell will be whatever column you wish to format + the current row
string currentCell = "F" + currentRow;

//create your formula
string AdjustedPriceFormula = "=C" + currentRow + "*" + "D" + currentRow;

//apply your formula to the cell.
theSheet.Cells(currentCell).FormulaA1 = AdjustedPriceFormula;

//increment your counters to apply the same data to the following row
currentRow++

This has worked for me several times since and adding multiple formulas to multiple columns/cells.

Hope This helps someone!

Answer 2

I came across this post debugging the same issue you were having. Went through some comments on the ClosedXML Github issue tracker and came across a comment on this thread:

Table column references are not supported yet. Please use normal A1 style references.

You can also use the older "R1C1" style references if you'd prefer, but to achieve what you want, you'd use something akin to:

dr["Total Bid GBP"].FormulaR1C1 = "=RC[-2]*RC[-1]";