You can get data from Excel like database using
System.Data.OleDb
;
using System.Data.OleDb;
public DataSet ImportFromExcel(string file)
{
DataSet ds = new DataSet();
Dictionary<string, string> props = new Dictionary<string, string>();
if (file.EndsWith(".xlsx"))
{
props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
props["Extended Properties"] = "Excel 12.0 XML";
}
else if (file.EndsWith(".xls"))
{
props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
props["Extended Properties"] = "Excel 8.0";
}
else
return null;
props["Data Source"] = file;
StringBuilder sb = new StringBuilder();
foreach (KeyValuePair<string, string> prop in props)
{
sb.Append(prop.Key);
sb.Append('=');
sb.Append(prop.Value);
sb.Append(';');
}
string connectionString = sb.ToString();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow dr in dtSheet.Rows)
{
string sheetName = dr["TABLE_NAME"].ToString();
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
DataTable dt = new DataTable();
dt.TableName = sheetName.Replace("$", string.Empty);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
ds.Tables.Add(dt);
}
cmd = null;
conn.Close();
}
return ds;
}
Then, you can dump the data into GridView like this:
void LoadDataIntoGridView()
{
string file = "C:\\MyExcel.xlsx";
System.Data.DataSet ds = ImportFromExcel(file);
foreach (System.Data.DataTable dt in ds.Tables)
{
GridView gv = new GridView();
gv.DataSource = dt;
gv.AutoGenerateColumns = true;
gv.DataBind();
Panel1.Controls.Add(gv);
}
}
About inserting data into SQL database. You need to learn SQL database.
You will not be able to work properly with SQL database without knowing the basic of it:
- How to connect to a SQL database
- How to CREATE DATABASE and CREATE TABLE
- How to perform the basic operations of SELECT, INSERT, UPDATE, DELETE
Example of CREATE TABLE:
CREATE TABLE `department`(
id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
name varchar(50) NOT NULL,
dateformation datetime NULL
)
Example of INSERT:
INSERT INTO department(id,name,dateformation)VALUES(1,'Sales','2012-10-27 09:00');
Tutorials of SQL database can be easily found every where.