I wrote a plugin that exports to CSV with math formulas in english like =SUM(A1:A10). The problem is that my users (including me) use a different locale, so for them I would need to write something like =SOMME(…). I’d rather avoid to write my plugin in multiple languages (complex + I’ll always forget some), so how could I import a CSV with english formula so that it translates the formula to the locale of the user? (I don’t want to ask my users to change their locale globally) I saw that when importing I can choose the locale of the CSV but it only translates decinal notation, not formulas.
Without sample data, it is impossible to give any advice on how to import csv.
This is what I would do:
- Import the csv manually with necessary options set, so you get the correct result.
- Run the following Basic macro against the correctly imported csv in order to get the right FilterOptions string for your own macro:
Sub showFilterOptions()
Dim args(),i%
args() = StarDesktop.CurrentComponent.getArgs()
for i = 0 to uBound(Args())
if args(i).Name = "FilterOptions" then inputbox args(i).Name,"",cStr(args(i).value)
next
End Subthere are currently no FilterOptions to translate english Formulas from csv to localized Formulas[1]
[1] until »localized Formulas« == »english Formulas«
Switch the formula language, do the import, switch back the formula language. No this requires an office restart.
now you need to write a plugin that imports from csv via:
…
target_cell_range.FormulaArray = …
# …FormulaArray accepts english functionnames and translates to localized names
# »target_cell_range« must be the same size ( in rows, columns ) as the nested input
1 Like
Thanks, but maybe I wasn’t clear enough: I’m writting a plugin for a software (xournal++) in lua that exports into a CSV, this CSV should contain basic formulas so that it can be consumed then by libre office. I’m not sure to understand, why do you point to a python library? And I can’t find the doc for FormulaArray there.
Hallo
- because python can be used directly from Libreoffice.
- with the csv module you can read …csv-files and »translate« it to the structures which are need by …
- … the calc-API-Property …FormulaArray
Ohh so you mean I need to write an additional LO plugin to parse the output of my own plugin… that should work, but it seems fairly heavy for such a simple and common task… Why can’t the CSV import module already deal with this when choosing the locale?
It can, but only with ⇒ Tools ⇒ Options ⇒ LO-Calc ⇒ Formula ⇒ [x]use English Functionnames
as already pointed out by the Link from @Wanderer
This setting is global right? I can’t ask my users to globally change this setting just to import my file as they wouldn’t be able to type formulas in their own languages in any document… + even if it’s local users won’t be able to modify my template with their own language.
@tobiasBora: please copy&paste some lines of your »xournal++« export here. so we can help to write an import plugin for Calc.
The code is there GitHub - leo-colisson/xournalGradeExam: A xournal lua plugin to quickly grade exams · GitHub and produces CSV like:
Questions ex1.1 ex1.2 ex2.1 Total
Points 2 5 3 =SUM(E2:G2)
Eve Bateau eve@bateau.org =100*E2/100 =100*F2/100 =0*G2/100 =SUM(E4:G4)
Alice =100*E2/100 =100*F2/100 =100*G2/100 =SUM(E5:G5)
Bob =0*E2/100 =0*F2/100 =100*G2/100 =SUM(E6:G6)
Didier =0*E2/100 =100*F2/100 =100*G2/100 =SUM(E7:G7)
Charlie =0*E2/100 =100*F2/100 =50*G2/100 =SUM(E8:G8)
which fails to load properly e.g. on a french system. I don’t want to write another Calc plugin since I want the installation to be as simple as possible (which is already fairly complex). So I ended up trying to detect the locale of the user based on LANG (not working on windows, need to find and test an alternative) and I allow the user to customize SUM inside my own plugin. Would love a simpler CSV import mechanism built-in inside calc (which already supports importing CSV with a different locale that ignores the formula!!), but in the meantime I’ll stick to that. Thanks for the help!
What could be the technical reason why anybody stores spreadsheet data and formulas in plain text? This makes no sense at all.
What do you mean? It seems really obvious. I want:
- a simple format that I can write from a plain lua script in my plugin ideally human readable, to contain (in my case) grade of students,
- compatible with both Libre Office and Excel, so that I can open the grades with tools people are used to,
- that contains formulas so that I can later modify them & avoid repetition (hence errors) as they automatically update their value. The above formula allows me to change the points attributed to each question without going through the hassle of re-opening my software and re-exporting grades + recompute the final grade directly.
Points 1 and 2 basically forces me to use CSV. If you have a simpler solution to propose, let me know, but any non-plaintext format would likely collide with point 1 (and certainly 2). For me the thing that makes no sense at all is that LO can’t understand that people with different system languages may want to share documents.
That’s what the Open Document Format (ODF) is supposed to do for all application developers. ODF is the one and only reason why LibreOffice exists. Raw data with formulas are compatible with any spreadsheet application on the market. Even some historic binary format (xls, 123, sxc) would be more practical than plain text because there are many applications able to read data and formulas from these document formats. csv can not even depict multiple sheets.
Your sample data contains both English and French formulas!
I can’t see any straightforward way to process such a mix in a generic way, so that it imports correctly with English-localised formulas, let alone with any other localisation.
Sorry, I forgot to replace one instance. I fixed it. It should be all-english CSV input that I want to import in my french LO.
With the fixed outliar =SOMME(…) the following snippet imports your csv-data correctly into my german localized LO with localized Formulas!
from pathlib import Path
import csv
import uno
def import_formula(*_):
"""
imports csv-data with english-localized-Formulas
into any localized LO-Spreadsheet!
todo: precheck the output for equal len for all »rows«!
"""
fp = XSCRIPTCONTEXT.ctx.ServiceManager.createInstance("com.sun.star.ui.dialogs.OfficeFilePicker")
fp.DisplayDirectory = f'{(Path.home() / "Downloads").as_uri() }'
fp.appendFilter("CSV (*.csv)", "*.csv")
fp.initialize((0,))
if fp.execute():
#csv_path = Path.from_uri(fp.SelectedFiles[0]) # since python 3.12
# in doubt use ⇓⇓
csv_path = Path( uno.fileUrlToSystemPath( fp.SelectedFiles[0] ))
with csv_path.open() as csv_file:
content = csv.reader(csv_file, delimiter='\t')
out = [ row for row in content if row ]
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.CurrentController.ActiveSheet
sheet[ :len(out), :len(out[0]) ].FormulaArray = out
edit: added file-picker-dialog
Thanks a lot, that’s really interesting… yet I guess the user will need to install a plugin on LO which I’d like to avoid…
Is it really a simple format? (XML + zipped + multiple files…) The specification says:
The typical files for a minimal spreadsheet document include: mimetype (one-line file containing only the string “application/vnd.oasis.opendocument.spreadsheet”; ./META-INF/manifest.xml (package manifest); content.xml (spreadsheet content); styles.xml (spreadsheet formatting).
- the main issue I have with this format is that I need to zip-compress it (hard to do in pure lua, and annoying to do via command line in a way that work on all OS + not great with version control, e.g. with git) and that I need to create multiple files (also means that I need a way to deal with temporary folders in a platform-agnostic way etc).
That being said, after doing some researches, I discovered the flat ODS format (.fods), that allows me to overcome two of the above issues (non compressed + single file). After a bit of time, I managed to get a minimal (heavily simplified from a basic export to fods) .fods format that would open:
<?xml version="1.0" encoding="UTF-8"?>
<office:document xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" office:mimetype="application/vnd.oasis.opendocument.spreadsheet">
<office:body>
<office:spreadsheet>
<table:table table:name="demo_unbook_grades" >
<table:table-row>
<table:table-cell office:value-type="string">
<text:p>Questions</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="3"/>
<table:table-cell office:value-type="string">
<text:p>ex1.1</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>ex1.2</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>ex2.1</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>Total</text:p>
</table:table-cell>
</table:table-row>
<table:table-row>
<table:table-cell office:value-type="string">
<text:p>Points</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="3"/>
<table:table-cell office:value-type="float" office:value="12" calcext:value-type="float">
<text:p>12</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="13" calcext:value-type="float">
<text:p>13</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="15" calcext:value-type="float">
<text:p>15</text:p>
</table:table-cell>
<table:table-cell table:formula="of:=SUM([.E2:.G2])" office:value-type="float" office:value="40" calcext:value-type="float">
<text:p>40</text:p>
</table:table-cell>
</table:table-row>
<table:table-row>
<table:table-cell office:value-type="string">
<text:p>Eve</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>Bateau</text:p>
</table:table-cell>
<table:table-cell office:value-type="string">
<text:p>eve@bateau.org</text:p>
</table:table-cell>
<table:table-cell/>
<table:table-cell table:formula="of:=100*[.E2]/100" office:value-type="float"/>
<table:table-cell table:formula="of:=100*[.F2]/100" office:value-type="float"/>
<table:table-cell table:formula="of:=0*[.G2]/100" office:value-type="float"/>
<table:table-cell table:formula="of:=SUM([.E3:.G3])" office:value-type="float"/>
</table:table-row>
<table:table-row>
<table:table-cell office:value-type="string">
<text:p>Alice</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="3"/>
<table:table-cell table:formula="of:=100*[.E2]/100" office:value-type="float"/>
<table:table-cell table:formula="of:=100*[.F2]/100" office:value-type="float"/>
<table:table-cell table:formula="of:=100*[.G2]/100" office:value-type="float"/>
<table:table-cell table:formula="of:=SUM([.E4:.G4])" office:value-type="float"/>
<table:table-cell office:value-type="string">
<text:p>Example of plein number</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="4.5" />
</table:table-row>
<table:table-row>
<table:table-cell office:value-type="string">
<text:p>Bob</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="3"/>
<table:table-cell table:formula="of:=0*[.E2]/100" office:value-type="float"/>
<table:table-cell table:formula="of:=0*[.F2]/100" office:value-type="float"/>
<table:table-cell table:formula="of:=100*[.G2]/100" office:value-type="float"/>
<table:table-cell table:formula="of:=SUM([.E5:.G5])" office:value-type="float"/>
</table:table-row>
<table:table-row>
<table:table-cell office:value-type="string">
<text:p>Didier</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="3"/>
<table:table-cell table:formula="of:=0*[.E2]/100" office:value-type="float"/>
<table:table-cell table:formula="of:=100*[.F2]/100" office:value-type="float"/>
<table:table-cell table:formula="of:=100*[.G2]/100" office:value-type="float"/>
<table:table-cell table:formula="of:=SUM([.E6:.G6])" office:value-type="float"/>
</table:table-row>
<table:table-row>
<table:table-cell office:value-type="string">
<text:p>Charlie</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="3"/>
<table:table-cell table:formula="of:=0*[.E2]/100" office:value-type="float"/>
<table:table-cell table:formula="of:=100*[.F2]/100" office:value-type="float"/>
<table:table-cell table:formula="of:=50*[.G2]/100" office:value-type="float"/>
<table:table-cell table:formula="of:=SUM([.E7:.G7])" office:value-type="float"/>
</table:table-row>
</table:table>
</office:spreadsheet>
</office:body>
</office:document>
You can see that it’s still very verbose compared to CSV… but at least it’s not too complicated and it may better deal with differences between the text 1.1 and the number 1.1 that arises in CSV any may lead to hard to spot bugs.
My main issue with flat ODS is that I’m not sure about its support with excel as I can’t find any official microsoft text saying that it is supported. Has anyone here an Excel that they could use to open the above .fods file to test please?
If the tradeoff is that I have a simple format that I can write directly, I can live with that. LO/ODF can’t also deal with nested objects like JSON can… every format has their tradeoffs.