How to print only one sheet
How to print only one sheet
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.
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
Re: How to print only one sheet
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
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 921 times
LibreOffice 4.2.3.3. on Ubuntu 14.04
Re: How to print only one sheet
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.
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
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) |
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to print only one sheet
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.
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.2.3.3. on Ubuntu 14.04
Re: How to print only one sheet
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]
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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to print only one sheet
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.
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?
Code: Select all
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
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-
Reason: Disregard multiple sheet selection-
-
- Posts: 46
- Joined: Wed Nov 19, 2008 7:54 pm
Re: How to print only one sheet
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
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
Re: How to print only one sheet
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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 46
- Joined: Wed Nov 19, 2008 7:54 pm
Re: How to print only one sheet
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
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
Re: How to print only one sheet
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.
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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 46
- Joined: Wed Nov 19, 2008 7:54 pm
Re: How to print only one sheet
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