-
July 9th, 2007, 10:59 PM
#1
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++??
-
July 9th, 2007, 11:08 PM
#2
-
July 9th, 2007, 11:50 PM
#3
Re: Read from excel file
Originally Posted by ashukasama
this example is launching the exel.exe
but i would prefer it show the data in my application.
-
July 10th, 2007, 12:29 AM
#4
Re: Read from excel file
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.
-
July 10th, 2007, 12:50 AM
#5
Re: Read from excel file
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$] "
-
July 10th, 2007, 01:19 AM
#6
Re: Read from excel file
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?
-
July 10th, 2007, 02:29 AM
#7
Re: Read from excel file
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.
-
July 10th, 2007, 03:18 AM
#8
Re: Read from excel file
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??
-
July 10th, 2007, 03:26 AM
#9
-
July 10th, 2007, 03:58 AM
#10
Re: Read from excel file
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.
-
March 10th, 2020, 07:39 AM
#11
Re: Read from excel file
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.
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
-
March 10th, 2020, 09:40 AM
#12
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++23 Compiler: Microsoft VS2022 (17.6.5)
-
March 10th, 2020, 03:39 PM
#13
Re: Read from excel file
Originally Posted by vcdebugger
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
-
March 11th, 2020, 07:00 AM
#14
Re: Read from excel file
Originally Posted by 2kaud
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.
-
March 11th, 2020, 07:02 AM
#15
Re: Read from excel file
Originally Posted by VictorN
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|