Click here to Skip to main content
13,343,419 members (57,678 online)
Click here to Skip to main content
Add your own
alternative version


36 bookmarked
Posted 18 Dec 2007

Get the Values From DataBase and Stored into excell Sheet

, 18 Dec 2007
Rate this:
Please Sign up or sign in to vote.
Get the Values From DataBase and Stored into excell Sheet


What the article/code snippet does, why it's useful, the problem it solves etc.

<o:p> Here I have Posted the simple program for Move the values from DataBase to Excel. And it will stored in the given path.<o:p>

Read the example I posted, try to implement it, when you run into problems/errors post the code you are working with and I will try and help you, however, going to just post a solution to your problem so you don't have to do anything except copy & paste to get it working


To export data to Excel I use the following method. This method takes your DataSet (which is what I assume you're binding to your DataGridView Or Directly) and populates an Excel file with it. To make this work you need to add a reference to the Microsoft.Excel11 Interop (or 12 for 2007, 10 for XP, etc). To do this follow these steps

  1. Click Project on the menu
  2. Select Add Reference from the drop down menu
  3. Once the dialog opens click the COM tab
  4. Scroll down to Microsoft Excel 12.0 (or whatever version you're running) and highlight it
  5. Click OK

Using the code

Once you've done that, add the following import statements to your file

//Imports System.Runtime.InteropServices.Marshal
//Imports Microsoft.Office.Interop.Excel

I have a function and a sub procedure for this, one to actually create the Excel file, and one to dump the data from the DataTable into the Excel file. The function, aptly named CreateExcelFile takes 2 parameters, the name you want to save the file as and the DataTable that contains your data.


Imports System.Data.SqlClient<o:p />

Imports System.Runtime.InteropServices.Marshal<o:p />

Imports Microsoft.Office.Interop.Excel<o:p />


‘ btn_Click event:<o:p />


Dim ds As New DataSet<o:p />

Dim cmd As SqlCommand<o:p />

Dim da As SqlDataAdapter<o:p />

Dim dttest As DataTable<o:p />

Dim consrt As String = "server=SERVER;database=AAAA;uid=sa; pwd=XXXX;"<o:p />

Dim conn As New SqlConnection(consrt)<o:p />

conn.Open()<o:p />


cmd = New SqlCommand("select * from TableName ", conn)<o:p />

cmd.CommandType = CommandType.Text<o:p />


cmd.ExecuteNonQuery()<o:p />


da = New SqlDataAdapter(cmd)<o:p />

da.Fill(ds)<o:p />

conn.Close()<o:p />


Form1.CreateExcelFile(TextBox1.Text.Trim, ds)<o:p />


End sub<o:p />

Shared Function

‘ shared function to Create and Store in given path<o:p />


Public Shared Function CreateExcelFile(ByVal fileName As String, ByVal ds1 As DataSet) As Boolean<o:p />


Dim excelExport As New Microsoft.Office.Interop.Excel.Application()<o:p />

Dim excelBook As Microsoft.Office.Interop.Excel.Workbook<o:p />

Dim excelSheets As Microsoft.Office.Interop.Excel.Sheets<o:p />

Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet<o:p />

Dim excelCells As Microsoft.Office.Interop.Excel.Range<o:p />

Dim location As Integer = System.Windows.Forms.Application.ExecutablePath.LastIndexOf("\")<o:p />

Dim exPath As String = System.Windows.Forms.Application.ExecutablePath<o:p />

Dim filePath As String<o:p />

Dim dstemp As DataSet<o:p />


dstemp = ds1<o:p />


Try<o:p />


filePath = exPath.Substring(0, (location + 1)) + "tmpFiles\" & fileName<o:p />


If Not System.IO.Directory.Exists(exPath.Substring(0, (location + 1)) + "tmpFiles\") Then<o:p />


System.IO.Directory.CreateDirectory(exPath.Substring(0, (location + 1)) + "tmpFiles\")<o:p />


End If<o:p />


excelExport.Visible = False : excelExport.DisplayAlerts = False<o:p />

excelBook = excelExport.Workbooks.Add<o:p />

excelSheets = excelBook.Worksheets<o:p />

excelSheet = CType(excelSheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)<o:p />

excelSheet.Name = "YourSheetName - " & Date.Now.Day & Date.Now.ToString("MMM") & Date.Now.ToString("yy")<o:p />

excelCells = excelSheet.Cells<o:p />


PopulateSheet(dstemp, excelCells)<o:p />


excelSheet.SaveAs(filePath)<o:p />

excelBook.Close()<o:p />

excelExport.Quit()<o:p />


ReleaseComObject(excelCells) : ReleaseComObject(excelSheet)<o:p />


ReleaseComObject(excelSheets)<o:p />

ReleaseComObject(excelBook) : ReleaseComObject(excelExport)<o:p />


excelExport = Nothing : excelBook = Nothing : excelSheets = Nothing<o:p />

excelSheet = Nothing : excelCells = Nothing<o:p />


System.GC.Collect()<o:p />

Return True<o:p />


' End If<o:p />


Catch ex As Exception<o:p />

MsgBox(ex.Message, "Error exporting data")<o:p />

Return False<o:p />

End Try<o:p />

Enjoy friends ,

This is my first Article so please, Accept if any mistakes have made in it.

Have a nice day


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


About the Author

Member 4159567
Web Developer
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralHaving Problem With Namespace Pin
karwavivek19-May-11 2:34
memberkarwavivek19-May-11 2:34 
Generalhelp Pin
animediablo4-Oct-10 6:43
memberanimediablo4-Oct-10 6:43 
i want to do that but in a web page not in a form
this code not work for the web page

Imports Microsoft.Office.Interop.Excel

Dim location As Integer = System.Windows.Forms.Application.ExecutablePath.LastIndexOf("\")
Dim exPath As String = System.Windows.Forms.Application.ExecutablePath

i know that you want to help solve problems not put code, but i dont know how to change that
GeneralMy vote of 1 Pin
cosmo6929-Apr-09 5:52
membercosmo6929-Apr-09 5:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.180111.1 | Last Updated 18 Dec 2007
Article Copyright 2007 by Member 4159567
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid