Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create excel graph with epplus

here is what I have. I have an excel sheet with two column. Column 1 had descriptions for legend, like Category1 Category 2 etc. Column 2 has numbers with total count like 6,4,18 etc.

Category Count
Category1 6
Category2 4
Category3 18

I need to display a graph with the count values and display the category names for each line. I tried different values but I cannot figure it out. Here is my current code

        ExcelChart ec = (ExcelLineChart)chartSheet.Drawings.AddChart("chart_1",      
                        eChartType.Line);
            ec.SetPosition(1, 0, 3, 0);
            ec.SetSize(800, 300);
            var ran1 = chartSheet.Cells["A4:A6"];
            var ran2 = workSheet.Cells["0:0"];

            var serie1 = (ExcelChartSerie)ec.Series.Add(ran1, ran2);
            serie1.Header = chartSheet.Cells["A3"].Value.ToString();

            ran1 = chartSheet.Cells["B4:B6"];
            var serie2 = ec.Series.Add(ran1, ran2);
            serie2.Header = chartSheet.Cells["B3"].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;
                        }
                    }
                }
            }

This gives me a line with counts, but does not display the category names like I wanted.

Thanks in advance for any response.

like image 343
Steve Ed Avatar asked Apr 30 '15 19:04

Steve Ed


People also ask

Does EPPlus work with XLS?

EPPlus does not work with the XLS format. Only XLSX. You'll need to find a new library.

Does EPPlus require Excel?

No, it does not require Excel to be installed on the server, as you can read in the docs: EPPlus is a . NET library that reads and writes Excel files using the Office Open XML format (xlsx).


1 Answers

Nevermind, I fiddled around a little bit and found the answer

Here is the working code

ExcelChart chart = chartSheet.Drawings.AddChart("FindingsChart",
OfficeOpenXml.Drawing.Chart.eChartType.ColumnClustered);
chart.Title.Text = "Category Chart";
chart.SetPosition(1, 0, 3, 0);
chart.SetSize(800, 300);
var ser1 = (ExcelChartSerie)(chart.Series.Add(workSheet.Cells["B4:B6"],
workSheet.Cells["A4:A6"]));
ser1.Header = "Category";
like image 113
Steve Ed Avatar answered Sep 27 '22 23:09

Steve Ed