locked
ODBC Excel .xlsx driver RRS feed

  • Question

  • I am trying to access a spreadsheet that is currently homed on a Server that doesn't have excel installed on it...however it has an excel driver (Microsoft Excel Driver .xls). I am running Office 2013 on my computer that I am using to pull the spreadsheet onto. I need to find the correct driver to be able to open and make changes to the spreadsheet...Please help!

    • Moved by George123345 Wednesday, November 6, 2013 6:16 AM ODBC issue
    Tuesday, November 5, 2013 3:00 PM

Answers

  • Hello,

    From your description, if I understand correctly, you want to know how to read excel file.

    For this, my suggestion is to use the OleDb, we just need to install the Microsoft Access Database Engine

    And for how to read excel file using OleDb, there is a sample for it:

    DataTable dt = new DataTable();
    
    
                OleDbConnection objOleDbCon = null;
    
                DataTable dtSheetName = new DataTable();
    
    
                string filePath = "E:\\BMX\\Lab\\TestLab\\WindowsFormsApplication1\\ADONET\\DataSource.xlsx";
    
    
                string fileExt = System.IO.Path.GetExtension(filePath);
    
                string fileName = System.IO.Path.GetFileName(filePath);
    
                OleDbCommand objOleDbCmd = new OleDbCommand();
    
                OleDbDataAdapter objOleDbDataAdapter = new OleDbDataAdapter();
    
                DataTable dtExcelData = new DataTable();
    
    
                string xConnStr = "";
    
    
                if (fileExt.ToLower() == ".xls")
    
                {
    
    
                    xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
    
                }
    
                else if (fileExt.ToLower() == ".xlsx")
    
                {
    
                    xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
    
                }
    
    
                objOleDbCon = new OleDbConnection(xConnStr);
    
                objOleDbCon.Open();
    
                try
    
                {
    
                    objOleDbCmd = new OleDbCommand("SELECT * FROM [" + "Course" + "$]", objOleDbCon);
    
                    objOleDbDataAdapter = new OleDbDataAdapter(objOleDbCmd);
    
                    objOleDbDataAdapter.Fill(dt);
    
    
                }
    
                catch (Exception ex)
    
                {
    
                    throw ex;
    
                }
    
                finally { objOleDbCon.Close(); }
    
    
                return dt;
    

    If I have misunderstood, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Fred Bao Thursday, November 14, 2013 9:48 AM
    Thursday, November 7, 2013 8:07 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for ADO.NET Managed Providers

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataproviders

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support

    Wednesday, November 6, 2013 6:15 AM
  • Hello,

    From your description, if I understand correctly, you want to know how to read excel file.

    For this, my suggestion is to use the OleDb, we just need to install the Microsoft Access Database Engine

    And for how to read excel file using OleDb, there is a sample for it:

    DataTable dt = new DataTable();
    
    
                OleDbConnection objOleDbCon = null;
    
                DataTable dtSheetName = new DataTable();
    
    
                string filePath = "E:\\BMX\\Lab\\TestLab\\WindowsFormsApplication1\\ADONET\\DataSource.xlsx";
    
    
                string fileExt = System.IO.Path.GetExtension(filePath);
    
                string fileName = System.IO.Path.GetFileName(filePath);
    
                OleDbCommand objOleDbCmd = new OleDbCommand();
    
                OleDbDataAdapter objOleDbDataAdapter = new OleDbDataAdapter();
    
                DataTable dtExcelData = new DataTable();
    
    
                string xConnStr = "";
    
    
                if (fileExt.ToLower() == ".xls")
    
                {
    
    
                    xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
    
                }
    
                else if (fileExt.ToLower() == ".xlsx")
    
                {
    
                    xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
    
                }
    
    
                objOleDbCon = new OleDbConnection(xConnStr);
    
                objOleDbCon.Open();
    
                try
    
                {
    
                    objOleDbCmd = new OleDbCommand("SELECT * FROM [" + "Course" + "$]", objOleDbCon);
    
                    objOleDbDataAdapter = new OleDbDataAdapter(objOleDbCmd);
    
                    objOleDbDataAdapter.Fill(dt);
    
    
                }
    
                catch (Exception ex)
    
                {
    
                    throw ex;
    
                }
    
                finally { objOleDbCon.Close(); }
    
    
                return dt;
    

    If I have misunderstood, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Fred Bao Thursday, November 14, 2013 9:48 AM
    Thursday, November 7, 2013 8:07 AM