CNiExcelCellRange::ReadStringDataIntoArray

[kathyH]

I am going to use function "CNiExcelCellRange::ReadStringDataIntoArray( CString* stringMatrix, int numRows, int numCols);" to read data from Excel sheet. How to define  "CString* stringMatrix" with 4 rows and 4 columns?

[nickb]

Hi KathyH!

In order to use this function, first define an array of CStrings, such as:
CString test[16];

This will store the first row's four column values, then the second row's four column values, etc.  Next, defin a new cell range like this:
CNiExcelWorksheet NIWorksheet = MFCWorksheet;
CNiExcelCellRange NICellRange = NIWorksheet.GetRange("B1:E4");

Then call the function:
NICellRange.ReadStringDataIntoArray(test, 4, 4);

Let me know if this prompts any further questions!

NickB

[kathyH]

Hi NickB,

Thanks for the reply.

I tried your suggestion to define CString array and it works fine except in some specific cases. If one of the cell has the number 5.30E+09, ReadStringDataIntoArray( ) will launch exception. Interesting is that if I have 5.00E+09 instead of 5.30E+09 there is no exception but the string read is not "5.00E+09" but some thing wierd. It seems that ReadStringDataIntoArray has the limitations in reading strings.

I also use ReadDataIntoArray() to read numeric data.  But in my .xls file there are no valid numbers in some cells so that I put NaN or INF to indicate the invalid numbers. However when read the numeric data back using ReadDataIntoArray the invalid numbers are read as 3.81xxxe-316. It's difficult to filter the invalid number out in my calculations. Is there special number or string can be used to indicate the invalid number and how to check the invalid number? If I leave the cell blank the number read back is 0 which is not what I want.

Reagards.

Kathy

[nickb]

Kathy,

The problem with a cell containing a value like 5.30E+09 is that Excel interperets this automatically to be a number.  Thus using ReadStringDataIntoArray() will not work, because it is looking for string data.  I formatted the cell to be by default text instead of a number, and I was able to use this method without problem. 

This appears to be the same issue you are running into when running ReadDataIntoArray.  If the cell contains text, you will get a return value that looks something like what you have described.  I do not think that there is any special string or number that is used to indicate an invalid number.  Would you like to store something for these invalid numbers in the matrix, or would you just like to delete them from the matrix?

NickB

[kathyH]

ReadDataIntoArray() reads limits for different variables from .xls file. Some variables have limits some don’t. I need to detect which one has no limit and don’t do the calculations on it. I also need to replace the invalid numbers with std::numeric_limits<real64>::quiet_NaN() in the matrix so that NaN will be display in the output window.  

The output should like this:

Meassurement   MeasValue   LowerLimit    Upperlimit

Frequency(Hz)          1.5                1.0              2.0

VSWR                    0.1               NaN               1

Thanks.

Kathy

[nickb]

Kathy,

I think that there may be problems with using std::numeric_limits<real64>::quiet_NaN(). numeric_limit is not defined for the real64 type.  If necessary, the user can define this type in the <limits> file, but other than doing this, I think that a brute force search and replace may be your best option.

NickB

[kathyH]

Hi NickB,

What did you mean by "brute force search and replace "?

Now I use the following way to search and replace and it works so far:

if

( CellValue > 0 && CellValue  < 3.0e-315)

{

         CellValue  = std::numeric_limits<real64>::quiet_NaN();

}

One thing I am not sure is that "3.0e-315" is good for comparing in different systems? I am use Windows XP and our client may have Windows2000 server.

Thanks

Kathy

[nickb]

Kathy,

What you have described is what I meant by search and replace.  I agree with you that this comparison is a concern.  3.0e-315 is already out of range for a double, which is what the <real64> type is.  I am surprised that it seems to be working your machine.  I am wondering if some of the data that might occur on your spreadsheet would have meaningful values below something like 1e-10.  This number is well within the scope of a double, and should be a much more robust number to compare against, regardless of the system.

NickB