Welcome, Guest: Register On Nairaland / LOGIN! / Trending / Recent / New
Stats: 3,177,331 members, 7,900,815 topics. Date: Thursday, 25 July 2024 at 05:18 PM

How To Send Data To Excel In Vb 2008 Without Crystal Report? - Programming - Nairaland

Nairaland Forum / Science/Technology / Programming / How To Send Data To Excel In Vb 2008 Without Crystal Report? (3282 Views)

Fingerprint Authentication In Vb.net Application / How To Deploy Application With Access Database In Vb.net 2008 / Post Ur Vb 6.0 Questions Here (2) (3) (4)

(1) (Reply)

How To Send Data To Excel In Vb 2008 Without Crystal Report? by kiddsx: 9:23pm On Dec 11, 2009
Please I need help on how to export my data in Vb 2008 to excel application without going through crystal report
Re: How To Send Data To Excel In Vb 2008 Without Crystal Report? by luckyCO(m): 4:29am On Dec 12, 2009
First put your data in datagridview and use the code below to export it.


call the function exporttoexcel and pass your datagridview, saveformat means you want to save the export file.

Public Sub ExportToExcel(ByVal grdView As DataGridView, Optional ByVal SaveFormat As Boolean = False)
Try



If ((grdView.Columns.Count = 0) Or (grdView.Rows.Count = 0)) Then
Exit Sub
End If

If MessageBox.Show("Are you sure you want to export the entire grid to Excel", "Choose Yes or No", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.No Then Exit Sub




'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 grdView.ColumnCount - 1
dset.Tables(0).Columns.Add(grdView.Columns(i).HeaderText)
Next
'add rows to the table
Dim dr1 As DataRow
Dim TotalGridRow As Long = (grdView.RowCount - 1) * 2



For i As Integer = 0 To grdView.RowCount - 1
dr1 = dset.Tables(0).NewRow

If SaveFormat = False Then
End If

For j As Integer = 0 To grdView.Columns.Count - 1
dr1(j) = grdView.Rows(i).Cells(j).Value
Next

dset.Tables(0).Rows.Add(dr1)
Next

Dim excel As New Excel.ApplicationClass
Dim wBook As Excel.Workbook
Dim wSheet As 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
Application.DoEvents()
Next

If SaveFormat = False Then
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)
Application.DoEvents()
Next
Next
End If
finishup:
Application.DoEvents()
wSheet.Columns.AutoFit()

If SaveFormat = True Then
Dim strFileName As String = SaveExcelFile()
If strFileName = "" Then Exit Sub

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)
End If

excel.Visible = True
excel.DisplayAlerts = True


Catch ex As Exception
MessageBox.Show("Error Occured Please Try Again", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub

Function SaveExcelFile(Optional ByVal filter As String = ".xls|*.xls|.xlsx|*.xlsx", Optional ByVal DefaultFileName As String = ""wink As String
Dim fshow As New SaveFileDialog
fshow.FileName = DefaultFileName
fshow.Filter = filter

fshow.ShowDialog()

Return fshow.FileName

End Function

(1) (Reply)

Java Devs, Which Do You Prefer? Eclipse Or Netbeans? / How Can I Extract Email Addresses? / Anyone Interested In Having A Peer-to-peer Script Or Custom Made Website ?

(Go Up)

Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health
religion celebs tv-movies music-radio literature webmasters programming techmarket

Links: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10)

Nairaland - Copyright © 2005 - 2024 Oluwaseun Osewa. All rights reserved. See How To Advertise. 15
Disclaimer: Every Nairaland member is solely responsible for anything that he/she posts or uploads on Nairaland.