This is a collection of useful BASIC functions/subs for Open Office Spreadsheets.
I have assembled the collection in small functions/subs with comments describing
what the procedures do.
For more information see also:
Open Office BASIC Programming Guide
'------------------------------------------------------------------ ' general utilities '------------------------------------------------------------------ ' check if document is a calc document ' returns: false, if no spreadsheet document ' true, else Function isSpreadhsheet( oDoc ) As Boolean On Local Error GoTo isSpreadhsheetException isSpreadhsheet = oDoc.SupportsService(_ "com.sun.star.sheet.SpreadsheetDocument") isSpreadhsheetException: If Err <> 0 Then isSpreadhsheet = False Resume isSpreadhsheetExceptionResume isSpreadhsheetExceptionResume: End If End Function '------------------------------------------------------------------ ' get the workbook Function getWorkbook( ) getWorkbook = ThisComponent End Function
'------------------------------------------------------------------ ' get the sheet object by sheet name ' oDoc, the workbook ' name, the name of the sheet Function getSheetByName( oDoc, name ) if name <> "" then getSheetByName = oDoc.Sheets.getByName( name ) end if End Function '------------------------------------------------------------------ ' gets a sheet object by index ' oDoc, the workbook ' index, the index of the sheet Function getSheetByIndex( oDoc, index ) getSheetByIndex = oDoc.Sheets.getByIndex( index ) End Function
'------------------------------------------------------------------ ' get the cell range of sheet oSheet from column c1 to c2 and ' row r1 to r2 Function getRange( oSheet, c1,r1,c2,r2 ) getRange = oSheet.getCellRangeByPosition( c1,r1,c2,r2) End Function '------------------------------------------------------------------ ' get the cell range or a single cell of sheet oSheet by name ' e.g. A7 or A7:B11 Function getRangeByName( oSheet, name ) getRangeByName = oSheet.getCellRangeByName( name ) End Function '------------------------------------------------------------------ ' get the cell range of workbook oDoc defined by name Function getNamedRange( oDoc, name ) getNamedRange = oDoc.NamedRanges.getByName(name).getReferredCells() End Function '------------------------------------------------------------------ ' get the cell of oSheet from column c1 and row r1 Function getCell( oSheet, c1,r1 ) getCell = oSheet.getCellByPosition( c1,r1 ) End Function
'------------------------------------------------------------------ ' get the absolute address as string of the given simple range rng Function getRangeName( rng ) as string getRangeName = rng.AbsoluteName End Function '------------------------------------------------------------------ ' get the range address of range rng as a string without the ' sheet name Function getRangeNameRel( rng ) as string dim str as string str = getRangeName( rng ) pos = instr(str,".")+1 str = mid(str, pos, len(str)-pos+1) getRangeNameRel = str End Function
'------------------------------------------------------------------- ' returns the current selection of the workbook sub getCurrentSelection() getCurrentSelection = ThisComponent.CurrentSelection end sub '------------------------------------------------------------------- ' selects the given range sub selectRng( rng ) ThisComponent.CurrentController.Select( rng ) end sub '------------------------------------------------------------------- ' selects the range with rows and columns c1,r1,c2,r2 of sheet oSheet sub selectRange( oSheet, c1,r1,c2,r2 ) selectRng( oSheet.getCellRangeByPosition(c1,r1,c2,r2) ) end sub '------------------------------------------------------------------- ' selects the cell with row r and column c of sheet oSheet sub selectCell(oSheet, c, r) ThisComponent.CurrentController.Select(oSheet.getCellByposition(c,r)) end sub
' get the value of cell Function getVal( cell ) getVal = cell.getValue End Function ' get the string of cell Function getString( cell ) getString = cell.getString End Function ' get the formula of cell Function getFormula( cell ) getFormula = cell.getFormula End Function
' set the value of cell sub setVal( cell, val ) cell.setValue( val ) End sub ' set the string of cell sub setString( cell, val ) cell.setString( val ) End sub ' set the formula of cell sub setFormula( cell, val ) cell.setFormula( val ) End sub ' set range to local current format sub setLocalCurrency( rng ) Dim aLocale As New com.sun.star.lang.Locale rng.NumberFormat = oFormats.getStandardFormat(_ com.sun.star.util.NumberFormat.CURRENCY, aLocale) End sub
'------------------------------------------------------------------ ' returns the column for not empty cells starting from nCol, nRow ' going left Function getFilledCol( oSheet, nCol, nRow ) dim cell as object x = nCol cell = oSheet.getCellByPosition(x,nRow) while cell.getType() <> com.sun.star.table.CellContentType.EMPTY x = x+1 cell = oSheet.getCellByPosition(x,nRow) wend getFilledCol = x-1 End Function ' returns the row for the area of not empty cells ' starting from nCol, nRow going down Function getFilledRow( oSheet, nCol, nRow ) dim cell as object y = nRow cell = oSheet.getCellByPosition(nCol,y) while cell.getType() <> com.sun.star.table.CellContentType.EMPTY y = y+1 cell = oSheet.getCellByPosition(nCol,y) wend getFilledRow = y-1 End Function
'----------------------------------------------------------------------- ' insert row before the current selected position sub insertRow dim document as object dim dispatcher as object rem get access to the document document = ThisComponent.CurrentController.Frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") dispatcher.executeDispatch(document, ".uno:InsertRows", "", 0, Array()) end sub '----------------------------------------------------------------------- ' insert row before the current selected position sub insertRowIdx(sheet, index, elements) sheet.Rows.insertByIndex(index, elements) end sub '----------------------------------------------------------------------- ' insert row before the current selected position sub insertColIdx(sheet, index, elements) sheet.Columns.insertByIndex(index, elements) end sub '----------------------------------------------------------------------- ' insert row before the current selected position sub deleteRowIdx(sheet, index, elements) sheet.Columns.removeByIndex(index, elements) end sub '----------------------------------------------------------------------- ' insert row before the current selected position sub deleteColIdx(sheet, index, elements) sheet.Rows.removeByIndex(index, elements) end sub '----------------------------------------------------------------------- sub deleteColByName(name) rem ------------------------------------------------------------------ rem define variables 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 = "Flags" args1(0).Value = name dispatcher.executeDispatch(document, ".uno:DeleteCell", "", 0, args1()) end sub
'----------------------------------------------------------------------- ' copy the range rngSrc to range starting at cell clDst sub copyRng( rngSrc, clDst ) Dim clDstAdr Dim rngSrcAdr rngSrcAdr = rngSrc.getRangeAddress() clDstAdr = clDst.getCellAddress() shDst.copyRange( clDstAdr, rngSrcAdr ) end sub '----------------------------------------------------------------------- ' copy the range rngSrc to range starting at cell clDst sub copyRng( rngSrc, clDst ) Dim clDstAdr Dim rngSrcAdr rngSrcAdr = rngSrc.getRangeAddress() clDstAdr = clDst.getCellAddress() shDst.copyRange( clDstAdr, rngSrcAdr ) end sub '----------------------------------------------------------------------- ' move the range rngSrc to range starting at cell clDst ' !!!TODO not tested yet sub moveRng( sheet, rngSrc, clDst ) Dim clDstAdr As New com.sun.star.table.CellAddress Dim rngSrcAdr As New com.sun.star.table.CellRangeAddress rngSrcAdr = rngSrc.getRangeAddress() clDstAdr = clDst.getCellAddress() sheet.moveRange( clDstAdr, rngSrcAdr ) end sub '----------------------------------------------------------------------- ' delete the range rng of sheet and shift the underlaying cells up ' !!!TODO not tested yet sub delRng( sheet, rng ) Dim rngAdr As New com.sun.star.table.CellRangeAddress rngAdr = rng.getRangeAddress() sheet.removeRange(rngAdr, com.sun.star.sheet.CellDeleteMode.UP) ' NONE the current values remain in their current position. ' UP the cells at and below the delete position are moved upwards. ' LEFT the cells at and to the right of the delete position are moved ' to the left. ' ROWS the rows after the delete position are moved upwards. ' COLUMNS the columns after the delete position are moved to the left. end sub '----------------------------------------------------------------------- ' insert the range rng of sheet and shift the underlaying cells up ' !!!TODO not tested yet sub insertRng( sheet, rng ) Dim rngAdr As New com.sun.star.table.CellRangeAddress rngAdr = rng.getRangeAddress() sheet.insertCells(rngAdr, com.sun.star.sheet.CellInsertMode.UP) ' NONE the current values remain in their current position. ' UP the cells at and below the insert position are moved upwards. ' LEFT the cells at and to the right of the insert position are moved ' to the left. ' ROWS the rows after the insert position are moved upwards. ' COLUMNS the columns after the insert position are moved to the left. end subCompute functions of a range with
CellRange.computeFunction(com.sun.star.sheet.GeneralFunction.CMD) ' for CMD use one of ' SUM sum of all numerical values ' COUNT total number of all values (including non-numerical values) ' COUNTNUMS total number of all numerical values ' AVERAGE average of all numerical values ' MAX largest numerical value ' MIN smallest numerical value ' PRODUCT product of all numerical values ' STDEV standard deviation ' VAR variance ' STDEVP standard deviation based on the total population ' VARP variance based on the total population Dim ReplaceDescriptor As Object ReplaceDescriptor = Sheet.createReplaceDescriptor() ReplaceDescriptor.SearchString = "is" ReplaceDescriptor.ReplaceString = "was" Sheet.ReplaceAll(ReplaceDescriptor) Dim Flags As Long Flags = com.sun.star.sheet.CellFlags.FLAG + _ ... rng.clearContents(Flags) ' with FLAG one of ' VALUE numerical values that are not formatted as date or time ' DATETIME numerical values that are formatted as date or time ' STRING strings ' ANNOTATION comments that are linked to cells ' FORMULA formulas ' HARDATTR direct formatting of cells ' STYLES indirect formatting ' OBJECTS drawing objects that are connected to cells ' EDITATTR character formatting that only applies to parts of the cells
Shell( CommandString, ParameterString, 2, true )