Is it possible to create dynamic charts in epplus?

Question

I am wondering if it is possible to configure epplus in such a way that when the excel file is opened the table data can be clicked on and a graph will be displayed based on the row of the table clicked. (I realize this is super easy to do in excel I would just rather have everything taken care of before it gets to certain people)

Currently I just have one data table and a graph for each row but it would be better if there was just one graph that changed based on what row is clicked in excel. I also tried a pivot table but that doesn't help me with the dynamic chart.

Answer 1

For anyone trying to figure this out as well. I ended up using a data validation drop down list and making a dynamic table row (outside the base table) that the dynamic chart is based on. The dynamic table row changes based on the value of the data validation dropdown list (you kinda need a feel for excel to be able to do this which I didn't have):

class Program
{
    static void Main(string[] args)
    {
        // Creating an instance 
        // of ExcelPackage 
        ExcelPackage excel = new ExcelPackage();

        // name of the sheet 
        var workSheet = excel.Workbook.Worksheets.Add("testSheet");
        //init table
        var randTable = new DataTable();
        randTable.TableName = "randTable";
        //init columns
        var countColumn = new DataColumn()
        {
            DataType = typeof(int),
            ColumnName = "Count",
            ReadOnly = true,
            Unique = true
        };
        var randomColumn0 = new DataColumn()
        {
            DataType = typeof(int),
            ColumnName = "Random-0",
            ReadOnly = true,
            Unique = false
        };
        var randomColumn1 = new DataColumn()
        {
            DataType = typeof(int),
            ColumnName = "Random-1",
            ReadOnly = true,
            Unique = false
        };
        //add columns to table
        randTable.Columns.AddRange(new DataColumn[] { countColumn, randomColumn0, randomColumn1 });


        //init data validation
        ExcelRange dropdownRange = workSheet.Cells[12, 1, 12, 3];
        var dropdownValidation = workSheet.DataValidations.AddListValidation(dropdownRange.Address);
        workSheet.Names.Add("count", dropdownRange);
        //style data validation
        dropdownRange.Merge = true;
        workSheet.Cells[dropdownRange.Address].Style.Fill.PatternType = ExcelFillStyle.Solid;
        workSheet.Cells[dropdownRange.Address].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
        workSheet.Cells[dropdownRange.Address].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        var rand = new Random();
        for (var i = 0; i < 10; i++)
        {
            //add table first column values to validation list
            dropdownValidation.Formula.Values.Add(i.ToString());
            var row = randTable.NewRow();
            row[countColumn] = i;
            row[randomColumn0] = rand.Next(0, 100);
            row[randomColumn1] = rand.Next(0, 100);
            randTable.Rows.Add(row);
        }

        //make the tableIndexer cell. This cell will be used to get the 
        //table indices for the selected table row cells
        ExcelRange randTableIndexer = workSheet.Cells[12, 4, 12, 4];
        randTableIndexer.Formula = "MATCH(INDEX(count,1,1), randTable[Count], 0)";
        workSheet.Cells[randTableIndexer.Address].Style.Fill.PatternType = ExcelFillStyle.Solid;
        workSheet.Cells[randTableIndexer.Address].Style.Fill.BackgroundColor.SetColor(Color.LightGreen);
        workSheet.Cells[randTableIndexer.Address].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        workSheet.Names.Add("tableIndex", randTableIndexer);

        //make the cells based off the table at row(randTableIndexer.value)
        ExcelRange graphCells = workSheet.Cells[13, 1, 13, 3];
        graphCells.CreateArrayFormula("INDEX(randTable[], tableIndex, 0)"); //need [] on table names in epplus formulas
        workSheet.Cells[graphCells.Address].Style.Fill.PatternType = ExcelFillStyle.Solid;
        workSheet.Cells[graphCells.Address].Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
        workSheet.Cells[graphCells.Address].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        graphCells.Calculate();

        //add table to workSheet
        workSheet.Cells[1, 1].LoadFromDataTable(randTable, true, OfficeOpenXml.Table.TableStyles.Medium15);
        workSheet.Cells.AutoFitColumns();


        //add dynamic chart
        var chart = workSheet.Drawings.AddChart("rands", eChartType.Pie) as ExcelPieChart;
        chart.Title.Text = "rands";
        chart.Series.Add(graphCells.Address, ExcelRange.GetAddress(1, 1, 3, 1));
        chart.Legend.Position = eLegendPosition.Bottom;
        chart.SetSize(500, 400);
        chart.SetPosition(60, 500);


        WriteToFile(excel);
    }

    public static void WriteToFile(ExcelPackage package)
    {
        // file name with .xlsx extension  
        string p_strPath = "C:\\your\\file\\path";

        if (File.Exists(p_strPath))
            File.Delete(p_strPath);

        // Create excel file on physical disk  
        FileStream objFileStrm = File.Create(p_strPath);
        objFileStrm.Close();

        // Write content to excel file  
        File.WriteAllBytes(p_strPath, package.GetAsByteArray());
    } 
}