private void ViewDetails(string orderedId)
{
mpeDetails.Show();
lblOrderNo.Text = orderedId;
gvOrderedDetails.DataSource = BL_OrderedExcel.GetOrderedListByDate(3, int.Parse(ddlBranch.SelectedValue), ddlSeason.SelectedValue, orderedId.ToString(), DateTime.Parse(ConvertNE.ConvertNToE(DateStringToInt.StringToInt(txtDate.Text))));
gvOrderedDetails.DataBind();
}
private void DeleteOrderedList(string orderedId)
{
var obj = new ImportExcel();
obj.ExcelData = "";
obj.CreatedBy = BK_Session.GetSession().UserId;
obj.BranchId = BK_Session.GetSession().BranchId;
obj.OrderedDate = BK_Session.GetSession().OpDate;
obj.OrderId = int.Parse(orderedId);
int id = 0;
var msg = BL_OrderedExcel.InsUpdDelExcelOrder('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)
{
var dtExcel = new DataTable("OrderedExcel");
dtExcel.Columns.Add("Store_x0020_No");
var httpPostedFile = ordImage.PostedFile;
if (httpPostedFile != null && httpPostedFile.ContentLength > 0)
{
var postedFile = ordImage.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=1\"";
}
//Create Connection to Excel work book and add oledb namespace
var excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
var dt = new DataTable();
// var format=excelConnection.GetOleDbSchemaTable()
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);
//excelSheets[0]
var query = string.Format("Select * from [{0}]", excelSheets[0]);
// excelSheets.Range["D5"].Text = sheet.Range["C5"].Formula;
//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 (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 = BK_Session.GetSession().BranchId;
obj.OrderedDate = BK_Session.GetSession().OpDate;
obj.Season = int.Parse(ddlSeason.SelectedValue);
int id = 0;
var msg = BL_OrderedExcel.InsUpdDelExcelOrder('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!!");
}
}
}
private void FillGridview(int eventFlag, string code)
{
gvOrderedList.DataSource = BL_OrderedExcel.GetOrderedList(eventFlag, int.Parse(ddlBranch.SelectedValue), code, "");
gvOrderedList.DataBind();
}
public void FillGridView()
{
gvOrderList.DataSource = BL_OrderedExcel.GetOrderedListByDate(2, int.Parse(ddlBranch.SelectedValue), ddlSeason.SelectedValue, "", DateTime.Parse(ConvertNE.ConvertNToE(DateStringToInt.StringToInt(txtDate.Text))));
gvOrderList.DataBind();
}
