Click here to Skip to main content
12,400,183 members (60,659 online)
Click here to Skip to main content
Articles » Languages » VB.NET » Applications » Downloads

Stats

646.2K views
30.1K downloads
168 bookmarked
Posted

Export to Excel using VB.Net

, 25 Apr 2008 CPOL
Export data from VB.Net Dataset to Excel without using Datagrid/DataTable
ExportToExcel
ExportToExcel
ExportToExcel.suo
ExportToExcel
bin
Debug
ExportToExcel.exe
ExportToExcel.pdb
ExportToExcel.vshost.exe
Resource
db1.mdb
Release
ExportToExcel.vbproj.user
My Project
Application.myapp
obj
Debug
ExportToExcel.exe
ExportToExcel.Form1.resources
ExportToExcel.pdb
ExportToExcel.Resources.resources
ExportToExcel.vbproj.GenerateResource.Cache
ExportToExcel.vbproj.ResolveComReference.cache
TempPE
My Project.Resources.Designer.vb.dll
Resource.db1DataSet.Designer.vb.dll
tempdbDataSet.Designer.vb.dll
Release
build.force
ExportToExcel.Form1.resources
ExportToExcel.Resources.resources
ExportToExcel.vbproj.GenerateResource.Cache
ExportToExcel.vbproj.ResolveComReference.cache
TempPE
My Project.Resources.Designer.vb.dll
Resource.db1DataSet.Designer.vb.dll
Resource
db1.mdb
db1DataSet.xsc
db1DataSet.xss
samplepage.bmp
Resources
samplepage.bmp
'included the Excel object lib 11.0 and office lib 11.0
Imports Microsoft.Office.Interop.Excel

Public Class Form1

#Region "Common Variable"

    Private conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Resource\db1.mdb;Persist Security Info=True")
    Private ComDset As New DataSet
    Private ComDset1 As New DataSet
#End Region

#Region "Common Function"
    Private Sub Load_Excel_Details()
        'Extracting from database
        Dim str, filename As String
        Dim col, row As Integer
        str = "SELECT * from Table1"
        Dim adp As New OleDb.OleDbDataAdapter(str, conn)
        Try
            ComDset.Reset()
            adp.Fill(ComDset, "TTbl")
            If ComDset.Tables.Count < 0 Or ComDset.Tables(0).Rows.Count <= 0 Then
                Exit Sub
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Dim Excel As Object = CreateObject("Excel.Application")
        If Excel Is Nothing Then
            MsgBox("It appears that Excel is not installed on this machine. This operation requires MS Excel to be installed on this machine.", MsgBoxStyle.Critical)
            Return
        End If


        'Export to Excel process
        Try
            With Excel
                .SheetsInNewWorkbook = 1
                .Workbooks.Add()
                .Worksheets(1).Select()

                Dim i As Integer = 1
                For col = 0 To ComDset.Tables(0).Columns.Count - 1
                    .cells(1, i).value = ComDset.Tables(0).Columns(col).ColumnName
                    .cells(1, i).EntireRow.Font.Bold = True
                    i += 1
                Next
                i = 2
                Dim k As Integer = 1
                For col = 0 To ComDset.Tables(0).Columns.Count - 1
                    i = 2
                    For row = 0 To ComDset.Tables(0).Rows.Count - 1
                        .Cells(i, k).Value = ComDset.Tables(0).Rows(row).ItemArray(col)
                        i += 1
                    Next
                    k += 1
                Next
                filename = "c:\File_Exported" & Format(Now(), "dd-MM-yyyy_hh-mm-ss") & ".xls"
                .ActiveCell.Worksheet.SaveAs(filename)
            End With
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            MsgBox("Data's are exported to Excel Succesfully in '" & filename & "'", MsgBoxStyle.Information)

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        ' The excel is created and opened for insert value. We most close this excel using this system
        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each i As Process In pro
            i.Kill()
        Next

    End Sub
#End Region

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Load_Excel_Details()
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim str As String
        str = "SELECT * from Table1"
        Dim adp As New OleDb.OleDbDataAdapter(str, conn)
        Try
            ComDset.Reset()
            adp.Fill(ComDset, "TTbl")
            DataGridView1.DataSource = ComDset.Tables(0)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'verfying the datagridview having data or not
        If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
            Exit Sub
        End If

        'Creating dataset to export
        Dim dset As New DataSet
        'add table to dataset
        dset.Tables.Add()
        'add column to that table
        For i As Integer = 0 To DataGridView1.ColumnCount - 1
            dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
        Next
        'add rows to the table
        Dim dr1 As DataRow
        For i As Integer = 0 To DataGridView1.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To DataGridView1.Columns.Count - 1
                dr1(j) = DataGridView1.Rows(i).Cells(j).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next

        Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

        wBook = excel.Workbooks.Add()
        wSheet = wBook.ActiveSheet()

        Dim dt As System.Data.DataTable = dset.Tables(0)
        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0

        For Each dc In dt.Columns
            colIndex = colIndex + 1
            excel.Cells(1, colIndex) = dc.ColumnName
        Next

        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

            Next
        Next

        wSheet.Columns.AutoFit()
        Dim strFileName As String = "D:\ss.xls"
        Dim blnFileOpen As Boolean = False
        Try
            Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
            fileTemp.Close()
        Catch ex As Exception
            blnFileOpen = False
        End Try

        If System.IO.File.Exists(strFileName) Then
            System.IO.File.Delete(strFileName)
        End If

        wBook.SaveAs(strFileName)
        excel.Workbooks.Open(strFileName)
        excel.Visible = True
    End Sub
End Class

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Senthil Sambandam
Technical Lead
India India
No Biography provided

You may also be interested in...

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160721.1 | Last Updated 26 Apr 2008
Article Copyright 2007 by Senthil Sambandam
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid