[Solved] Creating formulas in spreadsheets programmatically.

[jkordani]

Can anyone give me any pointers about how to make up cell formulas programatically? It seems like formulas need the cell names in the form of a1 b2 etc, but it doesn't seem like there is a function that recalls this information for an individual cell, just cell ranges. I need to make formulas out of cells that are physically removed from each other, so making a cell range is not possible because it looks like cell ranges are rectangular in shape. The easiest way to make that happen is for me to have access to the name of the cell (a1, b2 etc) so I can build my formula on the fly. Can anyone give me any pointers?

[jkordani]

Bump due to edit

[h1h]

what do you want to do with these individual cells or rather their values?

[jkordani]

At the very least, I want to be able to sum up chosen cells, and also do some multiplication with the result (total and tax scheme). I know how to make calc functions by hand using calc, and I noticed that the api allows for some operations to be performed on cell ranges, but I want to leave the user with a spreadsheet that they can tweak before they print it, and if they have to change values I want to retain the functionality that in-sheet functions provide, which is that any changes get propagated through to the formula. every time the user runs my macro, new cells, or perhaps just an extended range of cells , will be in the formula, so it makes sense for me to have to formula be crafted by the function as it spits out data, (oh this is the end of the column this time? <make end bound in formula>). So what would be useful for me would be to be able to get the cell name of the appropriate cell. (oh, this is where we iterated to? ok, whats it name? a5. Ok, our formula will sum a2 to a5.) I need the "whats its name" part. Or at the very least (is this a cell we want to have be in our formula? Add to dataset. Oh we're done? make a formula with all the addresses in the dataset.) Something tells me that this is one of those situations that will be resloved with one line of code and i just don't know what that command is, and its buggin me. Unless I've got the wrong idea on how to do this.

[h1h]

sorry to say so, but you absolutely lost me.

[jkordani]

I use a loop to fill cells with data from a database. Different columns are output depending on the users choice in a form. Some of the columns need to have functions run on them, and I want to have those functions be in the calc document. Calc formulas need to have the cell names, a1 b2. In the loop I have branching to determine with column gets which function. The problem is is that im using numbers to iterate through the cells, so unless I make a formula mapping column numbers to alphabet, I have no way of knowing what the cell name is. There is no function (apparent to me) that allows me to get the name of the cell. I can get its position, cell.getaddress or whatever, that allows access to its row and column and sheet in numerical form, but no cell.getname or whatever that returns a1, b2, etc. So how am I supposed to make formulas on the fly unless I already know which cells will contain the data i want in the formula, which I don't until I iterate too them, and then do a dumb conversion from column number to column letter. (which I might just break down and do at this point, but it doesn't seem like the right way to do things.) Does that make sense?

[pitonyak]

Setting a formula in a cell from a macro is easy, you just need to be able to turn the row and column index into a named cell reference. Converting the ROW is easy, just add one to it and convert it to a string. The column name, however, is more difficult.

Code: Select all

' Columns are numbered starting at 0 where 0 corresponds to A
' They run as A-Z,AA-AZ,BA-BZ,...,IV
' This is esentially a question of how do you convert a Base 10 number to
' a base 26 number. 
' Note that the_column is passed by value!
Function ColumnNumberToString(ByVal the_column As Long) As String
  Dim s$
  'Save this so I do NOT modify the parameter.
  'This was an icky bug that took me a while to find
  Do while the_column >= 0
    s$ = Chr(65 +  the_column MOD 26) + s$
    the_column =  the_column \ 26 - 1
  Loop 
  ColumnNumberToString = s$
End Function

[jkordani]

I was a comp sci major, youd think i would have figured that out. The part I got stuck on was figuring out how to convert the math to the column letter, but I understand how you did it. I can work this code into my program just fine, so i guess you can consider this topic solved. I just figured there was some magical already there command that would spit out the name. I've learned much here. Thanks very much!

[pitonyak]

You can always do things such as:

Given a row and column
Use get by to get the cell
Get the cell name.

Unfortunately, that would be very slow.

Asking is not a bad thing. Sometimes, there is a utility method that just does it for you. For example, in Writer, there is a method to move content around without using the clipboard. It is new and few people know about it. There is usually more than one solution.

[jkordani]

I guess if all you wanted to do was get the cell name, that would be a long winded solution. But Im iterating through rows and columns to fill data, it just so happens that certain ones I want to be in a formula, so in my case im doing those first two steps for every cell I go to, so asking for the name wouldn't be too much more overhead I don't think, for me in my case, unless there's something Im missing. So now my question becomes, can you indeed do what you describe? and if so, what is that function call. because its not getcellbyname().

[pitonyak]

But Im iterating through rows and columns to fill data

One cell at a time is very inefficient. You can get and set data for large blocks all at one time. Look at my free macro document discussing how to search Calc cells. I include timing information, and the code for large blocks at a time is way faster than one cell at a time. Of course, it is difficult to mix types, as in values and formulas.

So now my question becomes, can you indeed do what you describe? and if so, what is that function call. because its not getcellbyname().

Not certain that I followed all of that, but... Grab a copy of AndrewMacro.odt

Read the section titled "Human readable address of cell"

Code: Select all

  oActiveCell = ThisComponent.getCurrentSelection()
  oConv = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
  oConv.Address = oActiveCell.getCellAddress
  Print oConv.UserInterfaceRepresentation
  Print oConv.PersistentRepresentation

You can call "getCellByPosition(col, row)". You can also use getCellRangeByName("A1:L16"). The cell range can consist of a single cell, of course.

[TerryE]

If you have a replicating formula, that is you've set up the absolute and relative addressing so that you can replicate it by doing a copy then paste to a range then you can do the same programmatically in Basic.

Whilst this is easy in Excel (if you have mastered the trick of RC notation. Even if you haven't you just type the formula into the first field, toggle into the debugger immediate pane (yup, the VBA IDE has one of those) and type ?ActiveCell.FormulaR1C1 and up it comes to paste into your code. So then you do something like

Code: Select all

'***** VBA EXCEL Version ****
nRows = vbaSheet.Cells.SpecialCells(xlCellTypeLastCell).Row   
vbaSheet.Range("D2").Resize(nRows-1).FormulaR1C1 = _
    "=R[-4]C[-3]+R[-3]C[-3]+R[-2]C[-3]"

I know this looks yukky, but you don't have to compose it this form. The real advantage of R1C1 notation in that it is cell relative so the formula is exactly the same for the whole row.

However, in OOo we might have free and portability but we don't have RC notation yet so so we have to apply the formula to the first cell, and then we can then fill the entire range. Note that the Calc Fill function does automatically relocate formulae.

If you are doing large ranges then it is well worth having enableAutomaticCalculation turned off and do a manual calculate. Sometimes the first thing that you want to do after doing this is to anchor the fields by replacing the formula with the values. I realise that if you are going to do this then you could just as easily do this with a programmatic For ... Next loop, but my response to this is Time it!. Using the Calc calculation engine is faster and is easier to code.[/list]
So here is the OOo Basic Code

Code: Select all

***** OOB UNO Calc Version ****
PastFormulaToColumn(sFormula, oSheet, nColumn, nStartRow, nEndRow, bPasteValues)

That's even easier than VBA! Well apart from this canned routine below that you will need to put in your code or library.

Code: Select all

***** OOB UNO Calc Version ****
Function PastFormulaToColumn(sFormula, oSheet, nColumn, nStartRow, _
                        Optional ByVal nEndRow, Optional ByVal bPasteValues)
'
' Routine to Paste Array into a specified column on a worksheet in thisComponent 
'
Dim oCursor, oRange, vData

On Error Goto PasteHandler

' Apply default values for last two arguements 

If IsMissing(nEndRow) Then nEndRow = -1
If IsMissing(bPasteValues) Then bPasteValues = False

' If nEndRow is negative then -1 denote last row, -2 last but one etc. 
If nEndRow < 0 Then
   oCursor = oSheet.CreateCursorByRange(oSheet.getCellByPosition(0, 0))
   oCursor.GotoEndOfUsedArea(False)
   nEndRow = oCursor.RangeAddress.endRow + 1 + nEndRow
End If   

oSheet.getCellByPosition(nColumn, 1).Formula = sFormula
oRange = oSheet.getCellRangeByPosition(nColumn, nStartRow, _
                                       nColumn, nEndRow)
oRange.fillSeries(com.sun.star.sheet.FillDirection.TO_BOTTOM, _
      			  com.sun.star.sheet.FillMode.SIMPLE,0,0,0)

ThisComponent.calculate()

If bPasteValues Then
   vData = oRange.DataArray   ' The intermediate variant is needed 
   oRange.DataArray = vData   ' Otherwise this is a No-Op !!
End If

PastFormulaToColumn = True

Exit Function

PasteHandler:
   PastFormulaToColumn = False
End Function

Hope that you fnd this is useful. //Terry

[jkordani]

Ok I think my head just exploded.... Let me stare at this for awhile and see what I can garner.