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;
formulaColumns.Sort();
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
}
}
}
ws.Calculate();
}

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