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