Data Browser - Viewing Site  Sector 23 Code Bank Logged in as:  Guest  


Adding Hyperlink or other formula to Excel using EPPlus
To add a hyperlink or other formula to EPPlus, you must use version 4 or higher.
You can set a formula in a cell using:

Option 1: Manual cell entry

public void SetFormula(ExcelWorksheet ws, int row, int col, string formula)
ws.Cells[row, col].Formula = formula;

You must call ws.Calculate() to update all displayed values for formulas prior to saving.

Option 2: Import from DataTable (uses above function)

If you imported a range of data using LoadFromDataTable for example, you may need to go back and turn certain columns into formulas.

// assume dtData is a datatable that has already been loaded into the worksheet
// assume formulaColumns is a List<int> defining which column indices in dtData are formulas.
// The values in dtData for formula columns should look like either '=SUM(1,2)' or 'SUM(1,2)'.

if (formulaColumns != null && formulaColumns.Count > 0)
string formula;
for (int r = 0; r < dtData.Rows.Count; r++)
foreach (int c in formulaColumns)
formula= dtData.Rows[r][c].ToString();
if (!string.IsNullOrWhiteSpace(formula))
formula = formula.TrimStart('='); // remove any = since EP+ will add it back
this.SetFormula(ws, r + 2, c + 1, formula);// EP+ has 1-based indexing, not 0, and header row counts as 1

Created By: amos 3/23/2015 11:55:00 AM