This project has moved and is read-only. For the latest updates, please go here.

Excel Formulae

Jun 13, 2012 at 9:46 PM

I was looking for a way to render an Excel formula to a cell (using the OpenXML writer.) Nothing overly fancy in terms of referring to other columns by name or anything just using R1C1 notation to keep it simple. Anybody got any ideas on how to do that?

Jun 13, 2012 at 10:04 PM

Answering my own post!

How about this mod to the RenderTextItem method on

DoddleReport.OpenXml.ExcelReportWriter


private static int RenderTextItem(IXLWorksheet worksheet, int fieldsCount, string itemText, int currentRow, ReportStyle reportStyle) { foreach (var s in itemText.Split(new[] { Environment.NewLine }, StringSplitOptions.None)) { currentRow++; var row = worksheet.Row(currentRow); var cell = row.Cell(1); // Dirty hack to bung a formula into the cell if (s[1].Equals("=")) { cell.FormulaR1C1 = s; } else { cell.Value = s; } reportStyle.CopyToXlStyle(cell.Style); worksheet.Range(currentRow, 1, currentRow, fieldsCount).Merge(); } return currentRow; }

Aug 1, 2012 at 5:08 AM

This could work. I didn't actually write the OpenXml ExcelReportWriter (it was contributed by the community), but were you able to make this change and confirm it worked in your scenario?