add module to calc

How do I add a module to LibreOffice Calc?
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Ken ButtersConnect With a Mentor Commented:
I'd like to find out if I understand your question correctly.

I reviewed the original question here :

From what I understand you were asking how to create on ODS file. (Native OpenOffice spreadsheet Document).

The solution offered in your original question was to use the module Spreadsheet::WriteExcel

If you use that module, it would be as an part of your perl program, not a part of LibreOffice Calc itself.

Also it should be noted that the solution you seem to be pursuing (if you choose to use Spreadsheet:WriteExcel) would be to create an excel file (filetype of xls).  

This module would not create an ods file for you directly.  However that being said, the xls file being created could be opened and read by either openOffice or LibreOffice.

So in the end, are you trying to create an xls file using Spreadsheet:WriteExcel in a perl program that will produce an XLS file which in turn could be opened and read by LibreOffice Calc?
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.
dwcroninAuthor Commented:
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.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.
dwcroninAuthor Commented:
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.
dwcroninAuthor Commented:
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.
dwcroninAuthor Commented:
Thank you.  I didn't like asking for points back.
dwcroninAuthor Commented:
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?
dwcroninAuthor Commented:
thank you
dwcroninAuthor Commented:
i'll try to today
dwcroninAuthor Commented:
1) I  went to:
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:
6) I downloaded "".
7) I've tried:
chmod +x    -->  ./
it does not create the spreadsheet screen.  
What did I do wrong?
dwcroninAuthor Commented:
thank you.  I'll keep watching.
dwcroninAuthor Commented:
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
      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! :)
Ken ButtersCommented:
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?

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.


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.
dwcroninAuthor Commented:
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.
Ken ButtersCommented:
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.
Ken ButtersCommented:
Ok... turned up these alternatives as well:

LibreOffice internally supports the following scripting languages:
LibreOffice Basic

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 :
dwcroninAuthor Commented:
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.
Ken ButtersCommented:
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.

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.
dwcroninAuthor Commented:
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.
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_", "" )

	' Assign/Register the listener to the Cell or range
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)	
		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.

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

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( <>
      if(newLine.backgroundColor == white)
           newLine.backgroundColor = black
           newline.textColor = white
         } // end newLine.backgroundColor == white
           newLine.backgroundColor = white
           newline.textColor = black
        } // end newLine.backgroundColor == black
      oldLine = newLine
    }  // end <>
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.