Home > Technology > ASP.Net Chart Control-Exporting Chart to Excel FINALLY!

ASP.Net Chart Control-Exporting Chart to Excel FINALLY!

June 1st, 2009 Leave a comment Go to comments

Hi Everyone, I am writing this post because I have been trying to export a chart to excel using the new charting control that Microsoft has made available for Visual Studio.  I am building a Business Intelligence Dashboard for work and one of the essential components of this application is to export these charts to excel/word/etc.  After about 2 months (off and on) of trying to get this to work, I finally got it.

Let’s Get Started

Save the Chart as an Image to the server and make sure we have an empty excel file that is already stored in the server (For this example we will use MyExcel.xls).  You write this code either on the PageLoad or ButtonClick Handler.

Save the chart as an image to the server like shown below in VB.net.

Chart1.SaveImage(Server.MapPath(~\WebCharts\Chart.Jpeg”))

Now we can build the excel file after the image has been created and stored on the server.  This is the code to do that.  Note we must import the Microsoft.Office.Interop.Excel and Microsoft.Office.Core namespaces.  Here is the code that writes a dataset (in this example the dataset is called mydata) into the “MyExcel.xls” file that is stored at the server level.  At the end we will have an html link that will call this “MyExcel.xls” from the server to the user via browser.

 
Dim sFileName As String = Server.MapPath(“~\WebCharts\Chart.Jpeg”)

Dim sFilePath As String = Server.MapPath(“myexcel.xls”)

If File.Exists(sFilePath)

Then

File.Delete(sFilePath)

End

Dim objApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application

Dim objSheet As Microsoft.Office.Interop.Excel.Worksheet = New Microsoft.Office.Interop.Excel.Worksheet

Dim objWorkBook As Microsoft.Office.Interop.Excel.Workbook

Dim col, row As Integer

Try

If myData.Tables.Count < 0 Or myData.Tables(0).Rows.Count <= 0 Then

Exit Sub

End If

Catch ex As

Exception

End

Try

objWorkBook = objApp.Workbooks.Add()
objSheet = objWorkBook.ActiveSheet

objSheet = objWorkBook.Sheets(1)

objSheet.Name =
“Graph with Report”

‘This is where we add the picture.

objSheet.Shapes.AddPicture(sFileName, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 50, 150, 850, 500)


Dim i As Integer= 1

With

objApp.Worksheets(1).Range(“A1″, “H1″).Interior.ColorIndex = 23.Worksheets(1).Range(“A1″, “H1″).Font.Color = RGB(255, 255, 255)

.Cells(1, 1).Value =“Example History for Day(s) Selected: “ & Date1.Text & ” To: “& Date2.Text

.Cells(1, 1).EntireRow.Font.Bold =True

.Cells(3, 1).Value =“Example Sales Date”

.Cells(3, 1).EntireRow.Font.Bold =True

.Cells(3, 2).Value =“Example Amount”

.Cells(3, 2).EntireRow.Font.Bold =True

i = 2

Dim k As Integer= 1

For col = 0 To myData.Tables(0).Columns.Count – 1i = 4

For row = 0 To myData.Tables(0).Rows.Count – 1

.Cells(i, k).Value = myData.Tables(0).Rows(row).ItemArray(col)
i += 1

Next

k += 1

Next

objWorkBook.SaveAs(sFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)

End With

Catch ex As

Exception

Finally

objApp.Quit()

End

Okay now we should have a dataset and an image stored in our “MyExcel.xls” file ready to go.

Calling “My Excel.xls” From HTML

This is the easiest part and you don’t have to necessary call it from a hyperlink. You can use a button or any other control to call this file. Here is the code we will use to create the link in HTML:

a href=”MyExcel.xls””>Open Excel File from Browser /a

To make it even easier, I would just drag and drop the Hyperlink control in Visual Studio into my Project and find the excel file in Properties under Source.

I hope this helps as I have been looking for a solution for quite some time. The Chart Control forum has been very useful for other matter with regards to this new charting control. I would like to thank Alex Gorv (The forum moderator) personally for all his help with other issues I have encountered using this control.

Till next time,

Rene
Please contact me if you have any questions at: rene@ReniTECH.com

Comments (4) Trackbacks (0) Leave a comment Trackback
  1. July 7th, 2009 at 01:37 | #1

    Hi! I like your srticle and I would like very much to read some more information on this issue. Will you post some more?

  2. July 7th, 2009 at 17:50 | #2

    Do you think Michel Jackson killed himself?
    What you think about my web? http://www.easyfaxlesspaydayloan.com fast cash advances fast cash advances

  3. Balaji
    July 15th, 2009 at 12:29 | #3

    Hi rene,

    This is balaji i have finished exporting the same in C# method and in regard of this method it is opening in a seperate book i need it to be opened in tha same excel book named myexcel.xls which is pre-definely saved..Requirement is very urgent..Please help me out..

    Thanks & Regards
    balaji.S

  4. admin
    July 15th, 2009 at 14:19 | #4

    Hi balaji,
    I am not sure what you mean regarding you need it to export the chart or image into one excel workbook. This example does exactly that. If you send me the source code I may be able to help you. Thanks for the post.

  1. No trackbacks yet.

Spam Protection by WP-SpamFree