Export Data XLS/ODS to DBF

HMG Samples and Enhancements

Moderator: Rathinagiri

Post Reply
wolfgang.schwarz
Posts: 4
Joined: Wed Dec 06, 2017 4:22 pm
DBs Used: DBF

Export Data XLS/ODS to DBF

Post by wolfgang.schwarz »

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
User avatar
andyglezl
Posts: 1461
Joined: Fri Oct 26, 2012 7:58 pm
Location: Guadalajara Jalisco, MX
Contact:

Re: Export Data XLS/ODS to DBF

Post by andyglezl »

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
Andrés González López
Desde Guadalajara, Jalisco. México.
wolfgang.schwarz
Posts: 4
Joined: Wed Dec 06, 2017 4:22 pm
DBs Used: DBF

Re: Export Data XLS/ODS to DBF

Post by wolfgang.schwarz »

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.
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: Export Data XLS/ODS to DBF

Post by mol »

Hi Wolfgang!
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
You need to create workseet test.ods
Image


I hope it will give you an idea how to continue your work!
wolfgang.schwarz
Posts: 4
Joined: Wed Dec 06, 2017 4:22 pm
DBs Used: DBF

Re: Export Data XLS/ODS to DBF

Post by wolfgang.schwarz »

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
User avatar
nekbmm
Posts: 118
Joined: Sat Jul 16, 2016 3:16 am
DBs Used: DBF,SQLite
Location: Ivanjica, Serbia

Re: Export Data XLS/ODS to DBF

Post by nekbmm »

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.
Post Reply