Python-Calc

From Apache OpenOffice Wiki
Jump to: navigation, search

This code was originally written on the now deprecated oooforum, you can reach it here. The code was somewhat broken down into different areas so the reader can understand each step.

The intend of the code is to:

  1. Create a new spreadsheet
  2. Open an existing spreadsheet
  3. Insert strings into a cell
  4. Insert formulas into a cell
  5. Insert graphs to a range
from Danny.OOo.OOoLib import * 
 
def CalcExample(): 
    # create a new Calc spreadsheet. 
    oDoc = StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array() ) 
    #----- 
    # Use this instead to open an EXISTING calc document, 
    #  and assign it to variable oDoc. 
    #  cFile = "C:\Documents and Settings\danny\Desktop\MyCalc" # Windows 
    #  cFile = "/home/danny/Desktop/MyCalc.sxc" # Linux 
    #  cURL = convertToURL( cFile + ".sxc" ) 
    #  oDoc = StarDesktop.loadComponentFromURL( cURL, "_blank", 0, Array() ) 
    #----- 
 
    # Here are two ways to get access to one of the various sheets 
    #  in the spreadsheet document. 
    # Note that these don't make the sheet *vislble*, they merely give 
    #  access to the sheet's content within the program. 
    oSheet = oDoc.getSheets().getByIndex( 0 ) # get the zero'th sheet 
    #oSheet = oDoc.getSheets().getByName( "Sheet3" ) # get by name 
 
    #----- 
    # Put some sales figures onto the sheet. 
    oSheet.getCellByPosition( 0, 0 ).setString( "Month" ) 
    oSheet.getCellByPosition( 1, 0 ).setString( "Sales" ) 
    oSheet.getCellByPosition( 2, 0 ).setString( "End Date" ) 
 
    oSheet.getCellByPosition( 0, 1 ).setString( "Jan" ) 
    oSheet.getCellByPosition( 0, 2 ).setString( "Feb" ) 
    oSheet.getCellByPosition( 0, 3 ).setString( "Mar" ) 
    oSheet.getCellByPosition( 0, 4 ).setString( "Apr" ) 
    oSheet.getCellByPosition( 0, 5 ).setString( "May" ) 
    oSheet.getCellByPosition( 0, 6 ).setString( "Jun" ) 
    oSheet.getCellByPosition( 0, 7 ).setString( "Jul" ) 
    oSheet.getCellByPosition( 0, 8 ).setString( "Aug" ) 
    oSheet.getCellByPosition( 0, 9 ).setString( "Sep" ) 
    oSheet.getCellByPosition( 0, 10 ).setString( "Oct" ) 
    oSheet.getCellByPosition( 0, 11 ).setString( "Nov" ) 
    oSheet.getCellByPosition( 0, 12 ).setString( "Dec" ) 
 
    oSheet.getCellByPosition( 1, 1 ).setValue( 3826.37 ) 
    oSheet.getCellByPosition( 1, 2 ).setValue( 3504.21 ) 
    oSheet.getCellByPosition( 1, 3 ).setValue( 2961.45 ) 
    oSheet.getCellByPosition( 1, 4 ).setValue( 2504.12 ) 
    oSheet.getCellByPosition( 1, 5 ).setValue( 2713.98 ) 
    oSheet.getCellByPosition( 1, 6 ).setValue( 2248.17 ) 
    oSheet.getCellByPosition( 1, 7 ).setValue( 1802.13 ) 
    oSheet.getCellByPosition( 1, 8 ).setValue( 2003.22 ) 
    oSheet.getCellByPosition( 1, 9 ).setValue( 1502.54 ) 
    oSheet.getCellByPosition( 1, 10 ).setValue( 1207.68 ) 
    oSheet.getCellByPosition( 1, 11 ).setValue( 1319.71 ) 
    oSheet.getCellByPosition( 1, 12 ).setValue( 786.03 ) 
 
    oSheet.getCellByPosition( 2, 1 ).setFormula( "=DATE(2004;01;31)" ) 
    oSheet.getCellByPosition( 2, 2 ).setFormula( "=DATE(2004;02;29)" ) 
    oSheet.getCellByPosition( 2, 3 ).setFormula( "=DATE(2004;03;31)" ) 
    oSheet.getCellByPosition( 2, 4 ).setFormula( "=DATE(2004;04;30)" ) 
    oSheet.getCellByPosition( 2, 5 ).setFormula( "=DATE(2004;05;31)" ) 
    oSheet.getCellByPosition( 2, 6 ).setFormula( "=DATE(2004;06;30)" ) 
    oSheet.getCellByPosition( 2, 7 ).setFormula( "=DATE(2004;07;31)" ) 
    oSheet.getCellByPosition( 2, 8 ).setFormula( "=DATE(2004;08;31)" ) 
    oSheet.getCellByPosition( 2, 9 ).setFormula( "=DATE(2004;09;30)" ) 
    # Note that these last three dates are not set as DATE() function calls. 
    oSheet.getCellByPosition( 2, 10 ).setFormula( "10/31/2004" ) 
    oSheet.getCellByPosition( 2, 11 ).setFormula( "11/30/2004" ) 
    oSheet.getCellRangeByName( "C13" ).setFormula( "12/31/2004" ) 
    #----- 
 
    #----- 
    # Format the date cells as dates. 
    com_sun_star_util_NumberFormat_DATE = uno.getConstantByName( "com.sun.star.util.NumberFormat.DATE" ) 
    oFormats = oDoc.getNumberFormats() 
    oLocale = createUnoStruct( "com.sun.star.lang.Locale" ) 
    nDateKey = oFormats.getStandardFormat( com_sun_star_util_NumberFormat_DATE, oLocale ) 
    oCell = oSheet.getCellRangeByName( "C2:C13" ) 
    oCell.NumberFormat = nDateKey 
    #----- 
 
    #----- 
    # Now add a chart to the spreadsheet. 
 
    oCellRangeAddress = oSheet.getCellRangeByName( "A1:B13" ).getRangeAddress() 
    # oCellRangeAddress = MakeCellRangeAddress( 0, 0, 1, 1, 12 ) 
    # Get the collection of charts from the sheet. 
    oCharts = oSheet.getCharts() 
    # Add a new chart with a specific name, 
    #  in a specific rectangle on the drawing page, 
    #  and connected to specific cells of the spreadsheet. 
    oCharts.addNewByName( "Sales", 
               makeRectangle( 8000, 1000, 16000, 10000 ), 
               Array( oCellRangeAddress ), 
               True, True ) 
    # From the collection of charts, get the new chart we just created. 
    oChart = oCharts.getByName( "Sales" ) 
    # Get the chart document model. 
    oChartDoc = oChart.getEmbeddedObject() 
 
    # Get the drawing text shape of the title of the chart. 
    oTitleTextShape = oChartDoc.getTitle() 
    # Change the title. 
    oTitleTextShape.String = "Sales Chart" 
 
    # Create a diagram. 
    oDiagram = oChartDoc.createInstance( "com.sun.star.chart.BarDiagram" ) 
    # Set its parameters. 
    oDiagram.Vertical = True 
    # Make the chart use this diagram. 
    oChartDoc.setDiagram( oDiagram ) 
 
    # Ask the chart what diagram it is using. 
    # (Unnecessary, since variable oDiagram already contains this value.) 
    oDiagram = oChartDoc.getDiagram() 
    # Make more changes to the diagram. 
    oDiagram.DataCaption = uno.getConstantByName( "com.sun.star.chart.ChartDataCaption.VALUE" ) 
    oDiagram.DataRowSource = uno.getConstantByName( "com.sun.star.chart.ChartDataRowSource.COLUMNS" ) 
    # 
    #----- 
 
 
    #----- 
    # Now demonstrate how to manipulate the sheets. 
 
    # Insert six more sheets into the document. 
    nNumSheetsCurrently = oDoc.getSheets().getCount() 
    oDoc.getSheets().insertNewByName( "Fred", nNumSheetsCurrently+1 ) 
    oDoc.getSheets().insertNewByName( "Joe", nNumSheetsCurrently+2 ) 
    oDoc.getSheets().insertNewByName( "Bill", nNumSheetsCurrently+3 ) 
    oDoc.getSheets().insertNewByName( "Sam", nNumSheetsCurrently+4 ) 
    oDoc.getSheets().insertNewByName( "Tom", nNumSheetsCurrently+5 ) 
    oDoc.getSheets().insertNewByName( "David", nNumSheetsCurrently+6 ) 
    # Now find a sheet named "Sheet2" and get rid of it. 
    oDoc.getSheets().removeByName( "Sheet2" ) 
    # Now find the sheet named "Sam" and change its name to "Sheet 37" 
    oDoc.getSheets().getByName( "Sam" ).Name = "Sheet 37" 
    # 
    #----- 
 
    #------- 
    # Now print the document -- three different ways. 
 
    # Technique 1. 
    # Now print the document. 
    # Print two copies. 
    # Print pages 1 thru 4, and also page 10. 
    # 
    # NOTE: we would do it like this, except the word "print" 
    #       has a special meaning in python, and cannot be invoked 
    #       as a method. 
    #oDoc.print( 
    #    Array( 
    #        makePropertyValue( "CopyCount", 2 ), 
    #        makePropertyValue( "Pages", "1-4;10" ) ) ) 
    uno.invoke( oDoc, "print", ( Array( 
            makePropertyValue( "CopyCount", 2 ), 
            makePropertyValue( "Pages", "1-4;10" ) ), ) ) 
 
    # Technique 2. 
    # Print the document already, without any arguments. 
    uno.invoke( oDoc, "print", ( Array(), ) ) 
    #oDoc.print( Array() ) 
 
    # Using technique 1 or 2, be sure not to close the document 
    #  until printing is completed. 
    #    http://www.oooforum.org/forum/viewtopic.php?p=23144#23144 
 
 
    # Technique 3. 
    # Print the document by bringing up the Print Job dialog box 
    #  for the user to interact with. 
    oDocFrame = oDoc.getCurrentController().getFrame() 
    oDispatchHelper = createUnoService( "com.sun.star.frame.DispatchHelper" ) 
    oDispatchHelper.executeDispatch( oDocFrame, ".uno:Print", "", 0, Array() ) 
    # To learn some more about the dispatcher, see these articles... 
    #    http://www.oooforum.org/forum/viewtopic.php?t=5058 
    #    http://www.oooforum.org/forum/viewtopic.php?t=5057 
 
    # 
    #------- 
 
    #------- 
    # Now save the document 
 
    # Prepare the filename to save. 
    # We're going to save the file in several different formats, 
    #  but all based on the same filename. 
    cFile = "C:\Documents and Settings\dbrewer\Desktop\MyCalc" # Windows 
    #cFile = "/home/danny/Desktop/MyCalc.sxc" # Linux 
 
    # Now save the spreadsheet in native OOo Calc format. 
    cURL = convertToURL( cFile + ".sxc" ) 
    oDoc.storeAsURL( cURL, Array() ) 
 
    # Note the above used storeAsUrl, 
    #  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 it in CSV format. 
    cURL = convertToURL( cFile + ".csv" ) 
    oDoc.storeToURL( cURL, Array( makePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ) ) ) 
 
    # Now save it in DIF format. 
    cURL = convertToURL( cFile + ".dif" ) 
    oDoc.storeToURL( cURL, Array( makePropertyValue( "FilterName", "DIF" ) ) ) 
 
    # Now save it in SYLK format. 
    cURL = convertToURL( cFile + ".sylk" ) 
    oDoc.storeToURL( cURL, Array( makePropertyValue( "FilterName", "SYLK" ) ) ) 
 
    # Now save as HTML. 
    cURL = convertToURL( cFile + ".html" ) 
    oDoc.storeToURL( cURL, Array( makePropertyValue( "FilterName", "HTML (StarCalc)" ) ) ) 
 
    # A list of some filter names you can use for both loading 
    #  and saving a document can be found here... 
    #    http://www.oooforum.org/forum/viewtopic.php?t=3549 
 
    # 
    #------- 
 
 
    #------- 
    # Now close the document 
    oDoc.close( True ) 
    #------- 
 
 
 
 
 
# import Danny.OOo.Examples.CalcExamples 
# reload( Danny.OOo.Examples.CalcExamples ); from Danny.OOo.Examples.CalcExamples import * 
 
# CalcExample()


This code can be greatly enhanced, if you want to provide a better version of the code, please edit it and notify me for feedback. JZA (talk)

Set the font size of text in a spreadsheet

Here is an example of how to set the font size of text in a spreadsheet cell.

Sub Main 
   ' Get this spreadsheet document 
   oDoc = ThisComponent 
   ' Get the first sheet. 
   oSheet = oDoc.getSheets().getByIndex( 0 ) 
   ' Get cell A1 
   oCell = oSheet.getCellByPosition( 0, 0 ) 
 
   ' Put some text into the cell. 
   oCell.setString( "This is a test" ) 
 
   ' Get the cell's Text. 
   ' You can do many things with this text, like you can do with Writer text. 
   oText = oCell.getText() 
   ' Get a cursor on the text. 
   oCursor = oText.createTextCursor() 
 
   ' Select ALL of the text. 
   oCursor.gotoStart( False ) ' goto start of text 
   oCursor.gotoEnd( True ) ' goto end of text while holding down SHIFT key 
   ' Change selected char's height 
   oCursor.CharHeight = 34 
 
   ' Select the first four characters 
   oCursor.gotoStart( False ) ' goto start of text 
   oCursor.goRight( 4, True ) ' go right 4 chars, holding down SHIFT key 
   ' Change selected char's height 
   oCursor.CharHeight = 27 
End Sub

If all of the text is of uniform size, and you want to increase the size of the entire text cell, then simply do....

Sub Main 
   ' Get this spreadsheet document 
   oDoc = ThisComponent 
   ' Get the first sheet. 
   oSheet = oDoc.getSheets().getByIndex( 0 ) 
   ' Get cell A1 
   oCell = oSheet.getCellByPosition( 0, 0 ) 
 
   ' Get the cell's Text. 
   ' You can do many things with this text, like you can do with Writer text. 
   oText = oCell.getText() 
   ' Get a cursor on the text. 
   oCursor = oText.createTextCursor() 
 
   ' Select ALL of the text. 
   oCursor.gotoStart( False ) ' goto start of text 
   oCursor.gotoEnd( True ) ' goto end of text while holding down SHIFT key 
   ' Change selected char's height 
   oCursor.CharHeight = oCursor.CharHeight + 1 
End Sub

Underline each Nth row of cells

Underline a row of cells, every Nth row, by setting the cell border bottom and Adjust the column widths of a certain range of columns.


Sub Main 
 
   ' Create a new document. 
   oDoc = StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array() ) 
   ' Alternative: Use document that this macro is embedded into. 
   'oDoc = ThisComponent 
   ' Alternative: Load document from disk 
   ' CHANGE ME 
   'cFile = "C:\Documents and Settings\dbrewer\Desktop\Something.sxc" ' for Windows 
   'cFile = "/home/danny/Desktop/Something.sxc" ' for Linux 
   'cUrl = ConvertToUrl( cFile ) 
   'oDoc = StarDesktop.loadComponentFromURL( cUrl, "_blank", 0, Array() ) 
 
   ' Get first sheet of the document -- the zero'th sheet. 
   oSheet = oDoc.getSheets().getByIndex( 0 ) 
 
   UnderlineRows( oSheet ) 
   AdjustColumnWidths( oSheet ) 
End Sub 
 
 
' Pass in a single sheet of a spreadsheet document. 
' This will create borders that underline certain cells. 
Sub UnderlineRows( oSheet ) 
   '----- 
   ' CHANGE ME 
   ' CHANGE ME 
   ' You could turn these local variables into parameters. 
   nUnderlineFirstRow = 9 
   nUnderlineSkipRows = 4 
   nUnderlineLastRow = 50 
   cUnderlineFirstColumn = "A" 
   cUnderlineLastColumn = "I" 
 
   'nBorderColor = RGB( 0, 0, 0 ) ' Black 
   nBorderColor = RGB( 255, 200, 200 ) ' Pink 
 
   nInnerBorderWidth = 0 ' in 1000'th of cm 
   'nOuterBorderWidth = 75 ' in 1000'th of cm, means 0.075 cm width 
   nOuterBorderWidth = 1/32 * 2540 ' 1/32 inch (2.54 cm = 1 inch) 
   '----- 
 
   nRow = nUnderlineFirstRow 
   Do While nRow <= nUnderlineLastRow 
      ' Form a string such as...  "A19:I19" 
      cCellRangeName = cUnderlineFirstColumn + CSTR( nRow ) _ 
                  + ":" + cUnderlineLastColumn + CSTR( nRow ) 
 
      ' Get the range of cells whose border is to be adjusted. 
      oCellRange = oSheet.getCellRangeByName( cCellRangeName ) 
 
      'oCellRange.LeftBorder   = MakeCellBorderLine( nBorderColor, nInnerBorderWidth, nOuterBorderWidth, 0 ) 
      'oCellRange.RightBorder  = MakeCellBorderLine( nBorderColor, nInnerBorderWidth, nOuterBorderWidth, 0 ) 
      'oCellRange.TopBorder    = MakeCellBorderLine( nBorderColor, nInnerBorderWidth, nOuterBorderWidth, 0 ) 
      oCellRange.BottomBorder = MakeCellBorderLine( nBorderColor, nInnerBorderWidth, nOuterBorderWidth, 0 ) 
 
      nRow = nRow + nUnderlineSkipRows 
   Loop 
End Sub 
 
 
' Pass in a single sheet of a spreadsheet document. 
' This will set the column widths of certain cells. 
Sub AdjustColumnWidths( oSheet ) 
   '----- 
   ' CHANGE ME 
   ' CHANGE ME 
   ' You could turn these local variables into parameters. 
   cFirstColumn = "A" 
   cLastColumn = "I" 
   '----- 
 
   ' Turn the column names into column numbers. 
   nFirstColumn = CalcColumnNameToNumber( oSheet, cFirstColumn ) 
   nLastColumn = CalcColumnNameToNumber( oSheet, cLastColumn ) 
 
   ' Get the collection of all columns on the sheet. 
   oColumns = oSheet.getColumns() 
 
   ' Loop over each column... 
   For nCol = nFirstColumn To nLastColumn 
      ' Get a single column 
      oColumn = oColumns.getByIndex( nCol ) 
 
      ' Change column width 
      oColumn.Width = 2.5 * 2540 ' 2.5 inches * 2.54 cm / inch. 
   Next 
End Sub 
 
 
Function MakeCellBorderLine( nColor, nInnerLineWidth, nOuterLineWidth, nLineDistance ) _ 
         As com.sun.star.table.BorderLine 
   oBorderLine = createUnoStruct( "com.sun.star.table.BorderLine" ) 
   With oBorderLine 
      .Color = nColor 
 
      .InnerLineWidth = nInnerLineWidth 
      .OuterLineWidth = nOuterLineWidth 
      .LineDistance = nLineDistance 
   End With 
   MakeCellBorderLine = oBorderLine 
End Function 
 
Function CalcColumnNameToNumber( oSheet As com.sun.star.sheet.Spreadsheet,_ 
                     cColumnName As String ) As Long 
   oColumns = oSheet.getColumns() 
   oColumn = oColumns.getByName( cColumnName ) 
   oRangeAddress = oColumn.getRangeAddress() 
   nColumn = oRangeAddress.StartColumn 
   CalcColumnNameToNumber() = nColumn 
End Function

Take data from a Writer's table and insert in Calc

This code tries to do as mentioned, extract a table data from Writer and insert it in a Calc document.

Sub Main 
   ' Work with the writer document that this macro is embedded into. 
   oWriterDoc = ThisComponent 
 
   ' Get the collection of tables from the Writer doc. 
   oWriterTables = oWriterDoc.getTextTables() 
 
   ' Get the table named FooBar. 
   ' Each table has a name.  We want the table named FooBar. 
   ' To see or change the name of a table, first click in the table, 
   '  then Format-->Table, pick the Table tab, and adjust the Name. 
   oWriterTable = oWriterTables.getByName( "FooBar" ) 
 
   ' Find out how many Rows and Columns the table has. 
   nRows = oWriterTable.getRows().getCount() 
   nCols = oWriterTable.getColumns().getCount() 
 
 
   ' Create a new Calc spreadsheet. 
   oCalcDoc = StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array() ) 
 
   ' Get the first sheet of the document. 
   oSheet = oCalcDoc.getSheets().getByIndex( 0 ) 
'   oSheet = oCalcDoc.getSheets().getByName( "Sheet1" ) ' an alternate way to get by name 
 
   ' Determine where we are going to insert the data from the Writer table. 
   nFirstRow = 6 
   nFirstCol = 2 
 
   ' Copy the data, row by row, column by column 
   For nRow = 0 To nRows-1 
      For nCol = 0 To nCols-1 
         oWriterCell = oWriterTable.getCellByPosition( nCol, nRow ) 
         oCalcCell = oSheet.getCellByPosition( nCol+nFirstCol, nRow+nFirstRow ) 
 
         ' If the cell has a formula, then copy that... 
         If oWriterCell.getFormula() <> "" Then 
            oCalcCell.setFormula( oWriterCell.getFormula() ) 
 
         ' If the cell has a non-zero numeric value, then copy that... 
         ElseIf oWriterCell.getValue() <> 0 Then 
            oCalcCell.setValue( oWriterCell.getValue() ) 
            ' Question... what if the cell is numeric, but zero? 
         ElseIf oWriterCell.getString() = "0" Then 
            oCalcCell.setValue( oWriterCell.getValue() ) 
 
         Else 
            oCalcCell.setString( oWriterCell.getString() ) 
         EndIf 
      Next 
   Next 
End Sub
Personal tools