Here's a different approach. I wondered if there's a way to determine the page breaks using IsStartOfNewPage
. This works after making LO Calc calculating the page breaks by switching into the PageBreak View and back. Now, counting pages is quite easy by iterating over all used cells (using the current sheet's Cursor
and GotoEndOfUsedArea
).
I didn't test if cells spanning multiple pages will lead to wrong page count. Also, i assume that the resulting ToC will never take more than one page.
Option Base 0
Option Explicit
Private Type SheetInformation
SheetIndex As Long
SheetName As String
PageStart as Long
PageEnd as Long
PageCount As Long
End Type
Public Sub Calc_ToC
If (False = IsSpreadsheetDoc(ThisComponent)) Then
MsgBox "Works only for spreadsheets!"
Exit Sub
End If
ThisComponent.LockControllers
Dim mySheets(ThisComponent.Sheets.getCount() - 1) As New SheetInformation
Dim origSheet As Long
origSheet = ThisComponent.getCurrentController.ActiveSheet.RangeAddress.Sheet
Call collectSheetInfo(mySheets)
dim document as Object
dim dispatcher as Object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Nr"
args1(0).Value = origSheet + 1
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())
ThisComponent.unlockControllers()
Call insertToc(mySheets)
End Sub
Private Sub collectSheetInfo(allSheetsInfo() as New SheetInformation)
Dim i As Long
Dim maxPage As Long
maxPage = 0
For i = 0 To UBound(allSheetsInfo)
Dim sheetInfo As New SheetInformation
sheetInfo.SheetIndex = i
sheetInfo.SheetName = ThisComponent.Sheets.getByIndex(sheetInfo.SheetIndex).getName()
Call getPageCount(sheetInfo)
sheetInfo.PageStart = maxPage + 1
sheetInfo.PageEnd = sheetInfo.PageStart + sheetInfo.PageCount - 1
maxPage = sheetInfo.PageEnd
allSheetsInfo(i) = sheetInfo
Next
End Sub
Private Sub getPageCount(s As SheetInformation)
Dim oSheet, oCell, oCursor As Object
Dim i, j, pageCount As Long
Dim isHorizontalPageBreak, isVerticalPageBreak As Boolean
dim document as Object
dim dispatcher as Object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Nr"
args1(0).Value = s.SheetIndex + 1
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())
args1(0).Name = "PagebreakMode"
args1(0).Value = true
dispatcher.executeDispatch(document, ".uno:PagebreakMode", "", 0, args1())
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "NormalViewMode"
args2(0).Value = true
dispatcher.executeDispatch(document, ".uno:NormalViewMode", "", 0, args2())
oSheet = ThisComponent.Sheets.getByIndex(s.SheetIndex)
oCursor = oSheet.createCursor
oCursor.GotoEndOfUsedArea(True)
pageCount = 1
For i=0 To oCursor.RangeAddress.EndColumn
For j=0 To oCursor.RangeAddress.EndRow
oCell = oSheet.GetCellByPosition(i,j)
isHorizontalPageBreak = Abs(cINT(oCell.Rows.getByIndex(0).IsStartOfNewPage))
isVerticalPageBreak = Abs(cINT(oCell.Columns.getByIndex(0).IsStartOfNewPage))
If i = 0 Then
If isHorizontalPageBreak Then
pageCount = pageCount + 1
End If
ElseIf j = 0 Then
If isVerticalPageBreak Then
pageCount = pageCount + 1
End If
Else
If (isHorizontalPageBreak AND isVerticalPageBreak) Then
pageCount = pageCount + 1
End if
End if
Next j
Next i
s.pageCount = pageCount
End Sub
''' -------------------------------------------------------------
''' IsSpreadsheetDoc - Check if current document is a calc file
''' -------------------------------------------------------------
''' Source: "Useful Macro Information For OpenOffice.org By
''' Andrew Pitonyak", Ch. 6.1
''' -------------------------------------------------------------
Private Function IsSpreadsheetDoc(oDoc) As Boolean
Dim s$ : s$ = "com.sun.star.sheet.SpreadsheetDocument"
On Local Error GoTo NODOCUMENTTYPE
IsSpreadsheetDoc = oDoc.SupportsService(s$)
NODOCUMENTTYPE:
If Err <> 0 Then
IsSpreadsheetDoc = False
Resume GOON
GOON:
End If
End Function
Private Sub Result(s() As SheetInformation)
Dim msg As String
Dim i As Integer
Dim obj As SheetInformation
msg = ""
For i = 0 To UBound(s)
obj = s(i)
With obj
msg = msg & .SheetName & " (Index: " & .SheetIndex & _
") - Pages: " & .PageCount & _
" - from/to: " & .PageStart & "/" & .PageEnd & CHR(13)
End With
Next
MsgBox(msg)
End Sub
Private Sub insertToC(s() As SheetInformation)
Select Case MsgBox("Insert ToC on cursor position?" & CHR(10) & _
"(Yes: Insert at cursor; No: stop macro)", 36)
Case 6 'Yes - insert at cursor position'
Call DoInsert(s)
Case 7 'No - insert on new sheet'
ThisComponent.unlockControllers()
Exit Sub
End Select
End Sub
Private Sub DoInsert(s() As SheetInformation)
Dim oSheet, oCell, startCell As Object
Dim sheet,rowStart, colStart, row, col, start As Long
Dim sName As String
Dim currentSheet As SheetInformation
Dim newToc As Boolean
oSheet = ThisComponent.getCurrentController.ActiveSheet
startCell = ThisComponent.getCurrentSelection()
oCell = startCell
rowStart = startCell.CellAddress.Row
colStart = startCell.CellAddress.Column
oCell.SetString("Table of Contents")
For sheet = 1 to Ubound(s) + 1
currentSheet = s(sheet - 1)
row = rowStart + sheet
oCell = oSheet.getCellByPosition(colStart, row) ' column B
oCell.SetString(currentSheet.SheetName)
oCell = oSheet.getCellByPosition(colStart + 2, row) ' column D
start = currentSheet.PageStart
oCell.SetString("Page " & start)
Next
ThisComponent.unlockControllers()
End Sub
I've used some example code by Andrew Pitonyak ("Useful Macro Information For OpenOffice.org By Andrew Pitonyak (ODT)" and "OpenOffice.org Macros Explained (PDF)") and by Villeroy's Cell introspection module, and of course some of JimK's solution.
EDIT:
The macro doesn't test every page if it contains printable content. It simply assumes that the complete "used" cell range (identified using GotoEndOfUsedArea
) should be taken into account when creating the ToC. Thus, it may count empty pages as pages to print. So, it might yield bad results for sparsely filled sheets. But i hope it behaves more reliably for most cases where there are no empty pages.
So, it will expect the following sheets to be printed on six pages, even if one page (without X
) may stay empty:
+-+-+ +-+-+ +-+-+
|X|X| |X|X| |X| |
+-+-+ +-+-+ +-+-+
|X| | | |X| | | |
+-+-+ +-+-+ +-+-+
|X|X| |X|X| | |X|
+-+-+ +-+-+ +-+-+
outline
part. Nonetheless the idea can be useful. Maybe you can find some inspiration in pitonyak, 7.18, or you can do a cycle searching for heading and heading1 cell characteristic in the other sheet (maybe 1st column only) or blocks and print areas. Tricky the pagenumber, maybe computable from the print area number. Sorry just ideas, not more...