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!

Rosemari

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!

Rosemari

Just spent an hour searching for this, so i'll put this up here in case anyone else bumps into this: if you are using functions in excell, you must use the ";" character as a function parameter separator (example: IF(condition;trueval; falseva)). Inserting a formula in this format will result in a corrupted xlsx file. The fix: use "," instead of ";" when setting formulas using ClosedXML! – Kinny

@davy you lifesaver. – Memorize

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]";

Gotland

Answer 3

Additional comment to the quote referenced by @Lovethenakedgun:

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

. As far as I understand, that is still true, but there is a workaround to it. If you look into the sheet.xml file containing the table, you will see that the table-style references get translated to something like that:

"Table1[[#This Row],[testedName]]"

Now, you can actually use that in the code for the FormulaA1 property, just as I did below (in F#, tb is just a table object...):

tb.Name <- "Table1"
tb.Field(0).Name <- "testedName"
tb.Row(2).Cell(2).FormulaA1 <- "Table1[[#This Row],[testedName]]"
tb.Row(3).Cell(2).FormulaA1 <- "Table1[[#This Row],[testedName]]"
tb.Row(4).Cell(2).FormulaA1 <- "Table1[[#This Row],[testedName]]"

That gets translated to the [@testedName] in the workbook. You have to do it in every row separately though, as I did.

Shearer