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;
}
/// <summary>
/// Adds a list of payments to the BulkPayment object
/// </summary>
/// <param name="dt"></param>
/// <param name="mapper"></param>
/// <param name="deleteFirstRow"></param>
private void AddPayments(DataTable dt, Mapper mapper, bool deleteFirstRow)
{
//add each payment to bulkPayment object
if (deleteFirstRow)
{
//Deletes the first row with the column names
dt.Rows[0].Delete();
}
//stores the data in the first sheet to the bulkpayment.Payments collection
foreach (DataRow row in dt.Rows)
{
IPayment payment = AddPayment();
try
{
foreach (DataColumn column in dt.Columns)
{
if (mapper.ContainsKey(column.ColumnName))
{payment.SetProperty(mapper[column.ColumnName], row[column].ToString());}
}
}
catch
{
//MessageBox.Show(ex.Message);
RemovePayment(payment);
//add to Dropped Payments
_droppedPayments.Add(payment);
_sumDroppedPayments += payment.Amount;
_countDroppedPayments += 1;
}
}
}
