dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: Read from excel file

  1. #1
    Join Date
    Feb 2005
    Posts
    568

    Read from excel file

    How to read data from excel file??
    i read an example using ODBC but it need to set table name in the excel file before it function. can i set from c++??

  2. #2
    Join Date
    May 2007
    Posts
    437

    Re: Read from excel file

    ashu
    always use code tag

  3. #3
    Join Date
    Feb 2005
    Posts
    568

    Re: Read from excel file

    Quote Originally Posted by ashukasama
    this example is launching the exel.exe
    but i would prefer it show the data in my application.

  4. #4
    Join Date
    Aug 2002
    Location
    Dubai
    Posts
    1,183

    Re: Read from excel file

    Quote Originally Posted by lsy
    How to read data from excel file??
    i read an example using ODBC but it need to set table name in the excel file before it function. can i set from c++??
    These days you need to specify if it's VC6, VC7, VC8 and then if it's C++ CLI , MFC or anything else. Since you mentioned ODBC I guess you are using VC6 or older. There are two methods you can use here. You can open the Excel workbook using Excel automation and read the contents. The following code reads and displays the contents of cell "A1"

    Code:
    void CDialogTestDlg::OnButton1() {
         if(!AfxOleInit()) {
           AfxMessageBox("OLE init failed ");
            return;
         }   
        COleVariant   covOpt((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
        _Application app;  Workbooks books;
        _Workbook book; Worksheets sheets;
        _Worksheet sheet; Range range;
        LPDISPATCH lpDisp; 
        VARIANT v; 
        if(!app.CreateDispatch("Excel.Application"))   {
         AfxMessageBox("Failed.");
          return;
         }
         books = app.GetWorkbooks();
         book =  books.Open("c:\\Test\\test.xls" , 
                           covOpt , covOpt , covOpt ,
                           covOpt , covOpt , covOpt ,
                           covOpt , covOpt , covOpt ,
                           covOpt , covOpt , covOpt ,
                           covOpt,  covOpt);
         app.SetVisible(true);
         sheets = book.GetWorksheets();
         sheet = sheets.GetItem(COleVariant((short)1));
         range = sheet.GetRange( COleVariant("A1") , COleVariant("A1"));
         lpDisp = range.GetEnd(-4121);
          v  =  range.GetValue(covOpt);
         AfxMessageBox(_com_util::ConvertBSTRToString(v.bstrVal));     
     }
    or you can use ADO and open the Excel sheet as if it were a table in a database.

  5. #5
    Join Date
    Aug 2002
    Location
    Dubai
    Posts
    1,183

    Re: Read from excel file

    Quote Originally Posted by Sahir
    or you can use ADO and open the Excel sheet as if it were a table in a database.
    The connection string to be used in this case is

    "DRIVER=Microsoft Excel Driver (*.xls);DBQ=C:\\folder\\workbook.xls"

    and the SQL query should use the worksheet name in place of the table name. As in "Select * from [Sheet1$] "

  6. #6
    Join Date
    Feb 2005
    Posts
    568

    Re: Read from excel file

    Quote Originally Posted by Sahir
    These days you need to specify if it's VC6, VC7, VC8 and then if it's C++ CLI , MFC or anything else. Since you mentioned ODBC I guess you are using VC6 or older. There are two methods you can use here. You can open the Excel workbook using Excel automation and read the contents. The following code reads and displays the contents of cell "A1"

    Code:
    void CDialogTestDlg::OnButton1() {
         if(!AfxOleInit()) {
           AfxMessageBox("OLE init failed ");
            return;
         }   
        COleVariant   covOpt((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
        _Application app;  Workbooks books;
        _Workbook book; Worksheets sheets;
        _Worksheet sheet; Range range;
        LPDISPATCH lpDisp; 
        VARIANT v; 
        if(!app.CreateDispatch("Excel.Application"))   {
         AfxMessageBox("Failed.");
          return;
         }
         books = app.GetWorkbooks();
         book =  books.Open("c:\\Test\\test.xls" , 
                           covOpt , covOpt , covOpt ,
                           covOpt , covOpt , covOpt ,
                           covOpt , covOpt , covOpt ,
                           covOpt , covOpt , covOpt ,
                           covOpt,  covOpt);
         app.SetVisible(true);
         sheets = book.GetWorksheets();
         sheet = sheets.GetItem(COleVariant((short)1));
         range = sheet.GetRange( COleVariant("A1") , COleVariant("A1"));
         lpDisp = range.GetEnd(-4121);
          v  =  range.GetValue(covOpt);
         AfxMessageBox(_com_util::ConvertBSTRToString(v.bstrVal));     
     }
    or you can use ADO and open the Excel sheet as if it were a table in a database.
    if using the excel automation... it will launch the excel.exe...
    do there have any example for using ADO to open and retrieve the data?
    how the sql select statement will be?? how to define the table name?

  7. #7
    Join Date
    Aug 2002
    Location
    Dubai
    Posts
    1,183

    Re: Read from excel file

    Quote Originally Posted by lsy
    if using the excel automation... it will launch the excel.exe...
    do there have any example for using ADO to open and retrieve the data?
    how the sql select statement will be?? how to define the table name?
    You can open Excel and choose not to show it. You can find many examples of using ADO to read Excel sheet in VB. Doubtful if you will find any examples in C++ , so I wrote one for now.

    paste this data into an Excel sheet
    Code:
    Structure             Country    City        Height
    Burj Dubai            UAE        Dubai       818
    KVLY-TV mast          USA        Fargo       621
    Petronius Platform    USA        New Orleans 610
    CN Tower              Canada     Toronto     553.3
    Ostankino Tower       Russia     Moscow      540
    Sears Tower           USA        Chicago     527
    Taipei 101            Taiwan     Taipei      449
    BREN-Tower            USA        NTS         465
    Petronas Towers       Malaysia   KL          452
    GRES-2 Power Station  Kazakhstan Ekibastusz  419.7
    The worksheet should be named Sheet1. Save the workbook as C:\Test\test.xls

    Now the code

    Code:
    #include <comutil.h>
    #pragma comment(lib, "comsupp.lib")
    #import "C:\Program Files\Common Files\System\ADO\msado15.dll" 
    rename("EOF", "EndOfFile")
    using namespace ADODB;
    
    void CDialogTestDlg::OnButton1() {
        HRESULT hr;
        _ConnectionPtr cnn; 
        _bstr_t strV = L"";
       ::CoInitialize(NULL);
       cnn.CreateInstance(__uuidof(Connection));
       cnn->CursorLocation = adUseClient;
       LPCTSTR connStr = "DRIVER=Microsoft Excel Driver (*.xls);DBQ=C:\\Test\\test.xls";
       try{     hr = cnn->Open(_bstr_t(connStr), _bstr_t(""), _bstr_t("") , 
       adConnectUnspecified);
         if(hr == S_OK){   AfxMessageBox("Connected");  }
        else { AfxMessageBox("Unable to connect");  }
       }
       catch(_com_error &e){  AfxMessageBox(e.ErrorMessage());  }    
       _RecordsetPtr rs1;
        VARIANT* v = {0};
        rs1 = cnn->Execute(_bstr_t("Select * from [Sheet1$]") , v , adOpenForwardOnly);
        strV = rs1->Fields->Item[long(0)]->Value;
        AfxMessageBox(_com_util::ConvertBSTRToString(strV));
     }
    The message box will read "Burj Dubai"
    Last edited by Sahir; July 10th, 2007 at 02:38 AM.

  8. #8
    Join Date
    Feb 2005
    Posts
    568

    Re: Read from excel file

    Quote Originally Posted by Sahir
    You can open Excel and choose not to show it. You can find many examples of using ADO to read Excel sheet in VB. Doubtful if you will find any examples in C++ , so I wrote one for now.

    paste this data into an Excel sheet
    Code:
    Structure             Country    City        Height
    Burj Dubai            UAE        Dubai       818
    KVLY-TV mast          USA        Fargo       621
    Petronius Platform    USA        New Orleans 610
    CN Tower              Canada     Toronto     553.3
    Ostankino Tower       Russia     Moscow      540
    Sears Tower           USA        Chicago     527
    Taipei 101            Taiwan     Taipei      449
    BREN-Tower            USA        NTS         465
    Petronas Towers       Malaysia   KL          452
    GRES-2 Power Station  Kazakhstan Ekibastusz  419.7
    The worksheet should be named Sheet1. Save the workbook as C:\Test\test.xls

    Now the code

    Code:
    #include <comutil.h>
    #pragma comment(lib, "comsupp.lib")
    #import "C:\Program Files\Common Files\System\ADO\msado15.dll" 
    rename("EOF", "EndOfFile")
    using namespace ADODB;
    
    void CDialogTestDlg::OnButton1() {
        HRESULT hr;
        _ConnectionPtr cnn; 
        _bstr_t strV = L"";
       ::CoInitialize(NULL);
       cnn.CreateInstance(__uuidof(Connection));
       cnn->CursorLocation = adUseClient;
       LPCTSTR connStr = "DRIVER=Microsoft Excel Driver (*.xls);DBQ=C:\\Test\\test.xls";
       try{     hr = cnn->Open(_bstr_t(connStr), _bstr_t(""), _bstr_t("") , 
       adConnectUnspecified);
         if(hr == S_OK){   AfxMessageBox("Connected");  }
        else { AfxMessageBox("Unable to connect");  }
       }
       catch(_com_error &e){  AfxMessageBox(e.ErrorMessage());  }    
       _RecordsetPtr rs1;
        VARIANT* v = {0};
        rs1 = cnn->Execute(_bstr_t("Select * from [Sheet1$]") , v , adOpenForwardOnly);
        strV = rs1->Fields->Item[long(0)]->Value;
        AfxMessageBox(_com_util::ConvertBSTRToString(strV));
     }
    The message box will read "Burj Dubai"
    how can i get the second row data??
    " strV = rs1->Fields->Item[long(0)]->Value; "
    how can i specify the field??

  9. #9
    Join Date
    Oct 2006
    Location
    Sweden
    Posts
    3,654

    Re: Read from excel file


  10. #10
    Join Date
    Aug 2002
    Location
    Dubai
    Posts
    1,183

    Re: Read from excel file

    Quote Originally Posted by lsy
    how can i get the second row data??
    " strV = rs1->Fields->Item[long(0)]->Value; "
    how can i specify the field??
    Add a list box to the dialog and change the code as follows.

    Code:
    void CDialogTestDlg::OnButton1() {
        HRESULT hr;
        _ConnectionPtr cnn; 
        _bstr_t strV = L"";
       ::CoInitialize(NULL);
       cnn.CreateInstance(__uuidof(Connection));
       cnn->CursorLocation = adUseClient;
       LPCTSTR connStr = "DRIVER=Microsoft Excel Driver (*.xls);DBQ=C:\\Test\\test.xls";
       try{ hr = cnn->Open(_bstr_t(connStr), _bstr_t(""), _bstr_t(""), 
                           adConnectUnspecified);
          if(hr == S_OK){ AfxMessageBox("Connected"); }
          else { AfxMessageBox("Unable to connect");  }
       }
       catch(_com_error &e){
         AfxMessageBox(e.ErrorMessage());
       }    
       _RecordsetPtr rs1;
        VARIANT* v = {0};
        rs1 = cnn->Execute(_bstr_t("Select * from [Sheet1$]") , v , adOpenForwardOnly);
    
        while(! rs1->EndOfFile) { 
           strV = rs1->Fields->Item[long(2)]->Value;
            list1.InsertString(-1 , _com_util::ConvertBSTRToString(strV)); 
           rs1->MoveNext(); 
        } 
     }
    This will display the city names (i.e. the third column) in the list box
    Last edited by Sahir; July 10th, 2007 at 04:01 AM.

  11. #11
    Join Date
    May 2009
    Location
    Bengaluru, India
    Posts
    438

    Re: Read from excel file

    Quote Originally Posted by Sahir View Post
    These days you need to specify if it's VC6, VC7, VC8 and then if it's C++ CLI , MFC or anything else. Since you mentioned ODBC I guess you are using VC6 or older. There are two methods you can use here. You can open the Excel workbook using Excel automation and read the contents. The following code reads and displays the contents of cell "A1"

    Code:
    void CDialogTestDlg::OnButton1() {
         if(!AfxOleInit()) {
           AfxMessageBox("OLE init failed ");
            return;
         }   
        COleVariant   covOpt((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
        _Application app;  Workbooks books;
        _Workbook book; Worksheets sheets;
        _Worksheet sheet; Range range;
        LPDISPATCH lpDisp; 
        VARIANT v; 
        if(!app.CreateDispatch("Excel.Application"))   {
         AfxMessageBox("Failed.");
          return;
         }
         books = app.GetWorkbooks();
         book =  books.Open("c:\\Test\\test.xls" , 
                           covOpt , covOpt , covOpt ,
                           covOpt , covOpt , covOpt ,
                           covOpt , covOpt , covOpt ,
                           covOpt , covOpt , covOpt ,
                           covOpt,  covOpt);
         app.SetVisible(true);
         sheets = book.GetWorksheets();
         sheet = sheets.GetItem(COleVariant((short)1));
         range = sheet.GetRange( COleVariant("A1") , COleVariant("A1"));
         lpDisp = range.GetEnd(-4121);
          v  =  range.GetValue(covOpt);
         AfxMessageBox(_com_util::ConvertBSTRToString(v.bstrVal));     
     }
    or you can use ADO and open the Excel sheet as if it were a table in a database.
    I am getting build error for the above code.. what is the include file for using these OLE excel functions and classes like _Application, workbooks etc

  12. #12
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,038

    Re: Read from excel file

    Have you looked at the link in post #9? Does this not work?

    If you just want to read data from an excel file in C++, have a look at posts #14 onwards in this thread http://forums.codeguru.com/showthrea...a-grid-of-data
    Last edited by 2kaud; March 10th, 2020 at 09:44 AM.
    All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!

    C++17 Compiler: Microsoft VS2019 (16.5.1)

  13. #13
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,624

    Re: Read from excel file

    Quote Originally Posted by vcdebugger View Post
    I am getting build error for the above code.. what is the include file for using these OLE excel functions and classes like _Application, workbooks etc
    What errors?
    And I guess you have to use an #import... some of the ADO file
    Victor Nijegorodov

  14. #14
    Join Date
    May 2009
    Location
    Bengaluru, India
    Posts
    438

    Re: Read from excel file

    Quote Originally Posted by 2kaud View Post
    Have you looked at the link in post #9? Does this not work?

    If you just want to read data from an excel file in C++, have a look at posts #14 onwards in this thread http://forums.codeguru.com/showthrea...a-grid-of-data

    Thanks very much for the response.. the #import statements in that link in post#9 does not work because I am using a different version of Excel - Excel Office 365 version and this might have the files in a different path. I am not able to figure out.

    Even the link in the other thread post#9 is having #import of different version of Excel. It is not able to find the files in those path.

  15. #15
    Join Date
    May 2009
    Location
    Bengaluru, India
    Posts
    438

    Re: Read from excel file

    Quote Originally Posted by VictorN View Post
    What errors?
    And I guess you have to use an #import... some of the ADO file
    I am getting undeclared errors for _Application, Workbooks etc.. We need to import some files related Excel application. I have Excel Office 365 version installed on my PC. Not able to find out which dlls need to be imported from which path.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)