Excel Chart Export as Picture file VB.NET

The following program shows how can Export a Chart from Excel to Picture file like BMP,JPG, GIF etc. Before we create a Chart , we have to fill data in Excel sheet.

Continue reading “Excel Chart Export as Picture file VB.NET”

Advertisements

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