/// <summary> /// This function verify excel record before data update /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnVerify_Click(object sender, RoutedEventArgs e) { /*1. Check Scientific Name.*/ /* Helper class to extract data from data grid cell. */ DataGridCellHelper cell_helper = new DataGridCellHelper(dtGrid); int col = 2; // Column that store Scientific Name for (int row = 0; row < dtGrid.Items.Count; row++) { DataGridCell cell = cell_helper.GetCell(row, col); TextBlock text = cell.Content as TextBlock; string species = text.Text; string id, name; (id, name) = SpeciesDataHelper.CheckScientificName(species); //Debug.WriteLine($"row {row + 1}:{text.Text} => {name}"); /* Get the status data grid cell field */ cell = cell_helper.GetCell(row, dtGrid.Columns.Count - 1); ((TextBlock)cell.Content).Text = (id == "-1") ? id : name; } col = 4; // column for Size for (int row = 0; row < dtGrid.Items.Count; row++) { DataGridCell cell = cell_helper.GetCell(row, col); TextBlock text = cell.Content as TextBlock; /* Get the status data grid cell field */ cell = cell_helper.GetCell(row, dtGrid.Columns.Count - 1); ((TextBlock)cell.Content).Text = text.Text; } }
private void ExtractExcelData(int sheet_index) { /* Extract Excel File */ /* create a new datatable*/ DataTable dt = new DataTable(); using (var stream = File.Open(file_fullpath, FileMode.Open, FileAccess.Read)) { /* store the header columns */ List <string> header_columns = new List <string>(); // Auto-detect format, supports: // - Binary Excel files (2.0-2003 format; *.xls) // - OpenXml Excel files (2007 format; *.xlsx) using (var reader = ExcelReaderFactory.CreateReader(stream)) { DataSet result = reader.AsDataSet(); DataTable excel_dt = result.Tables[sheet_index]; /* get total row and total columns. */ int rowCount = excel_dt.Rows.Count; int colCount = excel_dt.Columns.Count; int sheetCount = reader.ResultsCount; /* Debug: Information about the excel spreadsheet */ Debug.WriteLine($"Total Number of Sheet: {sheetCount}"); Debug.WriteLine($"rows : { rowCount } coulumns : { colCount }"); #region Retrieve excel column header /* Retreive the Column Heading information*/ int emptyCell = 0; //int start_row; int start_row = 0; string strCellData, strData = ""; /* Quick - Fix !!!! */ bool CheckHeaderText(string text) { string[] header_text = { "code", "name", "vietnamese", "size", "price", "total", "tank", "Description", "qty", "price", "amount" }; bool flag = false; if (text.Trim() == string.Empty) { return(flag); } for (int n = 0; n < header_text.Length; n++) { //flag = header_text[n].Contains(text.ToLower()); flag = text.ToLower().Contains(header_text[n]); if (flag) { break; } } return(flag); } int cellCount = 0; for (start_row = 1; start_row < rowCount; start_row++) { cellCount = 0; strData = ""; for (int col = 0; col < colCount; col++) { object value = excel_dt.Rows[start_row][col]; //object value = temp.Value; if (value == null) { strCellData = ""; } else { strCellData = value.ToString().Trim(); } if (strCellData != "" && CheckHeaderText(strCellData)) //emptyCell++; { cellCount++; } strData += strCellData + "|"; } if (cellCount > 5) { break; } //Debug.WriteLine($"row {start_row} : {strData}"); } #endregion //strData = "Tank ID" + "|" + strData; Debug.WriteLine($"Header String {strData}"); #region (NOT USING) Displays all Excel header columns //string[] columns = strData.Split('|'); ////int cellCount = 0; ////cellCount = 0; //for(int i = 0; i < columns.Length; i++) //{ // string text = columns[i]; // if(text != string.Empty) // { // dt.Columns.Add(text, typeof(string)); // //cellCount++; // } // else // { // if(i == cellCount) // break; // else // { // dt.Columns.Add("Col" + i, typeof(string)); // } // } //} #endregion /* Create a 'standard' header column*/ string strColumnHeader = "TANK_FK|SPECIES_TEXT_2|SPECIES_FK|COMMON|SIZE_FK|QTY"; dt.Columns.Add("Tank", typeof(string)); // TANK_FK dt.Columns.Add("Code", typeof(string)); // SPECIES_TEXT_2 dt.Columns.Add("Scientific", typeof(string)); // SPECIES_FK dt.Columns.Add("Common", typeof(string)); // SPECIES_TEXT dt.Columns.Add("Size", typeof(string)); // SIZE dt.Columns.Add("Quantity", typeof(string)); // QTY dt.Columns.Add("Result", typeof(string)); // ==> for validate data status #region Link excel column to table column /* process the strData to link the excel column to table column */ /* retreive the table column name */ string[] strTableColumn = strColumnHeader.Split('|'); string scientific_field = "description vietnamese latin name "; string[] strMatchingText = { "tank", "code", scientific_field, "", // process Common Name when dealing with Scientific Name "size", "qty tot'pc" }; string[] strColumnName = strData.Split('|'); string[] strScientific = scientific_field.Split(' '); List <int> excel_col_index = new List <int>(); /* */ int table_col_index = 0; string table_col = ""; strData = ""; for (int col = 0; col < colCount; col++) { string text = strColumnName[col].ToLower(); string matching_text = strMatchingText[table_col_index]; //bool col_found = false; bool IsMatchingText() { string[] subText = text.Split(' '); for (int sub_index = 0; sub_index < subText.Length; sub_index++) { if (matching_text.Contains(subText[sub_index])) { return(true); } } return(false); } /*If there is match up between excel column and table column, * do the following and proceed to the next table column */ if (text != "" && matching_text != "" && IsMatchingText()) //text.Contains(matching_text)) { /* save the actual column (index) */ excel_col_index.Add(col); strData += text + "|"; switch (table_col_index) { case 0: case 1: case 4: case 5: break; /* Handle Scientific and Common Name together */ case 2: if (text.Contains(strScientific[0])) { excel_col_index.Add(col + 1); } else if (text.Contains(strScientific[1])) { excel_col_index.Add(col - 1); } else { excel_col_index.Add(col); } strData += text + "|"; table_col_index++; break; //case 3: break; default: throw new Exception($"Error : table index {table_col_index} not defined !!!"); } table_col_index++; } /* Terminate the loop once processed sthe last table column*/ if (table_col_index == strMatchingText.Length) { break; } } Debug.WriteLine($"Matching Column:{strData}"); #endregion /* Need to format Scientific Name before validating*/ // Debug Debug.WriteLine(""); #region Display excel records onto screen (data grid) bool addData = false; for (int row = start_row + 1; row < rowCount; row++) { strData = ""; object value = excel_dt.Rows[row][0]; strCellData = value.ToString(); /* Remove any row that has no tank number allocated to it. * Also remove invalid record ..... */ if (strCellData == string.Empty) { continue; } for (int i = 0; i < excel_col_index.Count; i++) { int col = excel_col_index[i]; value = excel_dt.Rows[row][col]; strCellData = value.ToString(); switch (i) { case 2: /* Foremat Scientific Name */ string temp = value.ToString(); string[] scientific = temp.Split(' '); strCellData = scientific[0] + (scientific.Length > 1 ? ' ' + scientific[1]: ""); break; default: strCellData = value.ToString(); break; } strData += (strData != "" ? "|" : "") + strCellData; // } // Debug //Debug.WriteLine($"{row}:{strData}"); /* The following line is print a dump and save as text file and export into excel as csv file.*/ Debug.WriteLine($"{strData}"); #region Format data such that each date row (i.e strData) will ONLY contains ONE tank ID /* Format the strData due to following user way to entering the tank id : * 1. Sometime user just key in 1A01, etc instead of 01A1, which we has agreed to. * ==> the program need to format the tank id as NNAN format. * 2. Sometime user combine a few tank id into a single spreadsheet line. * ==> the progarm need to break into record with just one tank id * and complete with the rest of the excel row information. * using delimitor as ',' or '-' */ // Debug string[] strDataCell = strData.Split('|'); string FormatTankID(string[] data) { string text = data[0]; // Tank ID feild int len = text.Length; if (len < 4) { text = "".PadRight(4 - len, '0') + text; } return(text); } if (strDataCell[0].Contains(",") || strDataCell[0].Contains("-")) { char delimiter; if (strDataCell[0].Contains(",")) { delimiter = ','; } else if (strDataCell[0].Contains("-")) { delimiter = '-'; } else { // Place an error catcher delimiter = ' '; } if (delimiter != ' ') { string[] data = strDataCell[0].Split(delimiter); string prev_text = ""; switch (delimiter) { case ',': for (int i = 0; i < data.Length; i++) { if (prev_text != "" && data[i].Length == 1) { int len = prev_text.Length - 1; strDataCell[0] = prev_text.Substring(0, len) + data[i]; } else { strDataCell[0] = data[i]; prev_text = data[i]; } /* Format Tank ID */ strDataCell[0] = FormatTankID(strDataCell); dt.Rows.Add(strDataCell); } break; case '-': if (data.Length == 2) { int start, end; prev_text = data[0]; int len = prev_text.Length - 1; /* Assumption is that start and end range is between 1 to 9 */ start = int.Parse(prev_text.Substring(len)); end = int.Parse(data[1]); for (int i = start; i <= end; i++) { strDataCell[0] = prev_text.Substring(0, len) + i; /* Format Tank ID */ strDataCell[0] = FormatTankID(strDataCell); dt.Rows.Add(strDataCell); } } else { throw new Exception($"Error : Delimiter {delimiter} throw Error !!!\n {strDataCell[0]}"); } break; default: throw new Exception($"Error : Delimiter {delimiter} not defined !!!"); } //Debug.WriteLine($"{strData}"); } } else { /* */ /* Format Tank ID */ strDataCell[0] = FormatTankID(strDataCell); dt.Rows.Add(strDataCell); //dt.Rows.Add(strData.Split('|')); } #endregion } // Debug Debug.WriteLine(""); #endregion } } /* update the data grid */ dtGrid.ItemsSource = dt.DefaultView; #region Format Size Field /* try to standardize the size format. */ /* Helper class to extract data from data grid cell. */ DataGridCellHelper cell_helper = new DataGridCellHelper(dtGrid); { int col = 4; // column for Size for (int row = 0; row < dtGrid.Items.Count; row++) { DataGridCell cell = cell_helper.GetCell(row, col); TextBlock text = cell.Content as TextBlock; string size = text.Text; if (size.Contains(",")) { /* Format some error like 2,5" should be 2.5"*/ size = size.Replace(',', '.'); } else if (size.Trim() == string.Empty) { /* use '-' as N/A value */ size = "-"; } /* Get the status data grid cell field */ cell = cell_helper.GetCell(row, col); ((TextBlock)cell.Content).Text = size.Trim(); } } #endregion }