Obtain row and column index from cell name in LibreOffice Calc

Question

I have a cell name as value in a variable. Example: myCellName = "C9"

I need the index of row and column for that cell in a separate variables. Example:

• rowIndex = myCellName .getRow() 3
• columnIndex = myCellName .getColumn() 9

Answer 1

At first: In your question you messed row and column. C9 is column C which is the third column and row 9 which is the ninth row.

At second: With Liberoffice and Openoffice the column and row numbers are 0 based. So the third column is column 2 and the ninth row is row 8.

To get the column number and row number from a cell name, you can use getcellRangeByName, see https://wiki.openoffice.org/wiki/Documentation/BASIC_Guide/Cells_and_Ranges, and then get the CellAddress from this Range object using getCellAddress, see https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XCellAddressable.html.

Examples:

First we use an existent Calc document with an existent sheet.

Sub Test1

 myCellName = "C9"

 oRange = ThisComponent.Sheets(0).getCellRangeByName(myCellName)

 oCellAddress = oRange.getCellAddress()

 msgbox oCellAddress.Row '8
 msgbox oCellAddress.Column '2

End Sub

Maybe we have not an existent Calc document with an existent sheet, then we can create some first:

Sub Test2

 myCellName = "C9"

 oDoc = CreateUnoService("com.sun.star.sheet.SpreadsheetDocument")
 oSheet = oDoc.createInstance("com.sun.star.sheet.Spreadsheet")
 oDoc.Sheets.insertByName("MySheet", oSheet)

 oRange = oSheet.getCellRangeByName(myCellName)

 oCellAddress = oRange.getCellAddress()

 msgbox oCellAddress.Row '8
 msgbox oCellAddress.Column '2

 oCellAddress = Nothing
 oRange = Nothing
 oSheet = Nothing
 oDoc = Nothing

End Sub