Display graph with multiple columns in Excel using EPPlus library in ASP.Net MVC

nabilabolo
 
on Nov 03, 2021 04:18 AM
502 Views

Hi,

i have a question regarding download chart in excel file.

I'm using EPplus library.

Now my charts is okay already, but when i add more column in datable the graph is not working anymore.

Any idea how can i display more column in table without disturb the graph?

Controller

public ActionResult YiReport()
{
    return View();
}
 
[HttpPost]
public ActionResult YiReport(ReportYiVM m)
{
    ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
 
    ExcelPackage pck = new ExcelPackage();
    pck.Workbook.Worksheets.Add("YI Trend Chart");
    ExcelWorksheet ws = pck.Workbook.Worksheets[0];
   // ExcelWorksheet w1 = pck.Workbook.Worksheets[1];
 
    ws.PrinterSettings.Orientation = eOrientation.Landscape;
    ws.Cells.AutoFitColumns();
 
    DataSet ds = new DataSet();
    ds = o.YI(m);
    DataTable dt = ds.Tables[0];
    //da.Fill(dt);
    ws.Cells["A1:H2"].Merge = true;
    ws.Cells["A1:H2"].Style.Font.Size = 14.3f;
    ws.Cells["A1:H2"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
    ws.Cells["A1:H2"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
    ws.Cells["A1:H2"].Value = m.prodtype + "YI Trend Chart" + m.datefrom + " to " + m.dateto;
    //ws.Cells["A1:H2"].Value = " 920-555--25 YI Trend Chart 02/09/2021 to 03/09/2021";
    ws.Cells[3, 1].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Light11);
 
    var chart = ws.Drawings.AddChart("Employee by department", OfficeOpenXml.Drawing.Chart.eChartType.XYScatterLines);
 
    chart.Series.Add("B3:B9", "A3:A9");
    chart.SetPosition(2, 4, 3, 4);
    chart.SetSize(500, 300);
    chart.ShowHiddenData = true;
    chart.XAxis.Orientation = OfficeOpenXml.Drawing.Chart.eAxisOrientation.MinMax;
 
 
    chart.Title.Text = "Employee by department";
    chart.ShowDataLabelsOverMaximum = true;
 
    Response.Clear();
    Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
    Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
    Response.AddHeader("content-disposition", "attachment;  filename=MyReport.xlsx");
    Response.ContentType = "application/text";
    Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
    Response.BinaryWrite(pck.GetAsByteArray());
    Response.End();
    return View();
}

model

public class ReportYiVM
{
    public string datefrom { get; set; }
    public string dateto { get; set; }
    public string prodtype { get; set; }
    public string prodlinefrom { get; set; }
    public string prodlineto { get; set; }
    public string yaxis { get; set; }
}


///OraModel.cs
public DataSet YI(ReportYiVM m) //DATEFORMAT = 02/09/2021
{
     m.datefrom = "02/09/2021";
     m.dateto = "03/09/2021";
     m.prodtype = "920-555--25";
     m.prodlinefrom = "06";
     m.prodlineto = "07";
     DataSet ds = new DataSet();
     OpenConn("ORA_REPORT");
 
     //ds = ExecuteReaderDS("select r.segment1 , r.LOT_NUMBER, r.pack_date, r.YELLOWNESS_INDEX , r.QUANTITY, r.Grade , r.Process_Line from Report_table r where r.pack_date between to_date('" + m.datefrom + "', 'dd/mm/yyyy') and to_date('" + m.dateto + "', 'dd/mm/yyyy') and r.segment1 = '" + m.prodtype + "' and r.PROCESS_LINE between '" + m.prodlinefrom +"' AND '"+ m.prodlineto + "'");

     CloseConnection();

     return ds;
 }

View

@using (Html.BeginForm())
{
    <input type="submit" id="submit" value="Generate" />
}
Download FREE API for Word, Excel and PDF in ASP.Net: Download