Grid to Excel

HMG Samples and Enhancements

Moderator: Rathinagiri

Post Reply
User avatar
koke
Posts: 116
Joined: Wed Aug 21, 2013 3:54 pm
DBs Used: DBF, mySql, mariaDB

Grid to Excel

Post by koke »

Buenas tardes.
Una pequeña contribución para los que necesiten enviar a Excel la información de un Grid, espero que sea de utilidad para ustedes.
Saludos.

translate by google
Good afternoon.
A small contribution for those who need to send Grid information to Excel, I hope it will be useful for you.
Greetings.
Attachments
GridtoExcel.zip
Compilado Con / Compiled With hmg 3.4.3
No incluye exe / exe not included
(2.95 KiB) Downloaded 440 times
,___,
[O.o]
/)__)
-”–”-
KoKe
User avatar
Steed
Posts: 427
Joined: Sat Dec 12, 2009 3:40 pm

Re: Grid to Excel

Post by Steed »

Thanks
huiyi_ch
Posts: 172
Joined: Sat May 21, 2016 5:27 am

Re: Grid to Excel

Post by huiyi_ch »

Thanks
User avatar
luisvasquezcl
Posts: 1258
Joined: Thu Jul 31, 2008 3:23 am
Location: Chile
Contact:

Re: Grid to Excel

Post by luisvasquezcl »

Gracias Koke por compartir.
Saludos cordiales,
User avatar
koke
Posts: 116
Joined: Wed Aug 21, 2013 3:54 pm
DBs Used: DBF, mySql, mariaDB

Re: Grid to Excel

Post by koke »

Buenos días
Realice una actualización del archivo excelPrint para poder imprimir Grid con más de 26 columnas.
Google translate
Good Morning
Update the excelPrint file to be able to print Grid with more than 26 columns.

Code: Select all

#include <hmg.ch>
*******************************************************************************
*** Elaborada por Jorge Benavides 
*** Function imprimexls
*** Parameters
***		windowname			el nombre de la forma que contiene el grid
***
***		gridname			el nombre del grid que se quiere imprimir
***
***		repotitle			El título del Reporte como aparecerá en Excel
***
***		papers				el tamaño del papel 1 es carta 5 oficio
***
***		orient				La orientación del papel &&'xlLandscape' 1 && 
***								'xlPortrait' 2
***
***		autoalign			Para que todas las columnas tengan el mismo ancho 
***								1 para si 2 para no
*******************************************************************************
Function imprimexls()
Parameters windowname,gridname,repotitle,papers,orient,autoalign
Private nRow:=1, oSheet, oExcel, aprompt:={}, afldnm:={}
Private tituloimp:="", ColumnasImp:={}, NoColImp:=""
	_iniciaexcel()
	_llenainfo()
	_terminaexcel(autoalign)
Return Nil

Function _llenainfo()
	Local elobjeto:="",therecs:=0,theitem:=""
	therecs := getProperty(windowname,gridname,'itemcount')
	For i:=1 To therecs 
		For g:=1 To getproperty(windowname,gridname,'columncount')
			theitem := getProperty(windowname,gridname,'cellex',i,g)
			oSheet:Cells(nRow,g):Value := AllTrim(theitem)
		Next
		nRow++
	Next
Return Nil

*****************************************************************
***
***			Inicialización del archivo de Excel
***			se crean los títulos de columna
***			Por Jorge Benavides
***	Actualizada el 01/08/2018
*****************************************************************
Function _iniciaexcel()
//	Declare Window &windowname
	Wait Window "Creating Excel File Please Wait ! . . ." NOWait    // ini wait window
   oExcel = CREATEObject( "Excel.Application" ) // create object

   oExcel:WorkBooks:Add() // create book
   oSheet = oExcel:ActiveSheet // create sheet
	oExcel:ActiveSheet:PageSetup:PaperSize:= iif(papers>0,papers,1) //Paper size
	With Object oSheet:PageSetup
		:Orientation := iif(orient>0,orient,1) //&&'xlLandscape' 1 && 'xlPortrait' 2
	End
   nRow++ //let to rows at the begining of file
   nRow++ // to use later
	With Object oSheet
	   For _i := 1 To getproperty(windowname,gridname,'columncount')
			 :Cells(nRow, _i):Value = getproperty(windowname,gridname,'columnHeader',_i)
	   Next
	End
	nRow++
Return Nil

*****************************************************************
***
***			Terminación del archivo de Excel
***			se aplica la configuración del archivo
***			Por Jorge Benavides
***	Actualizada el 01/08/2018
*****************************************************************
Function _terminaexcel()
Parameters autoalign
	Local ActRow:=0, ini := 0, letraini := 0

	nRow++
	nRow++
	
	letraini := convertToTitle(getproperty(windowname,gridname,'columnCount'))

	If repotitle <> NIL
	NoColImp1 := "A1:" + letraini + "1"
		 oSheet:Range(NoColImp1):Merge()
		 oSheet:Cells(1,1):Font:Size := 12
		 oSheet:Cells( 1, 1 ):Value := repotitle
	EndIf
	oSheet:Range("A1"):Select()
	nRow++
	NoColImp1 := "A1:" + letraini + "1"
	If autoalign = 1
		oSheet:Columns("A:" + letraini):AuToFit()
	EndIf 
	For i=1 To getproperty(windowname,gridname,'columncount')
		oSheet:Cells( 3, i ):Font:Bold := .T.
	Next
	oSheet:PageSetup:PrintTitleRows = "$1:$3"
	oExcel:Visible = .T.
	Wait Clear
Return Nil

*****************************************************************
*** 
*** https://helloacm.com/cc-coding-exercise-excel-sheet-column-number-and-title-conversion/
***
***			Convertida de C++ a Harbour
***			Por Jorge Benavides
***	Agregada el 01/08/2016
*****************************************************************
Function convertToTitle(n)
Local r := ""
	while (n >= 0)
		r := chr(65 + (n - 1) % 26) + r
		n := (n - 1) / 26
	End
	r := Right(r,len(r)-1)
Return r
,___,
[O.o]
/)__)
-”–”-
KoKe
User avatar
Steed
Posts: 427
Joined: Sat Dec 12, 2009 3:40 pm

Re: Grid to Excel

Post by Steed »

Thansk, again
ASESORMIX
Posts: 189
Joined: Thu Oct 25, 2012 8:08 pm
Location: Bqto, Venezuela

Re: Grid to Excel

Post by ASESORMIX »

Gracias por Contribuir.
User avatar
AidTIC
Posts: 117
Joined: Mon Apr 22, 2013 8:29 am
DBs Used: DBF
Contact:

Re: Grid to Excel

Post by AidTIC »

Primero, muchas gracias por esta contribución.

y en segundo, habría posibilidades de exportar a excel, tanto colores de fuente como tipo de letra.

Mil Gracias

+ + + + + + + + + + + + + + + + +

First, thank you very much for this contribution.

and second, there would be possibilities to export to excel, both font colors and typeface.

Thank you
Skydone Solutions
www.skydone.com
User avatar
serge_girard
Posts: 3158
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Contact:

Re: Grid to Excel

Post by serge_girard »

Thanks !

Serge
There's nothing you can do that can't be done...
User avatar
koke
Posts: 116
Joined: Wed Aug 21, 2013 3:54 pm
DBs Used: DBF, mySql, mariaDB

Re: Grid to Excel

Post by koke »

Buenos días.
AidTIC, probablemente ya encontraste la solución pero si no aquí tienes
saludos.

Good day.
AidTIC, provably you already found the answer to your question if not here you are.
greetings.

Code: Select all

oSheet:cells(nRow,nCol):interior:color := RGB(0,0,250) // modifica el color de fondo de la celda // change cell back color
oSheet:cells(nRow,nCol):font:color := RGB(250,250,250) // modifica el color de la letra // change fore color
oSheet:cells(nRow,nCol):font:name := "Calibri" //modifica el tipo de letra // change font type
oSheet:cells(nRow,nCol):font:size := 12 // modifica el tamaño de la letra // change font size
,___,
[O.o]
/)__)
-”–”-
KoKe
Post Reply