Here I'm trying to read Excel file and insert data into mysql database using NPOI library. Sometimes it works perfectly sometimes it misses some data, it shows Object reference not set to an instance of an object. What am I doing wrong? Help me..
using ExcelRead.Data;
using ExcelRead.Models;
using Microsoft.AspNetCore.Mvc;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Diagnostics;
using System.IO;
namespace ExcelRead.Controllers
{
public class HomeController : Controller
{
private ApplicationDbContext _context;
public HomeController(ApplicationDbContext context)
{
_context = context;
}
public IActionResult Index()
{
return View();
}
[HttpPost]
public IActionResult Excl()
{
String filePath = @"C:\Users\zameer.ahammad\Documents\TimeCards\FieldGlass\timeSheet.supplier.list.xlsx";
try
{
IWorkbook workbook = null;
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
workbook = new XSSFWorkbook(fs);
ISheet sheet = workbook.GetSheet("timeSheet.supplier.list");
if (sheet != null)
{
int rowCount = sheet.LastRowNum+1;
for (int i = 2; i <= rowCount; i++) //First two rows of excel data is not required
{
fieldglassTimesheetDataModel excelinsert = new fieldglassTimesheetDataModel();
IRow curRow = sheet.GetRow(i);
excelinsert.ID = curRow.GetCell(1).StringCellValue.Trim();
excelinsert.Worker = curRow.GetCell(3).StringCellValue.Trim();
excelinsert.ST = (float)curRow.GetCell(6).NumericCellValue;
excelinsert.OT = (float)curRow.GetCell(7).NumericCellValue;
excelinsert.DT = (float)curRow.GetCell(8).NumericCellValue;
//Console.WriteLine(cellVal0 + "\t\t" + cellVal1 + "\t\t" + cellVal2 + "\t\t" + cellVal3 + "\t\t" + cellVal4);
_context.Add(excelinsert);
_context.SaveChangesAsync();
//excelinsert = null;
}
}
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
}
return View("Index");
}
}
}