2

I am trying to read values from an Excel file and when I do:

for i in range(1, row_num + 1): 
        try:
            cell_obj = sheet.cell(row = i, column = 1)

I get the following warning:

DeprecationWarning: Call to deprecated function get_squared_range (Use ws.iter_rows()).
  for row in self.get_squared_range(column, row, column, row):

I've tried to implement what the Python documentation says, but it just didn't work or I wasn't using it properly, I also tried to do the following:

for i in range(1, row_num + 1): 
        try:
            cell_obj = sheet.cell(row = i, column = 1) 
            myList.append((cell_obj).value)   
            warnings.filterwarnings("ignore", category=DeprecationWarning)

but again, nothing is happening. Could anyone shed some light on this?

jonrsharpe
  • 115,751
  • 26
  • 228
  • 437
Eugenia Castilla
  • 67
  • 9
  • As per the discussions on [this question](https://stackoverflow.com/questions/879173/how-to-ignore-deprecation-warnings-in-python), you need to call that _before_ whatever code that is generating the warnings. So put it right under the `import warnings` statement. – shriakhilc May 23 '19 at 07:32
  • I did as you said and nothing has changed, the warning is still showing – Eugenia Castilla May 23 '19 at 07:48

2 Answers2

3

As the warning suggested, you may use the iter_rows method to iterate the cells.

Here is an example of using iter_rows to store cell values for first five rows.

from openpyxl import load_workbook
wb = load_workbook(filename = 'data.xlsx')
sheet = wb['Sheet1']
number_of_rows = sheet.max_row
number_of_columns = sheet.max_column

# Store values from first five rows
rows_iter = sheet.iter_rows(min_col=1, min_row=1,
                            max_col=number_of_columns, max_row=5)
values = [[cell.value for cell in row] for row in rows_iter]
print(values)

Output:

[['Segment', 'Country', 'Product', 'Discount Band', 'Units Sold', 'Manufacturing Price', 'Sale Price', 'Gross Sales', 'Discounts', ' Sales', 'COGS', 'Profit', 'Date', 'Month Number', 'Month Name', 'Year'], ['Government', 'Canada', 'Carretera', 'None', 1618.5, 3, 20, 32370, 0, 32370, 16185, 16185, datetime.datetime(2014, 1, 1, 0, 0), 1, 'January', '2014'], ['Government', 'Germany', 'Carretera', 'None', 1321, 3, 20, 26420, 0, 26420, 13210, 13210, datetime.datetime(2014, 1, 1, 0, 0), 1, 'January', '2014'], ['Midmarket', 'France', 'Carretera', 'None', 2178, 3, 15, 32670, 0, 32670, 21780, 10890, datetime.datetime(2014, 6, 1, 0, 0), 6, 'June', '2014'], ['Midmarket', 'Germany', 'Carretera', 'None', 888, 3, 15, 13320, 0, 13320, 8880, 4440, datetime.datetime(2014, 6, 1, 0, 0), 6, 'June', '2014']]

I have used Sample Excel Spreadsheet provided by Microsoft as data.xlsx

Installed packages:

et-xmlfile==1.0.1
jdcal==1.4.1
openpyxl==2.6.2
pkg-resources==0.0.0
arshovon
  • 13,270
  • 9
  • 51
  • 69
  • 2
    yes, 'fixing' the code seems like a much better way to solve this than just trying to turn off the warning message – Chris_Rands May 23 '19 at 09:50
  • @EugeniaCastilla, I am glad it worked. If the answer helped you, please upvote and accept it for future readers. :) – arshovon May 24 '19 at 08:24
  • how does the lib get to know the code is **iterate the cells**: `cell_obj = sheet.cell(row = i, column = 1)`? – Lei Yang Apr 07 '21 at 05:42
1

Since Python is an iterative language, statements get executed one-by-one.

Try putting the below statement

warnings.filterwarnings("ignore", category=DeprecationWarning)

before

cell_obj = sheet.cell(row = i, column = 1)

And it is advisable to have the warning filter statements at global space as locally suppressing a warning would not be reflective of the original intention.

varshith ramachandra
  • 29
  • 2