Export data to OpenOffice Calc and MS Office Excel

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

User avatar
mol
Posts: 3127
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 191 times
Been thanked: 105 times
Contact:

Re: Export data to OpenOffice Calc and MS Office Excel

Post by mol » Fri Oct 26, 2018 5:20 pm

Do anybody knows how to change font size and font name while exporting data to openoffice sheet?

User avatar
andyglezl
Posts: 1090
Joined: Fri Oct 26, 2012 7:58 pm
Location: Guadalajara Jalisco, MX
Has thanked: 27 times
Been thanked: 61 times
Contact:

Post by andyglezl » Fri Oct 26, 2018 7:30 pm

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: 3127
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 191 times
Been thanked: 105 times
Contact:

Post by mol » Fri Oct 26, 2018 8:45 pm

This does not work in OpenOffice.
It's more complicated.

User avatar
apais
Posts: 288
Joined: Fri Aug 01, 2008 6:03 pm
Location: uruguay
Has thanked: 40 times
Been thanked: 23 times
Contact:

Post by apais » Fri Oct 26, 2018 9:25 pm

Why don't you use libxlswriter ? It generates agnostic spreadsheets.

ROBROS
Posts: 159
Joined: Thu May 25, 2017 6:30 pm
DBs Used: DBF
Has thanked: 48 times
Been thanked: 13 times

Post by ROBROS » Fri Oct 26, 2018 9:33 pm

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: 3127
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 191 times
Been thanked: 105 times
Contact:

Post by mol » Sat Oct 27, 2018 6:17 am

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: 3127
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 191 times
Been thanked: 105 times
Contact:

Post by mol » Sat Oct 27, 2018 10:44 am

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: 246
Joined: Wed Aug 13, 2008 2:35 pm
Location: Córdoba - Argentina
Been thanked: 2 times

Post by Mario Mansilla » Wed Nov 28, 2018 1:05 pm

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: 1090
Joined: Fri Oct 26, 2012 7:58 pm
Location: Guadalajara Jalisco, MX
Has thanked: 27 times
Been thanked: 61 times
Contact:

Post by andyglezl » Wed Nov 28, 2018 11:19 pm

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: 89
Joined: Wed Aug 21, 2013 3:54 pm
DBs Used: DBF, mySql, mariaDB
Has thanked: 27 times
Been thanked: 24 times

Post by koke » Thu Nov 29, 2018 1:20 pm

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