Category: OpenOffice

Posted on

Another LibreOffice Fix

Most of the embedded systems I design output data in a CSV format for performance analysis, monitoring and debugging. So I add LibreOffice Calc to the CSV files “Open with” Explorer option for convenience. When moving from OpenOffice, or upgrading LibreOffice versions, the file association is sometimes broken do to left over registry keys pointing to the wrong place for scalc.exe.

In the past I would manually delete the left over entries using RegEdit, a rather slow and boring task. This post on the OO forum points out the easiest method of fixing this issue, using CCleaner. I like to use the portable version because I rarely run the program so by the time I want to run it again I need to get the latest version anyway.

Posted on

LibreOffice 3.6 series causing Win7 Explorer Crashes

I really like LibreOffice so recently I upgraded to the 3.6 version on my Win7 64 Pro PC. Soon after upgrading I noticed some major slowdowns but since I was working on humongous data analysis spreadsheets I assumed it was my abuse of spreadsheets. The spreadsheets contain thousands of data points that are charted and have basic statistics calculated. Normally I’d take the time to do analysis and graphing in R but, since I don’t anticipate needing to re-run these analyses I got lazy and just used Calc.

Last night I noticed that when I opened one of the spreadsheets in Calc, Windows Explorer started continuously accessing my NAS file server. Even after closing Calc the network access continued and then Explorer crashed when I tried to use any explorer function (e.g. empty the recycle bin). After a re-boot Explorer was still doing the excessive network access but after a few minutes it stopped. So I tried just opening Calc without opening a spreadsheet from the file server. The problem started right up indicating that this was not a problem with massive spreadsheets as I had guessed.

This morning I decided to investigate more and discovered these two somewhat related problems in the LibreOffice bug list (weird, the bug list server went down as I was testing the links). It turns out there is a bug in the LibreOffice Explorer shell extensions DLL, some users recommended using the command line tool to un-register the DLL but, one user said he solved his problem by re-installing LibreOffice and disabling the shell extensions option. This made me think that the easiest way would be to use the change feature of the Windows Installer. I tried it out and it worked perfectly fixing the problem. Here are step by step screen shots of this easy way to fix this problem.

ScreenShot01ScreenShot02ScreenShot03ScreenShot04ScreenShot05ScreenShot06ScreenShot07ScreenShot08ScreenShot09ScreenShot10

The rest of the steps should be obvious.

Posted on

Interactive Time Lines

I was trying to estimate the date/time for undated events in an 18th century travel diary. Realizing it would help to use OOCalc for date time math such as daily totals and speed estimates, I set up a spreadsheet for the purpose. As I worked on the spreadsheet I thought it would be great to see a time line graph to check my work for errors. Searching around it became clear that none of the built-in graphing functions in spreadsheets would work for creating a time line graphic. It appears that most people simply use drawing tools to make time lines.

Then I found the Simile Widgets Timeline component a versatile JavaScript based solution. Soon after I got a time line going it became clear that keeping the data file in sync with the changing spreadsheet data was cumbersome. With all the data already in OOCalc I decided it would be nice to output formatted data directly from the spreadsheet for use in the timeline.

With my success in creating a spreadsheet for this one project I decided to create a generic version of the spreadsheet that covers all time line attributes. The spreadsheet contains a final version of my custom Calc functions for time line JSON data creation that handles all time line data options (download the spreadsheet here). To test out my new OOCalc functions I created a time line of English and British Monarchs (also useful for my research) starting with my spreadsheet sample. I haven’t finished adding text excerpts to the monarchs time line but it does have pictures and Wikipedia links for all items.

Last month while organizing notes from a tour of historic sites I found I had not recorded the dates and times in the notes. As I was giving myself a dope slap for failing to record the times it dawned on me that the photos I took would give me the missing time information. While I was viewing the photos to get the times from the EXIF data it occurred to me that I could set up nearly automatic time line generation using my spreadsheet and the command line ExifTool.

To start you open a command prompt in the directory with your photos and then run the following command line.

"C:Program FilesEXIFtoolexiftool.exe" -p "$filename, $createdate" -q -f -d "%Y/%m/%d %H:%M:%S" . >PhotoTimes.csv

This creates a CSV data file containing the image filename and creation date that can be opened by OOCalc. Using the spreadsheet you generate a JSON data file to give you a time line as shown in these screenshots.

TimelineScreenshot1

TimelineScreenshot2

I’ve put a zip archive with the files used to create the photo time line on my site. Download the archive from this link.

The archive contains these files for the Photographs Time Line:

PhotoTimes.csv output from the ExifTool run
PhotoTimeline.ods spreadsheet for creating the JSON data file
PhotoTimes.js the JSON data file
PhotoTimes.html HTML page for displaying the time line
PhotoTimes.css CSS for better control of image size

See Also:

Simile Widgets Timeline documentation

Posted on

OpenOffice Calc Tips

It’s been two years since I switched from MS Office to OpenOffice for all my word processing, spreadsheet, presentation and local database needs. I had been using OpenOffice for all my personal documents for a number of years prior and with the advances in Calc’s functionality I was pretty sure it could handle my professional work as well.

After two whole years I can confidently say that OpenOffice Calc does everything I need as well or better than MS Office. Over this period, due to changed business needs, I’ve had to spend more time with spreadsheets than in the previous ten years. With the much heavier usage I was concerned that I would eventually hit a need for some feature of Excel that is not yet implemented in Calc. To my delight I have not found any missing features and my employer got a productivity bonus because I haven’t had to spend time learning the new Microsoft UI.

Lately I’ve gotten around to writing custom functions for Calc and had quite a bit to learn because this is the one area where decades of 123 and Excel experience is not much help. I’ve put some tips and custom functions in a Function Demos Spreadsheet that you can download. One spreadsheet feature that I’ve found to be essential for long term maintenance, that most people don’t use, is named ranges. If you haven’t used named ranges in Calc spreadsheets I recommend reading the tips linked at the end of this post.

Recently I needed to work with a Calc document I had constructed a year and a half ago. While I had used named ranges extensively making it easier to read the formulas, I wanted to change the range names as the abbreviations I had used weren’t as clear as I’d like. With dozens of named ranges defined across multiple sheets and each used on other sheets it wasn’t going to be easy to edit them one at a time. to manually edit a couple range names all you have to do is:

  1. Go to the menu item Insert->Names->Define .
  2. Select the Name you want to change.
  3. Copy the range definition from the Assigned to text box.
  4. In the name text box change the name.
  5. Paste the range definition in to the Assigned to text box.
  6. Then click the add button, you now have two names for the range, click OK to close the dialog.
  7. Use Edit->Find & Replace to change the old name in formulas to the new name.
  8. Go to the menu item Insert->Names->Define .
  9. Select the old name and click the delete button.

While this process is workable for changing a few names, it’s faster to do than to describe in text, it is not really efficient when you’ve got dozens of names to change. To aid with managing the multiple changes I created a custom array function that creates a list of all named ranges, it’s in the Function Demos Spreadsheet. After entering the array function in a new sheet, select the whole array and use the Data->Text to Columns… function to make the name list static. Next copy the name list to the column to the right, and edit that column to the new names you want to use. You now have a neat two column list of old and new range names that you can use to copy and paste names from for the Insert->Names->Define and Edit->Find & Replace dialogs.

General Info

Range Names Tips

Other Tips