private void btnValidate_Click(object sender, RoutedEventArgs e)
{
btnUpLoad.IsEnabled = false;
DataGridCellHelper cell_helper = new DataGridCellHelper(dtGrid);
for (int row = 0; row < dtGrid.Items.Count; row++)
{
//for(int column = 0; column < dtGrid.Columns.Count; column++)
//{
// DataGridCell cell = GetCell(row, column);
//}
DataGridCell cell = cell_helper.GetCell(row, 1);
TextBlock text = cell.Content as TextBlock;
string[] array = text.Text.Split(' ');
string species = array[0] + ' ' + array[1];
Debug.WriteLine(species);
//dtGrid.Items[row].Cells[dtGrid.Columns.Count-1].Text = SpeciesDataHelper.GetIDByScientificName(species).ToString();
string id, name;
(id, name) = SpeciesDataHelper.CheckScientificName(species);
//cell = GetCell(row, dtGrid.Columns.Count - 1);
//cell = GetCell(row, 1);
if (id.Trim() != "-1" && species.Length != name.Length)
{
id = "-1";
}
/* Display the status */
cell = cell_helper.GetCell(row, dtGrid.Columns.Count - 1);
((TextBlock)cell.Content).Text = id.ToString();
/* Display MPI Name */
cell = cell_helper.GetCell(row, dtGrid.Columns.Count - 2);
((TextBlock)cell.Content).Text = name.ToString();
}
}
private void btnValidate_Click(object sender, RoutedEventArgs e)
{
btnUpLoad.IsEnabled = false;
// https://social.msdn.microsoft.com/Forums/en-US/290d3c67-440e-4037-86b6-cf668990b5da/how-to-loop-through-all-the-the-cells-in-datagrid?forum=wpf
for (int row = 0; row < dtGrid.Items.Count; row++)
{
//for(int column = 0; column < dtGrid.Columns.Count; column++)
//{
// DataGridCell cell = GetCell(row, column);
//}
DataGridCell cell = GetCell(row, 1);
TextBlock text = cell.Content as TextBlock;
string [] array = text.Text.Split(' ');
string species = array[0] + ' ' + array[1];
Debug.WriteLine(species);
//dtGrid.Items[row].Cells[dtGrid.Columns.Count-1].Text = SpeciesDataHelper.GetIDByScientificName(species).ToString();
string id, name;
(id, name) = SpeciesDataHelper.CheckScientificName(species);
//cell = GetCell(row, dtGrid.Columns.Count - 1);
cell = GetCell(row, 1);
if (id.Trim() != "-1" && ((TextBlock)cell.Content).Text.Length != name.Length)
{
id = "-1";
}
/* Display the status */
cell = GetCell(row, dtGrid.Columns.Count - 1);
((TextBlock)cell.Content).Text = id.ToString();
/* Display MPI Name */
cell = GetCell(row, dtGrid.Columns.Count - 2);
((TextBlock)cell.Content).Text = name.ToString();
}
}
/// <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 btnImportExcelData_Click(object sender, RoutedEventArgs e)
{
/* Check whether the user has selected a excel file */
if (file_name == string.Empty)
{
/* DEBUG */
//Debug.WriteLine("No File is selected !!!");
MessageBox.Show("No File is selected !!!\nPlease try again");
return;
}
/* Check whether the user has selected one supplier column field as the supplier's species information */
int col_selected = cbxScientific.SelectedIndex;
if (col_selected < 0)
{
MessageBox.Show("Please select a column to extract supplier's species information.\nPlease try again");
return;
}
DataTable dt_Species = new DataTable(); // will store supplier's species information
/* Open the Excel file to reade*/
using (var stream = File.Open(file_name, FileMode.Open, FileAccess.Read))
{
/* using ExcelDataReader libary to process excel file */
// Auto-detect format, supports:
// - Binary Excel files (2.0-2003 format; *.xls)
// - OpenXml Excel files (2007 format; *.xlsx)
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
// Use DataSet method - The result of each spreadsheet will be created in the result.Tables
DataSet result = reader.AsDataSet();
DataTable dt = result.Tables[0]; // use 1st spreadsheet by default
/* retreive total number of rows and column in the spreadsheet */
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
/* TDD Debuging */
Debug.WriteLine($"rows : { rowCount } coulumns : { colCount }");
/* Retreive the Row and Cell information*/
string celData = "", rowData = "";
rowData = ExtractHeader(dt);
/* setup the datagrid column */
dt_Species.Columns.Add("Row Number", typeof(string));
dt_Species.Columns.Add("Supplier Species Information", typeof(string));
dt_Species.Columns.Add("MPI Species Name", typeof(string));
dt_Species.Columns.Add("Status", typeof(string));
for (int row = header_row + 1; row < rowCount; row++)
{
object value = dt.Rows[row][col_selected];
if (value.ToString().Trim() == "")
{
continue;
}
string[] array = value.ToString().Trim().Split(' ');
if (array.Length > 1 && array[1].Contains('-'))
{
string[] subStr = array[1].Split('-');
array[1] = subStr[0];
}
string species = array[0] + (array.Length > 1 ? " " + array[1] : "");
string id, name;
(id, name) = SpeciesDataHelper.CheckScientificName(species);
if (id.Trim() != "-1" && species.Length != name.Length)
{
id = "-1";
}
celData = "" + row + "|";
celData += value.ToString().Trim();
rowData = celData + "|" + name + "|" + id;
/* */
Debug.WriteLine($"row [{row}] :{rowData}");
dt_Species.Rows.Add(rowData.Split('|'));
}
}
}
//dataGrid.Items.Add()
dataGrid.ItemsSource = dt_Species.DefaultView;
}
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;
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)
{
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;
}
}
}