Export data to OpenOffice Calc and MS Office Excel

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

User avatar
mol
Posts: 3062
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 176 times
Been thanked: 96 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: 1028
Joined: Fri Oct 26, 2012 7:58 pm
Location: Guadalajara Jalisco, MX
Has thanked: 21 times
Been thanked: 42 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: 3062
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 176 times
Been thanked: 96 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: 286
Joined: Fri Aug 01, 2008 6:03 pm
Location: uruguay
Has thanked: 36 times
Been thanked: 22 times
Contact:

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

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

ROBROS
Posts: 144
Joined: Thu May 25, 2017 6:30 pm
DBs Used: DBF
Has thanked: 45 times
Been thanked: 12 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: 3062
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 176 times
Been thanked: 96 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: 3062
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 176 times
Been thanked: 96 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

Post Reply