How to Create an Excel Chart in VB.NET Picture Box

The following program shows how to get the Excel chart as an image in Picture Box. Before we crate a Chart, we have to fill data in Excel sheet. After entering data your Excel sheet is look like the following picture.

excel-create-chart

After filling in the data, we have to create a chart object in VB.NET and configure the Chart object with necessary data like positions, size, data range, chart type etc.. and use the command for export chart as picture file, then load the picture from the saved path and into the picture box.

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        'add data
        xlWorkSheet.Cells(1, 1) = ""
        xlWorkSheet.Cells(1, 2) = "Student1"
        xlWorkSheet.Cells(1, 3) = "Student2"
        xlWorkSheet.Cells(1, 4) = "Student3"

        xlWorkSheet.Cells(2, 1) = "Term1"
        xlWorkSheet.Cells(2, 2) = "80"
        xlWorkSheet.Cells(2, 3) = "65"
        xlWorkSheet.Cells(2, 4) = "45"

        xlWorkSheet.Cells(3, 1) = "Term2"
        xlWorkSheet.Cells(3, 2) = "78"
        xlWorkSheet.Cells(3, 3) = "72"
        xlWorkSheet.Cells(3, 4) = "60"

        xlWorkSheet.Cells(4, 1) = "Term3"
        xlWorkSheet.Cells(4, 2) = "82"
        xlWorkSheet.Cells(4, 3) = "80"
        xlWorkSheet.Cells(4, 4) = "65"

        xlWorkSheet.Cells(5, 1) = "Term4"
        xlWorkSheet.Cells(5, 2) = "75"
        xlWorkSheet.Cells(5, 3) = "82"
        xlWorkSheet.Cells(5, 4) = "68"

        'create chart
        Dim chartPage As Excel.Chart
        Dim xlCharts As Excel.ChartObjects
        Dim myChart As Excel.ChartObject
        Dim chartRange As Excel.Range

        xlCharts = xlWorkSheet.ChartObjects
        myChart = xlCharts.Add(10, 80, 300, 250)
        chartPage = myChart.Chart
        chartRange = xlWorkSheet.Range("A1", "d5")
        chartPage.SetSourceData(Source:=chartRange)
        chartPage.ChartType = Excel.XlChartType.xlColumnClustered

        'exporting chart as picture file
        xlWorkSheet.ChartObjects(1).chart.Export(FileName:= _
        "C:excel_chart_export.bmp", FilterName:="BMP")

        'load the pipcture into the picture box
        PictureBox1.Image = New System.Drawing.Bitmap _
			("C:excel_chart_export.bmp")

        xlWorkSheet.SaveAs("C:vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("Chart File Exported !")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

When you execute this program you will get the picture in Picture Box from Excel Chart .

Advertisements

Insert cell data in an Excel file using OLEDB

Without using Excel Object we can insert , edit , delete , select etc. in cell content of an Excel file using OLEDB in VB.NET . Here we are using OleDbConnection , OleDbDataAdapter , DataSet for doing these operations in an Excel file. You have to import System.Data in the project for doing these operations . In order to add new content in the cell, we can use the INSERT command, known to any SQL users.

Sample Insert SQL

sql = "Insert into [Sheet1$] (id,name) values('5','e')"

The following code creates an OleDbConnection to the excel file and adds in a few new rows.

Imports System.Data
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim myCommand As New System.Data.OleDb.OleDbCommand
            Dim sql As String

            MyConnection = New System.Data.OleDb.OleDbConnection _
            ("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _
            "'c:testfile.xls';Extended Properties=Excel 8.0;")

            MyConnection.Open()
            myCommand.Connection = MyConnection
            sql = "Insert into [Sheet1$] (id,name) values('5','e'); INSERT into [Sheet1$]) (id, name) VALUES ('50','fifty is a very good number!');"
            myCommand.CommandText = sql
            myCommand.ExecuteNonQuery()
            MyConnection.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        MsgBox("Row Added ")
    End Sub
End Class

When you execute this source code the will insert a row in the Excel file.

How to export from database to excel 2007

excelHere we are going to export data from database to Excel file . We load the data from database to dataset and then create a new Excel file and write the data to Excel file .
First step is to Load the Product table data to data set , for detail of Product table have a look at the table statement below:

CREATE TABLE [dbo].[Product] (
[Product_id] [int] NOT NULL ,
[Product_name] [varchar] (50) ,
[Product_price] [numeric](18, 0) NULL
) ON [PRIMARY]

Next is to create a new Excel file and write the data from dataset to Excel file.

For i = 0 To ds.Tables(0).Rows.Count - 1
	For j = 0 To ds.Tables(0).Columns.Count - 1
		xlWorkSheet.Cells(i + 1, j + 1) =  _
		ds.Tables(0).Rows(i).Item(j)
	Next
Next

Full source code:

Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click

        Dim cnn As SqlConnection
        Dim connectionString As String
        Dim sql As String
        Dim i, j As Integer

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        connectionString = "data source=servername;" & _
        "initial catalog=databasename;user id=username;password=password;"
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        sql = "SELECT * FROM Product"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As New DataSet
        dscmd.Fill(ds)

        For i = 0 To ds.Tables(0).Rows.Count - 1
            For j = 0 To ds.Tables(0).Columns.Count - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                ds.Tables(0).Rows(i).Item(j)
            Next
        Next

        xlWorkSheet.SaveAs("C:vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        cnn.Close()

        MsgBox("You can find the file C:vbexcel.xlsx")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class