##

## Introduction

Export the data's to **Excel **of Microsoft office is currently need in many concern. 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's from datagrid or by Datatable. I used Dataset which is easy to use. But for code optimization dataset is not advisable compare to **Reader **and **DataTable**

## Using the code

In current available code for export the data to Excel are given. Which are so completed and not in 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()**" which i used 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 in my company project which appreciate by the customers.