- exporttoexcel.zip
- ExportToExcel
- ExportToExcel
- ExportToExcel.sln
- ExportToExcel.suo
- ExportToExcel
- app.config
- bin
- Debug
- Release
- ExportToExcel.vbproj
- ExportToExcel.vbproj.user
- Form1.Designer.vb
- Form1.resx
- Form1.vb
- My Project
- obj
- Debug
- ExportToExcel.exe
- ExportToExcel.Form1.resources
- ExportToExcel.pdb
- ExportToExcel.Resources.resources
- ExportToExcel.vbproj.GenerateResource.Cache
- ExportToExcel.vbproj.ResolveComReference.cache
- ExportToExcel.xml
- TempPE
- My Project.Resources.Designer.vb.dll
- Resource.db1DataSet.Designer.vb.dll
- tempdbDataSet.Designer.vb.dll
- ExportToExcel.vbproj.FileList.txt
- Release
- build.force
- ExportToExcel.Form1.resources
- ExportToExcel.Resources.resources
- ExportToExcel.vbproj.GenerateResource.Cache
- ExportToExcel.vbproj.ResolveComReference.cache
- TempPE
- My Project.Resources.Designer.vb.dll
- Resource.db1DataSet.Designer.vb.dll
- Resource
- Resources
- samplepage.bmp
|
'included the Excel object lib 11.0 and office lib 11.0
Imports Microsoft.Office.Interop.Excel
Public Class Form1
#Region "Common Variable"
Private conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Resource\db1.mdb;Persist Security Info=True")
Private ComDset As New DataSet
Private ComDset1 As New DataSet
#End Region
#Region "Common Function"
Private Sub Load_Excel_Details()
'Extracting from database
Dim str, filename As String
Dim col, row As Integer
str = "SELECT * from Table1"
Dim adp As New OleDb.OleDbDataAdapter(str, conn)
Try
ComDset.Reset()
adp.Fill(ComDset, "TTbl")
If ComDset.Tables.Count < 0 Or ComDset.Tables(0).Rows.Count <= 0 Then
Exit Sub
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
Dim Excel As Object = CreateObject("Excel.Application")
If Excel Is Nothing Then
MsgBox("It appears that Excel is not installed on this machine. This operation requires MS Excel to be installed on this machine.", MsgBoxStyle.Critical)
Return
End If
'Export to Excel process
Try
With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()
Dim i As Integer = 1
For col = 0 To ComDset.Tables(0).Columns.Count - 1
.cells(1, i).value = ComDset.Tables(0).Columns(col).ColumnName
.cells(1, i).EntireRow.Font.Bold = True
i += 1
Next
i = 2
Dim k As Integer = 1
For col = 0 To ComDset.Tables(0).Columns.Count - 1
i = 2
For row = 0 To ComDset.Tables(0).Rows.Count - 1
.Cells(i, k).Value = ComDset.Tables(0).Rows(row).ItemArray(col)
i += 1
Next
k += 1
Next
filename = "c:\File_Exported" & Format(Now(), "dd-MM-yyyy_hh-mm-ss") & ".xls"
.ActiveCell.Worksheet.SaveAs(filename)
End With
System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
Excel = Nothing
MsgBox("Data's are exported to Excel Succesfully in '" & filename & "'", MsgBoxStyle.Information)
Catch ex As Exception
MsgBox(ex.Message)
End Try
' The excel is created and opened for insert value. We most close this excel using this system
Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
For Each i As Process In pro
i.Kill()
Next
End Sub
#End Region
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Load_Excel_Details()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim str As String
str = "SELECT * from Table1"
Dim adp As New OleDb.OleDbDataAdapter(str, conn)
Try
ComDset.Reset()
adp.Fill(ComDset, "TTbl")
DataGridView1.DataSource = ComDset.Tables(0)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'verfying the datagridview having data or not
If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
Exit Sub
End If
'Creating dataset to export
Dim dset As New DataSet
'add table to dataset
dset.Tables.Add()
'add column to that table
For i As Integer = 0 To DataGridView1.ColumnCount - 1
dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
Next
'add rows to the table
Dim dr1 As DataRow
For i As Integer = 0 To DataGridView1.RowCount - 1
dr1 = dset.Tables(0).NewRow
For j As Integer = 0 To DataGridView1.Columns.Count - 1
dr1(j) = DataGridView1.Rows(i).Cells(j).Value
Next
dset.Tables(0).Rows.Add(dr1)
Next
Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
wBook = excel.Workbooks.Add()
wSheet = wBook.ActiveSheet()
Dim dt As System.Data.DataTable = dset.Tables(0)
Dim dc As System.Data.DataColumn
Dim dr As System.Data.DataRow
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(1, colIndex) = dc.ColumnName
Next
For Each dr In dt.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
Next
Next
wSheet.Columns.AutoFit()
Dim strFileName As String = "D:\ss.xls"
Dim blnFileOpen As Boolean = False
Try
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
fileTemp.Close()
Catch ex As Exception
blnFileOpen = False
End Try
If System.IO.File.Exists(strFileName) Then
System.IO.File.Delete(strFileName)
End If
wBook.SaveAs(strFileName)
excel.Workbooks.Open(strFileName)
excel.Visible = True
End Sub
End Class
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.