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);
}
private async Task <int> loadTemplateAndLines()
{
// string[] lines = null;
// ge_search template_loaded = null;
if (data_file.fileext == ".csv")
{
lines = await _dataService.GetFileAsLines(data_file.Id);
SearchTerms st = new SearchTerms();
template_loaded = st.findSearchTerms(template, table, lines);
if (template_loaded.search_tables.Count == 0)
{
return(-1);
}
}
if (data_file.fileext == ".xlsx")
{
using (MemoryStream ms = await _dataService.GetFileAsMemoryStream(data_file.Id)) {
ge_log_workbook wb = new ge_log_workbook(ms);
SearchTerms st = new SearchTerms();
if (sheet.Contains(","))
{
string[] sheets = sheet.Split(",");
template_loaded = st.findSearchTerms(template, table, wb, sheets);
}
else
{
template_loaded = st.findSearchTerms(template, table, wb, sheet);
}
if (template_loaded.search_tables.Count == 0)
{
return(-1);
}
wb.setWorksheet(template_loaded.search_tables[0].sheet);
wb.evaluateSheet();
lines = wb.WorksheetToTable();
wb.close();
}
}
return(lines.Count());
}
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);
}