title: Apply xlsxwriter formatting to Excel file in AWS lambda function
body: <p>In a jupyter notebook I am able to create two Pandas dataframes and export them to individual sheets of an Excel workbook with some additional formatting, including text-wrapping, frozen pane, bold headers and auto-filters.</p>
  <p>Code below:</p>
  <pre><code># MODULES
  import pandas as pd 
  import numpy as np
  from datetime import date
  
  #CONSTRUCT DATAFRAME
  df = pd.DataFrame({'Data': [10, 22, 31, 43, 57, 99, 65, 74, 88],
                    'Data2':[10, 22, 31, 43, 57, 99, 65, 74, 88],
                    'Data3':[10, 22, 31, 43, 57, 99, 65, 74, 88]})
  
  #CONSTRUCT DATAFRAME
  df2 = pd.DataFrame({'df2_Data': ['blue', 'yellow', 'purple', 'orange', 'green', 'brown', 'gray', 'white', 'red'],
                    'df2_Data2':['bike', 'car', 'bus', 'train', 'boat', 'truck', 'plane', 'scooter', 'skateboard'],
                    'df2_Data3':['chicken', 'cow', 'dog', 'crocodile', 'snake', 'pig', 'rat', 'mouse', 'monkey']})
  
  
  #DATE FOR INCLUSION IN FILENAME 
  today = date.today()
  d2 = today.strftime(&quot;%B %d, %Y&quot;)
  
  
  writer = pd.ExcelWriter('ExcelExample{}.xlsx'.format(d2), engine='xlsxwriter')
  sheets_in_writer=['Sheet1','sheet2']
  
  data_frame_for_writer=[df, df2]
  
  for i,j in zip(data_frame_for_writer,sheets_in_writer):
      i.to_excel(writer,j,index=False)
      
  
  
  ### Assign WorkBook
  workbook=writer.book
  
  # Add a header format
  header_format = workbook.add_format({'bold': True,'text_wrap': True,'size':10,
                                                        'valign': 'top','fg_color': '#c7e7ff','border': 1})
  
  
  ### Apply same format on each sheet being saved
  for i,j in zip(data_frame_for_writer,sheets_in_writer):
      for col_num, value in enumerate(i.columns.values):
          writer.sheets[j].set_column(0, max_col - 1, 12)
          writer.sheets[j].write(0, col_num, value, header_format)
          writer.sheets[j].autofilter(0,0,0,i.shape[1]-1)
          writer.sheets[j].freeze_panes(1,0)
  writer.save()
  </code></pre>
  <p>This produces the following Excel workbook in which both/all sheets have the formatting applied.</p>
  <p><a href="https://i.stack.imgur.com/kqW7L.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kqW7L.png" alt="enter image description here" /></a>
  <a href="https://i.stack.imgur.com/QXVhy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QXVhy.png" alt="enter image description here" /></a></p>
  <p>I'm struggling to reproduce this functionality within an AWS lambda function due to my unfamiliarity with boto3</p>
  <p>My attempt is as follows, which does save an Excel file to the specified destination but of course has none of the required formatting.</p>
  <pre><code>import boto3 
  
  import numpy as np
  import pandas as pd
  
  import io 
  from io import BytesIO
  from io import StringIO 
  
  from datetime import date
  
  def lambda_handler(event, context):
  
      s3 = boto3.client('s3') 
  
      # STORE TODAY'S DATE 
      today = date.today()
      d2 = today.strftime(&quot;%B %d, %Y&quot;)
      print(&quot;d2 =&quot;, d2)
      
      #SPECIFY OUTPUT EXCEL FILE NAME AND FILEPATH 
      bucket = 'brnddmn-s3'
      filepath = 'output/'ExcelExample{}.xlsx'.format(d2)
      
      # GENERATE AND EXPORT EXCEL FILE TO BUCKET 
      with io.BytesIO() as output:
          with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
              df.to_excel(writer, sheet_name = 'Sheet1')
              df2.to_excel(writer, sheet_name = 'Sheet2')
          data = output.getvalue()
      s3 = boto3.resource('s3')
      s3.Bucket(bucket).put_object(Key=filepath, Body=data)
       
  </code></pre>
  <p>Any help greatly appreciated.</p>