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