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;
}
}
}
}
