SHARE
TWEET

openoffice-calc-macro-link-extract

h8rt3rmin8r Nov 16th, 2018 196 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top