Cell content inside formula

问题:

Is it possible to place the content of a cell inside a formula. By formula I mean the math formula editor (insert->object->formula).

回答1:

To the best of my knowledge, there is no way to reference a cell from a formula. Math formula editor has no knowledge about OO Calc. However, you can create a new formula whenever needed using macros.

Follow thesse steps to make it work:

1. Put the math formula you want to insert to a cell. For example, put some numbers to cells A1, A2, A3 and put the following to cell C3:

   =CONCATENATE("{";A1;"}";"over {";A2;" `+` ";A3;"}";" `=` ";A4).
   

   This will generate something like {1} over {2 `+` 3} `= in C3

2. Create a macro from the code below. In OO Calc, select

   Tools > Macros > Organize Macros > OpenOffice.org Basic > My Macros > Standard
   

   Create a new macro and paste the code below.

3. Now you can run macro using Tools > Macros > Run Macro. Run either insertFormula which inserts math formula generated from cell C3, or addFormulaListener which will register a listener and regenerate the formula for you whenever contents of C3 changes.

Here is the code. It contains constants formulaCellFrom and formulaCellTo, which specify which cell has the math formula source and which is the target cell where the generated formula object shall be placed. Note that the target cell must be large enough for the generated formula, otherwise the macro won't delete cell's old content when regenerating the formula.

const formulaCellFrom As String = "$C$1"
const formulaCellTo As String = "$C$10"

rem ----------------------------------------------------------------------
rem Adds listener for changes of the math formula
sub addFormulaListener
dim oSheet as Object
dim oCell as Object
rem go to cell containing markup 
oSheet =  ThisComponent.CurrentController.ActiveSheet
oCell = oSheet.getCellRangeByName(formulaCellFrom)
rem add listener
oListener = CreateUnoListener( "formulaListener_", "com.sun.star.chart.XChartDataChangeEventListener" )
oCell.addChartDataChangeEventListener(oListener)
end sub

rem ----------------------------------------------------------------------
rem Listener for cell changes
sub formulaListener_chartDataChanged
dim oCell as Object

rem remember current cursor position
oCell = ThisComponent.CurrentSelection

rem call insertFormula
call insertFormula

rem restore cursor position
ThisComponent.CurrentController.select(oCell)
end sub 

rem ----------------------------------------------------------------------
rem Creates a math formula from text in cell C1 and inserts it into cell C10
sub insertFormula

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")

rem go to cell containing markup and copy it
dim fromCellArgs(0) as new com.sun.star.beans.PropertyValue
fromCellArgs(0).Name = "ToPoint"
fromCellArgs(0).Value = formulaCellFrom
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, fromCellArgs())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem go to cell where I want the formula displayed
dim toCellArgs(0) as new com.sun.star.beans.PropertyValue
toCellArgs(0).Name = "ToPoint"
toCellArgs(0).Value = formulaCellTo
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, toCellArgs())

rem delete previous content
dim deleteArgs(0) as new com.sun.star.beans.PropertyValue
deleteArgs(0).Name = "Flags"
rem flags: A = All, S = String, V = Value, D = DateTeim, F = Formula, ...
rem ... N = Notes, T = Formats, O = Objects
deleteArgs(0).Value = "AO" 
dispatcher.executeDispatch(document, ".uno:Delete", "", 0, deleteArgs())

rem open Star.Math
oDesk = createUnoService ("com.sun.star.frame.Desktop")
dispatcher.executeDispatch(document, ".uno:InsertObjectStarMath", "", 0, Array())

rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem paste clipboard using Array() as place-holder for variable name
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

rem exit Star.Math
dispatcher.executeDispatch(document, ".uno:TerminateInplaceActivation", "", 0, Array())
end sub

The code was adapted from this question. Apparently, the macro must be created in My Macros and doesn't work when embedded in the spreadsheet (security measure? it just didn't work for me). The source and target cells are hardcoded but you can modify the macro to suit your needs. I'm not skilled in Visual Basic but such modifications should be easy.