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