Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I've produced a similar solution from Tim's Answer. First, I defined a simple interface that I use as part of my export methods:</p> <pre><code>public interface IPivotTableCreator { void CreatePivotTable( OfficeOpenXml.ExcelPackage pkg, // reference to the destination book string tableName, // "tab" name used to generate names for related items string pivotRangeName); // Named range in the Workbook refers to data } </code></pre> <p>Then I implemented a simple class that hold the variable values and procedural code to do the work:</p> <pre><code>public class SimplePivotTable : IPivotTableCreator { List&lt;string&gt; _GroupByColumns; List&lt;string&gt; _SummaryColumns; /// &lt;summary&gt; /// Constructor /// &lt;/summary&gt; public SimplePivotTable(string[] groupByColumns, string[] summaryColumns) { _GroupByColumns = new List&lt;string&gt;(groupByColumns); _SummaryColumns = new List&lt;string&gt;(summaryColumns); } /// &lt;summary&gt; /// Call-back handler that builds simple PivatTable in Excel /// http://stackoverflow.com/questions/11650080/epplus-pivot-tables-charts /// &lt;/summary&gt; public void CreatePivotTable(OfficeOpenXml.ExcelPackage pkg, string tableName, string pivotRangeName) { string pageName = "Pivot-" + tableName.Replace(" ", ""); var wsPivot = pkg.Workbook.Worksheets.Add(pageName); pkg.Workbook.Worksheets.MoveBefore(PageName, tableName); var dataRange = pkg.Workbook./*Worksheets[tableName].*/Names[pivotRangeName]; var pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells["C3"], dataRange, "Pivot_" + tableName.Replace(" ", "")); pivotTable.ShowHeaders = true; pivotTable.UseAutoFormatting = true; pivotTable.ApplyWidthHeightFormats = true; pivotTable.ShowDrill = true; pivotTable.FirstHeaderRow = 1; // first row has headers pivotTable.FirstDataCol = 1; // first col of data pivotTable.FirstDataRow = 2; // first row of data foreach (string row in _GroupByColumns) { var field = pivotTable.Fields[row]; pivotTable.RowFields.Add(field); field.Sort = eSortType.Ascending; } foreach (string column in _SummaryColumns) { var field = pivotTable.Fields[column]; ExcelPivotTableDataField result = pivotTable.DataFields.Add(field); } pivotTable.DataOnRows = false; } } </code></pre> <p>Then I create an instance of my <code>SimplePivotTable</code> creator class:</p> <pre><code>IPivotTableCreator ptCreator = new SimplePivotTable( new string[] { "OrganizationTitle", "GroupingTitle", "DetailTitle" }, /* collapsible rows */ new string[] { "Baseline", "Increase", "Decrease", "NetChange", "CurrentCount"}); /* summary columns */ </code></pre> <p>I have a third class that currently exposes about six different methods to take one-or-more data sets (usually List objects) and turn each of the data sets into a worksheet of data with a named range for the data. Now, I'm adapting those export methods to allow me to generate Pivot Tables for any/all of those export methods. They all do something like this:</p> <pre><code>OfficeOpenXml.ExcelPackage pkg = new ExcelPackage(); ExportCollectionToExcel(pkg, tableName, dataset); // Create worksheet filled with data // Creates a NamedRange of data ptCreator.CreatePivotTable(pkg, tableName, GetPivotRangeName(tableName)); </code></pre> <p>By using an interface, I leave open more opportunities (I think) to generate, for example, a different pivot table for multiple sheets. My basic <code>SimplePivotTable</code> class just used for a single table with some specific assumptions, but it wouldn't be hard to put the configuration data into a dictionary keyed to the Table names.</p> <p>Hope that helps someone.</p>
    singulars
    1. This table or related slice is empty.
    1. POEPPlus pivot tables/charts
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USTed H.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhen I use your (slightly modified) code, editing a Σ field crashes Excel ? Please see my post here : http://epplus.codeplex.com/workitem/14798
      singulars
      1. PO
      1. USStef Heyenrath
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload