Exporting Pie Chart into excel file from Asp.net csharp


This Asp.net Article shows how to make a 3D Pie chart from a datatable into an Excel file , save this file and open the excel application through C#.
It works Asp.net with .Net Development Server  while it will not not work in Asp.net application
hosted on IIS cause IIS does not allowed to start a process on server at all.


using Excel=Microsoft.Office.Interop.Excel;

private System.Data.DataTable GetGraphData()
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[2] { new DataColumn("ProjectStatus"), new DataColumn("per") });

        DataRow dr1 = dt.NewRow();
        dr1[0]="Compleet";
        dr1[1] = 20;
        dt.Rows.Add(dr1);

        DataRow dr2 = dt.NewRow();
        dr2[0] = "Pending";
        dr2[1] = 20;
        dt.Rows.Add(dr2);

        DataRow dr3 = dt.NewRow();
        dr3[0] = "UnCompleet";
        dr3[1] = 20;
        dt.Rows.Add(dr3);

        return dt;

    }
    protected void btnExp_Click(object sender, EventArgs e)
    {
        DataTable dt = GetGraphData();

        Excel.Application xla = new Excel.Application();
        xla.Visible = true;
        Excel.Workbook wb = xla.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
        Excel.Worksheet ws = (Excel.Worksheet)wb.ActiveSheet;
        //********************** Now create the chart. *****************************
        Excel.ChartObjects chartObjs = (Excel.ChartObjects)ws.ChartObjects(Type.Missing);
        Excel.ChartObject chartObj = chartObjs.Add(250, 60, 300, 300);
        Excel.Chart xlChart = chartObj.Chart;

        int nRows = 2;
        int nColumns = dt.Rows.Count;
        string upperLeftCell = "B2";
        int endRowNumber = System.Int32.Parse(upperLeftCell.Substring(1))
            + nRows - 1;
        char endColumnLetter = System.Convert.ToChar(
            Convert.ToInt32(upperLeftCell[0]) + nColumns - 1);
        string upperRightCell = System.String.Format("{0}{1}",
            endColumnLetter, System.Int32.Parse(upperLeftCell.Substring(1)));
        string lowerRightCell = System.String.Format("{0}{1}",
            endColumnLetter, endRowNumber);

        Excel.Range rg = ws.get_Range(upperLeftCell, lowerRightCell);
        for (int i = 1; i <= dt.Rows.Count; i++)
        {
            rg[1, i] = dt.Rows[i - 1][0].ToString();          //For Adding Header Text
            rg[2, i] = int.Parse(dt.Rows[i - 1][1].ToString());  //For Adding Datarow Value
        }


        Excel.Range chartRange = ws.get_Range(upperLeftCell, lowerRightCell);
        xlChart.SetSourceData(chartRange, Type.Missing);
        xlChart.ChartType = Excel.XlChartType.xl3DPie;

        // *******************Customize axes: ***********************
        Excel.Axis xAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory,
             Excel.XlAxisGroup.xlPrimary);
        //xAxis.HasTitle = true;
        // xAxis.AxisTitle.Text = "X Axis";

        Excel.Axis yAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlSeriesAxis,
             Excel.XlAxisGroup.xlPrimary);
        //yAxis.HasTitle = true;
        //yAxis.AxisTitle.Text = "Y Axis";

        Excel.Axis zAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue,
             Excel.XlAxisGroup.xlPrimary);
        //zAxis.HasTitle = true;
        //zAxis.AxisTitle.Text = "Z Axis";
     
        // *********************Add title: *******************************
        xlChart.HasTitle = true;
        xlChart.ChartTitle.Text = "Project Status Graph";
  
        // *****************Set legend:***************************
        xlChart.HasLegend = true;
        wb.SaveCopyAs(Server.MapPath(@"File/Graph.xls"));

        // ****************For Quiting The Excel Aplication ***********************
        //if (xla != null)
        //{
        //    xla.DisplayAlerts = false;
        //    wb.Close();
        //    wb = null;
        //    xla.Quit();
        //    xla = null;
        //}
}


First you have to add reference of the dll.
  1. Right click the project name
  2. Add reference -> .net tab and then choose Microsoft.Office.Interop.Excel dll namespace then press ok button.
  3. In .aspx code behind file you have to import the Microsoft.Office.Interop.Excel namespace and that’s it.

Recent Article