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