[libreoffice-users] Re: Batch for extracting lines into new files in CALC

Am 18.01.2012 18:13, Andreas Säger wrote:

> Connect a registered Base document to the spreadsheet. A connection to
> an indexed dBase copy might be more performant.
> Create a parameter query: SELECT * FROM "Table" WHERE "Column"= :p

Use the parameter query for testing if you get the wanted records for a 
given number :p.
Store the following Basic code somewhere in the global library container 
(aka "My Macros").
Modify the 6 constants on top of the code. Registered name of the 
database, table name, column name, first ID to substitute, last ID to 
substitute and the target path.
The code works without further modifications but with any type of 
database such as address books, csv, spreadsheets, dBase, MySQL, HSQLDB, 
MS Access, MS servers, Oracle servers, anything connectable with a Base 
document.

> Sub Import2xls
> Const cDataSource = "Chargen"
> Const cTableName = "MAT"
> Const cColumnName ="ID"
> Const cStartID = 405
> Const cEndID = 407
> Const cPath = "/tmp/"
>
> Dim addr as new com.sun.star.table.CellRangeAddress
> sQuery = "SELECT * FROM """& cTableName &"""WHERE """& cColumnName &"""="
> ' print squery
> Dim xlsprop as new com.sun.star.beans.PropertyValue
> xlsprop.Name = "FilterName"
> xlsprop.Value = "MS Excel 97"
>
> for i = cStartID to cEndID
>         doc = StarDesktop.loadComponentFromURL("private:factory/scalc","_blank", 0, Array() )
>         dbx = doc.DatabaseRanges
>         dbx.addNewByName("Import2xls", addr)
>         dbr = dbx.getByName("Import2xls")
>         dsc() = dbr.getImportDescriptor()
>         for each prop in dsc()
>                 if prop.Name = "SourceType" then prop.Value = com.sun.star.sheet.DataImportMode.SQL
>                 if prop.Name = "SourceObject" then prop.Value = sQuery & i
>                 if prop.Name = "DatabaseName" then prop.Value = cDataSource
>         next
>         dbr.ReferredCells.doImport(dsc())
>         doc.storeAsURL(convertToURL(cPath & format(i, String(len(cEndID),"0")) &".xls"), 
> Array(xlsprop))
>         doc.close(True)
> next
> End Sub



--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted