Read, change, and write Excel files on GCP App Engine Storage

Thing you want to do

  1. Read Excel file from GCP Storage
  2. Excel content change
  3. Write Excel file to GCP Storage
  4. File download

Example

# -*- coding: utf-8 -*-
import logging
from flask import render_template, Flask, Response
from google.cloud import storage
import openpyxl
from openpyxl.writer.excel import save_virtual_workbook
import io

app = Flask(__name__)

logging.getLogger().setLevel(logging.INFO)


@app.route('/applicationform/a0026', methods=['GET', 'POST'])
def a0026():
    client = storage.Client()
    bucket = client.get_bucket('★ Project name ★.appspot.com')
    blob = bucket.blob('a0025.xlsx')
    blob_io = io.BytesIO(blob.download_as_string())
    wb = openpyxl.load_workbook(blob_io, keep_vba=False)
    sheet = wb.get_sheet_by_name('★ Sheet name ★')
    sheet['K5'] = 'Test Test'
    save_data = save_virtual_workbook(wb)
    new_blob = bucket.blob('a0025_new.xlsx')
    new_blob.upload_from_string(save_data, content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    res = Response(save_data, content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    res.headers['Content-Disposition'] = 'attachment; filename="a0025_new.xlsx"'

    return res


if __name__ == '__main__':
    app.run(host='127.0.0.1', port=8080, debug=True)

For files with macros

** Set keep_vba to True. ** **

wb = openpyxl.load_workbook(blob_io, keep_vba=True)

** Changed Content-Type to "application / vnd.ms-excel.sheet.macroEnabled.12" **

new_blob.upload_from_string(save_data, content_type='application/vnd.ms-excel.sheet.macroEnabled.12')
res = Response(save_data, content_type='application/vnd.ms-excel.sheet.macroEnabled.12')

** Extension change **

new_blob = bucket.blob('a0025_new.xlsm')
res.headers['Content-Disposition'] = 'attachment; filename="a0025_new.xlsm"'

memo

Recommended Posts

Read, change, and write Excel files on GCP App Engine Storage
Read and write csv files with numpy
Read and write JSON files in Python
Read files on GCS using Cloud Storage Client Library
Read and write files with Slackbot ~ Bot development with Python ~
[Python] Use this to read and write wav files [wavio]
Read and write csv file
Read and write a file
Using properties files with Flexible Environment Java 8 on Google App Engine
Deploy a Python app on Google App Engine and integrate it with GitHub