Difference between revisions of "Python-Calc"
Line 437: | Line 437: | ||
This code tries to do as mentioned, extract a table data from Writer and insert it in a Calc document. | This code tries to do as mentioned, extract a table data from Writer and insert it in a Calc document. | ||
− | <source lang= | + | <source lang="python"> |
Sub Main | Sub Main | ||
' Work with the writer document that this macro is embedded into. | ' Work with the writer document that this macro is embedded into. |
Revision as of 13:51, 10 January 2021
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:
- Create a new spreadsheet
- Open an existing spreadsheet
- Insert strings into a cell
- Insert formulas into a cell
- 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