Database storage

Jagadeeshwar Reddy

Sir,

I want know how to store data into excel spread sheet in ASP.NET? and Excel spread sheet to SQL Server database..?

7 Replies

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

Bharathi Raja8y ago
http://www.c-sharpcorner.com/article/import-excel-data-into-sql-table-using-sqlbulkcopy-in-c-sharp/
Rafnas T P9y ago
hi,

please refer below link to insert data from excel into sql server database, it may help

http://www.c-sharpcorner.com/article/import-excel-data-into-sql-table-using-sqlbulkcopy-in-c-sharp/
SARDAR MUKHTAR9y ago
1. Get Data from Excel and store into Datatabele
-----------------------------
using System.Data.OleDb;
-------------
private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
DataTable dt = new DataTable();
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
string filePath = openFileDialog1.FileName;
string extension = Path.GetExtension(filePath);
string header = "YES";// rbHeaderYes.Checked ? "YES" : "NO";
string conStr, sheetName;
conStr = string.Empty;
switch (extension)
{
case ".xls": //Excel 97-03
conStr = string.Format(Excel03ConString, filePath, header);
break;
case ".xlsx": //Excel 07
conStr = string.Format(Excel07ConString, filePath, header);
break;
}
//Get the name of the First Sheet.
using (OleDbConnection con = new OleDbConnection(conStr))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = con;
con.Open();
DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
con.Close();
}
}
//Read Data from the Sheet.
using (OleDbConnection con = new OleDbConnection(conStr))
{
using (OleDbCommand cmd = new OleDbCommand())
{
using (OleDbDataAdapter oda = new OleDbDataAdapter())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * from [" + sheetName + "]";
cmd.Connection = con;
con.Open();
oda.SelectCommand = cmd;
oda.Fill(dt);
con.Close();

}
}
}
}
Jagadeeshwar Reddy9y ago
Mr.
Nitin Sontakke sir I wanted to know that because that is asked in some interviews as machine test.

Thank you very much Mr.
Vignesh Mani sir and
Bikesh Srivastava sir for ur valuable repleis.
Bikesh Srivastava9y ago
Firstly upload excel and get in object or datatable and then apply operation.
Nitin Sontakke9y ago
You can do anything.

However, just curious, as to why such a convoluted approach to data storage?
Vignesh Mani9y ago
http://www.c-sharpcorner.com/UploadFile/75a48f/insert-data-into-excel-by-using-asp-net/

http://www.c-sharpcorner.com/UploadFile/0c1bb2/inserting-excel-file-records-into-sql-server-database-using/

Database storage

Nitin Sontakke sir I wanted to know that because that is asked in some interviews as machine test.

Vignesh Mani sir and

Bikesh Srivastava sir for ur valuable repleis.