Connecting Sql with micrososft excell

am thinking of developing a system with c# and sql and excell , A user may use the system during the day when out of office but when he get in the office he just press the button update server,where by there might be other user in the office using the same system ,when he press the button the id from the continue from the last id ,is it possible and if possible where can i get the assistance or how can i do it?

//bring Excel sheets into your grid

//code

using System.Data.OleDb; //we have to create OleDb connection for Excel

using System.Data.SqlClient;

private void btnBrowse_Click(object sender, EventArgs e)

{

try

{

string strNewPath ="C:\\MonthlyStatus_BasicReport JUNE 2012.xls";

string strFileName="";

OpenFileDialog dlgPhotograph = new OpenFileDialog();

dlgPhotograph.Filter = "All files (*.*)|*.*|All files (*.*)|*.*";

dlgPhotograph.FilterIndex = 6;

dlgPhotograph.Multiselect = false;

dlgPhotograph.ShowReadOnly = false;

dlgPhotograph.Title = "Please select an Excel file";

dlgPhotograph.ValidateNames = true;

if (dlgPhotograph.ShowDialog(this) == DialogResult.OK)

{

System.IO.FileInfo fileObj = new System.IO.FileInfo(dlgPhotograph.FileName);

string path = "C:\\MonthlyStatus_BasicReport JUNE 2012.xls";

strNewPath = "C:\\MonthlyStatus_BasicReport JUNE 2012.xls";

//String strFileName = Server.MapPath("INZAXIS/") + DateTime.Now.ToString("dd_MMM_yyyy_HH_mm_ss_tt");

String strFileType = System.IO.Path.GetExtension(dlgPhotograph.FileName).ToString().ToLower();

if (strFileType == ".xls" || strFileType == ".xlsx")

{

string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

OleDbDataAdapter adap = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connString);

DataSet ds = new DataSet();

adap.Fill(ds, "MonthlyStatus_BasicReport JUNE 2012");

dataGridView1.DataSource = ds.Tables["MonthlyStatus_BasicReport JUNE 2012"].DefaultView;

MessageBox.Show("Upload excel file successfull");

}

catch (Exception ee)

{

//bslLogErrors.CreateLogErrorFile("frmEmployeePhoto", "btnSelect_Click", ee);

}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Kumar9y ago
Hi Dear,
Please follow the below link.

https://www.simple-talk.com/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc/
Panna Chatterjee9y ago
//bring Excel sheets into your grid
//code
using System.Data.OleDb; //we have to create OleDb connection for Excel
using System.Data.SqlClient;
private void btnBrowse_Click(object sender, EventArgs e)
{
try
{
string strNewPath ="C:\\MonthlyStatus_BasicReport JUNE 2012.xls";
string strFileName="";
OpenFileDialog dlgPhotograph = new OpenFileDialog();
dlgPhotograph.Filter = "All files (*.*)|*.*|All files (*.*)|*.*";
dlgPhotograph.FilterIndex = 6;
dlgPhotograph.Multiselect = false;
dlgPhotograph.ShowReadOnly = false;
dlgPhotograph.Title = "Please select an Excel file";
dlgPhotograph.ValidateNames = true;
//
if (dlgPhotograph.ShowDialog(this) == DialogResult.OK)
{
System.IO.FileInfo fileObj = new System.IO.FileInfo(dlgPhotograph.FileName);
string path = "C:\\MonthlyStatus_BasicReport JUNE 2012.xls";
strNewPath = "C:\\MonthlyStatus_BasicReport JUNE 2012.xls";
//String strFileName = Server.MapPath("INZAXIS/") + DateTime.Now.ToString("dd_MMM_yyyy_HH_mm_ss_tt");
String strFileType = System.IO.Path.GetExtension(dlgPhotograph.FileName).ToString().ToLower();
if (strFileType == ".xls" || strFileType == ".xlsx")
{
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
OleDbDataAdapter adap = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connString);
DataSet ds = new DataSet();
adap.Fill(ds, "MonthlyStatus_BasicReport JUNE 2012");
dataGridView1.DataSource = ds.Tables["MonthlyStatus_BasicReport JUNE 2012"].DefaultView;
MessageBox.Show("Upload excel file successfull");
}
}
}
catch (Exception ee)
{
//bslLogErrors.CreateLogErrorFile("frmEmployeePhoto", "btnSelect_Click", ee);
}
}
Pradeep Shet9y ago
Hi Carlos,

Yes its a nice idea ..go for it ..this is possible .. Let me know if you need any assistance.