| View previous topic :: View next topic |
| Author |
Message |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
 Posted: Sat Mar 03, 2007 9:41 am Post subject: [Calc]Click a cell and set property of another cell or range |
 |
|
This addresses some of the more frequently asked questions in the most simple and generic way. You can perform several actions on a range by using a well prepared hyperlink, which calls a macro passing the entire URL. The macro can evaluate additional arguments from the URL-string and perform the desired action.
You prepare one or many cells with a hyperlink, preferably a by formula =HYPERLINK(URL;displayString), which calls a macro and the macro gets the entire URL as string argument. The url can be calculated by cell-functions so you can pass string-arguments dynamically. You may even style the hyperlink-cells, so they appear like buttons. For instance:
| Code: |
=IF(ISBLANK(A1);"No Go"&T(STYLE("buttonDisabled"));HYPERLINK(urlToMacro;"Push Me")&T(STYLE("buttonEnabled")))
|
where "buttonDisabled" and "buttonEnabled" are two cell-styles that make a cell appear like buttons. If A1 is blank then the cell shows "No Go", styled to look like a disabled button else it provides a hyperlink to a macro, styled as enabled button. Since all used relative references adjust to each cell, you can create many "pseudo-buttons" on the fly by drag&drop or copy&paste. Each button may calculate it's own url from relative references. This can save many hours of coding.
My example "setRangePropertyByURL", has to be set up like this (one line):
vnd.sun.star.script:MyCalcLibrary.MyModule.setRangePropertyByURL?location=application&language=Basic
#file:///tmp/target.ods#Sheet1.A1:F1&PropertyName=Value
The red part is essential for calling the macro. Argument location=application has to be location=document if the macro to be called is saved within the current document.
The next blue url-part behind the "#" follows just the same syntax as hyperlinks in any document, pointing to a range in a spreadsheet. It may refer to another spreadsheet-file, followed by second #-mark, specifying the cell-range. It may be "#Sheet1.A1:F1" without file-url if the current document is a spreadsheet. It may be "#A1:F1" without file-url if "A1:F1" is a valid address on the current sheet. I may even refer to a named (database-) range "#NamedRange". If the #-part works as a stand-alone hyperlink to a range, then it should work within the macro-hyperlink as well.
Last argument &PropertyName=Value
PropertyName has to be a valid, case-sensitive property-name of a range object. My example setRangePropertyByURL can not use pseudo-properties nor properties, referencing other objects. The allowed properties-values need to be assignable to strings or numbers. I prepared a macro reportSimpleCellAndRangeProperties() which prints all valid properties for cells and ranges into a newly created sheet. A cell has the same set of simple properties as a range except for property FormulaLocal. FormulaLocal allows you to set the value or formula of another cell by clicking on a hyperlink.
Examples: =HYPERLINK("...&IsTextWrapped=TRUE")
=HYPERLINK("...&FormulaLocal=3.14")
=HYPERLINK("...&CellStyle=Result")
Localization issues
Problem if &value=boolean: You have to use english "True"/"False". With a reference to boolean cells, you may get invalid boolean strings, for instance german "WAHR"/"FALSCH". Use a numeric conversion in order to avoid localization: "...&IsTextWrapped="&N(A1) or "...&IsTextWrapped="&INT(A1) which converts a boolean in A1 to a numeric string. I could not find an UNO-service to handle this for all languages but cBool("numString") returns True for all numeric strings <> "0".
Property "FormulaLocal" is the same string you would type into the formula bar. It has to use function names, decimal separators and date/time/booleans according to the current locale (german: ...&FormulaLocal==PRODUKT(3,14;WAHR;31.12.2006)". International solution: Put the formula into a helper-cell and use ...&FormulaLocal="&FORMULA(A1)
Solved problem if &property=CellStyle: My example setRangePropertyByURL handles localized style-names. You can use &CellStyle=Ergebnis with a german locale where built-in style "Result" is displayed as "Ergebnis". The built-in english names (Default, Heading, Heading1, Result, Result1) will work in any case.
Problem if &Property=Decimal: Use the same decimal format as in VALUE("1.23") or VALUE("1,23") respectively.
The same technique can be used with any kind of documents, using similar macros. If your macros always operate on the same obects, you can omit the evaluation of additional arguments, just calling the red part of the avove scheme.
setRangePropertyByURL provides a more elaborate example for flexible manipulation of a spreadsheet ranges, specified by calculated arguments, passed to one single macro. You could even call different macros, depending on cell values.
Here we go. Copy to any module in any Basic-library and set your hyperlink-urls accordingly.
| Code: |
REM ***** BASIC *****
Option Explicit
'vnd.sun.star.script:Calc.propURL.setRangePropertyByURL?language=Basic&location=application#
Sub setRangePropertyByURL(byVal sURL$)
'calls: getSheetFromStringAddress, getURLStruct, loadDocument, getRangeByName,
' setSimplePropertyValue, getRealStyleName
Dim url, sMacro$, sMark
Dim oDoc, oSheet, oRange
Dim iPos%, strAddress$, sTail$
Dim sProperty$, sValue$
url = getURLStruct(sURL, "vnd.sun.star.script:")
if not isUnoStruct(url) then exit sub
sMacro = url.Main
sMark = url.Mark
sURL = sMark
url = getURLStruct(sURL)
if isUnoStruct(url) then
' the mark of the script-url has a valid file-url
oDoc = loadDocument(url.Main)
' get mark #Sheet.A1&argument=value
sMark = url.Mark
else
oDoc = thisComponent
endif
if isObject(oDoc) then
sURL = oDoc.getURL()
if oDoc.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
iPos = instr(sMark, "&")
if iPos > 0 then
strAddress = left(sMark, iPos -1)
sTail = mid(sMark, iPos +1)
iPos = instr(sTail, "=")
if iPos > 0 then
sProperty = left(sTail, iPos -1)
sValue = mid(sTail, iPos +1)
endif
endif
if iPos <= 0 then
msgbox "Missing &property=value in "& sMark, 16, sMacro
exit sub
endif
oSheet = getSheetFromStringAddress(oDoc, strAddress)
if isEmpty(oSheet) then oSheet = oDoc.CurrentController.getActiveSheet()
oRange = getRangeByName(oSheet, strAddress)
if isEmpty(oRange) then
msgbox "No such range: "& strAddress, 16, sMacro
exit sub
endif
else
msgbox "No spreadsheet: "& sURL, 16, sMacro
exit sub
endif
else
msgbox "No such file: "& url.Main, 16, sMacro
exit sub
endif
REM OK, we have a doc, a range on a sheet, a property-name and a property-value
REM added a little helper for a frequently used property, which may have localized names
if sProperty = "CellStyle" then
sValue = getRealStyleName(oDoc.StyleFamilies.getByName("CellStyles"), sValue)
'if value = "" then value = "Default"
endif
if not setSimplePropertyValue(oRange, sProperty, sValue) then
msgbox "Could not set "& sProperty &" to "& sValue, 16, sMacro
endif
End Sub
'_______________________________________________
Function getURLStruct(sURL$, Optional service$)
Dim srv, url , bOK as Boolean
srv = createUnoService("com.sun.star.util.URLTransformer")
url = createUnoStruct("com.sun.star.util.URL")
url.Complete = sURL
if isMissing(service) then
bOK = srv.parseStrict(url)
else
bOK = srv.parseSmart(url, service)
endif
if bOK then getURLStruct = url
End Function
'_______________________________________________
Function loadDocument(sURL$)
on error goto exitErr
loadDocument = Stardesktop.loadComponentFromURL(sURL, "_default", 0, Array())
exitErr:
End Function
'_______________________________________________
Function getRangeByName(oSheet, s$)
on error goto exitErr:
getRangeByName = oSheet.getCellRangeByName(s)
exitErr:
' return empty
End Function
'_______________________________________________
Function setSimplePropertyValue(obj, sN$, v) as Boolean
Dim oProp, bOK as Boolean, sType$
on error goto exitErr:
oProp= obj.PropertysetInfo.getPropertyByName(sN)
sType = oProp.Type.getName()
select case sType
case "boolean"
obj.setPropertyValue(sN, cBool(v))
case "byte","short","long"
obj.setPropertyValue(sN, cLng(v))
case "float","double"
obj.setPropertyValue(sN, cDbl(v))
case is ="string"
obj.setPropertyValue(sN, cStr(v))
end select
bOK = True
exitErr:
setSimplePropertyValue = bOK
End Function
'_______________________________________________
Function getRealStyleName(oFamily, sLocal$) as String
on error goto exitErr:
Dim oE, s$
If oFamily.hasByName(sLocal) then
s = sLocal
else
oE = oFamily.createEnumeration()
do while oE.hasMoreElements
oStyle = oE.getNextElement()
s = oStyle.DisplayName
if s = sLocal then exit do
loop
endif
getRealStyleName = s
exitErr:
' return ""
End Function
'_______________________________________________
Function getSheetFromStringAddress(oDoc,strAddress)
Dim iPoint%,iCrit%,sResult$,oSh
iCrit = 1
if left(strAddress,1)= "$" then iCrit = 2
iPoint = instr(iCrit,strAddress,".")
if instr(strAddress,"'") then
' handle [$]'my sheet'.A1 and sheet-link 'file:///...'#Sheet
iCrit = iCrit + 1
if instr(strAddress, "'.") then iPoint = iPoint - 1
Endif
if iPoint > iCrit then
sResult = mid(strAddress,iCrit,iPoint - iCrit)
if oDoc.Sheets.hasByName(sResult) then oSh = oDoc.Sheets.getByName(sResult)
elseif oDoc.namedRanges.HasByName(strAddress) then
oSh = oDoc.namedRanges.getByName(strAddress).getReferredCells()
' referred cells are Null if the name refers to
' a constant, a formula or to an address without absolut $sheet
if not isNull(oSh) then oSh = oSh.getSpreadSheet
elseif oDoc.DatabaseRanges.HasByName(strAddress) then
oSh = oDoc.DatabaseRanges.getByName(strAddress).getReferredCells.getSpreadSheet()
End If
getSheetFromStringAddress = oSh
End Function
|
Report all simple properties, usable with above setRangePropertyByURL(sURL$)
| Code: |
Sub reportSimpleCellAndRangeProperties()
REM print real properties of a cell and a range into a newly created sheet
REM they are the same except for oCell.FormulaLocal
Dim oDoc,oSheet,obj,oCell,oRange,oProps(),i%,j%,iCol%,lRow&,oP,sType$
oDoc = StarDesktop.loadcomponentFromURL("private:factory/scalc","_default",0,Array())
'oDoc = thisComponent
oSheet = oDoc.Sheets.getByIndex(0)
oCell = oSheet.getCellByPosition(0,0)
oRange = oSheet.getCellRangeByPosition(0,0,1,1)
oSheet.getCellRangeByPosition(0,0,3,0).setDataArray(Array(Array("Cell","Type","Range","Type")))
For each obj in Array(oCell,oRange)
lRow = 1
oProps = obj.PropertySetInfo.getProperties()
for j = 0 to uBound(oProps())
oP = oProps(j)
sType = oP.Type.getName()
select case sType
case "boolean","byte","short","long","float","double","string"
oSheet.getCellRangeByPosition(iCol,lRow,iCol +1,lRow).setDataArray(Array(Array(oP.Name,sType)))
lRow = lRow +1
end select
next
iCol = iCol +2
next obj
End Sub
|
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org
Last edited by Villeroy on Sat Mar 10, 2007 1:20 pm; edited 1 time in total |
|
| Back to top |
|
 |
TerryE
Super User
Joined: 16 Jul 2006
Posts: 550
Location: UK
|
| Posted: Fri Mar 09, 2007 12:04 am Post subject: |
|
|
| A really interesting article and one which will take time to injest and comprehend. However, one thing that did very much strike that would help the reader here, Villeroy, is that is you referenced a complete working example, it would make this quite a lot easier for the reader. //Terry |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed Oct 01, 2008 9:48 am Post subject: |
|
|
| TerryE wrote: | | A really interesting article and one which will take time to injest and comprehend. However, one thing that did very much strike that would help the reader here, Villeroy, is that is you referenced a complete working example, it would make this quite a lot easier for the reader. //Terry |
18 months later:
Sorry, I forgot about this one until someone asked for it recently.
The document has a working example putting a random number into one of 24 time-slots.
Edit 2011-01-04: Debugged and moved that file to the new forum:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=37066&p=170467#p170467
The new file uses a simplified addressing scheme with the following URL properties:
Sheet [optional, default=ActiveSheet]
StartColumn
StartRow
EndColumn [optional, default=StartColumn]
EndRow [optional, default=StartRow]
with 1-based integers. The numbers translate to range objects more easy than "SheetY.A1:X99".
Property [name of property]
Value [value of property]
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
