c# - OpenXML / EPPlus - Create PivotCache in .Net -
i'm hoping me, @ least, answer 1 of 2 questions asked here, looking how create pivotcache in epplus / openxml , can't find online / in documentation shows how it.
so, suposing have 1 excel sheet, wksrawdata
created in epplus , want create second sheet pivot table based upon pivot cache of wksrawdata.cells(wksrawdata.dimension.address)
- in hopes delete wksrawdata still keep pivot table. how that?
so far, code creating pivot table in second worksheet is:
dim wksrawdata excelworksheet = wbk.worksheets("raw data") dim wkspvttbl excelworksheet = wbk.worksheets("pivottbl") ' insert pivot table sheet dim datarange excelrange = wksrawdata.cells(wksrawdata.dimension.address) dim pvttable officeopenxml.table.pivottable.excelpivottable = wkspvttbl.pivottables.add(wkspvttbl.cells("b4"), datarange, "mypivottable") pvttable.compact = true pvttable.compactdata = true pvttable.outline = true pvttable.outlinedata = true pvttable.showheaders = true pvttable.useautoformatting = true pvttable.applywidthheightformats = true pvttable.showdrill = true pvttable.rowheadercaption = "caption" ' set top field dim r1 officeopenxml.table.pivottable.excelpivottablefield = pvttable.fields("firstfield") r1.sort = officeopenxml.table.pivottable.esorttype.ascending pvttable.rowfields.add(r1) ' set second field dim r2 officeopenxml.table.pivottable.excelpivottablefield = pvttable.fields("secondfield") r2.sort = officeopenxml.table.pivottable.esorttype.ascending pvttable.rowfields.add(r2) r2.showall = false ' set datafield dim df1 officeopenxml.table.pivottable.excelpivottablefield = pvttable.fields("datafield") df1.subtotalfunctions = officeopenxml.table.pivottable.esubtotalfunctions.sum pvttable.datafields.add(df1)
please, , on or other question appreciated - whether in c# or vb, epplus or openxml - need working!!!
thanks!
i belive want add data sheet. post in other thread shows full code epplus pivot table - collapse entire field
var pt = wspivot1.pivottables.add(wspivot1.cells["a1"], ws.cells["k1:n11"], "pivottable1");
calling "pivotables.add()" see tool tip below.
// summary: // create pivottable on supplied range // // parameters: // range: // range address including header , total row // // source: // source data range address // // name: // name of table. must unique // // returns: // pivottable object
Comments
Post a Comment