Re: Implementing interface for importing external data in calc
Thanks. A very frank query.Any advice on how do I get along understanding the code better in the files you mentioned would be helpful. also, is the interface implementation in the files you mentioned the orcus interface?? -- Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Dev-f1639786.html ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice
Re: Implementing interface for importing external data in calc
Hey, On Fri, Mar 22, 2019 at 12:46 AM smooth_vaibhav wrote: > Is there any documentation for the already available UI implementation and > the data import code files. I have already gone through the data import > code > files like the ones residing in sc/source/ui/dataprovider/. > As you might have discovered this is the data provider and data transformation code. Can you provide me with some code pointers for the UI implementation of the > data import functionality that you mentioned. > The corresponding UI code is in sc/source/ui/miscdlgs/dataproviderdlg.cxx and sc/source/ui/inc/dataproviderdlg.hxx but as I mentioned it is quite rough and might need some UX rethinking. Regards, Markus > > > > > -- > Sent from: > http://document-foundation-mail-archive.969070.n3.nabble.com/Dev-f1639786.html > ___ > 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
Re: Implementing interface for importing external data in calc
Is there any documentation for the already available UI implementation and the data import code files. I have already gone through the data import code files like the ones residing in sc/source/ui/dataprovider/. Can you provide me with some code pointers for the UI implementation of the data import functionality that you mentioned. -- Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Dev-f1639786.html ___ LibreOffice mailing list LibreOffice@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice
Re: Implementing interface for importing external data in calc
Hey, On Thu, Mar 21, 2019 at 5:37 PM 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. > The project is about adding some external data sources and the possibility to extend that through extensions. We already have a few providers for external sources from last year's GSoC project. The second step of transforming the data before we actually write it to the calc document itself is independent of the actual source. > 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. > Yes. There is already a really ugly UI that I hacked together a year ago. In a current master build just go to Data->Data Provider and play around. You might need to define a DB Range before actually being able to import the data into the document. As can be seen by the ugly UI there is quite some work necessary to make the feature user friendly and I think this should be a central part of this year's GSoC project. Regards, Markus > > Thanks. > > > > -- > Sent from: > http://document-foundation-mail-archive.969070.n3.nabble.com/Dev-f1639786.html > ___ > 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
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 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)
Re: Implementing interface for importing external data in calc
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
Implementing interface for importing external data in calc
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@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice
