It is Possible, though you need is to follow several steps,they are as following
Step 1 : Get the filepath using OpenFileDialog
var fd = new OpenFileDialog();
fd.Multiselect = false;
fd.Title = "Upload Excel File";
fd.ValidateNames = true;
fd.DefaultExt = "xls";
fd.Filter = "Excel Files (*.xls)|*.xls";
fd.FilterIndex = 1;
string fp = string.Empty;
fp = fd.FileName;
Step 2 : Make A query for eg:
select [column1],[column2],[column3],[column4],[column5] from [Sheet1$]
here "Sheet1$" is sheet1 from your excel.
Step 3: Store This Data in a DataReader, through which you can display it in both grid as well as store it in database.
here i have used OleDbDataReader.
string connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0';";
OleDbConnection con = new System.Data.OleDb.OleDbConnection(connection);
OleDbCommand cmd = new OleDbCommand(query, con);
con.Open();
var dr = cmd.ExecuteReader();
try
{
var bulkCopy = new SqlBulkCopy(_connectionString);
bulkCopy.DestinationTableName = "ExcelData";
bulkCopy.WriteToServer(dr);
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
here
"_connectionString" is the string in which you store your Data path
"ExcelData" is the name of table to store the data.
Note: 1)Your table should contain exact same columns as in the excel also the data type of the column in excel & table should be same.. you can view the type of data of excel column from "dr" while debugging.
2)Also You should have Microsoft office Access database engine 2007 installed in your machine. if not you may get it from this link
Download 2007 Office System Driver: Data Connectivity Components from Official Microsoft Download Center[
^]
3) check the version of excel too i have 97-2003 excel so i have used "Excel 8.0"
now for uploading the data Of this OleDbDataReader, just follow this link
How to Populate a DataGridView Control using OleDbDataReader[
^]