// GET api/<controller>/5
public ImportExcelModel Get(int id)
{
var info = ImportExcelRepository.GetInfo(id);
var retVal = ImportExcelModel.FromInfo(info);
if (retVal.ImportStatus.HasValue && retVal.ImportStatus == 1)
{
//StoreData.LoadData();
StoreData.LoadImportExcel();
}
return(retVal);
}
public DataTablesJsonResult OpenFileAndExcelPaging(IDataTablesRequest request, string fileName)
{
string folderName = "Upload";
string webRootPath = _hostingEnvironment.WebRootPath;
string newPath = Path.Combine(webRootPath, folderName);
string sFileExtension = Path.GetExtension(fileName).ToLower();
ISheet sheet; // tạo sheet
string fullPath = Path.Combine(newPath, fileName);
using (var stream = new FileStream(fullPath, FileMode.Open)) //open file từ wwwroot-upload
{
var startRow = 7; // đọc từ row 8
var startRowPaging = startRow + request.Start; // số dòng bằng 7 + request
stream.Position = 0;
if (sFileExtension == ".xls")
{
HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
else
{
XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
IRow headerRow = sheet.GetRow(startRowPaging); //Get Header Row
int cellCount = headerRow.LastCellNum;
var data = new List <ImportExcelModel>();
var length = 0;
if (sheet.LastRowNum > startRowPaging + request.Length) // row cuối lớn hơn row+ request
{
length = startRowPaging + request.Length; // length = đoạn đó
}
else
{
length = sheet.LastRowNum; // length bằng đoạn còn lại
}
for (int i = startRowPaging + (sheet.FirstRowNum + 1); i <= length; i++) //Read Excel File từ 8 đến length
{
var rowdata = new ImportExcelModel();
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.Cells.All(d => d.CellType == CellType.Blank))
{
continue;
}
rowdata.STT = row.GetCell(0).ToString();
rowdata.StudentId = row.GetCell(1).ToString();
rowdata.LastName = row.GetCell(2).ToString();
rowdata.FirstName = row.GetCell(3).ToString();
rowdata.Dob = row.GetCell(4).ToString();
rowdata.ClassName = row.GetCell(5).ToString();
rowdata.GradeName = row.GetCell(6).ToString();
rowdata.Gender = row.GetCell(7).ToString();
data.Add(rowdata);
}
var response = DataTablesResponse.Create(request, sheet.LastRowNum - startRow, sheet.LastRowNum - startRow, data); // phân trang từ row cuối trừ starrow = 7
return(new DataTablesJsonResult(response, true));
}
}
