title: Python/Excel: How to freeze the top line of a generated sheet (or sheets)
body: <p>This isn't actually a question -- it's more of an FYI.  It took me a while to figure this out, since my searching with Google turned up bits and pieces (including some related questions here on StackOverflow), but no article or blurb gave me the whole answer all in one place.  I was finally able to figure it out by recording a macro in Excel and looking at its source code and combining what I found there with what I found on the web.</p>
  
  <p>Anyway, the following code snippets show two helper functions, plus a sample main() type of function (which I always use, even in Perl and Python apps, since I have a C/C++/Java background).</p>
  
  <p>The first helper method will auto-fit every used column on the given sheet.  Since the auto-fit logic depends on the data in the cells, as well as the font(s) in use, you shouldn't call this method until the entire sheet has been completely populated with your data.  Also, I'm pretty new to VBA, so I'm not so sure that this line:</p>
  
  <pre><code>        sheet.Cells(1, i).EntireColumn.AutoFit()
  </code></pre>
  
  <p>is correct, but it does seem to work just fine.  If it's not entirely correct, maybe you can post a correction.</p>
  
  <pre><code>def autoFitSheet(sheet):
      """Auto-fits all the used columns on the given sheet.  Obviously, this
      method should only be called _after_ the sheet has been populated."""
  
      firstCol    = sheet.UsedRange.Column
      numCols     = sheet.UsedRange.Columns.Count
  
      for i in range(firstCol, (firstCol + numCols)):
          sheet.Cells(1, i).EntireColumn.AutoFit()
  </code></pre>
  
  <p>The second helper is a "freeze the top line" method.  All it does is freeze the top line of the sheet that you pass it.  A side effect of this method is that the sheet becomes the active sheet in the spreadsheet, so you might need/want to activate some other sheet once all your sheets' top lines have been frozen.</p>
  
  <pre><code>def freezeTopLine(window, sheet):
      """Freezes the top line of the given sheet."""
  
      sheet.Activate()
  
      window.SplitColumn  = 0
      window.SplitRow     = 1
      window.FreezePanes  = True
  </code></pre>
  
  <p>The last snippet is a sample main() function, which just shows how to call the two helper methods.  I'm actually using code very similar to this to populate a spreadsheet with mutliple tabs/sheets, and it works perfectly (on Windows 7 -- can't guarantee how well it will work on your system).</p>
  
  <pre><code>def main(...):
      #
      # (whatever...)
      #
  
      excel       = win32.gencache.EnsureDispatch('Excel.Application')
      workbook    = excel.Workbooks.Add()
      sheets      = workbook.Sheets
      window      = workbook.Windows(1)
  
      sheet1 = workbook.Worksheets('Sheet1')
      straddles.Name = 'My First Sheet'
      straddles.Tab.ColorIndex = 6 # yellow
  
      sheet2 = workbook.Worksheets('Sheet2')
      cashSummary.Name = 'Some Other Sheet'
      cashSummary.Tab.ColorIndex = 4 # bright green
  
      #
      # Populate sheets...
      #
  
      for sheet in sheets:
          autoFitSheet(sheet)
          freezeTopLine(sheet)
  
      #
      # (whatever...)
      #
  </code></pre>
  
  <p>Like I said, it took me a while to figure this out, and I was hoping that maybe I could save someone some of the headache that I went through.  Good luck.</p>