Subject: Too much info, Excel! Getting an extra cell from a range.

[howardb1]

I posted the following message on the Borland C++ Builder VCL Components news group, but I thought I'd post it here also.

The short of it is, I am loading three columns, one at a time into variant arrays using the Range->Get_Values() method, but whan I try to associate any of these columns with the oracle PL/SQL Table variable (i.e., the OracleQuery1->SetVariable( "i_plate_table", I_Plate_VArray ) ), I get an error saying "Variant array index out of bounds." It turns out that the variant array returned by the Range->Get_Values() method actually contains two columns, the first for the data specified by the range, and the second contains two extra elements.

Anyone know how to remove the second column so that I can perform the oracle variable association correctly?

Here is a copy of the message I posted on the borland news group:

Subject: Too much info, Excel! Getting an extra cell from a range.
Date: 24 Oct 2002 22:05:07 -0700
From: "Ron Brown"
Newsgroups: borland.public.cppbuilder.vcl.components.using

I'm writing a program in BCB6 with both patches, that pulls columns from a selected region in an Excel Spreadsheet. Unfortunately, the Column->get_Value call returns two columns. I want the first column, but the second contains two elements,
the first and last values the range. I need to pass the I_Plate_VArray to a procedure that only accepts one column.

How do I erase the second column?

Alternatively, is there a way to copy only the first column of the variant array to another. I have five columns in the
spreadsheet that need to be copied, so this second method, might not be to bad.

As you can see I'm pulling in a lot of data from Excel, so when I tried populating the I_Plate_VArray one cell at a time using
the PutElement method, it was very slow. Also, after a few insertions, the earlier values seemed to get overwritten by the
later ones.

Are there any variant array experts in the house?

Thank you,
Ron Brown

Here is the code segment. That the spreadsheet is already opened before this code is called.

Variant I_Plate_VArray,
Plate;

int r;
_WorksheetPtr ws;
Excel_2k::RangePtr Column;
TVariant CellAddr1,
CellAddr2;

ws = ExcelApplication1->ActiveSheet;
CellAddr1 = "A2";
CellAddr2 = "A5953";
Column = ws->get_Range( CellAddr1, CellAddr2 );

I_Plate_VArray = Column->get_Value(); //

 

[howardb1]

After fooling around with the I_Plate_VArray = Column->get_Value() statement I found that if you specified a range of two or more columns that the statements returned the proper information. It only goofs up when you try to get only one column, this is where it pulls the information from the specified column and a short second column. The situation gets even worse because the short column sometimes preceeds the long one and sometimes follows it.

So, I'm now pulling all three columns from the spreadsheet at once, then copying the data from the three column variant array into three separate single column variant arrays that can be assoiated with the oracle PL/SQL Table arrays.

It would be nice if the oracle variable association statement be enhansed to support multicolumn arrays, perhaps even arrays of structures. However, there should be a way of telling the oracle variable association statement to only use one column of the variant array, at least until Borland fixes the get_Value statement so that it properly returns only what asked for.

Thank you,
Ron Brown

 

[Marco Kalter]

Okay, I have added this to the list of enhancement requests.

------------------
Marco Kalter
Allround Automations

 

[howardb1]

Thank you