Fast Exporting from DataSet to Excel

Private Shared Sub ReleaseComObjects(ByVal objList As List(Of Object))
	'GC.Collect()
	'GC.WaitForPendingFinalizers()
	For Each obj In objList
		If obj Is Nothing Then Continue For
		Try
			Select Case True
				Case TypeOf obj Is Microsoft.Office.Interop.Excel.Workbook
					obj.Close()
				Case TypeOf obj Is Microsoft.Office.Interop.Excel.ApplicationClass
					obj.Quit()
			End Select
		Catch
		End Try
		Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
		'obj = Nothing
	Next
	'GC.Collect()
	'GC.WaitForPendingFinalizers()
End Sub
 

Public Sub InteropExportDatasetToExcel(ByVal dataSet As DataSet, ByVal outputPath As String, Optional ByVal hideWorkbook As Boolean = False)
	Dim excelApp As Microsoft.Office.Interop.Excel.ApplicationClass = Nothing
	Dim excelWorkbooks As Microsoft.Office.Interop.Excel.Workbooks = Nothing
	Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook = Nothing
	Dim excelSheets As Microsoft.Office.Interop.Excel.Sheets = Nothing
 
	Const colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
	Dim finalColLetter As String = String.Empty
	Dim colCharsetLen As Integer = colCharset.Length
 
	Try
		Threading.Thread.CurrentThread.CurrentCulture = Globalization.CultureInfo.CreateSpecificCulture("en-US")
		excelApp = New Microsoft.Office.Interop.Excel.ApplicationClass
		excelApp.DisplayAlerts = False
		excelWorkbooks = excelApp.Workbooks
		excelWorkbook = excelWorkbooks.Add(Type.Missing)
		excelSheets = excelWorkbook.Sheets
		Dim sheetIndex As Integer = 0
		Dim col, row As Integer
 
		Dim excelSheetTmp As Object  '' As Microsoft.Office.Interop.Excel.Worksheet
		Dim excelSheet As Object     '' As Microsoft.Office.Interop.Excel.Worksheet
		Dim excelRange As Object     '' As Microsoft.Office.Interop.Excel.Range
		Dim excelRangeFont As Object '' As Microsoft.Office.Interop.Excel.Range
		Dim excelFont As Object      '' As Microsoft.Office.Interop.Excel.Font

		For Each dt As DataTable In dataSet.Tables
			sheetIndex += 1
			Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object
			For col = 0 To dt.Columns.Count - 1
				rawData(0, col) = dt.Columns(col).ColumnName
			Next
			For col = 0 To dt.Columns.Count - 1
				For row = 0 To dt.Rows.Count - 1
					rawData(row + 1, col) = dt.Rows(row).ItemArray(col)
				Next
			Next
			If dt.Columns.Count > colCharsetLen Then finalColLetter = colCharset.Substring((dt.Columns.Count - 1) \ colCharsetLen - 1, 1)
			finalColLetter += colCharset.Substring((dt.Columns.Count - 1) Mod colCharsetLen, 1)
			excelSheetTmp = excelSheets(sheetIndex)
			excelSheet = excelSheets.Add(excelSheetTmp, Type.Missing, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet)
			excelSheet.Name = dt.TableName
			excelRange = excelSheet.Range(String.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1), Type.Missing)
			excelRange.Value2 = rawData
			excelRangeFont = excelSheet.Rows(1, Type.Missing)
			excelFont = excelRangeFont.Font
			excelFont.Bold = True
			ReleaseComObjects(New List(Of Object) From {excelFont, excelRangeFont, excelRange, excelSheet, excelSheetTmp})
		Next
		excelWorkbook.SaveAs(outputPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
							 Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
	Catch ex As Exception
		ex.Data(0) = outputPath
		Throw
	Finally
		If Not hideWorkbook Then
			excelApp.Visible = True
			excelWorkbook.Activate()
			AddHandler excelApp.WorkbookBeforeClose, Sub()
														 Try
															 excelApp.Visible = False
															 ReleaseComObjects(New List(Of Object) From {excelSheets, excelWorkbooks, excelApp})
														 Catch ex As Exception
															 Throw
														 End Try
													 End Sub
		Else
			ReleaseComObjects(New List(Of Object) From {excelSheets, excelWorkbook, excelWorkbooks, excelApp})
		End If
	End Try
End Sub
 

var ef = new ExcelFile();
 
foreach (DataTable dataTable in dataSet.Tables)
    ef.Worksheets.Add(dataTable.TableName).InsertDataTable(dataTable, 0, 0, true);
 
ef.SaveXls(dataSet.DataSetName + ".xls");

excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;

Imports Microsoft.Office.Interop
 
Module DataCenterExport
 
    Friend Sub ExportDataSetToExcel(ByVal dataSet As DataSet, Optional ByVal outputPath As String = "", Optional ByVal ShowWorkBook As Boolean = True, Optional ByVal OutputFileFormat As Excel.XlFileFormat = Excel.XlFileFormat.xlWorkbookNormal)
        ' Create the Excel Application object
        Dim excelApp As New Excel.Application
 
        excelApp.DefaultSaveFormat = OutputFileFormat
        excelApp.DefaultFilePath = outputPath
 
        ' Create a new Excel Workbook
        Dim excelWorkbook As Excel.Workbook = excelApp.Workbooks.Add
 
        Dim sheetIndex As Integer = 0
        Dim col, row As Integer
        Dim excelSheet As Excel.Worksheet
 
        ' Copy each DataTable as a new Sheet
        For Each dt As System.Data.DataTable In dataSet.Tables
 
            sheetIndex += 1
 
            ' Copy the DataTable to an object array
            Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object
 
            ' Copy the column names to the first row of the object array
            For col = 0 To dt.Columns.Count - 1
                rawData(0, col) = dt.Columns(col).ColumnName
            Next
 
            ' Copy the values to the object array
            For col = 0 To dt.Columns.Count - 1
                For row = 0 To dt.Rows.Count - 1
                    rawData(row + 1, col) = dt.Rows(row).ItemArray(col)
                Next
            Next
 
            ' Calculate the final column letter
            Dim finalColLetter As String = String.Empty
            Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
            Dim colCharsetLen As Integer = colCharset.Length
 
            If dt.Columns.Count > colCharsetLen Then
                finalColLetter = colCharset.Substring((dt.Columns.Count - 1) \ colCharsetLen - 1, 1)
            End If
 
            finalColLetter += colCharset.Substring((dt.Columns.Count - 1) Mod colCharsetLen, 1)
 
            ' Create a new Sheet
            excelSheet = CType(excelWorkbook.Sheets.Add(excelWorkbook.Sheets(sheetIndex), Type.Missing, 1, Excel.XlSheetType.xlWorksheet), Excel.Worksheet)
 
            excelSheet.Name = dt.TableName & sheetIndex.ToString()
 
            ' Fast data export to Excel
            Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1)
            excelSheet.Range(excelRange, Type.Missing).Value = rawData
 
            ' Mark the first row as BOLD
            CType(excelSheet.Rows(1, Type.Missing), Excel.Range).Font.Bold = True
 
            excelSheet = Nothing
        Next
 
        'Delete extra sheets
        Dim sheetCount As Integer = excelWorkbook.Sheets.Count
        Dim sheetDeleteAt As Integer = sheetIndex + 1
        For x = sheetDeleteAt To sheetCount
            CType(excelWorkbook.Sheets(sheetDeleteAt), Excel.Worksheet).Delete()
        Next
 
        'Save it
        If Not String.IsNullOrEmpty(outputPath) Then
            Try
                excelWorkbook.SaveAs(outputPath, OutputFileFormat, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
            Catch ex As System.Exception
                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Excel File Save Failed")
            End Try
        End If
 
        'Show it or close it out
        If ShowWorkBook Then
            excelApp.Visible = True
            excelWorkbook.Activate()
            excelWorkbook = Nothing
            excelApp = Nothing
        Else
            excelWorkbook.Close(True, Type.Missing, Type.Missing)
            excelWorkbook = Nothing
            excelApp.Quit()
            excelApp = Nothing
        End If
 
        ' Collect the unreferenced objects
        GC.Collect()
        GC.WaitForPendingFinalizers()
 
    End Sub
 
End Module

using Microsoft.Office.Interop.Excel; 
using System; 
using System.Collections.Generic; 
using System.Data; 
using System.Text; 
 
namespace EXPORT_DATASET_EXCEL_II 
{ 
static class ExportingCellByCellMethod 
{ 
 
public static void ExportToExcel(DataSet dataSet, string outputPath) 
// public static void ExportToExcel(DataSet dataSet) 
{ 
 
// Create the Excel Application object 
ApplicationClass excelApp = new ApplicationClass(); 
//excelApp.Interactive = false; 
// Create a new Excel Workbook 
Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing); 
 
int sheetIndex = 0; 
 
// Copy each DataTable as a new Sheet 
foreach (System.Data.DataTable dt in dataSet.Tables) 
{ 
 
// Create a new Sheet 
Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add( 
excelWorkbook.Sheets.get_Item(++sheetIndex), 
Type.Missing, 1, XlSheetType.xlWorksheet); 
 
excelSheet.Name = dt.TableName; 
 

// Copy the column names (cell-by-cell) 
for (int col = 0; col < dt.Columns.Count; col++) 
{ 
excelSheet.Cells[1, col + 1] = dt.Columns[col].ColumnName; 
} 
 

 
// Copy the values (cell-by-cell) 
for (int col = 0; col < dt.Columns.Count; col++) 
{ 
for (int row = 0; row < dt.Rows.Count; row++) 
{ 
excelSheet.Cells[row + 2, col + 1] = 
dt.Rows[row].ItemArray[col]; 
((Range)excelSheet.Rows[1, 
Type.Missing]).AutoFit(); 
} 
 
((Range)excelSheet.Columns[1, 
Type.Missing]).AutoFit(); 
((Range)excelSheet.Columns[2, 
Type.Missing]).AutoFit(); 
((Range)excelSheet.Columns[3, 
Type.Missing]).AutoFit(); 
 
} 
 
} 
 

// Save and Close the Workbook 

excelWorkbook.SaveAs(outputPath, 
XlFileFormat.xlWorkbookNormal, Type.Missing, 
Type.Missing, Type.Missing, Type.Missing, 
XlSaveAsAccessMode.xlExclusive, 
Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
Type.Missing); 
excelWorkbook.Close(true, Type.Missing, Type.Missing); 
 

excelWorkbook = null; 
 
// Release the Application object 
excelApp.Quit(); 
excelApp = null; 
 
// Collect the unreferenced objects 
GC.Collect(); 
GC.WaitForPendingFinalizers(); 
 
} 
 
} 
 
} 
 

using System; 
using System.Collections.Generic; 
using System.ComponentModel; 
using System.Data; 
using System.Drawing; 
using System.Text; 
using System.Windows.Forms; 
using System.Data.OleDb; 
using System.Configuration; 
 
namespace EXPORT_DATASET_EXCEL_II 
{ 
public partial class Form1 : Form 
{ 
System.Data.OleDb.OleDbConnection conn; 
OleDbDataAdapter DA; 
string S; 
DataSet source; 
 
public Form1() 
{ 
InitializeComponent(); 
conn = new System.Data.OleDb.OleDbConnection(); 
conn.ConnectionString = 
ConfigurationManager.ConnectionStrings["MSAccess"].ToString(); 
} 
 
private DataSet getDemoDataSet() 
{ 
S = "Select Emp_ID as [ID], Emp_Name as NAME,Reason as REASON, 
Submitted_Date as [SUBMITTED DATE] from LEAVEMASTER_TABLE"; 
source = new DataSet(); 
DA = new OleDbDataAdapter(S, conn); 
DA.Fill(source); 
source.Tables[0].TableName = "sample"; 
return source; 
} 
 
private void button2_Click(object sender, EventArgs e) 
{ 
DataSet demoDataSet = this.getDemoDataSet(); 
string savepath; 
saveFileDialog1 = new SaveFileDialog(); 
savepath = saveFileDialog1.FileName + ".xls"; 
saveFileDialog1.Filter = "xls files (*.xls)|*.xls"; 
 
if (saveFileDialog1.ShowDialog() == 
System.Windows.Forms.DialogResult.OK 
&& saveFileDialog1.FileName.Length > 0) 
{ 
ExportingCellByCellMethod.ExportToExcel(demoDataSet, 
savepath); 
 
} 
 

MessageBox.Show("Data Exported successfully"); 
 

} 
} 
}