c# - Defining a table rather than a range as a PivotTable 'cacheSource' -

i building tool automate creation of excel workbook contains table , associated pivottable. table structure on 1 sheet, data pulled database using tool @ later point. pivottable on second sheet using table previous sheet source.

i using epplus facilitate building tool running problems specifying cachesource. using following create range , pivottable:

 var datarange = dataworksheet.cells[dataworksheet.dimension.address.tostring()];   var pivottable = pivotworksheet.pivottables.add(pivotworksheet.cells["b3"], datarange, name); 

this sets cachesource to:

<x:cachesource type="worksheet" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <x:worksheetsource ref="a1:x2" sheet="dataworksheet" /> 

or within excel, data source set to:

dataworksheet!$a$1:$x$2 

this works fine if table size never changes, number of rows dynamic, finding when data refreshed, data read initial range specified.

what want programmatically set cachesource to:

<x:cachesource type="worksheet" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">   <x:worksheetsource name="dataworksheet" /> </x:cachesource> 

or in excel, set data source to:

dataworksheet 

i believe may possible accessing xml directly (any pointers on welcome) there way using epplus?

it can done not prettiest thing in world. can extract cache def xml , edit created epplus pivot table object wreak havoc save logic when call package.save() (or getasbytearray()) since parses xml on save generate final file. result of, said, epplus not capable of handling table source.

so, alternative save file epplus , manipulate content of xlsx renamed zip file using .net ziparchive. trick cannot manipulate files out of order in zip otherwise excel complain when opens file. , since cannot insert entry (only add end) have recreate zip. here extension method on ziparchive allow update cache source:

public static bool setcachesourcetotable(this ziparchive xlsxzip, fileinfo destinationfileinfo, string tablename, int cachesourcenumber = 1) {     var cachefound = false;     var cachename = string.format("pivotcachedefinition{0}.xml", cachesourcenumber);      using (var copiedzip = new ziparchive(destinationfileinfo.open(filemode.create, fileaccess.readwrite), ziparchivemode.update))     {         //go though each file in zip 1 one , copy on new file - entries need in order         xlsxzip.entries.tolist().foreach(entry =>         {             var newentry = copiedzip.createentry(entry.fullname);             var newstream = newentry.open();             var orgstream = entry.open();              //copy other files except cache def after             if (entry.name != cachename)             {                 orgstream.copyto(newstream);             }             else             {                 cachefound = true;                  //load xml document manipulate                 var xdoc = new xmldocument();                 xdoc.load(orgstream);                  //get reference worksheet xml proper namespace                 var nsm = new xmlnamespacemanager(xdoc.nametable);                 nsm.addnamespace("default", xdoc.documentelement.namespaceuri);                  //get source                 var worksheetsource = xdoc.selectsinglenode("/default:pivotcachedefinition/default:cachesource/default:worksheetsource", nsm);                  //clear attributes                 var att = worksheetsource.attributes["ref"];                 worksheetsource.attributes.remove(att);                  att = worksheetsource.attributes["sheet"];                 worksheetsource.attributes.remove(att);                  //create new attribute table                 att = xdoc.createattribute("name");                 att.value = tablename;                 worksheetsource.attributes.append(att);                  xdoc.save(newstream);             }              orgstream.close();             newstream.flush();             newstream.close();         });     }      return cachefound;  } 

and here how use it:

//throw in data var datatable = new datatable("tbldata"); datatable.columns.addrange(new[] {     new datacolumn("col1", typeof (int)), new datacolumn("col2", typeof (int)), new datacolumn("col3", typeof (object)) });  (var = 0; < 10; i++) {     var row = datatable.newrow();     row[0] = i; row[1] = i*10; row[2] = path.getrandomfilename();     datatable.rows.add(row); }  const string tablename = "pivottablesource"; using (var pck = new excelpackage()) {     var workbook = pck.workbook;      var source = workbook.worksheets.add("source");     source.cells.loadfromdatatable(datatable, true);     var datacells = source.cells["a1:c11"];      source.tables.add(datacells, tablename);      var pivotsheet = workbook.worksheets.add("pivot");     pivotsheet.pivottables.add(pivotsheet.cells["a1"], datacells, "pivottable1");      using (var orginalzip = new ziparchive(new memorystream(pck.getasbytearray()), ziparchivemode.read))     {         var fi = new fileinfo(@"c:\temp\pivot_from_table.xlsx");         if (fi.exists)             fi.delete();           var result = orginalzip.setcachesourcetotable(fi, tablename, 1);         console.write("cache source updated: ");         console.write(result);     } }