0

I'm trying to extract from an Excel Workbook a vector of double values. Excel looks like does not return a unidimensional array, but a multidimensional one (number of dimensions is 2).

The data I have from Excel is in SafeArray format (I can have Variant too, but it's basically the same problem).

I'm looking for a fast way to extract the doubles array into a Java object, without needing to loop all the values in the SafeArray.

Looking at the methods in SafeArray there is this one: getDoubles(int sa_idx, int nelems, double[] ja, int ja_start)

Obviously documentation is null, and I can't make up my mind of what this method does, since trying to use it gave me no results.

Thanks.

Milo Casagrande
  • 135
  • 1
  • 3
  • 11

1 Answers1

0

I'm assuming here your data comes from a spreadsheet (Range object), not VBA code.

In that case, Excel will hand you a SafeArray of Variants (Variant() in VBA terminology), not a SafeArray of doubles (Double()), no matter what the contents of the cells happen to be. And you are correct that the array will always be two-dimensional (or it will return a single Variant if the range size is exactly 1x1).

I'm not familiar with JACOB, and yes, the documentation looks rather lacking. Could it be that getDoubles() only works when the SafeArray is a SafeArray of Doubles? Did you try to use getVariants()? If you get something back we'll know we're on the right track.

Keep in mind that performance-wise, you are stuck. somebody (you or the library) is going to have to write that loop. The Variants won't magically become doubles; someone will have to loop through the array, call Win32 VariantChangeType() or an equivalent method, extract the double field from the Variant (apparently in JACOB you call changeType() then getDouble()), and finally put the results in a Java double array. Whether JACOB will do this for you or you have to do it yourself, I have no idea.

Not being familiar with JACOB I'm not sure I I'm making sense, but I hope this helps. Good luck.

[PS:]
I feel I need to clarify that comment on calling VariantChangeType(). This is technically optional, but I find it generally healthy to explicitly convert variants to the data type I'm interested in via VariantChangeType() or an equivalent method. It's too easy for numbers to be stored as strings or something else, particularly when the source is something as free-form as Excel. This API call is exactly what VB/VBA calls when you use a function like CDbl(). Be aware that a bit of errant formatting is all it takes for you to be provided with a DATE instead of a Double.

If you are absolutely, positive beyond sure that all the cells will contain numbers (doubles) and Excel will never return to you anything else, then by all means call JACOB's getDouble() directly and skip the conversion step.

Euro Micelli
  • 33,285
  • 8
  • 51
  • 70
  • Hi, yes, the data comes from a Range object, and from that I get the 2-dimensional SafeArray. And yes, it's made of Variants, but I know that I have doubles in that since we are dealing only with doubles. In JACOB SafeArray there is a method "toDoubleArray()" that does exactly that, converts to a double[], and it's native. That's what we wanted to use for the conversion, but we are stuck since we cannot directly (without a loop) extract the Variant in the 2-dimensional SafeArray that contains the values we need (the Variant can be converted easily to a SafeArray). – Milo Casagrande Jan 19 '11 at 08:57
  • I tried also the getVariants(), but as with the getDoubles(), I'm not getting anything out of it. I must be missing something for sure here on how to use those methods. Thanks anyway for your input! – Milo Casagrande Jan 19 '11 at 08:58
  • @Milo, sorry for not adding anything yet. I'm still scratching my head. – Euro Micelli Jan 20 '11 at 20:13
  • No worries... I'm still scratching my head too, but also have to take into account other work related issues. Since the procedure we have in place is working, we were only looking for an alternative, and hopefully faster, way. So, no rush at all! :-) – Milo Casagrande Jan 21 '11 at 11:54
  • Can you post a (slimmed down) version of the code that does work? That might help me figure out how the single call version should look like. – Euro Micelli Feb 01 '11 at 05:42