title: How to write a Python Pandas DataFrame to an .XLS file with specific column type formatting?
body: <p>I want to first start off by showing an example that works with XLSX (but unfortunately I need XLS and this method does not work for that). Just to re-clarify, <strong>my main issue is the column-type formatting</strong> for a resulting XLS file.</p>
  
  <pre><code># Method that works for XLSX
  import pandas as pd
  
  # Example Dataframe
  df = pd.DataFrame({'Numbers': [1000, 2000, 3000, 4000, 5000],
                     'Names': ['Adam','Blake','Chad','Drake','Erak'],
                     'Weights': [1.05, 2.10, 3.15, 4.20, 5.25],
  })
  
  # Create a Pandas Excel writer
  writer = pd.ExcelWriter(r'c:\users\3619678\desktop\example_file.xlsx')
  
  # Convert the dataframe to an Excel object
  df.to_excel(writer, sheet_name='Sheet1', index=False)
  
  # Get the xlsxwriter workbook and worksheet objects
  workbook  = writer.book
  worksheet = writer.sheets['Sheet1']
  
  # Define cell formats
  number_format = workbook.add_format({'num_format': '0.00'})
  
  # Set a certain column's width and apply certain format
  worksheet.set_column('A:A', 20, number_format)
  
  # Close the Pandas Excel writer and output the Excel file.
  writer.save()
  </code></pre>
  
  <p>Now here is the closest attempt I have so far for the XLS version:</p>
  
  <pre><code>import pandas as pd
  
  # Example Dataframe
  df = pd.DataFrame({'Numbers': [1000, 2000, 3000, 4000, 5000],
                     'Names': ['Adam','Blake','Chad','Drake','Erak'],
                     'Weights': [1.05, 2.10, 3.15, 4.20, 5.25],
  })
  
  import xlwt
  
  # Create a workbook and add a worksheet
  workbook = xlwt.Workbook(r'c:\users\3619678\desktop\example_file.xls')
  worksheet = workbook.add_sheet('sheet1',cell_overwrite_ok=True)
  
  # Create formats
  number_format = xlwt.easyxf(num_format_str='0.00')
  
  # Iterate over the data and write it out row by row
  for x, y in df.iterrows():
      for z, value in enumerate(y):
          worksheet.write(x, z, value, number_format)
  
  # Save/output the workbook
  workbook.save(r'c:\users\3619678\desktop\example_file.xls')
  </code></pre>
  
  <p>However the problem with this version is that not only does it not show the headers, it applies the format to all cells. Would really appreciate if someone could help me out, I've spent forever on this!</p>