asked on

Read data from Excel file with Oppen Office automation

Hello Experts,

I'm able to open a read Excel files with Microsoft Excel automation, but how do i do it with Open Office automation?
I've seen lots of articles on the web, but none show me how to navigate through cells and get the values

I've seen this example and it works, but only opens the in the open office application, lol..

LOCAL loOfcMgr, loDesktop, loDocument, args(1), ;
loCoreReflection, loPropertyValue, loText,;
loCursor

loOfcMgr = CREATEOBJECT("com.sun.star.ServiceManager")
loDesktop = loOfcMgr.createInstance("com.sun.star.frame.Desktop")

loCoreReflection = loOfcMgr.createInstance("com.sun.star.reflection.CoreReflection")

loPropertyValue = CREATEOBJECT("Empty")

loCoreReflection.forName("com.sun.star.beans.PropertyValue").CREATEOBJECT(@loPropertyValue)

args[1] = loPropertyValue
args[1].NAME = "ReadOnly"
args[1].VALUE = .F.

COMARRAY(loDesktop,10)

loDocument = loDesktop.loadComponentFromURL("file:///C:/tmp/example.xls","blank", 0, @args)

Open in new window

I would like to ask the user for the file and then loop trough the cells and get the values like i do with Microsoft Excel automation like this:

lcTab=GETFILE('XLS', 'File', 'Select', 0, 'Select the File to Import')
#DEFINE XLLASTCELL 10
loExcel = CREATEOBJECT("Excel.Application")
loWorkBook = loExcel.Workbooks.OPEN(STRTRAN(lcTab,'"',''))
loActiveSheet = loExcel.ActiveSheet
u_rows=loExcel.Activeworkbook.activesheet.UsedRange.Rows.Count
FOR i = 3 TO u_rows
  myval1 = (ALLTRIM(loactivesheet.cells(i,1).text))
  myval2 = (ALLTRIM(loactivesheet.cells(i,2).text)
  myval3 = (ALLTRIM(loactivesheet.cells(i,3).text)
ENDFOR

Open in new window

Anyone know a code example like this one but with open office automation?

Thx in advanced,
Miguel

Rgonzo1971

Hi,

Since OO does read Excel files, why don't you open it within OO

Regards

justaphase

ASKER

? Rgonzo1971...
I don't understand?

I talking about get data from an Excel file programmatically with visual foxpro using OO automation...
What are you saying?

Rgonzo1971

So you want vfp code

maybe this site is for you

http://fox.wikis.com/wc.dll?Wiki~ExcelAutomation
one example

#define xlLastCell 11
#define xlMaximized -4137
#define xlRangeAutoformatClassic2 2
#define xlPortrait 1

use MyTable  && or SELECT * INTO MyCursor

cFileName = "MyXLSFile"  && or whatever, including path
*copy to (cFileName) fields (cFields) TYPE xls
copy to (cFileName) TYPE xls

* then open excel and make the data look good, like this
oExcel = CreateObject("Excel.Application")
if vartype(oExcel) != "O"
  * could not instantiate Excel object
  * show an error message here
  return .F.
endif

* make excel visible during development
*oExcel.visible = .T.

* open the workbook you just created
oExcel.SheetsInNewWorkBook = 1
oWorkbook = oExcel.Workbooks.Open(cFileName)

* rename the Sheet to whatever you like
oActiveSheet = oExcel.ActiveSheet
oActiveSheet.Name = "MyData"

oExcelApp = oExcel.Application
oExcelApp.WindowState = xlMaximized

* find address of last occupied cell
lcLastCell = oExcel.ActiveCell.SpecialCells(xlLastCell).Address()

* resize all columns
lnMarker1 = at("$",lcLastCell,1)  && i.e. 1 when lcLastCell = "$AF$105"
lnMarker2 = at("$",lcLastCell,2)  && i.e. 4 when lcLastCell = "$AF$105"
lnStartPos = lnMarker1 + 1
lnStrLen = lnMarker2 - lnStartPos
oExcel.Columns("A:" + substr ;
  (lcLastCell,lnStartPos,lnStrLen)).EntireColumn.AutoFit

* you can even add a nice autoformat
oExcel.Range("A" + alltrim(str(nTOPBLANKROWS+1)) + ":" + lcLastCell).Select
oExcel.Selection.AutoFormat(xlRangeAutoformatClassic2,.t.,.t.,.t.,.t.,.t.,.t.)

* set Excel Print Area
oActiveSheet.PageSetup.PrintArea = "$A$1:" + lcLastCell

* define printed page footer
With loActiveSheet.PageSetup
	*.LeftHeader   = ""
	*.CenterHeader = ""
	*.RightHeader  = ""
	.LeftFooter   = "&BMy Footer goes here&B"
	.CenterFooter = "&D"
	.RightFooter  = "Page &P"
	*.PrintHeadings  = .F.
	.PrintGridlines = .F.
	.CenterHorizontally = .T.
	.CenterVertically = .F.
	.Orientation = xlPortrait
endwith

* save Excel file in new Excel format (COPY TO XLS uses old format)
oWorkbook.Save()

* display finished product to the user
oExcel.visible = .T.

Open in new window

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

justaphase

ASKER

Rgonzo1971 you're just trowing answers hoping that works...
In my question i already showed a code using Microsoft API, i know how it works, i'm searching to do that with Open Office API...

justaphase

ASKER

Thx pcelba,

With that manual i was able to build the code.
In case someone needs, Here is the code:

LOCAL oOfcMgr, oDesktop, oDocument, args(1), ;
oCoreReflection, loPropertyValue, loText,;
oCursor

oOfcMgr = CREATEOBJECT("com.sun.star.ServiceManager")
oDesktop = oOfcMgr.createInstance("com.sun.star.frame.Desktop")

oCoreReflection = oOfcMgr.createInstance("com.sun.star.reflection.CoreReflection")

loPropertyValue = CREATEOBJECT("Empty")

oCoreReflection.forName("com.sun.star.beans.PropertyValue").CREATEOBJECT(@loPropertyValue)

args[1] = loPropertyValue

COMARRAY(oDesktop,10)

oDocument = oDesktop.loadComponentFromURL("file:///C:/tmp/example.xls","_blank", 0, @args)

oSheets = oDocument.Sheets()
oSheet1 = oSheets.getByIndex(0)

Curs = oSheet1.createCursor()
Curs.gotoEndOfUsedArea(.t.)
NumRows = Curs.Rows.Count

FOR nRow = 3 TO NumRows
	messagebox(oSheet1.getCellByPosition (0 , nRow).Value)
ENDFOR

Open in new window