Excel file read

If you are using the main C++ distribution of wxWidgets, Feel free to ask any question related to wxWidgets development here. This means questions regarding to C++ and wxWidgets, not compile problems.
greg
Earned some good credits
Earned some good credits
Posts: 111
Joined: Fri Sep 12, 2008 7:17 am

Excel file read

Post by greg »

Hi
How can I read data from excel file (from sheet=2).
Regards
Greg
briceandre
Ultimate wxWidgets Guru
Ultimate wxWidgets Guru
Posts: 672
Joined: Tue Aug 31, 2010 6:22 am
Location: Belgium

Post by briceandre »

You need to implement (or find an implementation) of xls format (the native format of excel). wxWidgets does not provide it.

Note that you can also use excel to convert your file to a simpliest format (like csv). If this is an acceptable solution for you, reading a csv file is trivial.
Auria
Site Admin
Site Admin
Posts: 6695
Joined: Thu Sep 28, 2006 12:23 am
Contact:

Post by Auria »

On Windows I believe you can use the automation classes. This won't work on other platforms however
"Keyboard not detected. Press F1 to continue"
-- Windows
PB
Part Of The Furniture
Part Of The Furniture
Posts: 4193
Joined: Sun Jan 03, 2010 5:45 pm

Post by PB »

greg
Earned some good credits
Earned some good credits
Posts: 111
Joined: Fri Sep 12, 2008 7:17 am

Post by greg »

That's my piece of code.
I can open xls file via wxCOM.
But I don't know how to read a cell at row from given sheet.

Code: Select all

	wxOleInitialize();
	wxAutomationObject ExcelObject;
	wxString FileName = filename;
	ExcelObject.CreateInstance("Excel.Application");
	ExcelObject.CallMethod("Workbooks.Open", FileName);
	ExcelObject.PutProperty("Visible", true);


	ExcelObject.CallMethod("Workbooks.Close", true);
	ExcelObject.CallMethod("Quit");
Regards
Greg
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2408
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

Post by evstevemd »

Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
greg
Earned some good credits
Earned some good credits
Posts: 111
Joined: Fri Sep 12, 2008 7:17 am

Post by greg »

BasicExcel has a lot of limitations and that's why I can apply
this project. It crashes while reading xls file.
Looking for some sample via wxOLE and wxAutomation.
Can anybody help me?
Regards
Greg
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2408
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

Post by evstevemd »

greg wrote:BasicExcel has a lot of limitations and that's why I can apply
this project.
Even the author says that!
greg wrote:It crashes while reading xls file.
Looking for some sample via wxOLE and wxAutomation.
Can anybody help me?
So it is limitation or crash that drives you away? If the first then look for alternative or expand BasicExcel. Else it is resolvable. I think just conversions between C++/wx values
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
greg
Earned some good credits
Earned some good credits
Posts: 111
Joined: Fri Sep 12, 2008 7:17 am

Post by greg »

You wrote
I think just conversions between C++/wx values
Can You give a little sample how can I read cell contens
from xls file using wxOLE
I'm not going to expand BasicExcel at all.
Regards
Greg
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2408
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

Post by evstevemd »

greg wrote:You wrote
I think just conversions between C++/wx values
Can You give a little sample how can I read cell contens
from xls file using wxOLE
I'm not going to expand BasicExcel at all.
Unfortunately I dont know anything about wxOLE
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
PB
Part Of The Furniture
Part Of The Furniture
Posts: 4193
Joined: Sun Jan 03, 2010 5:45 pm

Post by PB »

greg wrote:BasicExcel has a lot of limitations and that's why I can apply
this project. It crashes while reading xls file.
BasicExcel was pretty much superseded by the ExcelFormat library (which, of course, has a lot of limitations too), I linked to in my first post here. Does it also crash?
PB
Part Of The Furniture
Part Of The Furniture
Posts: 4193
Joined: Sun Jan 03, 2010 5:45 pm

Post by PB »

I have no experience with automation and very little with wxWidgets, but as I might be needing to interact with MS Excel soon, I spent some time looking into it.

Here's a very crude and probably very inefficient example, which:
1. Launches MS Excel in invisible state
2. Opens a file
3. Reads and displays the value located in row 1, column 1 on sheet 2
4. Closes MS Excel

Code: Select all

wxString fileName = "C:\\test.xls";

wxAutomationObject excel;      
if (!excel.CreateInstance("Excel.Application"))
   return false;
excel.CallMethod("Workbooks.Open", fileName);         
      
wxAutomationObject sheets;
// assume the opened file is the active workbook, you can verify it by testing return value of excel.GetProperty("ActiveWorkbook.FullName");
if (excel.GetObject(sheets, "ActiveWorkbook.Worksheets")) {         
   wxVariant params[2];   
   wxAutomationObject sheet2;         
   params[0] = 2L; // we want sheet #2                  
   if (sheets.GetObject(sheet2, "Item", 1, params)) {   
      wxAutomationObject cells;
      if (sheet2.GetObject(cells, "Cells")) {
         wxAutomationObject cell;               
         params[0] = 1L; params[1] = 1L;   // row 1, column 1
         if (cells.GetObject(cell, "Item", 2, params)) {
            wxVariant value = cell.GetProperty("Value");                  
            wxMessageBox(value);
         }                        
      }            
   }
}
excel.CallMethod("Quit"); // Close Excel
Reading values from many cells one by one like this is very likely to be painfully slow. There might be a way how to get an array of values from whole range (e.g. A1:C50) in a single call.

I assume you're familiar with VBA in MS Excel. If not, just remember that collections (sheets, cells etc.) are 1 based. Object Browser might be your good friend - in MS Excel press <Alt+F11> to invoke VBA IDE and then <F2> to show the Object Browser, Range object should be the first to inspect. If you don't know in advance how many cells you have to read and the input data might be sparse, check Excel VBA documentation for the effective method of iterating only through populated cells of a Range (see e.g. UsedRange or SpecialCells). The difference in time it takes is HUGE, at least when doing it in VBA.

Good luck.

EDIT
Here's a modified example showing how to get a whole array of values at once. It can also be slow when there's many cells but it should still be much faster than going through individual cells.

Code: Select all

wxString fileName = "C:\\test.xls";

wxAutomationObject excel;      
if (!excel.CreateInstance("Excel.Application"))
   return false;
excel.CallMethod("Workbooks.Open", fileName);         
      

wxAutomationObject sheets;
// assume the opened file is the active workbook, you can verify it by testing the return value of excel.GetProperty("ActiveWorkbook.FullName");
if (excel.GetObject(sheets, "ActiveWorkbook.Worksheets")) {         
   wxVariant params[2];
   wxAutomationObject sheet2;         
   
   params[0] = 2L; // we want sheet #2                 
   if (sheets.GetObject(sheet2, "Item", 1, params)) {   
     wxAutomationObject usedRange;        
     
     // see what UsedRange is in Excel VBA documentation
     // be aware that it might not contain certain blank cells,
     // e.g. if the first row and column are empty, the range starts at B2, not A1!
     // so you might better use absolute addressing either via columns or addresses
     if (sheet2.GetObject(usedRange, "UsedRange")) {                             
       wxAutomationObject col1;          
       
       params[0] = 1L; 
       // the first column on sheet 2 which has at least one value in it
       if (usedRange.GetObject(col1, "Columns", 1, params)) {                                   
         wxString s;
         wxVariant value;            
         
         value = col1.GetProperty("Address");
         s.Printf("The address of UsedRange is %s", value.GetString().c_str());
         wxMessageBox(s);
         // get list of all values from UsedRange.Col(1)
         value = col1.GetProperty("Value");            
         if (value.IsType("list")) {
            wxVariantList vl = value.GetList();            
            
            // display at most 10 first values
            for (int i = 0; i < min(vl.GetCount(), 10); i++) {                                         
              s.Printf("ActiveWorkbook.Worksheets(2).UsedRange.Column(1) value #%d is: %s", i + 1, vl[i]->GetString().c_str());
              wxMessageBox(s);
            }
         }               
       }         
     }           
   }
}

excel.CallMethod("Quit"); // Close Excel
In real code I would probably obtain SpecialCells Range first and then iterated through its columns in chunks, but as always it all depends on your needs. IIRC, there are also some gotchas using UsedRange, e.g. in some cases it might not exist but I may be wrong - check VBA docs.
greg
Earned some good credits
Earned some good credits
Posts: 111
Joined: Fri Sep 12, 2008 7:17 am

Post by greg »

Thanks PB
I tried Your code but this

Code: Select all

if (excel.GetObject(sheets, "ActiveWorkbook.Worksheets"))
always returns 0.
What am I doing wrong?
By the way how can I get number of sheets?
Regards
Greg
PB
Part Of The Furniture
Part Of The Furniture
Posts: 4193
Joined: Sun Jan 03, 2010 5:45 pm

Post by PB »

I would say that the likeliest possibility is that there's no ActiveWorkbook - ie you couldn't open the file. Also open the file in question from Excel to see if it has at least one worksheet. I don't know if it is even possible to have a workbook without a single worksheet though. Check out the function below, it has some more diagnostics. Notice that I've changed Sheets to Worksheets, which is better if you're interested only in reading cell values (Sheets = Charts + Worksheets). I also demonstrate how to obtain worksheet count for ActiveWorkbook.

Code: Select all

bool TestExcel(const wxString& fileName)
{   
   wxAutomationObject excel;      
   if (!excel.CreateInstance("Excel.Application")) {
      wxMessageBox("Failed to create Excel instance");
      return false;
   }
   // For debugging purposes show Excel instance so we can see it was properly started 
   // also don't close it when we're done with it so it's left running with the file still opened
   if (!excel.PutProperty("Visible", true)) {
      // couldn't show it, we don't want to leave the function with hidden instance of Excel still running, so we close it
      excel.CallMethod("Quit"); 
      wxMessageBox("Succeeded to start Excel but failed to show it");
      return false;
   }

   wxString s;
   wxVariant result = excel.CallMethod("Workbooks.Open", fileName);
   if (result.IsNull()) {
      s.Printf("Failed to open %s", fileName.c_str());
      wxMessageBox(s);
      return false;
   }   

   wxAutomationObject activeWorkbook;
   if (excel.GetObject(activeWorkbook, "ActiveWorkbook")) {      
      result = activeWorkbook.GetProperty("FullName");
      s.Printf("ActiveWorkbook.FullName:\n %s", result.GetString().c_str());
      wxMessageBox(s);
      
      wxAutomationObject worksheets;
      if (activeWorkbook.GetObject(worksheets, "Worksheets")) {
         result = worksheets.GetProperty("Count");
         s.Printf("ActiveWorkbook.Worksheets.Count:\n %ld", result.GetLong());
         wxMessageBox(s);
         return true;
      } else
         wxMessageBox("Failed to obtain Worksheets");
   } else 
      wxMessageBox("Failed to obtain ActiveWorkbook");
   
   return false;
}
greg
Earned some good credits
Earned some good credits
Posts: 111
Joined: Fri Sep 12, 2008 7:17 am

Post by greg »

PB
You are great!
Your test code works perfect till now.
I'm testing on Windows console so I put *.xls local path instead of full path.
Thanks for Your previous advice but I have, I hope, final problem.
How can I go through all the sheets and get row/column values
as fast as it's possible using OLE.
How can I get single sheet name in that loop.


Thanks in advance.
Regards
Greg
Post Reply