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