Fast Exporting from DataSet to Excel

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"); 


} 
} 
} 

excelSheet.get_Range("A1:H25", Type.Missing).Value2 = 
    bidimensionalObjectArray;

excelSheet.get_Range("A1:H25", Type.Missing).set_Value(Type.Missing, bidimensionalObjectArray);