Repository: jazzband/tablib
Pull request #516 (2022-02-17 10:20:31)

feat: support column_width in xlsx format
This PR allows user to specify column width in XLXS format. For it has a default value, which is not suitable for long values in dataset. As a result, it is not convenient to look these tables. Also, it eliminates questions like #318 in future

I didn't find tests for exporting, so omitted this. Let me know if they are required

codecov[bot]: # [Codecov](https://codecov.io/gh/jazzband/tablib/pull/516?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband) Report
> Merging [#516](https://codecov.io/gh/jazzband/tablib/pull/516?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband) (b7d313e) into [master](https://codecov.io/gh/jazzband/tablib/commit/d9a4a9790a4d94185f8cac022572480bac593319?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband) (d9a4a97) will **decrease** coverage by `0.04%`.
> The diff coverage is `85.71%`.

[![Impacted file tree graph](https://codecov.io/gh/jazzband/tablib/pull/516/graphs/tree.svg?width=650&height=150&src=pr&token=ns7MUP4H8t&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband)](https://codecov.io/gh/jazzband/tablib/pull/516?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband)

```diff
@@            Coverage Diff             @@
##           master     #516      +/-   ##
==========================================
- Coverage   90.82%   90.78%   -0.05%     
==========================================
  Files          28       28              
  Lines        2660     2680      +20     
==========================================
+ Hits         2416     2433      +17     
- Misses        244      247       +3     
```


| [Impacted Files](https://codecov.io/gh/jazzband/tablib/pull/516?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband) | Coverage Δ | |
|---|---|---|
| [src/tablib/formats/\_xlsx.py](https://codecov.io/gh/jazzband/tablib/pull/516/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband#diff-c3JjL3RhYmxpYi9mb3JtYXRzL194bHN4LnB5) | `95.00% <85.71%> (-2.00%)` | :arrow_down: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/jazzband/tablib/pull/516?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/jazzband/tablib/pull/516?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband). Last update [d9a4a97...b7d313e](https://codecov.io/gh/jazzband/tablib/pull/516?src=pr&el=lastupdated&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=jazzband).

Birdi7: Hello @hugovk! Sorry for ping, but I just want to ask if you have any time for review and merge this?
Birdi7: @hugovk Thank you for the review! I've added the test on check the new feature, checkout `test_xlsx_column_width`. Would you mind review the PR, and if everything is ok, merge it to master?
Birdi7: Thank you for such usefull comment! I've found a bug with new tests. Your comments are pushed, alongside with all three tests. What do you think on it?
Birdi7: Thank you for your comment! I believe this significantly  improves redability. Could please take a look if the PR is fine now?
rakinson: Hi there, I would find this enhancement useful. Do you mind to take this last step to add this feature? 
Thank you!
Birdi7: Hi! I won't be able to finish this PR, so feel free to do that!
andrewgy8: Hey! We think it would be useful over at Django-Import-Export!

I dont mind picking up this PR (rebasing, docs updates, and your comments @hugovk ) if we think it can be merged in. 

Given the work put in already by you all, it would be a shame to leave it abandoned. Thanks!
claudep: Sure, feel free to take and polish this PR!
andrewgy8: Okie dokie! I have:
- updated the code with comments from @hugovk ✅ 
- rebased on master ✅ 
- cleaned up the tests a bit ✅ 
- and cleaned up the column width code a bit ✅ 

Let me know if there is anything else I can do to get this across the line! Thanks for the help!
andrewgy8: Updated @claudep ! Thanks for the quick review!
claudep: Thanks, let's wait now for @hugovk to make a final review.

Patch:
diff --git a/.github/CONTRIBUTING.md b/.github/CONTRIBUTING.md
index cfb1fd0..4c82fbe 100644
--- a/.github/CONTRIBUTING.md
+++ b/.github/CONTRIBUTING.md
@@ -4,7 +4,7 @@ This is a [Jazzband](https://jazzband.co/) project. By contributing you agree to
 by the [Contributor Code of Conduct](https://jazzband.co/about/conduct) and follow the
 [guidelines](https://jazzband.co/about/guidelines).
 
-If you'd like to contribute, simply fork 
+If you'd like to contribute, simply fork
 [the repository](https://github.com/jazzband/tablib), commit your changes to a feature
 branch, and send a pull request to `master`. Make sure you add yourself to
 [AUTHORS](https://github.com/jazzband/tablib/blob/master/AUTHORS).
diff --git a/.pre-commit-config.yaml b/.pre-commit-config.yaml
index 70cc463..92fa8eb 100644
--- a/.pre-commit-config.yaml
+++ b/.pre-commit-config.yaml
@@ -29,9 +29,21 @@ repos:
   - repo: https://github.com/pre-commit/pre-commit-hooks
     rev: v5.0.0
     hooks:
+      - id: check-added-large-files
+      - id: check-case-conflict
       - id: check-merge-conflict
       - id: check-toml
       - id: check-yaml
+      - id: debug-statements
+      - id: end-of-file-fixer
+      - id: forbid-submodules
+      - id: requirements-txt-fixer
+      - id: trailing-whitespace
+
+  - repo: meta
+    hooks:
+      - id: check-hooks-apply
+      - id: check-useless-excludes
 
 ci:
   autoupdate_schedule: quarterly
diff --git a/AUTHORS b/AUTHORS
index ed4eec6..c8fc980 100644
--- a/AUTHORS
+++ b/AUTHORS
@@ -4,11 +4,13 @@ by the Jazzband GitHub team.
 Here is a list of past and present much-appreciated contributors:
 
     Alex Gaynor
+    Andrew Graham-Yooll
     Andrii Soldatenko
     Benjamin Wohlwend
     Bruno Soares
     Claude Paroz
     Daniel Santos
+    Egor Osokin
     Erik Youngren
     Hugo van Kemenade
     Iuri de Silvio
diff --git a/HISTORY.md b/HISTORY.md
index 077b317..cf3b702 100644
--- a/HISTORY.md
+++ b/HISTORY.md
@@ -1,5 +1,11 @@
 # History
 
+## 3.8.0 (Unreleased)
+
+### Improvements
+
+- Add support for exporting XLSX with column width (#516)
+
 ## 3.7.0 (2024-10-08)
 
 ### Improvements
diff --git a/docs/development.rst b/docs/development.rst
index de8bdd3..08c7502 100644
--- a/docs/development.rst
+++ b/docs/development.rst
@@ -125,8 +125,8 @@ registered.
 
    .. admonition:: Excluding Support
 
-       If the format excludes support for an import/export mechanism (*e.g.* 
-       :class:`csv <tablib.Dataset.csv>` excludes 
+       If the format excludes support for an import/export mechanism (*e.g.*
+       :class:`csv <tablib.Dataset.csv>` excludes
        :class:`Databook <tablib.Databook>` support),
        simply don't define the respective class methods.
        Appropriate errors will be raised.
@@ -138,7 +138,7 @@ registered.
     registry.register('xxx', MyXXXFormatClass())
 
 3. From then on, you should be able to use your new custom format as if it were
-a built-in Tablib format, e.g. using ``dataset.export('xxx')`` will use the 
+a built-in Tablib format, e.g. using ``dataset.export('xxx')`` will use the
 ``MyXXXFormatClass.export_set`` method.
 
 .. _testing:
diff --git a/docs/formats.rst b/docs/formats.rst
index 178edbd..ce546dd 100644
--- a/docs/formats.rst
+++ b/docs/formats.rst
@@ -250,6 +250,18 @@ The ``import_set()`` method also supports a ``skip_lines`` parameter that you
 can set to a number of lines that should be skipped before starting to read
 data.
 
+The ``export_set()`` method supports a ``column_width`` parameter. Depending
+on the value passed, the column width will be set accordingly. It can be
+either ``None``, an integer, or default "adaptive". If "adaptive" is passed,
+the column width will be unique and will be calculated based on values' length.
+For example::
+
+    data = tablib.Dataset()
+    data.export('xlsx', column_width='adaptive')
+
+.. versionchanged:: 3.8.0
+    The ``column_width`` parameter for ``export_set()`` was added.
+
 .. versionchanged:: 3.1.0
 
     The ``skip_lines`` parameter for ``import_set()`` was added.
diff --git a/docs/intro.rst b/docs/intro.rst
index 62b26e6..80beba9 100644
--- a/docs/intro.rst
+++ b/docs/intro.rst
@@ -60,4 +60,3 @@ Pythons Supported
 Python 3.6+ is officially supported.
 
 Now, go :ref:`install Tablib <install>`.
-
diff --git a/docs/tutorial.rst b/docs/tutorial.rst
index 38f1786..e4c2ae6 100644
--- a/docs/tutorial.rst
+++ b/docs/tutorial.rst
@@ -313,7 +313,7 @@ Now that we have extra meta-data on our rows, we can easily filter our :class:`D
     >>> students.filter(['female']).yaml
     - {first: Bessie, Last: Monke}
 
-By default, when you pass a list of tags you get filter type or. :: 
+By default, when you pass a list of tags you get filter type or. ::
 
     >>> students.filter(['female', 'creative']).yaml
     - {first: Daniel, Last: Dupont}
diff --git a/src/tablib/formats/_xlsx.py b/src/tablib/formats/_xlsx.py
index 966ba5c..1a75c20 100644
--- a/src/tablib/formats/_xlsx.py
+++ b/src/tablib/formats/_xlsx.py
@@ -1,6 +1,5 @@
 """ Tablib - XLSX Support.
 """
-
 import re
 from io import BytesIO
 
@@ -35,7 +34,8 @@ class XLSXFormat:
             return False
 
     @classmethod
-    def export_set(cls, dataset, freeze_panes=True, invalid_char_subst="-", escape=False):
+    def export_set(cls, dataset, freeze_panes=True, invalid_char_subst="-",
+                   escape=False, column_width="adaptive"):
         """Returns XLSX representation of Dataset.
 
         If ``freeze_panes`` is True, Export will freeze panes only after first line.
@@ -48,6 +48,12 @@ class XLSXFormat:
         If ``escape`` is True, formulae will have the leading '=' character removed.
         This is a security measure to prevent formulae from executing by default
         in exported XLSX files.
+
+        If ``column_width`` is set to "adaptive", the column width will be set to the maximum
+        width of the content in each column. If it is set to an integer, the column width will be
+        set to that integer value. If it is set to None, the column width will be set as the
+        default openpyxl.Worksheet width value.
+
         """
         wb = Workbook()
         ws = wb.worksheets[0]
@@ -59,6 +65,8 @@ class XLSXFormat:
 
         cls.dset_sheet(dataset, ws, freeze_panes=freeze_panes, escape=escape)
 
+        cls._adapt_column_width(ws, column_width)
+
         stream = BytesIO()
         wb.save(stream)
         return stream.getvalue()
@@ -166,3 +174,31 @@ class XLSXFormat:
 
                 if escape and cell.data_type == 'f' and cell.value.startswith('='):
                     cell.value = cell.value.replace("=", "")
+
+    @classmethod
+    def _adapt_column_width(cls, worksheet, width):
+        if isinstance(width, str) and width != "adaptive":
+            msg = (
+                f"Invalid value for column_width: {width}. "
+                "Must be 'adaptive' or an integer."
+            )
+            raise ValueError(msg)
+
+        if width is None:
+            return
+
+        column_widths = []
+        if width == "adaptive":
+            for row in worksheet.values:
+                for i, cell in enumerate(row):
+                    cell_width = len(str(cell))
+                    if len(column_widths) > i:
+                        if cell_width > column_widths[i]:
+                            column_widths[i] = cell_width
+                    else:
+                        column_widths.append(cell_width)
+        else:
+            column_widths = [width] * worksheet.max_column
+
+        for i, column_width in enumerate(column_widths, 1):  # start at 1
+            worksheet.column_dimensions[get_column_letter(i)].width = column_width


Test patch:
diff --git a/.github/workflows/test.yml b/.github/workflows/test.yml
index 6548ce0..6a4a92c 100644
--- a/.github/workflows/test.yml
+++ b/.github/workflows/test.yml
@@ -8,7 +8,7 @@ permissions:
 env:
   FORCE_COLOR: 1
   PIP_DISABLE_PIP_VERSION_CHECK: 1
-  
+
 jobs:
   test:
     runs-on: ${{ matrix.os }}
diff --git a/tests/files/issue_524.yaml b/tests/files/issue_524.yaml
index b0cf710..308982a 100644
--- a/tests/files/issue_524.yaml
+++ b/tests/files/issue_524.yaml
@@ -1,1 +1,1 @@
-title: Voice of Miki Vanoušek
\ No newline at end of file
+title: Voice of Miki Vanoušek
diff --git a/tests/requirements.txt b/tests/requirements.txt
index d2f56f9..7b98a10 100644
--- a/tests/requirements.txt
+++ b/tests/requirements.txt
@@ -1,7 +1,7 @@
-pytest
-pytest-cov
 odfpy
 openpyxl>=2.6.0
+pytest
+pytest-cov
 pyyaml
 tabulate
 xlrd
diff --git a/tests/test_tablib.py b/tests/test_tablib.py
index 0df8e5d..3211991 100755
--- a/tests/test_tablib.py
+++ b/tests/test_tablib.py
@@ -1339,6 +1339,25 @@ class XLSTests(BaseTestCase):
 
 
 class XLSXTests(BaseTestCase):
+    def _helper_export_column_width(self, column_width):
+        """check that column width adapts to value length"""
+        def _get_width(data, input_arg):
+            xlsx_content = data.export('xlsx', column_width=input_arg)
+            wb = load_workbook(filename=BytesIO(xlsx_content))
+            ws = wb.active
+            return ws.column_dimensions['A'].width
+
+        xls_source = Path(__file__).parent / 'files' / 'xlsx_cell_values.xlsx'
+        with xls_source.open('rb') as fh:
+            data = tablib.Dataset().load(fh)
+        width_before = _get_width(data, column_width)
+        data.append([
+            'verylongvalue-verylongvalue-verylongvalue-verylongvalue-'
+            'verylongvalue-verylongvalue-verylongvalue-verylongvalue',
+        ])
+        width_after = _get_width(data, width_before)
+        return width_before, width_after
+
     def test_xlsx_format_detect(self):
         """Test the XLSX format detection."""
         in_stream = self.founders.xlsx
@@ -1483,6 +1502,29 @@ class XLSXTests(BaseTestCase):
         wb = load_workbook(filename=BytesIO(_xlsx))
         self.assertEqual('[1]', wb.active['A1'].value)
 
+    def test_xlsx_column_width_adaptive(self):
+        """ Test that column width adapts to value length"""
+        width_before, width_after = self._helper_export_column_width("adaptive")
+        self.assertEqual(width_before, 11)
+        self.assertEqual(width_after, 11)
+
+    def test_xlsx_column_width_integer(self):
+        """Test that column width changes to integer length"""
+        width_before, width_after = self._helper_export_column_width(10)
+        self.assertEqual(width_before, 10)
+        self.assertEqual(width_after, 10)
+
+    def test_xlsx_column_width_none(self):
+        """Test that column width does not change"""
+        width_before, width_after = self._helper_export_column_width(None)
+        self.assertEqual(width_before, 13)
+        self.assertEqual(width_after, 13)
+
+    def test_xlsx_column_width_value_error(self):
+        """Raise ValueError if column_width is not a valid input"""
+        with self.assertRaises(ValueError):
+            self._helper_export_column_width("invalid input")
+
 
 class JSONTests(BaseTestCase):
     def test_json_format_detect(self):