Page 1 of 1

How to Format OpenOffice Spreadsheet using vb.net

PostPosted: Fri Nov 29, 2019 7:37 am
by npandit
In Excel I have been using this code to format excel sheet
' With .PageSetup
' .PaperSize = excel.XlPaperSize.xlPaperA4
' .PrintArea = "A1:AE65"
' .Orientation = excel.XlPageOrientation.xlPortrait
' .Zoom = False
' .FitToPagesTall = 1
' .FitToPagesWide = 1
' .LeftMargin = APP.InchesToPoints(0.5)
' .RightMargin = APP.InchesToPoints(0.75)
' .TopMargin = APP.InchesToPoints(1.5)
' .BottomMargin = APP.InchesToPoints(1)
' .HeaderMargin = APP.InchesToPoints(0.5)
' .FooterMargin = APP.InchesToPoints(0.5)

' End With
However i Want to set the same specifications for Open office Spreadsheet using vb.net
Can anyone suggest how to format it?

Re: How to Format Open Office Spreadsheet using vb.net

PostPosted: Fri Nov 29, 2019 7:51 am
by Zizi64
No question in this forum please

It is a Tutorials part of the forum. You question is not a Tutorial.
Next time please choose a relevant area for your question in the forum.


If you want control the AOO/LO or the documents in the AOO/LO you MUST use the API functions.
API = Application Programming Interface. API has thousands of the functions and procedures but these are basicly different then the MS VBA functions and procedures.

Re: How to Format Open Office Spreadsheet using vb.net

PostPosted: Fri Nov 29, 2019 12:25 pm
by Villeroy
Open a template with as many customizations you want:
Code: Select all   Expand viewCollapse view
sub OpenTemplate(sRegion$, sTemplate$)
dim p(0 to 2) as new com.sun.star.beans.PropertyValue
p(0).Name = "AsTemplate"
p(0).Value = True
p(1).Name = "TemplateName"
p(1).Value = sTemplate
p(2).Name = "TemplateRegionName"
p(2).Value = sRegion
StarDesktop.loadComponentFromURL("private:factory/swriter", 0, "_default", p)
end sub

sRegion refers to the same categories as seen in the template manager.
StarDesktop is an internal StarBasic variable. In VB you have to create an instance of http://www.openoffice.org/api/docs/comm ... sktop.html

See http://www.kalitech.fr/clients/doc/VB_APIOOo_en.html for some VB specific details.

Re: How to Format Open Office Spreadsheet using vb.net

PostPosted: Fri Nov 29, 2019 12:30 pm
by JeJe
things like margins are set using the page style. In OOBasic: thiscomponent.stylefamilies.getbyname("PageStyles")
use .getbyname(thename) or .getbyindex(0 or whatever index)

MRI gives a list of properties of the page style:

(Name) (Value Type) (Value) (Info.) (Attr.) (Handle)
BackColor long -1 146
BackGraphicFilter string "" 146
BackGraphicLocation .style.GraphicLocation NONE 146
BackGraphicURL string "" 146
BackTransparent boolean True 146
BackgroundColor long -1 146
BorderDistance long 0 148
BottomBorder .table.BorderLine -STRUCT- 148
BottomBorderDistance long 0 148
BottomMargin long 2000 155
CenterHorizontally boolean False 161
CenterVertically boolean False 162
DisplayName string Default Read_Only 1225
FirstPageNumber short 1 175
FooterBackColor long -1 1227
FooterBackGraphicFilter string "" 1227
FooterBackGraphicLocation .style.GraphicLocation NONE 1227
FooterBackGraphicURL string "" 1227
FooterBackTransparent boolean True 1227
FooterBackgroundColor long -1 1227
FooterBodyDistance long 250 1227
FooterBorderDistance long 0 1227
FooterBottomBorder .table.BorderLine -STRUCT- 1227
FooterBottomBorderDistance long 0 1227
FooterDynamic boolean True 1227
FooterHeight long 751 1227
FooterIsDynamicHeight boolean True 1227
FooterIsOn boolean True 1227
FooterIsShared boolean True 1227
FooterLeftBorder .table.BorderLine -STRUCT- 1227
FooterLeftBorderDistance long 0 1227
FooterLeftMargin long 0 1227
FooterOn boolean True 1227
FooterRightBorder .table.BorderLine -STRUCT- 1227
FooterRightBorderDistance long 0 1227
FooterRightMargin long 0 1227
FooterShadowFormat .table.ShadowFormat -STRUCT- 1227
FooterShared boolean True 1227
FooterTopBorder .table.BorderLine -STRUCT- 1227
FooterTopBorderDistance long 0 1227
HeaderBackColor long -1 1226
HeaderBackGraphicFilter string "" 1226
HeaderBackGraphicLocation .style.GraphicLocation NONE 1226
HeaderBackGraphicURL string "" 1226
HeaderBackTransparent boolean True 1226
HeaderBackgroundColor long -1 1226
HeaderBodyDistance long 250 1226
HeaderBorderDistance long 0 1226
HeaderBottomBorder .table.BorderLine -STRUCT- 1226
HeaderBottomBorderDistance long 0 1226
HeaderDynamic boolean True 1226
HeaderHeight long 751 1226
HeaderIsDynamicHeight boolean True 1226
HeaderIsOn boolean True 1226
HeaderIsShared boolean True 1226
HeaderLeftBorder .table.BorderLine -STRUCT- 1226
HeaderLeftBorderDistance long 0 1226
HeaderLeftMargin long 0 1226
HeaderOn boolean True 1226
HeaderRightBorder .table.BorderLine -STRUCT- 1226
HeaderRightBorderDistance long 0 1226
HeaderRightMargin long 0 1226
HeaderShadowFormat .table.ShadowFormat -STRUCT- 1226
HeaderShared boolean True 1226
HeaderTopBorder .table.BorderLine -STRUCT- 1226
HeaderTopBorderDistance long 0 1226
Height long 29700 159
ImplementationId []byte -SEQUENCE- Pseud Read_Only
ImplementationName string ScStyleObj Pseud Read_Only
IsBackgroundTransparent boolean True 146
IsLandscape boolean False 156
LeftBorder .table.BorderLine -STRUCT- 148
LeftBorderDistance long 0 148
LeftMargin long 2000 154
LeftPageFooterContent .sheet.XHeaderFooterContent -INTERFACE- 181
LeftPageHeaderContent .sheet.XHeaderFooterContent -INTERFACE- 180
Name string Default Pseud
NumberingType short 4 156
PageScale short 100 173
PageStyleLayout .style.PageStyleLayout ALL 156
ParentStyle string "" Pseud
PrintAnnotations boolean False 166
PrintCharts boolean True 169
PrintDownFirst boolean True 172
PrintDrawing boolean True 171
PrintFormulas boolean False 186
PrintGrid boolean False 167
PrintHeaders boolean False 168
PrintObjects boolean True 170
PrintZeroValues boolean True 187
PrinterPaperTray string [From printer s... 158
PropertiesToDefault []string Pseud WriteOnly
PropertySetInfo .beans.XPropertySetInfo -INTERFACE- Pseud Read_Only
PropertyToDefault string Ignored
RightBorder .table.BorderLine -STRUCT- 148
RightBorderDistance long 0 148
RightMargin long 2000 154
RightPageFooterContent .sheet.XHeaderFooterContent -INTERFACE- 183
RightPageHeaderContent .sheet.XHeaderFooterContent -INTERFACE- 182
ScaleToPages short 0 174
ScaleToPagesX short 0 188
ScaleToPagesY short 0 188
ShadowFormat .table.ShadowFormat -STRUCT- 150
Size .awt.Size -STRUCT- 159
SupportedServiceNames []string -Sequence- Pseud Read_Only
TopBorder .table.BorderLine -STRUCT- 148
TopBorderDistance long 0 148
TopMargin long 2000 155
TransparentFooterBackground boolean True 1227
TransparentHeaderBackground boolean True 1226
Types []type -Sequence- Pseud Read_Only
UserDefinedAttributes .container.XNameContainer -INTERFACE- 122
Width long 21001 159
WritingMode short 0 136




Automating OO:

https://www.openoffice.org/udk/common/m ... ation.html

https://wiki.openoffice.org/wiki/Docume ... ion_Bridge