Fix version incompatibilities of py-pandas and py-openpyxl (#40472)

* Fix version incompatibilities of py-pandas and py-openpyxl

* Add variant excel for py-pandas

* Add package py-pyxlsb

* Add versios for py-xlsxwriter

* Define excel dependencies for py-pandas 1.4, 1.5, 2.0, 2.1

* Fix variant excel in py-pandas

* Add package py-odfpy, which is also a dependency for py-pandas@2.0:

* Rearrange excel dependencies for py-pandas

* Change url to pypi

* Add missing newline to fix style in py-odfpy

4 files changed, 57 insertions, 0 deletions

diff --git a/var/spack/repos/builtin/packages/py-odfpy/package.py b/var/spack/repos/builtin/packages/py-odfpy/package.py
new file mode 100644
index 0000000000..338fb3beab
--- /dev/null
+++ b/var/spack/repos/builtin/packages/py-odfpy/package.py
@@ -0,0 +1,18 @@
+# Copyright 2013-2023 Lawrence Livermore National Security, LLC and other
+# Spack Project Developers. See the top-level COPYRIGHT file for details.
+#
+# SPDX-License-Identifier: (Apache-2.0 OR MIT)
+
+from spack.package import *
+
+
+class PyOdfpy(PythonPackage):
+    """Odfpy is a library to read and write OpenDocument v. 1.2 files."""
+
+    homepage = "https://github.com/eea/odfpy"
+    pypi = "odfpy/odfpy-1.4.1.tar.gz"
+
+    version("1.4.1", sha256="db766a6e59c5103212f3cc92ec8dd50a0f3a02790233ed0b52148b70d3c438ec")
+
+    depends_on("py-setuptools", type="build")
+    depends_on("py-defusedxml", type=("build", "run"))
diff --git a/var/spack/repos/builtin/packages/py-pandas/package.py b/var/spack/repos/builtin/packages/py-pandas/package.py
index e7ed9e7e60..f3d531f3bc 100644
--- a/var/spack/repos/builtin/packages/py-pandas/package.py
+++ b/var/spack/repos/builtin/packages/py-pandas/package.py
@@ -17,6 +17,8 @@ class PyPandas(PythonPackage):
 
     maintainers("adamjstewart")
 
+    variant("excel", when="@1.4:", default=False, description="Build with support for Excel")
+
     version("2.1.1", sha256="fecb198dc389429be557cde50a2d46da8434a17fe37d7d41ff102e3987fd947b")
     version("2.1.0", sha256="62c24c7fc59e42b775ce0679cfa7b14a5f9bfb7643cfbe708c960699e05fb918")
     version("2.0.3", sha256="c02f372a88e0d17f36d3093a644c73cfc1788e876a7c4bcb4020a77512e2043c")
@@ -129,6 +131,20 @@ class PyPandas(PythonPackage):
     # Optional dependencies
     # https://pandas.pydata.org/pandas-docs/stable/getting_started/install.html#optional-dependencies
 
+    # Excel dependencies for 1.4+ (not coded up for earlier versions)
+    depends_on("py-odfpy@1.4.1:", type=("run"), when="@2.0: +excel")
+    depends_on("py-openpyxl@3.0.10:", type=("run"), when="@2.1: +excel")
+    depends_on("py-openpyxl@3.0.7:", type=("run"), when="@1.5: +excel")
+    depends_on("py-openpyxl@3.0.3:", type=("run"), when="@1.4: +excel")
+    depends_on("py-pyxlsb@1.0.9:", type=("run"), when="@2.1: +excel")
+    depends_on("py-pyxlsb@1.0.8:", type=("run"), when="@1.5: +excel")
+    depends_on("py-pyxlsb@1.0.6:", type=("run"), when="@1.4: +excel")
+    depends_on("py-xlrd@2.0.1:", type=("run"), when="@1.4: +excel")
+    depends_on("py-xlwt@1.3.0:", type=("run"), when="@1.4:1.5 +excel")
+    depends_on("py-xlsxwriter@3.0.3:", type=("run"), when="@2.1: +excel")
+    depends_on("py-xlsxwriter@1.4.3:", type=("run"), when="@1.5: +excel")
+    depends_on("py-xlsxwriter@1.2.2:", type=("run"), when="@1.4: +excel")
+
     # Historical dependencies
     depends_on("py-setuptools@61:", when="@2.0", type="build")
     depends_on("py-setuptools@51:", when="@1.3.2:1", type="build")
diff --git a/var/spack/repos/builtin/packages/py-pyxlsb/package.py b/var/spack/repos/builtin/packages/py-pyxlsb/package.py
new file mode 100644
index 0000000000..d873e0b68b
--- /dev/null
+++ b/var/spack/repos/builtin/packages/py-pyxlsb/package.py
@@ -0,0 +1,19 @@
+# Copyright 2013-2023 Lawrence Livermore National Security, LLC and other
+# Spack Project Developers. See the top-level COPYRIGHT file for details.
+#
+# SPDX-License-Identifier: (Apache-2.0 OR MIT)
+
+
+from spack.package import *
+
+
+class PyPyxlsb(PythonPackage):
+    """Excel 2007-2010 Binary Workbook (xlsb) parser"""
+
+    pypi = "pyxlsb/pyxlsb-1.0.10.tar.gz"
+
+    version("1.0.10", sha256="8062d1ea8626d3f1980e8b1cfe91a4483747449242ecb61013bc2df85435f685")
+    version("1.0.8", sha256="dcf26d6494b45d8852d68571f828c2361b74711a2e19ba03eee77f96b9210464")
+    version("1.0.6", sha256="47e8230582de15ad9824a456d1d4cb36a6535f4ad5e5eb2464d31f0445b9db46")
+
+    depends_on("py-setuptools", type="build")
diff --git a/var/spack/repos/builtin/packages/py-xlsxwriter/package.py b/var/spack/repos/builtin/packages/py-xlsxwriter/package.py
index af333f03c0..09c2285311 100644
--- a/var/spack/repos/builtin/packages/py-xlsxwriter/package.py
+++ b/var/spack/repos/builtin/packages/py-xlsxwriter/package.py
@@ -12,6 +12,10 @@ class PyXlsxwriter(PythonPackage):
 
     pypi = "XlsxWriter/XlsxWriter-1.0.2.tar.gz"
 
+    version("3.1.7", sha256="353042efb0f8551ce72baa087e98228f3394fcb380e8b96313edf1eec8d50823")
+    version("3.0.3", sha256="e89f4a1d2fa2c9ea15cde77de95cd3fd8b0345d0efb3964623f395c8c4988b7f")
+    version("1.4.3", sha256="641db6e7b4f4982fd407a3f372f45b878766098250d26963e95e50121168cbe2")
+    version("1.2.2", sha256="5a5e2195a4672d17db79839bbdf1006a521adb57eaceea1c335ae4b3d19f088f")
     version("1.0.2", sha256="a26bbbafff88abffce592ffd5dfaa4c9f08dc44ef4afbf45c70d3e270325f856")
 
     depends_on("py-setuptools", type="build")