[Solved] Report (Print) Cell Formulas

[Tony_a20]

I would like to print out cell formulas in a report like format that gives each Cell row/col coordinate and the formula content. I am using OO 2.2 with XP Pro Media Edition. I have read everything I can find in Help, and tried all the possible things available from the pull down menus, and I have searched this forum.
I know I can set the view options to show formulas in the cell space, but this requires changing the column width, which on a wide spreadsheet splits the page view when printed.
I have also tried to copy/paste the formulas into a text document, but either the copy function on the context menu won't copy, or values rather than formulas are shown when pasted.
I am at the point of frustration where I am willing to copy each individual formula by hand on to paper, and thence to a text document. I'm sure that will be quicker and easier than the time and effort I've already expended.
I would like something simple such as:

A1: =IF(G5>0;G5+1;0) A2: =A1+3 A3: =Sum(A1:A2)

formated in columns and rows to fit a pre-defined page.

Does anyone have any ideas? Any help would be appreciated.

Tony_A20

[uros]

Hi Tony!
Try this macro:

Code: Select all

Sub FormulaControl
	Dim aSortFields(1) as New com.sun.star.util.SortField
	Dim aSortDesc(0) as New com.sun.star.beans.PropertyValue

	oDesktop = createUnoService("com.sun.star.frame.Desktop")
	oDocument = ThisComponent										' za potrebe debugginga
	If Not bSkok Then
		sUrl = oDocument.getURL
		If oDocument.IsReadOnly(sUrl) Then
			MsgBox "Document is opened as read-only !!!", 16
			Stop
		End If
	End If
	oSelectedCells = oDocument.CurrentSelection
	If HasUnoInterfaces( oSelectedCells, "com.sun.star.lang.XServiceInfo" ) Then
		If oSelectedCells.supportsService( "com.sun.star.sheet.SheetCellRanges" ) Then
			MsgBox "There is more then one non-contiguous ranges selected. Select one cell only!", 16
			Stop
		End if
		If oSelectedCells.supportsService( "com.sun.star.drawing.Shapes" ) Then
			MsgBox "There is a chart selected. Select one cell only!", 16
			Stop
		End if
	End If
	oActiveCell = oSelectedCells.RangeAddress
	oSheets = oDocument.Sheets
	oSheet = oSheets.getByIndex(oActiveCell.Sheet)						' aktivna tabela
	sName = osheet.Name
	
	bExist = False
	For i = 0 To oSheets.Count - 1
		If oSheets(i).Name = "FormulaControlSheet" Then
			If MsgBox("FormulaControlSheet already exists! Can data be erased?",1) = 1 Then
				bExist = True
				oSheetKont = oSheets(i)
				oCellCursor = oSheetKont.createCursor()
				oCellCursor.gotoEndOfUsedArea( False )
				nLastRow = oCellCursor.getRangeAddress().EndRow
				nLastCol = oCellCursor.getRangeAddress().EndColumn
				oRange = oSheetKont.getCellRangeByPosition(0,0,nLastCol,nLastRow)
				oRange.ClearContents(5)
				Exit For
			Else
				Stop
			End If
		End If
	Next i
	If Not bExist Then
		oSheets.insertNewByName("FormulaControlSheet",0)
		oSheetKont = oSheets.getByName("FormulaControlSheet")
	End If
	oDocument.getCurrentController.select(oSheetKont.getCellByPosition(0,0))
	oSheetKont.getCellByPosition(1,0).String = "Sheet: "& sName
	
	oRange = oDocument.createInstance("com.sun.star.sheet.SheetCellRanges")
	oRange.insertByName("", oSheet)
	oCells = oRange.Cells.createEnumeration
	i = 1
	While oCells.hasMoreElements
		oCell = oCells.nextElement
		If oCell.Formula <> oCell.String And oCell.Formula <> oCell.Value Then
			row = oCell.CellAddress.Row() + 1
			col = oCell.Columns.ElementNames(0)
			form = oCell.Formula
			tekst = oCell.String
'			If Left(form,6)="='file" Then 'for links only
				oSheetKont.getCellByPosition(0,i).String = col & row
				oSheetKont.getCellByPosition(1,i).String = form
'				oSheetKont.getCellByPosition(2,i).Value = Len(form)
				i = i + 1
'			End If
		End if
	Wend
	For i = 0 To 2
		oColumn = oSheetKont.Columns.getByIndex(i)
		oColumn.OptimalWidth = True
	Next i

	oCellCursor = oSheetKont.createCursor()
	oCellCursor.gotoEndOfUsedArea( False )
	nLastRow = oCellCursor.getRangeAddress().EndRow
	nLastCol = oCellCursor.getRangeAddress().EndColumn
	If nLastRow >= 1 Then
		oRange = oSheetKont.getCellRangeByPosition(0,1,nLastCol,nLastRow)
		aSortFields(0).Field = 1
		aSortFields(0).SortAscending = True
'		aSortFields(0).Field = 3
'		aSortFields(0).SortAscending = False
'		aSortFields(1).Field = 2
'		aSortFields(1).SortAscending = True
		aSortDesc(0).Name = "SortFields"
		aSortDesc(0).Value = aSortFields()
		oRange.Sort(aSortDesc())
		MsgBox "End of formula listing!"
	Else
		MsgBox "There is no formula on sheet " & sName
	End If
End Sub

Uros

[Tony_a20]

Hello uros,

Thanks for your reply, but playing around with macros is a little beyond me especially for a one time problem. I was hoping someone would know how to print out formatted cell contents using some existing procedure I wasn't aware of. Perhaps something will be included in a future release of OO Calc.

I do appreciate the time you obviously spent on my problem, but the easiest thing for me to do is just copy out the formulas by hand.

Thanks again,
Tony

[Villeroy]

Function FORMULA might help. =FORMULA(A1) shows the formula of cell A1 if any.

[uros]

Function FORMULA might help. =FORMULA(A1) shows the formula of cell A1 if any.

This is realy new for me!

[Villeroy]

Function FORMULA might help. =FORMULA(A1) shows the formula of cell A1 if any.

This is realy new for me!

It's one of those functions missing in other spreadsheet programs. So this won't work with Excel nor Gnumeric.

[Tony_a20]

Thanks for your replies fellas,
the =Formula(A1) might be something I can work with, by effectively setting up a shadow spreadsheet to mirror the real spreadsheet with the =Formula in each cell, then I can just print out the shadow cells. I'll give it a try.

Thanks,
Tony

[uros]

Hi Tony!
A small enhancement, just to nicer look of the control sheet. Use

• =IF(ISNA(FORMULA(A1));"";FORMULA(A1))

Function FORMULA(A1) returns "#N/A" if there is no formula in cell A1. Use IF function to show blank instead and make your control sheet more readable.
Uros

[Tony_a20]

Thanks to Villeroy for the solution to my problem. While not a report, this solution is better. For the benefit of anyone else with this problem this is what I did.

Problem: Display and print formulas in an OOCalc spreadsheet.

Solution: This might be awkward for something large, but it was ideal for the small 10x20 cell spreadsheet I wished to document.
Step 1. - Open a new text page in OOWriter and format it as desired. A horizontal format is best.
Step 2. - Open OOCalc spreadsheet.
Step 3. - Under Tools>Options>Calc>View check Display "Formulas," and uncheck "Zero Values."
Step 4. - Select entire original spreadsheet, and copy to an empty area directly below the original spreadsheet. This is the mirror SS.
Step 5. - Select all column headers for the spreadsheet and set column widths to "Optimal Column Width." All formulas should now be completely visible in both spreadsheets.
Step 6. - In the mirror SS, delete contents of all cells showing formulas. Keep formats.
Step 7. - In the mirror SS, in all the cells that had formulas, enter "=Formula ()," with the brackets containing the cell reference of the original SS. For example "=Formula (A1)" in the mirror SS cell to reference cell A1 in the original SS.
Step 8. - Select the whole mirror SS, or cells of interest, and copy and paste them into the OOWriter text page. During the copy/paste operation, the cells selected will be transformed into an image containing the result of the formulas or functions displayed. In OOWriter, the copied cells can be manipulated as a normal imported image on the text page.

I found the copy/paste function very useful to copy Calc spreadsheet data, including the whole spreadsheet, into a Writer text document. Very smooth. My hat is off to the programmer(s) of this feature. The only thing to keep in mind is that what is pasted is the resultant of any formula or function in a cell, not what is displayed in the copied cell.

I hope this helps someone else.

Thanks again,

Tony