My need is: How can I programatically format a scalc cell using CLI UNO
It probably could be more easily done with OLE automation, but the
openoffice.org site recommends use of Cli Uno,
and I want to do it the recommended way. I have spent days on this - very
difficult to find documentation. Most of this is translated from C++ or C#.
The following code does almost all I want it to:
Starts OOoCalc
Inserts spreadsheet named test
Inserts many values into cells
etc.
But I cannot get it to format cell A7 in sheet names test - it fails at
runtime with error at line 80
All of this is because I cannot create a Locale, which is needed to format
First the code, followed by error detail.
Code:
Imports System
Imports System.Collections.Generic
Imports System.Windows.Forms
Imports unoidl.com.sun.star.lang
Imports unoidl.com.sun.star.uno
Imports unoidl.com.sun.star.bridge
Imports unoidl.com.sun.star.frame
Imports unoidl.com.sun.star.container
Imports unoidl.com.sun.star.sheet
Imports unoidl.com.sun.star.beans
Imports unoidl.com.sun.star.table
Imports unoidl.com.sun.star.util
Imports unoidl.com.sun.star.reflection
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
' Dim FileName As String = "F:\odtfiles est.odt"
Dim FileName As String = "C:\testCLIOO.ods"
' oDoc = unoidl.com.sun.star.sheet.XSpreadsheetDocument
' oDoc.getSheets()
Dim localContext As unoidl.com.sun.star.uno.XComponentContext =
uno.util.Bootstrap.bootstrap()
Dim multiServiceFactory As
unoidl.com.sun.star.lang.XMultiServiceFactory =
DirectCast(localContext.getServiceManager(),
unoidl.com.sun.star.lang.XMultiServiceFactory)
Dim componentLoader As XComponentLoader =
DirectCast(multiServiceFactory.createInstance("com.sun.star.frame.Desktop"),
XComponentLoader)
Dim xComponent As XComponent =
componentLoader.loadComponentFromURL("private:factory/scalc", "_blank", 0,
New unoidl.com.sun.star.beans.PropertyValue(-1) {})
Dim oDoc As unoidl.com.sun.star.sheet.XSpreadsheetDocument
oDoc = xComponent
Dim oSheets As unoidl.com.sun.star.sheet.XSpreadsheets
' Dim oSheet As unoidl.com.sun.star.sheet.XSpreadsheet
Dim oSheet As XSpreadsheet
Dim oSheetsIA As XIndexAccess
Dim oCell As XCell
oSheets = oDoc.getSheets
oSheetsIA = oSheets
Dim Sheet1 As XSpreadsheet
Dim Sheet2 As XSpreadsheet
Dim cSeries As unoidl.com.sun.star.sheet.XCellSeries
oSheets.insertNewByName("test", 0)
oSheet = oSheetsIA.getByIndex(0).Value
' Now using sheet: test
oCell = oSheet.getCellByPosition(0, 0)
oCell.setFormula("Test")
oSheet.getCellByPosition(1, 0).setFormula("Sales")
oSheet.getCellByPosition(2, 0).setFormula("Month")
oSheet.getCellByPosition(3, 0).setFormula("Year")
cSeries = oSheet.getCellRangeByPosition(3, 3, 5, 3)
cSeries.fillSeries(unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT,
unoidl.com.sun.star.sheet.FillMode.LINEAR,
unoidl.com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2.0, 6.0)
oCell = oSheet.getCellByPosition(0, 1)
oCell.setValue(54321.0)
oSheet.getCellByPosition(0, 2).setValue(625.0)
oCell = oSheet.getCellByPosition(0, 3)
oCell.setFormula("=SUM(A1:A3")
oCell = oSheet.getCellByPosition(0, 5)
oCell.setFormula("=DATE(2009;8;10)")
oCell = oSheet.getCellByPosition(0, 6) ' cell A7
oCell.setFormula("=40035") ' numerical value for 08/10/2009 in A7
' Dim oFormatter As unoidl.com.sun.star.util.XNumberFormatter
Dim oFormatter As XNumberFormatter
' Dim oFormat As
unoidl.com.sun.star.util.XNumberFormatsSupplier
Dim oFormatSupp As XNumberFormatsSupplier
oFormatSupp = oDoc
Dim oTypes As XNumberFormatTypes
Dim oLocale As unoidl.com.sun.star.lang.Locale
oTypes = oFormatSupp.getNumberFormats()
Dim nFormat As uno.Any
Dim oFormat As NumberFormat
Const dateFormat = unoidl.com.sun.star.util.NumberFormat.DATE
oLocale.Language = "en" ' ********* => this is the line 80 referred
to in error detail
oLocale.Country = "US"
oLocale.Variant = "Traditional_WIN"
' Now using sheet: Sheet3
Sheet1 = oSheetsIA.getByIndex(3).Value
' Sheet1 = oSheets.getByName(SheetName)
Sheet1.getCellByPosition(0, 1).setValue(9876)
' Now using sheet: Sheet1
oSheet = oSheetsIA.getByIndex(1).Value
oCell = oSheet.getCellByPosition(0, 4)
oCell.setValue(2.0)
oCell = oSheet.getCellByPosition(1, 4)
oCell.setValue(4.0)
cSeries = oSheet.getCellRangeByPosition(0, 4, 5, 4)
'
cSeries.fillSeries(unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT,
unoidl.com.sun.star.sheet.FillMode.LINEAR,
unoidl.com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2.0, 6.0)
cSeries.fillAuto(unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT,
2)
oSheet.getCellByPosition(0, 5).setFormula("Monday")
oSheet.getCellByPosition(1, 5).setFormula("Tuesday")
cSeries = oSheet.getCellRangeByPosition(0, 5, 5, 5)
cSeries.fillAuto(unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT,
2)
' Now using sheet: Sheet2
Sheet2 = oSheetsIA.getByIndex(2).Value
Sheet2.getCellByPosition(0, 1).setValue(4532)
Sheet2.getCellByPosition(1, 1).setValue(2345)
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
End
End Sub
End Class
Error detail:
System.NullReferenceException was unhandled
Message="Object reference not set to an instance of an object."
Source="CLIOOCalc"
StackTrace:
at CLIOOCalc.Form1.Button1_Click(Object sender, EventArgs e) in
C:\Documents and Settings\Brad\My Documents\Visual Studio
2005\Projects\CLIOOCalc\CLIOOCalc\Form1.vb:line 80
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons
button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at
System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&
m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd,
Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&
msg)
at
System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32
dwComponentID, Int32 reason, Int32 pvLoopData)
at
System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
reason, ApplicationContext context)
at
System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason,
ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[]
commandLine)
at CLIOOCalc.My.MyApplication.Main(String[] Args) in
17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[]
args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence
assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Note that while entering code, before I execute, VB 2005 shows error message
at line 80:
oLocale is used before it has been assigned a value. A null reference
exception could result at runtime. But line 80 does assign a value
--
View this message in context:
http://www.nabble.com/Cli-Uno-MS-visual-basic-2005---cannot-format-cell-programatically-tp24943858p24943858.html
Sent from the openoffice - dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@openoffice.org
For additional commands, e-mail: dev-h...@openoffice.org
