There's a few different ways of doing this depending on what your circumstances are.
Is there a folder where Excel files saved and then must be uploaded? Do your users select the file to be uploaded? You could use SQL server and a DTS package to upload the data from the Excel file to the table. What's your scenario?
Please look here:
http://support.microsoft.com/kb/319951/EN-US/
or here
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.
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);
}
}
}
or this
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.grdsqld ata);
}
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.Hea derRow);
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.Foo terRow);
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();
}
}
}
or this
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);
}
}
}