[libreoffice-users] scalc interchange ods between english and spanish

[Uwe Brauer]

Hi

I am using the english version of LO 5.3 and therefore I use the «.» to
indicate decimals: 3.4

However I have to interchange the documents with my colleagues which
using the Spanish version (of excel) so I must send xlsx or xls format
anyhow.

How I can I ensure that the spanish excel version of my colleagues
interpret 3.4 as a number, since in Spanish the convention is either 3,4
or 3'4?. I hoped that could be internally solved without any
specification but it seems not to be the case.

Any suggestion would be welcome. Thanks

Uwe Brauer

[Philip Jackson]

Hi Uwe,

Regardless of the version of calc you are using, you can change the
decimal indicator. In the spreadsheet, select the cells concerned and
then Format > Cells > Numbers and select Language to Spanish (Spain)
and your numbers will go from 3.4 to 3,4

So your colleagues won't know you ever had a problem.

Philip

Post by Uwe Brauer
Hi
I am using the english version of LO 5.3 and therefore I use the «.» to
indicate decimals: 3.4
However I have to interchange the documents with my colleagues which
using the Spanish version (of excel) so I must send xlsx or xls format
anyhow.
How I can I ensure that the spanish excel version of my colleagues
interpret 3.4 as a number, since in Spanish the convention is either 3,4
or 3'4?. I hoped that could be internally solved without any
specification but it seems not to be the case.
Any suggestion would be welcome. Thanks
Uwe Brauer

--
Philip Jackson
Domain le Theron
34210 Siran
France Tel : [phone]

Open pgp public key : fingerprint = 92B9 5310 37BE C6FE 2B2C B922 26BD
500A 2354 3A63

author of "The Circle of Fifths" for improvising musicians
Get it here https://www.books2read.com/u/bWnRM4

auteur : "Le Cycle des Quintes" pour musiciens improvisateurs
disponibilité : https://www.books2read.com/u/bw8rv9

[Uwe Brauer]

Hi Philip

Post by Philip Jackson
Hi Uwe,
Regardless of the version of calc you are using, you can change the
decimal indicator. In the spreadsheet, select the cells concerned and
then Format > Cells > Numbers and select Language to Spanish (Spain)
and your numbers will go from 3.4 to 3,4

I tried that but I am not entirely sure it worked flawlessly. It seems
however that the xlsx format (at least for simple spreadsheets)
translate well the different formats.

Uwe Brauer

[Tom Davies]

Hi :)
Does .xlsx work well enough now?


<start of a rambling aside mansplaining>

It used to be that only LibreOffice (and all other non-Microsquish office
programs) could write .xlsx that could be read by all versions of M$
Office. Each different version of MS Office defaults to using a different
"transitional" OOXML that doesn't conform to the OOXML ISO Standard, as
written by Microsoft (and agreed internationally despite there already
being an ISO Standard for editable documents). Sometimes the transitional
format used in one version of MS Office would create problems in other
versions of MS Office - even if that other version was newer.

This created a climate where document exchange was/is only viable through
non-editable formats such as .Pdfs and screenshots. People were seldom
aware that non-microsquish programs were the only ones capable of writing
.xlsx (and the rest) that could be read properly by all versions of MS
Office.

When micosquish switched to developing the various transitional X formats
they kinda stopped developing their older formats. Those older formats
thus accidentally became the best formats for sharing files. Sadly all
Microsquish formats seem to have been used to spread malware from infected
versions of MS Office. Non-Microsoft programs never seem to have that
problem except when passing on a file that was originally created by an
infected MS Office. The non-MS program would typically have been
unaffected and unable to infect the rest of the non-MS users system. To a
large extent this attack vector was minimised by MS blocking the use of
their own macros from other computers.

</end of rambling aside>


So, does xlsx now seem to better than xls? Does M$ Office now try to block
their older formats in an effort to further force people to buy into their
newer versions?

I think it better for people to use non-MS programs to share documents. In
Europe there seems to be much less resistance to non-microsoft programs,
perhaps because a rough average of around 20% of office users are already
using LibreOffice/OpenOffice including some very large organisations.
However Spain may be different, or the individual's company may still
believe MS's FUD and be scared of trying anything or updating anything.

Regards from
Tom :)

Post by Uwe Brauer
Hi Philip

Post by Philip Jackson
Hi Uwe,
Regardless of the version of calc you are using, you can change the
decimal indicator. In the spreadsheet, select the cells concerned and
then Format > Cells > Numbers and select Language to Spanish (Spain)
and your numbers will go from 3.4 to 3,4

I tried that but I am not entirely sure it worked flawlessly. It seems
however that the xlsx format (at least for simple spreadsheets)
translate well the different formats.
Uwe Brauer
--
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-
unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

[Uwe Brauer]

Post by Tom Davies
Hi :)
Does .xlsx work well enough now?

Yes and no.

That is after some trouble I found out that I convert cvs with . For
decimals to xlsx which are read correctly by LO and Excel.

However there is an issue.

When I convert csv (and you might ask why I use them, for simple tables
I use emacs org mode (with a poor man spreadsheet, which exports to
csv).

Then I convert it either opening the file with LO and exporting to xlsx
or on the command line with gnumeric

ssconvert example.csv example.xlsx

What does not work, is the command line converter of LO,

/usr/bin/soffice --headless --convert-to xlsx test.csv

the decimals
are fine but the non ascii chars are encoded incorrectly. See my message
sent some weeks ago.

Regards

Uwe Brauer

[Tom Davies]

Hi :)
Thanks :)

I'm fairly sure .csv has no internationally agreed standard but given what
one certain company does with such standards I thinks that's not really
relevant.

Advantages are (imo);
* that it can be read in some way or other by a vast range of programs
* very easy to edit
* easy to convert a file from one implementation to another
* very compact yet straight-forwards
* easy to compress and uncompress teliably and consistently

Thanks for the insight wrt jeadless/cli mode too.
Regards from
Tom :)

Post by Uwe Brauer

Post by Tom Davies
Hi :)
Does .xlsx work well enough now?

Yes and no.
That is after some trouble I found out that I convert cvs with . For
decimals to xlsx which are read correctly by LO and Excel.
However there is an issue.
When I convert csv (and you might ask why I use them, for simple tables
I use emacs org mode (with a poor man spreadsheet, which exports to
csv).
Then I convert it either opening the file with LO and exporting to xlsx
or on the command line with gnumeric
ssconvert example.csv example.xlsx
What does not work, is the command line converter of LO,
/usr/bin/soffice --headless --convert-to xlsx test.csv
the decimals
are fine but the non ascii chars are encoded incorrectly. See my message
sent some weeks ago.
Regards
Uwe Brauer
--
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-
unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

[Remy Gauthier]

Hello,

I exchange ODS and XLS files between the French Canadian (decimal
separator « , ») and English US/Canada locales (decimal separator « .
») without issues. When the number is already in a cell on a worksheet,
the application will use the locale to display a decimal number in the
correct way.

However, you cannot transfer files in text format (such as CSV)
directly from one to the other because the formatting of the decimal
numbers will conform to the current locale when the file is created: if
I import a CSV file created from the "English" version, afer the import
I must do a global replace of « . » to « , » to make numbers become
numbers again. Also, when going the other way, the CSV separator is
usually a « ; » to allow decimal numbers to contain a comma as decimal
separator, which is something Excel does not always handle very well.

So far, the best way I found is to save as XLS (or XLSX) from LO (Excel
considers ODS files as "broken" and does not preserve formulas), do the
changes in Excel, then open the XLS file in LO and save in ODS. Just
remember here that some page and cell formatting may be lost in the
exchange, so do not attempt anything fancy on that front :-).

If you use formulas that format numbers (=TEXT()), you will need to
have some intelligence to use the proper separator when encoding the
number format (more so for Excel than for LO). What I usually do is
have a cell that I name SEP that contains a formula like this:

=IF(ISNUM("3.4");".";",")

Then when I need to format a value with the =TEXT() function, I use it
this way to have a number with one decimal digit:

=TEXT(value;"0"&SEP&"0") or, if you prefer,
=TEXT(value;CONCATENATE("0";SEP;"0"))

In English locales, this translates to: =TEXT(value;"0.0"); in the non-
English locale, it becomes =TEXT(value;"0,0")

I hope this helps.

Rémy Gauthier.

Post by Uwe Brauer
Hi

Post by Uwe Brauer
I am using the english version of LO 5.3 and therefore I use the «.» to

indicate decimals: 3.4
However I have to interchange the documents with my colleagues which

Post by Uwe Brauer
using the Spanish version (of excel) so I must send xlsx or xls format

anyhow.
How I can I ensure that the spanish excel version of my colleagues

Post by Uwe Brauer
interpret 3.4 as a number, since in Spanish the convention is either 3,4

or 3'4?. I hoped that could be internally solved without any
specification but it seems not to be the case.
Any suggestion would be welcome. Thanks
Uwe Brauer 

[Uwe Brauer]

Post by Remy Gauthier
Hello,
I exchange ODS and XLS files between the French Canadian (decimal
separator « , ») and English US/Canada locales (decimal separator « .
») without issues. When the number is already in a cell on a worksheet,
the application will use the locale to display a decimal number in the
correct way.
However, you cannot transfer files in text format (such as CSV)
directly from one to the other because the formatting of the decimal
numbers will conform to the current locale when the file is created: if
I import a CSV file created from the "English" version, afer the import
I must do a global replace of « . » to « , » to make numbers become
numbers again. Also, when going the other way, the CSV separator is
usually a « ; » to allow decimal numbers to contain a comma as decimal
separator, which is something Excel does not always handle very well.

Ok maybe I mixed CSV with excel files and that is where my confusion
starts. You are right the xlsx format seems pretty save.

Post by Remy Gauthier
So far, the best way I found is to save as XLS (or XLSX) from LO (Excel
considers ODS files as "broken" and does not preserve formulas), do the
changes in Excel, then open the XLS file in LO and save in ODS. Just
remember here that some page and cell formatting may be lost in the
exchange, so do not attempt anything fancy on that front :-).

Still the excel files, when opened in excel contain cells with small
green edges, which according to the documentation is a indication of
possible format violation.

Post by Remy Gauthier
If you use formulas that format numbers (=TEXT()), you will need to
have some intelligence to use the proper separator when encoding the
number format (more so for Excel than for LO). What I usually do is
=IF(ISNUM("3.4");".";",")
Then when I need to format a value with the =TEXT() function, I use it
=TEXT(value;"0"&SEP&"0") or, if you prefer,
=TEXT(value;CONCATENATE("0";SEP;"0"))
In English locales, this translates to: =TEXT(value;"0.0"); in the non-
English locale, it becomes =TEXT(value;"0,0")
I hope this helps.

Thanks it does, will try to avoid to use the TEXT function, though.

Uwe Brauer