[GH-ISSUE #62] Question about writing a number

gitea-mirror commented

2026-05-05 11:32:10 -06:00

Owner

Originally created by @AlfonsoUceda on GitHub (Jul 6, 2016).
Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/62

Originally assigned to: @jmcnamara on GitHub.

Hi @jmcnamara

I need to ask you something related writing a number in an Excel, because I'm porting my old XLSX writer to the new writer based in this library.

The value I write in the excel is the following 0.041666666666666664 with worksheet_write_number function, the same for my both renderers and the format [h]:mm:ss with format_set_num_format function.

I've noticed the old renderer write the following XML for the cell:

<c r="AF2" s="5" t="n"><v>0.041666666666666664</v></c>

and the new renderer writes

<c r="AF2" s="3"><v>4.1666666666666657E-2</v></c>

I've seen the attribute is optional because n is number and it's default but you can see the values are different, If I translate those values in ruby console I get

0.041666666666666664 # => 0.041666666666666664
4.1666666666666657E-2 # => 0.04166666666666666

the second one gives me a wrong value.

FYI, the old renderer is written in ruby, the new renderer is a wrapper in ruby of your lib.

Testing with a C example:

#include "xlsxwriter.h"

int main() {

    lxw_workbook  *workbook  = new_workbook("test.xlsx");
    lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
    lxw_format    *format    = workbook_add_format(workbook);

    format_set_num_format(format, "[h]:mm:ss");

    worksheet_write_number(worksheet, 0, 0, 0.041666666666666664, format);

    workbook_close(workbook);

    return 0;
}

the cell is like this (truncates the last four):

<c r="A1" s="1"><v>0.04166666666666666</v></c>

Originally created by @AlfonsoUceda on GitHub (Jul 6, 2016). Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/62 Originally assigned to: @jmcnamara on GitHub. Hi @jmcnamara I need to ask you something related writing a number in an Excel, because I'm porting my old XLSX writer to the new writer based in this library. The value I write in the excel is the following `0.041666666666666664` with [worksheet_write_number](http://libxlsxwriter.github.io/worksheet_8h.html#ad9fc47d3beaa2ab4759414e8580c2289) function, the same for my both renderers and the format `[h]:mm:ss` with [format_set_num_format](http://libxlsxwriter.github.io/format_8h.html#af77bbd0003344cb16d455c7fb709e16c) function. I've noticed the old renderer write the following XML for the cell: ``` xml <c r="AF2" s="5" t="n"><v>0.041666666666666664</v></c> ``` and the new renderer writes ``` <c r="AF2" s="3"><v>4.1666666666666657E-2</v></c> ``` I've seen the attribute is optional because `n` is number and it's default but you can see the values are different, If I translate those values in ruby console I get ``` ruby 0.041666666666666664 # => 0.041666666666666664 4.1666666666666657E-2 # => 0.04166666666666666 ``` the second one gives me a wrong value. FYI, the old renderer is written in ruby, the new renderer is a wrapper in ruby of your lib. Testing with a C example: ``` C #include "xlsxwriter.h" int main() { lxw_workbook *workbook = new_workbook("test.xlsx"); lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL); lxw_format *format = workbook_add_format(workbook); format_set_num_format(format, "[h]:mm:ss"); worksheet_write_number(worksheet, 0, 0, 0.041666666666666664, format); workbook_close(workbook); return 0; } ``` the cell is like this (truncates the last four): ``` xml <c r="A1" s="1"><v>0.04166666666666666</v></c> ```

gitea-mirror

2026-05-05 11:32:10 -06:00

closed this issue
added the
question

ready to close
labels

gitea-mirror commented

2026-05-05 11:32:18 -06:00

Author

Owner

@jmcnamara commented on GitHub (Jul 7, 2016):

Hi Alfonso,

Excel stores number as IEEE754 doubles. These have 15 significant digits of precision. So the best precision you could get with this number is:

123456789012345
===============
416666666666666

And not as you expected:

12345678901234567
=================
41666666666666664

You can verify this yourself by pasting 0.041666666666666664 into a cell in Excel and looking at the XML output. With Excel 2013 I get the following:

<c r="A1"><v>4.1666666666666602E-2</v>

Which is:

123456789012345
===============
41666666666666602

Libxlsxwriter uses the same precision and IEEE754 doubles as Excel.

You "old" file producer may use a higher precision in the output format, or maybe Ruby has higher precision floating point but either way if you open the file in Excel and resave it you will probably lose the precision.

John

@jmcnamara commented on GitHub (Jul 7, 2016): Hi Alfonso, Excel stores number as IEEE754 doubles. These have 15 significant digits of precision. So the best precision you could get with this number is: ``` 123456789012345 =============== 416666666666666 ``` And not as you expected: ``` 12345678901234567 ================= 41666666666666664 ``` You can verify this yourself by pasting 0.041666666666666664 into a cell in Excel and looking at the XML output. With Excel 2013 I get the following: ``` xml <c r="A1"><v>4.1666666666666602E-2</v> ``` Which is: ``` 123456789012345 =============== 41666666666666602 ``` Libxlsxwriter uses the same precision and IEEE754 doubles as Excel. You "old" file producer may use a higher precision in the output format, or maybe Ruby has higher precision floating point but either way if you open the file in Excel and resave it you will probably lose the precision. John

gitea-mirror commented

2026-05-05 11:32:22 -06:00

Author

Owner

@AlfonsoUceda commented on GitHub (Jul 7, 2016):

Hi @jmcnamara

I saved the excel generated by the old renderer and Excel transforms the value from 0.041666666666666664 to 4.1666666666666664E-2, and the number in the new renderer is 4.1666666666666657E-2.

4.1666666666666664E-2 #=> 0.041666666666666664
4.1666666666666657E-2 #=> 0.04166666666666666
☝️ you can see the length is the same.

@AlfonsoUceda commented on GitHub (Jul 7, 2016): Hi @jmcnamara I saved the excel generated by the old renderer and Excel transforms the value from `0.041666666666666664` to `4.1666666666666664E-2`, and the number in the new renderer is `4.1666666666666657E-2`. `4.1666666666666664E-2` #=> 0.041666666666666664 `4.1666666666666657E-2` #=> 0.04166666666666666 ☝️ you can see the length is the same.

gitea-mirror commented

2026-05-05 11:32:25 -06:00

Author

Owner

@jmcnamara commented on GitHub (Jul 7, 2016):

What happens if you paste the number manually into Excel, save the file and view the xml?

@jmcnamara commented on GitHub (Jul 7, 2016): What happens if you paste the number manually into Excel, save the file and view the xml?

gitea-mirror commented

2026-05-05 11:32:28 -06:00

Author

Owner

@AlfonsoUceda commented on GitHub (Jul 7, 2016):

It's used as shared string :S even If I put the format as a number in the excel, I use excel 2016 but I'll ask a mate to do it with a lower version of excel.

@AlfonsoUceda commented on GitHub (Jul 7, 2016): It's used as shared string :S even If I put the format as a number in the excel, I use excel 2016 but I'll ask a mate to do it with a lower version of excel.

gitea-mirror commented

2026-05-05 11:32:31 -06:00

Author

Owner

@AlfonsoUceda commented on GitHub (Jul 7, 2016):

@jmcnamara one thing I saw is, If I write in a cell 01:00:00 is transformed to 1:00:00, then I save the excel and unzipped it, when I see the cell XML I have the following:

<sheetData>
  <row r="1" spans="1:1" x14ac:dyDescent="0.2">
    <c r="A1" s="1">
      <v>4.1666666666666664E-2</v>
    </c>
  </row>
</sheetData>

# IRB session
2.1.5 :001 > 4.1666666666666664E-2
 => 0.041666666666666664

EDIT:
@jmcnamara A mate with a v14 of excel, he saved the value as number and normal, and they are shared strings in the XML

@AlfonsoUceda commented on GitHub (Jul 7, 2016): @jmcnamara one thing I saw is, If I write in a cell `01:00:00` is transformed to `1:00:00`, then I save the excel and unzipped it, when I see the cell XML I have the following: ``` xml <sheetData> <row r="1" spans="1:1" x14ac:dyDescent="0.2"> <c r="A1" s="1"> <v>4.1666666666666664E-2</v> </c> </row> </sheetData> ``` ``` ruby # IRB session 2.1.5 :001 > 4.1666666666666664E-2 => 0.041666666666666664 ``` EDIT: @jmcnamara A mate with a v14 of excel, he saved the value as number and normal, and they are shared strings in the XML

gitea-mirror commented

2026-05-05 11:32:34 -06:00

Author

Owner

@AlfonsoUceda commented on GitHub (Jul 7, 2016):

@jmcnamara With "02:00:00" is right because the float precision is 17 digist.

<!-- New renderer -->
<c r="AF2" s="4"><v>0.08333333333333333</v></c>

<!-- Old renderer -->
<c r="AF2" s="6" t="n"><v>0.08333333333333333</v></c>

<!-- Handmade using Excel 2010 -->
<c r="D1" s="2"><v>8.3333333333333329E-2</v></c>

0.041666666666666664 #=> 01:00:00
"041666666666666664".size #=> 18

0.08333333333333333 #=> 02:00:00
"08333333333333333".size #=> 17

@AlfonsoUceda commented on GitHub (Jul 7, 2016): @jmcnamara With "02:00:00" is right because the float precision is 17 digist. ``` xml  <c r="AF2" s="4"><v>0.08333333333333333</v></c>  <c r="AF2" s="6" t="n"><v>0.08333333333333333</v></c>  <c r="D1" s="2"><v>8.3333333333333329E-2</v></c> ``` ``` ruby 0.041666666666666664 #=> 01:00:00 "041666666666666664".size #=> 18 0.08333333333333333 #=> 02:00:00 "08333333333333333".size #=> 17 ```

gitea-mirror commented

2026-05-05 11:32:36 -06:00

Author

Owner

@jmcnamara commented on GitHub (Jul 7, 2016):

Interesting.

Times in Excel are expressed as a percentage of the day so 1:00:00 is 1/24 which is:

0.0416666666666666666666666666666666666666...

Excel uses IEEE754 doubles internally for calculations. This has a limited precision of 15 digits (it can be slightly higher for some numbers).

So when Excel writes the number to a file it uses some formatting to convert from the internal double to the external string representation. From my analysis this format is "%.16g". At least that gives the most consistent results when comparing against files generated by Excel and there are a lot of tests in the Python/Perl/C versions of the library that do that.

However in the case above the format looks like "%.17g":

#include <stdio.h>

int main() {

    double num = 0.041666666666666664;

    printf("%.15g\n", num);
    printf("%.16g\n", num);
    printf("%.17g\n", num);
    printf("%.18g\n", num);
    printf("%.19g\n", num);
    puts("");

    printf("%.15g\n", 1.0/24.0);
    printf("%.16g\n", 1.0/24.0);
    printf("%.17g\n", 1.0/24.0);
    printf("%.18g\n", 1.0/24.0);
    printf("%.19g\n", 1.0/24.0);

    return 0;
}

Gives:

0.0416666666666667
0.04166666666666666
0.041666666666666664
0.0416666666666666644
0.04166666666666666435

0.0416666666666667
0.04166666666666666
0.041666666666666664
0.0416666666666666644
0.04166666666666666435

However, if I change the test suite to "%.17g" then some of the test cases fail so that isn't a general solution.

Ultimately this is just an artifact of handling fixed precision floating point numbers.

Beyond 15/16 digits the C and Ruby versions are both wrong, just differently.

@jmcnamara commented on GitHub (Jul 7, 2016): Interesting. Times in Excel are expressed as a percentage of the day so `1:00:00` is 1/24 which is: ``` 0.0416666666666666666666666666666666666666... ``` Excel uses IEEE754 doubles internally for calculations. This has a limited precision of 15 digits (it can be slightly higher for some numbers). So when Excel writes the number to a file it uses some formatting to convert from the internal double to the external string representation. From my analysis this format is `"%.16g"`. At least that gives the most consistent results when comparing against files generated by Excel and there are a lot of tests in the Python/Perl/C versions of the library that do that. However in the case above the format looks like `"%.17g"`: ``` c #include <stdio.h> int main() { double num = 0.041666666666666664; printf("%.15g\n", num); printf("%.16g\n", num); printf("%.17g\n", num); printf("%.18g\n", num); printf("%.19g\n", num); puts(""); printf("%.15g\n", 1.0/24.0); printf("%.16g\n", 1.0/24.0); printf("%.17g\n", 1.0/24.0); printf("%.18g\n", 1.0/24.0); printf("%.19g\n", 1.0/24.0); return 0; } ``` Gives: ``` 0.0416666666666667 0.04166666666666666 0.041666666666666664 0.0416666666666666644 0.04166666666666666435 0.0416666666666667 0.04166666666666666 0.041666666666666664 0.0416666666666666644 0.04166666666666666435 ``` However, if I change the test suite to `"%.17g"` then some of the test cases fail so that isn't a general solution. Ultimately this is just an artifact of handling fixed precision floating point numbers. Beyond 15/16 digits the C and Ruby versions are both wrong, just differently.

gitea-mirror commented

2026-05-05 11:32:40 -06:00

Author

Owner

@jmcnamara commented on GitHub (Jul 7, 2016):

BTW, you can see the same thing in Ruby:

$ irb
irb(main):001:0> '%.15g' % (1.0/24.0) 
=> "0.0416666666666667"
irb(main):002:0> '%.16g' % (1.0/24.0) 
=> "0.04166666666666666"
irb(main):003:0> '%.17g' % (1.0/24.0) 
=> "0.041666666666666664"
irb(main):004:0> '%.18g' % (1.0/24.0) 
=> "0.0416666666666666644"

@jmcnamara commented on GitHub (Jul 7, 2016): BTW, you can see the same thing in Ruby: ``` ruby $ irb irb(main):001:0> '%.15g' % (1.0/24.0) => "0.0416666666666667" irb(main):002:0> '%.16g' % (1.0/24.0) => "0.04166666666666666" irb(main):003:0> '%.17g' % (1.0/24.0) => "0.041666666666666664" irb(main):004:0> '%.18g' % (1.0/24.0) => "0.0416666666666666644" ```

gitea-mirror commented

2026-05-05 11:32:43 -06:00

Author

Owner

@AlfonsoUceda commented on GitHub (Jul 8, 2016):

@jmcnamara thank you for answer and investigation, we are going to use the text format because it's work in our integration.

@AlfonsoUceda commented on GitHub (Jul 8, 2016): @jmcnamara thank you for answer and investigation, we are going to use the text format because it's work in our integration.

gitea-mirror commented

2026-05-05 11:32:44 -06:00

Author

Owner

@jmcnamara commented on GitHub (Jul 8, 2016):

Hi Alfonso.

Thanks for the update.

John

@jmcnamara commented on GitHub (Jul 8, 2016): Hi Alfonso. Thanks for the update. John

Rows
Columns

[GH-ISSUE #62] Question about writing a number #53