can anyone plz share the idea , how should i proceed,

bobbhai · Apr 4, 2015 8:00 PM

Bro, explain it clearly (with example if applicable). I have some experience, I might be able to help if I understand the question .

Fuel Tank · Apr 4, 2015 10:42 PM

I have three column in excel. No third one i have some formula that will give calculate the value. Lets say I use to formula to add first 2 column and get the result in third. Now i have to connect this excel sheet to sql server. i need to get the excel data to sql server.. Now i concern is, i need to record the change in excel to sql , i also need to record the change in formula , that user used to calculate the value. I did connect the excel to sql , but with some reason its not working , also i couldn;t find the connection string of excel to sql. Here is my code so far i have done. using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; using System.IO; using System.Drawing; namespace ExcelUpload { public partial class Excel_Upload : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnUpload_Click(object sender, EventArgs e) { if (FileUpload1.HasFile)// this will check and ask to upload the file.HASFile is boolean value and only return true or false. { // to allow user to enter only excel file, use the extension method string fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName);// system.Io has extension. filename will gave the path for extension. if (fileExtension.ToLower() != ".xlsm" && fileExtension.ToLower() != ".xlsx")// convert the extesion to lower casee ..its to avoid the user mix behaviour fo using the extension { { lbUpload.Text = "Only .xlsm and xlsx file are Allowed."; lbUpload.ForeColor = System.Drawing.Color.Green; } else { //if we want to restrict the file size ..contacnt length is in byte. if we want to 1024*1024 bytes = 1048576 bytes=1MB int filesize = FileUpload1.PostedFile.ContentLength; if (filesize > 5242880) // 5MB max { lbUpload.Text = " Maximun files size . %MB exceeded"; lbUpload.ForeColor = System.Drawing.Color.Red; } else { FileUpload1.SaveAs(Server.MapPath("~/Upload/" + FileUpload1.FileName)); // connection to sql SqlConnection con = new SqlConnection("provideer=Microsoft.Ace.SqlDB.12.0; Data Source=‪"+C:\Users\baburam\Desktop\GPS Excel.xlsx+";Extended propertities=Excel_Gridview 12.0; HDR=Yes"); SqlCommand cmd = new SqlCommand("select * from[Sheet1$]",con); SqlDataAdapter objAdapter = new SqlDataAdapter(cmd); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); string con_str = @"datasource=.;database=GPS_Demo;integrated security =True"; SqlBulkCopy bulkinsert = new SqlBulkCopy(con_str); bulkinsert.DestinationTableName = "Customer"; bulkinsert.WriteToServer(rdr); con.Close() ; Array.ForEach(Directory.GetFiles((Server.MapPath("~/Upload/"))), File.Delete); lbUpload.Text = "File Uploaded"; lbUpload.ForeColor = System.Drawing.Color.Green; } } } else { lbUpload.Text = "Please Select the File"; lbUpload.ForeColor = System.Drawing.Color.Red; } } } }

bobbhai · Apr 5, 2015 11:44 AM

how do i use connection string to excel to sql .. // connection to sql SqlConnection con = new SqlConnection("provideer=Microsoft.Ace.SqlDB.12.0; Data Source=‪"+C:\Users\baburam\Desktop\GPS Excel.xlsx+";Extended propertities=Excel_Gridview 12.0; HDR=Yes"); SqlCommand cmd = new SqlCommand("select * from[Sheet1$]",con);

bobbhai · Apr 5, 2015 11:45 AM

Check provider string... Why is it provideer? And why do u have a +sign in your source ..? Use @.. Also is your first row column names since u have HDR=yes..?.

alece · Apr 5, 2015 5:59 PM

i did different approach. with this i got an error "Could not find installable ISAM".. string path = string.Concat((Server.MapPath("~/Upload/" + FileUpload1.FileName))); FileUpload1.PostedFile.SaveAs(path); OleDbConnection con = new OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=‪" + path + ";Extended Propertities=Excel 12.0"); OleDbCommand cmd = new OleDbCommand("select * from[Sheet1$]", con); OleDbDataAdapter objAdapter = new OleDbDataAdapter(cmd);

bobbhai · Apr 5, 2015 8:56 PM

why don't u try something like this? string path = @"C:\Desktop\test.xlsx"; string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;"; SqlCommand cmd = new SqlCommand("select * from[Sheet1$]",conStr);

alece · Apr 6, 2015 9:01 AM