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
|
|