Difference between revisions of "NL/Documentation/BASIC Guide/Formatting Spreadsheet Documents"

Revision as of 14:46, 2 March 2013

Programmeursgids voor BASIC

{{{parttitle}}}

[[{{{PrevPart}}}|< Vorige deel ]]

[[{{{NextPart}}}|Volgende deel >]]

• De taal van Apache OpenOffice Basic

• Overzicht van een programma in BASIC
• Werken met variabelen
• Tekenreeksen
• Getallen
• Booleaanse waarden
• Datum en tijd
• Arrays
• Bereik en levensduur van variabelen
• Constanten
• Operatoren
• Aftakken
• Loops
• Procedures en functies
• Foutafhandeling
• Andere instructies

• Runtime-bibliotheek

• Functies voor conversie
• Strings (Tekenreeksen)
• Datum en tijd
• Bestanden en mappen
• Bericht- en invoervakken
• Andere functies

• Introductie voor de API

• Universal Network Objects (UNO)
• Eigenschappen en methoden
• Modules, services en interfaces
• Gereedschap voor werken met UNO
• Overzicht van centrale interfaces

• Werken met documenten

• De StarDesktop
• Opmaakprofielen en sjablonen

• Tekstdocumenten

• De structuur van tekstdocumenten
• Tekstdocumenten bewerken
• Meer dan alleen tekst

• Werkbladdocumenten

• De structuur van werkbladdocumenten
  • Rijen en kolommen
  • Cellen en bereiken
  • Werkbladdocumenten opmaken
• Werkbladdoucmenten bewerken

• Tekeningen en presentaties

• De structuur van tekeningen
• Tekenobjecten bewerken
• Presentaties

• Grafieken (Diagrammen)

• Diagrammen gebruiken in werkbladen
• De structuur van diagrammen
• Typen diagram

• Databases

• SQL: een Query-taal
• Typen toegang tot database
• Gegevensbronnen
• Toegang tot database

• Dialoogvensters

• Werken met dialoogvensters
• Eigenschappen
• Gebeurtenissen
• Elementen voor beheer dialoogvenster

• Formulieren

• Werken met formulieren
• Besturingselementen Formulieren
• Database-formulieren

< Vorige pagina

Volgende pagina >

Een werkbladdocument verschaft eigenschappen en methoden voor het opmaken van cellen en pagina's.

Celeigenschappen

Er zijn vele opties voor het opmaken van cellen, zoals het specificeren van het lettertype en afmeting voor tekst. Elke cel ondersteunt de services com.sun.star.style.CharacterProperties en com.sun.star.style.ParagraphProperties, waarvan de belangrijkste eigenschappen werden beschreven in Tekstdocumenten. Speciale celopmaak wordt behandeld door de service com.sun.star.table.CellProperties. De belangrijkste eigenschappen voor deze service worden beschreven in de volgende gedeelten.

U kunt alle genoemde eigenschappen toepassen op individuele cellen en ook op celbereiken.

VBA : Het object CellProperties in de API van Apache OpenOffice is vergelijkbaar met het object Interior uit VBA dat ook specifieke eigenschappen voor cellen definieert.

Achtergrondkleur en schaduw

De service com.sun.star.table.CellProperties verschaft de volgende eigenschappen voor het definiëren van achtergrondkleuren en schaduw:

CellBackColor (Long)

achtergrondkleur van de tabelcel

IsCellBackgroundTransparent (Boolean)

stelt de achtergrondkleur op transparant in

ShadowFormat (struct)

specificeert de schaduw voor cellen (structuur die overeenkomt met com.sun.star.table.ShadowFormat)

De structuur com.sun.star.table.ShadowFormat en de gedetailleerde specificaties voor de schaduw voor cellen hebben de volgende structuur:

Location (enum)

positie van de schaduw (waarde van de structuur com.sun.star.table.ShadowLocation).

ShadowWidth (Short)

grootte van de schaduw in 100-en van een millimeter

IsTransparent (Boolean)

stelt de schaduw in op transparant

Color (Long)

kleur van de schaduw

Het volgende voorbeeld schrijft het getal 1000 in de cel B2, verandert de achtergrondkleur naar rood met behulp van de eigenschap CellBackColor en maakt dan voor de cel een lichtgrijze schaduw welke 1 mm naar links en naar beneden verplaatst wordt.

Dim Doc As Object
Dim Blad As Object
Dim Cel As Object
Dim SchaduwOpmaak As New com.sun.star.table.ShadowFormat
 
Doc = ThisComponent
Blad = Doc.Sheets(0)
Cel = Blad.getCellByPosition(1,1)
 
Cel.Value = 1000
 
Cel.CellBackColor = RGB(255, 0, 0)
 
SchaduwOpmaak.Location = com.sun.star.table.ShadowLocation.BOTTOM_RIGHT
SchaduwOpmaak.ShadowWidth = 100
SchaduwOpmaak.Color = RGB(160, 160, 160)
 
Cel.ShadowFormat = SchaduwOpmaak

Uitlijnen

Apache OpenOffice verschaft verschillende functies die u toestaan de uitlijning van een tekst in een tabel te veranderen.

De volgende eigenschappen definiëren de horizontale en verticale uitlijning van een tekst:

HoriJustify (enum)

horizontale uitlijning van de tekst (waarde uit com.sun.star.table.CellHoriJustify)

VertJustify (enum)

verticale uitlijning van de tekst (waarde uit com.sun.star.table.CellVertJustify)

Orientation (enum)

richting van de tekst (waarde die overeenkomt met com.sun.star.table.CellOrientation)

IsTextWrapped (Boolean)

staat automatische regeleinden binnen de cel toe

RotateAngle (Long)

draaihoek van de tekst in 100-en van een graad

The following example shows how you can "stack" the contents of a cell so that the individual characters are printed one under another in the top left corner of the cell. The characters are not rotated.

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object
 
Doc = ThisComponent
Sheet = Doc.Sheets(0)
Cell = Sheet.getCellByPosition(1,1)
 
Cell.Value = 1000
 
Cell.HoriJustify = com.sun.star.table.CellHoriJustify.LEFT
Cell.VertJustify = com.sun.star.table.CellVertJustify.TOP
Cell.Orientation = com.sun.star.table.CellOrientation.STACKED

Number, Date and Text Format

Apache OpenOffice provides a whole range of predefined date and time formats. Each of these formats has an internal number that is used to assign the format to cells using the NumberFormat property. Apache OpenOffice provides the queryKey and addNew methods so that you can access existing number formats as well as create your own number formats. The methods are accessed through the following object call:

NumberFormats = Doc.NumberFormats

A format is specified using a format string that is structured in a similar way to the format function of Apache OpenOffice Basic. However there is one major difference: whereas the command format expects English abbreviations and decimal points or characters as thousands separators, the country-specified abbreviations must be used for the structure of a command format for the NumberFormats object.

The following example formats the B2 cell so that numbers are displayed with three decimal places and use commas as a thousands separator.

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object
Dim NumberFormats As Object
Dim NumberFormatString As String
Dim NumberFormatId As Long
Dim LocalSettings As New com.sun.star.lang.Locale
 
Doc = ThisComponent
Sheet = Doc.Sheets(0)
Cell = Sheet.getCellByPosition(1,1)
 
Cell.Value = 23400.3523565
 
LocalSettings.Language = "en"
LocalSettings.Country = "us"
 
NumberFormats = Doc.NumberFormats
NumberFormatString = "#,##0.000"
 
NumberFormatId = NumberFormats.queryKey(NumberFormatString, LocalSettings, True)
If NumberFormatId = -1 Then
   NumberFormatId = NumberFormats.addNew(NumberFormatString, LocalSettings)
End If
 
MsgBox NumberFormatId
Cell.NumberFormat = NumberFormatId

The Format Cells dialog in Apache OpenOffice Calc provides an overview of the different formatting options for cells.

Page Properties

Page properties are the formatting options that position document content on a page as well as visual elements that are repeated page after page. These include

• Paper formats
• Page margins
• Headers and footers.

The procedure for defining page formats differs from other forms of formatting. Whereas cell, paragraph, and character elements can be formatted directly, page formats can also be defined and indirectly applied using page styles. For example, headers or footers are added to the page style.

The following sections describe the main formatting options for spreadsheet pages. Many of the styles that are described are also available for text documents. The page properties that are valid for both types of documents are defined in the com.sun.star.style.PageProperties service. The page properties that only apply to spreadsheet documents are defined in the com.sun.star.sheet.TablePageStyle service.

VBA : The page properties (page margins, borders, and so on) for a Microsoft Office document are defined by means of a PageSetup object at the Worksheet object (Excel) or Document object (Word) level. In Apache OpenOffice, these properties are defined using a page style which in turn is linked to the associated document.

Page Background

The com.sun.star.style.PageProperties service defines the following properties of a pages background:

BackColor (long)

color of background

BackGraphicURL (String)

URL of the background graphics that you want to use

BackGraphicFilter (String)

name of the filter for interpreting the background graphics

BackGraphicLocation (Enum)

position of the background graphics (value according to enumeration)

BackTransparent (Boolean)

makes the background transparent

Page Format

The page format is defined using the following properties of the com.sun.star.style.PageProperties service:

IsLandscape (Boolean)

landscape format

Width (long)

width of page in hundredths of a millimeter

Height (long)

height of page in hundredths of a millimeter

PrinterPaperTray (String)

name of the printer paper tray that you want to use

The following example sets the page size of the "Default" page style to the DIN A5 landscape format (height 14.8 cm, width 21 cm):

Dim Doc As Object
Dim Sheet As Object
Dim StyleFamilies As Object 
Dim PageStyles As Object
Dim DefPage As Object
 
Doc = ThisComponent
StyleFamilies = Doc.StyleFamilies
PageStyles = StyleFamilies.getByName("PageStyles")
DefPage = PageStyles.getByName("Default")
 
DefPage.IsLandscape = True
DefPage.Width = 21000
DefPage.Height = 14800

Page Margin, Border, and Shadow

The com.sun.star.style.PageProperties service provides the following properties for adjusting page margins as well as borders and shadows:

LeftMargin (long)

width of the left hand page margin in hundredths of a millimeter

RightMargin (long)

width of the right hand page margin in hundredths of a millimeter

TopMargin (long)

width of the top page margin in hundredths of a millimeter

BottomMargin (long)

width of the bottom page margin in hundredths of a millimeter

LeftBorder (struct)

specifications for left-hand line of page border (com.sun.star.table.BorderLine structure)

RightBorder (struct)

specifications for right-hand line of page border (com.sun.star.table.BorderLine structure)

TopBorder (struct)

specifications for top line of page border (com.sun.star.table.BorderLine structure)

BottomBorder (struct)

specifications for bottom line of page border (com.sun.star.table.BorderLine structure)

LeftBorderDistance (long)

distance between left-hand page border and page content in hundredths of a millimeter

RightBorderDistance (long)

distance between right-hand page border and page content in hundredths of a millimeter

TopBorderDistance (long)

distance between top page border and page content in hundredths of a millimeter

BottomBorderDistance (long)

distance between bottom page border and page content in hundredths of a millimeter

ShadowFormat (struct)

specifications for shadow of content area of page (com.sun.star.table.ShadowFormat structure)

The following example sets the left and right-hand borders of the "Default" page style to 1 centimeter.

Dim Doc As Object
Dim Sheet As Object
Dim StyleFamilies As Object 
Dim PageStyles As Object
Dim DefPage As Object
 
Doc = ThisComponent
StyleFamilies = Doc.StyleFamilies
PageStyles = StyleFamilies.getByName("PageStyles")
DefPage = PageStyles.getByName("Default")
 
DefPage.LeftMargin = 1000
DefPage.RightMargin = 1000

Headers and Footers

The headers and footers of a document form part of the page properties and are defined using the com.sun.star.style.PageProperties service. The properties for formatting headers are:

HeaderIsOn (Boolean)

header is activated

HeaderLeftMargin (long)

distance between header and left-hand page margin in hundredths of a millimeter

HeaderRightMargin (long)

distance between header and right-hand page margin in hundredths of a millimeter

HeaderBodyDistance (long)

distance between header and main body of document in hundredths of a millimeter

HeaderHeight (long)

height of header in hundredths of a millimeter

HeaderIsDynamicHeight (Boolean)

height of header is automatically adapted to content

HeaderLeftBorder (struct)

details of the left-hand border of frame around header (com.sun.star.table.BorderLine structure)

HeaderRightBorder (struct)

details of the right-hand border of frame around header (com.sun.star.table.BorderLine structure)

HeaderTopBorder (struct)

details of the top line of the border around header (com.sun.star.table.BorderLine structure)

HeaderBottomBorder (struct)

details of the bottom line of the border around header (com.sun.star.table.BorderLine structure)

HeaderLeftBorderDistance (long)

distance between left-hand border and content of header in hundredths of a millimeter

HeaderRightBorderDistance (long)

distance between right-hand border and content of header in hundredths of a millimeter

HeaderTopBorderDistance (long)

distance between top border and content of header in hundredths of a millimeter

HeaderBottomBorderDistance (long)

distance between bottom border and content of header in hundredths of a millimeter

HeaderIsShared (Boolean)

headers on even and odd pages have the same content (refer to HeaderText , HeaderTextLeft, and HeaderTextRight )

HeaderBackColor (long)

background color of header

HeaderBackGraphicURL (String)

URL of the background graphics that you want to use

HeaderBackGraphicFilter (String)

name of the filter for interpreting the background graphics for the header

HeaderBackGraphicLocation (Enum)

position of the background graphics for the header (value according to com.sun.star.style.GraphicLocation enumeration)

HeaderBackTransparent (Boolean)

shows the background of the header as transparent

HeaderShadowFormat (struct)

details of shadow of header (com.sun.star.table.ShadowFormat structure)

The properties for formatting footers are:

FooterIsOn (Boolean)

footer is activated

FooterLeftMargin (long)

distance between footer and left-hand page margin in hundredths of a millimeter

FooterRightMargin (long)

distance between footer and right-hand page margin in hundredths of a millimeter

FooterBodyDistance (long)

distance between footer and main body of document in hundredths of a millimeter

FooterHeight (long)

height of footer in hundredths of a millimeter

FooterIsDynamicHeight (Boolean)

height of footer is adapted automatically to the content

FooterLeftBorder (struct)

details of left-hand line of border around footer (com.sun.star.table.BorderLine structure)

FooterRightBorder (struct)

details of right-hand line of border around footer (com.sun.star.table.BorderLine structure)

FooterTopBorder (struct)

details of top line of border around footer (com.sun.star.table.BorderLine structure)

FooterBottomBorder (struct)

details of bottom line of border around footer (com.sun.star.table.BorderLine structure)

FooterLeftBorderDistance (long)

distance between left-hand border and content of footer in hundredths of a millimeter

FooterRightBorderDistance (long)

distance between right-hand border and content of footer in hundredths of a millimeter

FooterTopBorderDistance (long)

distance between top border and content of footer in hundredths of a millimeter

FooterBottomBorderDistance (long)

distance between bottom border and content of footer in hundredths of a millimeter

FooterIsShared (Boolean)

the footers on the even and odd pages have the same content (refer to FooterText, FooterTextLeft, and FooterTextRight )

FooterBackColor (long)

background color of footer

FooterBackGraphicURL (String)

URL of the background graphics that you want to use

FooterBackGraphicFilter (String)

name of the filter for interpreting the background graphics for the footer

FooterBackGraphicLocation (Enum)

position of background graphics for the footer (value according to com.sun.star.style.GraphicLocation enumeration)

FooterBackTransparent (Boolean)

shows the background of the footer as transparent

FooterShadowFormat (struct)

details of shadow of footer (com.sun.star.table.ShadowFormat structure)

Changing the Text of Headers and Footers

The content of headers and footers in a spreadsheet is accessed through the following properties:

LeftPageHeaderContent (Object)

content of headers for even pages (com.sun.star.sheet.HeaderFooterContent service)

RightPageHeaderContent (Object)

content of headers for odd pages (com.sun.star.sheet.HeaderFooterContent service)

LeftPageFooterContent (Object)

content of footers for even pages (com.sun.star.sheet.HeaderFooterContent service)

RightPageFooterContent (Object)

content of footers for odd pages (com.sun.star.sheet.HeaderFooterContent service)

If you do not need to distinguish between headers or footers for odd and even pages (the FooterIsShared property is False), then set the properties for headers and footers on odd pages.

All the named objects return an object that supports the com.sun.star.sheet.HeaderFooterContent service. By means of the (non-genuine) properties LeftText, CenterText, and RightText, this service provides three text elements for the headers and footers of Apache OpenOffice Calc.

The following example writes the "Just a Test." value in the left-hand text field of the header from the "Default" template.

Dim Doc As Object
Dim Sheet As Object
Dim StyleFamilies As Object 
Dim PageStyles As Object
Dim DefPage As Object
Dim HText As Object
Dim HContent As Object
Doc = ThisComponent
StyleFamilies = Doc.StyleFamilies
PageStyles = StyleFamilies.getByName("PageStyles")
DefPage = PageStyles.getByName("Default")
 
DefPage.HeaderIsOn = True
HContent = DefPage.RightPageHeaderContent
HText = HContent.LeftText
HText.String = "Just a Test."
DefPage.RightPageHeaderContent = HContent

Note the last line in the example: Once the text is changed, the TextContent object must be assigned to the header again so that the change is effective.

Another mechanism for changing the text of headers and footers is available for text documents (Apache OpenOffice Writer) because these consist of a single block of text. The following properties are defined in the com.sun.star.style.PageProperties service:

HeaderText (Object)

text object with content of the header (com.sun.star.text.XText service)

HeaderTextLeft (Object)

text object with content of headers on left-hand pages (com.sun.star.text.XText service)

HeaderTextRight (Object)

text object with content of headers on right-hand pages (com.sun.star.text.XText service)

FooterText (Object)

text object with content of the footer (com.sun.star.text.XText service)

FooterTextLeft (Object)

text object with content of footers on left-hand pages (com.sun.star.text.XText service)

FooterTextRight (Object)

text object with content of footers on right-hand pages (com.sun.star.text.XText service)

The following example creates a header in the "Default" page style for text documents and adds the text "Just a Test" to the header.

Dim Doc As Object
Dim Sheet As Object
Dim StyleFamilies As Object 
Dim PageStyles As Object
Dim DefPage As Object
Dim HText As Object
 
Doc = ThisComponent
StyleFamilies = Doc.StyleFamilies
PageStyles = StyleFamilies.getByName("PageStyles")
DefPage = PageStyles.getByName("Default")
 
DefPage.HeaderIsOn = True
HText = DefPage.HeaderText 
 
HText.String = "Just a Test."

In this instance, access is provided directly through the HeaderText property of the page style rather than the HeaderFooterContent object.

Centering (Spreadsheets Only)

The com.sun.star.sheet.TablePageStyle service is only used in Apache OpenOffice Calc page styles and allows cell ranges that you want printed to be centered on the page. This service provides the following properties:

CenterHorizontally (Boolean)

table content is centered horizontally

CenterVertically (Boolean)

table content is centered vertically

Definition of Elements to be Printed (Spreadsheets Only)

When you format sheets, you can define whether page elements are visible. For this purpose, the com.sun.star.sheet.TablePageStyle service provides the following properties:

PrintAnnotations (Boolean)

prints cell comments

PrintGrid (Boolean)

prints the cell gridlines

PrintHeaders (Boolean)

prints the row and column headings

PrintCharts (Boolean)

prints charts contained in a sheet

PrintObjects (Boolean)

prints embedded objects

PrintDrawing (Boolean)

prints draw objects

PrintDownFirst (Boolean)

if the contents of a sheet extend across several pages, they are first printed in vertically descending order, and then down the right-hand side.

PrintFormulas (Boolean)

prints the formulas instead of the calculated values

PrintZeroValues (Boolean)

prints the zero values