h8rt3rmin8r

openoffice-calc-macro-link-extract

Nov 16th, 2018
738
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. REM  *****  BASIC  *****
  2. 'from: https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=2762
  3. 'written by: Villeroy, https://forum.openoffice.org/en/forum/memberlist.php?mode=viewprofile&u=107
  4. 'Retrieved by Jim DeLaHunt, 2014-02-22, for Vancouver Open Data Day
  5. 'Usage: =CELL_URL(SHEET(A1);ROW(A1);COLUMN(A1);B1)
  6. 'Note usage of SHEET() to get current sheet number,
  7. ‘ROW() and COL() to be able to use relative references.
  8. ‘4th parameter (optional) is 1-based index of text item in cell.
  9.    REM  *****  BASIC  *****
  10.    REM ################### RETURNING STRING #################################################
  11.    Function CELL_NOTE(vSheet,lRowIndex&,iColIndex%)
  12.    'calls: getSheetCell
  13.    REM returns annotation text
  14.    Dim v
  15.       v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
  16.       if vartype(v) = 9 then
  17.          CELL_NOTE = v.Annotation.getText.getString
  18.       else
  19.          CELL_NOTE = v
  20.       endif
  21.    End Function
  22.    Function CELL_URL(vSheet,lRowIndex&,iColIndex%,optional n%)
  23.    'calls: getSheetCell
  24.    REM returns URL of Nth text-hyperlink from a cell, default N=1)
  25.    Dim v
  26.       If isMissing(n) then n= 1
  27.       If n < 1 then
  28.          CELL_URL = Null
  29.          exit function
  30.       endif
  31.       v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
  32.       if vartype(v) = 9 then
  33.          if v.Textfields.Count >= n  then
  34.             CELL_URL = v.getTextfields.getByIndex(n -1).URL
  35.          else
  36.             Cell_URL = Null
  37.          endif
  38.       else
  39.          CELL_URL = v
  40.       endif
  41.    End Function
  42.    Function CELL_FORMULA(vSheet,lRowIndex&,iColIndex%)
  43.    'calls: getSheetCell
  44.    REM return unlocalized (English) formula
  45.    Dim v
  46.       v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
  47.       if vartype(v) = 9 then
  48.          CELL_FORMULA = v.getFormula()
  49.       else
  50.          CELL_FORMULA = v
  51.       endif
  52.    End Function
  53.    Function CELL_STYLE(vSheet,lRowIndex&,iColIndex%,optional bLocalized)
  54.    'calls: getSheetCell
  55.    REM return name of cell-style, optionally localized
  56.    Dim v,s$,bLocal as Boolean
  57.       if not isMissing(bLocalized) then bLocal=cBool(bLocalized)
  58.       v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
  59.       if vartype(v) = 9 then
  60.          if bLocal then
  61.             s = thisComponent.StyleFamilies("CellStyles").getByName(v.CellStyle).DisplayName
  62.          else
  63.             s = v.CellStyle
  64.          endif
  65.          CELL_STYLE = s
  66.       else
  67.          CELL_STYLE = v
  68.       endif
  69.    End Function
  70.    Function CELL_LINE(vSheet,lRowIndex&,iColIndex%,optional n)
  71.    'calls: getSheetCell
  72.    REM Split by line breaks, missing or zero line number returns whole string.
  73.    REM =CELL_LINE(SHEET(),1,1,2) -> second line of A1 in this sheet
  74.    Dim v,s$,a(),i%
  75.       v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
  76.       if vartype(v) = 9 then
  77.          s = v.getString
  78.          if not isMissing(n) then i = cInt(n)
  79.          if i > 0 then
  80.             a() = Split(s,chr(10))
  81.             If (i <= uBound(a())+1)then
  82.                CELL_LINE = a(i -1)
  83.             else
  84.                CELL_LINE = NULL
  85.             endif
  86.          else
  87.             CELL_LINE = s
  88.          endif
  89.       else
  90.          CELL_LINE = v
  91.       endif
  92.    end Function
  93.    REM ################### RETURNING NUMBER #################################################
  94.    Function CELL_ISHORIZONTALPAGEBREAK(vSheet,lRowIndex&,iColIndex%)
  95.    'calls: getSheetCell
  96.    Dim v
  97.       v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
  98.       if vartype(v) = 9 then
  99.          CELL_ISHORIZONTALPAGEBREAK = Abs(cINT(v.Rows.getByIndex(0).IsStartOfNewPage))
  100.       else
  101.          CELL_ISHORIZONTALPAGEBREAK = v
  102.       endif
  103.    End Function
  104.    Function CELL_ISVERTICALPAGEBREAK(vSheet,lRowIndex&,iColIndex%)
  105.    'calls: getSheetCell
  106.    Dim v
  107.       v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
  108.       if vartype(v) = 9 then
  109.          CELL_ISVERTICALPAGEBREAK = Abs(cINT(v.Columns.getByIndex(0).IsStartOfNewPage))
  110.       else
  111.          CELL_ISVERTICALPAGEBREAK = v
  112.       endif
  113.    End Function
  114.    Function CELL_CHARCOLOR(vSheet,lRowIndex&,iColIndex%)
  115.    'calls: getSheetCell
  116.    REM returns color code as number
  117.    Dim v
  118.       v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
  119.       if vartype(v) = 9 then
  120.          CELL_CHARCOLOR = v.CharColor
  121.       else
  122.          CELL_CHARCOLOR = v
  123.       endif
  124.    End Function
  125.    Function CELL_BACKCOLOR(vSheet,lRowIndex&,iColIndex%)
  126.    'calls: getSheetCell
  127.    REM returns color code as number
  128.    Dim v
  129.       v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
  130.       if vartype(v) = 9 then
  131.          CELL_BACKCOLOR = v.CellBackColor
  132.       else
  133.          CELL_BACKCOLOR = v
  134.       endif
  135.    End Function
  136.    Function CELL_VISIBLE(vSheet,lRowIndex&,iColIndex%)
  137.    'calls: getSheetCell
  138.    REM returns visibility state as number 0|1
  139.    Dim v
  140.       v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
  141.       if vartype(v) = 9 then
  142.          CELL_VISIBLE = Abs(v.Rows.isVisible)
  143.       else
  144.          CELL_VISIBLE = v
  145.       endif
  146.    End Function
  147.    Function CELL_LOCKED(vSheet,lRowIndex&,iColIndex%)
  148.    'calls: getSheetCell
  149.    REM returns locked state as number 0|1
  150.    Dim v
  151.       v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
  152.       if vartype(v) = 9 then
  153.          CELL_LOCKED = Abs(v.CellProtection.isLocked)
  154.       else
  155.          CELL_LOCKED = v
  156.       endif
  157.    End Function
  158.    Function CELL_NumberFormat(vSheet,lRowIndex&,iColIndex%)
  159.    'calls: getSheetCell
  160.    REM returns the number format index
  161.    Dim v
  162.       v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
  163.       if vartype(v) = 9 then
  164.          CELL_NumberFormat = v.NumberFormat
  165.       else
  166.          CELL_NumberFormat = v
  167.       endif
  168.    End Function
  169.    Function CELL_NumberFormatType(vSheet,lRowIndex&,iColIndex%)
  170.    'calls: getSheetCell
  171.    REM return a numeric com.sun.star.util.NumberFormat which describes a format category
  172.    Dim v,lNF&
  173.       v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
  174.       if vartype(v) = 9 then
  175.          lNF = v.NumberFormat
  176.          CELL_NumberFormatType = ThisComponent.getNumberFormats.getByKey(lNF).Type
  177.       else
  178.          CELL_NumberFormatType = v
  179.       endif
  180.    End Function
  181.    '################### HELPERS FOR ABOVE CELL FUNCTIONS #########################################
  182.    Function getSheet(byVal vSheet)
  183.    REM Helper for sheet functions. Get cell from sheet's name or position; cell's row-position; cell's col-position
  184.    on error goto exitErr
  185.       select case varType(vSheet)
  186.       case is = 8
  187.          if thisComponent.sheets.hasbyName(vSheet) then
  188.             getSheet = thisComponent.sheets.getByName(vSheet)
  189.          else
  190.             getSheet = NULL
  191.          endif
  192.       case 2 to 5
  193.          vSheet = cInt(vSheet)
  194.          'Wow! Calc has sheets with no name at index < 0,
  195.          ' so NOT isNull(oSheet), if vSheet <= lbound(sheets) = CRASH!
  196.          'http://www.openoffice.org/issues/show_bug.cgi?id=58796
  197.          if(vSheet <= thisComponent.getSheets.getCount)AND(vSheet > 0) then
  198.             getSheet = thisComponent.sheets.getByIndex(vSheet -1)
  199.          else
  200.             getSheet = NULL
  201.          endif
  202.       end select
  203.    exit function
  204.    exitErr:
  205.    getSheet = NULL
  206.    End Function
  207.    Function getSheetCell(byVal vSheet,byVal lRowIndex&,byVal iColIndex%)
  208.    dim oSheet
  209.    '   print vartype(vsheet)
  210.       oSheet = getSheet(vSheet)
  211.       if varType(oSheet) <>9 then
  212.          getSheetCell = NULL
  213.       elseif (lRowIndex > oSheet.rows.count)OR(lRowIndex < 1) then
  214.          getSheetCell = NULL
  215.       elseif (iColIndex > oSheet.columns.count)OR(iColIndex < 1) then
  216.          getSheetCell = NULL
  217.       else
  218.          getSheetCell = oSheet.getCellByPosition(iColIndex -1,lRowIndex -1)
  219.       endif
  220.    End Function
RAW Paste Data