Develop Chart in ASP.NET With Export to Excel and PDF For MS Excel as Well as Open Office

Recently while working on a project in ASP.NET 4.0, I came across a scenario where I had to develop a chart that can be exported to PDF and Excel. Apart from this it has to be viewable in both Microsoft Office and Open Office.

Let us try to develop this functionality.

Create a new empty web application and add a new webpage Chart.aspx.

ASP.NET 4.0 comes with a new chart control as in the following:



Go to the toolbox and drag a Chart control onto the page.

Now specify the datasource. Here I am using sqldatasource and a view of Northwind database to demonstrate the issue.



Now build the application and run Chart.aspx page.



As seen above, the chart has been generated successfully. But, now we want to export the chart in PDF or Excel format.

First, let us see how to export the chart to Excel.

Let us add one button to the page. Add the following button click event:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.IO;

 

namespace ReportView

{

    public partial class Chart : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

 

        }

 

        protected void btnExportToExcel_Click(object sender, EventArgs e)

        {

            string attachment = "attachment; filename=Chart.xls";

            Response.ClearContent();

            Response.AddHeader("content-disposition", attachment);

            Response.ContentType = "application/vnd.ms-excel";

            StringWriter sw = new StringWriter();

            HtmlTextWriter htw = new HtmlTextWriter(sw);

            chart.RenderControl(htw);

            Response.Write(sw.ToString());

            Response.End();

        }

    }

}

Now run the page and click on the Export to Excel button. Excel will be generated.

Save the Excel file and open it in Microsoft Office. You will see that it has added one image tag of the chart but actually the image is not rendered.

There is one workaround for this. Add the following event for the button:
 

    protected void btnExportToExcel_Click(object sender, EventArgs e)

    {

        string tmpChartName = "ChartImage.jpg";

        string imgPath = HttpContext.Current.Request.PhysicalApplicationPath + tmpChartName;

        chart.SaveImage(imgPath);

 

        string imgPath2 = Request.Url.GetLeftPart(UriPartial.Authority) + VirtualPathUtility.ToAbsolute("~/" + tmpChartName);

        Response.Clear();

        Response.ContentType = "application/vnd.ms-excel";

        Response.AddHeader("Content-Disposition", "attachment; filename=Chart.xls;");

        StringWriter stringWrite = new StringWriter();

        HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

        string headerTable = @"

 

";

        Response.Write(headerTable);

        Response.Write(stringWrite.ToString());

        Response.End();

    }

Here we are first saving the chart as an image. After saving the image we are creating one HTML with a table with a single tr and td tag and an image tag inside that td tag. We are then binding the generated chart image to the img tag as shown above.

Build the application and click on the Export to Excel button. Excel will be generated.

Save the Excel file and open in Microsoft Office. The chart will be displayed in the Excel file.

But there is one issue. If you don't have Microsoft office installed and you are using Open Office, then nothing will be displayed in the generated Excel file.

Let us try to change the MIME type in the code above and try to export the chart.

Response.ContentType = "application/vnd.oasis.opendocument.spreadsheet";

Response.AddHeader("Content-Disposition", "attachment; filename=Chart.ods;");

You will see that the chart is not being exported in the Open Office spreadsheet.

Also, what if you want to export the chart above to PDF? Specifying MIME for PDF in the code above will not work.

So directly using the chart control in your page has some limitations. The solution for this is simple.

Remove the chart control from the page and add a local RDLC report.



Drag the chart control from the toolbox displayed in the RDLC report and specify the datasource and view for the chart.



Save the report and go to the Chart.aspx page.

Add one ReportViewer control and ScriptManager control in the page and specify the Chart.rdlc report in the ReportViewer.



Specify the datasource in ReportViewer to the Northwind sqldatasource and run the page.

The chart will be displayed as in the following:



In the toolbar of ReportViewer there are options for exporting to Excel, PDF and Word.

After exporting the chart can be clearly viewed in Microsoft Office as well as Open Office.

Chart can also be exported to PDF without any issue.

Thus our task is accomplished without using any third party components. Thanks to the ReportViewer control.