How to scan all sheet cells

Question

I want to scan excel sheet cells using win32com for python and save a dictionary of cells location and their values.

Is there an efficient way to scan a sheet ? Meaning, scan as less cells as possible but still scan all cells with data/value and ignore far away cells.

I used to use openpyxl, and it had a parameter of self.max_row and self.max_column for each sheet. Is there a similar parameter or a good way to calculate them ?

Answer 1

How about trying something like this...

end_row = ActiveSheet.UsedRange.Rows.Count
end_column = ActiveSheet.UsedRange.Columns.Count

If you have a very large range you plan on going through, I would suggest trying to minimize the back and forth through the COM layers by taking a range of cells into Python all at once.

I documented some of the things I ran into a while back here.

The main tips from my prior writing:

# Take many values at once rather than reading each individually
end_num = sh.UsedRange.Rows.Count
col_a = sh.Range(xl.Cells(1, 1), xl.Cells(end_num, 1)).Value

# The magic numbers to turn calculation on and off
xlCalculationManual = -4135
xlCalculationAutomatic = -4105

# Turn calculation to manual
xl.Calculation = xlCalculationManual

# Turn screen updating off
xl.ScreenUpdating = False

# RUN YOUR CODE HERE #

# Turn things back on when done
xl.ScreenUpdating = True
xl.Calculation = xlCalculationAutomatic