Change Named range EPPlus

Question

I'm using EPPlus to populate an excel file, I have many pivot tables that refresh when the file is opened.

Their source is always one of the two sheets populated by my application.

The problem is that everytime I populate there is a different number of rows, I tried using a named range in their source but when I change the range of the named range with EPPlus , it doesnt work.

   epplusWs.Workbook.Names["named_range"].Address = "data_sheet!$A$1:$H$" + row.ToString();

can you paste the code you are using to create your pivot tables? — Yahya Hussein, Jan 09 '18 at 13:01
I'm not using EPPlus to create them, I have a template with them created with the named range as the data source, — Carlos Siestrup, Jan 09 '18 at 14:00
so what is the problem? you are updating the address of named range address and the updates are not taking affect? — Yahya Hussein, Jan 09 '18 at 14:59
Yes, I also tried a dynamic range using a formula but EPPlus gives an error when saving the file if there is a macro or dynamic range in the file. — Carlos Siestrup, Jan 09 '18 at 16:02
Would something like this work? : https://stackoverflow.com/questions/33282955/defining-a-table-rather-than-a-range-as-a-pivottable-cachesource/33312038#33312038 — Ernie S, Jan 10 '18 at 22:44
No,the number is in row.ToString(). I think EPPlus doesnt support changes in the named ranges or source of pivot tables. — Carlos Siestrup, Jan 19 '18 at 12:51

score 1 · Answer 1 · answered Jul 19 '18 at 22:44

1

This is a late answer but you could add a new named ranged like this:

    ExcelNamedRange NR = new ExcelNamedRange("named_range", sheet: worksheet, nameSheet: worksheet, address: <your address string here>, index: 1);
    package.Workbook.Worksheets[1].Names.Add("named_range", NR);

answered Jul 19 '18 at 22:44

Lyco

549
3
14

just an fyi, this solution doesn't work any more since the version 5.1.2 - this constructor scope is now set to internal in this version, it is recommended to use Gilles Boisson way instead – Kalpesh Popat May 30 '20 at 05:37

score 1 · Answer 2 · answered Nov 02 '18 at 09:33

1

I find a way to edit an existing one :

var range = epplusWs.Workbook.Names[rangeName];
range.Address = "YourNewRange";
epplusWs.Workbook.Names.Remove(rangeName);
epplusWs.Workbook.Names.Add(rangeName, range);

answered Nov 02 '18 at 09:33

Gilles Boisson

11
1

Change Named range EPPlus

2 Answers2