Accessing header string-value of an Excel cell

Question

I have a table on an Excel file (.xlsx) that I created with Python 2.7:

On the first column, you can see the individuals (geometries) that are checked (respectively '3001', '3002' and '3003'). All geometries have a RelativeThickness law, a MaxThickness law and a position of the maximum thickness law, evolving along the geometry. There are criteria (not shown here) that say "OK" or "N.OK" (not OK) if the criteria of a certain law is not respected.

For example : the laws evolving along the geometries are in fact curves. If I put a criteria "Only allow 1 maxima on the curve" on the law "MaxThickness" and I apply this criteria to my 3 individuals, it's made to put 'OK' for 3001 and 3002 and 'N.OK' for 3003 (+ coloring red or green) since 3001 and 3002 only have 1 maxima along the curve but 3003 has 2 maxima. The global effect of all the criteria on all the laws is summarized in the "TOTAL" column.

I would like to go through each one of my rows and to check when there's a "N.OK". If there's a "N.OK" I would like Python's code to return me the header concerned (for example : Python wouldn't return me anything for 3001 and 3002 but for 3003 it would return me "MaxThickness", "MaxThicknessPosition" and "TOTAL").

It would be like this (but I don't know how to access the header and return its string value) :

for row in sheet1.iter_rows(min_row=1, max_col=6, max_row=4):
    for cell in row:
        if cell.value == 'N.OK':
            header = cell.header

"sheet1" is my active sheet on the Excel file (wb=Workbook() and sheet1=wb.active).

Important : I have to stay on Python 2.7 and I can't use pandas (I can't use cmd window to use conda, anaconda, miniconda, pip,... because I can't have admin access) and I already tried to download and install it manually but I have a problem with the C files that are compressed in it. I found that but I'm not sure I understand how it works.

If this is possible, I would also like to "ignore" the "TOTAL" column. I would need to print or to store why an individual is rejected (based on what law that is not respected) to be able to say "3003 is rejected because of MaxThickness and MaxThicknessPosition" but I don't want to print or to store "TOTAL is not respected".

Thank you very much if you can help me with this !

score 1 · Accepted Answer · answered Oct 19 '18 at 15:58

1

You just need to create something to look up the headers.

headers = dict((c.column, c.value for c in ws[1]))

for row in ws.iter_rows((min_row=1, max_col=6, max_row=4):
    for cell in row:
        if cell value == "N.OK":
             header = headers[column]

For what it's worth I think it's pretty idiotic for someone coming to Python to be forced to work with Python 2.7. Libraries, including openpyxl, will soon no longer work with Python 2.

answered Oct 19 '18 at 15:58

Charlie Clark

18,477
4
49
55

Thank you very much ! I just had to remove one ")" and one "(", and to add one ")" after "c.value" and I had to add "." between "cell" and "value" and I had to change "headers[column]" by "headers[cell.column]" to make it work. But thank you, I have now exactly what I wanted without using any additional modules I'm so thankfull for that ! Have a great day ! :) – Q.L. Oct 22 '18 at 07:26

Accessing header string-value of an Excel cell

1 Answers1