This project has moved. For the latest updates, please go here.

Excel Chart Series

Apr 13, 2012 at 4:47 PM
Edited Apr 18, 2012 at 5:17 AM

Hi Everyone, 

I'm trying to create excel file with chart like this

http://imageshack.us/photo/my-images/833/chartsample.png/

Below is my sample code, any one can help me on this???

 

string fileName = "graph_" + DateTime.Now.ToString("yyyyMMdd") + DateTime.Now.ToString("HHmmss") + ".xlsx";
            FileInfo Newfile = new FileInfo(Server.MapPath("Downloads/" + fileName));

            // Create Excel Package
            ExcelPackage package = new ExcelPackage(Newfile);
            // Add a new sheet 
            ExcelWorksheet Worksheet = package.Workbook.Worksheets.Add("Graph");

            // Worksheet Data

            Worksheet.Cells["B1"].Value = "W1";
            Worksheet.Cells["D1"].Value = "W2";
            Worksheet.Cells["F1"].Value = "W3";
            Worksheet.Cells["H1"].Value = "W4";
            Worksheet.Cells["J1"].Value = "W5";

            Worksheet.Cells["B2"].Value = "C";
            Worksheet.Cells["C2"].Value = "R";
            Worksheet.Cells["D2"].Value = "C";
            Worksheet.Cells["E2"].Value = "R";
            Worksheet.Cells["F2"].Value = "C";
            Worksheet.Cells["G2"].Value = "R";
            Worksheet.Cells["H2"].Value = "C";
            Worksheet.Cells["I2"].Value = "R";
            Worksheet.Cells["J2"].Value = "C";
            Worksheet.Cells["K2"].Value = "R";

            Worksheet.Cells["A3"].Value = "A";
            Worksheet.Cells["A4"].Value = "B";
            Worksheet.Cells["A5"].Value = "C";
            Worksheet.Cells["A6"].Value = "D";

            Worksheet.Cells["B3"].Value = 10;
            Worksheet.Cells["B4"].Value = 20;
            Worksheet.Cells["B5"].Value = 30;
            Worksheet.Cells["B6"].Value = 40;

            Worksheet.Cells["C3"].Value = 40;
            Worksheet.Cells["C4"].Value = 30;
            Worksheet.Cells["C5"].Value = 20;
            Worksheet.Cells["C6"].Value = 10;

            Worksheet.Cells["D3"].Value = 10;
            Worksheet.Cells["D4"].Value = 20;
            Worksheet.Cells["D5"].Value = 30;
            Worksheet.Cells["D6"].Value = 40;

            Worksheet.Cells["E3"].Value = 10;
            Worksheet.Cells["E4"].Value = 20;
            Worksheet.Cells["E5"].Value = 30;
            Worksheet.Cells["E6"].Value = 40;

            Worksheet.Cells["F3"].Value = 40;
            Worksheet.Cells["F4"].Value = 30;
            Worksheet.Cells["F5"].Value = 20;
            Worksheet.Cells["F6"].Value = 10;

            Worksheet.Cells["G3"].Value = 10;
            Worksheet.Cells["G4"].Value = 20;
            Worksheet.Cells["G5"].Value = 30;
            Worksheet.Cells["G6"].Value = 40;

            Worksheet.Cells["H3"].Value = 40;
            Worksheet.Cells["H4"].Value = 30;
            Worksheet.Cells["H5"].Value = 20;
            Worksheet.Cells["H6"].Value = 10;

            Worksheet.Cells["I3"].Value = 10;
            Worksheet.Cells["I4"].Value = 20;
            Worksheet.Cells["I5"].Value = 30;
            Worksheet.Cells["I6"].Value = 40;

            Worksheet.Cells["J3"].Value = 40;
            Worksheet.Cells["J4"].Value = 30;
            Worksheet.Cells["J5"].Value = 20;
            Worksheet.Cells["J6"].Value = 10;

            Worksheet.Cells["K3"].Value = 10;
            Worksheet.Cells["K4"].Value = 20;
            Worksheet.Cells["K5"].Value = 30;
            Worksheet.Cells["K6"].Value = 40;

            // Stacked Column            
            ExcelChart chart = Worksheet.Drawings.AddChart("ColumnStackedChart", eChartType.ColumnStacked);
            chart.Legend.Position = eLegendPosition.Right;
            chart.Legend.Add();
            chart.Title.Text = "Graph by Week";
            chart.SetPosition(120, 50);
            chart.SetSize(800, 300);
            chart.Series.Add(Worksheet.Cells["B3:B6"], Worksheet.Cells["B1:B2"]);
            chart.Series.Add(Worksheet.Cells["C3:C6"], Worksheet.Cells["C1:C2"]);
            //chart.Series.Add(Worksheet.Cells["D3:D6"], Worksheet.Cells["D1:D2"]);
            //chart.Series.Add(Worksheet.Cells["E3:E6"], Worksheet.Cells["E1:E2"]);
            //chart.Series.Add(Worksheet.Cells["F1:F6"], Worksheet.Cells["B1:K2"]);
            //chart.Series.Add(Worksheet.Cells["G1:G6"], Worksheet.Cells["B1:K2"]);
            //chart.Series.Add(Worksheet.Cells["H1:H6"], Worksheet.Cells["B1:K2"]);
            //chart.Series.Add(Worksheet.Cells["I1:I6"], Worksheet.Cells["B1:K2"]);
            //chart.Series.Add(Worksheet.Cells["J1:J6"], Worksheet.Cells["B1:K2"]);
            //chart.Series.Add(Worksheet.Cells["K1:K6"], Worksheet.Cells["B1:K2"]);
            //chart.Style = eChartStyle.Style2;

            //Save
            package.SaveAs(Response.OutputStream);
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;  filename=" + fileName);
            Response.End();
Apr 17, 2012 at 8:58 PM

Might want to remove those code names from the source you're sharing :)

Apr 18, 2012 at 5:18 AM

Done :), chart generated too.