using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.IO; using System.Data.OleDb; using System.Data; using System.Collections; using System.Configuration; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page { // Connection Establishment to the admin database SqlConnection sqlcAdminConn = new SqlConnection(ConfigurationManager.ConnectionStrings["adminConnectionString"].ConnectionString); // variable for Excel Connection string connectionString = ""; protected void Page_Load(object sender, EventArgs e) { } // Button Click Event protected void btnUpload_Click(object sender, EventArgs e) { // Variables to throw Exceptions manually int a,b=100,c=0; // Condition to check File availability if (FileUpload1.HasFile) { // Do not display warnibg when file is available lblWarning.Visible = false; string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName); string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName); string fileLocation = Server.MapPath("~/App_Data/" + fileName); FileUpload1.SaveAs(fileLocation); //Check whether file extension is xls or xslx try { // Condition to check File extension/version for 1997-2003 if (fileExtension == ".xls") { // Excel Connection string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; GridBinding(); } // Condition to check File extension/version for slsx else if (fileExtension == ".xlsx") { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; GridBinding(); } // File Extension other than xls/xlsx else { // Exprssion to throw Exception a = b / c; } //String strConnection = "Data Source=172.16.100.42;Initial Catalog=MySamplesDB;Integrated Security=True"; sqlcAdminConn.Open(); //file upload path string path = FileUpload1.PostedFile.FileName; //Create connection string to Excel work book string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False"; //Create Connection to Excel work book OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); //Create OleDbCommand to fetch data from Excel [StaffCode],[Date],[FirstIn],[LastOut],[TotalHrsFILO] OleDbCommand cmdExcel = new OleDbCommand("Select * from [Sheet2$] where [Language]='EN' ", excelConnection); try { //Reader to load content from Excel sheet OleDbDataReader dReader; excelConnection.Open(); dReader = cmdExcel.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(sqlcAdminConn); //Give your Destination table name sqlBulk.DestinationTableName = "admin.dbo.SwipeCardData"; sqlBulk.WriteToServer(dReader); excelConnection.Close(); lblWarning.Text = "File Uploaded Successfully"; lblWarning.Visible = true; } // if the data is already available catch (Exception ex) { lblWarning.Text = ex.Message.ToString(); //"Data Already Uploaded for the Day"; lblWarning.Visible = true; } } // if the file format is other than xls/xlsx catch (Exception exFileFormat) { lblWarning.Text = "File Compatibility Issue Select xls/xlsx format"; lblWarning.Visible = true; } } // If the file is not prasent file upload control else { // warning message to display if file is not available lblWarning.Text = "* Select one Excel File to be Uploaded "; lblWarning.Visible = true; } } // Code to bind the grid view if data is available in xlx/xlsx file public void GridBinding() { //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 [Sheet2$]"; dAdapter.SelectCommand = cmd; dAdapter.Fill(dtExcelRecords); con.Close(); GridView1.DataSource = dtExcelRecords; GridView1.DataBind(); } }
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)