Export Data XLS/ODS to DBF
Posted: Wed Dec 13, 2017 5:29 pm
I'm not sure if I'm right here. I have a problem with open Office Calc. With Excel, the example works very well. I don't know about objects. In the HMG debugger I get the value of ODoc class Win_Oleauto object. Can someone give me the straightening way to read and cache the cell values. With Kind regards
/*
* Converter
* (c) 2017 Wolfgang Schwarz
* Version 1 Build 30
*/
#include "minigui.ch"
PROCEDURE MAIN()
LOCAL oExcel, oWorksheet, i
DEFINE WINDOW Form_Main ;
AT 0,0 ;
WIDTH 400 ;
HEIGHT 200 ;
MAIN ;
TITLE 'Excel Test'
DEFINE MAIN MENU
POPUP 'Test Excel / Office '
ITEM 'Start' ACTION ExcelTest()
END POPUP
END MENU
END WINDOW
CENTER WINDOW Form_Main
ACTIVATE WINDOW Form_Main
Return
function ExcelTest
# define oSheet
# define oCell
cReadData := Getfile ( { {'Excel Files','*.xls'} , {'CSV Files','*.csv'} , {'OpenOfiiceCalc Files','*.ods'}} , 'Open File' )
*Example: Excel file A1 = Name, B1 PhoneNumber, in A 2 - 10 Name, B 2 - 10 Numbers
if empty (cReadData)
msgbox ("No Data",)
return
endif
*seek now Excel or OpenOfficeCalc
if ( oExcel := win_oleCreateObject( 'Excel.Application' ) ) != NIL
msginfo ("Found Excel")
oExcel := TOleAuto():New( 'Excel.Application' )
if Ole2TxtError() != 'S_OK'
Msginfo ( 'Error in Excel .' )
RETURN NIL
endif
cStartprogram = "excel"
else
IF ( oServiceManager := win_oleCreateObject( "com.sun.star.ServiceManager" ) ) != NIL
msginfo ("Found Openoffice")
cStartprogram = "openofficecalc"
else
msginfo ( "Error - in OpenOffice.", win_oleErrorText() )
return nil
endif
endif
*open exec
if cStartprogram = "excel"
CalcProg = "Excel.Application"
oExcel := CreateObject(CalcProg)
oExcel:Workbooks:Open(cReadData)
oExcel:Visible := .t.
endif
* open office
if cStartprogram = "openofficecalc"
oDesktop := oServiceManager:createInstance( "com.sun.star.frame.Desktop" )
oDoc := oDesktop:loadComponentFromURL( "file:///"+(cReadData), "_blank", 0, {} )
endif
if cStartprogram = "excel"
for i = 2 to 10
cTypeA=oExcel:WorkSheets(1):cells(i,1):text
cTypeB=oExcel:WorkSheets(1):cells(i,2):text
msgbox ("Cell A : "+cTypeA + " Cell B : " + cTypeB )
next
msginfo ("Ready Exit Excel",)
oExcel:Quit()
endif
if cStartprogram = "openofficecalc"
for i = 2 to 10
* this does not work
* oDoc:=getSheets(0)
* getCellbyPosition:=getSheets(0)
* cTypeA=getCellByPosition(i,1)
* msgbox ("Cell A : "+cTypeA)
next
msginfo ("Ready Exit Open Office",)
oDoc:Close( .T. )
endif
/*
* Converter
* (c) 2017 Wolfgang Schwarz
* Version 1 Build 30
*/
#include "minigui.ch"
PROCEDURE MAIN()
LOCAL oExcel, oWorksheet, i
DEFINE WINDOW Form_Main ;
AT 0,0 ;
WIDTH 400 ;
HEIGHT 200 ;
MAIN ;
TITLE 'Excel Test'
DEFINE MAIN MENU
POPUP 'Test Excel / Office '
ITEM 'Start' ACTION ExcelTest()
END POPUP
END MENU
END WINDOW
CENTER WINDOW Form_Main
ACTIVATE WINDOW Form_Main
Return
function ExcelTest
# define oSheet
# define oCell
cReadData := Getfile ( { {'Excel Files','*.xls'} , {'CSV Files','*.csv'} , {'OpenOfiiceCalc Files','*.ods'}} , 'Open File' )
*Example: Excel file A1 = Name, B1 PhoneNumber, in A 2 - 10 Name, B 2 - 10 Numbers
if empty (cReadData)
msgbox ("No Data",)
return
endif
*seek now Excel or OpenOfficeCalc
if ( oExcel := win_oleCreateObject( 'Excel.Application' ) ) != NIL
msginfo ("Found Excel")
oExcel := TOleAuto():New( 'Excel.Application' )
if Ole2TxtError() != 'S_OK'
Msginfo ( 'Error in Excel .' )
RETURN NIL
endif
cStartprogram = "excel"
else
IF ( oServiceManager := win_oleCreateObject( "com.sun.star.ServiceManager" ) ) != NIL
msginfo ("Found Openoffice")
cStartprogram = "openofficecalc"
else
msginfo ( "Error - in OpenOffice.", win_oleErrorText() )
return nil
endif
endif
*open exec
if cStartprogram = "excel"
CalcProg = "Excel.Application"
oExcel := CreateObject(CalcProg)
oExcel:Workbooks:Open(cReadData)
oExcel:Visible := .t.
endif
* open office
if cStartprogram = "openofficecalc"
oDesktop := oServiceManager:createInstance( "com.sun.star.frame.Desktop" )
oDoc := oDesktop:loadComponentFromURL( "file:///"+(cReadData), "_blank", 0, {} )
endif
if cStartprogram = "excel"
for i = 2 to 10
cTypeA=oExcel:WorkSheets(1):cells(i,1):text
cTypeB=oExcel:WorkSheets(1):cells(i,2):text
msgbox ("Cell A : "+cTypeA + " Cell B : " + cTypeB )
next
msginfo ("Ready Exit Excel",)
oExcel:Quit()
endif
if cStartprogram = "openofficecalc"
for i = 2 to 10
* this does not work
* oDoc:=getSheets(0)
* getCellbyPosition:=getSheets(0)
* cTypeA=getCellByPosition(i,1)
* msgbox ("Cell A : "+cTypeA)
next
msginfo ("Ready Exit Open Office",)
oDoc:Close( .T. )
endif