private void btnELChooseFile_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); if (FileUpload1.ShowDialog() == DialogResult.OK) { string FileName = Path.GetFileName(FileUpload1.FileName); HFExcel.Text = FileName.Trim(); string Extension = Path.GetExtension(FileUpload1.FileName); string FilePath = System.Windows.Forms.Application.StartupPath + "\\App_Data\\" + FileName; System.IO.File.Copy(FileUpload1.FileName, FilePath); string conStr = string.Empty; switch (Extension) { case ".xls": //Excel 97-03 conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; break; case ".xlsx": //Excel 07 conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; break; } OleDbConnection connExcel = new OleDbConnection(conStr); // OleDbCommand cmdExcel = new OleDbCommand(); OleDbDataAdapter oda = new OleDbDataAdapter(); DataTable dt = new DataTable(); //cmdExcel.Connection = connExcel; try { connExcel.Open(); DataTable dtExcelSchema; dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); var SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); OleDbCommand cmdExcel = new OleDbCommand("SELECT * From [" + SheetName + "]", connExcel); cmdExcel.CommandText = "SELECT * From [" + SheetName + "]"; oda.SelectCommand = cmdExcel; //oda.Fill(dt); oda.Fill(ds); DataTable exceldt = ds.Tables[0]; //ViewState["dt"] = dt; // MessageBox.Show("try"); string Result = string.Empty; int TotalCount = 0; int ResultCount = 0; int FailedCount = 0; int AlreadyCount = 0; //DataTable dt = new DataTable(); //dt = ViewState["dt"] as DataTable; //TotalCount = dt.Rows.Count; List<string> ListLinkedIds = new List<string>(); if (exceldt.Rows.Count > 0) { // MessageBox.Show("Enter"); for (int i = 0; i < exceldt.Rows.Count; i++) { objBO.QueryType = "Insert"; objBO.ProductID = exceldt.Rows[i]["Group"].ToString().Trim(); objBO.ProductGroupID = exceldt.Rows[i]["LocationId"].ToString().Trim(); objBO.ProductName = exceldt.Rows[i]["EquipmentName"].ToString().Trim(); objBO.BarcodeNo = exceldt.Rows[i]["EquipmentId"].ToString().Trim(); objBO.ProductModel = exceldt.Rows[i]["EquipmentModel"].ToString().Trim(); objBO.ProductSerial = exceldt.Rows[i]["EquipmentSerial"].ToString().Trim(); objBO.ProductMake = exceldt.Rows[i]["EquipmentMake"].ToString().Trim(); objBO.CalibrationFrequency = exceldt.Rows[i]["CalibrationFrequency"].ToString().Trim(); objBO.DateofPuechase = ValidateClass.ConvDMYTOYMD(exceldt.Rows[i]["DateofPuechase"].ToString()).Trim(); objBO.ASSETNo = exceldt.Rows[i]["ASSETNo"].ToString().Trim(); objBO.Calibrated = ValidateClass.ConvDMYTOYMD(exceldt.Rows[i]["CalibrationDate"].ToString()).Trim(); objBO.CalibrateDue = ValidateClass.ConvDMYTOYMD(exceldt.Rows[i]["CalibrateDue"].ToString()).Trim(); objBO.WarrantyDate = ValidateClass.ConvDMYTOYMD(exceldt.Rows[i]["WarrantyDate"].ToString()).Trim(); objBO.ProductRemark = exceldt.Rows[i]["EquipmentRemark"].ToString().Trim(); objBO.EmailBefore = exceldt.Rows[i]["EmailBefore"].ToString().Trim(); objBO.CalibratCertificate = null; objBO.Location = "1"; objBO.Status = "Y"; objBO.CreatedBy = userName; objBO.TransactionFlag = "N"; Result = objBAL.ProductMaster(objBO); if (Result == "Success") { ResultCount = ResultCount + 1; } else if (Result == "Already") { AlreadyCount = AlreadyCount + 1; } else { FailedCount = FailedCount + 1; } } if (TotalCount > 0) { string TotalRecord = string.Empty; string RecordSuccessfully = string.Empty; string AlreadyExisting = string.Empty; string RecordFailed = string.Empty; if (TotalCount > 0) { TotalRecord = "Total Record" + ' ' + TotalCount.ToString(); } else { TotalRecord = string.Empty; } if (ResultCount > 0) { RecordSuccessfully = ResultCount.ToString() + ' ' + "Record(s) are Inserted Successfully, "; } else { RecordSuccessfully = string.Empty; } if (AlreadyCount > 0) { AlreadyExisting = AlreadyCount.ToString() + ' ' + "Asset No. is Already Existing, "; } else { AlreadyExisting = string.Empty; } if (FailedCount > 0) { RecordFailed = FailedCount.ToString() + ' ' + "Record(s) are Failed, "; } else { RecordFailed = string.Empty; } BindProduct(); MessageBox.Show("Product Master", " " + TotalRecord + "( " + RecordSuccessfully + " " + AlreadyExisting + " " + RecordFailed + ")"); } else { MessageBox.Show("no rows"); } } } catch (Exception ex) { MessageBox.Show(ex.Message); //MessageBox.Show("catch"); } } else { MessageBox.Show("no rows"); } }
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)