Libre/Open Office project; could use some help, suggestions
ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
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.
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195
Rep:
Quote:
Originally Posted by Ramurd
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.
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
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.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.