title: Trying to style large number of rows with openpyxl, is there a 'faster' way?
body: <p>I've got a function I use to check and style rows of a spreadsheet based on matching criteria with openpyxl. While this has been functional for nearly every smaller sheet as I've graduated to using large sets of rows the speed has slowed considerably.</p>
  <p>I'm struggling to find ways to speed it up and was hoping for some help figuring out what I'm missing.</p>
  <p>I believe my problem is that I am looping through every row, but then looping through each cell of the target row to match a specific column value. Though I am not sure what I can do to get around this issue (Shown below):</p>
  <pre><code>    for row in sheet.iter_rows():
          for cell in row:
              cell.alignment = Alignment(wrapText=True)
              if row[type_row].value in group1:
  </code></pre>
  <p>Entire code of the function and another function I use to actually format the cells:</p>
  <pre><code>import openpyxl as opxl
  from openpyxl.styles import Font, Border, Side, PatternFill, Alignment, NamedStyle
  
  def format_cell(cell, fill, font, border):
      cell.fill = fill
      cell.font = font
      cell.border = border
  
  def set_style_base(sheet, type_row, col_for_other, col_for_step):
      group1 = ['Temp1', 'Temp2', 'Temp3']
      group2 = ['ACTIVITY']
      group3 = ['ACTIVITY2']
      group4 = ['OPEN']
      group5 = ['BREAK']
      group6 = ['WARNING']
      group7 = ['DIAGNOSTIC']
      pass_start_fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')
      pass_end_fill = PatternFill(start_color='99FF99', end_color='99FF99', fill_type='solid')
      error_start_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
      error_end_fill = PatternFill(start_color='FF6666', end_color='FF6666', fill_type='solid')
      fail_start_fill = PatternFill(start_color='FF8000', end_color='FF8000', fill_type='solid')
      fail_end_fill = PatternFill(start_color='FFB266', end_color='FFB266', fill_type='solid')
      group2_fill = PatternFill(start_color='FFA500', end_color='FFA500', fill_type='solid')
      group3_fill = PatternFill(start_color='000000', end_color='000000', fill_type='solid')
      group4_fill = PatternFill(start_color='00BFFF', end_color='00BFFF', fill_type='solid')
      group5_fill = PatternFill(start_color='800080', end_color='800080', fill_type='solid')
      group6_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
      thick = Side(border_style=&quot;thick&quot;)
      border_thick = Border(top=thick, left=thick, right=thick, bottom=thick)
      bold_font = Font(bold=True)
      def_font = Font(bold=False)
      for row in sheet.iter_rows():
          for cell in row:
              cell.alignment = Alignment(wrapText=True)
              if row[type_row].value in group1:
                  if row[col_for_step].data_type == &quot;s&quot;:
                      if 'START:' in row[col_for_step].value:
                          if row[col_for_other].data_type == &quot;s&quot;:
                              if row[col_for_other].value == 'PASS':
                                  format_cell(cell, pass_start_fill, bold_font, border_thick)
                              if row[col_for_other].value == 'FAIL':
                                  format_cell(cell, fail_start_fill, bold_font, border_thick)
                              if row[col_for_other].value == 'ERROR':
                                  format_cell(cell, error_start_fill, bold_font, border_thick)
                          if 'END:' in row[col_for_step].value:
                              if row[col_for_other].data_type == &quot;s&quot;:
                                  if row[col_for_other].value == 'PASS':
                                      format_cell(cell, pass_end_fill, bold_font, border_thick)
                                  if row[col_for_other].value == 'FAIL':
                                      format_cell(cell, fail_end_fill, bold_font, border_thick)
                                  if row[col_for_other].value == 'ERROR':
                                      format_cell(cell, error_end_fill, bold_font, border_thick)
              if row[type_row].value in group2:
                  format_cell(cell, group2_fill, bold_font, border_thick)
              if row[type_row].value in group3:
                  format_cell(cell, group3_fill, def_font, border_thick)
                  cell.font = Font(color = 'FFA500') #Font color needs to be set
              if row[type_row].value in group4:
                  format_cell(cell, group4_fill, bold_font, border_thick)
              if row[type_row].value in group5:
                  format_cell(cell, group5_fill, bold_font, border_thick)
              if row[type_row].value in group6:
                  format_cell(cell, group6_fill, def_font, border_thick)
                  cell.font = Font(color = '000000') #Font color needs to be set
              if row[type_row].value in group7: #special case as 'DIAGNOSTIC' has no fill
                  cell.border = Border(top=thick, left=thick, right=thick, bottom=thick)
                  cell.font = Font(bold=True)
              if row[type_row].value not in [group1, group2, group3, group4, group5, group6, group7]:
                  if row[col_for_step].data_type == &quot;s&quot;:
                      if 'START:' in row[col_for_step].value:
                          if row[col_for_other].data_type == &quot;s&quot;:
                                  if row[col_for_other].value == 'PASS':
                                      format_cell(cell, pass_start_fill, bold_font, border_thick)
                                  if row[col_for_other].value == 'FAIL':
                                      format_cell(cell, fail_start_fill, bold_font, border_thick)
                                  if row[col_for_other].value == 'ERROR':
                                      format_cell(cell, error_start_fill, bold_font, border_thick)
                      if 'END:' in row[col_for_step].value:
                          if row[col_for_other].data_type == &quot;s&quot;:
                              if row[col_for_other].value == 'PASS':
                                  format_cell(cell, pass_end_fill, bold_font, border_thick)
                              if row[col_for_other].value == 'FAIL':
                                  format_cell(cell, fail_end_fill, bold_font, border_thick)
                              if row[col_for_other].value == 'ERROR':
                                  format_cell(cell, error_end_fill, bold_font, border_thick)
  </code></pre>
  <p>Any direction or suggestions would be amazing.
  Thanks</p>