Return "" for null date columns, trim empty trailing rows

pandas-dev · jreback · Feb 5, 2021 · Jan 30, 2021 · Jan 30, 2021 · Jan 31, 2021
commit d6c3af1ea172f723d72d9eedc6c264ab1f704c08
diff --git a/pandas/io/excel/_openpyxl.py b/pandas/io/excel/_openpyxl.py
@@ -503,14 +503,14 @@ def _convert_cell(self, cell, convert_float: bool) -> Scalar:
 
         from openpyxl.cell.cell import TYPE_BOOL, TYPE_ERROR, TYPE_NUMERIC
 
-        if cell.is_date:
+        if cell.value is None:
+            return ""  # compat with xlrd
+        elif cell.is_date:
             return cell.value
         elif cell.data_type == TYPE_ERROR:
             return np.nan
         elif cell.data_type == TYPE_BOOL:
             return bool(cell.value)
-        elif cell.value is None:
-            return ""  # compat with xlrd
         elif cell.data_type == TYPE_NUMERIC:
             # GH5394
             if convert_float:
@@ -529,11 +529,18 @@ def get_sheet_data(self, sheet, convert_float: bool) -> List[List[Scalar]]:
         sheet.reset_dimensions()
 
         data: List[List[Scalar]] = []
-        for row in sheet.rows:
-            data.append([self._convert_cell(cell, convert_float) for cell in row])
+        last_row_with_data = -1
+        for row_number, row in enumerate(sheet.rows):
+            converted_row = [self._convert_cell(cell, convert_float) for cell in row]
+            if any(cell != "" for cell in converted_row):
+                last_row_with_data = row_number
+            data.append(converted_row)
 
-        # With dimension reset, openpyxl no longer pads rows
         if len(data) > 0:
+            # Trim trailing rows that have no data
+            data = data[: last_row_with_data + 1]
+
+            # With dimension reset, openpyxl no longer pads rows
             max_width = max(len(data_row) for data_row in data)
             if min(len(data_row) for data_row in data) < max_width:
                 data = [

diff --git a/pandas/tests/io/data/excel/dimension_large.xlsx b/pandas/tests/io/data/excel/dimension_large.xlsx
diff --git a/pandas/tests/io/excel/test_openpyxl.py b/pandas/tests/io/excel/test_openpyxl.py
@@ -135,7 +135,7 @@ def test_to_excel_with_openpyxl_engine(ext):
 @pytest.mark.parametrize(
     "filename", ["dimension_missing", "dimension_small", "dimension_large"]
 )
-def test_read_with_missing_dimension(datapath, ext, header, expected_data, filename):
+def test_read_with_bad_dimension(datapath, ext, header, expected_data, filename):
     # GH 38956, 39001, 39181 - no/incorrect dimension information
     path = datapath("io", "data", "excel", f"{filename}{ext}")
     result = pd.read_excel(path, header=header)