---
Это сообщение проверено на вирусы антивирусом Avast.
http://www.avast.com

Hi all,

I use OpenOffice ActiveX object to read data from Excel sheet. The code is:

LOCAL oServiceManager, oDesktop, oCoreReflection, aPropertyValue :=
ARRAY(2), oDocument, aArgs, oRange
LOCAL VT_VARIANT := 0x0000000c
LOCAL VT_ARRAY   := 0x00002000
LOCAL oSheets, oSheet, nBegCol, nBegRow, nEndCol, nEndRow, aData
   The service manager is always the starting point
   If there is no office running then an office is started up
  oServiceManager := CreateObject( 'com.sun.star.serviceManager')
  oServiceManager :=oServiceManager:dynamicCast( ActiveXObject() )

   Create the CoreReflection service that is later used to create structs
  oCoreReflection :=
oServiceManager:createInstance("com.sun.star.reflection.CoreReflection")

   Create the Desktop
  oDesktop := oServiceManager:createInstance("com.sun.star.frame.Desktop")
  aPropertyValue[1] :=
oServiceManager:Bridge_GetStruct("com.sun.star.beans.PropertyValue")
  aPropertyValue[1]:Name := "AsTemplate"
  aPropertyValue[1]:Value := .T.

  aPropertyValue[2] :=
oServiceManager:Bridge_GetStruct("com.sun.star.beans.PropertyValue")
  aPropertyValue[2]:Name := "Hidden"
  aPropertyValue[2]:Value := .T.
  aArgs := VTType():new(aPropertyValue, VT_ARRAY + VT_VARIANT )
  oDocument :=
oDesktop:loadComponentFromURL("File:///d:\DK_011-96_KSPOVO.xls", "_blank", 
0, @aArgs)
  oSheets   := oDocument:getSheets()
  oSheet    := oSheets:getByIndex(0)
  nBegCol   := 0
  nBegRow   := 5
  nEndCol   := LEN(oSheet:Data[1])-1
  nEndRow   := 7
  oRange    := oSheet:getCellRangeByPosition(nBegCol, nBegRow, nEndCol,
nEndRow)
  aData     := oRange:getDataArray()

The result must be the array, which consists of 3 arrays - 6th, 7th and 8th
rows of Excel sheet. But I get 8th row 3 times. So every time I get the
array of last row repeated (nEndRow-nEndCol+1) times. The same code in C++ 
works properly. Looks like the type conversion does not work properly. Any 
good idea


Best regards,

Altiy


---
Это сообщение проверено на вирусы антивирусом Avast.
http://www.avast.com

Now this is actually a interesting piece of code.
Can you copy/paste the part of C++ source you modelled this .prg after ?

Will any xls/ods spreadsheet do for reading?

Other option is to use SQL to query data from spreadsheets.

It is something regarding querying more that 1 row of data via methods.

All test i did point to that, that if there is more than 1 row of data,
strange things happen. Who's fault it actually is (alaska or lo) cannot be
said from my standpoint

but if you query row by row, data is o.k. , I used getCellRangeByName method
as it is more intuitive.

  oRange    := oSheet:getCellRangeByName("A1:G1")
  aData     := oRange:getDataArray()

  oRange    := oSheet:getCellRangeByName("A2:G2")
  aData     := oRange:getDataArray()

  oRange    := oSheet:getCellRangeByName("A3:G3")
  aData     := oRange:getDataArray()

So solution for you should be actually quite straighforward. Write a basic
function, that does row by row query inside table, combine data into single
array and return it.

"Altiy Zemlitskiy" <altiy@wgsoftpro.com> wrote in message
news:575933d0$3c5a6921$1122bd@news.alaska-software.com...
>Now this is actually a interesting piece of code.
>Can you copy/paste the part of C++ source you modelled this .prg after ?
>
>unfortunately, I have just piece of code of interpreter, which was written on
C++. It uses command on local language, not easy to read
>
>Will any xls/ods spreadsheet do for reading?
>
>Surely. At least it can be used, but speed would be much more better if we
read all range, not line by line
>
>
>Other option is to use SQL to query data from spreadsheets.
>
>Do you mean ODBC? I’m not sure that we can use it w/o Excel installed
>
>
>Best regards,
>Altiy
>
>---
>Это сообщение проверено на вирусы антивирусом Avast.
>http://www.avast.com