Difference between revisions of "Python-Calc"

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