Grid to Excel

HMG Samples and Enhancements

Moderator: Rathinagiri

Post Reply
User avatar
koke
Posts: 84
Joined: Wed Aug 21, 2013 3:54 pm
DBs Used: DBF, mySql, mariaDB
Has thanked: 23 times
Been thanked: 24 times

Grid to Excel

Post by koke » Wed Jun 06, 2018 4:59 pm

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 173 times
,___,
[O.o]
/)__)
-”–”-
KoKe

User avatar
Steed
Posts: 366
Joined: Sat Dec 12, 2009 3:40 pm
Has thanked: 1 time
Been thanked: 2 times

Post by Steed » Thu Jun 07, 2018 8:59 pm

Thanks

huiyi_ch
Posts: 87
Joined: Sat May 21, 2016 5:27 am
Has thanked: 1 time
Been thanked: 10 times

Post by huiyi_ch » Thu Jun 07, 2018 10:51 pm

Thanks

User avatar
luisvasquezcl
Posts: 1117
Joined: Thu Jul 31, 2008 3:23 am
Location: Chile
Has thanked: 13 times
Been thanked: 14 times
Contact:

Post by luisvasquezcl » Fri Jun 08, 2018 3:53 am

Gracias Koke por compartir.
Saludos cordiales,

User avatar
koke
Posts: 84
Joined: Wed Aug 21, 2013 3:54 pm
DBs Used: DBF, mySql, mariaDB
Has thanked: 23 times
Been thanked: 24 times

Post by koke » Wed Aug 01, 2018 4:41 pm

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: 366
Joined: Sat Dec 12, 2009 3:40 pm
Has thanked: 1 time
Been thanked: 2 times

Post by Steed » Wed Aug 01, 2018 9:16 pm

Thansk, again

ASESORMIX
Posts: 81
Joined: Thu Oct 25, 2012 8:08 pm
Location: Bqto, Venezuela
Been thanked: 6 times

Post by ASESORMIX » Thu Aug 02, 2018 1:12 pm

Gracias por Contribuir.

AidTIC
Posts: 75
Joined: Mon Apr 22, 2013 8:29 am
Been thanked: 2 times

Post by AidTIC » Tue Sep 04, 2018 8:14 am

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

User avatar
serge_girard
Posts: 2166
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 472 times
Been thanked: 108 times
Contact:

Post by serge_girard » Tue Sep 04, 2018 9:44 am

Thanks !

Serge

User avatar
koke
Posts: 84
Joined: Wed Aug 21, 2013 3:54 pm
DBs Used: DBF, mySql, mariaDB
Has thanked: 23 times
Been thanked: 24 times

Post by koke » Tue Sep 11, 2018 5:42 pm

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