Bug Hunting Session
Bug 74101 - add NOPRINTROW and NOPRINTCOL functions
Summary: add NOPRINTROW and NOPRINTCOL functions
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2014-01-27 03:32 UTC by Tim Deaton
Modified: 2017-07-22 11:25 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
use of filter to hide/show rows (8.96 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-12-31 07:56 UTC, Winfried Donkers (retired)
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Tim Deaton 2014-01-27 03:32:09 UTC
I would like to urge you to add a NOPRINTROW and a NOPRINTCOL function to 
LibreOffice Calc.  I'll explain what I mean below, and include the details of what I do now (in hopes that it helps).  I've never seen what I do in print, but because of how frequently I use it, I'd be very surprised if many others 
didn't find such functions to be almost indispensible once they found them.
 
The first spreadsheet program I used (ancient shareware: ExpressCalc) had a 
function called NOPRINT.  I could put it in a cell by itself, or use it inside 
another function (like IF(G15=0,NOPRINT,"")).  Either way, if any cell in a 
given row evaluated to "NOPRINT", then that row would not be printed when I 
printed the spreadsheet.
 
This function was very handy whenever I wanted to build a re-usable table that 
might allow for 100 rows of data entry & related calculations, but in which 
not all rows are likely to be filled every time.  For instance, I might have 
an invoice sheet listing 30 different possible items, but I only want the 5 
that happen to have quantities filled in to actually show up on the printout.
 
Unfortunately, I've never found a similar function in any other spreadsheet 
program.  However, in Excel97, I can achieve the same result using a couple of 
macros, some predefined range names, and a "Print" button that runs the 
macros.  I define a convenient column with the range name "NOPRINT", the cell 
that I want to appear (after printing) at the upper-left corner of the screen 
as "PRESTART", and the cell that I want to be active when printing is finished 
as "START".  If there happen to be any columns I don't want printed, I define 
them in a fourth range name called "COLNOPRINT".  Finally, in the NOPRINT 
column, I put the NA() function on each row that I never want printed, and I 
use an IF() statement that evaluates to NA() unless the row should be printed.
 
Then, the macros basically do this:
1) Unprotect the sheet.
2) Go to the PRESTART and START cells to get to the view I want to return to.
3) Create a "TempView" view for later use.
4) Hide any columns included in a COLNOPRINT range.
5) Find all cells in the NOPRINT column that evaluate to NA(), and hide those 
rows.
6) Print the sheet.
7) Return to the "TempView" view (to undo all the things hidden in this 
process).
8) Delete the "TempView" (so it doesn't interfere with future use and other 
sheets)
9) Protect the sheet.
 
The 'Visual Basic For Applications' macros are as follows:
'
'==============================================================================
' FindRange Function
' Macro from MS Excel Tech support 5/1/96 by Paul Peck
' to find if a range exists
'
'
Function FindRange(r)
    Dim x As Range
    On Error Resume Next
    Set x = ActiveSheet.Range(r)
    On Error GoTo 0
    If Not (x Is Nothing) Then FindRange = True
End Function
'
'==============================================================================
' Print_Valid Macro
' Macro recorded & edited 11/24/99 by Tim Deaton
'
'
Sub Print_Valid()
    On Error GoTo ErrorMsg
    ActiveSheet.Unprotect
    'set up & create the view to return to when finished
    If FindRange("PRESTART") Then
        Application.Goto Reference:="PRESTART", Scroll:=True
    End If
    If FindRange("START") Then
        Application.Goto Reference:="START"
    End If
    ActiveWorkbook.CustomViews.Add "TempView", True, True
    'Hide any columns that should not be printed
    If FindRange("COLNOPRINT") Then
        Application.Goto Reference:="COLNOPRINT"
        Selection.EntireColumn.Hidden = True
    End If
    'select "noprint" range, then "n/a" cells, then hide "n/a" rows
    Application.Goto Reference:="NOPRINT"
    Selection.SpecialCells(xlFormulas, 16).Select
    Selection.RowHeight = 0
    'delete any defined print range so the entire visible area will print
    If FindRange("Print_Area") Then
        ActiveWorkbook.Names("Print_Area").Delete
    End If
    'view, then print the sheet
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True
    'return to the view you started with, then delete that view name
    ActiveWorkbook.CustomViews("TempView").Show
    ActiveWorkbook.CustomViews("TempView").Delete
    ActiveSheet.Protect
    Exit Sub
ErrorMsg:
    Msg = Str(Err) & ": " & Error(Err)
    MsgBox Msg, vbCritical
    Resume Next
End Sub
'
'==============================================================================
' Print_Valid_Plus Macro
' Macro recorded & edited 12/15/99 by Tim Deaton
'
'
Sub Print_Valid_Plus()
    On Error GoTo ErrorMsg
    'unprotect all sheets in workbook
    For i = 1 To Sheets.Count
        Sheets(i).Unprotect
    Next i
    'call Print_Valid() procedure to setup & print the sheet
    Print_Valid
    'protect all sheets in the workbook
    For i = 1 To Sheets.Count
        Sheets(i).Protect
    Next i
    Exit Sub
ErrorMsg:
    Msg = Str(Err) & ": " & Error(Err)
    MsgBox Msg, vbCritical
    Resume Next
End Sub
'

Usually when I need a NOPRINT function, I also need to protect the sheet to 
protect various pre-defined formulas.  But since the macros use VIEWs, they have to "Unprotect" all sheets in the workbook and then "Protect" them at the end of the process.  Therefore, if I have multiple sheets in the workbook, the 
PRINT button has to run the "PrintValidPlus" macro.  Otherwise, I have the 
button run the "PrintValid" macro.
 
In order for this to work well, I have to define the range names as local ONLY 
to this sheet.
   ("Sheetname!Rangename")
Otherwise, I could only use this technique on one sheet in a workbook.
 
It would be very helpful if I could do the same with views, but Excel doesn't 
offer that option.  Starting with Excel97, all views are global to the entire 
workbook -- a very bad idea in my opinion.
 
I hope I've explained this in enough detail and clearly enough that some 
of your spreadsheet developers will agree that it's needed, and will build it 
into the spreadsheet program.
 
In any case, thanks for taking the time to read and understand what I've 
written.
 
Sincerely,
Tim Deaton
Comment 1 Robinson Tryon (qubit) 2014-12-29 23:41:25 UTC
(In reply to Tim Deaton from comment #0)
> The first spreadsheet program I used (ancient shareware: ExpressCalc) had a 
> function called NOPRINT.  I could put it in a cell by itself, or use it
> inside 
> another function (like IF(G15=0,NOPRINT,"")).  Either way, if any cell in a 
> given row evaluated to "NOPRINT", then that row would not be printed when I 
> printed the spreadsheet.

Sounds like a neat idea.
Status -> NEW

I don't know how hard it would be to implement, or whether it would require an extension to ODF, so I'll cc Winfried for an expert opinion :-)
Comment 2 Winfried Donkers (retired) 2014-12-31 07:56:55 UTC
Created attachment 111558 [details]
use of filter to hide/show rows

(I don't know if if get the gist of the requested enhancement right, so I made an attachment to make myself clear.)

Doesn't the use of filter do just what you want?
Comment 3 Tim Deaton 2015-01-03 22:00:17 UTC
Thank you, Robinson & Winfried, for noticing and commenting on this.

Winfried, I had not previously been aware of the filter menu option.  THANK YOU!  It does indeed do much of what I want.  With the AutoFilter turned on, I could do things manually in 5 steps:
1. click the down-arrow for a column with data only in the rows I want to print, 
2. select "Not Empty" from that menu, 
3. print the spreadsheet,
4. click the down-arrow for the same column again, and
5. select "All".   

With the NOPRINT function I suggested, the printing process would search for the output of that function (just as it now searches for a "print range") and on finding that output would perform those five steps automatically. 

-Tim Deaton
Comment 4 Winfried Donkers (retired) 2015-01-05 09:01:04 UTC
(In reply to Tim Deaton from comment #3)
> With the NOPRINT function I suggested, the printing process would search for
> the output of that function (just as it now searches for a "print range")
> and on finding that output would perform those five steps automatically. 

Hi Tim,

I have two more comments on your request that need consideration:
- The NOPRINT function you suggest only supports not printing empty cell-rows, whereas the filter offers many more options. To make a NOPRINT function versatile (i.e. useful for many users, not just one or two), it would need to have rather a lot of arguments as various filter-options (ranges as well as criteria) must be possible. This makes the function less easy than your macro.
- The NOPRINT function you suggest is not a function in the way the functions are defined (see http://docs.oasis-open.org/office/v1.2/cos01/OpenDocument-v1.2-cos01-part2.html#__RefHeading__1017860_715980110 ), i.e. a function returns a result which is shown in the cell where the function resides, a function is stored with the document together with its result (making it fast to read a spreadsheet document as no calculation is needed).

All in all, I think that the functionality you want can be added in 3 ways:
-use your macro and saved in in your default spread document so that it is available to you in all the spreadsheet documents that you create. This is a solution for you/your documents only;
-make an extension that offers the functionality of your macro. Extensions can me made quite easily (if you can write macros, making extensions shoud be within your grasp, too) and can be made available to alle users who want his functionality;
-develop an extra functionality in Calc. Not a function, but a  menu option (something like 'print filtered selection ...'), possibly with remebering the filter settings, so that setting is needed only the first time and when settings are to be changed.

The first two options are directly available to you, the third would need some discussion with UI-experts before developing can be started. It may even be that UI-experts say that the gain of the extra functionality is small compared with the added complexity of the menu.
It is not that your idea is bad, not at all, but I try to balance the current possibilities with the effort needed to enhance them and the benefit to the general user.

This is just my personal opinion; I interested in your thoughts.
Comment 5 Eike Rathke 2016-04-12 15:04:32 UTC
I advise against such functions that influence document view/print behavior that may or may not be supported by applications reading the file. Additionally this would get specifically tricky when it comes to calculation of page sizes, fitting content, scaling, manual page breaks and what not.