public JsonResult UploadExcel(ProductOpening ObjOpening)
{
ViewBag.msg = "";
try
{
if (SaveExcelFile(ObjOpening.ExcelFile))
{
LP_Inventory objINV = new LP_Inventory();
DataTable dt =Helper.ToDataTable(ReadExcelFile(ObjOpening.ExcelFile.FileName));
LP_Inventory_BLL objINVBLL = new LP_Inventory_BLL();
objINVBLL.UploadExcel(dt, dt);
// objattendancebll = new Attendance_BLL();
var flag = true;// objattendancebll.Insert(objattendanceproperty.tbl_Attendance);
if (flag)
{
ViewBag.msg = "Success";
}
else
{
ViewBag.msg = "Contact Administrator";
}
}
return Json(new { success = true, msg = ViewBag.msg }, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
return Json(new { success = false, msg = "Contact Administrator" }, JsonRequestBehavior.AllowGet);
}
}
private List<LP_Inventory> ReadExcelFile(string filename)
{
try
{
int rowstart, productIdx, stock, productTypeIdx, unitPrice, totalAmount, creationDate;
rowstart = Convert.ToInt32(ConfigurationManager.AppSettings["RowStartReading"].ToString());
productIdx = Convert.ToInt32(ConfigurationManager.AppSettings["productIdx"].ToString());
stock = Convert.ToInt32(ConfigurationManager.AppSettings["stock"].ToString());
productTypeIdx = Convert.ToInt32(ConfigurationManager.AppSettings["productTypeIdx"].ToString());
unitPrice = Convert.ToInt32(ConfigurationManager.AppSettings["unitPrice"].ToString());
totalAmount = Convert.ToInt32(ConfigurationManager.AppSettings["totalAmount"].ToString());
creationDate = Convert.ToInt32(ConfigurationManager.AppSettings["creationDate"].ToString());
List<LP_Inventory> Listattendance = new List<LP_Inventory>();
LP_Inventory Inventory;
string filepath = Server.MapPath("/ExcelFiles/" + filename).ToString();
excel.Application xlApp = new excel.Application();
excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filepath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
excel._Worksheet xlWorksheet = (excel._Worksheet)xlWorkbook.Sheets[1];
excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = rowstart; i <= rowCount; i++)
{
Inventory = new LP_Inventory();
//PID
if (xlRange.Cells[i, productIdx] != null && xlRange.Cells[i, productIdx].Value2 != null)
{
Inventory.productIdx =Convert.ToInt16( xlRange.Cells[i, productIdx].Value2.ToString());
}
//Stock
if (xlRange.Cells[i, stock] != null && xlRange.Cells[i, stock].Value2 != null)
{
Inventory.stock = Convert.ToInt16(xlRange.Cells[i, stock].Value2.ToString());
}
//Producttype
if (xlRange.Cells[i, productTypeIdx] != null && xlRange.Cells[i, productTypeIdx].Value2 != null)
{
Inventory.productTypeIdx = xlRange.Cells[i, productTypeIdx].Value2.ToString();
}
//UnitPrice
if (xlRange.Cells[i, unitPrice] != null && xlRange.Cells[i, unitPrice].Value2 != null)
{
Inventory.unitPrice = Convert.ToDecimal(xlRange.Cells[i, unitPrice].Value2.ToString());
}
//Total
if (xlRange.Cells[i, totalAmount] != null && xlRange.Cells[i, totalAmount].Value2 != null)
{
Inventory.totalAmount =Convert.ToDecimal(xlRange.Cells[i, totalAmount].Value2.ToString());
}
//CreateDate
if (xlRange.Cells[i, creationDate] != null && xlRange.Cells[i, creationDate].Value2 != null)
{
String date = xlRange.Cells[i, creationDate].Value2.ToString();
//string newdate = DateTime.ParseExact(date, "yy-MM-dd", CultureInfo.CurrentCulture).ToString("yyyy/MM/dd");
Inventory.creationDate = DateTime.Now;// Convert.ToDateTime(newdate);// DateTime.ParseExact(xlRange.Cells[i, AttendanceDate].Value2.ToString(), "yyyy/MM/dd", null);
//attendance.Attendance_Date =Convert.ToDateTime(xlRange.Cells[i, AttendanceDate].Value2.ToString());
}
Listattendance.Add(Inventory);
}
return Listattendance;
}
catch (Exception ex)
{
using (var tw = new StreamWriter(Server.MapPath("/ExcelFiles/" + "abc.txt"), true))
{
tw.WriteLine(ex.Message);
}
return new List<LP_Inventory>();
}
}
