import database contentimport databaseTom Schindl
How do I import database content efficiently
When creating a rowset and looping through it setting the column values one by one it it's horribly slow. Is there a better solution available
Yes. You could use the XImport-Interface which is supported by a CellRange
' Load the tools lib
Globalscope.BasicLibraries.loadLibrary("Tools")
' Create a new OO-Calc-Document
CalcDoc = CreateNewDocument( "scalc" )
' Get the spreadsheet
Sheet = CalcDoc.Sheets(0)
Sheet = CalcDoc.Sheets(1)
' Create a PropertyValue-Array to specify what we are
' loading from the database
Dim importDesc(2) As New com.sun.star.beans.PropertyValue
ImportDesc(0).Name = "DatabaseName"
importDesc(0).Value = "oeush_billing"
importDesc(1).Name = "SourceType"
importDesc(1).Value = com.sun.star.sheet.DataImportMode.SQL
importDesc(2).Name = "SourceObject"
importDesc(2).Value = "SELECT cu_id Kundennummer, cu_companyname as Firma, " _
+ "cu_givenname Vorname, cu_surname Nachname, co_payment1 Betrag," _
+ "co_label Bezeichnung, co_bill_number Rechnungsummer " _
+ "FROM customer, contract WHERE co_ref_customer = cu_id " _
+ "AND co_ref_project = " + ProjectId
' now lets import by using
Sheet.getCellRangeByName("A1:A1").doImport(importDesc())
' Now do some formating
Dim range As Object
' make the headline bold and with a grey background
range = Sheet.getCellRangeByPosition(0,0,6,0)
range.CharWeight = com.sun.star.awt.FontWeight.BOLD
range.CellBackColor = RGB(200,200,200)
range.HoriJustify = com.sun.star.table.CellHoriJustify.LEFT
' make sure that the cols show the whole text
Dim columns As Object
Dim i As Integer
columns = range.getColumns()
for i = 0 to 6
columns.getByIndex( i ).optimalWidth = true
next
Initial versionImproved setting of optimal width