Introduction
Exporting data to Excel of Microsoft Office is currently needed. But many of them used very complicated coding for simple export either from Datagrid
or DataTable
. I used simple Dataset
to export the data.
Background
Introducing the way of sending data from datagrid
or by Datatable
. I used Dataset
which is easy to use. But for code optimization, dataset
is not advisable compared to Reader
and DataTable
.
Using the Code
Currently, available code for exporting the data to Excel are given which is so complete and not straight forward because they used the Datagrid
and DataTable
which are given below:
From DataTable to Excel sheet:
Dim dt1 As New DataTable
Dim I1, J1 As Integer
For I1 = 0 To dsmas1.Tables(0).Columns.Count - 1
dt1.Columns.Add(dsmas1.Tables(0).Columns(I1).ColumnName)
Next
For I1 = 0 To dsmas1.Tables(0).Rows.Count - 1
Dim DR As DataRow = Nothing
DR = dt1.NewRow
For J1 = 0 To dsmas1.Tables(0).Columns.Count - 1
DR.Item(J1) = dsmas1.Tables(0).Rows(I1).ItemArray(J1)
Next
dt1.Rows.Add(DR)
Next
rel_ds.Tables.Add(dt1)
Dim dt As New DataTable
Dim I, J As Integer
For I = 0 To dschd1.Tables(0).Columns.Count - 1
dt.Columns.Add(dschd1.Tables(0).Columns(I).ColumnName)
Next
For I = 0 To dschd1.Tables(0).Rows.Count - 1
Dim DR As DataRow = Nothing
DR = dt.NewRow
For J = 0 To dschd1.Tables(0).Columns.Count - 1
DR.Item(J) = dschd1.Tables(0).Rows(I).ItemArray(J)
Next
dt.Rows.Add(DR)
Next
rel_ds.Tables.Add(dt)
For DataGrid to Excel sheet:
If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
Exit Sub
End If
Dim dset As New DataSet
dset.Tables.Add()
For i As Integer = 0 To DataGridView1.ColumnCount - 1
dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
Next
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
But I used only Dataset to Excel sheet.
In code, I used a user defined function called "Load_Excel_Details()
" in which I used an old technique like create Object for Excel, add the Book for a sheet and sheet for the data.
i.e,.
- Excel <--
System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
- Book <------
Excel Workbooks.Add()
- Sheet <-----
Excel SheetsInNewWorkbook = 1
- Data <-----
Excel cells(1, i).value
which is used in the code like given below:
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.xls"
.ActiveCell.Worksheet.SaveAs(filename)
End With
Points of Interest
I used simple For
loops.... and insert the values by Rows, Columns and Cells formats.
History
I proposed this after I used it in my company project which was appreciate by the customers.