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
Export Data XLS/ODS to DBF
Moderator: Rathinagiri
-
- Posts: 4
- Joined: Wed Dec 06, 2017 4:22 pm
- DBs Used: DBF
Re: Export Data XLS/ODS to DBF
Hola Wolfgang
Porqué no solamente exportar tu archivo ".xls" ó ".ods" como delimitado por comas ","
y lo importas con un Append en tu DBF ???
-----------------------------------------------------------------------------------------------------------
Hi Wolfgang
Why not just export your file ".xls" or "ODS" as comma delimited ","
And you import it with an Append on your DBF ???
APPEND FROM <xcFile>
[FIELDS <idField list>]
[<scope>] [WHILE <lCondition>] [FOR <lCondition>]
[SDF | DELIMITED [WITH BLANK | <xcDelimiter>] |
[VIA <xcDriver>]]
See:
http://www.hmgforum.com/viewtopic.php?t=1615
Porqué no solamente exportar tu archivo ".xls" ó ".ods" como delimitado por comas ","
y lo importas con un Append en tu DBF ???
-----------------------------------------------------------------------------------------------------------
Hi Wolfgang
Why not just export your file ".xls" or "ODS" as comma delimited ","
And you import it with an Append on your DBF ???
APPEND FROM <xcFile>
[FIELDS <idField list>]
[<scope>] [WHILE <lCondition>] [FOR <lCondition>]
[SDF | DELIMITED [WITH BLANK | <xcDelimiter>] |
[VIA <xcDriver>]]
See:
http://www.hmgforum.com/viewtopic.php?t=1615
Andrés González López
Desde Guadalajara, Jalisco. México.
Desde Guadalajara, Jalisco. México.
-
- Posts: 4
- Joined: Wed Dec 06, 2017 4:22 pm
- DBs Used: DBF
Re: Export Data XLS/ODS to DBF
Thanks for the information on this way I did not even think.
The next problem will be the adoption of the text / CSV format. I am already looking for it in the documents of Open Office. Only the translation does not work properly.
The next problem will be the adoption of the text / CSV format. I am already looking for it in the documents of Open Office. Only the translation does not work properly.
Re: Export Data XLS/ODS to DBF
Hi Wolfgang!
You don't need to abandon openoffice.
Please look at my little sample reading data from OpenOffice calc document:
You need to create workseet test.ods
I hope it will give you an idea how to continue your work!
You don't need to abandon openoffice.
Please look at my little sample reading data from OpenOffice calc document:
Code: Select all
#include <minigui.ch>
function Main
MsgInfo("start test")
ReadDataFromOpenOffice()
return
*-----------------
function ReadDataFromOpenOffice
local oServiceManager,oDesktop,oDocument,oSchedule,oSheet,oCell,oColums,oColumn
local i
local cFile := "test.ods"
local cPath
//
//we need to convert file name to url format
cPath := substr(exename(),1,rat("\",exename()))
cPath := strtran(cPath, "\", "/")
cPath += cFile
begin sequence with { |e| break(e)}
oServiceManager := TOleAuto():New("com.sun.star.ServiceManager")
oDesktop := oServiceManager:createInstance("com.sun.star.frame.Desktop")
recover using e
oDeskTop := NIL
end sequence
IF oDesktop = NIL
MsgStop("No accesss to OpenOffice","Error")
RETURN Nil
ENDIF
oDocument := oDesktop:loadComponentFromURL("file:///"+ cPath, "_blank", 0, {})
oSchedule := oDocument:GetSheets()
oSheet := oSchedule:GetByIndex(0)
for nCol = 0 to 4
oCell := oSheet:getCellByPosition(nCol,0)
nCellValue := oCell:value
cCellValue := oCell:string
msgDebug(nCol, nCellValue, cCellValue)
next
//msginfo ("Ready Exit Open Office",)
oDocument:Close( .T. )
return
I hope it will give you an idea how to continue your work!
-
- Posts: 4
- Joined: Wed Dec 06, 2017 4:22 pm
- DBs Used: DBF
Re: Export Data XLS/ODS to DBF
That's exactly what I was looking for. But in Excel (2007) also works a CSV file without problems. In the Open Office the text import window with the separation option tab,;; Blank. What I found so far does not want to translate into harbor. The text file is with; separated and writes everything in column A. I now try the CSV data with Fopen and Freadstr read and prepare. But the previous path was better in my program. Thanks for your help
Re: Export Data XLS/ODS to DBF
How to open an excel file to not be raedonly the data input attributes ?
IF ( oServiceManager := win_oleCreateObject( "com.sun.star.ServiceManager" ) ) != NIL
oDesktop := oServiceManager:createInstance( "com.sun.star.frame.Desktop" )
oBook := oDesktop:loadComponentFromURL( "file:///"+cDir+"\Poreska_prijava_pdv.xls", "_blank",0, {} )
oSheet := oBook:getSheets():getByIndex(0)
oSheet:getCellRangeByName( "B10" ):SetString('U PERIODU OD: '+ Dtoc(Datup1) + ' DO: '+ Dtoc(Datup2))
etc...
Thanks.
IF ( oServiceManager := win_oleCreateObject( "com.sun.star.ServiceManager" ) ) != NIL
oDesktop := oServiceManager:createInstance( "com.sun.star.frame.Desktop" )
oBook := oDesktop:loadComponentFromURL( "file:///"+cDir+"\Poreska_prijava_pdv.xls", "_blank",0, {} )
oSheet := oBook:getSheets():getByIndex(0)
oSheet:getCellRangeByName( "B10" ):SetString('U PERIODU OD: '+ Dtoc(Datup1) + ' DO: '+ Dtoc(Datup2))
etc...
Thanks.