Stuffing values into a LibreOffice Calc spreadsheet from a text file programatically

Factual Questions
1

I need to automate filling cells in a LibreOffice spreadsheet programatically from a text file that I will attempt to parse. Can anyone recommend a language and a programming environment, preferably free, that will allow me to do this in Windows XP? I’m planning to upgrade to a Windows 7 sooner or later, but my netbook, notebook, and desktop are all running XP for now.

I used to program in c for a living eons ago and would like to learn Java or Javascript (I realize these are only vaguely related). Any recommendations for tutorials or books would be appreciated.

I’ve been screwing around with Eclipse for Android and Java (ran through some tutorials), but am grossly incompetent.

Please respond in small words and speak slowly as I’m pretty much an idiot.

2

LibreOffice comes with a macro language based on Basic - that is probably your best starting point.

Si

3

I have good news and bad news. The good news is, there are a lot of ways you could do this. The bad news is, there are a lot of ways you could do this. :slight_smile:

Calc will read text files natively. It can open both delimited and fixed-width (columnar layout) files, and handles pretty much every character encoding under the sun. I note, with some surprise, that you can even import formulas from text files as long as you keep the cell references straight. For instance, a text file containing this will work exactly as expected:


2.5,=A1*2

Nifty. I don’t think that works in Excel, but I might be wrong. There are also various and sundry options to make it interpret date and currency values appropriately to your locale, and to delimit text values explicitly.

So one option is just to re-parse your (presumably proprietary text format) file into a suitably formatted text file using whatever programming language you feel like using, and then just open it in Calc like you would any spreadsheet. If you go this route, the sky is the limit. There are lots of free compilers and (if you like them), free IDEs for many different languages, even on Windows.

If you want to pick up where you left off with C and/or branch out into C++, you can install MinGW to get a native Windows port of the GNU C/C++ compiler (also Objective C, Fortran, and … something else I’m forgetting). It also includes headers for the Win32 API so you can build both GUI and console Windows apps. In terms of IDEs, there are C/C++ plugins for Eclipse that support MinGW. Code::Blocks is also compatible with MinGW. Purists can also opt to spurn IDEs entirely and manage their projects purely through Makefiles and configure scripts, using vi/emacs/whatever to edit both the code and all the relevant files.

I’m not familiar enough with Java development to suggest any IDEs other than Eclipse, which you’ve already discovered.

Depending on the complexity of the file you are re-parsing, you might even be able to just pull it off with a shell script and a regular expression tool like sed or awk. You can pick up all the standard *nix utilities on Windows by way of Cygwin. This is also an alternative way to get the GNU compiler suite, but programs built that way are (usually) dependent on the Cygwin runtime. That is, you can’t run them on a Windows computer that doesn’t have Cygwin.

As far as books go, check out Bruce Eckle’s site. I know he makes editions of “Thinking in C++” available for free online. He may do the same with “Thinking in Java”. I’m not sure.

The other major route is to use whatever scripting facilities LibreOffice provides. Apparently:


OOo-dev internally supports the following scripting languages:
1. OOo-dev Basic
2. JavaScript
3. BeanShell
4. Python
In addition, developers can use high-level languages, for example Java programming language, to control OOo-dev externally. See the API project at OpenOffice.org.

(For some reason I’m using a dev build of OOO instead of LibreOffice at work, but to-may-to, to-mah-to.)

I’ve never messed with this end of the suite, so I can’t really comment. JavaScript stands out, since you say you might be interested in learning it.

Python is also worthy of mention whether you’re using it as an external solution for reparsing to .csv, or to work directly with LibreOffice’s internal API. Python is free, there are Windows builds , and very easy to learn. Even so, Python is not a mere “toy language”. You can do just about anything with it, really. In my experience, it’s a particularly easy language to use for manipulating both text and binary file input/output. The Python “struct” library is so easy to use that I feel little dirty using it. There are even bindings for the Win32 API if you want to use them – google ‘pywin32’.

4

I do this kind of thing quite a lot, and it depends on what you are really looking to do.

As mentioned, one easy way is to reformat your text into a .csv file, which can then be read by and Spreadsheet. The downside of this is that you don’t have any formatting.

I have done some StarOffice/OOO macro programming, and I found it a big pain in the neck. It was also fairly slow.

The best solution I think is to use Perl. There is a Perl package which can write to spreadsheets (xls format which OOO etc. also can read) natively. We’ve just experimented with this, since my company is a bit paranoid and getting new Perl packages involves an incredible hassle including involving a legal team of the size needed for Supreme Court cases. However our experiments were promising, and Perl is an excellent tool for playing with text.

5

Thanks for the replies! The parsing is from poorly formatted paragraphs so I’ll have to add some reasonable amount of smarts to try to account for the variations, plus some have an additional field which I have to look for. In other words, it’s not any straightforward reading into a spreadsheet, but not very difficult for any programmer or programming language.

I’ll play around some of the options you guys have proposed, and I have actually heard good things about python and perl. Almost all of these languages will be useful to me in the future.

6

The programmatic interface to Open/Libre office is called UNO. I am pretty sure that there is a pyUNO module, so you can inject the text directly into an OO document.