[Node.js] Excel.js 사용법 총정리 / How to use Exceljs (테이블 만들기, insertRows, columns, value 등)

이번 프로젝트 중 가장 시간도 오래걸리고 어려웠던 엑셀 파일 만들어 내보내기

를 정리해서 기록으로 남기기

 

 

기존에는 xlsx를 익히고 사용했었다가 css가 유료인걸 뒤늦게 알았다.. 👼🏼 

찾아보니 많은 개발자들이 이 사실에 빡치고 exceljs로 갈아타고 있다고 한다. 

 

며칠 전 적은 xlsx 사용법 포스팅

https://blckchainetc.tistory.com/426

[Node.js] How to use xlsx with Typescript / xlsx 사용법

 

CSS가 전혀 필요없다면 xlsx를,

기본적인 텍스트 정렬, 셀 백그라운드 색 지정 등 (혹은 그 이상)이 필수라면 exceljs를 권하고 싶다. 

 

 

 

 

---

 

 

node.js v16.13.1

typescript v4.5.5

exceljs v4.3.0

 

 

 

1. exceljs 설치하기 installation

npm i exceljs

 

 

2. exceljs 가져오기 importing 

import ExcelJS from 'exceljs';

 

 

3. Create a Workbook 

 

엑셀 파일은 하나의 workbook과 그 안에 여러개의 worksheet으로 이루어져 있다. 먼저 workbook을 만들고 그 다음 worksheet이름과 함께 worksheet을 워크북에 만들어 준다. 

 

const workbook = new ExcelJS.Workbook();

 

 

4. Create a worksheet 

 

'first sheet' 이라는 이름의 워크시트를 해당 워크북에 붙인다. 

const worksheet = workbook.addWorksheet('first sheet');

* 워크시트 삭제는 workbook.removeWorksheet('sheet name' or index number); 로 삭제 가능하다.

 

 

 

5. 특정 셀에 텍스트 추가하기 

 

5-1) 내용 입력하기

worksheet.getCell('A1').value = '엑셀 특정 셀에 값 넣기';

sheet 이름은 방금 추가한 'first sheet', A1에는 '엑셀 특정 셀에 값 넣기'가 잘 들어갔다. 

 

 

5-2) CSS 추가하여 값 입력하기 

worksheet.getCell('A2').value = {
    richText: [{ text: 'css 추가합니다요', font: { size: 15, color: { argb: 'A52A2A' } } }],
  };

 

 

 

6.  Columns (Headers) 

  worksheet.columns = [
    { header: 'Id', key: 'id', width: 10 },
    { header: 'Name', key: 'name', width: 32 },
    { header: 'D.O.B.', key: 'DOB', width: 10, outlineLevel: 1 },
  ];

 

columns는 엑셀의 필드값을 지정해준다. header에 필드명, key값은 해당 Header의 키값으로 나중에 데이터를 넣을 때 이 키를 기준으로 데이터가 쏙쏙 맞춰서 들어가기 때문에 중요하다. 그리고 width, outlineLevel 등 style CSS도 함께 설정할 수 있다. 

 

 

 

7. insert Rows 

columns가 지정되었으면 이제 데이터를 넣어보기. 

 

  const data = [
    {
      id: 1,
      name: 'Jamey',
      DOB: '2022-12-25',
    },
    {
      DOB: '2100-01-10',
      name: 'Jimmy',
      id: 2,
    },
    {
      id: 3,
      name: 'Jesus',
      DOB: '2000-12-25',
    },
  ];

  worksheet.insertRow(2, { id: 0, name: 'Jenny', DOB: '2020-11-11' });
  worksheet.insertRows(3, data);

insertRow는 말 그대로 한 줄을 입력하고 insertRows는 여러 줄을 입력한다. 

insertRow(__시작하는 Row number___, an object of data );

insertRows(__시작하는 Row number___, an array of objects of data ); 

 

아까 columns를 지정할 때 key값을 넣어 주었는데 여기 InsertRow에서 이 기능이 아주 편리하게 쓰인다. data 내용을 자세히 보면 2번째 객체의 순서는 뒤죽박죽이다. 그래도 엑셀에 입력된건 Key값에 맞춰 (객체의 Property값 = columns key값) 잘 들어간 걸 볼 수 있다. 

 

 

8.  위의 테이블  꾸미기 (CSS / STYLE )

위 테이블의 컬럼별 정렬 맞추기, headers (columns) 중앙 맞추기 및 배경색 변경 해보기

먼저 위와 같이 만들어 놓고 for문을 돌려 css를 적용시킬 수도 있지만 columns 지정할 때 style도 함께 넣어줄 수 있기 때문에 columns를 다시 지정해보자!! 

 

  worksheet.columns = [
    {
      header: 'Id',
      key: 'id',
      width: 10,
      style: { font: { size: 14 }, numFmt: '@', alignment: { horizontal: 'center' } },
    },
    {
      header: 'Name',
      key: 'name',
      width: 32,
      style: { font: { size: 14 }, numFmt: '@', alignment: { horizontal: 'center' } },
    },
    {
      header: 'D.O.B.',
      key: 'DOB',
      width: 20,
      outlineLevel: 1,
      style: { font: { size: 14 }, numFmt: 'YYYY-MM-DD', alignment: { horizontal: 'center' } },
    },
    {
      header: 'salary',
      key: 'salary',
      width: 20,
      style: { font: { size: 14 }, numFmt: '$#,##0', alignment: { horizontal: 'right' } },
    },
  ];

columns지정할 때의 css에 따라 아래 데이터들도 모두 같은 스타일을 가진다. 이제 columns 중 salary의 정렬을 header부분만 중앙으로 맞추고, Headers 필드의 백그라운드 컬러도 바꿔보기 

 

  const headerStyle = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'cce6ff' },
  } as FillPattern;

  const headerBorderStyle = {
    left: { style: 'thin', color: { argb: 'bfbfbf' } },
    right: { style: 'thin', color: { argb: 'bfbfbf' } },
  } as Borders;

  for (let i = 1; i <= worksheet.columnCount; i++) {
    const headerEachCell = worksheet.getCell(`${String.fromCharCode(i + 64)}1`);
    headerEachCell.fill = headerStyle;
    headerEachCell.border = headerBorderStyle;
    headerEachCell.alignment = { horizontal: 'center' };
  }

border left / right에 회식 선을 넣은 이유는 Fill을 할 경우 border가 모두 사라져서 넣어주었다. 

 

여기서 엑셀 숫자 형식 변경 - numFmt 사용자 정의로 사용하고 싶다면 👇👇

https://blckchainetc.tistory.com/435

Excel how to custom Number format 숫자 서식 사용자 지정 커스텀하기 / -0 을 0으로 바꾸기 음수

 

 

 

7.  위에 만든 표 (columns) 위치 변경하기 

columns 위의 셀 그 위에 또 셀을 나누기(?) 로 추가 비슷무리하게 해준다. 

 

worksheet.spliceRows(1, 0, [], [], []);
worksheet.getCell('A2').value = '완성이다.';

그리고 입력하고 싶은 값 입력 하면 완성 ! 

 

* spliceColumns() 으로 columns 이동 가능

 

 

 

8. 파일 내보내기 

여기서 파일 내보내기는 지정한 경로로 파일을 추출하는 것이다. Local 다운로드 방식은 아래에 있음 

workbook.xlsx.writeFile('example.xlsx');

writeFile() <- 경로 및 저장할 파일명.xlsx을 적어주면 된다. 나는 지금 경로 그대로 파일을 볼 거라 경로없이 바로 파일명만 적어서 현재 경로에 파일이 생성되게 했다. 

 

 

 

9. addTable() 매서드로 테이블 추가하기  

위에서는 columns, data insert로 테이블 비슷무리하게 만들었다. 이제는 exceljs에 있는 addTable() 매서드로 진짜(?) 테이블 만들어보기 

 

둘 다 해본 후 개인적인 생각 

 

- columns, insertRows 위의 방식은 columns를 정의할 때 위치를 지정하는 값이 없는 듯해서 만들 때 위치를 모두 고려한 후 나중에 spliceRows() or spliceColumns() 등으로 위치를 움직여야하는게 귀찮.. 하지만 데이터 넣는게 아주 간편 [{},{},{}..] 형식으로 db에서 가져온 데이터 바로 insert 가능

 

- addTable매서드는 테이블 위치를 내가 지정할 수 있는 점이(Ref) 좋지만 data를 넣을 때 [[],[],[]..] 형식으로 넣어야 해서 db에서 가져온 데이터의 형식이 [ {},{},{}..]일 경우 한번 더 가공해줘야하는 점이 귀찮.. 하지만 이미 만들어진 테이블 style 테마를 정할 수 있어서 css의 귀찮음을 없애줌 but 그래도 css의 모든 것을 해결해주진 않는다. (정렬, 폰트 등) 

 

8-1) worksheet2 - 두 번재 worksheet를 만들어 준다. 

const worksheet2 = workbook.addWorksheet('second sheet');

 

8-2) 아까 만들어놓은 data ([ {}, {},...]를 가공한다. 

const arrayOfArraysData = data.map(Object.values);
console.log('arrayOfArraysData =', arrayOfArraysData);

그런데 위의 elements를 보면 아까 data의 순서 그대로 뒤죽박죽이다. 그럼 엑셀에도 Headers에 관계없이 위의 순서대로 들어가서 다른 방법으로 데이터를 가공해야한다. 

 

const arrayOfArraysData = data.map(({ id, name, DOB, salary }) => {
    return [id, name, DOB, salary];
  });
  console.log('array of arrays data refined=', arrayOfArraysData);



addTable Columns 순서에 맞춰서 return한 값들

 

 

 

8-3) addTable을 한다. 

  worksheet2.addTable({
    name: 'letsMakeTable',
    ref: 'C3',
    headerRow: true,
    totalsRow: true,
    style: {
      theme: 'TableStyleLight7',
      showRowStripes: true,
    },
    columns: [
      { name: 'id', filterButton: true },
      { name: 'name', filterButton: true },
      { name: 'D.O.B', filterButton: true },
      { name: 'salary', filterButton: true },
    ],
    rows: arrayOfArraysData,
  });

name: 테이블 명 

ref : 테이블 시작 위치 셀을 정한다. - 필수

headerRow: headers 사용할 건지

totalsRow: 토탈 row 사용할 건지

style: 스타일 지정 

표 테마가 아주 다양하게 있다. 아래는 TableStyleLight7의 테마이고 TableStyleDark ~, TableStyleLigth~, TableStyleMedium~ 이렇게 쭉 엄청 많다. width, 정렬과 같은 CSS는 아직 처리가 된 상태가 아니다. 

columns: headers 정의하기 - 필수

rows: data 입력할 데이터 [[],[],[],,,,[]] 형식 - 필수

 

 

8-4) 테이블에 CSS 추가하기 

이미 만들어진 테이블에 css를 추가할 때, column, row 별 css는 for문 1번, 각 column, row의 셀 하나씩 꾸밀 때는 이중 for문을 돈다. 뭔가 비효율적인데 exceljs table style 꾸미기는 이렇게밖에 못찾았다. 더 효율적인 방법이 있다면 댓글로 알려주시면 감사하겠습니닷

  worksheet2.eachRow((row, rowNo) => {
    row.height = 18;
    row.eachCell((cell, colNo) => {
      if (cell.value || cell.value === 0) {
        const eachCell = row.getCell(colNo);
        eachCell.font = { size: 14 };

        if (colNo === 6) eachCell.numFmt = '$#,##0';
      }
    });
  });

각 Row 별로 height를 추가하고 각 셀의 데이터가 있으면 font 설정하기 column "F"이면 숫자 형식을 $로 하기 

 

  for (let i = 1; i <= worksheet2.columnCount; i++) {
    const eachColumn = worksheet2.getColumn(i);

    if (i === 6) {
      eachColumn.alignment = { horizontal: 'right' };
    } else eachColumn.alignment = { horizontal: 'center' };

    if (eachColumn.values.length !== 0) {
      eachColumn.width = 20;
    }
  }

columns 수로 for문을 돌며 정렬 맞추고 해당 column에 내용이 있으면 width 설정

 

  const tableColumnLength = Object.keys(data[0]).length;

  for (let i = +tableStartRow - 1; i < tableColumnLength + +tableStartRow - 1; i++) {
    const headerEachCell = worksheet2.getCell(`${String.fromCharCode(i + 65)}${tableStartRow}`);
    headerEachCell.alignment = { horizontal: 'center' };
  }

table의 column 갯수를 구해 for문 돌며 해당 Header의 정렬 맞추기 

 

 

 

최종 테이블

 

 

 

10. 만든 엑셀 .xlsx 파일 로컬 pc에 다운로드 되도록 하기

만들어진 workbook을 writeBuffer() 로 controller로 보낸다. 

 

service

workbook = letsLearnExceljs();
return workbook.xlsx.writeBuffer();

 

controller 

 

res에 header 설정해주기, 파일명 담기 

res.end에 service에서 받은 파일 담아 보내기 & end시키기

    res.setHeader(
      'Content-Type',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    );

    res.setHeader(
      'Content-Disposition',
      `attachment; filename*=utf-8''${encodeURI('엑셀샘플')}.xlsx`,
    );

    return service
      .letsLearnExceljs()
      .then(bufferedExcelFile => {
        res.status(200).end(bufferedExcelFile);
      })
      .catch((e) => console.log(e));

 

 

 

 

전체 코드 (리팩토링 X)

const letsLearnExceljs = () => {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('first sheet');

  // worksheet.getCell('A1').value = '엑셀 특정 셀에 값 넣기';
  // worksheet.getCell('A2').value = {
  //   richText: [{ text: 'css 추가합니다요', font: { size: 15, color: { argb: 'A52A2A' } } }],
  // };

  worksheet.columns = [
    {
      header: 'Id',
      key: 'id',
      width: 10,
      style: { font: { size: 14 }, numFmt: '@', alignment: { horizontal: 'center' } },
    },
    {
      header: 'Name',
      key: 'name',
      width: 32,
      style: { font: { size: 14 }, numFmt: '@', alignment: { horizontal: 'center' } },
    },
    {
      header: 'D.O.B.',
      key: 'DOB',
      width: 20,
      outlineLevel: 1,
      style: { font: { size: 14 }, numFmt: 'YYYY-MM-DD', alignment: { horizontal: 'center' } },
    },
    {
      header: 'salary',
      key: 'salary',
      width: 20,
      style: { font: { size: 14 }, numFmt: '$#,##0', alignment: { horizontal: 'right' } },
    },
  ];

  const data = [
    {
      id: 1,
      name: 'Jamey',
      DOB: '2022-12-25',
      salary: 1000,
    },
    {
      salary: 2000,
      DOB: '2100-01-10',
      name: 'Jimmy',
      id: 2,
    },
    {
      id: 3,
      name: 'Jesus',
      DOB: '2000-12-25',
      salary: 1000000,
    },
  ];

  worksheet.insertRow(2, { id: 0, name: 'Jenny', DOB: '2020-11-11', salary: 3000 });
  worksheet.insertRows(3, data);

  const headerStyle = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'cce6ff' },
  } as FillPattern;

  const headerBorderStyle = {
    left: { style: 'thin', color: { argb: 'bfbfbf' } },
    right: { style: 'thin', color: { argb: 'bfbfbf' } },
  } as Borders;

  for (let i = 1; i <= worksheet.columnCount; i++) {
    const headerEachCell = worksheet.getCell(`${String.fromCharCode(i + 64)}1`);
    headerEachCell.fill = headerStyle;
    headerEachCell.border = headerBorderStyle;
    headerEachCell.alignment = { horizontal: 'center' };
  }
  worksheet.spliceRows(1, 0, [], [], []);
  worksheet.getCell('A2').value = '완성이다.';

  
  
  
  
  // WORKSHEET2  ADDTABLE 
  
  const worksheet2 = workbook.addWorksheet('second sheet');
  const arrayOfArraysData = data.map(({ id, name, DOB, salary }) => {
    return [id, name, DOB, salary];
  });

  const tableStartColumn = 'C';
  const tableStartRow = '3';

  worksheet2.addTable({
    name: 'letsMakeTable',
    ref: `${tableStartColumn}${tableStartRow}`,
    headerRow: true,
    totalsRow: true,
    style: {
      theme: 'TableStyleLight7',
      showRowStripes: true,
    },
    columns: [
      { name: 'id', filterButton: true },
      { name: 'name', filterButton: true },
      { name: 'D.O.B', filterButton: true },
      { name: 'salary', filterButton: true },
    ],
    rows: arrayOfArraysData,
  });

  worksheet2.eachRow((row, rowNo) => {
    row.height = 18;
    row.eachCell((cell, colNo) => {
      if (cell.value || cell.value === 0) {
        const eachCell = row.getCell(colNo);
        eachCell.font = { size: 14 };

        if (colNo === 6) eachCell.numFmt = '$#,##0';
      }
    });
  });

  for (let i = 1; i <= worksheet2.columnCount; i++) {
    const eachColumn = worksheet2.getColumn(i);

    if (i === 6) {
      eachColumn.alignment = { horizontal: 'right' };
    } else eachColumn.alignment = { horizontal: 'center' };

    if (eachColumn.values.length !== 0) {
      eachColumn.width = 20;
    }
  }

  const tableColumnLength = Object.keys(data[0]).length;

  for (let i = +tableStartRow - 1; i < tableColumnLength + +tableStartRow - 1; i++) {
    const headerEachCell = worksheet2.getCell(`${String.fromCharCode(i + 65)}${tableStartRow}`);
    headerEachCell.alignment = { horizontal: 'center' };
  }

  return workbook;
};

const run = async () => {
  const workbook = letsLearnExceljs();
  workbook.xlsx.writeFile('example.xlsx');
};

export default { run };

first sheet & secon sheet 

엑셀로 표현해야하는 데이터 수, columns 수 등에 따라 동적으로 표현하는 게 좋다. 위의 예시들은 exceljs 설명에 치중하고 하드코딩으로 하나의 함수 안에 쭉 표현했다. addTable() 매서드를 사용한 코드는 css쪽이 복잡스러워서 실무에서는 addTable()대신 첫번재로 설명한 columns + insertRows로 진행햇다 

 

 

 

 

Reference : https://www.npmjs.com/package/exceljs