While uploading excel getting error as There is no row at position 0

Question

I am trying to upload an excel with one row. But with below code, I am getting error as

There is no row at position 0

Below is the code for the same.

protected void btnOWNUpload_Click(object sender, EventArgs e)
    {
        string conStr = ""; string strFilePath = "";
        DataTable dtExcelRows = new DataTable();

        HttpPostedFile file = fluSapID_OWN.PostedFile;
        string FileNameWithoutExt = string.Empty;
        string FileExt = string.Empty;
        string type = String.Empty; string fname;
        string filename = Path.GetFileName(fluSapID_OWN.FileName);

        string filenamewithoutrext = string.Empty;
        FileExt = Path.GetExtension(fluSapID_OWN.FileName).ToLower();
        if (Path.GetExtension(fluSapID_OWN.FileName).ToLower() != ".xls" &&
            Path.GetExtension(fluSapID_OWN.FileName).ToLower() != ".xlsx"
        )
        {

            Response.Write("Only .xls, .xlsx are allowed.!");
            return;
        }

        string path = Server.MapPath("\\");
        string extension = Path.GetExtension(file.FileName);

        switch (extension)
        {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }

        if (!fluSapID_OWN.HasFile)
        {
            string script = "alert(\"Please Select File.!\");";
            ScriptManager.RegisterStartupScript(this, GetType(), "ServerControlScript", script, true);
        }
        else if (fluSapID_OWN.HasFile)
        {
            switch (extension)
            {
                case ".xlsx":
                    type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    break;

                case ".xls":
                    type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    break;
            }
        }

        fname = path + filename;
        conStr = String.Format(conStr, fname, "YES");

        System.Data.OleDb.OleDbConnection connExcel = new System.Data.OleDb.OleDbConnection(conStr);
        System.Data.OleDb.OleDbCommand cmdExcel = new System.Data.OleDb.OleDbCommand();
        System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter();
        cmdExcel.Connection = connExcel;

        connExcel.Open();

        System.Data.DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
        System.Data.DataTable dtExcelColumnsTable = connExcel.GetSchema("Columns");            
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString().Replace('\'', ' ').Trim();  // here is the error for no row at position 0
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dtExcelRows);
        connExcel.Close();

       // GetDataFromExcelEnodeB(path, extension, filename, 2, "OWN", CurrentUserName);
        if (dtExcelRows.Rows.Count > 0 && dtExcelRows != null)
        {
            validateExcelDataEnodebOWN(dtExcelRows, dtExcelColumnsTable, filename, 2, "OWN", CurrentUserName);
        }
        
    }

Answer 1

I didn't notice the connection string in the OP, so it's not clear if the issue is with the connection string. Any objects that have a Dispose method, should be disposed of. A using statement can be used to achieve this.

Try the following - it was tested using a Windows Forms App, but you can modify it for your usage.

Excel file:

GetSheetName:

private string GetSheetName(string filename)
{
    string sheetName = string.Empty;
    string connectionStr = string.Empty;
    string dataDirectory = Path.GetDirectoryName(filename);

    if (Path.GetExtension(filename) == ".xls")
        //connectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = {filename};Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"""; //Jet is 32-bit only
        connectionStr = $@"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = {filename};Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""";
    else if (Path.GetExtension(filename) == ".xlsx")
        connectionStr = $@"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = {filename};Extended Properties=""Excel 12.0;HDR=NO;IMEX=1""";
    else
        throw new Exception("Excel format not supported");

    Debug.WriteLine($"connectionStr: '{connectionStr}'");

    using (OleDbConnection conn = new OleDbConnection(connectionStr))
    {
        conn.Open();

        System.Data.DataTable dtExcelSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
        System.Data.DataTable dtExcelColumnsTable = conn.GetSchema("Columns");

        Debug.WriteLine("GetOleDbSchemaTable");
        for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
        {
            //create reference
            DataRow dr = dtExcelSchema.Rows[i];

            foreach (DataColumn col in dtExcelSchema.Columns)
            {
                Debug.WriteLine($"[{i}][\"{col.ColumnName}\"]: {dr[col]?.ToString()}");
            }
        }

        Debug.WriteLine("GetSchema");
        for (int i = 0; i < dtExcelColumnsTable.Rows.Count; i++)
        {
            //create reference
            DataRow dr = dtExcelColumnsTable.Rows[i];

            foreach (DataColumn col in dtExcelColumnsTable.Columns)
            {
                Debug.WriteLine($"[{i}][\"{col.ColumnName}\"]: {dr[col]?.ToString()}");
            }
        }

        sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString().Replace('\'', ' ').Trim();

        Debug.WriteLine($"SheetName: {sheetName}");
    }

    return sheetName;
}