Scripting language for Calc

[alex72gr]

Hello!
I use LibreOffice Calc in order to create the timetable for my school. Some teachers are not available for some hours, which are denoted with the character “X” in the corresponding cells of the spreadsheet (timetable).

However, I need to enter these data (not available hours for each one of the teachers) in another application, which uses the xml format for its input file. The xml file carries many data and constraints, including the not available hours for each one of the teachers. So, saving the spreadsheet as a csv file does not work, since the xml file has completely different syntax and symbols (although the information is identical)!

What I need to do is write a script in order to “scan” the timetable of my school (the spreadsheet) and then create the xml file with the above mentioned data (with different syntax and symbols than the csv file).

Which programming language the best for this task in terms of simplicity and efficiency? Thanks a lot!

C:\fakepath\sample_timetable_constraints_(xml).odt

sample_timetable_constraints.ods

[SM_Riga]

Hi @alex72gr. Can you please provide sample timetable and XML schema needed for the result? You can export your spreadsheet to xml format via XSLT filter (with some caveats, though), so maybe no any scripting is needed. Spreadsheet by itself is a file in xml format.

[alex72gr]

Hi! I uploaded the sample files. The ods file is the sample spreadsheet file with the time constraints. The odt file is actually the xml file. What I need is a script which “transforms” the spreadsheet information to the xml file (with the specific “notation” and syntax). Since this type of tasks is useful for me, if you think that it would be better for me to learn a programming language, please let me know. This XSLT filter might be a good solution, but Python (for example) might much better.

[SM_Riga]

@alex72gr, thanks for information provided. This, obviously, is not a straight answer to your question, but I am not a programmer, so, if possible, I typically try to achieve the goal with Calc native functionality. As a brain trainer for Saturday, I have created a sample spreadsheet for you, which performs table data transition to XML string. Please find this Demo file attached. Explanations are included, but feel free to ask or comment if something is not clear or should be edited.

Just a remark - usually the goal can be achieved in several ways and using different approaches. If you’d prefer writing you own script for this task with no any previous experience in this area - I would advice Basic as easy to learn and understand. There are plenty of usage examples around and the most of tasks you can face are already discussed in details. And OOO Macros explained by Andrew Pitonyak is some kind of must-read in this case. Sorry, but at this moment I can not be of any help regarding Python or JavaScript - if you are heading in this direction, I am sure other much more experienced users will do the best to help you.

[alex72gr]

Thank you for responding! I checked you Demo file. Unfortunately, I can’t understand how Basic works (I think than mean LibreOffice Basic). But, I’m going to check this Demo file in detail soon. Most likely I need to read the fine manual of LibreOffice Basic, first! Bye!

[SM_Riga]

Thanks for comment @alex72gr There is no any single macro or user defined function involved in the demo, only native Calc tools and functions. Named ranges are used for better readability. Have a nice read.