EPPlus create chart from pivot

Question

I'm using EPPlus in .Net and have created a pivot table with a row field and one summary data field.

Does anyone know how to create charts from a pivot table? And I don't know pivot table's range.

Here is my code:

    static void DT2XL(DataTable dt, ExcelPackage excel)
    {
        var wsData = excel.Workbook.Worksheets["Eco-Data"];
        var wsPivot = excel.Workbook.Worksheets["Eco-Pivot"];

        if (wsData == null) wsData = excel.Workbook.Worksheets.Add("Eco-Data");
        if (wsPivot == null) wsPivot = excel.Workbook.Worksheets.Add("Eco-Pivot");

        wsData.Cells["A1"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Medium6);

        foreach (DataColumn col in dt.Columns)
        {
            if (col.DataType == typeof(System.DateTime))
            {
                var colNumber = col.Ordinal + 1;
                var range = wsData.Cells[2, colNumber, dt.Rows.Count + 1, colNumber];
                range.Style.Numberformat.Format = "dd.MM.yyyy";
            }
        }

        var dataRange = wsData.Cells[wsData.Dimension.Address.ToString()];
        dataRange.AutoFitColumns();
        var pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], dataRange, "EcoPivotTable");
        pivotTable.MultipleFieldFilters = true;
        pivotTable.RowGrandTotals = true;
        pivotTable.ColumGrandTotals = true;
        pivotTable.Compact = true;
        pivotTable.CompactData = true;
        pivotTable.GridDropZones = false;
        pivotTable.Outline = false;
        pivotTable.OutlineData = false;
        pivotTable.ShowError = true;
        pivotTable.ErrorCaption = "[error]";
        pivotTable.ShowHeaders = true;
        pivotTable.UseAutoFormatting = true;
        pivotTable.ApplyWidthHeightFormats = true;
        pivotTable.ShowDrill = true;
        pivotTable.FirstDataCol = 3;
        pivotTable.RowHeaderCaption = "Үзүүлэлтүүд";
        pivotTable.GrandTotalCaption = "Нийт";

        foreach (DataColumn c in dt.Columns)
        {
            var field = pivotTable.Fields[c.ColumnName];
            if (c.ColumnName.ToLower().StartsWith("column") ||
                c.ColumnName.ToLower().StartsWith("row"))
                pivotTable.RowFields.Add(field);
            else if (c.ColumnName.ToLower().StartsWith("data"))
                pivotTable.DataFields.Add(field);
        }

        //I want to create a chart from 'pivotTable'
        //...
        //...
        //...
        //...
        //...
    }

Thank you!

Did you got the solution?? Please let me know, you also can mail me on formosus19@hotmail.com — Abhinav, Sep 18 '15 at 09:32
`I don't know pivot table's range.` is the critical bit, to me. I'm trying to figure out how to determine this myself.... — David T. Macknet, May 18 '16 at 16:16
As far as I can tell, the pivot chart is not instantiated in memory, only the instructions to create the pivot chart are placed in the database and passed to excel. When I graph the chart, I separately figure out the rows and columns of the pivot chart that I am creating, and I use those counts to pass to the chart object. — Glenn Gordon, Jun 03 '16 at 15:42

score 2 · Answer 1 · answered Oct 21 '14 at 11:14

Here is to code to create a PivotChart:

    var reportFilterNames = new List<string>(); //TODO: Fill with column names from DataTable
    var valueNames = new List<string>(); //TODO: Fill with column names from DataTable

    ExcelWorksheet dataWorksheet = package.Workbook.Worksheets[report.ReportType];
    ExcelRange dataRange = dataWorksheet.Cells["A1:" + dataWorksheet.Dimension.End.Address];

    ExcelWorksheet pivotWorksheet = package.Workbook.Worksheets.Add(dt.TableName);
    ExcelRange pivotDataRange = pivotWorksheet.Cells["A" + (2 + reportFilterNames.Count)];
    ExcelPivotTable pivotTable = pivotWorksheet.PivotTables.Add(pivotDataRange, dataRange, "Stats");
    pivotTable.DataOnRows = false;

    pivotTable.RowFields.Add(pivotTable.Fields["DATE"]);

    foreach (string reportFilterName in reportFilterNames)
        pivotTable.PageFields.Add(pivotTable.Fields[reportFilterName]);

    foreach (string valueName in valueNames)
        pivotTable.DataFields.Add(pivotTable.Fields[valueName]);

    var chart = pivotWorksheet.Drawings.AddChart("PivotChart", eChartType.Line, pivotTable);
    chart.SetPosition(1, 0, 4, 0);
    chart.SetSize(1200, 800);

EPPlus create chart from pivot

1 Answers1