protected void Button2_Click(object sender, EventArgs e) { string srvFileLocation = ""; string fileName = "", fileExtension = ""; string connectionString = ""; if (FileUpload1.HasFile) { fileName = Path.GetFileName(FileUpload1.PostedFile.FileName); fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName); string fileLocation = Server.MapPath("~/App_Data/" + fileName); FileUpload1.SaveAs(fileLocation); Label1.Text = "File Uploaded: " + FileUpload1.FileName; //Check whether file extension is xls or xslx if (fileExtension == ".xls") { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } else if (fileExtension == ".xlsx") { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } // String cn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=C:\\Users\\Charu\\Documents;" +"Extended Properties=Excel 8.0;"; //Create OleDB Connection and OleDb Command OleDbConnection con = new OleDbConnection(connectionString); OleDbCommand cmd = new OleDbCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = con; OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd); DataTable dtExcelRecords = new DataTable(); con.Open(); DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString(); cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]"; dAdapter.SelectCommand = cmd; dAdapter.Fill(dtExcelRecords); con.Close(); GridView1.DataSource = dtExcelRecords; GridView1.DataBind(); } else { Label1.Text = "No File Uploaded."; } if (FileUpload1.HasFile) { fileName = Path.GetFileName(FileUpload1.PostedFile.FileName); fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName); srvFileLocation = Server.MapPath("~/App_Data/" + fileName); FileUpload1.SaveAs(srvFileLocation); } string excelConnectionString = ""; if (fileExtension == ".xls") { excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + srvFileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } else if (fileExtension == ".xlsx") { excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + srvFileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } //string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + srvFileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; // Create Connection to Excel using (OleDbConnection connection = new OleDbConnection(excelConnectionString)) { OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection); connection.Open(); // Create DbDataReader to Data Worksheet using (DbDataReader dr = command.ExecuteReader()) { // SQL Server Connection String string sqlConnectionString = "Data Source=SNSWAMY\\SQLEXPRESS;Initial Catalog=payroll;Persist Security Info=True;User ID=avoc;Password=avoc123"; // Bulk Copy to SQL Server using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString)) { //bulkCopy.ColumnMappings.Add("process_datetime", "process_datetime"); bulkCopy.DestinationTableName = "leave_tran"; bulkCopy.WriteToServer(dr); } } } }
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)