Hi! I wrote a script that creates JSON files based on the contents of Google Sheets using Spreadsheet::Read and Spreadsheet::ReadSXC, which are previously downloaded into local files using HTTP::Tiny. All works nice, even when the localization of the Google Sheet is different than US in the formatting of numbers: in my case, comma is for decimals and period is for thousand separator.

But something wents wrong when I opened one of the downloaded ODS files with LibreOffice, edited some of the values and saved it again before procesing it with my script. The modified ODS file now gives floats with my localization standard!!! If I open again the ODS, I can see the values in my local format.

To check what is going on, I extracted the "content.xml" from both original and modified ODS files and compared a cell content. This is what I found:

Integer value (in a float cell):

- Google Sheet

          <table:table-cell table:style-name="ce37" table:formula="of:
+=[.D486]" office:value-type="float" office:value="2800" calcext:value
+-type="float">
            <text:p>2,800.00</text:p>
          </table:table-cell>

[download]

- LibreOffice Calc

          <table:table-cell table:style-name="ce116" table:formula="of
+:=[.D486]" office:value-type="float" office:value="2800" calcext:valu
+e-type="float">
            <text:p>2.800,00</text:p>
          </table:table-cell>

[download]

Float value:

- Google Sheet

          <table:table-cell table:style-name="ce37" office:value-type=
+"float" office:value="1138.85" calcext:value-type="float">
            <text:p>1,138.85</text:p>
          </table:table-cell>

[download]

- LibreOffice Calc

          <table:table-cell table:style-name="ce116" office:value-type
+="float" office:value="1138.85" calcext:value-type="float">
            <text:p>1.138,85</text:p>
          </table:table-cell>

[download]

As in the JSON file I must write numbers without the thousand separator, with ODS files just downloaded from Sheets, I had to remove the commas with a regexp, but in the ODS file that was modified by Calc, the regexp removes the decimal separator and multiplies the value x100!!!

So, it seems that cell and cellrow methods retrieves the value from the "text:p" tag instead of the "office:value" property of the "table:tablecel" tag. Format parsing in these methods seems to work only for dates and strings, but not for numeric values, .

Is there a way to process cell values in a "neutral" region, i.e. without localization?

In reply to Spreadsheet::ReadSXC and localization -- Google Sheets vs LibreOffice Calc by vitoco

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.