Import/Export DataSet to Excel file with ADO.NET

Often it is necessary to Import/Export DataSet to external databases or external file. Here I will present how Import/Export DataSet to excel file (*.xls or *.xlsx) with ADO.NET.

The most significant advantage of this method is that excel installation is not necessary on running station.

You will first need a connection string to connect to the Excel Workbook, which would be the following:

Excel 8 (Excel 1997):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=YES";

Excel 12 (Excel 2007):

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\MyExcel.xlsx;Extended Properties="Excel 12.0;HDR=YES";

Implementation of Import:

public static DataSet ImportFromExcel(string connectionString, string fileName)

{

DataSet returnDS = new DataSet();

string excelConnectionString = string.Format(connectionString, fileName);

string[] excelSheets = getExcelSheetNames(excelConnectionString);

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

DbDataAdapter adapter = factory.CreateDataAdapter();

using (DbConnection connection = factory.CreateConnection())

{

connection.ConnectionString = excelConnectionString;

foreach (string excelSheet in excelSheets)

{

DbCommand selectCommand = factory.CreateCommand();

selectCommand.CommandText = string.Format("SELECT * FROM [{0}]", excelSheet);

selectCommand.Connection = connection;

adapter.SelectCommand = selectCommand;

DataTable dt = new DataTable(excelSheet.Remove(excelSheet.Length - 1, 1));

adapter.Fill(dt);

returnDS.Tables.Add(dt);

}

}

return returnDS;

}

Implementation of Export:

public static void ExportToExcel(DataSet dataSet, string connectionString, string fileName)

{

if (dataSet != null && dataSet.Tables.Count > 0)

{

using (OleDbConnection connection = new OleDbConnection(string.Format(connectionString, fileName)))

{

OleDbCommand command = null;

connection.Open();

foreach (DataTable dt in dataSet.Tables)

{

command = new OleDbCommand(getCreateTableCommand(dt), connection);

command.ExecuteNonQuery();

for (int rowIndex = 0; rowIndex <>

{

command = new OleDbCommand(getInsertCommand(dt, rowIndex), connection);

command.ExecuteNonQuery();

}

}

}

}

}

How to use:

Lets define 2 variables:

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES""";

string fileName = @"C:\MyExcel.xls";

Import:

DataSet ds = ImportFromExcel(connectionString, fileName);

Export:

ExportToExcel(ds, connectionString, fileName);

Source Code: ExcelReaderWriter.cs