public override void ProcessWorksheet(Range range) { StringBuilder line; string text; int pad; string[] ignored_words = { "Scientific Name", "African fish:" }; string[] delimitors = { "/", "-" }; for (int row = 1; row < rowCount; row++) { line = new StringBuilder(); for (int column = 1; column < 4; column++) // colCount; column++) { if (range.Cells[row, column] != null && range.Cells[row, column].Value != null) { text = range.Cells[row, column].Value.ToString().Trim(); //Console.Write(range.Cells[row, column].Value.ToString() + "|"); } else { text = string.Empty; //Console.Write("\t\t|"); } line.Append(text + "|"); } string[] str = line.ToString().Split('|'); string scientific_name; if (str[2].Trim() != string.Empty) { //string[] temp_text = str[2].Split('/'); //string[] temp_text = str[2].Split(' '); //if (CheckForWord(str[2], delimitors) ==true) //{ // string temp = str[2].Trim(); // foreach (var ch in delimitors) // { // if(delimitors.Contains(ch)) // { // temp_text = temp.Split(ch[0]); // temp = temp.Replace(ch[0], ' ').Trim(); // } // } //} //string[] split_text = temp_text[0].Split(' '); string[] split_text = str[2].Split(' '); scientific_name = split_text[0] + (split_text.Length > 1 ? " " + split_text[1] : ""); } else { scientific_name = str[2].Trim(); } //pad = 100 - ((scientific_name != string.Empty) ? scientific_name.Length:0) - line.Length; pad = 50 - line.Length; if (scientific_name != string.Empty && CheckForWord(scientific_name.Trim(), ignored_words) != true) { Console.Write($"[{(row).ToString().PadLeft(3, '0')}]{line}" + "".PadRight(pad, ' ')); //string scientific_name = split_text[0] + " " + split_text[1]; int record_id = SpeciesDataHelper.GetIDByScientificName(scientific_name); if (record_id > 0) { Console.Write($"[S] {SpeciesDataHelper.GetScientificName(record_id)}"); } else { Console.Write("\t\t* RECORD NOT FOUND !!! *"); } } else { Console.Write($"[{(row).ToString().PadLeft(3, '0')}]"); } //Console.Write($"[{(row).ToString().PadLeft(3, '0')}]{scientific_name}"); Console.Write("\n"); // Debug purpose if (row == 200) { break; } } }
public override void ProcessWorksheet(Range range) { string[] group_list = { "DISCUS", "LOACH", "PUFFERS", "INVERTEBRATES", "Wild FISH", "CATFISH", "BARBS", "ORTHER FISHES", "TETRAS", "GOURAMI", "GUPPIES", "PLATIES", "SWORDtailS", "MOLLIES", "CICHLIDS", "ANGELS", "GOLD FISH" }; StringBuilder line; List <string> size = new List <string>(); List <string> group = new List <string>(); string text; //int count = 0; int len02 = 1; int len03 = 1; string group_name = string.Empty; string common_name = string.Empty; string scientific_name = string.Empty; string description = string.Empty; int pet_size_id = -1; int group_id = -1; for (int row = 1; row < rowCount; row++) { line = new StringBuilder(); for (int column = 1; column < 10; column++) // colCount; column++) { if (range.Cells[row, column] != null && range.Cells[row, column].Value != null) { text = range.Cells[row, column].Value.ToString().Trim(); //Console.Write(range.Cells[row, column].Value.ToString() + "|"); } else { text = string.Empty; //Console.Write("\t\t|"); } switch (column) { /* Code field*/ case 1: string temp = text; text = temp.PadRight(10, ' '); break; case 2: if (text.Length > len02) { len02 = text.Length; } temp = text; //text = temp.PadRight(len02, ' '); break; case 3: if (text.Length > len03) { len03 = text.Length; } temp = text; //text = temp.PadRight(len03, ' '); break; case 5: //bool not_found = size.Contains(text.Trim()) ? false : true ; if (text.Trim() != string.Empty && text.Trim() != "Size") { if (size.Count == 0 || CheckForWord(text.Trim(), size.ToArray()) != true) { size.Add(text.Trim()); pet_size_id = ProcessRECORD_PET_SIZE(text.Trim()); } } break; default: break; } if (text.Trim() != string.Empty && column != 1 && column < 4) { line.Append(text + " |"); } else { line.Append(text + "|"); } } //Console.Write($"[{(row).ToString().PadLeft(3, '0')}]{line}"); string[] str = line.ToString().Split('|'); //Console.Write(str[0] +"|"+str[1]+"|"+str[2] +"|"); Console.Write(str[0] + "|"); /* Extract the Group Information and pre-format it*/ //group_name = (str.Length > 2) ? str[1].Trim() : ""; if (str[0].Trim() == string.Empty) { group_name = (str.Length > 2) ? str[1].Trim() : ""; if (group_name != string.Empty) { group_name = group_name[0] + group_name.Substring(1).ToLower(); /* convert the group name from pural to singular */ if (group_name.Contains("ies")) { group_name = group_name.Substring(0, group_name.Length - "ies".Length) + "y"; } /* only add the group name ONCE to the list*/ if (group.Contains(group_name) != true) { group.Add(group_name); group_id = ProcessRECORD_GROUP(group_name); //Console.Write(group_name); } } } /* ignore line contains the following words */ else if (str[1].Trim() == "Scientific Name") { } /* extract scientific name */ else if (str[1].Trim() != string.Empty) { //string code = str[0].Trim(); RECORD_PET data = new RECORD_PET(); data.CODE = str[0].Trim(); data.GROUP_FK = group_id; data.SIZE_FK = pet_size_id; string[] split_text = str[1].Split(' '); /* Only use the first 2 words as the scientfic name */ scientific_name = split_text[0] + " " + split_text[1]; int record_id = SpeciesDataHelper.GetIDByScientificName(scientific_name); data.SPECIES_FK = record_id; if (record_id > 0) /* if record found */ { Console.Write("Record Found|"); description = str[1].Substring(scientific_name.Length).Trim(); if (description != string.Empty) { Console.Write($" *{group_name} " + description); data.DESCRIPTION = group_name + " " + description; } else { //Console.Write($" ={scientific_name}"); description = str[2].Trim(); if (description != string.Empty) { Console.Write(" =" + description); data.DESCRIPTION = description; } else { data.DESCRIPTION = string.Empty; } } } else { Console.Write(" Not Found! |"); description = str[2].Trim(); if (description != string.Empty) { Console.Write(" +" + description); data.DESCRIPTION = description; } else { data.DESCRIPTION = string.Empty; } } processRECORD_PET(data); } Console.Write("\n"); // Debug if (row == 685) { break; } //if (row == 200) break; } Console.WriteLine(); Console.WriteLine("Group :"); if (group.Count > 0) { for (int i = 0; i < group.Count; i++) { Console.WriteLine($" {(i + 1).ToString().PadLeft(2, '0')} [" + group[i] + "]"); } } Console.WriteLine(); Console.WriteLine(); Console.WriteLine("Size :"); if (size.Count > 0) { for (int i = 0; i < size.Count; i++) { Console.WriteLine($" {(i+1).ToString().PadLeft(2,'0')} [" + size[i] + "]"); } } Console.WriteLine(); }
public override void ProcessWorksheet(Range range) { StringBuilder line; string text; string[] ignored_words = { "Latin Name" }; for (int row = 1; row < rowCount; row++) { line = new StringBuilder(); for (int column = 1; column < 4; column++) // colCount; column++) { if (range.Cells[row, column] != null && range.Cells[row, column].Value != null) { text = range.Cells[row, column].Value.ToString().Trim(); //Console.Write(range.Cells[row, column].Value.ToString() + "|"); } else { text = string.Empty; //Console.Write("\t\t|"); } switch (column) { case 3: int pad = 25 - text.Length; text += "".PadRight(pad, ' '); break; default: break; } line.Append(text + "|"); } string[] str = line.ToString().Split('|'); string scientific_name; if (str[2].Trim() != string.Empty) { string[] split_text = str[2].Split(' '); scientific_name = split_text[0] + (split_text.Length > 1 ? " " + split_text[1] : ""); } else { scientific_name = str[2].Trim(); } if (scientific_name != string.Empty && CheckForWord(scientific_name, ignored_words) != true) { Console.Write($"[{(row).ToString().PadLeft(3, '0')}]{line}"); //string scientific_name = split_text[0] + " " + split_text[1]; int record_id = SpeciesDataHelper.GetIDByScientificName(scientific_name); if (record_id > 0) { Console.Write($"[S] {SpeciesDataHelper.GetScientificName(record_id)}"); } else { Console.Write("\t\t* RECORD NOT FOUND !!! *"); } } else { Console.Write($"[{(row).ToString().PadLeft(3, '0')}]"); } Console.Write("\n"); // Debug purpose if (row == 520) { break; } } }