[Solved] Removing/Adding Page Breaks, cell borders

[lieutdan13]

I had all of this done in Excel, but it doesn't work now that I moved over to OO. I would like to do the following:

1) Remove all page breaks for the entire sheet

2) Remove all cell borders

3) Find the last row in the sheet with content (I already know what the column is going to be)

4) Loop through all rows with content and apply 1 (one) page break if it reaches a row whose "A" cell is empty, but has content in "C". Do not apply anymore page breaks.

5) Select all rows "A:C" with content and apply a border for better print readability


Like I said, I had this all working in Excel. I just want this to work in OO. I don't care what language. Can anyone or many of you point me in the right direction? Thank you in advance.

[Charlie Young]

I had all of this done in Excel, but it doesn't work now that I moved over to OO. I would like to do the following:

1) Remove all page breaks for the entire sheet

I'm doing ooBasic here, leave it as an exercise to translate. I'll be working on the active sheet.

Remove the page breaks:

Code: Select all

Sub removePageBreaks
	Dim oDoc as Object
	Dim oSheet As Object
	
	oDoc = ThisComponent
	oSheet = oDoc.CurrentController.ActiveSheet
	oSheet.removeAllManualPageBreaks()
		
End Sub

2) Remove all cell borders

Pass the activesheet as fromRange to:

Code: Select all

Sub removeCellBorders(fromRange As Object)
	Dim BlankBorder As new com.sun.star.table.BorderLine
	
	BlankBorder.Color = 0
	BlankBorder.InnerLineWidth = 0
	BlankBorder.LineDistance = 0
	BlankBorder.OuterLineWidth = 0
	
	With fromRange
		.DiagonalBLTR = BlankBorder
		.DiagonalTLBR = BlankBorder
		.TopBorder = BlankBorder
		.BottomBorder = BlankBorder
		.LeftBorder = BlankBorder
		.RightBorder = BlankBorder
	End With

End Sub	

3) Find the last row in the sheet with content (I already know what the column is going to be)

Couple of ways to do this. I'll just get the entire used area, starting at A1

Code: Select all

Function getUsedArea() As Object
	Dim oDoc As Object
	Dim oSheet As Object
	Dim oCellCursor As Object
	oDoc  = ThisComponent
    oSheet = oDoc.CurrentController.getActiveSheet()
    oCellCursor = oSheet.createCursor()
    oCellCursor.GotoEndOfUsedArea(True)	
	getUsedArea = oCellCursor
End Function

Then just use that to get the end of the area. The last row with content is getEndofUsedArea.Row. Note that it is just as easy here to get the column at the same time.

Code: Select all

Function getEndofUsedArea() As new com.sun.star.table.CellAddress
	Dim oCellCursor As Object
	Dim UsedArea As Object
	Dim ca As new com.sun.star.table.CellAddress
	
	UsedArea = getUsedArea()	
	ca.Sheet = UsedArea.RangeAddress.Sheet
	ca.Column = UsedArea.RangeAddress.EndColumn
	ca.Row = UsedArea.RangeAddress.EndRow
	getEndofUsedArea = ca
End Function

4) Loop through all rows with content and apply 1 (one) page break if it reaches a row whose "A" cell is empty, but has content in "C". Do not apply anymore page breaks.

Here's the main routine, which does 1-3 and then 4. It inserts the break above said row, you might want to make it happen one more row down.

Code: Select all

Sub Main
	Dim oDoc As Object
	Dim oSheet As Object
	Dim aRow As Object
	Dim BreakRow As Boolean
	Dim i As Long
	Dim end_row As Long
	oDoc  = ThisComponent
    oSheet = oDoc.CurrentController.getActiveSheet()
    removePageBreaks()
    removeCellBorders(oSheet)
    
    end_row = getEndofUsedArea.Row
    i = 0
    BreakRow = False
    Do While i <= end_row and not BreakRow
    	aRow = oSheet.Rows(i)
    	if aRow.getCellByPosition(0,0).getType() = com.sun.star.table.CellContentType.EMPTY And aRow.getCellByPosition(2,0).getType() <> com.sun.star.table.CellContentType.EMPTY then
    		BreakRow = True
    	else
    		i = i + 1
    	endif
    Loop
    if BreakRow then
    	aRow.isStartOfNewPage = True
    endif 
End Sub

5) Select all rows "A:C" with content and apply a border for better print readability

Like I said, I had this all working in Excel. I just want this to work in OO. I don't care what language. Can anyone or many of you point me in the right direction? Thank you in advance.

See if you can manage part 5 given 1-4.

[lieutdan13]

Thank you Charlie for taking the time to write your post!! This is exactly what I needed. I had no problem with the code. It was just a matter of copy and paste. And I did figure out how to add the border to the active range. I copied and modified removeCellBorders, got the active range "oRange = oSheet.getCellRangeByPosition(0, 0, 2, end_row)" and called my new sub-routine. Thanks a bunch.