Excel file read
-
- Ultimate wxWidgets Guru
- Posts: 672
- Joined: Tue Aug 31, 2010 6:22 am
- Location: Belgium
You can also check out (generic C/C++):
ExcelFormat Library
http://stackoverflow.com/questions/4931 ... xcel-files
ExcelFormat Library
http://stackoverflow.com/questions/4931 ... xcel-files
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.
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
Greg
- evstevemd
- Part Of The Furniture
- Posts: 2408
- Joined: Wed Jan 28, 2009 11:57 am
- Location: United Republic of Tanzania
check basic excel
http://www.codeproject.com/KB/office/BasicExcel.aspx
http://www.codeproject.com/KB/office/BasicExcel.aspx
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?
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
- evstevemd
- Part Of The Furniture
- Posts: 2408
- Joined: Wed Jan 28, 2009 11:57 am
- Location: United Republic of Tanzania
Even the author says that!greg wrote:BasicExcel has a lot of limitations and that's why I can apply
this project.
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 valuesgreg wrote:It crashes while reading xls file.
Looking for some sample via wxOLE and wxAutomation.
Can anybody help me?
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?
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
- evstevemd
- Part Of The Furniture
- Posts: 2408
- Joined: Wed Jan 28, 2009 11:57 am
- Location: United Republic of Tanzania
Unfortunately I dont know anything about wxOLEgreg wrote:You wroteCan You give a little sample how can I read cell contensI think just conversions between C++/wx values
from xls file using wxOLE
I'm not going to expand BasicExcel at all.
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?
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
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
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.
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.
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
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
Thanks PB
I tried Your code but this
always returns 0.
What am I doing wrong?
By the way how can I get number of sheets?
I tried Your code but this
Code: Select all
if (excel.GetObject(sheets, "ActiveWorkbook.Worksheets"))
What am I doing wrong?
By the way how can I get number of sheets?
Regards
Greg
Greg
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;
}
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.
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
Greg