[Solved] How to tell how many rows a merged cell takes up?
-
- Posts: 3
- Joined: Wed Jul 21, 2021 4:37 pm
[Solved] How to tell how many rows a merged cell takes up?
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.
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.
Last edited by UnknownasCaptain on Thu Jul 22, 2021 4:45 pm, edited 1 time in total.
OpenOffice 4.1.8 - Win7
Re: How to tell how many rows a merged cell takes up?
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
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: How to tell how many rows a merged cell takes up?
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)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to tell how many rows a merged cell takes up?
Thanks, Villeroy! I learned something today!
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
-
- Posts: 3
- Joined: Wed Jul 21, 2021 4:37 pm
Re: How to tell how many rows a merged cell takes up?
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...
...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.
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)
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.
OpenOffice 4.1.8 - Win7
Re: [Solved] How to tell how many rows a merged cell takes u
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:
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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] How to tell how many rows a merged cell takes u
Works like this:
- Attachments
-
- t105739.ods
- (13.66 KiB) Downloaded 180 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 3
- Joined: Wed Jul 21, 2021 4:37 pm
Re: [Solved] How to tell how many rows a merged cell takes u
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:
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
}
OpenOffice 4.1.8 - Win7
Re: [Solved] How to tell how many rows a merged cell takes u
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.
The zip contains a spreadsheet document with a Basic library and some routines to copy visible cells an to fill blanks.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice