LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (https://www.linuxquestions.org/questions/programming-9/)
-   -   Libre/Open Office project; could use some help, suggestions (https://www.linuxquestions.org/questions/programming-9/libre-open-office-project%3B-could-use-some-help-suggestions-4175517471/)

Ramurd 09-05-2014 11:50 AM
Libre/Open Office project; could use some help, suggestions
 
Ok, here's the thing: a collegue of mine write this thing in MS Excel with some VB Scripting. Just to prove the power of Open Source, I thought it should be doable with one of the main OpenSource Office suites (in my case LibreOffice, but I think OpenOffice should suffice as well)

The thing is: it's not that well documented, the UI does not give suggestions on the Objects and their functions / variables so it's a bit a steep curve to get started. But the internet is a nice thing, and so I stumbled on this document: http://www.pitonyak.org/OOME_3_0.pdf

(I'm not -as yet- aware of a better documentation)

So here's the thing; I'm trying to make a document with 3 sheets:
- Sheet 1 is the control sheet; I can put buttons on this page
- Sheet 2 contains records that are already being worked on. (I'll explain this a bit later)
- Sheet 3 should contain the output of a jdbc query.

The basics are around; now they have to be connected with some scripting. I guessed the best thing would be to use the native Basic. So what's the idea:

Once pressed upon a button on Sheet 1, Sheet 3 should be removed, recreated and filled with the new output of the query. (I did manage to remove and recreate the sheet; the filling with output of the query (with headers) not yet)

What should happen next is to compare the first field (column A) of each record against the records on Sheet 2 Column B. All records that appear on Sheet 2, Column B should be removed from Sheet 3 (the query output); To prevent Sheet 2 from endless growing: Any records in Sheet 2 that do not appear in Sheet 3 Column A, should be removed from Sheet 2.

Imagine the flow like this:
The query tells me "what items to handle today" (it filters out the completed items to handle)
In Sheet 2 I record the items I'm handling at the moment (but they're not completed yet)

So, if I press the button, I get a new workload, but it's a bit too full, so I need to know what NEW things to handle. (the other things I already know of); once such an action is handled, it's stored in the database and thus will not return from the query.

I have this idea also about adding Listeners that make my work so, that I automatically do all my work (possibly a few hours a day) by just one click on a button... :-) but that'll wait till the end of the project...

Now, this thing I see as a learning experience; so I'm NOT asking for complete code, or remarks like "you should not do this" or ... but I do need a bit of help to get started.

Once this project is completed, I'll give some idea of what LibreCode I wrote, for others to enjoy.

Anyway: here we go. The first piece of code:

Code:
Sub MakeNewQuerySheet()
        Dim QueryOutputSheetName As String
        Dim QueryOutputSheet
        Dim oSheets
       
        QueryOutputSheetname = "QueryResults"
       
        oSheets = ThisComponent.Sheets
       
        REM ***
        REM * if there's already a QuerySheet: toss it away
        REM * a new one will be made
        REM ***
        If oSheets.hasByName(QueryOutputSheetName) Then
                oSheets.removeByName(QueryOutputSheetName)
        End If
       
        REM ***
        REM * Now make a new sheet
        REM ***
        oSheets.insertNewByName(QueryOutputSheetName,oSheets.getCount())
       
        REM ***
        REM * now fill the sheet with data from the jdbc source
        REM ***
        QueryOutputSheet=oSheets.getByname(QueryOutputSheetName)
        REM QueryOutputSheet.fillFomExternalSource();
       
End Sub
The fillFromExternalSource function is non-existant (I knew that); but how do I go about filling this sheet with all the data returned from the jdbc connection (could be anywhere between 1 and 50000 rows)
I do have a "base" file that contains the JDBC connection; I have assigned a query (that only does a SELECT on the table) and I want that query (result) added to the QueryOutputSheet.

Anyone with hints (or links) how to achieve this?

timl 09-06-2014 12:41 AM
If nothing pops up here can I suggest a trip the the libreoffice mailing list?

http://nabble.documentfoundation.org...-f1639498.html

I have seen questions of this nature posted there and there may be someone able to field queries about coding

jlinkels 09-06-2014 08:23 AM
Quote:
Originally Posted by Ramurd (Post 5232868)
Just to prove the power of Open Source, I thought it should be doable with one of the main OpenSource Office suites
Although I sympathize with you about proving the power of Open Source, LibreOffice is not the program which is able to demonstrate that. On the contrary, if LibreOffice had to be the demonstration of Open Source, it would be the best to scare anyone off FOSS.

I am currently using various versions on different computers, both 3.x and 4.x. While 3.x is commonly lacking some latest and greatest features, it is pretty stable.

4.x OTOH makes a mess of almost everything:
  • Writer is next to hopeless when it comes to auto numbering and heading styles. Placing of graphics is very reliable exactly at the place where you don't want them. (But that has been like this since version 1)
  • Calc is on no par with Excel and demonstrates some serious slowness bugs while saving large files
  • The most hilarious is Impress. When Autosave is turned on it might remove at random 90% of all the images you added to your presentation. It might as well happen when you are rehearsing your 30 slides presentation your were to give in front of a 200 persons audience.
And while reading MSOffice documents is mostly succesful (except for graphics, styles, headings, tables, fonts and drawings) interoperability cannot be relied on.

Sorry for this rant, but being a die-hard Linux user I feel kind of lost without a good office suite.

As for the quality of FOSS, look into Inkscape, Gimp, Apache, ImageMagick (and a few more, less well-known). Usually those programs focus on getting the applications stable and useful instead of increasing the version number counter.

jlinkels

Ramurd 09-16-2014 09:35 AM
Well, I think that OS thing actually is a good thing; Even though there certainly are people that are not that thrilled.
The code as I have it now (even though it's pretty Dutch) could use some performance boosts and a few minor issues (as it is, if the query results in > 32k rows this thing crashes, but even so: excel doesn't like too big result sets either)

Code:
REM ***** BASIC *****

Sub Main
End Sub

Sub MaakNieuweQuerySheet()
        Dim QueryOutputSheetName As String
        Dim QueryOutputSheet
        Dim oConnection
        Dim myDS
        Dim oSheets
        Dim DatabaseName as String
        Dim controlSheet
        Dim statusCell
       
        QueryOutputSheetname = "QueryResults"
        DatabaseName = "isbmonitor"
       
        oSheets = ThisComponent.Sheets
        controlSheet = oSheets.getByName("Uitleg")
       
        REM ***
        REM * All status cells red
        REM ***
        statusCell = controlSheet.getCellByPosition(0,12)
        statusCell.CellBackColor=RGB(255,0,0)
        statusCell = controlSheet.getCellByPosition(1,12)
        statusCell.CellBackColor=RGB(255,0,0)
        statusCell = controlSheet.getCellByPosition(2,12)
        statusCell.CellBackColor=RGB(255,0,0)       
        statusCell = controlSheet.getCellByPosition(3,12)
        statusCell.CellBackColor=RGB(255,0,0)
        statusCell = controlSheet.getCellByPosition(4,12)
        statusCell.CellBackColor=RGB(255,0,0)
        statusCell = controlSheet.getCellByPosition(5,12)
        statusCell.CellBackColor=RGB(255,0,0)
       
        ' counter cells ook opschonen
        statusCell = controlSheet.getCellByPosition(2,13)
        statusCell.setValue(0)
        statusCell = controlSheet.getCellByPosition(4,13)
        statusCell.setString("0 van 0")
        statusCell = controlSheet.getCellByPosition(5,13)
        statusCell.setString("0 van 0")
       
        MsgBox("Ga pas verder kijken bij de volgende messagebox.", "Start")

        REM ***
        REM * now we can really begin
        REM ***
        statusCell=controlSheet.getCellByPosition(0,12)
        statusCell.setString("Start")
        statusCell.CellBackColor=RGB(255,255,0)
       
        REM ***
        REM * Als er al een query output sheet is, wegmikken
        REM * We maken een nieuwe
        REM ***
        If oSheets.hasByName(QueryOutputSheetName) Then
                oSheets.removeByName(QueryOutputSheetName)
        End If
        statusCell.setstring("Cleanup")
        statusCell.CellBackColor=RGB(0,255,0)
        statusCell = controlSheet.getCellByPosition(1,12)
        statusCell.setString("Query")
        statusCell.CellBackColor=RGB(255,255,0)
       
        REM ***
        REM * Nieuwe sheet maken, de oude is weg
        REM ***
        oSheets.insertNewByName(QueryOutputSheetName,oSheets.getCount())
       
        REM ***
        REM * connect to the database
        REM ***
        Dim oBaseContext
        Dim DBContext
        Dim DBConn
               
        oBaseContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
        If NOT oBaseContext.hasElements() Then
            MsgBox "There are no registered data sources."
        End If

        If(oBaseContext.hasByName(DatabaseName)) Then
                DBContext = oBaseContext.getByName(DatabaseName)
        Else
                MsgBox "Database " & DatabaseName & " is not registered..."       
        End If
       
        REM ***
        REM * hier gebruiken we een handler voor
        REM ***
        Dim DbConnHanler
        DbConnHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
        DBConn = DBContext.ConnectWithCompletion(DbConnHandler)

        REM ***
        REM * we're connected now, I hope
        REM ***
        Dim oStatement
        Dim sSQL$
        Dim oResult
        oStatement=DBConn.CreateStatement()
        sSQL="SELECT * FROM permanentpdumonitor WHERE monitor_event = 'F' and repair_pdu_id is null"
        oResult = oStatement.executeQuery(sSQL)
       
        statusCell.CellBackColor=RGB(0,255,0)
        statusCell = controlSheet.getCellByPosition(2,12)
        statusCell.setString("toData")
        statusCell.CellBackColor=RGB(255,255,0)
       
        REM ***
        REM * nieuwe sheet vullen met data uit jdbc koppeling
        REM ***
        QueryOutputSheet=oSheets.getByname(QueryOutputSheetName)
       
        Dim oData()
        ResultSetToData(oResult,ThisComponent,"MaakNieuweQuerySheet",oData())
'        ResultSetToSheet(oResult,QueryOutputSheet)
       
        statusCell.CellBackColor=RGB(0,255,0)
        statusCell = controlSheet.getCellByPosition(3,12)
        statusCell.setString("toSheet")
        statusCell.CellBackColor=RGB(255,255,0)
       
        AppendDataToSheet(QueryOutputSheet,oData())
        statusCell.CellBackColor=RGB(0,255,0)
       
        REM ***
        REM * Disconnect from the database
        REM ***
        DBConn.close()
       
        REM ***
        REM * Now clear InBehandeling
        REM ***
       
        statusCell = controlSheet.getCellByPosition(4,12)
        statusCell.setString("InBehandeling")
        statusCell.CellBackColor=RGB(255,255,0)
        CleanupInBehandeling()
        statusCell.CellBackColor=RGB(0,255,0)

        statusCell = controlSheet.getCellByPosition(5,12)
        statusCell.setString("QueryOutput")
        statusCell.CellBackColor=RGB(255,255,0)
        CleanupQueryOutput()
        statusCell.CellBackColor=RGB(0,255,0)

        MsgBox "Aan de slag!"               
End Sub

Sub ResutSetToSheet(oResult,oSheet)

        Dim oSheets
        Dim controlSheet
        Dim controlCell
        Dim oData()
        Dim Counter As Long
        Dim n
        Dim i
       
        oSheets=ThisComponent.sheets
        controlSheet = oSheets.getByName("Uitleg")
        controlCell = controlSheet.getCellByPosition(2,13)

        Counter = 0
        controlCell.setValue(Counter)
       
        n = 0
        Do While oResult.next()
                If n = 0 Then
                        oMeta = oResult.getMetaData()
                        n = oMeta.getColumnCount()
                        oData() = DimArray(n)
                        For i = 1 to n
                                oData(i-1) = oMeta.getColumnName(i)
                                oSheet.addRow(oData())
                        Next
                End If
                       
        Loop

End Sub

Sub CleanupInBehandeling()
        Dim QueryOutputSheet
        Dim InBehandelingSheet
        Dim ControlSheet
        Dim Sheets
        Dim QueryOutputSheetName As String
        Dim InBehandelingSheetName As String
        Dim ControlSheetName
        Dim StatusString
        Dim StatusCell
       
        QueryOutputSheetName="QueryResults"
        InBehandelingSheetName="InBehandeling"
        ControlSheetName="Uitleg"
       
        Sheets = ThisComponent.Sheets
        QueryOutputSheet=Sheets.getByName(QueryOutputSheetName)
        InBehandelingSheet=Sheets.getByName(InBehandelingSheetName)
       
        REM ***
        REM * for each pdu_id (column name) in InBehandeling
        REM * - see if that pdu exists in QueryOutputSheet
        REM * - if not: delete the row
        REM ***
       
        ' pdu_id is column B in "InBehandeling"
        ' pdu_id is column A in "QueryResults"
        Dim IBCol As Integer
        Dim IBCurRow As Integer
        Dim IBEndRow As Integer
        Dim IBEndCol As Integer
        Dim aAddress As Variant
        Dim oCursor As Object
        Dim oCell As Object
        Dim StringToFind As String
        Dim Found As Variant
        Dim VerwijderCount As Long
        Dim VerwijderArray()
        Dim n as Long
       
        IBCol=1 ' column B is 1
        oCell=InBehandelingSheet.getCellByPosition(1,1)
        oCursor = InBehandelingSheet.createCursorByRange(oCell)
        oCursor.GoToEndOfUsedArea(True)
        aAddress = oCursor.RangeAddress
       
        IBEndRow=aAddress.EndRow
        IBEndCol=IBCol
       
        VerwijderCount=0
       
        controlSheet=ThisComponent.sheets.getByName(ControlSheetName)
        statusCell = controlSheet.getCellByPosition(4,13)
        statusString = "0 van " & VerwijderCount
        statusCell.setString(StatusString)
       
        For IBCurRow = 1 To IBEndRow 'we skip the header
                oCell = InBehandelingSheet.GetCellByPosition(IBCol,IBCurRow)
                StringToFind=oCell.String
'                Found = true
               
                Found = SheetSearch(StringToFind,QueryOutputSheet,true)
'                Print StringToFind & ":" & Found '& CHR$(10)
               
                if(IsNull(Found)) Then
'                        print "String: " & StringToFind & "; Count: " & VerwijderCount & "."
                        AppendToArray(VerwijderArray, StringToFind)
'                        VerwijderArray(VerwijderCount) = StringToFind
                        VerwijderCount = VerwijderCount + 1
               
                        statusString = "0 van " & VerwijderCount
                        statusCell.setString(StatusString)
                End If
        Next

        Dim IndexCell
        Dim IndexRow
       
        StatusString = "0 van " & VerwijderCount
        statusCell.setString(StatusString)
        If VerwijderCount > 0 Then
                For n = 0 To VerwijderCount-1
                        IndexCell=SheetSearch(VerwijderArray(n),InBehandelingSheet,true)
                        if(Not IsNull(IndexCell)) Then
                                IndexRow=IndexCell.CellAddress.Row
                                InBehandelingSheet.Rows.removeByIndex(IndexRow,1)
                       
                                StatusString = n+1 & " van " & VerwijderCount
                                StatusCell.setString(StatusString)
                        End If
                Next
        End If
       
'        MsgBox VerwijderCount & " rows uit '" & InBehandelingSheetName & "' verwijderd."
End Sub

Sub CleanupQueryOutput()
        Dim QueryOutputSheet
        Dim InBehandelingSheet
        Dim Sheets
        Dim QueryOutputSheetName As String
        Dim InBehandelingSheetName As String
        Dim ControlSheet
        Dim ControlSheetName
        Dim StatusCell
        Dim StatusString As String
       
        QueryOutputSheetName="QueryResults"
        InBehandelingSheetName="InBehandeling"
        ControlSheetName="Uitleg"
       
        Sheets = ThisComponent.Sheets
        QueryOutputSheet=Sheets.getByName(QueryOutputSheetName)
        InBehandelingSheet=Sheets.getByName(InBehandelingSheetName)
        ControlSheet=Sheets.getByName(ControlSheetName)
       
        REM ***
        REM * for each pdu_id (column name) in QueryOutputSheet
        REM * - see if that pdu exists in InBehandelingSheet
        REM * - if so: delete the row (we're already working on that item)
        REM ***
       
        ' pdu_id is column B in "InBehandeling"
        ' pdu_id is column A in "QueryResults"
        Dim QOCol As Integer
        Dim QOCurRow As Integer
        Dim QOEndRow As Integer
        Dim QOEndCol As Integer
        Dim aAddress As Variant
        Dim oCursor As Object
        Dim oCell As Object
        Dim StringToFind As String
        Dim Found As Variant
        Dim VerwijderCount As Long
        Dim VerwijderArray()
       
        QOCol=0 ' column A is 0
        oCell=QueryOutputSheet.getCellByPosition(1,0)
        oCursor = QueryOutputSheet.createCursorByRange(oCell)
        oCursor.GoToEndOfUsedArea(True)
        aAddress = oCursor.RangeAddress
       
        QOEndRow=aAddress.EndRow
        QOEndCol=IBCol
       
        VerwijderCount=0
        StatusCell = controlSheet.getCellByPosition(5,13)
        statusString = "0 van " & VerwijderCount
       
        For QOCurRow = 1 To QOEndRow 'we skip the header
                oCell = QueryOutputSheet.GetCellByPosition(QOCol,QOCurRow)
                StringToFind=oCell.String
               
                Found = SheetSearch(StringToFind,InBehandelingSheet,true)
               
                if(not IsNull(Found)) Then
                        AppendToArray(VerwijderArray, StringToFind)
                        VerwijderCount = VerwijderCount + 1
                       
'                        removeArray(removeCounter) = StringToFind
'                        removeCounter = removeCounter + 1
               
                        StatusString = "0 van " & VerwijderCount
                        StatusCell.setString(StatusString)
                End If
        Next
       
        Dim n As Long
        Dim RemoveCell
        Dim RemoveRow
        for n = 0 To VerwijderCount-1
                RemoveCell=SheetSearch(VerwijderArray(n),QueryOutputSheet,true)
                RemoveRow=RemoveCell.CellAddress.Row
                QueryOutputSheet.Rows.removeByIndex(RemoveRow,1)
               
                StatusString = n+1 & " van " & VerwijderCount
                statusCell.setString(StatusString)
        Next
       
'        MsgBox VerwijderCount & " rows uit '" & QueryOutputSheetName & "' verwijderd."
       
End Sub


Function SheetSearch(sString$, oSheet, bWholeWord As Boolean) As Variant
        Dim oDescriptor
        Dim oFound
       
        oDescriptor = oSheet.createSearchDescriptor()
       
        With oDescriptor
                .SearchString = sString$
                .SearchWords = bWholeWord
                .SearchCaseSensitive = True
        End With

        oFound = oSheet.findFirst(oDescriptor)
       
        SheetSearch = oFound       
End Function

Sub AppendDataToSheet(oSheet, oData())
REM        Dim oSheet    'The first sheet in the document.
        Dim oAddr      'Address of the current cursor.
        Dim iNumRows%  'Number of rows to add.
        Dim iNumCols%  'Number of columns to add.
        Dim x()        'Utility variable used as a single row.
        Dim oRange    'Range to add the data and focus the cursor.

        oAddr = oSheet.getCellByPosition(0,0)

        REM Determine the number of rows in the data.
        REM Next, obtain the first row and see how many columns it has.
        iNumRows% = UBound(oData()) - LBound(oData())
        x() = oData(lBound(oData()))
        iNumCols = UBound(x()) - LBound(x())

        oRange = oSheet.getCellRangeByPosition(0, 0, iNumCols, iNumRows)
       
        oRange.setDataArray(oData())
        oRange.getColumns().optimalWidth = True
        oRange = oSheet.getCellByPosition(0, iNumRows+1)
End Sub


Sub ResultSetToData(oResult, oDoc, sFunc$, oData())
        Dim oData2()  'Temporary row data array.
        Dim oMeta      'Each result set also has meta-data.
        Dim n As Long  'Number of columns returned by a result set.
        Dim i As Long  'General index variable.
        Dim nRowCount& 'Count of the number of rows.
        Dim x          'Generic work variable.
        Dim oSheets
        Dim controlSheet
        Dim controlCell
       
        oSheets=ThisComponent.sheets
        controlSheet = oSheets.getByName("Uitleg")
        controlCell = controlSheet.getCellByPosition(2,13)
       
        If IsNull(oResult) OR IsEmpty(oResult) Then
                oData() = Array(Array(sFunc, "NULL result set returned"))
                Exit Sub
        End If

        oData() = Array()
        Do While oResult.next()
                If n = 0 Then
                        REM Add column titles.
                        oMeta = oResult.getMetaData()
                        n = oMeta.getColumnCount()
                        oData2() = DimArray(n)
                        For i = 1 To n
                                oData2(i-1) = oMeta.getColumnName(i)
                        Next
                        REM Advanced programming concept....
                        REM Do not replace the next two lines with:
                        REM      oData() = Array(oData2())
                        REM because it would use a reference to oData2().
                        REM AppendToArray() copies the array by value.
                        AppendToArray(oData(), oData2())
                End If

                REM The first column is always the function name.
                oData2() = Array()
                oData2() = DimArray(n)
   
                REM Although the row number should be available using
                REM oResult.getRow(), this is not always the case.
                nRowCount = nRowCount + 1
                For i = 1 To n
                                oData2(i-1) = oResult.getString(i)
                                controlCell.setValue(nRowCount)
                Next
                AppendToArray(oData(), oData2())
        Loop

        REM In case no rows are returned...
        If n = 0 AND nRowCount = 0 Then
                oData() = Array(Array(sFunc, "No rows returned"))
        End If
End Sub

Sub AppendToArray(oData(), ByVal x)
        Dim iUB As Long  'The upper bound of the array.
        Dim iLB As Long  'The lower bound of the array.
        iUB = UBound(oData()) + 1
        iLB = LBound(oData())
        ReDim Preserve oData(iLB To iUB)
        oData(iUB) = x
End Sub


Sub SchoonQueryOutput()
        REM ***
        REM * Variabelen declaraties
        REM ***
       
        Dim QueryOutputSheetName As String
        Dim InVerwerkingSheetName As String
        Dim VerwerkSheet
        Dim QuerySheet
        Dim VerwerkPDUCells
        Dim VerwerkPDUArray()
               
        QueryOutputSheetName = "QueryResults"
        InVerwerkingSheetName = "InVerwerking"
       
        VerwerkSheet = ThisComponent.Sheets.getByName(InVerwerkingSheetName)
        QuerySheet  = ThisComponent.Sheets.getByName(QueryOutputSheetName)
       
        VerwerkPDUCells=VerwerkSheet.getCellRange(1)
       
End Sub

schneidz 09-16-2014 10:56 AM
would handling the odf via a bash script be more (or less) sane ?

Ramurd 09-17-2014 04:27 AM
Quote:
Originally Posted by schneidz (Post 5239151)
would handling the odf via a bash script be more (or less) sane ?
I'm not really sure what you mean, so I guess insanity kicks in at one point or another. I'm really fond of doing things with a script, preferaby called from a cron :-) That would make my sheet available with most recent data at the start of my working day. There's a few other things I have to do as well; e.g. find a good way to place this sheet in a central place where the sharing mechanism works, so we can all benefit from having the same working set available.

It appears sharing via sshfs does not really work; might be nfs4 would be a better option; otherwise we can try with samba.

schneidz 09-17-2014 08:47 AM
what i mean is you can unzip an *.ods and maybe parse thru the xml in content.xml.


All times are GMT -5. The time now is 12:38 PM.