aws s3 excel 파일을 aws lambda에서 읽기 / get excel file from s3 in lambda

import { GetObjectCommand } from "@aws-sdk/client-s3";
import { S3Client } from "@aws-sdk/client-s3";
import XLSX from 'xlsx';

 
const REGION = "ap-northeast-2"; //e.g. "us-east-1"
const s3Client = new S3Client({ region: REGION });
 
const dateFmt = "yyyy-mm-dd hh:mm:ss AM/PM";



export const handler = async(event,context) => {
    
    const bucket = event.Records[0].s3.bucket.name;
    const key = decodeURIComponent(event.Records[0].s3.object.key.replace(/\+/g, ' '));
    const bucketParams = {
        Bucket: bucket,
        Key: key,
    };
     
    console.info(key)
    /////////////////////////////////////////////////////////////
    
    try {
        
          
        const concatStream = (stream) =>
          new Promise((resolve, reject) => {
            const chunks = [];
            stream.on("data", (chunk) => chunks.push(chunk));
            stream.on("error", reject);
            stream.on("end", () => resolve(Buffer.concat(chunks)));
          });
        
        // S3에서 객체 가져오기
        const data = await s3Client.send(new GetObjectCommand(bucketParams));
        const bodyContents = await concatStream(data.Body);
        
        // sheetjs에서 엑셀 읽기
        const workbook = XLSX.read(bodyContents)
        console.info(workbook)
        const sheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[sheetName];
        const range = XLSX.utils.decode_range(worksheet["!ref"]);
        
        for (let rowNum = 4; rowNum <= range.e.r - range.s.r + 1; rowNum++) {
            const values = [];
            for (let colNum = 1; colNum <= range.e.c - range.s.c + 1; colNum++) {
                const cellAddress = XLSX.utils.encode_cell({
                    r: rowNum - 1,
                    c: colNum - 1,
                });
                const cell = worksheet[cellAddress] || { t: "s", v: "" };
                if (cell.t === "s" && colNum >= 9) {
                    values.push(getHyperlinkAddress(cell));
                } else if (cell.t === "n" && colNum === 2) {
                    const dateString = XLSX.SSF.format(dateFmt, cell.v);
                    values.push(dateString);
                } else {
                    values.push(cell.v);
                }
            }
            console.info(values);
        }
        
        return {
            statusCode: 200,
            body: JSON.stringify(bodyContents),
        };
    } catch (err) {
        console.log("Error", err);
    }
};

function getHyperlinkAddress(cell) {
  if (cell.l) {
    return cell.l.Target;
  }
  return "-";
}

 

서드파티 라이브러리 임포트를 위한 계층

Lambda 계층 만들기 및 공유 - AWS Lambda

s3 객체 가져오기

AWS SDK를 사용하여 Amazon S3 버킷에서 객체 가져오기 - Amazon Simple Storage Service

sheetjs에서 aws와 사용하기

Amazon Web Services | SheetJS Community Edition