This post is about a script that exports OmegaT project to an XLS document with a separate worksheet for each source file.This script has been requested and kindly sponsored by cApStAn sprl
The scripts requires jxl.jar from JExcelApi located in the scripts folder (tested with version 2.6.10). Without the jar it won’t run.
The script itself needs to be downloaded here: write_excel_multiple_sheets.groovy.zip, and extracted to OmegaT scripts folder.
Each time the script runs, it creates a XLS file inside the script_output subfolder in your project’s folder. The filename will be based on the project name and language pair (for instance, Missale_Romanum (LA - SA).xls or omegat-ru (EN - RU).xls)
The first worksheet of the resultant XLS file is sort of a mastersheet with links to all the other sheets, which in a way is similar to Project Files window in OmegaT:
Clicking on a filename or the sheet name in the list will get you directly to the corresponding file. To get back to the mastersheet, you need to click on the filename in the upper part of the sheet (B1).
The script will inform about its results in OmegaT status bar and Scripting window console.
The script is localizable (so make sure you put the .properties file along with the script itself, either to properties subfolder, or on the same level where .groovy file is). Localization covers both script’s messages and a few captions in the resultant file.
Comments, suggestions, bug reports and friendly hugs are most welcome.
UPDATE: Briac Pilpre, whose ideas were used in the script, made a modification for a “horizontal layout” of the sheets, i. e. Segment # | Source | Target. His version can be found at this pastebin link. Setting the variable “threeColumnsStyle” to false in line 34 of the linked script (def threeColumnsStyle = false) restores the “vertical layout” of the output file. Thank you, Briac, for the original idea to use jxl library, and for the current addition.
But as of now,
Good luck
True Translation 
A big thank you for making this useful script, and congratulations and a friendly hug of course 🙂
Thanks a lot Kos ~ This is very useful. The OmegaT alignment works fine as well.
I’m really happy about the aligner, but, really, I didn’t do anything to have it in OmegaT or to have it the way it is. It’s the way out of my skills level.
Yet another superb blog for OmegaT enthusiasts. Большое спасибо!
Большое пожалуйста, пользуйтесь на здоровье!
I cannot make it work :S It says: Se está ejecutando el programa “/Applications/OmegaT_4.0.0_Beta_Mac/OmegaT.app/Contents/Java/scripts/write_excel.groovy”…
Se produjo un error
javax.script.ScriptException: java.lang.ClassNotFoundException: jxl.format.Alignment
Even if I have jxl.jar into /scripts :S any idea?
My mistake, sorry.
Line 36, where it says,
def jxlJar = new File(Preferences.getPreferenceDefault(Preferences.SCRIPTS_DIRECTORY, “.”)+File.separator+”jxl*.jar”).toURI().toURL()
remove that asterix in jxl*.jar
I updated the script at SF.net repository, so you can redownload it just as well, but it’s the only change.
Practical piece – I am thankful for the specifics – Does anyone know where my business might obtain a fillable a form copy to complete ?
Hi Kos,
it has been a while since I posted something here. I hope you are doing great.
I am testing OmegaT-4.1.4 on a clean machine (with java 1.8.0_161) and wanted to test all the scripts available.
This script gives me following error:
Das Skript “/home/james/OmegaT/scripts/write_excel_multiple_sheets.groovy” wird ausgeführt…
Fehler
javax.script.ScriptException: javax.script.ScriptException: java.lang.ClassNotFoundException: jxl.format.Alignment
I tried jxl.jar versions from 2.6.10 to 12. Always with the same result.
Could you please help me out here? 😉
Thank you in advance and warmest greetings from Tirol.
Konstantin
Damn, I didn’t expect my e-mail to show up like that.
I made a mistake and entered in both fields my e-mail ;-(
Could you please replace it with my name?
Thank you.
Sorry to have bothered you Kos,
it seems I was just overworked and tired.
Everything works fine now. I obviously oversaw something.
Please do not forget to take out my e-mail.
Thank you again for your blog and scripts.
Thank you for the comments and for the follow-up report. I’m happy you find some of my scribbles useful.
Hi Kos,
As I already mentioned, I spent the last 10 days trying to get a grasp of the finer functions of OmegaT and the scripts.
I am just blown away… (btw. I am also experimenting with the DGT-OmegaT scripts, I take them, adapt them accordingly and then test them for usage with OmegaT 4.1.4, which I still rather use).
Now, I decided to take the script-version of Briac, made two copies of it (1. write_excel_multiple_sheets_Horizontal.groovy and 2. write_excel_multiple_sheets_Vertical.groovy), with the necessary changes of course, and now I can use whatever I need. I also changed each output-name so they do not overwrite it other every time (eg: xlsfilename = projectname + ” (Horizontal-$srcCode-$tgtCode).xls”).
My questions (and requests) are:
a) is it possible and easily doable to have the appropriate locale for each cell/column according to the source and target language? This way the correct spelling-check would take place instead of having one column completely underlined.
b) could there also be one more column with ALL the “Notes”
c)could there also be one more column with ALL the “Comments”
I know we have the scripts that export notes and comments, but they do that filtered and to an HTML-format.
I would rather have an Excel-file, or EVEN BETTER a LibreCalc-file, because it is much easier to use and manipulate them.
Thank you for your time Kos
Konstantin
Hi.
Everything you need to export notes is in the script that does that. So it’s just a matter of using that piece of code and adding it to this script, and telling it where to place the data. If you look at how other information is placed in Excel file, you’ll see that it’s pretty easy to add new cells. So I’ll leave you with that. At the moment don’t have any particular interest in expanding these scripts, but you’re more than welcome to adapt them as you see fit.
OK Kos, 😉
that is exactly what I have been doing for the last couple of days. “Stealing” code from one script and pasting it in the other one.
The only thing is, I never really learned to script, or java, but I like to experiment and test things. So, I suppose this is a good time to get to know those things a little better…. (I was just hoping you could give me some more concrete tips and directions, anyway…)
When I successfully have done some modifications I will report back and give you the changed scripts, so that you can post them.
Thank you again Kos, I’ll be in touch.
That’s exactly my situation a few years ago. To a big part it still is this way now, I still look up pieces of code elsewhere and put them together as scripts, only now I feel much more comfortable doing that
Hi Kos,
I really am sorry to bother you again with this, but I need just one (very important) piece of information.
I think I know where to change and add code, but I really can’t know the names of the needed fields.
What I mean is, the code in question is (mainly) this (as far as I can say):
if (threeColumnsStyle) {
sheet.addCell(Number.newInstance(0, count, ste.entryNum(), segFormat))
sheet.addCell(Label.newInstance(1, count, source, sourceFormat))
sheet.addCell(Label.newInstance(2, count, target, targetFormat))
count++
So, I want to try adding something like this:
sheet.addCell(Label.newInstance(3, count, notes, notesFormat))
sheet.addCell(Label.newInstance(4, count, comments, commentsFormat))
But “notes”, “notesFormat” and “comments”, “commentsFormat” is not the correct naming as far as I can say.
What should I use?
More importantly, is there documentation or a list somewhere with all the naming used in OmegaT?
I absolutely need such a list, without it I can’t even bother starting to play around with coding and scripting.
Thank you for your patience with me.
Wow, thank a lot. This is so cool!
I, too, am no coder, but would definitely love to see comments/notes added to the resulting Excel files. Will try to figure it out if I have some time to spare, although I doubt I will manage as I can just about manage an AutoHotkey script 😉
Michael
PS: I am also currently discovering all the amazing things added to DGT-OmegaT (super easy merging/splitting of segments, paragraph markers, etc.), which is pretty amazing.
Hi!
I’m trying to run that alternate 3-column script from pastebin. Unfortunately it gives me error which I’m unable to correct (not a programmer here…).
1. javax.script.ScriptException: javax.script.ScriptException: java.lang.ClassNotFoundException: jxl.format.Alignment – that one I was able to bypass by changing jxl-2.6.10.jar in the code to jxl.jar (a reference to a file as I understand).
2. Then it pops up this: javax.script.ScriptException: javax.script.ScriptException: java.util.MissingResourceException: ResourceBundle (.properties file for localization) is missing.
That part I’m unable to fix in any way. Would you be able to take a look?