Get the Values From DataBase and Stored into excell Sheet

• Download DocDatabaseToExcel - 5 KB

Introduction

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

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

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

Imports System.Runtime.InteropServices.Marshal

Imports Microsoft.Office.Interop.Excel

‘ btn_Click event:

Dim ds As New DataSet

Dim cmd As SqlCommand

Dim da As SqlDataAdapter

Dim dttest As DataTable

Dim consrt As String = "server=SERVER;database=AAAA;uid=sa; pwd=XXXX;"

Dim conn As New SqlConnection(consrt)

conn.Open()

cmd = New SqlCommand("select * from TableName ", conn)

cmd.CommandType = CommandType.Text

cmd.ExecuteNonQuery()

da = New SqlDataAdapter(cmd)

da.Fill(ds)

conn.Close()

Form1.CreateExcelFile(TextBox1.Text.Trim, ds)

‘ End sub

Shared Function

‘ shared function to Create and Store in given path

Public Shared Function CreateExcelFile(ByVal fileName As String, ByVal ds1 As DataSet) As Boolean

Dim excelExport As New Microsoft.Office.Interop.Excel.Application()

Dim excelBook As Microsoft.Office.Interop.Excel.Workbook

Dim excelSheets As Microsoft.Office.Interop.Excel.Sheets

Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet

Dim excelCells As Microsoft.Office.Interop.Excel.Range

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

Dim exPath As String = System.Windows.Forms.Application.ExecutablePath

Dim filePath As String

Dim dstemp As DataSet

dstemp = ds1

Try

filePath = exPath.Substring(0, (location + 1)) + "tmpFiles\" & fileName

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

System.IO.Directory.CreateDirectory(exPath.Substring(0, (location + 1)) + "tmpFiles\")

End If

excelExport.Visible = False : excelExport.DisplayAlerts = False

excelBook = excelExport.Workbooks.Add

excelSheets = excelBook.Worksheets

excelSheet = CType(excelSheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)

excelSheet.Name = "YourSheetName - " & Date.Now.Day & Date.Now.ToString("MMM") & Date.Now.ToString("yy")

excelCells = excelSheet.Cells

PopulateSheet(dstemp, excelCells)

excelSheet.SaveAs(filePath)

excelBook.Close()

excelExport.Quit()

ReleaseComObject(excelCells) : ReleaseComObject(excelSheet)

ReleaseComObject(excelSheets)

ReleaseComObject(excelBook) : ReleaseComObject(excelExport)

excelExport = Nothing : excelBook = Nothing : excelSheets = Nothing

excelSheet = Nothing : excelCells = Nothing

System.GC.Collect()

Return True

' End If

Catch ex As Exception

MsgBox(ex.Message, "Error exporting data")

Return False

End Try

Enjoy friends ,

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

Have a nice day