private void AddFileFieldControl(string field) { Label lblField = new Label {Width = 120, Text = field}; ComboBox cbo = new ComboBox {Width = 140}; StringCollection propertyList = new Mapper().GetPaymentProperties(); //add payment properties to second list foreach (string str in propertyList) { cbo.Items.Add(str); if (field.ToLower() == str.ToLower()) { cbo.SelectedItem = str; } } flowLayoutPanel1.Controls.Add(lblField); flowLayoutPanel1.Controls.Add(cbo); flowLayoutPanel1.SetFlowBreak(cbo, true); }
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; }
private void LogMappingResults(Mapper mapper) { //displays the mapping results StringBuilder sb = new StringBuilder(); sb.AppendLine("Mapping Results:"); foreach (DictionaryEntry de in mapper) { sb.AppendFormat("Excel Column: {0}, Payment Property: {1};", de.Key, de.Value); sb.AppendLine(); } _log.WriteLog(sb.ToString()); _log.WriteLog(""); }
private Mapper CreateMapperObject() { Mapper mapper = new Mapper(); string labelValue = string.Empty; string comboValue = string.Empty; foreach (Control ctl in flowLayoutPanel1.Controls) { Type ctlType = ctl.GetType(); if (ctlType == typeof(Label)) { labelValue = ctl.Text; } else if (ctlType == typeof(ComboBox)) { comboValue = ctl.Text; } if (labelValue != string.Empty && comboValue != string.Empty && comboValue != string.Empty) { mapper.Add(labelValue, comboValue); labelValue = string.Empty; comboValue = string.Empty; } } return mapper; }
/// <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; } } }
/// <summary> /// Adds Payments to Payments list; Verifies payments; Identifies Claims; and Identifies Transaction Types /// </summary> /// <param name="table"></param> /// <param name="mapper"></param> /// <param name="deleteFirstRow"></param> public void ProcessPayments(DataTable table, Mapper mapper, bool deleteFirstRow) { AddPayments(table, mapper, deleteFirstRow); VerifyPayments(); IdentifyClaims(); IdentifyTransactionTypes(); }