public void ReadExcelData(string FilePath) { try { if (!string.IsNullOrEmpty(FilePath)) { string strExtension = Path.GetExtension(FilePath); string strFileName = string.Empty; string FileOrginalName = FilePath.Split('|')[0]; string action = string.Empty; string connectionString = string.Empty; OleDbCommand ExcelCommand; OleDbDataAdapter ExcelAdapter; DataTable dtSuspendUsers = new DataTable(); DataTable dtRawData = new DataTable(); strFileName = Path.GetFileNameWithoutExtension(FilePath); string strSourcePath = Server.MapPath("../ShippingDataUploadFiles/" + strFileName); if (strExtension == ".xls" || strExtension == ".xlsx") { lblErrorMessage.Text = ""; if (strExtension == ".xls") { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strSourcePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } else if (strExtension == ".xlsx") { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strSourcePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; ; } OleDbConnection con = new OleDbConnection(connectionString); OleDbCommand cmd = new OleDbCommand(); con.Open(); //Get the list of sheet available in excel sheet DataTable dtExcelDetails = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //Get first sheet name string getExcelSheetName = string.Empty; //getExcelSheetName = foundRows["Table_Name"].ToString(); if (true) { //Select rows from second sheet in excel sheet and fill into dataset ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + "AddressBook$" + @"]", con); ExcelAdapter = new OleDbDataAdapter(ExcelCommand); ExcelAdapter.Fill(dtRawData); con.Close(); if (dtRawData.Rows.Count > 0) { ViewState["dtRawData"] = dtRawData; // FormatShippingAddress(dtRawData); } if (dtRawData.Rows.Count > 0) { string[] strAddTemplateColumns = { "ShippingAddressID", "LineUp_Address_Id", "LineUp" }; for (int i = 0; i < strAddTemplateColumns.Count(); i++) { if (dtRawData.Columns.Contains(strAddTemplateColumns[i])) { hdnTemplateType.Value = "Add/DeleteExportTemplate"; } } if (string.IsNullOrEmpty(hdnTemplateType.Value)) { hdnTemplateType.Value = "DataFeedTemplate"; } } string[] FileDetails = new string[10]; FileDetails[0] = strFileName; if (Session["UserName"] != null) { FileDetails[1] = Session["UserName"].ToString(); } else { FileDetails[1] = "Admin"; } FileDetails[2] = DateTime.Now.ToString(); FileDetails[3] = dtRawData.Rows.Count.ToString(); ShowUploadedDetails(FileDetails); } } else { lblErrorMessage.Text = "Please upload only .xls or .xlsx file format."; } } } catch (Exception ex) { throw; } }
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)