Category Archives: oo Calc

OpenOffice calc

OpenOffice Calc macro: sort a cell range

sortRange(destSheet, 0, y1, 25, J-1)

…

Sub sortRange(oSheet as Object, x1 as Integer, y1 as Integer, x2 as Integer, y2 as Integer)
 Dim aSortFields2(2) as Object 
 Dim aSortDesc(0) as New com.sun.star.beans.PropertyValue 
 For i =0 to 2 
 aSortFields2(i)= New com.sun.star.util.SortField 
 Next i

oRange = oSheet.getCellRangeByPosition(x1, y1, x2, y2) 'range B1:F7

aSortFields2(0).Field = 6 
 aSortFields2(0).SortAscending = True 'sort ascending by column G
 aSortFields2(1).Field = 11 
 aSortFields2(1).SortAscending = True 'then sort ascending by column L
 aSortFields2(2).Field = 12 
 aSortFields2(2).SortAscending = True 'then sort ascending by column M
 aSortDesc(0).Name = "SortFields" 
 aSortDesc(0).Value = aSortFields2() 
 oRange.Sort(aSortDesc()) 
End Sub

Reference:

How to sort a cellrange in Calc using Basic

OpenOffice Calc macro: loading multiple CSV into calc

sub importPayPal
    GlobalScope.BasicLibraries.loadLibrary("Tools")
    basePath = "file:///Volumes/HDA/Users/skwong/Documents/2013/09/130908b/workplaceCSV/" 
    ' Create new workbook
    oDoc = StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array() ) 

  rem loading first document 

  Dim NextFile As String

  NextFile = Dir(basePath + "*", 0)

    While NextFile  <> ""
	FileN=GetFileNameWithoutExtension(NextFile,"/")
	MyResult = Right(FileN, 1)
	if (MyResult = "a") then
	    ImportCSVIntoTable(oDoc, "saikee", basePath + NextFile)
	endif
    NextFile = Dir
    Wend
    oDoc.getSheets().removeByName( "Sheet1" )
end sub

Sub ImportCSVIntoTable(oDoc as object, tableName as String, fileName as String)
Rem http://www.oooforum.org/forum/viewtopic.phtml?p=365860
	Dim oSheet as Object 
	Dim oPlan as Object 

   oSheet = oDoc.createInstance ( "com.sun.star.sheet.Spreadsheet" ) 
   oDoc.Sheets.insertByName ( tableName, oSheet ) 
   oPlan = oDoc.Sheets.getByName(tableName) 

      sURL = ConvertToURL ( fileName ) 
      sOrigin = "" 
      sFilter = "Text - txt - csv (StarCalc)"
'	See https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options for the options
'	44 = use comma as delimiter
'	34 = use " as Text delimiter
      sOpc = "44,34,0,1,1/2/2/1/3/2" 
'	following use "|" as delimiter - ("|" = 124) 
'      sOpc = "124,,0,1,1/2/2/1/3/2" 
      nModo = com.sun.star.sheet.SheetLinkMode.NORMAL 
      ' link file 
      oPlan.link(sURL, sOrigin, sFilter, sOpc, nModo) 
      ' reset link 
      oPlan.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE) 
End Sub

Another example:

sub import1csv(oDoc as object, oIndex as integer, fileName as string)
Dim Doc as Object
Dim Url As String
Dim FileProperties(1) As New com.sun.star.beans.PropertyValue

FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="scalc: Text - txt - csv (StarOffice Calc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).Value ="59,34,33,8,"
rem kodovanie 33 je latin2 - win-1250

Doc = StarDesktop.loadComponentFromURL(fileName, "_blank", 0, FileProperties())
end sub

Reference:

 OpenOffice Calc macro: loading multiple CSV into calc

OpenOffice Calc macro: set column width

sub DailyProfit
rem ------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

 oCalcDoc = ThisComponent 
 oSheets = oCalcDoc.getSheets()
 oSheets.removeByName( "DProfit" )
 SetColWidth( 1, 2.5, oSheet )
 SetColWidth( 2, .5, oSheet )
end sub
sub SetColWidth( col as integer, nchs as single, oSheet as object )
 dim w as single
 w = nchs * 2540
 ' get column
 oColumn = oSheet.getColumns.getByIndex( col )
 ' column width (in 100ths of mm)
 oColumn.Width = w
end sub

Reference:

 

OpenOffice Calc macro: Find last column with data

Sub ReplaceInUsedRange
   oDocument = ThisComponent
   oSheets = oDocument.Sheets
   oSheet = oSheets.getByIndex(0)
   oCellCursor = oSheet.createCursor()

   oCellCursor.gotoStartOfUsedArea( False )
   nFirstRow = oCellCursor.getRangeAddress().StartRow
   nFirstCol = oCellCursor.getRangeAddress().StartColumn

   oCellCursor.gotoEndOfUsedArea( False )
   nLastRow = oCellCursor.getRangeAddress().EndRow
   nLastCol = oCellCursor.getRangeAddress().EndColumn

   oRange = oSheet.getCellRangeByPosition(nFirstCol,nFirstRow,nLastCol,nLastRow)
   vDescriptor = oRange.createReplaceDescriptor()
   With vDescriptor
      .SearchString = "SearchString"
      .ReplaceString = "ReplaceString"
      .SearchWords = False
      .SearchCaseSensitive = False
   End With
   nReplaced = oRange.replaceAll( vDescriptor )
   MsgBox "Replaced " & nReplaced & " times."
End Sub

Reference:

 

OpenOffice Calc macro: Modifying Chart RangeAddress

dim graphSheet, graph, graphs As Object 
dim range As New com.sun.star.table.CellRangeAddress 

graphSheet = doc.getSheets().getByName("Graph") 
graphs = graphSheet.Charts 
'graph = graphs.getByIndex(0).EmbeddedObject 
' don't use getEmbeddedObject !! 
   oGraf = oGrafs.getByName("The_Name_Of_Graph") 
   ' define a extension 
   mRange(0).EndRow = 3 
   ' altera a extension atual 
   oGraf.setRanges ( mRange ( ) )
    
With range 
   .Sheet = 1 
   .StartColumn = 0 
   .StartRow = 0 
   .EndColumn = numMonths + 1 
   .EndRow = which + 1    
End With 
    
graph.Range = range

Reference:

Modifying Chart RangeAddress

OpenOffice Calc macro: check if a sheet exists

   oCalcDoc = ThisComponent 
   oSheets = oCalcDoc.getSheets() 

   If oSheets.hasByName( "Sheet2" ) Then 
      MsgBox( "This spreadsheet DOES have a sheet named Sheet2." ) 
   EndIf 

   If oSheets.hasByName( "MeowMix" ) Then 
      MsgBox( "This spreadsheet DOES have a sheet named MeowMix." ) 
   EndIf 

   oSheet = oSheets.getByName( "Sheet2" ) 
   ' Change cell B2 
   oSheet.getCellByPosition( 1, 1 ).setFormula( "July" ) 
   ' Change cell C2 
   oSheet.getCellByPosition( 2, 1 ).setValue( 2856 )

Reference:

check if sheet exists

OpenOffice Calc macro: Save

Sub Main 

   ' Prepare the filename to save. 
   cFile = "C:\Documents and Settings\dbrewer\Desktop\MyCalc" ' Windows 
'   cFile = "/home/danny/Desktop/MyCalc.sxc" ' Linux 

   ' Now save the spreadsheet. 
   cURL = ConvertToURL( cFile + ".sxc" ) 
   oDoc.storeAsURL( cURL, Array() ) 

   ' Note the above used storAsUrl, the following use storeToUrl. 

   ' Now save it in Excel format. 

   cURL = ConvertToURL( cFile + ".xls" ) 
   oDoc.storeToURL( cURL, Array( MakePropertyValue( "FilterName", "MS Excel 97" ) ) ) 

   ' Now save a PDF. 
   cURL = ConvertToURL( cFile + ".pdf" ) 
   oDoc.storeToURL( cURL, Array( MakePropertyValue( "FilterName", "calc_pdf_Export" ) ) ) 

   ' Now save as HTML. 
   cURL = ConvertToURL( cFile + ".html" ) 
   oDoc.storeToURL( cURL, Array( MakePropertyValue( "FilterName", "HTML (StarCalc)" ) ) )

 End Sub

'---------- 
'   Create and return a new com.sun.star.beans.PropertyValue. 
' 
Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue 
   Dim oPropertyValue As New com.sun.star.beans.PropertyValue 
   If Not IsMissing( cName ) Then 
      oPropertyValue.Name = cName 
   EndIf 
   If Not IsMissing( uValue ) Then 
      oPropertyValue.Value = uValue 
   EndIf 
   MakePropertyValue() = oPropertyValue 
End Function

Reference:

Question: How would I add or delete Sheets using Macro…