Link to home
Start Free TrialLog in
Avatar of dwcronin
dwcroninFlag for United States of America

asked on

add module to calc

How do I add a module to LibreOffice Calc?
Avatar of ltlbearand3
ltlbearand3
Flag of United States of America image

I need some more clarification on what you mean by add a module to LibreOffice Calc.  Are you wanting to add a module in code, add and separate add on, ????  Maybe some more details on exactly what you want to accomplish will help.
Avatar of dwcronin

ASKER

I asked a question titled:"Format an ods document"  on 2013-10-26 that was answered by FishMonger.  The answer tells me to add the module "Spreadsheet::WriteExcel" if I want to use perl to edit a csv file and output a formatted ods readable document.  I gave points for that solution but I can't figure out what to do.  I bought my mom a kindle and she reads lots (nice term) of harlequin books about cowboys.  I am trying to create a spreadsheet that changes between white text on a black background and black text on a white background when the author changes.  I am trying to do this as a perl script/open office calc macro.  Learning to write the code seems like fun to me.  She enjoys reading cowboy books.  If you see a vampire book, I bought my sister a kindle too.
Well, you may need to request a moderator to help expand out the topic to include perl as I am not a perl programmer.  The module referenced in your other question may work to read a csv file, but my research is that it will not create the ods document for you.  The title is WriteExcel.  After a little research I suggest trying to use the Open Office Module "OpenOffice::OODoc."  You can read about its properties at this link.
Bear,
How do I get a moderator to reopen my Perl/ods question?  I'm not married to the idea of Perl; it just seemed like an appropriate/good language to learn.  I'm more interested in writing a program to format the ods document.  My mom probably reads 3 books/week and I don't want to keep reformatting the list of kindle books when I look at it but the formatting is helpful.
You should see a little icon under your original questions that states "Request Attention."  Click on that link and you should be able to send a message to a moderator.
I am sorry.  I did not read the question close enough to understand that you wanted to re-open your other question. I thought you wanted to expand this question out to using perl.  Unfortunately for you, once a question is closed it cannot be re-opened.  What you might want to do is request this question get deleted and ask a new question referencing the other question.  Just make sure you include perl in the topics.
Thank you.  I didn't like asking for points back.
I've requested that this question be deleted for the following reason:

The Experts (Fishmonger specifically) helped me on a question ("format an ods document").  I mistakenly thought I had enough information to  solve my problem and prematurely closed it.  I later tried to use the info to solve the problem but ran into difficulties  with the solution.  Could I please reopen my question and state the difficulties I'm having?  I would like to close my currently open question so I can state the question as one piece rather than breaking it up?
thank you
i'll try to today
1) I  went to:
www.cpan.org
2) I read:
"Installing Perl Modules"
3) From a terminal, I typed:
cpan App::cpanminus
4) Following Fishmonger's direction, I added:
cpanm Spreadsheet::WriteExcel
5) I looked at the examples Fishmonger suggested:
http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.39/lib/Spreadsheet/WriteExcel/Examples.pm
6) I downloaded "a_sample.pl".
7) I've tried:
perl a_sample.pl
and
chmod +x a_sample.pl    -->  ./a_sample.pl
it does not create the spreadsheet screen.  
What did I do wrong?
thank you.  I'll keep watching.
ASKER CERTIFIED SOLUTION
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ken,
I think Fishmonger told me not to let the word "Excel" scare me and my ods list of books could be formatted with the cpan Perl subroutines that have the word "Excel" in them.  I have a "listofbooks" file that changes frequently (3 times per week) and I don't want to manually reformat it every time.  I thought the easiest way to reformat it would be to:
1)  mom reads book
2) I use libreoffice calc to add:
      new book title
      author
      date purchased
 to the end of "listofbooks.ods"
3) re-sort the new list using calc
4) save the file in CSV format
5) run a perl script on "listofbooks.csv" to create a new and formatted "listofbooks.ods"

I thought I could write the Perl script but I was wrong.  I used to be a c/c++ coder and thought the new language would be fun but I'm having trouble and she won't quit reading DAMN cowboy books! :)
listofbooks.csv
listofbooks.ods
I think step 5 is the problem.   I don't think that you will be able to use  Spreadsheet::WriteExcel to create an "ods" file at all.  you can only use it to create an excel file.

Once you do that, if you want it back in "ods" format, I think you'd have to open it back up with LibreOffice Calc, and then resave the xls as an ODS file.

I am fluent in MS Excel's macro language, but not Libre's  macro language.  I took a quick peek at it, and they don't seem too terribly different.  --- meaning I don't think it would be that hard to figure out.

It looks like LibreCalc has a macro recorder, so I am thinking a better overall solution for you would be to record a macro in LibreCalc, (recording your reformatting steps), and then use that as a baseline code example to write your macro.  That way you'd be able to reformat your spreadsheet without ever having to convert it to any other format at all.

Does that sound like an acceptable way to go, or were you more interested in the Perl solution for Perl's sake?
Ken,

Just so you know there are a few languages that can be use to code in LibreOffice Macros.  The default and most often used is the base language.  This is very similar to VBA used in Excel but does have some gotcha's to watch out for and you do not get intellisense to help you along the way (although there are some tricks some use to help with that - I just prefer the internet search when I need it).  My understanding was dwcronin wanted this in Perl for the sake of learning Perl.

Dwcronnin,

I am still monitoring this thread, and if you decide to change and switch over to the basic coding language in Libreoffice, I can jump back in and help.
Great!  What is the coding language of LO calc (macros I assume)?  I  would rather learn that than Perl since I always use Calc.  That would be more convenient to just hook a macro up to a button to do the coloring and not have to leave to leave my book list spreadsheet.
From what I am reading about LibreOffice... you have two choices.

1) Python
2) LibreOffice Basic

Python seems to be the language of choice for a lot of developers out there due to the fact that it is more robust than LibreOffice Basic, and it is a language that once learned is applicable in other settings.

... Like Perl... Python is used outside of LibreOffice... as an independent language, but unlike Perl... Python can be used within LibreOffice as well.

From what I can see, Python might be a little more complicated to code within LibreOffice, but seems like the long run benefits make it worthwhile.
Ok... turned up these alternatives as well:

LibreOffice internally supports the following scripting languages:
LibreOffice Basic
JavaScript
BeanShell
Python

If you already have familiarity with one... then that might help sway you one way or the other.

Here is a reference you will probably need for API / Documentation : http://api.libreoffice.org/
Ken,
I will hurry up and learn python.  I hope that it's not too unlike C other than syntax.  I bought python in 24 hours.  So I'll read it today.
I've been studying Python myself recently due to a desire to create a plugin for Calibre... a free ebook reader which is written in python.

If you are familiar with C,  then I don't think you'll have any trouble with Python.
dwcronin,

I looked at your sheet and I don't know what the rules are for the formatting.  I cannot help you code in Python either, but the LIbreOffice Basic is what I use when coding in LibreOffice or OpenOffice.  If you want to post some of the rules of what you are trying to accomplish with the formatting, I can help guide your through the basic code.  I just need to understand the logic of the formatting.  For example how does the code know when to format a cell and to what format?  You could even post some pseudo code to help describe the process.

If you are now at the stage where you just want to make this work, I can get your there through the basic code.
Bear,
Like I told Ken Butters I would, I read "Python In A Day: Learn the basics, Learn it quick, Start coding fast (In A Day Books)" over the weekend.  It looks like I could pick it up if I have to but that's not really the goal and I already know BASIC.  I would love to use a BASIC program if it will do the job.  I am using Ubuntu 13.10 and libreoffice calc.  I am trying to make a program that will switch between:
   1) white text on a black background
   2) black text on a white background
when the AUTHOR changes

There are three columns in my spreadsheet:
1) book title
2) book author
3) book purchase date
But the coloring only depends on AUTHOR change.  
You answered my former question "add button to libreoffice calc" on 2013-12-21 with a BASIC script that I use alot!  BASIC would be fine.  
I'll include the csv version of my sheet, if this is necessary. but it's nicer if I don't have to leave calc.
listofbooks.ods
listofbooks.csv
I had wanted to help walk you through this code, but it ended up being a little complicated since we have to code the macro to actually watch for the change event and setting colors is not the most intuitive.  Therefore I just wrote the code and will help walk you through what I did.  Hopefully this will give you a sense of the LibreOffice Basic code.

In LibreOffice if you want to watch for a change in a cell, you must set up a listener which is done through the CreateUnoListener object.  You then have to assign the listener to the cell or range to which you want to listen.  Then you have to take that subroutine and assign it to an event in Calc.  For your example you want to listen to Column B.  Therefore you end up with a routine like this:

' Have to use the Listener to react when a cell is changed.
' In Tools > Customize > Events Tab you need to asign the
' Start_ListenerOnCell macro to the Open Document Event

' Add this to the Open Document Event
Sub Start_ListenerOnCell
    Dim oListener as Object
    Dim oSheet1Cell as Object

	' Here is where we need to define what cells should be watched
	' If you add another object make sure to declare it above
	' Added it to the list below and in the Remove_Listener Module
	oSheet1Cell = Thiscomponent.sheets(0).columns(1)	'Zero Based Column Count 1 = B

	' Create the Listener     
    oListener = CreateUnoListener( "MyApp_", "com.sun.star.util.XModifyListener" )

	' Assign/Register the listener to the Cell or range
	oSheet1Cell.addmodifylistener(oListener)
End Sub

Open in new window


Now we also have to tell it to stop listening and clean up the work with code like this:
Sub MyApp_disposing(oEvent)
 	REM   All listeners must support this event:
   ' Print "disposing of the listener"
End Sub

Open in new window


As noted in the comments you have to assign the listener to Open Document event through Tools >> Customize >> Events

Next we have to tell the Macro what to do when it "hears" a change:
Sub MyApp_Modified(oEvent)
	Dim oSheet as Object
	Dim oRange as Object
	Dim oCellReference as Object
	Dim lngColor as long
		
	' Get the cell that actually changed
	oSheet = thisComponent.getCurrentSelection.getSpreadSheet
	oRange = thisComponent.getCurrentSelection.getRangeAddress
	
	' Get the Reference to Update
	oCellReference =  oSheet.getCellByPosition(oRange.StartColumn - 1,oRange.StartRow)
	
	' Check if background is currently White.  If so, make it Dark
	If oCellReference.CellBackColor = -1 Then
		oCellReference.CellBackColor = RGB(0,0,0)
		oCellReference.CharColor = RGB(255,255,255)
		oSheet.getCellByPosition(oRange.StartColumn,oRange.StartRow).CellBackColor = RGB(0,0,0)	
		oSheet.getCellByPosition(oRange.StartColumn,oRange.StartRow).CharColor = RGB(255,255,255)	
		oSheet.getCellByPosition(oRange.StartColumn + 1,oRange.StartRow).CellBackColor = RGB(0,0,0)	
		oSheet.getCellByPosition(oRange.StartColumn + 1,oRange.StartRow).CharColor= RGB(255,255,255)	
	Else
		oCellReference.CellBackColor = -1	
		oCellReference.CharColor = RGB(0,0,0)
		oSheet.getCellByPosition(oRange.StartColumn,oRange.StartRow).CellBackColor = -1	
		oSheet.getCellByPosition(oRange.StartColumn,oRange.StartRow).CharColor = RGB(0,0,0)	
		oSheet.getCellByPosition(oRange.StartColumn + 1,oRange.StartRow).CellBackColor = -1
		oSheet.getCellByPosition(oRange.StartColumn + 1,oRange.StartRow).CharColor= RGB(0,0,0)	
	End If
End Sub

Open in new window


The CellBackColor property sets the background color of the cell (a value of -1 is the same as No Fill).  The CharColor sets the color of the font.  I use RGB to make the code a little more readable as more people can figure out what color is being set from those values.

You can view code by pressing ALT-F11 and expanding the selections to chose the module.  I have attached an updated version of your spreadsheet with the macro in place.

Now whenever something is changed in the author column it will switch between black and white.  It does slow up opening the document as it sets up the listener.
listofbooks-withCode.ods
dwcronin,

I am little confused on why you chose the answer that you chose.  Could you please explain?  Thanks.
sure.

It seems my question is harder than I initially thought.  I thought it would be a half hour question for whoever knew how to do it and then I thought I underestimated it and should close it.  My pseudocode (sorry but 25 years since I learned pseudocode) is:
----------------------------------------------------------------
newLine.backgroundColor = black   // the color is irrelevant.  I'm just trying
newLine.textColor = white                 // to make it obvious when the author changes
while (newLine.lineNumber < EOF)
{
    read newLine
   if(newLine.author <> oldLine.author)
   {
      if(newLine.backgroundColor == white)
         {
           newLine.backgroundColor = black
           newline.textColor = white
         } // end newLine.backgroundColor == white
     else
        {
           newLine.backgroundColor = white
           newline.textColor = black
        } // end newLine.backgroundColor == black
      oldLine = newLine
    }  // end newLine.author <> oldLine.author)
--------------------------------------------------------------------------------------------------------------
I don't know how to make CALC understand how I want to format this long list of authors that changes every 2 or 3 days.  I could do it manually but the list is long and changes often.  Manually would be tedious and error-prone.  I don't know if I should write a macro or use a script (I know C, C++, Java, a bit of BASIC, and I'm trying to learn Perl) that properly formats the ever-changing file.  I'm doing the "weinie-way" and giving up.