JS에서 엑셀 Import/Export

juhojung·2022년 3월 6일
2

Javascript

목록 보기
1/3

문서관리를 서비스하는 입장에서 엑셀이라는 도구는 필수였다.

그래서 서비스에서 엑셀을 Import/Export 하는 것을 작업했고,
나중에 또 삽질하지 않기위해 혹은 누군가 삽질하지말라고 작성 해본다.

https://github.com/exceljs/exceljs - exceljs라는 라이브러리인데, json으로 뽑아주고, 파일로 변환해주는등 많은 기능이 들어있는 라이브러리이다.

https://github.com/SheetJS/sheetjs - 대표적으로 사용하는 js excel 라이브러리이고, 사용하고 싶지 않았으나 어쩔 수 없이 사용했다..(?)

나는 위 두개 라이브러리를 사용했다.

1. Export to Excel

const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet("sheet1"); // sheet1 이라는 이름으로 시트생성
// ~~~ sheet에 데이터를 이러쿵저러쿵 넣어줌 ~~~

const _filename = `temp${new Date().getTime()}.xlsx`;
    await workbook.xlsx.writeFile(_filename);  // filename은 임시 파일이므로 어지간하면 겹치지않게 getTime

    res.setHeader("Content-disposition", "attachment; filename=" + getDownloadFilename(req, "ReviewComment.xlsx")); // 다운받아질 파일명 설정
    res.setHeader("Content-type", "application/vnd.ms-excel; charset=utf-8"); // 파일 형식 지정

    var filestream = fs.createReadStream(_filename); // readStream 생성
    filestream.pipe(res); // express 모듈의 response를 pipe에 넣으면 스트림을 통해 다운로드된다.

    fs.unlinkSync(_filename); // 다운했으니 삭제

해당 코드는 API작성 중간에 util에 정의해놓고 불러다 쓰게끔 하였다.
참고로 res 는 express.Response 이다.

2. Merge Cell

 merge_list = [
   "A1:B1", 
   "A2:B2", 
   "A3:B3", 
   "A4:B4", 
   "F1:I1", 
   "F2:I4", 
   "Q1:R1", 
   "Q2:R2", 
 ];

위와 같이 병합할 셀들의 정보를 작성하고 (셀위치:셀위치)

// cell merged
...javascript
const sheet = workbook.addWorksheet("sheet1");
for (var merge of merge_list) {
	sheet.mergeCells(merge);
}

sheet.mergeCells 로 병합하면 된다.

3. Cell Style

cell_styles: {
        cell: string;
        row?: number;
        rowCount?: number;
        horizontal?: "center" | "left" | "right";
        vertical?: "top" | "middle" | "bottom" | "distributed" | "justify";
        font?: {
            name?: string;
            size?: number;
            family?: number;
            scheme?: "minor" | "major" | "none";
            charset?: number;
            bold?: boolean;
            italic?: boolean;
            underline?: boolean | "none" | "single" | "double" | "singleAccounting" | "doubleAccounting";
            vertAlign?: "superscript" | "subscript";
            strike?: boolean;
            outline?: boolean;
        };
        border?: { top?: Border; right?: Border; left?: Border; bottom?: Border };
        fill?: Fill;
    }[] = [
        //header prev
        { cell: "C1", horizontal: "center" },
        { cell: "C2", horizontal: "center" },
        { cell: "C3", horizontal: "center" },
        { cell: "C4", horizontal: "center" },
        { cell: "D3", font: { bold: true } },
        { cell: "F1", horizontal: "center", font: { size: 12, bold: true } },
        { cell: "F2", horizontal: "center", vertical: "middle", font: { size: 20, bold: true } },
        { cell: "P1", horizontal: "center" },
        { cell: "P2", horizontal: "center" },
        //
        // header
        {
            cell: "A5",
            row: 5,
            rowCount: 31,
            horizontal: "center",
            vertical : "middle",
            font: { bold: true, size: 11 },
            border: {
                top: { style: "medium", color: { argb: "000" } },
                left: { style: "thin", color: { argb: "000" } },
                right: { style: "thin", color: { argb: "000" } },
                bottom: { style: "thin", color: { argb: "000" } },
            },
            fill: { type: "pattern", pattern: "solid", fgColor: { argb: "B6DDE8" } },
        },
        //
    ];

이런식으로 스타일을 정의 해준다면,

for (var i = 0; i < cell_styles.length; i++) {
        let style = cell_styles[i];
        if (style.row != undefined) { // 행에 일괄 적용되어야하는 스타일
            let row = sheet.getRow(style.row);
            for (var j = 1; j <= style.rowCount; j++) {
                let cell = row.getCell(j);
                let alignment = {};
                if (style.horizontal) Object.assign(alignment, { horizontal: style.horizontal });
                if (style.vertical) Object.assign(alignment, { vertical: style.vertical });
                cell.alignment = alignment;
                cell.font = { ...style.font };
                cell.border = style.border;
                cell.fill = style.fill;
            }
        } else {
            let cell = sheet.getCell(style.cell);
            cell.alignment = { horizontal: style.horizontal, vertical: style.vertical };
            cell.font = { ...style.font };
            cell.border = style.border;
            cell.fill = style.fill;
        }
    }

위와같이 sheet.getRow Or sheet.getCell 로 데이터를 가져온다음 스타일을 넣어주면된다.
각 스타일별로의 설명은 난이도가 낮으므로 생략

4. Excel Image 처리

사실 이 velog 에서 내가 얘기하고 싶었던건, 이부분의 비중이 크다.

필자가 만나본 엑셀 사용자 중 대부분이 엑셀에 셀 안에 그림을 삽입해서 사용하는것을 확인했다. 해당 기능을 만들어봤다.
ExcelJs 라이브러리 에서는 이미지데이터를 가져오는것이 Pro? 라이센스로 빠져있어서 사용못하고,
기능좋아서 구매 방법 슬쩍봤는데 답도없더라..
xlsx(sheetjs) 로는 구현이 가능하다고 github 에서 소문을 듣고 만들어봤다.
4-1. Excel Import with Image
아래는 이미지를 엑셀에서 가져오는 코드이다.

const GetExcelImageData = async (
    file: Express.Multer.File // 필자의 경우 multer 를 사용해서 이렇게 했다.
): Promise<{ row: number; col: number; name: string; ext: string; buffer: Excel.Buffer }[]> => {
    try {
        if (file.mimetype.indexOf("xml") != -1) { // 엑셀파일이라면
            const uploadPath = path.resolve(__dirname, "../../", edmsUploadFolder) + "/";
            const workbook = new Excel.Workbook();
            const ret = await workbook.xlsx.readFile(path.resolve(__dirname, "../../", file.path)); // 엑셀데이터 불러오기
            let imageDatas = [];
            for (var sheet of ret.worksheets) { // 시트별로
                let images = sheet.getImages(); // 시트내에 모든 이미지데이터를 가져온다.
                for (var image of images) {
                    const img = workbook.model.media.find((m: any) => m.index === image.imageId); // 여기서 img 는 Excel.Image 형식이다.
                    const imgFileName = `${img.name}${new Date().getTime()}.${img.extension}`;
                    imageDatas.push({
                        row: image.range.tl.nativeRow, // 이미지가 위치해있는 행 번호
                        col: image.range.tl.nativeCol, // 이미지가 위치해 있는 열 번호
                        name: imgFileName,
                        ext: `${img.extension}`,
                        buffer: img.buffer,
                    });
                    fs.writeFileSync(uploadPath + imgFileName, Buffer.from(img.buffer)); // 버퍼파일 형태로 되어있는 이미지를 서버에 저장해주고 이후에 이걸 불러다가 쓸 예정
                }
            }
            return imageDatas;
        }
    } catch (err) {
        logger.error(err);
    }
    return null;
};

위 코드를 넣어서 돌려보면 imageDatas 는

imageDatas = [
	{
    	row : 5,
        col : 4,
        name : "image123556",
        ext : ".png",
        buffer : buffer < 52 .. >
    }
    ...
]

이렇게 나온다. 행과 열을 매칭해서 이미지를 파악하면 된다.
4-2. Excel Export with Image

엑셀 생성시 이미지 넣는 기능

Export to Excel 시에 workbook을 정의해주는데,
해당 workbook 객체를 사용해서 이미지를 넣어주면된다.
원리는 이미지 경로에서 이미지를 로드 한 후, workbook 에 이미지를 import 하고,
어느곳에 위치할건지 위치를 정해주는 방식이다.
아래코드는 이미지 파일을 특정 셀에 넣는 코드이다.

const file_path_to_sheet_image = (
    workbook: ExcelJS.Workbook, // workbook
    sheet: ExcelJS.Worksheet, // sheet
    file_path: string, // image file path
    col: number, // 열 번호
    row: number // 행번호
) => {
    try {
        let repo_path = file_path;
        let size = imageSizeOf(repo_path); // imagesizeOf 라이브러리로 이미지의 정보를 가져오는용도로 사용.
        let ext: any = size.type;
        let imgId = workbook.addImage({ filename: repo_path, extension: ext }); ///우선 workbook 에 이미지를 올리기
        let height = size.height;
        let width = size.width;
        while (height > 300 || width > 300) {
            height -= height / 2;
            width -= width / 2;
        }
        sheet.addImage(imgId, { tl: { col: col - 1, row: row - 0.5 }, ext: { height: height, width: width } }); // tl 안에 col, row 로 위치를 정해줌
        sheet.getRow(row).height = height * 0.5; // 이미지의 높이에 따른 행의 높이 조절
    } catch (err) {
        console.log(err);
    }
};

ExcelJS, xlsx 두개 라이브러리를 사용해서 엑셀을 핸들링하는것은 상당히 귀찮은 일이지만, 라이브러리가 있다는것에 감사한 일이다.
끝.

profile
"어찌 할 수 없는 일에 대해 고민하는 시간은 낭비일 뿐이다."

0개의 댓글