Export Data from Dataset or Datatable to Excel File

Save Data from DataSet or DataTable to Excel File (VB.NET)

Namespace that needs to import for excel:
Imports Microsoft.Office.Interop.Excel

Add instance of excel application
Excel = New Excel.Application

Add Workbook to the Worksheet (We can Add More Workbooks Too in Similar Way)

Create new worksheet
createWorksheet("Workbook Name")

Show Dialog Box
Dim dlgsave As New SaveFileDialog
dlgsave.Filter = "Excel Spreadsheets (*.xls)|*.xls"
If dlgsave.ShowDialog() = DialogResult.OK Then

Insert Column Name
into the excel sheet
For iDColumn = 0 To dtShareholders.Columns.Count - 2
Excel.Cells(iRRow, iRColumn).Value = dtShareholders.&_
Columns(iDColumn + 1).ColumnName.ToString
Excel.Cells(iRRow, iRColumn).Borders.Weight = 2

Excel.Cells(iRRow, iRColumn).Interior.ColorIndex = 49
Excel.Cells(iRRow, iRColumn).Font.ColorIndex = 2
Excel.Cells(iRRow, iRColumn).Font.Bold = True
Excel.Cells(iRRow, iRColumn).FormulaHidden = True
Excel.Cells(iRRow, iRColumn).RowHeight = 25
Excel.Cells(iRRow, iRColumn).VerticalAlignment = xlCenter
iRColumn = iRColumn + 1

This loop will print all rows one by one into the excel sheet
iRRow = iRRow + 1
For iDRow = 0 To dtShareholders.Rows.Count - 2
iRColumn = 1
Excel.Cells(iRRow, iRColumn).Value = dtShareholders.&_
Excel.Cells(iRRow, iRColumn).EntireColumn.AutoFit()
Excel.Cells(iRRow, iRColumn).Borders.Weight = 2
Excel.Cells(iRRow, iRColumn).Font.Bold = True
Excel.Cells(iRRow, iRColumn).RowHeight = 15
Excel.Cells(iRRow, iRColumn).HorizontalAlignment = xlLeft
iRRow = iRRow + 1

Set the Selected Sheet in the Workbook
Dim strFileName As String
strFileName = IO.Path.GetDirectoryName&_
.Location) & "\SheetName"
If IO.File.Exists(strFileName & ".xls") Then
IO.File.Delete(strFileName & ".xls")
End If

Save the active workbook into the given location and delete if there is any file with the same name on the same location already exist.
Excel.ActiveWorkbook().SaveAs(strFileName & ".xls")
Dim destination As String = dlgsave.FileName
''Check if the file Saved or not
If destination <> "" Then
If IO.File.Exists(destination) Then
End If

System.IO.File.Copy(strFileName & ".xls", destination
MessageBox.Show("Excel Sheet saved successfully",&_
mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
MessageBox.Show("Report Is Not Saved, Operation Canceled By User",&_
mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
End If


Anonymous said...

string file = Server.MapPath("New.xls");
string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+file+";Extended Properties=Excel 8.0;";
string query = "Select * from [New$]";

DataSet DSExcel = new DataSet();
using (OleDbConnection Conn = new OleDbConnection(constr))
using (OleDbDataAdapter DA = new OleDbDataAdapter(query, Conn))
DA.Fill(DSExcel, "Info"); -----Am getting error here as external table is not in the expected format.
DA.AcceptChangesDuringFill = false;

Please Tell

Lakhan Pal Garg said...

Can you Please Send me the Excel file and code on lakhangarg@gmail.com so that i can resolve your issue and Post the solution here.