How to extract charts from an excel spreadsheet whit VB.NET
In this article I am going to show you a VB.NET script that allows you to extract charts from an excel file. You have to use Visual Studio Express (or payment versions).
A VB.NET console application is used in order to implement this project but if you want you can also use a Windows Form Application to have an user friendly interface (see download area).
First of all, you have to add the following reference to your project: Microsoft.Office.Interop.Excel. You can add this Extension in this way: go to Project -> Add Reference -> .NET and select from the list the Extension.
The next step is to insert the following code:
Imports Microsoft.Office.Interop.Excel Module Module1 Sub Main() Dim excel As New Application Dim wb As Workbook Dim ws As Worksheet Dim chart As Chart wb = excel.Workbooks.Open("C:\... file path") For y = 1 To wb.Worksheets.Count ws = wb.Worksheets.Item(y) For i = 1 To ws.ChartObjects.Count chart = ws.ChartObjects(i).Chart chart.Export("C:\... Output folder path ...\Worksheets " & y.ToString & "Chart " & i.ToString & ".png", "PNG") Next i Next y excel.Workbooks.Close() excel.Quit() End Sub End Module
The first code line need to import the "Excel" namespace names. Thus, you can instantiate the "Application" object and create a reference to the following objects: "Workbook", "Worksheet" and "Chart".
Now you can use the "Workbooks.Open" Method (in more detail: this method creates a new "workbook" object and returns it) in order to open the excel file you want.
The first "For...Next" Statement allows to "open" all the worksheets included in the workbook opened in the previous step. In fact, the "Count" Method is used to return the number of elements in the "Worksheets" Collection.
The next step is to use the "Worksheets.Item" Property in order to return a single "Worksheet" Object from the "Worksheets" Collection.
The second "For...Next" Statement allows to export all the charts included in each worksheet. In fact, the "Count" Method is used to return the number of elements in the "ChartObjects" Collection.
Thus the "Worksheets.ChartObjects" Method selects the chart number "i" and the "Chart.Export" Method xports the chart in a graphic format in a defined path.
At the end of the code you have to use the "Workbooks.Close" Method to close the Workbook and the "Application.Quit" Method to quit Microsoft Excel.
If you find errors in this code, I will be very grateful if you report them to me. Thanks!