EPPlus Table @ Column reference

Question

I'm trying to use EPPlus to create a table on a worksheet. I can create the table, but all my @ variables become #Ref! when opening up the file. If I paste the exact same formula into Excel it takes it and has no problem. What am I missing here? Do I need to apply the table somehow after creating it? Thanks, Lee

private void ProcessVehicleData(BorrowingBase bbData, ExcelWorksheet ew, int colStart, int rowStart) {
    int origFirstRow = rowStart;
    foreach (DailyCAPS data in bbData.DailyCAPS) {
        FillRow(ew, data, colStart, rowStart);
        ++rowStart;
    }

    try {
        ExcelAddressBase eab = new ExcelAddressBase(origFirstRow - 1, ExcelColumnNameToNumber("A"), rowStart - 1, ExcelColumnNameToNumber("Y"));
        ExcelTable et = ew.Tables.Add(eab, "VehicleData");

        if (origFirstRow != rowStart) {
            ew.Cells[origFirstRow, ExcelColumnNameToNumber("Y")].Formula = "=IF([@Inventory Days]>210,\"H\",IF([@TitleApp]+[@UtahTitleReceived]=0,\"B\",\"\"))";
        }
    }
    catch { }
}

score 2 · Answer 1 · answered Jan 21 '20 at 16:50

2

See comments for answer...github.com/JanKallman/EPPlus/issues/521

answered Jan 21 '20 at 16:50

Lee

41
4

score 0 · Answer 2 · answered Jan 21 '20 at 02:02

0

No,epplus can't do it.
Because epplus Tables.Add is only pure data fill not workbook query,so =[@XXX] is not work.

answered Jan 21 '20 at 02:02

Wei Lin

3,591
2
20
52

@Lee epplus not support it or you can post a issue on github... so no solution for this question unless you change the lib or tool like vba. but it's anothor question. – Wei Lin Jan 21 '20 at 03:32
So I looked on github (thanks for the recommendation) and found the following article: https://github.com/JanKallman/EPPlus/issues/521 – Lee Jan 21 '20 at 06:20
Apparently using the table title and column name you can indeed use references. ie, Table[ColumnName] works fine from epplus to Excel – Lee Jan 21 '20 at 06:21

EPPlus Table @ Column reference

2 Answers2