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
- Click Project on the menu
- Select Add Reference from the drop down menu
- Once the dialog opens click the COM tab
- Scroll down to Microsoft Excel 12.0 (or whatever version you're running) and highlight it
- Click OK
Using the code
Once you've done that, add the following import statements to your file
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
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.