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 <s...@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
_______________________________________________ LibreOffice mailing list LibreOffice@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice