Reading Excel File in C#, ASP.Net

Try below code....

if (txtVName1.Text != string.Empty && cmbVid1.SelectedIndex != 0)
           {
               string input = string.Empty;
               OpenFileDialog p1 = new OpenFileDialog();
               p1.Title = "Select an Excel File..";
               p1.Filter = "EXCEL files (*.xls)|*.xls|All files (*.*)|*.*";
               p1.InitialDirectory = "c:";
               try
               {
                   if (p1.ShowDialog() == DialogResult.OK)
                   {
                       input = p1.FileName;
                       if (input != string.Empty)
                       {

                           OleDbConnection oledbcnn = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + p1.FileName + "';Extended Properties=Excel 8.0;");
                           DataSet dsoledb = new DataSet();
                           oledbcnn.Open();
                           OleDbDataAdapter daoledb = new OleDbDataAdapter();
                           DataTable dt = oledbcnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                           daoledb.TableMappings.Add("Table", "Hello");
                           daoledb = new OleDbDataAdapter(@"select * from [Sheet1$]", oledbcnn);
                           daoledb.Fill(dsoledb);
                           if (dsoledb.Tables[0].Columns[0].ColumnName == "S No" && dsoledb.Tables[0].Columns[1].ColumnName == "MacId")
                           {
                               dsoledb.Tables[0].Columns.Add("Key", typeof(string));
                               dsoledb.Tables[0].Columns.Add("Status", typeof(string));
                               dsoledb.Tables[0].Columns.Add("Decrypted MacId", typeof(string));
                               dataGridView1.DataSource = dsoledb.Tables[0];

                           }
                           else
                           {
                               MessageBox.Show("File Format Does Not Match \n File Should have Two Columns Named \"S No\" and \"MacId\" ", "RSBY 64k Key Generation");
                               return;

                           }
                       }
                   }
               }
               catch (Exception ex)
               {
                   MessageBox.Show(ex.Message);
               }

have this code

string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName +
                             ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";

var output = new DataSet();

using (var conn = new OleDbConnection(strConn))
{
    conn.Open();

    var dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

    foreach (DataRow row in dt.Rows)
    {
        string sheet = row["TABLE_NAME"].ToString();

        var cmd = new OleDbCommand("SELECT * FROM [+"+sheet+"+]", conn);
        cmd.CommandType = CommandType.Text;

        OleDbDataAdapter xlAdapter = new OleDbDataAdapter(cmd);

        xlAdapter.Fill(output,"School");
    }
}

or like this

var fileName = @"C:\ExcelFile.xlsx";
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""; ;
using (var conn = new OleDbConnection(connectionString))
{
    conn.Open();

    var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";

        var adapter = new OleDbDataAdapter(cmd);
        var ds = new DataSet();
        adapter.Fill(ds);
    }
}

string connectionString = "";
if (FileUpload.HasFile)
{
string fileName = Path.GetFileName(FileUpload.PostedFile.FileName);
string fileExtension = Path.GetExtension(FileUpload.PostedFile.FileName);
string fileLocation = Server.MapPath("~/upload/" + fileName);
FileUpload3.SaveAs(fileLocation);
//Check whether file extension is xls or xslx
if (fileExtension == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}

//Create OleDB Connection and OleDb Command
try
{
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
dAdapter.SelectCommand = cmd;
dAdapter.Fill(dtExcelRecords);

OleDbDataReader odr = cmd.ExecuteReader();
string usr = "";
string name = "";
string surname = "";

while (odr.Read())
{
usr = valid(odr, 1); // in the column 1 we find the user
string name = valid(odr, 2); // in the column 2 we find the name of the user
string surname = valid(odr, 3); // in the column 3 we find the surname of the user
Insert(usr, name, surname); // put the data in the database with your custom function Insert
}
con.Close();
}
catch (DataException ex)
{
lblResponse.Text = ex.Message;
}
}





Reference :-

Read Excel File into DataSet in ASP.NET Using C#[^]

OleDbConnection cnn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(@"~\data\cocustomerdetails.xlsx") + "; Extended Properties=Excel 12.0;");

OleDbCommand oconn = new OleDbCommand("select * from [Sheet1$]", cnn);
cnn.Open();
OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
DataTable dt = new DataTable();
adp.Fill(dt);

then bind the data table with data grid or other control...


you can visit on the page for more detail



Read Excel Sheet Data into DataTable[^]