Implementing interface for importing external data in calc
sos
sos at pmg.be
Thu Mar 21 16:12:12 UTC 2019
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:///"<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 list
LibreOffice at lists.freedesktop.org<mailto: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/20190321/fd63a53d/attachment.html>
More information about the LibreOffice
mailing list