Tuesday, September 14, 2010

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


7 comments:

  1. Thanks for this!

    One error though in opening xlsx-file produced here.

    See http://blogs.msdn.com/b/mattm/archive/2010/04/05/exporting-to-excel-2007-xslx-vs-xslb.aspx

    "Excel cannot open the file ‘xxx.xlsx’ because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file"

    To solve this, use:
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\path\xxx.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES"

    ReplyDelete
  2. That actually is not an error, only a newer provider that supports the new xlsx extension.

    Thank you for your contribution.

    ReplyDelete
  3. Nice code !
    However, all numbers are written to Excel as string and not as number, is there a way to change this behavious to respect number format ?

    Thanks
    Sylvin

    ReplyDelete
  4. In fact, double type in not defined in the ClassDataTypes, I have added it and convert to number excel type, but no success so far

    ReplyDelete
  5. Any news on the number conversion?

    ReplyDelete
  6. How we do this in a ntier application

    ReplyDelete
    Replies
    1. Hard to answer without knowing your requirements and topology.
      In general you could import the file from a network file storage where the application server and users can access or copy/transfer/upload it to such place before importing it. Another option could be to import it to a dataset in the clientside and send it as a dataset to the server.

      Delete