EPPlusSoftware
When the contents of ExcelTable are referenced, Contiguous cell Range assignment formulas cannot be used
EPPlus usage
Noncommercial use
Environment
Windows
Epplus version
7.4
Spreadsheet application
Excel365
Description
When a user references ExcelTable's formulas in cells other than ExcelTable, using structured reference (#This Row), an error occurs when multiple cell formulas are assigned to Contiguous cell range at once
If you don't use the continuous range assignment formula, the relative and absolute references to the formula will be very difficult to handle, and I think this should be a bug to deal with.
var worksheet = package.Workbook.Worksheets["Sheet1"];
var excelTable = worksheet.Tables[0];
[testExcelTable.xlsx](https://github.com/user-attachments/files/17452770/testExcelTable.xlsx)
var col = excelTable.Range.Offset(0,10).TakeSingleColumn(0).SkipRows(1);
var formulaStr = col.TakeSingleCell(0, 0).Formula;
col.Formula = formulaStr;
These are array formulas. Use the CreateArrayFormula(formulaStr, true); for each cell when copying this.
We will look at adding better support for this in a coming version.
It is not an array formula, I just need to copy the formula of the connected area according to the formula of the first cell, similar to the effect of VBA interface Fill Down, in EPPLUS, if there is no structured reference to ExcelTable, you can select a continuous area and then assign the formula value to it. The relative and absolute reference of formulas are processed automatically.
As shown in the image below, what I ultimately need is that after the formula is assigned to the selected continuous region, the content of the formula is different, if it is an array formula the content of the formula is the same.
If the formula is the following way, it is normal
Excel will create this formula as an array formula...
<c r="K2" cm="1">
<f t="array" ref="K2">表1[[#This Row],[列5]]+M2</f>
<v>2</v>
</c>
If it's not an array formula, you can set it as usual with the Formula property
Finally, I can only use this copy method to go through all the cells to achieve this, but I don't know how the performance is?
var sht = package.Workbook.Worksheets["Sheet1"];
var srcCell= sht.Cells["K2"];
var fillRange = sht.Cells["K3:K5"];
for (int i = 0; i < 3; i++)
{
var dstCell = srcCell.Offset(i + 1, 0);
srcCell.Copy(dstCell);
}
