| Author |
 Topic  |
|
|
dhabersat
USA
Posts |
 Posted - 05/30/2007 : 10:11:37 AM
|
Origin Version: 7.5SR6
Operating System: WinXP
I understand the basic principles of importing ranges using the COM model for Excel in Origin C. My goal is to move data from an Excel spreadsheet into an Origin worksheet.
When the range includes only numeric values, I have no problem. However, I want to be able to import a range that may include errors (such as #N/A, #DIV/0!, etc.). When I find an error, I want to replace it with some kind of empty cell (NANUM or blank?) on a Origin worksheet.
I've tried doing the imports using a vector/dataset combo and the range's Value property, as this was the example that I found on the forums for importing data. This does not handle ranges with errors in it very well (program crashes and dies).
I could do it one cell at a time, reading the Text property of each cell into a string variable and then checking the string to see if it is one of the standard errors. This would be very time consuming, as reading a range cell by cell is exceedingly slow. Unfortunately, the Text property only works on single cells and not ranges.
Can you help me find a better way to do this? Thanks!
|
|
|
Mike Buess
USA
3037 Posts |
Posted - 05/30/2007 : 11:55:50 AM
|
If any non-numeric cell value is considered an error the easiest method by far is the following LabTalk expression which replaces all non-numeric values in column #i with "--".
wcol(i) = wcol(i)==NANUM ? NANUM : wcol(i);
To look for specific error tokens you can use something like this...
Worksheet wks = Project.ActiveLayer();
string strNAN = "#N/A|#DIV/0!"; // '|' separated list of error tokens
string str;
StringArray sa;
StringArray saNAN;
strNAN.GetTokens(saNAN,'|');
Dataset dd;
foreach (Column cc in wks.Columns)
{
dd.Attach(cc);
dd.GetStringArray(sa);
str.SetTokens(sa,'|');
for(int i=0;i<saNAN.GetSize();i++)
{
str.Replace(saNAN[i],"--");
}
str.GetTokens(sa,'|');
dd.PutStringArray(sa);
}
...Of course the examples above assume you've already imported to Origin worksheet. If you can't import I'd have to see the import script to find out why.
I can reproduce your problem with the ReportCreation example merely by changing a couple values in the Excel wks to strings. Doesn't crash origin but stops execution with Unknown errors. The problem indeed occurs with the Range's value property but I don't know of a solution.
Mike Buess
Origin WebRing Member
Edited by - Mike Buess on 05/30/2007 12:54:41 PM
Edited by - Mike Buess on 05/30/2007 1:52:37 PM |
 |
|
|
dhabersat
USA
Posts |
Posted - 05/30/2007 : 2:34:43 PM
|
Thanks for the quick reply, Mike.
These Excel workbooks are not imported into Origin. I do not think that would work very well, as these are very complicated files. Really, what I want to do is pull some of the initial data and the resultant analysis out of the worksheet and into Origin for graphing. Sometimes the data is bad for analysis (hence the errors) but I want to be able to still read what data that I can. Although I could conceivably alter the spreadsheet template itself to not report the errors in cells, my data is stored in a few hundred files (and growing) and I would prefer to retain the errors since they highlight the fact that the analysis broke down at that point.
Here is an example block of code to import data. The sub ImportTest() is called; it tries to read the data in the named range "list" from a particular spreadsheet into a dataset. "list" is just a simple column of numbers, plus maybe an error code or two.
void ImportTest()
{
// Declare Excel objects
Object oExcel, oExcelWkbks, oExcelWkbk, oExcelWksh;
// Create an Excel application object and set it as invisible
oExcel = CreateObject("excel.application");
// Get the workbooks collection of the Excel object
oExcelWkbks = oExcel.Workbooks;
oExcelWkbk = oExcelWkbks.Open("test.xls");
////////////////////////////////////////////////////////////////////////////////////
// Define the dataset to store information here
Worksheet wks("Data1");
Dataset data(wks,0);
// Excel range to import
Object range;
range = oExcelWkbk.Names("list").RefersToRange;
// The import command
SmartExcelImport(range, data);
////////////////////////////////////////////////////////////////////////////////////
// Close the Excel and Word applications
oExcelWkbk.Close();
oExcel.Quit();
printf("Done!\n");
}
Now, by defining the SmartExcelImport function, I can control how the data is read. The ideal choice would look something like:
void SmartExcelImport(Object range, Dataset& data)
{
data = range.Value;
}
This simple version works great if, as I said above, there are only numeric values in the specified range. If there is a #N/A value in one of the cells, then Origin C dies on the "data = range.Value;" line with an error of
quote:
Origin C Function Runtime Error, general operation failure
To get around this, I can write an import function that scans each Cell's individual Text property and check for errors before writing to the dataset. This example works on #N/A values but is extremely slow because of the cell-by-cell reading.
void SmartExcelImport(Object range, Dataset& data)
{
int i,n;
string str;
n = range.Cells.Count;
for (i=0;i<n;i++) {
str = range.Cells(i+1).Text;
if ( !(str == "#NULL!" || str == "#DIV/0!" || str == "#VALUE!" ||
str == "#REF!" || str == "#NAME?" || str == "#NUM!" || str == "#N/A") ) {
data[i] = atof(str);
}
}
}
I have looked at many ways of trying to store the entire range.Value in an Origin C object, but it has always died on the retrieval step no matter what I do. It does not matter if the column the dataset is tied to is Text, Numeric, or both. I have tried using vectors of both <double> and <string>.
In Excel, the Range.Value property is treated as an array of type Variant. Is it possible to create a Variant-like structure that could accept the Range.Value property and then it would be a relatively simple matter of converting that to either double or NANUM as appropriate. I looked at the contents of variant.h and VariantTypes.h in the Origin C header files, but these functions are not well documented and I could not find enough enough to use them properly. It looks like you can create a _VARIANT array, but it's not clear how to use it from there (or even that this is the same as Excel's Variant).
Hopefully this clarifies what I'm trying to do.
Thanks!
|
|
|
|
dhabersat
USA
Posts |
Posted - 05/30/2007 : 2:43:42 PM
|
Oh, and one more thing. If I could control Origin's import wizard programatically and have fine precision over what parts of an Excel workbook to import, I would be happy to use that apparatus. I'm writing a larger routine in Origin C to scan for each data file under a directory, create the appropriate folder structure and worksheets (with custom names and formatting), and finally to import that data. That's really all that I'm doing for now (I'll do some graphing automation once I can import the large volumes of data that I have).
|
|
|
| |
Topic |
|
|
|
reading Excel ranges in Origin C