[Solved] How to tell how many rows a merged cell takes up?

[UnknownasCaptain]

Hello! I've been googling and trying for a couple hours and I haven't been able to find an answer, so I apologize in advance for what is undoubtedly a simple question.

As part of my workflow, I frequently find myself in this scenario, where I have to select all the rows that are spanned by a single merged cell:

When doing this manually, I find that the keyboard shortcut "Shift+End" does exactly what I want without a fuss. It can tell exactly how many rows are spanned by the merged cell, and selects all the relevant data. So, in this example: Having B2 selected, it can tell that B2 spans from B2-B5, and it expands my selection to all the data in rows 2-5.

So far, so good. Making a macro that analyzes this data for my own purposes is easy, I simply use the functions getCurrentSelection() and getDataArray().

However, I've been trying to automate this process, so that I don't have to press "Shift+End" every time. This is where I find myself stuck. The most data that I can gather from B2 is whether B2.isMerged equals TRUE or FALSE, but this doesn't tell me B2's "height".
What function would I need to be able to tell that, in this example, B2 is actually a merged cell that goes from B2 to B5?

Thanks in advance. i am using OpenOffice 4.1.8 running on Windows 7.

[FJCC]

I can't find a way to do this except with a recorded macro and a little extra code to demonstrate manipulating the result.

Code: Select all

dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Sel"
args1(0).Value = true

dispatcher.executeDispatch(document, ".uno:GoToEndOfRow", "", 0, args1())

CurrSel = ThisComponent.CurrentSelection

Range = CurrSel.RangeAddress
print CurrSel.AbsoluteName
print "Number or rows = " & Range.EndRow - Range.StartRow + 1

[Villeroy]

Code: Select all

view = ThisComponent.getCurrentController()
oSh = ThisComponent.Sheets.getByIndex(0)
addr1 = oSh.getRangeAddress()
oCell = oSh.getCellByPosition(1,1) 'B1
oCursor = oSh.createCursorByRange(oCell)
oCursor.collapseToMergedArea()
addr2 = oCursor.getRangeAddress()
rg = oSh.getCellRangeByPosition(addr2.StartColumn, addr2.StartRow, addr1.EndColumn, addr2.EndRow)
view.select(rg)

[FJCC]

Thanks, Villeroy! I learned something today!

[UnknownasCaptain]

Fantastic, thank you very much, and thanks for the fast response!
One last thing: Using Shift+End, Calc stops the selection at the last row where there is usable data. The selection created by...

Code: Select all

getCellRangeByPosition(addr2.StartColumn, addr2.StartRow, addr1.EndColumn, addr2.EndRow)

...selects all the empty columns all the way to the end of the document, which slows down my script a bit because it has to iterate through all the empty data.
Is there a way to shrink this selection down to just the usable data, much like Shift+End does?
Thanks in advance once again. I'll mark this thread as Solved for now, as the initial question was answered beautifully.

[Villeroy]

The way how you store data is inefficient. It is extremely hard to get any information from wide areas that are defined by formatting attributes.

At least you don't have to do the cell-by-cell walk:

Code: Select all

oUsed = getUsedRange(oSh)
oIntersect = rg.queryIntersection(oUsed.getRangeAddress())
oBlanks = oIntersect.queryEmptyCells()
For each oBlank in oBlanks
  do_stuff
Next

Code: Select all

Function getUsedRange(oSheet)
Dim oCursor
   oCursor = oSheet.createCursor()
   oCursor.gotoStartOfUsedArea(False)
   oCursor.gotoEndOfUsedArea(True)
   getUsedRange = oCursor
End Function

[Villeroy]

Works like this:

[UnknownasCaptain]

I wasn't able to get getDataArray() working from an intersection, but getting the used range did the trick.

For final transparency, I am using this as part of an AutoHotKey script. This is my finished result:

Code: Select all

Calc_Example()
{
	oSM := ComObjCreate("com.sun.star.ServiceManager")			; This line is mandatory with AHK for OOo API
	oDesk := oSM.createInstance("com.sun.star.frame.Desktop")	; Create the first and most important service	
	Array := ComObjArray(VT_VARIANT:=12, 2)
	Array[1] := MakePropertyValue(oSM, "Hidden", ComObject(0xB,true))
	oDoc := oDesk.CurrentComponent("private:factory/scalc", "_blank", 0, Array)

	oSel := oDoc.getCurrentSelection
	oCell := ""
	oActiveSheet := oDoc.getCurrentController().getActiveSheet()
	
	if(oSel.getImplementationName == "ScCellObj"){
		oCell := oSel
	}
	else if(oSel.getImplementationName == "ScCellRangeObj"){
		oCell := oSel.getCellByPosition(0,0)
	}
	else if(oSel.getImplementationName == "ScCellRangesObj"){ ;SSSSSSSSSSSSSS
		oCell := oSel.getByIndex(0).getCellByPosition(0,0)
	}
	else{
		MsgBox % oSel.getImplementationName
		return 
	}

	starterRow := oCell.CellAddress.Row
	starterCol := oCell.CellAddress.Column
	
	;Get the used range of the sheet, first, so we can use it as reference for what columns to evaluate.
	oCursor := oActiveSheet.createCursor()
	oCursor.gotoStartOfUsedArea(False)
	oCursor.gotoEndOfUsedArea(True)
	oUsedRange := oCursor.getRangeAddress()
	
	;Get the area of our base cell, whether merged or not, so we can use it as for reference for what rows to evaluate.
	oCursor := oActiveSheet.createCursorByRange(oCell)
	oCursor.collapseToMergedArea()
	oMergedArea := oCursor.getRangeAddress()

	;All together now!
	rg := oActiveSheet.getCellRangeByPosition(oUsedRange.StartColumn, oMergedArea.StartRow, oUsedRange.EndColumn, oMergedArea.EndRow)
	mData := rg.getDataArray()
	
	row_ := -1
	while(row_ < mData.MaxIndex()){
		row_++
		col_ := -1
		for key in mData[row_]
		{
			col_++
			currRow := starterRow + row_
			currCol := col_
			MsgBox, Evaluating %currRow% - %currCol%, which has a value of %key%!
		}
	}
}

;Used for OOo API.
MakePropertyValue(poSM, cName, uValue)
{	oPropertyValue			:= Object()
	oPropertyValue 			:= poSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
	oPropertyValue.Name		:= cName
	oPropertyValue.Value	:= uValue
	Return oPropertyValue
}

[Villeroy]

Fill down blank ranges? https://sourceforge.net/projects/ooomac ... on%201.01/
The zip contains a spreadsheet document with a Basic library and some routines to copy visible cells an to fill blanks.