Following sample code shows how to:
1. Create MS Excel instance
2. Open a workbook
3. Determine worksheet count
3. Iterate through all worksheets
4. Obtain data from a range (using UsedRange), both by columns and by rows
Code: Select all
#ifndef WX_PRECOMP
#include "wx/wx.h"
#endif
#include <wx/stopwatch.h>
#include <wx/msw/ole/automtn.h>
#include <vector>
#include <wx/filedlg.h>
typedef std::vector<wxVariant> variantVector;
// obtains all values from range
// do some sanity check on Range.Count
// before calling this function, so you don't attempt to grab
// tens of thousands or more values at once
void GetDataFromRange(wxAutomationObject& range, variantVector& data)
{
wxVariant values = range.GetProperty("Value");
if (values.IsNull()) // no values in the range
return;
if (values.IsType("list")) {
size_t count = values.GetCount();
data.reserve(count);
for (size_t i = 0; i < count; i++) {
data.push_back(values[i]);
}
} else { // a single value
data.push_back(values);
}
}
// gets either Row or Column Range depending on variable "column" at "index"
bool GetColumnOrRow(wxAutomationObject& range, long index, bool column, wxAutomationObject& item)
{
wxString itemName;
wxVariant params[2];
params[0] = index;
if (column)
itemName = "Columns";
else
itemName = "Rows";
return range.GetObject(item, itemName, 1, params);
}
bool ProcessWorksheet(wxAutomationObject& worksheet)
{
wxAutomationObject dataRange;
if (worksheet.GetObject(dataRange, "UsedRange")) {
wxString sheetName;
wxString usedRangeAddress;
long valueCount;
long columnCount, rowCount;
wxString s;
// Get and display some info about the worksheet and usedRange
sheetName = worksheet.GetProperty("Name").GetString();
usedRangeAddress = dataRange.GetProperty("Address").GetString();
valueCount = dataRange.GetProperty("Count").GetLong();
columnCount = dataRange.GetProperty("Columns.Count").GetLong();
rowCount = dataRange.GetProperty("Rows.Count").GetLong();
s.Printf("Next step will be reading data from the following sheet:\n"
"Sheet.Name: %s\nSheet.UsedRange properties:\n"
" Address: %s\n Count: %ld\n Columns.Count: %ld\n Rows.Count: %ld",
sheetName.c_str(), usedRangeAddress.c_str(),
valueCount, columnCount, rowCount);
wxMessageBox(s);
// Get the data
wxVariant result;
wxVariant params[2];
variantVector data;
size_t valuesRead;
wxStopWatch stopWatch;
long time;
// First, I show how to get data column by column
wxAutomationObject column;
stopWatch.Start();
valuesRead = 0;
for (long l = 1; l <= columnCount; l++) {
if (GetColumnOrRow(dataRange, l, true, column)) {
data.clear();
result = column.GetProperty("Count");
if (result.GetLong() > 5000) {
// in production code you should proceed getting data in chunks instead of attempting
// to grab them all at once
stopWatch.Pause();
s.Printf("There are %ld values in the column %ld. Continue anyway?", result.GetLong(), l);
if (wxMessageBox(s, "Question", wxYES_NO | wxNO_DEFAULT) != wxYES) {
stopWatch.Resume();
continue;
}
stopWatch.Resume();
}
GetDataFromRange(column, data);
if (!data.empty()) {
// do something with the data here
valuesRead += data.size();
// for debugging purposes display first and last value in the column
wxString firstVal, lastVal;
wxVariant v;
v = data.front();
firstVal = v.IsNull() ? "<blank>" : v.GetString();
v = data.back();
lastVal = v.IsNull() ? "<blank>" : v.GetString();
v = column.GetProperty("Address");
s.Printf("Column %ld with address %s (%d values):\n First value: %s\n Last value: %s",
l, v.GetString().c_str(), data.size(), firstVal.c_str(), lastVal.c_str());
} else {
s.Printf("Column %ld has no data", l);
}
stopWatch.Pause();
wxMessageBox(s);
stopWatch.Resume();
} else {
s.Printf("Failed to get the Column object for column %ld", l);
stopWatch.Pause();
wxMessageBox(s);
stopWatch.Resume();
}
}
time = stopWatch.Time();
s.Printf("Get data by columns:\n obtaining %d values in total (from %ld columns) took %d ms.", valuesRead, columnCount, time);
// And this is how would you get data by rows
// which method is better for you depends on your needs
wxAutomationObject row;
stopWatch.Start();
valuesRead = 0;
for (long l = 1; l <= rowCount; l++) {
if (GetColumnOrRow(dataRange, l, false, row)) {
data.clear();
GetDataFromRange(row, data);
if (!data.empty()) {
// do something with the data here
valuesRead += data.size();
// for debugging purposes display first and last value in the row
wxString firstVal, lastVal;
wxVariant v;
v = data.front();
firstVal = v.IsNull() ? "<blank>" : v.GetString();
v = data.back();
lastVal = v.IsNull() ? "<blank>" : v.GetString();
s.Printf("Row %ld (%d values):\n First value: %s\n Last value: %s", l, data.size(), firstVal.c_str(), lastVal.c_str());
} else {
s.Printf("Row %ld has no data", l);
}
/* don't flood me with information about every row
stopWatch.Pause();
wxMessageBox(s);
stopWatch.Resume();
*/
} else {
s.Printf("Failed to get the Row object for row %ld", l);
stopWatch.Pause();
wxMessageBox(s);
stopWatch.Resume();
}
}
time = stopWatch.Time();
s.Printf("Get data by rows:\n obtaining %d values in total (from %ld rows) took %d ms.", valuesRead, rowCount, time);
wxMessageBox(s);
}
return false;
}
bool ProcessWorkbook(wxAutomationObject& workbook)
{
wxString s;
wxString fullWorkbookName;
long worksheetCount;
fullWorkbookName = workbook.GetProperty("FullName").GetString();
wxAutomationObject worksheets;
if (!workbook.GetObject(worksheets, "Worksheets")) {
wxMessageBox("Failed to get the Worksheets");
return false;
}
worksheetCount = worksheets.GetProperty("Count").GetLong();
s.Printf("File %s has %ld worksheets", fullWorkbookName.c_str(), worksheetCount);
wxMessageBox(s);
wxVariant params[2];
wxAutomationObject worksheet;
for (long l = 1; l <= worksheetCount; l++) {
params[0] = l;
if (workbook.GetObject(worksheet, "Worksheets", 1, params)) {
ProcessWorksheet(worksheet);
}
}
return true;
}
/**** MyApp ****/
class MyApp : public wxApp
{
public:
virtual bool OnInit()
{
if (!wxApp::OnInit())
return false;
wxString fileName = "C:\\test.xls";
// uncomment the following line to invoke file open dialog
// fileName = "";
if (fileName.IsEmpty()) {
wxFileDialog dlg(NULL, _("Open Excel file"), "", "",
"XLS* files (*.xls*)|*.xls*", wxFD_OPEN|wxFD_FILE_MUST_EXIST);
if (dlg.ShowModal() == wxID_CANCEL)
return false;
fileName = dlg.GetPath();
}
wxAutomationObject excel;
if (!excel.CreateInstance("Excel.Application")) {
wxMessageBox("Failed to create Excel instance");
return false;
}
wxVariant result = excel.CallMethod("Workbooks.Open", fileName);
if (result.IsNull()) {
wxString s;
s.Printf("Failed to open %s", fileName.c_str());
wxMessageBox(s);
} else {
wxAutomationObject workbook;
if (!excel.GetObject(workbook, "ActiveWorkbook")) {
wxMessageBox("Failed to get ActiveWorkbook");
} else {
ProcessWorkbook(workbook);
}
}
excel.CallMethod("Quit"); // Close Excel
return false; // quit
}
};
IMPLEMENT_APP(MyApp)* if first column is empty UsedRange starts at B1,
* if first row is empty UsedRange starts at A2
* if both first row and columns are empty UsedRange starts at B2.
Depending on your needs, you might want to always grab first rows/columns, even if they're blank. This is how you might proceed, if you want your range to start always at A1 cell (I haven't time to test if it really works):
Code: Select all
// if we want to get our data always starting from the cell A1
// even if first row(s)/column(s) are completely blank
bool GetMyRange(wxAutomationObject& worksheet, wxAutomationObject& myRange)
{
bool result = false;
wxAutomationObject usedRange;
if (worksheet.GetObject(usedRange, "UsedRange")) {
wxVariant result = usedRange.GetProperty("Address");
wxString usedRangeAddress = result.GetString();
if (!usedRangeAddress .IsEmpty()) {
wxString myRangeAddress;
wxString s = usedRangeAddress.AfterFirst(':');
if (s.IsEmpty()) { // colon not found, so usedRange is a single cell, e.g. $C$12
myRangeAddress = "$A$1: " + usedRangeAddress;
} else { // cell range, e.g. $C$12:$D$100
myRangeAddress = "$A$1:" + s;
}
wxVariant params[2];
params[0] = myRangeAddress;
return worksheet.GetObject(myRange, "Range", 1, params);
}
return true;
}
return false;
}1. Call GetDataFromRange() where range is UsedRange
2. The values in data will be organized like this: values 0-99 are from column 1, values 100-199 are from column 2 and so on. I didn't test if it really works like that, but it should. Debug first. Then profile - it might not be that much faster to be worth the hassle.
