<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> </head> <body text="#000000" bgcolor="#FFFFFF"> 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 = <a class="moz-txt-link-rfc2396E" href="file:///" moz-do-not-send="true"> "file:///"</a> & 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 <div class="moz-cite-prefix">On 3/21/2019 6:47 AM, smooth_vaibhav wrote: </div> <blockquote type="cite" cite="mid:1553147233003-0.post@n3.nabble.com"> <pre class="moz-quote-pre" wrap="">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: <a class="moz-txt-link-freetext" href="http://document-foundation-mail-archive.969070.n3.nabble.com/Dev-f1639786.html" moz-do-not-send="true">http://document-foundation-mail-archive.969070.n3.nabble.com/Dev-f1639786.html</a> _______________________________________________ LibreOffice mailing list <a class="moz-txt-link-abbreviated" href="mailto:LibreOffice@lists.freedesktop.org" moz-do-not-send="true">LibreOffice@lists.freedesktop.org</a> <a class="moz-txt-link-freetext" href="https://lists.freedesktop.org/mailman/listinfo/libreoffice" moz-do-not-send="true">https://lists.freedesktop.org/mailman/listinfo/libreoffice</a></pre> </blockquote> </body> </html>