LambdaでExcelを操作してみた（openpyxl）

sam init \
    --runtime python3.11 \
    --name lambda-excel-sample \
    --app-template hello-world \
    --no-tracing \
    --no-application-insights \
    --structured-logging \
    --package-type Zip

openpyxl

import os
import boto3

from datetime import datetime, timedelta, timezone

from openpyxl import load_workbook

# DynamoDBなどから取得したデータを想定しています
DATA = [
    {"timestamp": 1690188000000, "temperature": 32.9, "humidity": 64},
    {"timestamp": 1690191000000, "temperature": 30.7, "humidity": 58},
    {"timestamp": 1690194000000, "temperature": 34.1, "humidity": 54},
    {"timestamp": 1690197000000, "temperature": 36.5, "humidity": 59},
    {"timestamp": 1690200000000, "temperature": 33.3, "humidity": 66},
    {"timestamp": 1690203000000, "temperature": 29.8, "humidity": 49},
]

JST = timezone(timedelta(hours=+9), "JST")

s3 = boto3.client("s3")

BUCKET_NAME = os.getenv("BUCKET_NAME")
KEY_NAME = "sample.xlsx"

OUTPUT_FILE_NAME = "output.xlsx"

def lambda_handler(event, context):
    local_file_path = download_file(BUCKET_NAME, KEY_NAME)
    if local_file_path is None:
        return {
            "statusCode": 500,
        }

    output_file_path = os.path.join("/tmp", OUTPUT_FILE_NAME)

    res = make_excel(local_file_path, output_file_path)
    if res is None:
        return {
            "statusCode": 500,
        }

    upload_file(BUCKET_NAME, OUTPUT_FILE_NAME, output_file_path)

    return {
        "statusCode": 200,
    }

def download_file(bucket_name: str, file_name: str) -> str | None:
    local_file_path = os.path.join("/tmp", file_name)

    s3.download_file(bucket_name, file_name, local_file_path)

    if os.path.exists(local_file_path):
        return local_file_path

    return None

def make_excel(local_file_path: str, output_file_path: str) -> str | None:
    wb = load_workbook(filename=local_file_path)
    ws_test1 = wb["テスト1"]

    for i, d in enumerate(DATA, start=2):
        ws_test1[f"A{i}"] = datetime.fromtimestamp(
            d["timestamp"] / 1000, tz=JST
        ).strftime("%Y-%m-%d %H:%M:%S")
        ws_test1[f"B{i}"] = d["temperature"]
        ws_test1[f"C{i}"] = d["humidity"]

    wb.save(output_file_path)

    if os.path.exists(output_file_path):
        return output_file_path

    return None

def upload_file(bucket_name: str, file_name: str, file_path: str) -> None:
    s3.upload_file(file_path, bucket_name, file_name)

sam build --use-container
sam deploy \
    --guided \
    --region ap-northeast-1 \
    --stack-name lambda-excel-sample-stack

aws s3 cp sample.xlsx s3://excel-xxx-ap-northeast-1-bucket

curl https://xxx.execute-api.ap-northeast-1.amazonaws.com/Prod/hello/