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