3

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 ?

ρss
  • 5,115
  • 8
  • 43
  • 73
Ran S
  • 105
  • 9
  • 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 ? – Ran S Dec 28 '15 at 11:08
  • Why don't you just use `openpyxl`? – steinar Dec 28 '15 at 12:48
  • It doesn't keep the workbook validators and images. Also it doesn't support insert new lines and it is very difficult to update the excel formulas when adding new line manullay using `openpyxl`. – Ran S Dec 28 '15 at 13:37

1 Answers1

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
clutton
  • 620
  • 1
  • 8
  • 18
  • I used `last_col = xlApp.ActiveCell.SpecialCells(constants.xlLastCell).Column` `last_row = xlApp.ActiveCell.SpecialCells(constants.xlLastCell).Row` for getting the last cell. I will take the advice for taking a range of cells into python – Ran S Jan 03 '16 at 08:47
  • Note you can use the technique mentioned in my answer to http://stackoverflow.com/questions/23290216/error-xlprimary-not-defined-in-python-win32com/23306850#23306850 to import the constants – Oliver Jan 05 '16 at 06:57