Function Excel

[jrendon]

Here are some functions to send data to excel I have been collecting and also an example for their use, and I hope you like this compilation.

Greetings.

Juan Rendón

FUNCTIONSXLS.zip

Functions to send data to xls

(17.05 KiB) Downloaded 888 times

[Rathinagiri]

Thanks a lot Juan Rendón

[sudip]

Hello Juan Rendón,
Thank you very much for sharing those functions.
I also want to share one header file (excel.ch)

excel.zip

(8.98 KiB) Downloaded 639 times

and 2 very simple functions:

ExcelDt: It is very useful and doesn't give error.
ExcelBorder: Very simple border drawing function.

Code: Select all

function ExcelDt(mDt)
   return (ltrim(str(day(mDt)))+"-"+left(cmonth(mDt), 3)+"-"+str(year(mDt), 4))
   
function ExcelBorder(oSheet, col1, row1, col2, row2)
// row: numeric, col: character eg., A, B, AB etc.
   local mRange
   mRange = col1+ltrim(str(row1))+":"+col2+ltrim(str(row2))
   with object oSheet
     :Range(mRange):Borders(xlEdgeTop):LineStyle = xlContinuous
     :Range(mRange):Borders(xlEdgeLeft):LineStyle = xlContinuous
     :Range(mRange):Borders(xlEdgeBottom):LineStyle = xlContinuous
     :Range(mRange):Borders(xlEdgeRight):LineStyle = xlContinuous
   end with   

With best regards.

Sudip

[Rathinagiri]

Thanks a lot Sudip.

Is there anyway to deal with openoffice calc?

[sudip]

Rathi,
I got some codes regarding open office from http://www.embalajesterra.com/misdoc/pr ... ramas.html.
As far I know, samples for OpenOffice in this site contain Minigui Extended codes, (which can be easily ported to HMG Official).
Code sample for OpenOffice Calc is as follows:

Code: Select all

STATIC FUNCTION Lis_OpenOffice()
local oServiceManager,oDesktop,oDocument,oSchedule,oSheet,oCell,oColums,oColumn

Crear_fichero()

CURSORWAIT()

// inicializa
oServiceManager := TOleAuto():New("com.sun.star.ServiceManager")
oDesktop := oServiceManager:createInstance("com.sun.star.frame.Desktop")
IF oDesktop = NIL
MsgStop('OpenOffice Calc no esta disponible','error')
RETURN Nil
ENDIF
oDocument := oDesktop:loadComponentFromURL("private:factory/scalc","_blank", 0, {})

// tomar hoja
oSchedule := oDocument:GetSheets()

// tomar primera hoja por nombre oSheet := oSchedule:GetByName("Hoja1")
// o por indice
oSheet := oSchedule:GetByIndex(0)

oSheet:getCellByPosition(0,0):SetString("Suizo Pruebas")
oSheet:getCellByPosition(0,1):SetString(W_Imp1.L_Version1.value)
oSheet:getCellByPosition(0,2):SetString(W_Imp1.L_Version2.value)
oSheet:getCellByPosition(0,3):SetString(W_Imp1.L_Version3.value)
oSheet:getCellByPosition(0,4):SetString(W_Imp1.L_Version4.value)

LIN:=6

oSheet:getCellByPosition(0,LIN):SetString("Codigo")
oSheet:getCellByPosition(1,LIN):SetString("1º plato")
oSheet:getCellByPosition(2,LIN):SetString("2º plato")
oSheet:getCellByPosition(3,LIN):SetString("Postre")
oSheet:getCellRangeByPosition(0,LIN,0,LIN):HoriJustify:=3
oSheet:getCellRangeByPosition(0,LIN,3,LIN):CharWeight:=150 //NEGRITA
aMiColor:={255,255,150} //AMARILLOPALIDO
oSheet:getCellRangeByPosition(0,LIN,3,LIN):CellBackColor:=RGB(aMiColor[3],aMiColor[2],aMiColor[1])

LIN++

SELEC MENUS
GO TOP
DO WHILE .NOT. EOF()
DO EVENTS
oSheet:getCellByPosition(0,LIN):SetValue(CodMenu)
oSheet:getCellByPosition(1,LIN):SetString(Comida1)
oSheet:getCellByPosition(2,LIN):SetString(Comida2)
oSheet:getCellByPosition(3,LIN):SetString(Comida3)
oSheet:getCellRangeByPosition(0,LIN,0,LIN):NumberFormat:=4 //#.##0,00

LIN++
SKIP

ENDDO

oColumns:=oSheet:getColumns()
oColumns:getByIndex(1):setPropertyValue("OptimalWidth", .T.)
oColumns:getByIndex(2):setPropertyValue("OptimalWidth", .T.)

CURSORARROW()

RETURN NIL

STATIC FUNCTION Crear_fichero()
IF .NOT. FILE("MENUS.DBF")
aArq:={}
Aadd( aArq , { 'CODMENU' , 'N' , 10 , 0 } )
Aadd( aArq , { 'COMIDA1' , 'C' , 30 , 0 } )
Aadd( aArq , { 'COMIDA2' , 'C' , 30 , 0 } )
Aadd( aArq , { 'COMIDA3' , 'C' , 30 , 0 } )
DBCreate( "MENUS" , aArq )
USE MENUS
FOR N=1 TO 100
APPEND BLANK
REPLACE CODMENU WITH N
REPLACE COMIDA1 WITH "PRIMER PLATO "+LTRIM(STR(N))
REPLACE COMIDA2 WITH "SEGUNDO PLATO "+LTRIM(STR(N))
REPLACE COMIDA3 WITH "POSTRE "+LTRIM(STR(N))
NEXT
ELSE
USE MENUS
ENDIF
RETURN NIL

With best regards.

Sudip

[Rathinagiri]

Oh! Thanks a lot Sudip.

[mustafa]

Hola jrendon y sudip
Estan muy bien las rutinas , felicidades

Hello jrendon and sudip
Routines are very well, congratulations

Mustafa

[sudip]

Hello Rathi,
I must say that I got those codes (regarding OpenOffice) and url of that site from Minigui Yahoo Group.
Regards.
Sudip

[luisvasquezcl]

Hi all,
excellent contributions, thank you very much.
regards,
Luis Vasquez

[Vanguarda]

Hi friends,

Thanks JRendon and Sudip for sharing it nice work with us.
It is very useful for me.


with best regards,