Insert Excel to Sql Database for asp.net
Insert Excel to Sql Database for asp.netcoding plzzzz
You must Sign In to post a response.
Category: ASP.NET
coding plzzzz
#240204
http://support.microsoft.com/kb/319951/EN-US/ - It may help you
#240206
you can insert values from excel to sql
first you have to store excel data in datatable,
you can store data in datatable as
string constring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MySpreadsheet.XLS;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
datatable dt=new datatable();
Oledbconnection con=new oledbconnection(constring);
oledbdataadapter da=new oledbdataadapter("select * from sheet1",con)
da.fill(dt);
now you can store datatable data into sql using for/while loop.
Regards,
Sujit Kumar
first you have to store excel data in datatable,
you can store data in datatable as
string constring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MySpreadsheet.XLS;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
datatable dt=new datatable();
Oledbconnection con=new oledbconnection(constring);
oledbdataadapter da=new oledbdataadapter("select * from sheet1",con)
da.fill(dt);
now you can store datatable data into sql using for/while loop.
Regards,
Sujit Kumar
#240229
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69818
// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft
.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
Properties=""Excel 8.0;HDR=YES;""";
// Create Connection to Excel Workbook
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select ID,Data FROM [Data$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=.;
Initial Catalog=Test;Integrated Security=True";
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "ExcelData";
bulkCopy.WriteToServer(dr);
}
}
}
// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft
.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
Properties=""Excel 8.0;HDR=YES;""";
// Create Connection to Excel Workbook
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select ID,Data FROM [Data$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=.;
Initial Catalog=Test;Integrated Security=True";
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "ExcelData";
bulkCopy.WriteToServer(dr);
}
}
}
#240237
hi muthu,
check this one:
excel to sql and vice versa:
Bind/Export Gridview data to EXCEL file---- ON button click:
protected void btnsubmit_Click(object sender, EventArgs e)
{
grdsqldata.PageSize = Convert.ToInt32(ViewState["rows"]);
grdsqldata.DataBind();
GridViewExportUtil.Export("Book1.xls",this.grdsqldata);
}
public static void Export(string fileName, GridView grd)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a form to contain the grid
Table table = new Table();
table.GridLines = grd.GridLines;
// add the header row to the table
if (grd.HeaderRow != null)
{
//GridViewExportUtil.PrepareControlForExport(grd.HeaderRow);
table.Rows.Add(grd.HeaderRow);
table.GridLines = grd.GridLines;
table.BackColor = System.Drawing.Color.Beige;
}
// add each of the data rows to the table
foreach (GridViewRow row in grd.Rows)
{
//GridViewExportUtil.PrepareControlForExport(row);
table.Rows.Add(row);
}
// add the footer row to the table
if (grd.FooterRow != null)
{
table.GridLines = grd.GridLines;
//GridViewExportUtil.PrepareControlForExport(grd.FooterRow);
table.Rows.Add(grd.FooterRow);
}
// render the table into the htmlwriter
table.RenderControl(htw);
// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:
// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
// Create Connection to Excel Workbook
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select ID,Data FROM [Data$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "Sqltablename";
bulkCopy.WriteToServer(dr);
}
}
}
check this one:
excel to sql and vice versa:
Bind/Export Gridview data to EXCEL file---- ON button click:
protected void btnsubmit_Click(object sender, EventArgs e)
{
grdsqldata.PageSize = Convert.ToInt32(ViewState["rows"]);
grdsqldata.DataBind();
GridViewExportUtil.Export("Book1.xls",this.grdsqldata);
}
public static void Export(string fileName, GridView grd)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a form to contain the grid
Table table = new Table();
table.GridLines = grd.GridLines;
// add the header row to the table
if (grd.HeaderRow != null)
{
//GridViewExportUtil.PrepareControlForExport(grd.HeaderRow);
table.Rows.Add(grd.HeaderRow);
table.GridLines = grd.GridLines;
table.BackColor = System.Drawing.Color.Beige;
}
// add each of the data rows to the table
foreach (GridViewRow row in grd.Rows)
{
//GridViewExportUtil.PrepareControlForExport(row);
table.Rows.Add(row);
}
// add the footer row to the table
if (grd.FooterRow != null)
{
table.GridLines = grd.GridLines;
//GridViewExportUtil.PrepareControlForExport(grd.FooterRow);
table.Rows.Add(grd.FooterRow);
}
// render the table into the htmlwriter
table.RenderControl(htw);
// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:
// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
// Create Connection to Excel Workbook
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select ID,Data FROM [Data$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "Sqltablename";
bulkCopy.WriteToServer(dr);
}
}
}
#285805
First execute this procedure
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
After executing procedure then write the query
Insert into dbo.tableName
select * from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;database=C:\contact.xls',
'select * from [Sheet1$]‘)
Regards,
Sujit Kumar
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
After executing procedure then write the query
Insert into dbo.tableName
select * from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;database=C:\contact.xls',
'select * from [Sheet1$]‘)
Regards,
Sujit Kumar
Return to Return to Discussion Forum