BUG: read_excel with openpyxl and missing dimension

pandas-dev · jreback · Feb 5, 2021 · Jan 30, 2021 · Jan 30, 2021 · Jan 31, 2021
commit 2bcf35b80ba765e8aee002bb0a19acd2799a5cc5
diff --git a/doc/source/whatsnew/v1.2.2.rst b/doc/source/whatsnew/v1.2.2.rst
@@ -26,7 +26,7 @@ Fixed regressions
 Bug fixes
 ~~~~~~~~~
 
--
+- Fixed bug in :func:`pandas.read_excel` producing incorrect results when the engine ``openpyxl`` is used and the excel file is missing dimension information (:issue:`38956`)
 -
 
 .. ---------------------------------------------------------------------------

diff --git a/pandas/io/excel/_openpyxl.py b/pandas/io/excel/_openpyxl.py
@@ -527,4 +527,10 @@ def get_sheet_data(self, sheet, convert_float: bool) -> List[List[Scalar]]:
         for row in sheet.rows:
             data.append([self._convert_cell(cell, convert_float) for cell in row])
 
+        # openpyxl may not have the correct padding if the dimension tag is
+        # not specified or is incorrect
+        max_width = max(len(row) for row in data)
+        if min(len(row) for row in data) < max_width:
+            data = [row + (max_width - len(row)) * [""] for row in data]
+
         return data
diff --git a/pandas/tests/io/data/excel/no_dimension.xlsx b/pandas/tests/io/data/excel/no_dimension.xlsx
diff --git a/pandas/tests/io/excel/test_openpyxl.py b/pandas/tests/io/excel/test_openpyxl.py
@@ -116,3 +116,24 @@ def test_to_excel_with_openpyxl_engine(ext):
         ).highlight_max()
 
         styled.to_excel(filename, engine="openpyxl")
+
+
+@pytest.mark.parametrize(
+    "header, expected_data",
+    [
+        (
+            0,
+            {
+                "Title": [np.nan, "A", 1, 2, 3],
+                "Unnamed: 1": [np.nan, "B", 4, 5, 6],
+                "Unnamed: 2": [np.nan, "C", 7, 8, 9],
+            },
+        ),
+        (2, {"A": [1, 2, 3], "B": [4, 5, 6], "C": [7, 8, 9]}),
+    ],
+)
+def test_read_with_missing_dimension(datapath, ext, header, expected_data):
+    path = datapath("io", "data", "excel", f"no_dimension{ext}")
+    result = pd.read_excel(path, header=header)
+    expected = DataFrame(expected_data)
+    tm.assert_frame_equal(result, expected)