Implementing interface for importing external data in calc

sos sos at
Thu Mar 21 16:12:12 UTC 2019


you can import data into a spreadsheet in 2 ways

Fast = connecting the Calcdoc to a databasedoc and using buildin "Importer" service oDBRange.getReferredCells.doImport(oDesc())

Slow = import cell by cell using a dataset or a array


function ConnectCalc_to_DBdoc(Optional sqlcalc as String, sDocURL as String , optional sArea as string)
        dim extt as string
    extt = right(bstandnm , 3)
    SearchFlags = +

    if ucase(extt) = "OTS" then 'calc template
     Dim args(3) As New
    Dim URL As String
   ' URL = convertToUrl("T:\Template\TOOLS\Berichten_spreadsheet.ots")
    URL = convertToUrl(sDocurl)
    args(0).Name = "AsTemplate"
    args(0).Value = True
    args(1).Name = "MacroExecutionMode"
    args(1).Value =
    args(2).Name = "FilterName"
    args(2).Value = "calc8_template"
    args(3).Name = "Hidden"
    args(3).Value = false
    ODS = StarDeskTop.LoadComponentFromUrl(URL, "_blank", 0, args())
     ODS = StarDesktop.loadComponentFromURL("private:factory/scalc","_blank",SearchFlags,Array())

    oSheet = ODS.Sheets.getByIndex(0)
    if ismissing(sArea) then
    sArea = "A1"
    area = osheet.getCellRangeByName(sArea).getRangeAddress()' adres is nodig, niet de range opzich
    if not oDS.DatabaseRanges.hasByName("MyImport") then
    oDBRange = oDS.DataBaseRanges.getByName("MyImport")
    oDBcontext = CreateUnoService("")
    if oDBcontext.hasbyname("mysql_native")then

     oDBcontext.registerDatabaseLocation("mysql_native",converttoURL("\\your DBdoclocation")

    oDB = oDBcontext.GetByName("mysql_native")
    oDB.Password = "yourpassword"

'**** we make first a QUERY because a simple  SQLstring in the  ImportDescritor only works with  OO-SQL en not with a native SQL  (this is a bug)

    oQdefs = oDB.QueryDefinitions
    oQ = createUnoService("")
    oQ.EscapeProcessing = False
    oQ.command = sqlcalc
        If oQDefs.hasByName("calcdoc") Then
              oQDefs.removeByName("calcdoc")', oQueryObject)
           End If
    oQDefs.insertByName("calcdoc", oQ)

    Dim oDesc(3) as new
    oDesc(0).Name = "DatabaseName"
    oDesc(0).Value = "mysql_native"
    oDesc(1).Name = "SourceType"
    oDesc(1).Value =
    oDesc(2).Name = "SourceObject"
    oDesc(2).Value = "calcdoc"
 '   oDesc(3).Name = "IsNative"  'must been false  !
  '  oDesc(3).Value = false

    Dim FileProperties(1) As New
    Url = "file:///"<file:///> & sDocurl
       FileProperties(0).Name = "Overwrite"
    FileProperties(0).Value = True
        FileProperties(1).Name = "FilterName"
    if lcase(extt) = "xls" then
        FileProperties(1).Value = "MS Excel 97"
        FileProperties(1).Value = ""
    end if

    If NOT IsMissing(sDocurl) and len(sDocurl) > 5 and ucase(right(sDocurl,3)) <> "OTS" Then
        oDS.storeAsURL(Url, FileProperties())
    end if
    ConnectionviaDBdoc = ODS


Sub ResultSetToCalc(oResultGet)
    Dim args(3) As New
    args(1).Name = "MacroExecutionMode"
    args(1).Value =
    args(2).name = "Hidden"
    args(2).Value = true
    ods = nothing
    ODS = StarDesktop.LoadComponentFromUrl("private:factory/scalc","_default",0,args())
    oSheet = ODS.Sheets.getByIndex(0)
    oCellCursor = oSheet.createCursor()

    xPos = 0
    yPos = 3

    For i = 0 To oResultGet.MetaData.ColumnCount - 1
        Cell = oSheet.getCellByPosition(xPos + i, yPos)
        Cell.String = oResultGet.MetaData.getColumnName(i+1)
    Next i
        yPos = yPos + 1
        For i = 0 To oResultGet.MetaData.ColumnCount - 1
            Cell = oSheet.getCellByPosition(xPos + i, yPos)
            Cell.String = oResultGet.getString(oResultGet.findColumn(oResultGet.MetaData.getColumnName(i+1)))
        Next i

    nFirstCol = oCellCursor.getRangeAddress().StartColumn
    nLastCol = oCellCursor.getRangeAddress().EndColumn
    nStartRow = oCellCursor.getRangeAddress().StartRow
    nLastRow = oCellCursor.getRangeAddress().EndRow

    oRange = oSheet.getCellRangeByPosition(nFirstCol, nStartRow, nLastCol, nStartRow)    'Rij met de kolomnamen
    oRange.CharWeight =
    oRange.CellBackColor = RGB(200,200,200)
    oRange.HoriJustify =

    oColumns = oRange.getColumns()
    for i = 0 to nLastCol
         oColumns.getByIndex( i ).optimalWidth = true

    ODS.CurrentController.Frame.ContainerWindow.Visible = True

End Sub

On 3/21/2019 6:47 AM, smooth_vaibhav wrote:

I have been keen about the project idea of implementing the import
functionality of external data in calc. I have some queries regarding it.

1) There are numerous possibilities of external data sources like database,
files. So should the implementation should be independent of external source
of data or is it the case that we first gonna store it in a ScDBdata.

2) Its given that the user can select the range of data to be selected and
imported. Does that imply we provide them with a UI such that they can
choose the number of columns and rows to be imported.

I acknowledge the fact that the Mail list is to be used just for important
purposes but I have some queries to clear for better understanding of the
project idea.


Sent from:
LibreOffice mailing list
LibreOffice at<mailto:LibreOffice at>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <>

More information about the LibreOffice mailing list