public ge_search findSearchTerms(ge_search dic, string table, ge_log_workbook wb, string[] sheets)
{
List <ge_search> gs_wb = new List <ge_search>();
foreach (string sheet in sheets)
{
ge_search gs_ws = findSearchTerms(dic, table, wb, sheet);
gs_wb.Add(gs_ws);
}
ge_search gs_ret = new ge_search();
gs_ret.name = "logger header created:" + DateTime.Now;
foreach (ge_search gs in gs_wb)
{
foreach (search_item si in gs.search_items)
{
gs_ret.search_items.Add(si);
}
search_table st = gs.search_tables.Where(e => e.name.Contains(table)).FirstOrDefault();
if (st != null)
{
gs_ret.search_tables.Add(st);
}
}
gs_ret.SplitItemsToArrayItems();
gs_ret.setFoundTableValues();
return(gs_ret);
}
public ge_search findSearchTerms(ge_search dic, string name, string[] lines)
{
ge_search new_dic = new ge_search();
string delimeter = ",";
new_dic.name = "logger header created:" + DateTime.Now;
int max_line = lines.Count() - 1;
int max_search_lines = Math.Min(max_line, MAX_SEARCH_LINES);
foreach (search_item si in dic.search_items)
{
for (int i = 0; i < max_search_lines; i++)
{
if (lines[i].Contains(si.search_text))
{
si.row = i;
si.row_text = lines[i + si.row_offset];
si.set_value();
new_dic.search_items.Add(si);
break;
}
}
}
search_item end_line = dic.search_items.Find(i => i.name == "data_end");
if (end_line != null)
{
if (lines[max_line].Contains(end_line.search_text))
{
end_line.row_text = lines[max_line];
}
end_line.row = max_line;
new_dic.search_items.Add(end_line);
}
new_dic.SplitItemsToArrayItems();
Boolean colNotFound = false;
foreach (search_table st in dic.search_tables)
{
search_range sh = st.header;
string header = "";
search_item si = null;
colNotFound = false;
if (sh.row > 0)
{
header = lines[sh.row];
}
if (!String.IsNullOrEmpty(sh.search_item_name))
{
si = new_dic.search_items.Find(e => e.name == sh.search_item_name);
if (si == null)
{
continue;
}
header = new_dic.search_items.Find(e => e.name == sh.search_item_name).row_text;
}
if (header == "")
{
new_dic.status = $"table {st.name} header {st.header.search_item_name} not found";
continue;
}
string[] columns = header.Split(delimeter);
foreach (value_header vh in st.headers)
{
if (vh.found == NOT_FOUND)
{
int i = columns.findFirstIndexContains(vh.search_text);
if (i == NOT_FOUND && vh.IsRequired() == true)
{
new_dic.status = $"required column [{vh.id}] search text [{vh.search_text}] of table [{name}] not found";
colNotFound = true;
break;
}
if (i != NOT_FOUND)
{
vh.found = i + vh.col_offset;
vh.source = ge_log_constants.SOURCE_ACTUAL;
}
}
}
if (colNotFound == false)
{
if (name == null | name == "" | name == st.name)
{
new_dic.search_tables.Add(st);
new_dic.setFoundTableValues(st);
new_dic.status = $"all required columns of table {name} found";
break;
}
}
}
return(new_dic);
}
public ge_search findSearchTerms(ge_search dic, string table, ge_log_workbook wb, string sheet)
{
ge_search gs_ws = new ge_search();
gs_ws.name = $"logger header created:{DateTime.Now} sheet {sheet}";
wb.MAX_SEARCH_LINES = MAX_SEARCH_LINES;
try {
if (!String.IsNullOrEmpty(sheet))
{
wb.setWorksheet(sheet);
}
if (String.IsNullOrEmpty(sheet))
{
if (wb.setOnlyWorksheet() == false)
{
gs_ws.status = "There is more than one worksheet, in this workbook. Unable to determine which worksheet to get data from";
return(gs_ws);
}
}
if (wb.worksheet == null)
{
gs_ws.status = $"There is more than one worksheet in this workbook, or unable to find {sheet}";
return(gs_ws);
}
// recalcuate an formula cells ahead of search
wb.evaluateSheet();
foreach (search_item si in dic.search_items)
{
// ge_log_workbook is zero based array of worksheet so will match string[]
int found = wb.matchReturnRow(si.search_text, si.MatchExact());
if (found != NOT_FOUND)
{
if (si.split > 0)
{
si.value = wb.matchReturnValueCSV(si.search_text, si.start_offset, si.row_offset, si.length, si.MatchExact());
}
else
{
si.value = wb.matchReturnValue(si.search_text, si.start_offset, si.row_offset, si.MatchExact());
}
si.row = found;
si.col = wb.matchReturnColumn(si.search_text, si.MatchExact());
si.row_text = wb.RowCSV(si.row, si.col + si.col_offset);
gs_ws.search_items.Add(si);
}
}
gs_ws.SplitItemsToArrayItems();
foreach (search_table st in dic.search_tables.Where(e => e.name.Contains(table)))
{
Boolean colNotFound = false;
search_range sh = st.header;
int header_row = 0;
int header_offset = 0;
search_item si2 = null;
colNotFound = false;
if (!String.IsNullOrEmpty(sh.search_item_name))
{
si2 = gs_ws.search_items.Find(e => e.name == sh.search_item_name);
if (si2 == null)
{
//header not found
gs_ws.status = $"header row not found, data cannot be located on sheet {sheet}";
return(gs_ws);
}
// ge_log_workbook is zero based array of worksheet so will match[]
header_row = si2.row;
header_offset = si2.row_offset;
}
foreach (value_header vh in st.headers)
{
int col_start = 0;
if (vh.search_offset != null)
{
search_item si3 = gs_ws.search_items.Find(e => e.name == vh.search_offset.search_item_name);
if (si3 != null)
{
col_start = si3.col + si3.getcoloffset();
}
}
int i = wb.matchReturnColumn(vh.search_text, header_row, header_offset, col_start, vh.MatchExact());
if (i == NOT_FOUND && vh.IsRequired() == true)
{
gs_ws.status = $"column search text [{vh.search_text}] of table [{table}] not found";
colNotFound = true;
break;
}
else
{
// ge_log_workbook is zero based array of worksheet so
// add 1 so column is correctly located in the csv file
vh.found = i + vh.col_offset;
vh.source = ge_log_constants.SOURCE_ACTUAL;
}
}
if (colNotFound == false)
{
st.name = table;
st.id = table;
st.sheet = sheet;
gs_ws.search_tables.Add(st);
gs_ws.setFoundTableValues(st);
gs_ws.status = $"all columns of table {table} found on {sheet}";
break;
}
}
} catch (Exception e) {
gs_ws.status = e.Message;
}
return(gs_ws);
}