feature request: Support negative infinitiy

[tdamsma]

Feature Request

Currently both inf and -inf are substituted with 1/0 when nan_inf_to_errors=True. I think it would be better to substitute -inf with -1/0. I would be happy to make a PR for this

[jmcnamara]

Does Excel support negative infinity?

[tdamsma]

As far as I understand excel support neither, that's why we basically substitute inf with 1/0. And following that logic, I think it makes sense to substitute -inf with -1/0.

This is a little edgecase, but I am making a library for nice pandas to excel-tables and back to pandas conversions. And then I couldn't get the roundtrip to work if I used nan_to_inf_errors=True due to this, as there is no distinction betqeen +/- inf.

But also I discovered that substituting np.Inf, np.finfo(np.float64).max and -np.Inf, np.finfo(np.float64).min actually does work for the roundtrip (icm with nan_inf_to_errors=False). By the way I write excel files with XlsxWriter and read them back with OpenPyXl.

After my patch, so using self._write_formula(row, col, '-1/0', cell_format, '#DIV/0!'):

When writing np.finfo(np.float64).min:

So in the second case there is no visual distinction in excel between =/- inf. However somehow OpenPyXl can infer that these values are +/- inf. In the case with -1/0 OpenPyXl interprets the value as positive infinity.

Perhaps I should just ignore the edge case, but it somehow bothers me

[tdamsma]

And writing this down got me wondering, what if we write self._write_formula(row, col, '-1/0', cell_format, np.finfo(np.float64).min) for -inf? It writes allright, but reading the excel file back with openpyxl now returns not -inf but -1.7976931348623157e+308 which is the value of np.finfo(np.float64).min

[jmcnamara]

There seems to be more than one question here but just focusing on the main ask it could be implemented like this:

diff --git a/xlsxwriter/worksheet.py b/xlsxwriter/worksheet.py
index 8f0b9cff..3675ab34 100644
--- a/xlsxwriter/worksheet.py
+++ b/xlsxwriter/worksheet.py
@@ -7,6 +7,7 @@

 # Standard packages.
 import datetime
+import math
 import os
 import re
 import tempfile
@@ -595,9 +596,12 @@ class Worksheet(xmlwriter.XMLwriter):
                 if isnan(number):
                     return self._write_formula(row, col, '#NUM!', cell_format,
                                                '#NUM!')
-                elif isinf(number):
+                elif number == math.inf:
                     return self._write_formula(row, col, '1/0', cell_format,
                                                '#DIV/0!')
+                elif number == -math.inf:
+                    return self._write_formula(row, col, '-1/0', cell_format,
+                                               '#DIV/0!')
             else:
                 raise TypeError(

Would that be suitable for your purposes?

[tdamsma]

Yes, that would be suitable. And indeed there is still some weirdness related to excel infinity handling that I don't fully grasp yet, but that is for another day. I think this small chang would definitely be an improvement, so thanks!

On Wed, 13 Oct 2021, 20:34 John McNamara, @.***> wrote:

There seems to be more than one question here but just focusing on the main ask it could be implemented like this:

diff --git a/xlsxwriter/worksheet.py b/xlsxwriter/worksheet.py index 8f0b9cff..3675ab34 100644--- a/xlsxwriter/worksheet.py+++ b/xlsxwriter/worksheet.py@@ -7,6 +7,7 @@

Standard packages.

import datetime+import math import os import re import tempfile@@ -595,9 +596,12 @@ class Worksheet(xmlwriter.XMLwriter): if isnan(number): return self._write_formula(row, col, '#NUM!', cell_format, '#NUM!')- elif isinf(number):+ elif number == math.inf: return self._write_formula(row, col, '1/0', cell_format, '#DIV/0!')+ elif number == -math.inf:+ return self._write_formula(row, col, '-1/0', cell_format,+ '#DIV/0!') else: raise TypeError(

Would that be suitable for your purposes?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/jmcnamara/XlsxWriter/issues/831#issuecomment-942603064, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB4BSU2QPZ5Q2BZD22YNWVLUGXGMDANCNFSM5F4PUQZA . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[jmcnamara]

Unfortunately the fix I proposed isn't compatible with Python 3.4 since it doesn't have math.inf. So I'll need to come up with a different approach or drop support for Python 3.4 (which is EOL).

[jmcnamara]

Fixed on main.