How can read excel sheet 2016 using c# code

Question

I am reading excel sheet using below code but that gives blank data table.

  public static DataTable ReadExcel(string fileName)
    {
        string fileExt = ".xlsx";
        string conn = string.Empty;
        DataTable dtexcel = new DataTable();
        if (fileExt.CompareTo(".xlsx") == 0)
            conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007  
        else
            conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0 Xml;HDR=YES';"; //for above excel 2007  
        using (OleDbConnection con = new OleDbConnection(conn))
        {
            try
            {
                OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1  
                oleAdpt.Fill(dtexcel); //fill excel data into dataTable  
            }
            catch(Exception ex) { }
        }
        return dtexcel;
    }

It displays empty data table as below screenshot.

@RajeshVerma You manually setting file extension to `.xlsx` use [Path.GetExtension](https://msdn.microsoft.com/en-us/library/system.io.path.getextension(v=vs.110)) instead. Which version of a excel file you are testing? probably you are tesing it against 2007 or later file — huMpty duMpty, Mar 19 '18 at 11:47
@RajeshVerma Are you sure, because you're catching the exception and not logging it so you might be missing it? — juharr, Mar 19 '18 at 11:47
is file you are passing exists i dont see any check for that — Pranay Rana, Mar 19 '18 at 11:47
You assign `fileExt = ".xlsx";` and later check for the value you assigned `if (fileExt.CompareTo(".xlsx") == 0)`. Looks weird. Why dont you check if the filename ends with `fileExt`??? — Cleptus, Mar 19 '18 at 11:48
looks like a simple typo. Change `if (fileExt.CompareTo(".xlsx") == 0)` for `if (fileName.EndsWith(fileExt))` — Cleptus, Mar 19 '18 at 11:50
I change as above suggestions given by all of you now it gives error "External table is not in the expected format." — RajeshVerma, Mar 19 '18 at 12:00
@RajeshVerma: Try to look around for similar issues. see [Excel “External table is not in the expected format.”](https://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format) — huMpty duMpty, Mar 19 '18 at 14:26

score 0 · Answer 1 · answered Mar 19 '18 at 12:15

you forgot few things, like OleDbConnection.Open(); and using OleDbCommand

public DataTable ReadExcel(string fileName)
        {
            string fileExt = ".xlsx";
            string sheetName = "Sheet1$";
            string conn = string.Empty;
            DataTable dt = new DataTable();
            if (fileExt.CompareTo(".xlsx") != 0)
                conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007  
            else
                conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0 Xml;HDR=YES';"; //for above excel 2007  
            using (OleDbConnection con = new OleDbConnection(conn))
            using ( OleDbCommand cmd = new OleDbCommand())            
            {
                con.Open();
                try
                {
                    cmd.Connection = con;                   
                    cmd.CommandText = "SELECT * FROM [" + sheetName + "]";                     
                    dt.TableName = sheetName;

                    using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                    {
                        da.Fill(dt);
                    }

                }
                catch (Exception ex) { }
            }
            return dt;
        }

score 0 · Answer 2 · answered Mar 19 '18 at 12:20

try rhis code hope it help you

protected void btn_Click(object sender, EventArgs e)
    {

        string filename = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName);
        FileUpload1.SaveAs(Server.MapPath("File/" + filename));
        string CurrentFilePath = Server.MapPath("File/" + filename);
        string connectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + CurrentFilePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";
        OleDbConnection conn = new OleDbConnection(connectString);
        conn.Open();
        DataTable Sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        foreach (DataRow dr in Sheets.Rows)
        {
            string sht = dr[2].ToString().Replace("'", "");
            OleDbDataAdapter da = new OleDbDataAdapter("Select * From [" + sht + "]", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);

        }

    }

score 0 · Answer 3 · answered Mar 20 '18 at 17:33

For me working following code

 string filePath = AppDomain.CurrentDomain.BaseDirectory.Replace("\\bin\\Debug", "").Replace("\\bin\\Release", "");
                string fileLocation = filePath + ConfigurationManager.AppSettings["EmployeeDetailsFilePath"];
                Stream inputStream = File.Open(fileLocation, FileMode.Open, FileAccess.Read);
                IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(inputStream);
                reader.IsFirstRowAsColumnNames = true;
                DataSet dataSet = reader.AsDataSet();
                inputStream.Dispose();
                reader.Dispose();

here must add reference of ExcelDataReader dll in your project.

How can read excel sheet 2016 using c# code

3 Answers3