Skip to main content

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.

Popular posts from this blog

Uploading large file in chunks in Asp.net Mvc c# from Javascript ajax

Often we have a requirement to upload files in Asp.net, Mvc c# application but when it comes to uploading larger file, we always think how to do it as uploading large file in one go have many challenges like UI responsiveness, If network fluctuate for a moment in between then uploading task get breaks and user have to upload it again etc.

regex - check if a string contains only alphabets c#

How to validate that input string contains only alphabets, validating that textbox contains only alphabets (letter), so here is some of the ways for doing such task. char have a property named isLetter which is for checking if character is a letter or not, or you can check by the regular expression  or you can validate your textbox through regular expression validator in asp.net. Following code demonstrating the various ways of implementation.

Regular expression for alphanumeric with space in asp.net c#

How to validate that string contains only alphanumeric value with some spacial character and with whitespace and how to validate that user can only input alphanumeric with given special character or space in a textbox (like name fields or remarks fields). In remarks fields we don't want that user can enter anything, user can only able to enter alphanumeric with white space and some spacial character like -,. etc if you allow. Some of regular expression given below for validating alphanumeric value only, alphanumeric with whitspace only and alphanumeric with whitespace and some special characters.

How to validate dropdownlist in JavaScript

In this article you will see how to put validation in dropdownlist by javascript, suppose first item value of dropdownlist is 0 and text is "-Select-" just like given below and we have to validate that at least one item is selected excluding default i.e "-Select-".