Export data to OpenOffice Calc and MS Office Excel

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: Export data to OpenOffice Calc and MS Office Excel

Post by mol »

Do anybody knows how to change font size and font name while exporting data to openoffice sheet?
User avatar
andyglezl
Posts: 1461
Joined: Fri Oct 26, 2012 7:58 pm
Location: Guadalajara Jalisco, MX
Contact:

Re: Export data to OpenOffice Calc and MS Office Excel

Post by andyglezl »

En OpenOffice no se, en Excel lo hago así.
-----------------------------------------------------
In OpenOffice I do not know, in Excel I do it that way.


oSheet:Range( "A1:D2" ):Font:Name := "Calibri"
oSheet:Range( "A1:D2" ):Font:Bold := .T.
oSheet:Range( "A1:D2" ):Font:Size := 12
Andrés González López
Desde Guadalajara, Jalisco. México.
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: Export data to OpenOffice Calc and MS Office Excel

Post by mol »

This does not work in OpenOffice.
It's more complicated.
User avatar
apais
Posts: 440
Joined: Fri Aug 01, 2008 6:03 pm
DBs Used: DBF
Location: uruguay
Contact:

Re: Export data to OpenOffice Calc and MS Office Excel

Post by apais »

Why don't you use libxlswriter ? It generates agnostic spreadsheets.
Angel Pais
Web Apps consultant/architect/developer.
HW_apache (webserver modules) co-developer.
HbTron (Html GUI for harbour desktop hybrid apps) co-developer.
https://www.hbtron.com
ROBROS
Posts: 256
Joined: Thu May 25, 2017 6:30 pm
DBs Used: DBF
Location: D 83071 Stephanskirchen

Re: Export data to OpenOffice Calc and MS Office Excel

Post by ROBROS »

Hi Marek,
I searched for:
openoffice calc functions change font

Maybe you can record the corresponding Makro?

I have no knowledge of VBA, but sometimes recording makros helps me a lot, Recording makros is certainly a feature in openoffice.

Robert
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: Export data to OpenOffice Calc and MS Office Excel

Post by mol »

I'm trying to do it in this way. VBA is very easy opposite to macros in openoffice (I'm not familiar with Java).
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: Export data to OpenOffice Calc and MS Office Excel

Post by mol »

It was so easy to realize, but so hard to find right method name :-D

Code: Select all

nFontSize := 22
nFontName := "ARIAL"
oSheet:getCellRangeByPosition(1,1,1,2):CharHeight := nFontSize
oSheet:getCellRangeByPosition(1,1,1,2):CharFontName := nFontName
if you want to set BOLD, you must do int in this way

Code: Select all

oSheet:getCellRangeByPosition(1,1,1,2):CharWeight := 150		// percent of font weight --- BOLD  ==>  "com.sun.star.awt.FontWeight.BOLD"
DONTKNOW The font weight is not specified/known.
THIN specifies a 50% font weight.
ULTRALIGHT specifies a 60% font weight.
LIGHT specifies a 75% font weight.
SEMILIGHT specifies a 90% font weight.
NORMAL specifies a normal font weight.
SEMIBOLD specifies a 110% font weight.
BOLD specifies a 150% font weight.
ULTRABOLD specifies a 175% font weight.
BLACK specifies a 200% font weight.

if you want to underline content of your cell:

Code: Select all

oSheet:getCellByPosition(0,i):CharUnderline := nLineType
where nLineType can be: (info from http://www.openoffice.org/api/docs/comm ... rline.html)
0 - not underline
1 - single line
2 - double line
3 - dot line (....)
4 - not underline ???
5 - dotted line ( _ _ _ _ )
6 - dotted line ( __ __ __ __ )
7 - dot and dotted ( _._._._._)
8 - _.._.._.._..
9 - ~~~~~~~~~
10 - ~~~~~~~~

If you want to use Italic font:

Code: Select all

oSheet:getCellByPosition(0,i):CharPosture := nFontSlant
where nFontSlant
0 - no slant
1 - slant not designed into the font (where font does not have italic option - it's slanted by openoffice)
2 - where font has italic option built-in
I used small piece of code to test it:

Code: Select all


 function test1
 	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("OpenOffice not installed?","Error")
	  RETURN Nil
	ENDIF
	oDocument := oDesktop:loadComponentFromURL("private:factory/scalc","_blank", 0, {})
	oSchedule := oDocument:GetSheets()
	oSheet := oSchedule:GetByIndex(0)

	for i:=0 to 100
		c := "Line: "+str(i,3)
		oSheet:getCellByPosition(0,i):SetString( c )
	next i
	
	for i:=0 to 10
		oSheet:getCellByPosition(0,i):CharUnderline := i
	next i
return
Mario Mansilla
Posts: 269
Joined: Wed Aug 13, 2008 2:35 pm
Location: Córdoba - Argentina

Re: Export data to OpenOffice Calc and MS Office Excel

Post by Mario Mansilla »

Hola Amigos :
necesito exportar a Excel una base de datos cuyos valores son : 00000001 ,001 , 0001 .
Si utilizo la propiedad Numberformat "###0" me coloca todos 1 alineados a la derecha .
oHoja:Columns(2):NumberFormat := "###0"

Si utilizo la propiedad Numberformat "@" me coloca todos 1 alineados a la izquierda .
oHoja:Columns(2):NumberFormat := "@"

Si directamente no coloco nada me asigna un valor numerico sin ceros

Que valor deberia usar para que al exportar me deje los valores tal como estan en la base de datos .

Saludos
Mario Mansilla

Hello friends :
I need to export to Excel a database whose values are: 00000001, 001, 0001.
If I use the Numberformat property "### 0" I put all 1 aligned to the right.
oHoja: Columns (2): NumberFormat: = "### 0"

If I use the Numberformat property "@" I put all 1 aligned to the left.
oHoja: Columns (2): NumberFormat: = "@"

If I do not place anything directly, it assigns me a numeric value without zeros

What value should I use to export the values as they are in the database?
User avatar
andyglezl
Posts: 1461
Joined: Fri Oct 26, 2012 7:58 pm
Location: Guadalajara Jalisco, MX
Contact:

Re: Export data to OpenOffice Calc and MS Office Excel

Post by andyglezl »

Al tener ceros a la izquierda, deben ser texto, por lo tanto deben de ir entre
comillas y no usar Numberformat "###0".

"00000001" ,"001" , "0001"
Andrés González López
Desde Guadalajara, Jalisco. México.
User avatar
koke
Posts: 116
Joined: Wed Aug 21, 2013 3:54 pm
DBs Used: DBF, mySql, mariaDB

Re: Export data to OpenOffice Calc and MS Office Excel

Post by koke »

Si es un número que quieres exportar como texto agrega una comilla sencilla antes del mismo.

---- google translate ----

If it is a number that you want to export as text, add a single quote before it.
,___,
[O.o]
/)__)
-”–”-
KoKe
Post Reply