openpyxl delete all values except the column row excel

Tags: , ,



I have an excel:

   A      B        C             D
1  long  short  suggested_long  suggested_short
2  -2.11  5.11   #N/A           
3  -4.11  3.66   #REF!           #N/A
4  93.44  7.55   0               0
5  1256.4 966.5  563.5           #REF!

So, long is in cell(A1), 5.11 is in cell(B2); I want to clear all the values in suggested_long and suggested_short but need to keep the column name rows as I need to insert data after cleaning all the values. Then the result should be:

   A      B        C             D
1  long  short    
2  -2.11  5.11              
3  -4.11  3.66             
4  93.44  7.55               
5  1256.4 966.5             

I know openpyxl can do like:

for row in ws['C1:D5']:
  for cell in row:
    cell.value = None

to delete the value in the cell, but I have uncertain number of cell values in suggested_long and suggested_short. I assume others may have the same problem, so I post it here, many thanks

Answer

Try this:

for row in ws['C2:D'+str(len(ws['D']))]:
  for cell in row:
    cell.value = None


Source: stackoverflow