protected void btnup_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
if (System.IO.Path.GetExtension(FileUpload1.FileName) == ".xls" || System.IO.Path.GetExtension(FileUpload1.FileName) == ".xlsx")
{
FileUpload1.SaveAs(Server.MapPath("~/Excal/sample.xlsx"));
}
}
}
protected void Button2_Click(object sender, EventArgs e)
{
string sSourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\sample.xlsx; Extended Properties=""Excel 12.0;HDR=YES;""";
string sDestConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
using (sSourceConnection)
{
string sql = string.Format("Select [ID],[Name],[Designation] FROM [{0}]", "sample$");
OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
sSourceConnection.Open();
using (OleDbDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
{
bulkCopy.DestinationTableName = "rr";
//You can mannualy set the column mapping by the following way.
//bulkCopy.ColumnMappings.Add("MSISDN", "MSISDN");
bulkCopy.WriteToServer(dr);
}
}
}
lblmsg.Text = "Record update";
}
-
1and what is the problem with this?codingbiz– codingbiz2012-10-08 09:34:22 +00:00Commented Oct 8, 2012 at 9:34
Add a comment
|
1 Answer
I have done that before few day. Below code works for me ..
try
{
string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + Path of File + ";" +
@"Extended Properties=""Excel 12.0 Xml;HDR=Yes""";
//Create Connection to Excel work book
OleDbConnection excelConnection =
new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand
("Select * from [Sheet1$]",
excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "ExcelTable"; // write your table name
//sqlBulk.ColumnMappings.Add("ID", "ID");
//sqlBulk.ColumnMappings.Add("Name", "Name");
sqlBulk.WriteToServer(dReader);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
