public PurchaseQuotationItem GetExcelQuotationItem(DataSet ds, int i, ref string errorMessage) { PurchaseQuotationItem pqItem = new PurchaseQuotationItem(); try { if (ds.Tables[0].Rows[i]["Item Code"] != null) { pqItem.Product_id = Convert.ToInt32(ds.Tables[0].Rows[i]["Item Code"]); } else { return(null); } if (ds.Tables[0].Rows[i]["Item Required Date"] != null) { pqItem.Required_date = Convert.ToDateTime(ds.Tables[0].Rows[i]["Item Required Date"]); } else { return(null); } if (ds.Tables[0].Rows[i]["Required Quantity"] != null) { pqItem.Required_qty = Convert.ToInt32(ds.Tables[0].Rows[i]["Required Quantity"]); } else { //returnMessage = "Required Quantity cannot be empty it the excel sheet"; return(null); } if (ds.Tables[0].Rows[i]["Unit Price"] != null) { pqItem.Unit_price = Convert.ToInt32(ds.Tables[0].Rows[i]["Unit Price"]); } else { //returnMessage = "Unit Price cannot be empty it the excel sheet"; return(null); } if (ds.Tables[0].Rows[i]["Discount"] != null) { pqItem.Discount_percent = Convert.ToInt32(ds.Tables[0].Rows[i]["Discount"]); } else { //returnMessage = "Discount Percentage cannot be empty it the excel sheet"; return(null); } if (ds.Tables[0].Rows[i]["VAT Code"] != null) { pqItem.Discount_percent = Convert.ToInt32(ds.Tables[0].Rows[i]["VAT Code"]); } else { //returnMessage = "VAT Code cannot be empty it the excel sheet"; return(null); } if (ds.Tables[0].Rows[i]["Amount"] != null) { pqItem.LineTotal = Convert.ToInt32(ds.Tables[0].Rows[i]["Amount"]); } else { //returnMessage = "VAT Code cannot be empty it the excel sheet"; return(null); } pqItem.Quoted_qty = null; //GetQuantity() pqItem.Quoted_date = null; return(pqItem); } catch (Exception ex) { errorMessage = ex.Message; return(null); } }
public bool UploadExcelData(string fileExtension, string fileName, ref string returnMessage) { try { ApplicationUser currentUser = ApplicationUserManager.GetApplicationUser(User.Identity.Name, HttpContext.GetOwinContext()); string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/App_Data/ExcelFiles"), fileName); if (System.IO.File.Exists(fileLocation)) { System.IO.File.Delete(fileLocation); } Request.Files["FileUpload"].SaveAs(fileLocation); string excelConnectionString = string.Empty; excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; //connection String for xls file format. if (fileExtension == ".xls") { excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } //connection String for xlsx file format. else if (fileExtension == ".xlsx") { excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } //Create Connection to Excel work book and add oledb namespace OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); excelConnection.Open(); DataTable dt = new DataTable(); string exquery; dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { returnMessage = "Excel file is empty"; return(false); } String[] excelSheets = new String[dt.Rows.Count]; int t = 0; //excel data saves in temp file here. foreach (DataRow row in dt.Rows) { excelSheets[t] = row["TABLE_NAME"].ToString(); t++; } for (int k = 0; k < dt.Rows.Count; k++) { DataSet ds = new DataSet(); int sheets = k + 1; OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString); exquery = string.Format("Select * from [{0}]", excelSheets[k]); using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(exquery, excelConnection1)) { dataAdapter.Fill(ds); } if (ds != null) { if (ds.Tables[0].Rows.Count > 0) { if (ExcelDataIsValid(ds, ref returnMessage)) { int row = 0; int vendorId = 0; for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { List <PurchaseQuotationItem> pqItemList = new List <PurchaseQuotationItem>(); PurchaseQuotation pItem = new PurchaseQuotation(); PurchaseQuotationItem pqItem = new PurchaseQuotationItem(); if (ds.Tables[0].Rows[i]["Vendor"] != null) { pItem.Vendor = Convert.ToInt32(ds.Tables[0].Rows[i]["Vendor"]); //vendorId = pItem.Vendor; pqItem = GetExcelQuotationItem(ds, i, ref returnMessage); if (pqItem != null) { pqItemList.Add(pqItem); } else { returnMessage = "Vendor name cannot be empty it the excel sheet"; return(false); } for (int j = i + 1; j < ds.Tables[0].Rows.Count; j++) { if (ds.Tables[0].Rows[j]["Vendor"].ToString() == "" || ds.Tables[0].Rows[j]["Vendor"].ToString() == null) { pqItem = GetExcelQuotationItem(ds, j, ref returnMessage); if (pqItem != null) { pqItemList.Add(pqItem); row = j; } else { returnMessage = "Vendor name cannot be empty it the excel sheet"; return(false); } } else { row = j - 1; break; } } } else { } pItem.Reference_Number = ds.Tables[0].Rows[i]["Reference Number"].ToString(); pItem.Quotation_Status = ds.Tables[0].Rows[i]["Quotation Status"].ToString(); pItem.Ship_To = Convert.ToInt32(ds.Tables[0].Rows[i]["Ship To"]); pItem.Freight = Convert.ToInt32(ds.Tables[0].Rows[i]["Freight"]); pItem.Loading = Convert.ToInt32(ds.Tables[0].Rows[i]["Loading"]); pItem.Posting_Date = Convert.ToDateTime(ds.Tables[0].Rows[i]["Posting Date"]); pItem.Valid_Date = Convert.ToDateTime(ds.Tables[0].Rows[i]["Valid Up To"]); pItem.Required_Date = Convert.ToDateTime(ds.Tables[0].Rows[i]["Required Date"]); //pItem.DocDiscAmt = Convert.ToInt32(ds.Tables[0].Rows[i]["DocDiscAmt"]); pItem.Remarks = ds.Tables[0].Rows[i]["Remarks"].ToString(); pItem.Created_User_Id = currentUser.Created_User_Id; pItem.Created_Branc_Id = currentUser.Created_Branch_Id; pItem.Created_Date = DateTime.Now; pItem.Modified_User_Id = currentUser.Modified_User_Id; pItem.Modified_Branch_Id = currentUser.Modified_Branch_Id; pItem.Modified_Date = DateTime.Now; //plist.PurchaseQuotationList.Add(pItem); if (purchaseDb.AddNewQuotation(pItem, pqItemList, ref returnMessage)) { i = row; } else { return(false); } } //return plist; return(true); } else { //returnMessage = "12325"; return(false); } } else { returnMessage = "Excel file is empty"; return(false); } } else { returnMessage = "Excel file is empty"; return(false); } } returnMessage = "Excel file is empty"; return(false); } catch (Exception ex) { returnMessage = ex.Message; return(false); } }