title: select cells from an excel table python
body: <p>i have a an excel table that contains :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID product</th>
  <th>03/1/2021</th>
  <th>16/1/2022</th>
  <th>12/2/2022</th>
  <th>14/3/2023</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>A</td>
  <td>4</td>
  <td>1</td>
  <td>2</td>
  <td>5</td>
  </tr>
  <tr>
  <td>B</td>
  <td>6</td>
  <td>1</td>
  <td>3</td>
  <td></td>
  </tr>
  <tr>
  <td>C</td>
  <td></td>
  <td>7</td>
  <td></td>
  <td>6</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>and in the same sheet I have a drop down list that contains(the year , and the month)</p>
  <p><a href="https://i.stack.imgur.com/qFN5C.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qFN5C.png" alt="enter image description here" /></a></p>
  <ul>
  <li>if i select in the drop down list for example year = 2020 and month= 1,</li>
  </ul>
  <p>it will be return something like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID product</th>
  <th>03/1/2021</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>A</td>
  <td>4</td>
  </tr>
  <tr>
  <td>B</td>
  <td>6</td>
  </tr>
  <tr>
  <td>C</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>and then it will calculate the som of the cells : som = 10 in this case</p>
  <p>here is my code :</p>
  <pre><code># import load_workbook
  import pandas as pd
  import numpy as np
  from openpyxl import load_workbook
  from openpyxl.worksheet.datavalidation import DataValidation
  from openpyxl import Workbook
  from openpyxl.styles import PatternFill
  
  # set file path
  filepath= r'test.xlsx'
  
  wb=load_workbook(filepath)
  ws=wb[&quot;sheet1&quot;]
  
  
  #Generates 10 year in the Column MK;
  for number in range(1,10): 
      ws['MK{}'.format(number)].value= &quot;202{}&quot;.format(number)
      
  data_val = DataValidation(type=&quot;list&quot;,formula1='=MK1:MK10') 
  ws.add_data_validation(data_val)
  
  # drop down list with all the values from the column MK
  data_val.add(ws[&quot;E2&quot;]) 
  
  #Generates the numbers of month in the Column MN;
  for numbers in range(1,12): 
      ws['MN{}'.format(numbers)].value= &quot;{}&quot;.format(numbers)
      
  
  data_vals = DataValidation(type=&quot;list&quot;,formula1='=MN1:MN14') 
  ws.add_data_validation(data_vals)
  
  
  # drop down list with all the values from the sheet list column MK
  data_vals.add(ws[&quot;E3&quot;]) 
        
  # add a color to the cell 'year' and 'month'
  ws['E2'].fill = PatternFill(start_color='FFFFFF00', end_color='FFFFFF00', fill_type = 'solid')
  ws['E3'].fill = PatternFill(start_color='FFFFFF00', end_color='FFFFFF00', fill_type = 'solid')
  
  
  # save workbook
  wb.save(filepath)
  </code></pre>
  <p>Any suggestions?</p>
  <p>thank you for your help.</p>