Calc Guide

6

Chapter
Printing, Exporting, andE-mailing

This PDF is designed to be read onscreen, two pages at atime. If you want to print a copy, your PDF viewer shouldhave an option for printing two pages on one sheet ofpaper, but you may need to start with page 2 to get it toprint facing pages correctly. (Print this cover page
separately.)CopyrightThis document is Copyright © 2005–2010 by its contributors as listed
in the section titled Authors. You may distribute it and/or modify it
under the terms of either the GNU General Public License, version 3 orlater, or the Creative Commons Attribution License, version 3.0 or
later.
All trademarks within this guide belong to their legitimate owners.

Authors
Jean Hollis Weber
Emma Kirsopp
Peter Kupfer
Shelagh Manton
Kashmira Patel
Linda Worthington

Feedback
Please direct any comments or suggestions about this document to:authors@documentation.openoffice.org

Acknowledgements
Portions of this chapter are taken from articles written by Bruce
Byfield and first published on the Linux Journal website; used withpermission

Publication date and software version
Published 20 April 2010. Based on OpenOffice.org 3.2.

You can download
an editable version of this document fromhttp://oooauthors.org/english/userguide3/published/

Contents
Copyright............................................................................................... 2
Quick printing........................................................................................ 5
Controlling printing...............................................................................5
Printer options.................................................................................... 6
Selecting sheets to print.....................................................................6
Selecting the page order, details, and scale........................................7
Using print ranges.................................................................................9Defining a print range......................................................................... 9
Adding to the print range...................................................................9
Removing a print range......................................................................9
Editing a print range......................................................................... 10
Printing rows or columns on every page...........................................11
Defining a custom print range..........................................................11
Page breaks.......................................................................................... 13
Inserting a page break......................................................................13
Row break...................................................................................... 13
Column break................................................................................13
Deleting a page break.......................................................................13
Headers and footers............................................................................. 14
Setting a header or a footer..............................................................14
Header or footer appearance............................................................15
Setting the contents of the header or footer.....................................16
Exporting to PDF.................................................................................17
Quick export to PDF.......................................................................... 17
Controlling PDF content and quality.................................................17
General page of PDF Options dialog..............................................17
Initial View page of PDF Options dialog........................................19
User Interface page of PDF Options dialog...................................19
Links page of PDF Options dialog..................................................21
Security page of PDF Options dialog.............................................22
Exporting to XHTML............................................................................ 23
Printing, Exporting, and E-mailing3

Saving as Web pages (HTML)..............................................................24
E-mailing spreadsheets........................................................................24
E-mailing a spreadsheet to several recipients..................................24
Digital signing of documents................................................................25
Removing personal data....................................................................... 26

4

Printing, Exporting, and E-mailing

Quick printing
Click the Print File Directly icon
to send the entire document to
the default printer defined for your computer.

Note

You can change the action of the Print File Directly icon tosend the document to the printer defined for the document
instead of the default printer for the computer. Choose Tools >Options > Load/Save > General and the Load printersettings with the document option.

Controlling printing
For more control over printing, use File > Print.

Figure 1. The Print dialog
On the Print dialog, you can choose:• Which printer to use (if more than one are installed on yoursystem) and the properties of the printer—for example, pageorientation (portrait or landscape), which paper tray to use, andControlling printing

5

what paper size to print on. The properties available depend on
the selected printer; consult the printer’s documentation for
details.
• What sheets and pages to print, how many copies to print, and inwhat order to print them. Use dashes to specify page ranges andcommas or semicolons to separate ranges; for example: 1, 5, 11–
14, 34–40. Selection refers to the highlighted part of a page or
pages.
• Whether to print to a printer or to a file.

Printer options
You can set printer options for the current document only or for all
spreadsheets.
• To select options for the current document, click the Options

button on the bottom left of the Print dialog (Figure 1).
• To set print options permanently, go to Tools > Options >OpenOffice.org Calc > Print. The dialogs for both are very
similar.

Figure 2. Printer Options dialog

Selecting sheets to print
You can select one or more sheets for printing. This can be useful if
you have a large spreadsheet with multiple sheets and only want toprint certain sheets. For example, an accountant might record costs
over time with one sheet for each month. To print the November andDecember sheets, follow this procedure.
1) Go to the November sheet. Hold down the Control key and clickon the tab of the December sheet.
2) If you wish to print all of the sheets, choose File > Print andclick the Options button.

6

Printing, Exporting, and E-mailing

3) Deselect Print only selected sheets. This affects the print
preview, export, and printing of your spreadsheet. Click OK.
Note

The Options button is different from the Properties button.
Properties deals with the settings of the printer, whereas
Options deals with OOo’s settings.

Selecting the page order, details, and scale
To select the page order, details, and scale to be printed:
1) Choose Format > Page from the main menu. The Page Style
dialog opens; any changes made here are saved in the page stylefor this spreadsheet.
2) Choose the Sheet tab (Figure 3).
3) After making your selections (see next page), click OK.

Figure 3. The Sheet tab of the Page Style dialog
Page Order
You can set the order in which pages print. This is especially useful in alarge document; for example, controlling the print order can save time
if you have to collate the document a certain way.
Controlling printing

7

Where a sheet prints to more than one page of paper, it can be printed
either by column, where the first column of pages prints, and then thesecond column and so on, or by row as shown in the graphic on the top
right of the dialog in Figure 3.
Print
You can specify which details to print. These details include:• Row and column headers• Sheet grid—prints the borders of the cells as a grid
• Comments—prints the comments defined in your spreadsheet on
•
•
•
•
•

a separate page, along with the corresponding cell reference
Objects and graphics
ChartsDrawing objects
Formulas—prints the formulas contained in the cells, instead of
the results
Zero Values—prints cells with a zero value

Note

Remember that since the print detail options are a part of thepage’s properties, they are also a part of the page style’s
properties. Therefore, different page styles can be set up to
quickly change the print properties of the sheets in the
spreadsheet.

Scale
Use the scale features to control the number of pages the data will
print on. This can be useful if a large amount of data needs to beprinted compactly or, if the reader has poor eyesight, text can beenlarged when it prints.
• Reduce/Enlarge printout—scales the data in the printout either

larger or smaller. For example, if a sheet would normally print out
as four pages (two high and two wide), a scaling of 50% would
print as one page (both width and height are halved).
• Fit print range(s) on number of pages—defines exactly how many
pages the printout will take up. This option will only reduce a
printout, it will not enlarge it. To enlarge a printout, thereduce/enlarge option must be used.
• Fit print range(s) to width/height—defines how high and wide theprintout will be, in pages.

8

Printing, Exporting, and E-mailing

Using print ranges
Print ranges have several uses, including printing only a specific partof the data or printing selected rows or columns on every page.

Defining a print range
To define a new print range or modify an existing print range:
1) Highlight the range of cells that comprise the print range.
2) Choose Format > Print Ranges > Define.
The page break lines display on the screen.
Tip

You can check the print range by using File > Page Preview.
OOo will only display the cells in the print range.

Adding to the print range
After defining a print range, you can add more cells to it. This allows
multiple, separate areas of the same sheet to be printed, while notprinting the whole sheet. After you have defined a print range:
1) Highlight the range of cells to be added to the print range.
2) Choose Format > Print Ranges > Add. This adds the extra cells
to the print range.
The page break lines no longer display on the screen.
Note

The additional print range will print as a separate page, even if
both ranges are on the same sheet.

Removing a print range
It may become necessary to remove a defined print range, for example
if the whole sheet needs to be printed later.
Choose Format > Print Ranges > Remove. This removes all defined
print ranges on the sheet. After the print range is removed, the default
page break lines will appear on the screen.

Using print ranges

9Editing a print range
At any time, you can directly edit the print range, for example toremove or resize part of the print range. Choose Format > Print
Ranges > Edit. If you have already selected a print range, the Edit
Print Ranges dialog looks something like Figure 4.

Figure 4: Edit a print range
In this example, three rectangles are selected, each separated by asemicolon. The first is bounded by cell A3 ($A$3) in the top left andcell C9 ($C$9) in the bottom right corners. Clicking anywhere in the
text entry box shows the currently selected print range on the screen,with each rectangle in a different color, as in Figure 5.
After making any changes, click the Shrink icon next to the text
entry box to redisplay the rectangles with their new values.
To re-expand the Edit Print Ranges dialog, click the Shrink icon again.

Figure 5: Print range marked by colored boxes

10

Printing, Exporting, and E-mailingPrinting rows or columns on every pageIf a sheet is printed on multiple pages, you can set up certain rows orcolumns to repeat on each printed page.
For example, if the top two rows of the sheet as well as column A need
to be printed on all pages, do the following:
1) Choose Format > Print Ranges > Edit. On the Edit Print
Ranges dialog, type the rows in the text entry box under Rows to
repeat. For example, to repeat rows 1 and 2, type $1:$2. This
automatically changes Rows to repeat from - none - to- user defined -.

Figure 6: Specifying repeating rows2) To repeat columns, type the columns in the text entry box under
Columns to repeat. For example, to repeat column A, type $A.
This automatically changes Columns to repeat from - none - to- user defined -.
3) Click OK.
Note

You do not need to select the entire range of the rows to berepeated; simply select one cell in each row.

Defining a custom print range
In addition to highlighting a print range for each print job, you candefine a range of cells to be used repeatedly. This may be useful if
there are different areas of a large spreadsheet that need to be printed
for different reports. Several different print ranges can be defined tomeet this need.
1) To define a print range, use the same procedure as labeling an
area of the sheet. Highlight the cells you want to define as a print
range and select Insert > Names > Define. (The cells can behighlighted after opening the Define Names dialog as well.)Using print ranges

112) On the Define Names dialog (Figure 7), type a name for the range
in the text box with the blinking cursor. The name of the range
cannot contain any spaces.
3) Click the More button in the dialog and then select the Print
range option. Click the Add button.
4) To include more than one group of cells in the selection, type in
the additional ranges. For example, to select the rectangle with
A3 as the top left cell and F20 as the bottom right cell, enter;$A$3:$F$20 or ;A3:F20 (both work and are equivalent) after
the initial selection. Make sure that each group of cells is
separated with a semicolon.
5) Click OK.

Figure 7: Define Names dialog
To print this range:
1) Choose Format > Print Ranges > Edit (Figure 4). The
previously defined area now appears in the drop-down box underPrint range.
2) Select the defined print range and click OK.
This method can be useful to quickly change the print range withouthighlighting a large area of cells every time.

12

Printing, Exporting, and E-mailingNote

If the cell range name refers to more than one group of cells, itwill not appear in the drop-down list. You will need to type it inor highlight and select it.

Page breaks
While defining a print range can be a powerful tool, it may sometimes
be necessary to manually adjust Calc’s printout. To do this, you can use
a manual break. A manual break helps to ensure that your data prints
properly. You can insert a horizontal page break above, or a verticalpage break to the left of, the active cell.

Inserting a page break
To insert a page break:1) Navigate to the cell where the page break will begin.
2) Select Insert > Manual Break.
3) Select Row Break or Column Break depending on your need.
The break is now set.

Row breakSelecting Row Break creates a page break above the selected cell. For
example, if the active cell is H15, then the break is created between
rows 14 and 15.

Column break
Selecting Column Break creates a page break to the left of the selected
cell. For example, if the active cell is H15, then the break is createdbetween columns G and H.
Tip

To see page break lines more easily on screen, you can change
their color. Choose Tools > Options > OpenOffice.org >Appearance and scroll down to the Spreadsheet section.

Deleting a page breakTo remove a page break:1) Navigate to a cell that is next to the break you want to remove.
2) Select Edit > Delete Manual Break.
3) Select Row Break or Column Break depending on your need.
Page breaks

13

The break is now removed.

Note

Multiple manual row and column breaks can exist on the same
page. When you want to remove them, you have to remove
each one individually. This may be confusing at times, because
although there may be a column break set on the page, when
you go to Edit > Manual Break, the Column break choice
may not be available (grayed out).
In order to remove the break, you have to be in the cell next tothe break. For example, if you set the column break while you
are in H15, you can not remove it if you are in cell D15.
However, you can remove it from any cell in column H.

Headers and footers
Headers and footers are predefined pieces of text that are printed at
the top or bottom of a sheet outside of the sheet area. Headers are set
the same way as footers.
Headers and footers are assigned to a page style. You can define morethan one page style for a spreadsheet and assign different page styles
to different sheets. For more about page styles, see Chapter 4.

Setting a header or a footer
To set a header or footer:1) Navigate to the sheet that you want to set the header or footer
for. Select Format > Page.
2) Select the Header (or Footer) tab. See Figure 8.
3) Select the Header on option.
From here you can also set the margins, the spacing, and height for
the header or footer. You can check the AutoFit height box toautomatically adjust the height of the header or footer.
MarginChanging the size of the left or right margin adjusts how far theheader or footer is from that side of the page.
Spacing
Spacing affects how far above or below the sheet the header orfooter will print. So, if spacing is set to 1.00", then there will be 1
inch between the header or footer and the sheet.

14

Printing, Exporting, and E-mailingHeight
Height affects how big the header or footer will be.

Figure 8: Header dialog

Header or footer appearance
To change the appearance of the header or footer, click the Morebutton in the dialog. This opens the Border/Background dialog.

Figure 9: Header/Footer Border/Background dialog
Headers and footers

15

From this dialog you can set the background and border style of theheader or footer. See Chapter 4 (Using Styles and Templates) for more
information.

Setting the contents of the header or footer
The header or footer of a Calc spreadsheet has three columns for text.
Each column can have different contents.
To set the contents of the header or footer, click the Edit button in theheader or footer dialog shown in Figure 8 to display the dialog shownin Figure 10.

Figure 10: Edit contents of header or footer
Areas
Each area in the header or footer is independent and can havedifferent information in it.
HeaderYou can select from several preset choices in the Header drop-down
list, or specify a custom header using the buttons below the area
boxes. (To format a footer, the choices are the same.)
Custom header
Click in the area (Left, Center, Right) that you want to customize,then use the buttons to add elements or change text attributes.
Opens the Text Attributes dialog.
Inserts the total number of pages.
16

Printing, Exporting, and E-mailing

Inserts the File Name field.
Inserts the Date field.
Inserts the Sheet Name field.
Inserts the Time field.
Inserts the current page number.

Exporting to PDF
Calc can export documents to PDF (Portable Document Format). This
industry-standard file format is ideal for sending the file to someone
else to view using Adobe Reader or other PDF viewers.

Quick export to PDF
Click the Export Directly as PDF icon
to export the entire
document using your default PDF settings. You are asked to enter the
file name and location for the PDF file, but you do not get to choose apage range, the image compression, or other options.

Controlling PDF content and quality
For more control over the content and quality of the resulting PDF, use
File > Export as PDF. The PDF Options dialog opens. This dialog has
five pages (General, Initial View, User Interface, Links, and Security).
Make your selections and click Export. Enter the location and filename of the PDF to be created. Click Save to export the file.

General page of PDF Options dialog
On the General page, you can choose which pages to include in the
PDF, the type of compression to use for images (which affects thequality of images in the PDF), and other options.
Range section• All: Exports the entire document.
• Pages: To export a range of pages, use the format 3-6 (pages 3 to6). To export single pages, use the format 7;9;11 (pages 7, 9, and11). You can also export a combination of page ranges and single
pages, by using a format like 3-6;8;10;12.
• Selection: Exports whatever content is selected.
Exporting to PDF

17

Figure 11: General page of PDF Options dialog
Images section• Lossless compression: Images are stored without any loss ofquality. Tends to make large files when used with photographs.
Recommended for other kinds of images or graphics.
• JPEG compression: Allows for varying degrees of quality. A
setting of 90% works well with photographs (small file size, little
perceptible loss).
• Reduce image resolution: Lower-DPI (dots per inch) images
have lower quality. For viewing on a computer screen a resolutionof 72dpi (for Windows) or 96dpi (GNU/Linux) is sufficient, whilefor printing it is generally preferable to use at least 300 or 600
dpi, depending on the capability of the printer. Higher dpi
settings greatly increase the size of the exported file.

18

Printing, Exporting, and E-mailing

Note

EPS images with embedded previews are exported only as
previews. EPS images without embedded previews areexported as empty placeholders.

General section• PDF/A-1: PDF/A is an ISO standard established in 2005 for longterm preservation of documents, by embedding all the pieces
necessary for faithful reproduction (such as fonts) whileforbidding other elements (including forms, security, encryption,and tagged PDF). If you select PDF/A-1, the forbidden elements
are greyed-out (not available).
• Tagged PDF: Exports special tags into the corresponding PDF
tags. Some tags that are exported are table of contents,
hyperlinks, and controls. This option can increase the file size
significantly.
• Create PDF form - Submit format: Choose the format for
submitting forms from within the PDF file. This setting overrides
the control’s URL property that you set in the document. There is
only one common setting valid for the whole PDF document: PDF(sends the whole document), FDF (sends the control contents),
HTML, and XML. Most often you will choose the PDF format.
• Export bookmarks: Exports sheet names in Calc documents as“bookmarks” (a table of contents list displayed by some PDF
readers, including Adobe Reader).
• Export comments: Exports comments in Calc documents as PDF
notes. You may not want this!
• Export automatically inserted blank pages: Not available in
Calc.

Initial View page of PDF Options dialog
On the Initial View page (Figure 12), you can choose how the PDF
opens by default in a PDF viewer. The selections are self-explanatory.

User Interface page of PDF Options dialog
On the User Interface page (Figure 13), you can choose more settings
to control how a PDF viewer displays the file. Some of these choices
are particularly useful when you are creating a PDF to be used as apresentation or a kiosk-type display.

Exporting to PDF

19

Figure 12: Initial View page of PDF Options dialog

Figure 13: User Interface page of PDF Options dialog
Window options section• Resize window to initial page: Causes the PDF viewer window
to resize to fit the first page of the PDF.

20

Printing, Exporting, and E-mailing• Center window on screen: Causes the PDF viewer window to becentered on the computer screen.
• Open in full screen mode: Causes the PDF viewer to open fullscreen instead of in a smaller window.
• Display document title: Causes the PDF viewer to display the
document’s title in the title bar.
User interface options section• Hide menubar: Causes the PDF viewer to hide the menu bar.
• Hide toolbar: Causes the PDF viewer to hide the toolbar.
• Hide window controls: Causes the PDF viewer to hide otherwindow controls.
Transitions
Not available in Calc.
Bookmarks
Select how many heading levels are displayed as bookmarks, if
Export bookmarks is selected on the General page.

Links page of PDF Options dialog
On this page you can choose how links are exported to PDF.

Figure 14: Links page of PDF Options dialog
Export bookmarks as named destinations
If you have defined Writer bookmarks, Impress or Draw slide names,or Calc sheet names, this option exports them as “named
destinations” to which Web pages and PDF documents can link.
Convert document references to PDF targets
If you have defined links to other documents with OpenDocument
extensions (such as .ODT, .ODS, and .ODP), this option converts thefile names to .PDF in the exported PDF document.
Exporting to PDF

21

Export URLs relative to file system
If you have defined relative links in a document, this option exports
those links to the PDF.
Cross-document links
Defines the behavior of links clicked in PDF files.

Security page of PDF Options dialog
PDF export includes options to encrypt the PDF (so it cannot be
opened without a password) and apply some digital rights management(DRM) features.
• With an open password set, the PDF can only be opened with the

password. Once opened, there are no restrictions on what theuser can do with the document (for example, print, copy, orchange it).
• With a permissions password set, the PDF can be opened byanyone, but its permissions can be restricted. See Figure 15.

Figure 15: Security page of PDF Options dialog.

22

Printing, Exporting, and E-mailing• With both the open password and permission password set, thePDF can only be opened with the correct password, and its
permissions can be restricted.
Note

Permissions settings are effective only if the user’s PDF viewer
respects the settings.

Figure 16 shows the dialog displayed when you click the Set open
password button on the Security page of the PDF Options dialog.
After you set a password for permissions, the other choices on the
Security page (shown in Figure 15) become available. These selections
are self-explanatory.

Figure 16: Setting a password to encrypt a PDF

Exporting to XHTML
Calc can export spreadsheets to XHTML. Choose File > Export. On
the Export dialog, specify a file name for the exported document, thenselect the XHTML in the File format list and click the Export button.

Exporting to XHTML

23

Saving as Web pages (HTML)Calc can save files as HTML documents. Use File > Save As andselect HTML Document, or File > Wizards > Web Page.
If the file contains more than one sheet, the additional sheets will
follow one another in the HTML file. Links to each sheet will be placedat the top of the document. Calc also allows the insertion of links
directly into the spreadsheet using the Hyperlink dialog.

E-mailing spreadsheets
OOo provides several quick and easy ways to send spreadsheets as ane-mail attachment in one of three formats: OpenDocument Spreadsheet(OOo’s default format), Microsoft Excel, or PDF.
To send the current document in OpenDocument format:
1) Choose File > Send > Document as E-mail. OpenOffice.org
opens your default e-mail program with the spreadsheet (*.ODS)
document attached.
2) In your e-mail program, enter the recipient, subject, and any text
you want to add, then send the e-mail.
File > Send > E-mail as OpenDocument Spreadsheet has thesame effect.
If you choose E-mail as Microsoft Excel, OOo first creates a file in
Excel format and then opens your e-mail program with the *.XLS fileattached.
Similarly, if you choose E-mail as PDF, OOo first creates a PDF using
your default PDF settings (as when using the Export Directly as PDF
toolbar button) and then opens your email program with the *.PDF fileattached.

E-mailing a spreadsheet to several recipients
To e-mail a document to several recipients, you can use the features inyour e-mail program or you can use OOo Writer’s mail merge facilities
to extract email addresses from an address book.
For details, see Chapter 10 (Printing, Exporting, and E-mailing) in theGetting Started guide.

24

Printing, Exporting, and E-mailingDigital signing of documents
To sign a document digitally, you need a personal key, the certificate. A
personal key is stored on your computer as a combination of a private
key, which must be kept secret, and a public key, which you add to yourdocuments when you sign them. You can get a certificate from acertification authority, which may be a private company or agovernmental institution.
When you apply a digital signature to a document, a checksum iscomputed from the document’s content plus your personal key. The
checksum and your public key are stored together with the document.
When someone later opens the document on any computer with arecent version of OpenOffice.org, the program will compute thechecksum again and compare it with the stored checksum. If both are
the same, the program will signal that you see the original, unchanged
document. In addition, the program can show you the public key
information from the certificate. You can compare this key with the
public key that is published on the web site of the certificate authority.
Whenever someone changes something in the document, this change
breaks the digital signature.
On Windows operating systems, the Windows features of validating asignature are used. On Solaris and Linux systems, files that aresupplied by Thunderbird, Mozilla or Firefox are used. For a moredetailed description of how to get and manage a certificate, andsignature validation, see “Using Digital Signatures” in the OOo Help.
To sign a document:
1) Choose File > Digital Signatures.
2) If you have not saved the document since the last change, amessage box appears. Click Yes to save the file.
3) After saving, you see the Digital Signatures dialog. Click Add to
add a public key to the document.
4) In the Select Certificate dialog, select your certificate and clickOK.
5) You see again the Digital Signatures dialog, where you can add
more certificates if you want. Click OK to add the public key tothe saved file.
A signed document shows an iconin the status bar. You can doubleclick the icon to view the certificate.

Digital signing of documents25Removing personal data
You may wish to ensure that personal data, versions, notes, hidden
information, or recorded changes are removed from files before you
send them to other people or create PDFs from them.
In Tools > Options > OpenOffice.org > Security > Options, you
can set Calc to remind (warn) you when files contain certain
information and remove personal information automatically on saving.
To remove personal and some other data from a file, go to File >Properties. On the General tab, uncheck Apply user data and thenclick the Reset button. This removes any names in the created andmodified fields, deletes the modification and printing dates, and resets
the editing time to zero, the creation date to the current date and time,
and the version number to 1.
To remove version information, either go to File > Versions, select theversions from the list and click Delete, or use Save As and save thefile with a different name.

26

Printing, Exporting, and E-mailing