Help answer threads with 0 replies.
Go Back > Forums > Linux Forums > Linux - Newbie
User Name
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!


  Search this Thread
Old 11-11-2016, 04:26 AM   #1
Little Hal
LQ Newbie
Registered: Sep 2011
Location: Leicestershire, UK
Distribution: Was XP -Still Experimenting with Linux
Posts: 7

Rep: Reputation: Disabled
Question Open Office Formula Corrupts in Linux

Hi, I had a few tries at dumping Windows and switching to Linux.

My one remaining problem is related to spreadsheets (of which I have several hundred). When I last tried Linux (I can't remember now which flavour) I thought "THIS IS IT!" so I bit the bullet, powered down, plugged in my data drive and started opening my various files. Every thing worked ok until I opened a spreadsheet with (I think) Libre. To my horror every single formula had been prefixed with of:
Presume this related to the fact that my spreadsheets had been created with Open Office.

But how can I overcome this?
I put my drive back into Windows & did a search & replace to get rid of the f but how can I stop this happening in Linux?

Little Hal
Old 11-11-2016, 08:16 AM   #2
Registered: May 2011
Location: Texas
Distribution: Primarily Deb/Ubuntu, and some CentOS
Posts: 831

Rep: Reputation: 229Reputation: 229Reputation: 229
This has to do with MS Excel and LO Calc. Some formulas may not transfer from Excel to Calc so you will have to redo some of the formulas for them to work. This is not a Linux problem.

Edit: Wait, they were created with Open Office, and the formulas don't work with LO Calc? Havnt heard that one before.

Last edited by erik2282; 11-11-2016 at 08:23 AM.
Old 11-12-2016, 11:21 PM   #3
Registered: Nov 2015
Distribution: Debian Sid
Posts: 119

Rep: Reputation: 51
There's a good chance that a much better solution is out there, somewhere. But here's the not-beginner-friendly approach that I would take. Backup your files if you try this, you're almost certain to mess it up at least once.
1) Create a test spreadsheet in Libre Office with the sort of formula you're having trouble with and save it to the format that your spreadsheets are in. I made one with numbers in A1, A2, and A3, and then summed them.
2) unzip filename.xlsx
3) For me, this created the dirctory xl. The actual data is at xl/worksheets/sheet1.xml. If I open this file (I used emacs, but an xml editor would let you see it better) I can see the following string half way through it: SUM(A1:A3)
4) Now open one of your spreadsheets from excel in the same way and try to find the analogous formula. For this example, let's say you find of:SUM(A1:A3)
5) Cook up a sed command to transform the formula from the old format to the new format. For this example, you might try:
sed -i "s/of://g" sheet1.xml
The format here is "s/<find>/<replace>/g". If you post the old format and the new format, we can help you write this. Sed is very flexible with moving bits around and such, this is exactly the sort of case it's used for.
6) Once you get it to work for one file, write a bash script to do this to all of them. My bash is rusty, I'm sure this wouldn't run, but it gives you the idea. If you get to this point and need help writing a working script, let us know. And as before, do this on copies, be sure not to mess up your originals.
cd ~/spreadsheets
mkdir tempdir
for i in `ls` do
    unzip $i -d tempdir
    rm $i
    sed -i "s/of://g" tempdir/xl/worksheet/sheet1.xml
    cd tempdir
    zip -r ../$i ./*
    cd ..
    rm -r tempdir/*
So, if you're comfortable with the bash command line and scripting, the only hard part is finding the text to replace. Think of it as an exercise to help you get used to Linux

Last edited by crazy-yiuf; 11-12-2016 at 11:25 PM.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Open Office Calc Formula to Computer Time Duration tailinlinux Linux - Newbie 7 06-28-2012 02:47 AM
Help Please - Open Office CALC Formula's SangrelX Linux - Software 1 06-23-2009 09:42 PM
open office formula editor Four Linux - Newbie 1 03-30-2006 09:46 PM
how do I display a Linear regression formula using Open Office? izquierdista Linux - Software 2 07-20-2005 06:51 PM
elapsed time formula for open office calc philosophe Linux - Software 1 03-30-2005 05:32 AM > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 10:48 AM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration