pywintypes.com_error: (-2147352567,'Exception occured.', (0, None, None, None, 0, -2147352566)

Question

Background： I am using xlwings to open an xlsx file and use a list (tuple) to receive all the data of the table. The code is as follows.

def get_excel_all_data(path: string, input_sheet_name: string):    
    app = xw.App(visible=True, add_book=False)
    app.display_alerts = False
    # path is my file location
    wb = app.books.open(path, update_links=True)
    # Create a tuple to receive data
    list_value = ()
    # Get the number of sheets
    sheet_num = len(wb.sheets)
    # Iterate through the sheet to get the specified name
    for i in range(0, sheet_num):
        sht_list = wb.sheets[i].name
        # using re to match whether the Sheet name is the same as Sheet1
        result = re.search(input_sheet_name, sht_list)

        if result is not None:
            # Set the active worksheet to the found sheet
            act_sht = wb.sheets(input_sheet_name)
            # act_sht.api.ShowAllData()
            # Get all used cells of the sheet
            last_cell = act_sht.used_range.last_cell
            # Get row and column
            row = last_cell.row
            column = last_cell.column
            # Write to tuple
            list_value = act_sht.range((1, 1),(row, column)).value
            break

When the code is running in write to tuple，it crashed：

Python told me：

Traceback (most recent call last):
  File "d:\pythonProject\create_doc.py", line 12, in <module>
    all_data = universal.get_excel_all_data(r'D:\application\data\test.XLSX', "Sheet1")
  File "d:\pythonProject\Universal\universal.py", line 87, in get_excel_all_data
    list_value = act_sht.range((1, 1),(row, column)).value
  File "C:\Users\sin\anaconda3\lib\site-packages\xlwings\main.py", line 1993, in value
    return conversion.read(self, None, self._options)
  File "C:\Users\sin\anaconda3\lib\site-packages\xlwings\conversion\__init__.py", line 32, in read
    pipeline(ctx)
  File "C:\Users\sin\anaconda3\lib\site-packages\xlwings\conversion\framework.py", line 66, in __call__
    stage(*args, **kwargs)
  File "C:\Users\sin\anaconda3\lib\site-packages\xlwings\conversion\standard.py", line 96, in __call__
    c.value = c.range.raw_value
  File "C:\Users\sin\anaconda3\lib\site-packages\xlwings\main.py", line 1586, in raw_value
    return self.impl.raw_value
  File "C:\Users\sin\anaconda3\lib\site-packages\xlwings\_xlwindows.py", line 835, in raw_value
    return self.xl.Value
  File "C:\Users\sin\anaconda3\lib\site-packages\xlwings\_xlwindows.py", line 126, in __getattr__
    v = getattr(self._inner, item)
  File "C:\Users\sin\anaconda3\lib\site-packages\win32com\client\__init__.py", line 583, in __getattr__
    return self._ApplyTypes_(*args)
  File "C:\Users\sin\anaconda3\lib\site-packages\win32com\client\__init__.py", line 572, in _ApplyTypes_
    self._oleobj_.InvokeTypes(dispid, 0, wFlags, retType, argTypes, *args),
pywintypes.com_error: (-2147352567, 'Exception occured', (0, None, None, None, 0, -2147352566), None)

SOME DATA:

The xlsx file already uses 10,000 rows and 52 columns.But the program automatically obtains row is 10000, column is 53.

Here is what I tried.

1. Tried to read range(1,1), no problem

2. Read range((1,1), (100, 52)), no problem

3. Manually change row to 10000 and column to 53, that is range((1,1), (10000, 53)), the same error prompt above appears.

4. If read other files (not so much data), no problem

5. Manually copying the whole sheet to a new xlsx file is also unreadable and the error is the same

Answer 1

Oh my god! I know what happend!

I use the dichotomous method to find the specific ranks where the problem occurs.

Then I find some data is displayed as horizontal lines. Excel tells me that this is the date format, but the value is too large, so it shows up as #######.

When I changed it to the correct format, the code worked correctly.

Until I used xlwings, I never thought the data on #### was unavailable!

Thanks,buddy,@moken @KarlKnechtel.You two have given me good ideas