Develop chart in ASP.NET with export to Excel and PDF for MS Excel as well as Open Office

Recently while working on one project in ASP.NET 4.0, i came across a scenario where i had to develop one chart which can be exported to PDF and Excel. Apart from this it has to be viewable in both MS 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 shown below.

Go to tool box and drag Chart control on 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, chart has been generated successfully. But, now we want to export above chart in PDF or Excel.

First, let us see how to export above chart to excel.

Let us add one button in the page. Add following event in the button.

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 Export to Excel button. Excel will be generated.
Save the excel file and open it in MS Office. You will see that it has added one image tag of chart but actually image is not rendered.

There is one workaround for this. Add following event in 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 image. After saving the image we are creating one html with a table with 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 Export to Excel button. Excel will be generated.

Save the excel file and open in MS Office. Chart will be displayed in the excel file.

But there is one issue. If you don't have MS 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 above code 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 getting exported in Open Office spreadsheet.

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

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

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

 

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

 
Save the report and goto Chart.aspx page.

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

 

Specify the datasource in reportviewer to northwind sqldatasource and run the page.
Chart will be displayed as shown below:

In the toolbar of reportviewer there are options for export to Excel, PDF and Word.
After exporting chart can be clearly viewed in MS 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 reportviewer control.