How to print only one sheet

The Application Programming Interface and the OASIS Open Document Format

How to print only one sheet

Postby couper » Sun Sep 28, 2008 10:42 am

I want to print only one sheet, such as sheet 2, however in sheet1, there may be serveral pages.
If you don't how many pages in sheet 1, how to do this? Could you offer me some sample code?
Appreciate your answer.
OOo 3.0.X on Ms Windows XP
couper
 
Posts: 5
Joined: Fri Jul 18, 2008 11:40 am

Re: How to print only one sheet

Postby squenson » Sat Oct 04, 2008 9:43 am

I attach a file with a macro that only prints one sheet with the current page numbers. The trick is to hide all the other sheets before printing and to restore their status afterward. You should run the macro PrintSheet that displays an input box, and it calls the macro PrintSingleSheet that does all the work. I copy the code below, so it will also be indexed and can be retrieved using the search function of this forum.
Code: Select all   Expand viewCollapse view
Sub PrintSheet

   Dim sSheetName as String
   
   
   sSheetName = InputBox("Which sheet do you want to print?")
   PrintSingleSheet(sSheetName)
   
End Sub


Sub PrintSingleSheet(sSheetName as String)
' This macro prints a single sheet based on its name
' The trick is to hide all sheets but the one to print
' and restore their status immediately after

   Dim oSheets as Object
   Dim bSheetIsVisible(255) as Boolean
   Dim bSheetExists
   Dim i as Long
   Dim oDoc as Object
   Dim args2(2) as New com.sun.star.beans.PropertyValue
   Dim dispatcher as Object
   
   
                     ' Search the sheet within the workbook
                     ' If not found, display a message
   oSheets = ThisComponent.Sheets
   bSheetExists = FALSE
   For i = 0 to oSheets.getCount() - 1
      If sSheetName = oSheets.getByIndex(i).Name Then
         bSheetExists = TRUE
         Exit For
      End If
   Next i
   
   If Not(bSheetExists) Then
      MsgBox "The sheet """ & sSheetName & """ does not exist in this document. Nothing will be printed."
      Exit Sub
   End If
   
   
                     ' Hide all the sheets but the one we need to print
   For i = 0 to oSheets.getCount() - 1
      bSheetIsVisible(i) = oSheets.getByIndex(i).IsVisible
      If sSheetName <> oSheets.getByIndex(i).Name Then
         oSheets.getByIndex(i).IsVisible = FALSE
      Else
         oSheets.getByIndex(i).IsVisible = TRUE
      End If
   Next i

                     ' Print the document (only one visible sheet)   
   oDoc = ThisComponent.CurrentController.Frame
   args2(0).Name = "Copies"
   args2(0).Value = 1
   args2(1).Name = "Selection"
   args2(1).Value = true
   args2(2).Name = "Collate"
   args2(2).Value = false
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   dispatcher.executeDispatch(oDoc, ".uno:Print", "", 0, args2())

                     ' Restore the visibility status of all sheets
   For i = 0 to oSheets.getCount() - 1
      oSheets.getByIndex(i).IsVisible =  bSheetIsVisible(i)
   Next i

End Sub
Attachments
PrintSingleSheetByMacro.ods
(9.93 KiB) Downloaded 401 times
LibreOffice 4.1.3.2. on Ubuntu 13.10
User avatar
squenson
Volunteer
 
Posts: 1878
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: How to print only one sheet

Postby Villeroy » Sat Oct 04, 2008 10:52 am

Great demo! Let's nail down things a little bit.
First of all, the "uno:.print" dispatch does nothing on my machine although printing to the one and only default printer works fine. This may be due to my currently used Ubuntu BrokenOffice.org or something else. I can simply click the print icon after running your code.

Sheet names are case insensitive. Your macro fails to recognize "sheet1" although oSHeets.hasByName("sheet1"). I reduced your code by several lines, introducing this method on one side and Basic StrComp(s1,s2,0) on the other side.

Finally I added Sub Sabotage in order to demonstrate a situation where this technique must fail.

Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****
Sub PrintSheet

   Dim sSheetName as String

   sSheetName = InputBox("Which sheet do you want to print?")
   if ThisComponent.Sheets.hasByName(sSheetName) then

   Sabotage
      PrintSingleSheet(sSheetName)
   
   else
      MsgBox "The sheet """ & sSheetName & """ does not exist in this document. Nothing will be printed."
   endif
   
End Sub

Sub Sabotage
Const sN = "Sabotage"
   With ThisComponent.getSheets()
      If Not .hasByName(sN) then .insertNewByName(sN,0)
      With .getByName(sN)
         With .getCellByPosition(0,0)
            .setString(sN)
            addr = .getRangeAddress()
         End With
         .setPrintAreas(Array(addr))
         .IsVisible = False
      End With
   End With
End Sub

Sub PrintSingleSheet(sSheetName as String)
' This macro prints a single sheet based on its name
' The trick is to hide all sheets but the one to print
' and restore their status immediately after

Dim oSheets as Object
Dim bSheetIsVisible(255) as Boolean
Dim i as Long, s as String
Dim oDoc as Object
Dim args2(2) as New com.sun.star.beans.PropertyValue
Dim dispatcher as Object


               ' Search the sheet within the workbook
               ' If not found, display a message
oSheets = ThisComponent.Sheets


' Hide all the sheets but the one we need to print
For i = 0 to oSheets.getCount() - 1
   With oSheets.getByIndex(i)
      s = .getName()
      bSheetIsVisible(i) = .IsVisible
      .IsVisible = (StrComp(sSheetName,s,0) = 0)
   End With
Next i

' Print the document (only one visible sheet)   
oDoc = ThisComponent.CurrentController.Frame
args2(0).Name = "Copies"
args2(0).Value = 1
args2(1).Name = "Selection"
args2(1).Value = true
args2(2).Name = "Collate"
args2(2).Value = false
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(oDoc, ".uno:Print", "", 0, args2())

' Restore the visibility status of all sheets
For i = 0 to oSheets.getCount() - 1
   oSheets.getByIndex(i).IsVisible =  bSheetIsVisible(i)
Next i

End Sub

 Edit: (StrComp(sSheetName,s,0)) = 0
(StrComp(sSheetName,s,0) = 0) 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17255
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to print only one sheet

Postby squenson » Sat Oct 04, 2008 11:20 am

Thank you Villeroy for your comments and constructive enhancements!

First, there *must* be an easier way to do it with the uno:Print object. When you use the dialog box to print, you can select a sheet, then print it, so there *must* be a parameter to pass as an argument but my searches have been unsuccessful so far.

I like the "sabotage" procedure that creates a print range which apparently takes precedence when you print. In fact, once the "Sabotage" sheet is created, you cannot print anything else but this sheet using the Print menu option! Bug or feature, I don't know, but it looks odd and I guess many people will search for hours why they cannot print their current sheet or whole document...

So a solution would be to delete all the print ranges in the document and recreate them after the print is completed.
LibreOffice 4.1.3.2. on Ubuntu 13.10
User avatar
squenson
Volunteer
 
Posts: 1878
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: How to print only one sheet

Postby Villeroy » Sat Oct 04, 2008 11:31 am

This is exactly the behaviour of the user interface and raised lots of confusion in the Calc forums. If you set up print ranges for a single sheet or any collection of sheets, then there is only one option to override these print ranges: Select what you want to print and print with option "Selected cells".

IMHO, it would take some time to solve this problem by means of macros, so we could deliver a high-quality add-on which simply does what the user expects when he calls a command "Print sheet":

[Dialog Print Sheets]
drop down Sheet: <default: active sheet>
[o] Used range
[ ] Set up ranges (disabled if none)

Note: Call the built-in dialog to print selected cells only

[OK] [Cancel]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17255
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to print only one sheet

Postby Villeroy » Sat Oct 04, 2008 11:51 pm

Meanwhile I'm totally confused about which options overrides which other option, but I'm fairly confident that the following routine does something useful. It seems to work in this simple manner since I dropped the dispatch-calls. XPrintable.print(aProperties()) seems to print set up print areas of the active sheet selected sheets, disregarding other sheets having print areas or not. So this code can be focussed on the active sheet's print areas. The important comments are in the code.
Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****
Option Explicit

Sub PrintActiveSheet()
'calls: getUsedRange
REM Print active sheet's selection without prompting for details.
REM If the selection is a single cell or not a range at all,
REM print set up print ranges or the sheet's used range.
REM The API can not distinguish between a single cell being highlighted or not,
REM so this routine can not print a single highlighted cell.
Dim oView, oSheet, oUsedRange, oSelection
Dim aOldAreas(), aPrintAreas(), oBasket

oView = ThisComponent.getCurrentController()
oSheet = oView.getActiveSheet()
aOldAreas() = oSheet.getPrintAreas()
oSelection = oView.getSelection()

REM Reduce a multiple sheet selection to active sheet:
If oSelection.supportsService("com.sun.star.sheet.SheetCellRanges") then
   oSelection = oSelection.queryIntersection(oSheet.getRangeAddress())
   if oSelection.getCount() = 1 then oSelection =oSelection.getByIndex(0)
Endif

REM get an array of addresses:
If oSelection.supportsService("com.sun.star.sheet.SheetCell") then
   aPrintAreas() = aOldAreas()
elseif oSelection.supportsService("com.sun.star.sheet.SheetCellRanges") then
   aPrintAreas() = oSelection.getRangeAddresses()
elseif oSelection.supportsService("com.sun.star.sheet.SheetCellRange") then
   aPrintAreas() = Array(oSelection.getRangeAddress())
else ' shape, note, chart, ...
   aPrintAreas() = aOldAreas()
endif

REM got no addresses?
if uBound(aPrintAreas()) < 0 then
   REM load helpers if necessary: GlobalScope.BasicLibraries.loadLibrary("Calc")
   oUsedRange = getUsedRange(oSheet)
   aPrintAreas = Array(oUsedRange.getRangeAddress())
endif

oSheet.setPrintAreas(aPrintAreas())

Dim aPrintOptions(2) as new com.sun.star.beans.PropertyValue
aPrintOptions(0).Name = "CopyCount"
aPrintOptions(0).Value = 1

REM save paper while testing:
' aPrintOptions(1).Name = "FileName"
' aPrintOptions(1).Value = "file:///tmp/printActiveSheet.ps"

REM http://api.openoffice.org/docs/common/ref/com/sun/star/view/PrintOptions.html
REM @attention Using of this property with TRUE as value is highly reommended.
REM Otherwhise following actions (as e.g. closing the corresponding model) can fail.
aPrintOptions(2).Name = "Wait"
aPrintOptions(2).Value = True


ThisComponent.print(aPrintOptions())
oSheet.setPrintAreas(aOldAreas())
End Sub

'_______ Helper functions ____________
Function getUsedRange(oSheet)
Dim oRg
   oRg = oSheet.createCursor()
   oRg.gotoStartOfUsedArea(False)
   oRg.gotoEndOfUsedArea(True)
   getUsedRange = oRg
End Function

The macro prints contents of the active sheet and only the active sheet.

1. The selected range(s) if more than one cell is selected (disregarding other selected sheets)

2. If a single cell is selected
2.1 The active sheet's set up print range(s) if any
2.2 The active sheet's used range
Bugs anyone? Does it work with any printer setup?
 Edit: Played with this code another time and noticed that I did not take multiple sheets into account. Added a small snippet to disregard other sheets than the single active one. 
Last edited by Villeroy on Wed Mar 11, 2009 10:45 pm, edited 2 times in total.
Reason: Disregard multiple sheet selection-
User avatar
Villeroy
Volunteer
 
Posts: 17255
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to print only one sheet

Postby abhiabhi_84 » Wed Mar 18, 2009 10:58 am

Hi,

I have multiple printable sheets in file. All of them have print range defined.
But I could not print only ACTIVE sheet. Is there anyway to print only ACTIVE sheet or only UNHIDDEN sheets.

I tried hiding all the sheets except one, and printed the unhidden sheet using macro. But even unhidden sheets were printed, may be because the sheets had print range defined.

Any solution or suggestion?

Abhi
OOo 2.3.X on Ms Windows XP
abhiabhi_84
 
Posts: 46
Joined: Wed Nov 19, 2008 7:54 pm

Re: How to print only one sheet

Postby Villeroy » Wed Mar 18, 2009 6:14 pm

The above macro does exactly what you want. It prints the active sheet to the default printer.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17255
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to print only one sheet

Postby abhiabhi_84 » Thu Mar 19, 2009 2:46 pm

No BOSS !
This code prints all the sheets.
By all sheets i mean, hidden sheets and also sheets for which print range is not defined.

Abhi
OOo 2.3.X on Ms Windows XP
abhiabhi_84
 
Posts: 46
Joined: Wed Nov 19, 2008 7:54 pm

Re: How to print only one sheet

Postby Villeroy » Thu Mar 19, 2009 3:16 pm

You are right. I always set a PDF-printer as default printer when testing the macro in order to run many tests without wasting material and nerves. Now I proved that my physical printer receives all the sheets. I give up on this.

But since you have set print ranges for all your sheets and you want to print the print ranges of the active sheet, you can call the printer dialog (Ctrl+P) and set option "Selected Sheets". This works for me.
The macro tried to handle the case where the selected sheet has no print ranges defined. It defines a temporary print range based on your selection or the used range.

If I ever had this problem I would print the whole thing to to a file (PostScript) or export to PDF and then use my preferred PDF/PostScript-viewer to print out the pages I need.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17255
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to print only one sheet

Postby abhiabhi_84 » Thu Mar 19, 2009 3:24 pm

Is it then possible to call printer dialog through macro and also write a code to select "Print selected sheet only"?
OOo 2.3.X on Ms Windows XP
abhiabhi_84
 
Posts: 46
Joined: Wed Nov 19, 2008 7:54 pm


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 2 guests