0

I am trying to open an existing excel file from S3 with 2 tabs. The first tab has a pivot table with the second tab being the data source for the pivot table. I am pulling data in from an external data source, putting it into a data frame and would like to replace the second tab with the data frame. The file needs to end up in S3 with the pivot table intact so that the report can be emailed out.

This is what I am currently trying:

import pandas as pd
import io
import boto3
import xlsxwriter

df1 = pd.read_csv(data, sep=",")

bucket = 'bucket-name'
filepath = 'my_file.xlsx'
    
with io.BytesIO() as output:
   with pd.ExcelWriter(output, engine='xlsxwriter', if_sheet_exists='replace', mode='a') as writer:
       df1.to_excel(writer, 'Data')
   data = output.getvalue()
   s3 = boto3.resource('s3')
   s3.Bucket(bucket).put_object(Key=filepath, Body=data)

This is the error I am getting:

[ERROR] ValueError: Append mode is not supported with xlsxwriter!

Is there another way to complete this process or even an entirely different way to accomplish this outcome?

rujole13
  • 59
  • 6
  • Does this answer your question? [pandas.ExcelWriter ValueError: Append mode is not supported with xlsxwriter](https://stackoverflow.com/questions/54863238/pandas-excelwriter-valueerror-append-mode-is-not-supported-with-xlsxwriter) – ZabielskiGabriel Aug 16 '22 at 13:52
  • 1
    @ZabielskiGabriel Thanks for the answer but this unfortunately does not work. Since openpyxl requires it to be an xlsx file, it will not work with BytesIO. I need the BytesIO buffer to take the output before replacing the tab. – rujole13 Aug 18 '22 at 13:18

0 Answers0