Date: prev next · Thread: first prev next last
2019 Archives by date, by thread · List index


Hey,

please ignore this email. The original email is about a GSoC project and
not about macro progamming.

Kind regards,
Markus

On Fri, Mar 22, 2019 at 12:12 AM sos <sos@pmg.be> wrote:

hallo,

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

fast

function ConnectCalc_to_DBdoc(Optional sqlcalc as String, sDocURL as
String , optional sArea as string)
        dim extt as string
    extt = right(bstandnm , 3)
    SearchFlags = com.sun.star.frame.FrameSearchFlag.CREATE +
com.sun.star.frame.FrameSearchFlag.ALL

    if ucase(extt) = "OTS" then 'calc template
     Dim args(3) As New com.sun.star.beans.PropertyValue
    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 =
com.sun.star.document.MacroExecMode.ALWAYS_EXECUTE_NO_WARN
    args(2).Name = "FilterName"
    args(2).Value = "calc8_template"
    args(3).Name = "Hidden"
    args(3).Value = false
    ODS = StarDeskTop.LoadComponentFromUrl(URL, "_blank", 0, args())
    else
     ODS =
StarDesktop.loadComponentFromURL("private:factory/scalc","_blank",SearchFlags,Array())
       endif

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


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("com.sun.star.sdb.QueryDefinition")
    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 com.sun.star.beans.PropertyValue
    oDesc(0).Name = "DatabaseName"
    oDesc(0).Value = "mysql_native"
    oDesc(1).Name = "SourceType"
    oDesc(1).Value = com.sun.star.sheet.DataImportMode.QUERY
    oDesc(2).Name = "SourceObject"
    oDesc(2).Value = "calcdoc"
 '   oDesc(3).Name = "IsNative"  'must been false  !
  '  oDesc(3).Value = false
    oDBRange.getReferredCells.doImport(oDesc())
    oDBcontext.revokeDatabaseLocation("mysql_native")
    oDS.DatabaseRanges.removeByName("MyImport")

    Dim FileProperties(1) As New com.sun.star.beans.PropertyValue
    Url = "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"
    else
        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
end FUNCTION

slow

Sub ResultSetToCalc(oResultGet)
    Dim args(3) As New com.sun.star.beans.PropertyValue
    args(1).Name = "MacroExecutionMode"
    args(1).Value =
com.sun.star.document.MacroExecMode.ALWAYS_EXECUTE_NO_WARN
    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
    oResultGet.beforeFirst()
    while oResultGet.next()
        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
    wend

    oCellCursor.gotoStartOfUsedArea(true)
    oCellCursor.gotoEndOfUsedArea(true)
    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 = com.sun.star.awt.FontWeight.BOLD
    oRange.CellBackColor = RGB(200,200,200)
    oRange.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER

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

    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.

Thanks.



--
Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Dev-f1639786.html
_______________________________________________
LibreOffice mailing 
listLibreOffice@lists.freedesktop.orghttps://lists.freedesktop.org/mailman/listinfo/libreoffice

_______________________________________________
LibreOffice mailing list
LibreOffice@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.