private DataTable ReadPaymentsFromFile(Mapper mapper)
{
string fileName = Path.GetFileName(txtExcelFilename.Text);
DataTable dt = new DataTable();
// check for Excel office version -- old versions
if (fileName.ToLower().EndsWith(".xls"))
{
FileStream stream = new FileStream(txtExcelFilename.Text, FileMode.Open);
ExcelDataReader excelReader
= new ExcelDataReader(stream);
DataSet ds = excelReader.GetDataSet();
dt = ds.Tables[0];
//this stores the mapped column names to the column names in Excel
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
if (mapper.ContainsKey(ds.Tables[0].Rows[0][i].ToString()))
{
dt.Columns[i].ColumnName = (string)ds.Tables[0].Rows[0][i];
}
}
stream.Close();
}
else
{
// Office 2007 file parser
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + txtExcelFilename.Text + ";" +
"Extended Properties=\"Excel 12.0;HDR=YES;\"";
// if you don't want to show the header row (first row)
// use 'HDR=NO' in the string
string oleSql = "SELECT * FROM [{0}]";
DataSet ds = new DataSet();
using (OleDbConnection oleCnn = new OleDbConnection(connectionString))
{
oleCnn.Open();
DataTable schema = oleCnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheet1Name = schema.Rows[0]["TABLE_NAME"].ToString();
oleSql = String.Format(oleSql, sheet1Name);
using (OleDbDataAdapter oleDA = new OleDbDataAdapter(oleSql, oleCnn))
{
oleDA.Fill(ds);
}
}
dt = ds.Tables[0];
}
return dt;
}