How to use:
ExcelFileReader reader =
new ExcelFileReader(Path.Combine(importDirectory, importFilename), true);
Then just say, reader.GetExcelAsDataSet() to get the dataset out.
I have added necessary comments, otherwise
using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.IO;
using Microsoft.Win32;
namespace Import.ImportCommon
{
/// <summary>
/// Summary description for ExcelFileReader.
/// </summary>
public class ExcelFileReader
{
private string _fileName;
// NOTE: May need to support Jet 3.5 for Win2000 and NT.
private static string EXCEL_DRIVER =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
private string _excelConnectionString =
";Extended Properties=\"Excel 8.0;HDR={0};IMEX=1\"";
private bool _firstRowIsHeaders =
false;
/// <summary>
/// Initializes a new instance of the <see cref="ExcelFileReader"/>
class.
/// </summary>
/// <param name="fileName">Name of the file.</param>
/// <param name="firstRowIsHeaders">if set to <c>true</c>
[first row is headers].</param>
public ExcelFileReader(
string fileName,
bool firstRowIsHeaders)
{
EnableMixedDataSuppport();
FirstRowIsHeaders
= firstRowIsHeaders;
FileName
= fileName;
if (!File.Exists(fileName))
{
throw new ApplicationException(
string.Concat(
"Cannot locate Excel file: ", fileName));
}
if (!fileName.ToLower().EndsWith(
@".xls"))
{
throw new ApplicationException(
string.Concat(
"Excel file must end in .xls: ", fileName));
}
}
/// <summary>
/// Enables the mixed data suppport.
/// </summary>
private void EnableMixedDataSuppport()
{
//The Jet driver's default behavior is to truncate Excel cell's content
//according to a setting in the registry. This resets that entry to allow
//mixed data in a column by default.
RegistryKey
regKey =
null;
try
{
regKey
= Registry.LocalMachine.OpenSubKey(
@"Software\Microsoft\Jet\4.0\Engines\Excel",
true);
if (regKey !=
null)
{
int guess =
int.Parse(regKey.GetValue(
"TypeGuessRows", 0).ToString());
if (guess != 0)
regKey.SetValue(
"TypeGuessRows", 0);
// make mixed types text
string mix = regKey.GetValue(
"ImportMixedTypes",
"Text").ToString();
if (!mix.Equals(
"Text"))
regKey.SetValue(
"ImportMixedTypes",
"Text");
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine(
"Unable to set Jet registry settings for Excel import: " + ex.Message);
}
finally
{
if (regKey !=
null)
{
regKey.Close();
}
}
}
/// <summary>
/// Gets the excel sheet as data set.
/// </summary>
/// <returns></returns>
public DataSet GetExcelSheetAsDataSet()
{
return GetExcelSheetAsDataSet(
true,
string.Empty);
}
/// <summary>
/// Gets the excel sheet as data set.
/// </summary>
/// <param name="sheetToRead">The sheet to read.</param>
/// <returns></returns>
public DataSet GetExcelSheetAsDataSet(
string sheetToRead)
{
return GetExcelSheetAsDataSet(
false, sheetToRead);
}
/// <summary>
/// Gets the excel sheet as data set.
/// </summary>
/// <param name="readFirstSheetOnly">if set to <c>true</c>
[read first sheet only].</param>
/// <param name="sheetToRead">The sheet to read.</param>
/// <returns></returns>
public DataSet GetExcelSheetAsDataSet(
bool readFirstSheetOnly,
string sheetToRead)
{
string select =
string.Empty;
ArrayList
sheetNames = GetExcelSheetNames();
if (readFirstSheetOnly)
{
select
=
string.Concat(
"select * from [", sheetNames[0],
"]");
}
else if (!sheetToRead.Equals(
string.Empty))
{
select
=
string.Concat(
"select * from [", sheetToRead,
"]");
}
OleDbConnection
con =
null;
DataSet
dataSet =
null;
try
{
con
=
new OleDbConnection(GetConnectionString());
OleDbDataAdapter
da =
new OleDbDataAdapter(select, con);
dataSet
=
new DataSet(
"ExcelTables");
DataTable
dt =
new DataTable();
da.Fill(dt);
dataSet.Tables.Add(dt);
dataSet.AcceptChanges();
}
finally
{
if (con !=
null)
{
con.Close();
}
}
return dataSet;
}
/// <summary>
/// Gets the excel as data set.
/// </summary>
/// <returns></returns>
public DataSet GetExcelAsDataSet()
{
DataSet
dataSet =
new DataSet(
"ExcelTables");
string select =
string.Empty;
ArrayList
sheetNames = GetExcelSheetNames();
OleDbConnection
con =
null;
try
{
con
=
new OleDbConnection(GetConnectionString());
foreach (
object sheetName
in sheetNames)
{
select
=
string.Concat(
"select * from [", sheetName.ToString(),
"]");
OleDbDataAdapter
adapter =
new OleDbDataAdapter(select, con);
DataTable
table =
new DataTable(sheetName.ToString());
adapter.Fill(table);
dataSet.Tables.Add(table);
}
dataSet.AcceptChanges();
}
finally
{
if (con !=
null)
{
con.Close();
}
}
return dataSet;
}
/// <summary>
/// Gets the excel sheet names.
/// </summary>
/// <returns></returns>
public ArrayList GetExcelSheetNames()
{
OleDbConnection
objConn =
null;
DataTable
dt =
null;
try
{
objConn
=
new OleDbConnection(GetConnectionString());
objConn.Open();
dt
= objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null);
if (dt ==
null)
{
return null;
}
ArrayList
excelSheets =
new ArrayList();
foreach (DataRow row
in dt.Rows)
{
excelSheets.Add(row[
"TABLE_NAME"].ToString());
}
return excelSheets;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objConn !=
null)
{
objConn.Close();
objConn.Dispose();
}
if (dt !=
null)
{
dt.Dispose();
}
}
}
/// <summary>
/// Gets or sets the name of the file.
/// </summary>
/// <value>The name of the file.</value>
public string FileName
{
get
{
return _fileName; }
set
{ _fileName =
value; }
}
/// <summary>
/// Gets the excel connection string.
/// </summary>
/// <value>The excel connection string.</value>
private string ExcelConnectionString
{
get
{
string flag = FirstRowIsHeaders ?
"Yes" :
"No";
return string.Format(_excelConnectionString, flag);
}
}
/// <summary>
/// Gets or sets a value indicating whether [first row is headers].
/// </summary>
/// <value><c>true</c> if [first row is headers]; otherwise, <c>false</c>.</value>
private bool FirstRowIsHeaders
{
get
{
return _firstRowIsHeaders; }
set
{ _firstRowIsHeaders =
value; }
}
/// <summary>
/// Gets the connection string.
/// </summary>
/// <returns></returns>
private string GetConnectionString()
{
return string.Concat(EXCEL_DRIVER, FileName, ExcelConnectionString);
}
}
}