private void DeleteImportList(string invoiceNo)
{
var obj = new ImportExcel();
obj.ExcelData = "";
obj.CreatedBy = BK_Session.GetSession().UserId;
obj.BranchId = BK_Session.GetSession().BranchId;
obj.ImportedDate = BK_Session.GetSession().OpDate;
obj.InvoiceNo = invoiceNo;
int id = 0;
var msg = BllImportExcel.InsUpdDelExcelImport('D', obj, out id);
if (msg == "Record Deleted Successfully")
{
FillGridView();
_msgbox.ShowSuccess(msg);
}
else
{
_msgbox.ShowWarning(msg);
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
try
{
if (ddlSeason.SelectedValue == "0")
{
_msgbox.ShowWarning("Please Select Season!!");
return;
}
var dtExcel = new DataTable("ImportExcel");
dtExcel.Columns.Add("Date");
dtExcel.Columns.Add("DocNo");
dtExcel.Columns.Add("Customer_x0020_Name");
dtExcel.Columns.Add("Stock_x0020_No");
dtExcel.Columns.Add("Gender");
dtExcel.Columns.Add("Category");
dtExcel.Columns.Add("Item_x0020_Descr");
dtExcel.Columns.Add("Style");
dtExcel.Columns.Add("Color");
dtExcel.Columns.Add("Size");
dtExcel.Columns.Add("Qty");
dtExcel.Columns.Add("Item_x0020_Rate");
dtExcel.Columns.Add("MRP_x0020_INR");
dtExcel.Columns.Add("MRP_x0020_NPR");
dtExcel.Columns.Add("AccountMRP");
dtExcel.Columns.Add("SalesMRP");
var httpPostedFile = impImage.PostedFile;
if (httpPostedFile != null && httpPostedFile.ContentLength > 0)
{
var postedFile = impImage.PostedFile;
if (postedFile != null)
{
var fileExtension =
System.IO.Path.GetExtension(postedFile.FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
var fileLocation = Server.MapPath("~/Content/") + postedFile.FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
postedFile.SaveAs(fileLocation);
var excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileLocation +
";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=0\"";
//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=0\"";
}
//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=0\"";
}
//Create Connection to Excel work book and add oledb namespace
var excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
var dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return;
}
var excelSheets = new String[dt.Rows.Count];
var t = 0;
//excel data saves in temp file here.
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
var excelConnection1 = new OleDbConnection(excelConnectionString);
var query = string.Format("Select * from [{0}]", excelSheets[0]);
//string.Format("Select [Date],DocNo,Customer Name as CustomerName,Stock No as StockNo,Gender,Category,Item Descr as ItemDescr,Style,Color,Size,Qty,Item Rate as ItemRate,MRP INR as MRPINR,MRP NPR as MRPNPR from [{0}]", excelSheets[0]);
using (var dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(dtExcel);
}
}
}
if (!ValidateExcel(dtExcel))
{
_msgbox.ShowWarning("Excel columns doesn't match required.Please browse valid excel sheet!!");
return;
}
if (dtExcel.Rows.Count > 0)
{
var sw = new StringWriter();
dtExcel.WriteXml(sw);
var obj = new ImportExcel();
obj.ExcelData = sw.ToString();
obj.CreatedBy = BK_Session.GetSession().UserId;
obj.BranchId = int.Parse(ddlBranch.SelectedValue);
obj.ImportedDate = BK_Session.GetSession().OpDate;
obj.InvoiceNo = txtInvoiceNo.Text;
obj.AirwayBillNo = txtAirwayBillNo.Text;
obj.Season = int.Parse(ddlSeason.SelectedValue);
var id = 0;
var msg = BllImportExcel.InsUpdDelExcelImport('I', obj, out id);
if (msg == "Data Imported Successfully")
{
_msgbox.ShowSuccess(msg);
}
else
{
_msgbox.ShowWarning(msg);
}
}
else
{
_msgbox.ShowWarning("Please browse excel sheet having data!!");
}
}
else
{
_msgbox.ShowWarning("Please browse data first!!");
}
}
catch (Exception ex)
{
throw ex;
}
}