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