AWS Lambda: encoding problem when creating and downloading EXCEL file

Question

I’m creating a microservice to dynamically generate EXCEL files and download them.

I have an AWS Api Gateway and a Python 3.8 Lambda function. To create the EXCEL I use the Openpyxl package.

The file is generated fine but when I download it I seem to get some encoding problem I can’t figure out.

The Lambda

Here I summarize the function, highlighting the end, where I save the file to a buffer and return it.

wb = I create my Workbook correctly

buffer = io.BytesIO()
wb.save(buffer)
    
excel_final = buffer.getvalue()
buffer.close()
         
response = excel_final

return response

When testing the Lambda output, I see this response, that look Unicode:

“PK\u0003\u0004\u0014\u0000\u0000\u0000\b\u0000}t\u008dT\u………….”

Api Gateway

The Integration Response is set to Passthrough and the Method Response to application/xml. Tried with different setups but couldn’t get it working.

When I test the API method, I also get the data in the following format, I think it’s utf-8 decoded:

“PK�u�TAMb��docProps/app.xmlM�= 1D��q��A�Bb@�R��{/�dC�B~�9��noF� g*�-�T��"��N]�n�h�cy;�Ό�HI`�� M��F�r�xN��pe'å! �rmީ�5�&��;i^PK�u�T�z��docProps/core.xml��N�0�_e�u�V=DY.C�@Bb�[�x[E�F�Q��'-[��V��

Javascript

In my request.onload I get the bytes response and turn it into a Blob for download.

let blob = this.response;
let final_blob = new Blob([blob], {type: 'application/xml'})

The file is downloaded but recognized as corrupt. Can’t open it.

Tests

I tried different Content-Types, coding/encoding in Lambda or Javascript, but never got it working.

Any clues welcome!

Is API Gateway Binary Support set up correctly? This article is a good practical example: aws.amazon.com/blogs/compute/… — tankthinks
– tankthinks, Commented Apr 13, 2022 at 15:06
Also, we often do file downloads with Lambda at my company. Whenever we do, we save the file in S3 and return a S3 presigned url to the Web App. This is a nice pattern that avoids all of the API Gateway headache. — tankthinks
– tankthinks, Commented Apr 13, 2022 at 15:08
@tankthinks Read the article and other similar, It looks it has more to do with byte input rather than output. When you edit "Integration Request" what you are affecting is the input before Lambda, right? — Francisco Ghelfi
– Francisco Ghelfi, Commented Apr 13, 2022 at 15:43
@tankthinks I thought about saving to S3, but seems a too expensive walkaround solution for something that must be only a problem of encoding. I made another similar service to create/download PDFs with no problem. — Francisco Ghelfi
– Francisco Ghelfi, Commented Apr 13, 2022 at 15:44

Francisco Ghelfi · Accepted Answer · 2022-04-20 02:42:20Z

2

I finally got it working and I’m able to download PDF, EXCEL or ZIP files directly from Lambda, without actually storing the file in a S3 bucket.

Lambda

Communication between Lambdas and Api Gateway requires base64 encoding, so in the Lambda I just return the bare base64 encoded binary.

wb = I create my Workbook correctly 
buffer = io.BytesIO() 
wb.save(buffer) 
excel_final = buffer.getvalue() 
buffer.close()

return base64.b64encode(excel_final)

Api Gateway - Settings

First go to Settings of your API and add application/ms-excel at 'Binary Media Types'.

Api Gateway - Method Response

Add Content-Type header at ‘Response Headers for 200’.

Add application/ms-excel response model at ‘Response Body for 200’

Api Gateway - Integration Response

Set Content handling to Convert to binary (if needed).

Set Content-Type value to application/ms-excel at ‘Header Mappings’.

Frontend

Now you’ll receive a binary file as your response. Just generate the blob and download as you prefer.

let blob = this.response;
let final_blob = new Blob([blob], {type: 'application/xml'});

// In my case I generate a link and click it

Conclusion

In my rookiness, I was messing around with the config having always something wrong in the chain. In my defense I must say documentation didn’t help too much.

Once I got it working It doesn’t seem like much of a hassle and works like a charm with any kind of binary file.

With the S3 approach you are required needlessly to store a file, make another request to download it and finally delete it from the bucket.

Hope it helps!

answered Apr 20, 2022 at 2:42

Francisco Ghelfi

9621 gold badge16 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Himasnhu Over a year ago

This approach looks good even I have a similar usecase but when I try to open the excel file in my case it gives me "can't open file the extension and format don't match" any idea what might be causing it ? I made the changes to API gateway config as recommended in your answer and encoded my response to base64

Francisco Ghelfi Over a year ago

Hi @Himanshu I guess your are not generating the workbook correctly, before using the pipeline to deliver the file.

Collectives™ on Stack Overflow

AWS Lambda: encoding problem when creating and downloading EXCEL file

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related