Click here to Skip to main content
Click here to Skip to main content

Get the Values From DataBase and Stored into excell Sheet

, 18 Dec 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

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

License

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

Share

About the Author

Member 4159567
Web Developer
United States United States
No Biography provided

Comments and Discussions

 
Generalhelp Pinmemberanimediablo4-Oct-10 6:43 

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

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

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