Setting data used by a line graph using EPPlus

Question

I am trying to add a simple line graph in excel automatically by using EPPlus. I have and know the range of cells that contain the data that I want to use. I want the graph to look like the following:

The Before and After columns go on for a lot more than what is shown.

I am using this code to create the graph and position it, but I am not sure how to set the data that the graph uses:

ExcelChart ec = ws.Drawings.AddChart("Line Time Graph", OfficeOpenXml.Drawing.Chart.eChartType.Line);
ec.Title.Text = "Benchmarks";
ec.SetPosition(_times.Count + 2, 0, 1, 0);

It might have something to do with ec.Series.Add but I am not sure how to properly use it. If you could point me in the right direction that would be great.

Answer 1

Hope this helps you:

ExcelChart ec = ws.Drawings.AddChart("Line Time Graph", OfficeOpenXml.Drawing.Chart.eChartType.Line);
ec.Title.Text = "Benchmarks";
ec.SetPosition(_times.Count + 2, 0, 1, 0);
ec.SetSize(800, 600);

var ran1 = sheet.Cells["A3:A10"];
var ran2 = sheet.Cells["G3:G10"]; // label range if there is. Otherwise, let select blank range then edit XML data later to remove 'c:cat' tags (bellow example)

var serie1 = ec.Series.Add(ran1, ran2);
// use serie1 variable to format and so on
// set serie1.HeaderAddress to A2 also

var ran1 = sheet.Cells["B3:B10"];
var serie2 = ec.Series.Add(ran1, ran2);
// use serie2 variable to format and so on

Another full example I've just tested:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OfficeOpenXml;
using System.IO;
using OfficeOpenXml.Drawing.Chart;

namespace TestExcelEPPluss
{
    class Program
    {
        static void Main(string[] args)
        {
            ExcelPackage package = new ExcelPackage();
            var sheet = package.Workbook.Worksheets.Add("TestingGraph");

            Random r = new Random();
            var cell = sheet.Cells["A1"];
            cell.Value = "Before";

            cell = sheet.Cells["B1"];
            cell.Value = "After";

            for (int i = 0; i < 100; i++)
            {
                cell = sheet.Cells[i + 2, 1];
                cell.Value = r.Next(300, 500);
                cell = sheet.Cells[i + 2, 2];
                cell.Value = r.Next(300, 500);
            }

            ExcelChart ec = (ExcelLineChart)sheet.Drawings.AddChart("chart_1", eChartType.Line);
            ec.SetPosition(1, 0, 3, 0);
            ec.SetSize(800, 300);
            //ec.Legend.Add();

            var ran1 = sheet.Cells["A2:A101"];
            var ran2 = sheet.Cells["0:0"];

            var serie1 = (ExcelLineChartSerie)ec.Series.Add(ran1, ran2);
            serie1.Header = sheet.Cells["A1"].Value.ToString();

            ran1 = sheet.Cells["B2:B101"];
            var serie2 = ec.Series.Add(ran1, ran2);
            serie2.Header = sheet.Cells["B1"].Value.ToString();

            var xml = ec.ChartXml;
            var lst = xml.GetElementsByTagName("c:lineChart");
            foreach (System.Xml.XmlNode item in lst[0].ChildNodes)
            {
                if (item.Name.Equals("ser"))
                {
                    foreach (System.Xml.XmlNode subitem in item.ChildNodes)
                    {
                        if (subitem.Name.Equals("c:cat"))
                        {
                            item.RemoveChild(subitem);
                            break;
                        }
                    }
                }
            }

            string path = @"C:\test1.xlsx";
            File.WriteAllBytes(path, package.GetAsByteArray());
            package.Dispose();

            Console.WriteLine("Done - Path: {0}", path);
            Console.ReadLine();
        }
    }
}

c:cat is the category for ser series, let remove c:cat tags from charts xml, then your chart will use 1,2,3,4,5,... as default for the category (x axis here).