Long Calc Sheet - subtotal at bottom of page, transfer to next page?

Question

Assume i have a LibreOffice Calc sheet spanning multiple pages in printout. Is there a way to calculate a subtotal for each page and print it at the bottom of the respective page as well as at the top of the following page?

I know that one can use subtotals to calculate subtotals automatically, but "only" depending on the data (e.g. dates, names, part numbers) but not simply for each page. Additionally, the calculated subtotal won't appear on the next page.

Answer 1

Cell values such as totals can be put in a header and/or footer through use of a macro. The macro below was created by Zizi64, Tibor Kovacs, for Open Office and is copied from his spreadsheet Prestige2.ods from the link above. Modify it for your needs.

This has been tested in LibreOffice and it works. Of course, you'll need to enable macros in Options | Security. In the example below, calling macro EditFooterHeader() inserts the value of cell L1 into the footer.

REM ***** BASIC *****

OPTION EXPLICIT

Function EditFooterText(WS_Index as integer, MyFooterLeftText, MyFooterCenterText, MyFooterRightText as string) as string

Dim oDocument as Object Dim oSheet as object Dim oPStyle as Object Dim oThisStyle as Object Dim oFContent as Object Dim oText as Object Dim oCursor as Object Dim oField as Object Dim i as integer Dim StyleName as string Dim sAns as String

  rem Adjusting the actual pagestyle (Pagestyle of actual WorkSheet

in this document) oDocument = ThisComponent oSheet = oDocument.Sheets.getByIndex( WS_Index-1 ) oPStyle = oDocument.StyleFamilies.getByName("PageStyles") oThisStyle = oPStyle.getByName(oSheet.PageStyle) StyleName = oThisStyle.Name

    oThisStyle.FooterOn = True
    'Zizi64: False/True turns on/off the running foot

    oFContent = oThisStyle.RightPageFooterContent
    'Zizi64: Get the all text from running foot

'******************************************************** ' oText = oFContent.LeftText ' oCursor = oText.createTextCursor() ' oText.insertString(oCursor, "", True)

' oCursor.CharHeight = 12 ' oCursor.CharFontName = "Arial" ' oCursor.CharWeight = com.sun.star.awt.FontWeight.NORMAL ' oCursor.CharPosture = com.sun.star.awt.FontSlant.NONE ' oCursor.CharUnderline = com.sun.star.awt.FontUnderline.NONE ' ' insert text ... ' oText.insertString(oCursor, MyFooterLeftText, False) '********************************************************

' oText = oFContent.CenterText ' oCursor = oText.createTextCursor() ' oText.insertString(oCursor, "", True)

' oCursor.CharHeight = 12 ' oCursor.CharFontName = "Courir New" ' oCursor.CharWeight = com.sun.star.awt.FontWeight.NORMAL ' oCursor.CharPosture = com.sun.star.awt.FontSlant.NONE ' oCursor.CharUnderline = com.sun.star.awt.FontUnderline.NONE

' oText.insertString(oCursor, MyFooterCenterText, False) '********************************************************

    oText = oFContent.RightText

    oCursor = oText.createTextCursor()

    oText.insertString(oCursor, "", True)

    oCursor.CharHeight = 12
    oCursor.CharFontName = "Times New Roman"
    oCursor.CharWeight = com.sun.star.awt.FontWeight.NORMAL
    oCursor.CharPosture = com.sun.star.awt.FontSlant.NONE
    oCursor.CharUnderline = com.sun.star.awt.FontUnderline.NONE

    oText.insertString(oCursor, MyFooterRightText, False) '********************************************************  



    oThisStyle.RightPageFooterContent = oFContent      'write content back into running foot

          EditFooterText = StyleName & ": Style modified!:" End function