Re: Implementing interface for importing external data in calc

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