In our organisation, people use an Excel spreadsheet with tabular data and a few formulas, which I need to connect to a data provider in C#. With OpenXML, or even better, ClosedXml, we can create or update Excel documents using C# code. However, there is one feature that I cannot yet do in C#, and that is copying a formula to other rows with automatic update of the cell references. The formula looks like this in a cell on row 5:
=IFS(P5=$B$24,$E$24,P5=$B$25,$E$25,P5=$B$26,$E$26
The builtin library function IFS is called with the value in cell P5, and a lookup table in fixed cells in columns B and E, and rows 24-26.
In Excel, it is very easy to add (insert) a new row below row 5, and to copy the formula to this new row by dragging the lower-left corner of the cell with the mouse. The formula is automatically changed to refer to P6 instead of P5, while the cell references with dollar signs are kept unchanged (absolute reference).
For copying a formula in C#, I have not found a way to automatically update P5 to P6.
In Excel, using the macro recorder, I found the following commands:
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Auto fill range
selectedSheet.getRange("C3").autoFill("C3:C5", ExcelScript.AutoFillType.fillDefault);
}
This is in another Excel sheet. It basically takes the content from C3, and copies it to the next two rows: C4 and C5, using command autoFill . It does update the references as described above.
I have not found an autoFill feature in ClosedXml, but there is one in OpenXml:
but the problem is that I don't have any idea how to use this. There is no code example. Google did not find examples either.
I did write a bit of OpenXml code that reads a cell value, but fails to write one:
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
public void UpdateExcel(string FileNameXlsx)
{
var isEditable = true;
using (SpreadsheetDocument document =
SpreadsheetDocument.Open(FileNameXlsx, isEditable, new OpenSettings()))
{
WorkbookPart workbookPart = document.WorkbookPart;
Workbook workbook = workbookPart.Workbook;
Sheet sheet = workbook.Descendants<Sheet>().First();
WorksheetPart wsPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
Worksheet ws = wsPart.Worksheet;
Cell cell_L5 = ws.Descendants<Cell>().First(c => c.CellReference == "L5");
Cell cell_L6 = ws.Descendants<Cell>().First(c => c.CellReference == "L6");
cell_L6.CellFormula = cell_L5.CellFormula;
document.Save();
}
}
The minor problem is how to write this code in a simpler way, and how to write a value to a cell.
The main problem is how to get the autoFill feature in this code.
The reason to try to get the autoFill to work is that we want to separate concerns: the "user" is responsible for the actual formula in the first data row, and I as a developer am responsible to create more rows with data from a database, and using the formula that happens to be present in the first data row. I am in a sense using the input Excel as a template. Of course, I could write correct formula's in all rows if the formula above would be stable, however, it is foreseen that the user(s) may actually alter the formula, and my system should work with any formula that is in the first datarow. Admittedly, this is kind of strange and low-tech, but, for now, this is the situation, and, if possible, I could make people really happy with the autoFill feature.
You can do this in ClosedXML by retrieving the cell.FormulaR1C1 (which is relative by nature) and setting the target cells' .FormulaR1C1 property accordingly.