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); }