Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / Visual Basic
Article

Get the Values From DataBase and Stored into excell Sheet

Rate me:
Please Sign up or sign in to vote.
1.00/5 (2 votes)
18 Dec 2007CPOL3 min read 43.1K   1.4K   36   3
Get the Values From DataBase and Stored into excell Sheet

Introduction

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

Background

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.

Code

Imports System.Data.SqlClient<o:p>

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

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

<o:p>

‘ btn_Click event:<o:p>

<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>

<o:p>

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

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

<o:p>

cmd.ExecuteNonQuery()<o:p>

<o:p>

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

da.Fill(ds)<o:p>

conn.Close()<o:p>

<o:p>

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

<o:p>

End sub<o:p>

Shared Function

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

<o:p>

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

<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>

<o:p>

dstemp = ds1<o:p>

<o:p>

Try<o:p>

<o:p>

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

<o:p>

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

<o:p>

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

<o:p>

End If<o:p>

<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>

<o:p>

PopulateSheet(dstemp, excelCells)<o:p>

<o:p>

excelSheet.SaveAs(filePath)<o:p>

excelBook.Close()<o:p>

excelExport.Quit()<o:p>

<o:p>

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

<o:p>

ReleaseComObject(excelSheets)<o:p>

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

<o:p>

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

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

<o:p>

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

Return True<o:p>

<o:p>

' End If<o:p>

<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

License

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


Written By
Web Developer
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralHaving Problem With Namespace Pin
Karwa_Vivek19-May-11 1:34
Karwa_Vivek19-May-11 1:34 
Generalhelp Pin
animediablo4-Oct-10 5:43
animediablo4-Oct-10 5:43 
GeneralMy vote of 1 Pin
cosmo6929-Apr-09 4:52
professionalcosmo6929-Apr-09 4: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.