public static ExcelViewInfo GetView(string excelpath, string sheetName = "", int pageNum = 1, string searchWords = "") { var excelInfo = new ExcelViewInfo(); excelInfo.PageNum = pageNum; if (pageNum == 1) { excelInfo.IsFirstPage = true; } excelInfo.SearchWords = searchWords; using (var fs = new FileStream(excelpath, FileMode.Open)) { using (var package = new ExcelPackage(fs)) { excelInfo.SheetInfos = GetSheetInfos(package.Workbook.Worksheets); var currentSheet = package.Workbook.Worksheets[sheetName] ?? package.Workbook.Worksheets[0]; sheetName = currentSheet.Name; excelInfo.CurrentSheetName = sheetName; excelInfo.CurrentRowsCount = excelInfo.SheetInfos.FirstOrDefault(u => u.SheetName == sheetName).RowCount; excelInfo.CurrentColsCount = excelInfo.SheetInfos.FirstOrDefault(u => u.SheetName == sheetName).ColCount; excelInfo.Headers = GetHeaders(currentSheet); excelInfo.RowInfos = GetRowInfos(currentSheet, excelInfo); } } return(excelInfo); }
private static Dictionary <int, List <string> > GetRowInfos(ExcelWorksheet sheet, ExcelViewInfo excelInfo) { var dic = new Dictionary <int, List <string> >(); var step = 18; //默认每页显示条数 var start = ((excelInfo.PageNum - 1) * step) != 0 ? ((excelInfo.PageNum - 1) * step) : 1; var searchList = string.IsNullOrEmpty(excelInfo.SearchWords) ?new string[0]: excelInfo.SearchWords.Split(' ', StringSplitOptions.RemoveEmptyEntries); //搜索关键字切割 if (searchList.Length == 0) //有无关键字需要采取不同的策略 { for (var i = start + 1; i <= excelInfo.PageNum * step; i++) { if (i == sheet.Dimension.Rows) { excelInfo.IsEndPage = true; } dic.Add(i, new List <string>()); for (var j = 1; j < sheet.Dimension.Columns; j++) { if (!ContainKeyWord(sheet, i, searchList)) { continue; } dic[i].Add(sheet.GetValue(i, j) == null ? "" : sheet.GetValue(i, j).ToString()); } } } else { int rowCount = 1;//记录符合关键字的行数,第一行列头 for (var i = 1; i <= sheet.Dimension.Rows; i++) { if (i == sheet.Dimension.Rows) { excelInfo.IsEndPage = true; } if (!ContainKeyWord(sheet, i, searchList)) { continue; } rowCount++; if (rowCount > excelInfo.PageNum * step) { break; } if (rowCount >= start + 1 && rowCount <= excelInfo.PageNum * step) { dic.Add(i, new List <string>()); for (var j = 1; j < sheet.Dimension.Columns; j++) { var temp = sheet.GetValue(i, j) == null ? "" : sheet.GetValue(i, j).ToString(); temp = HighLight(temp, searchList); dic[i].Add(temp); } } } } return(dic); }