Solved

add module to calc

Posted on 2014-02-02
32
415 Views
Last Modified: 2014-05-19
How do I add a module to LibreOffice Calc?
0
Comment
Question by:dwcronin
  • 14
  • 8
  • 5
32 Comments
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39828311
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.
0
 

Author Comment

by:dwcronin
ID: 39832909
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.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39834449
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.
0
 

Author Comment

by:dwcronin
ID: 39863230
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.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39864749
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.
0
 

Author Comment

by:dwcronin
ID: 39865804
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39866318
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.
0
 

Author Comment

by:dwcronin
ID: 39868241
Thank you.  I didn't like asking for points back.
0
 

Author Comment

by:dwcronin
ID: 39873845
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?
0
 

Author Comment

by:dwcronin
ID: 39880640
thank you
0
 

Author Comment

by:dwcronin
ID: 39910499
i'll try to today
0
 

Author Comment

by:dwcronin
ID: 39915115
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?
0
 

Author Comment

by:dwcronin
ID: 39931653
thank you.  I'll keep watching.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 19

Accepted Solution

by:
Ken Butters earned 500 total points
ID: 39959006
I'd like to find out if I understand your question correctly.

I reviewed the original question here : http://www.experts-exchange.com/Programming/Misc/Q_28277999.html

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?
0
 

Author Comment

by:dwcronin
ID: 39965334
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
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39966645
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?
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39968139
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.
0
 

Author Comment

by:dwcronin
ID: 39994598
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.
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39994718
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.
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39994736
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/
0
 

Author Comment

by:dwcronin
ID: 39994917
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.
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39995010
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.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39998176
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.
0
 

Author Comment

by:dwcronin
ID: 39999575
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
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40000912
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
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 40074070
dwcronin,

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

Author Comment

by:dwcronin
ID: 40075422
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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
The viewer will learn how to edit the master slide. They will also learn how to combine multiple themes into one master slide to use them in their presentation.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now