Implementing interface for importing external data in calc
Markus Mohrhard
markus.mohrhard at googlemail.com
Thu Mar 21 16:22:58 UTC 2019
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 at 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 at lists.freedesktop.orghttps://lists.freedesktop.org/mailman/listinfo/libreoffice
>
> _______________________________________________
> LibreOffice mailing list
> LibreOffice at lists.freedesktop.org
> https://lists.freedesktop.org/mailman/listinfo/libreoffice
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice/attachments/20190322/12003dbd/attachment.html>
More information about the LibreOffice
mailing list