I wish to create a PivotTable that relies on a Data Model that takes from three Tables already defined in my workbook. Unfortunately I cannot find how to do this.
What I've done so far:
The code I have written to create a sample Excel file with three tables and then create a simple one-table pivot is at the bottom of this post. As you can see from the hardcoded data in that code, these three tables define two people, two pets, and the ownership of those pets. One pet is owned by both people, the other pet is owned by only one person. The pivot SimplePivotTbl
is then created on the single table PeoplePets
.
Next I opened test.xlsx
in Excel 2016 and created the obvious multi-table PivotTable ComplexPivotTbl
that counts the number of owners for each pet. The resulting Excel file can be downloaded here.
What I need: the code to do step 2. In other words, the code to do what I did "by hand" with Excel to make that PivotTable.
Code to create a new workbook with the three tables:
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Table.PivotTable;
namespace EPPlusTestApp
{
class Program
{
static void Main(string[] args)
{
const string outputFile = @"C:\temp\test.xlsx";
if (System.IO.File.Exists(outputFile))
{
System.IO.File.Delete(outputFile);
}
var xls = new ExcelPackage(new FileInfo(outputFile));
ExcelWorksheet ws = xls.Workbook.Worksheets.Add("Tables");
// first table
ws.Cells[1, 1].Value = "PersonId";
ws.Cells[1, 2].Value = "PersonName";
ws.Cells[1, 3].Value = "Age";
ws.Cells[2, 1].Value = 1;
ws.Cells[2, 2].Value = "John Doe";
ws.Cells[2, 3].Value = 35;
ws.Cells[3, 1].Value = 2;
ws.Cells[3, 2].Value = "Jane Smith";
ws.Cells[3, 3].Value = 40;
ws.Tables.Add(ws.Cells[1, 1, 3, 3], "People");
// second table
ws.Cells[1, 5].Value = "PetId";
ws.Cells[1, 6].Value = "PetName";
ws.Cells[1, 7].Value = "Species";
ws.Cells[2, 5].Value = 10;
ws.Cells[2, 6].Value = "Spot";
ws.Cells[2, 7].Value = "Dog";
ws.Cells[3, 5].Value = 11;
ws.Cells[3, 6].Value = "Fluffy";
ws.Cells[3, 7].Value = "Cat";
ws.Tables.Add(ws.Cells[1, 5, 3, 7], "Pets");
// third table
ws.Cells[1, 10].Value = "PersonId";
ws.Cells[1, 11].Value = "PetId";
ws.Cells[2, 10].Value = 1;
ws.Cells[2, 11].Value = 10;
ws.Cells[3, 10].Value = 2;
ws.Cells[3, 11].Value = 10;
ws.Cells[4, 10].Value = 1;
ws.Cells[4, 11].Value = 11;
ws.Tables.Add(ws.Cells[1, 10, 4, 11], "PeoplePets");
ws.Cells.AutoFitColumns();
// simple pivottable
var ptws = xls.Workbook.Worksheets.Add("SimplePivotTable");
var pt = ptws.PivotTables.Add(ptws.Cells[1, 1], (ExcelRangeBase)ws.Tables["PeoplePets"].Address, "SimplePivotTbl");
var fld = pt.RowFields.Add(pt.Fields["PersonId"]);
fld.Sort = eSortType.Ascending;
fld = pt.ColumnFields.Add(pt.Fields["PetId"]);
fld.Sort = eSortType.Ascending;
var dfield = pt.DataFields.Add(pt.Fields["PetId"]);
dfield.Function = DataFieldFunctions.Count;
ptws.Cells.AutoFitColumns();
xls.Save();
}
}
}
I posted this as an issue on the EPPlus github, and the response was:
No, there is not support for data models at this point. I'll add this as an enhancement for future versions.