본문 바로가기
개발자, 코딩

[js]Sheet.js를 사용한 클라이언트에서 excel download 예제!

by The Career 2022. 1. 13.
반응형

Sheet.js는 엑셀을 읽고, 다운로드를 할 수 있는 Javascript 라이브러리입니다.

 

 

SheetJS

SheetJS Tools for Excel Spreadsheets

sheetjs.com

 

업무 페이지를 개발하다보면 엑셀의 활용도가 참 높습니다. 테이블로 이루어진 통계를 엑셀로 다운로드하거나, 엑셀로 관리하던 데이터를 DB에 넣는 등 업무 페이지와 엑셀은 뗄 수 없는 관계입니다. 

 

오늘은 SheetJS를 활용해서 엑셀을 다운로드 할 수 있는 예제입니다.

 

❓ 엑셀 다운로드를 쉽게 만들려면 ?

일반적으로 웹 환경에서 엑셀 다운로드는 몇 가지 방법이 있습니다.

  • 미리 만들어진 엑셀 파일을 다운로드 하는 방법
  • html 테이블 태그를 사용하여 엑셀로 다운로드 하기

제가 주로 사용하는 방식은 위 2가지입니다. 1번은 DB의 데이터를 사용해서 미리 엑셀을 만들어놓고 다운로드 링크만 걸어주는 방식입니다. 두 번째 방법은, 컨트롤러 단에서 DB에서 가져온 리스트를 html 테이블 태그 형태로 묶어 엑셀로 다운로드 할 수 있는 방법입니다.

 

2번 방법의 경우, 그때마다 달라지는 DB의 내용을 그대로 담을 수 있지만 엑셀 최신버전과 안맞는(?) 부분이 생기는지 이 방법으로 생성한 엑셀은 실행할때마다 오류화면이 발생합니다. 그리고 view가 변경되면 html table을 생성하는 부분의 코드도 변경해주어야 하기 때문에 수정시 번거롭습니다.

 

따라서 이번에는 table 태그를 그대로 읽어와서 엑셀을 만들 수 있는 방법을 찾아보았습니다.

 

1️⃣  테이블 만들기

먼저 테이블을 만들어줍니다. 예제 테이블은 php 반복문을 통해 생성했습니다.

지난번 살펴본 dataTable.js도 함께 사용했습니다. 그리고 table 태그로 엑셀을 생성하기 위한 라이브러리를 선언해줍니다.

 

<!-- 파일서버 load -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.8/FileSaver.min.js"></script>
<!-- 엑셀 load -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.15.5/xlsx.full.min.js"></script>

 

아래는 테이블을 그리기 위한 전체 코드입니다.

 

<?php

    /* 테스트 데이터 생성 */
    $items = [];
    $items[0] = array("DATE" => "2021년 1월", "ITEM_NAME" => "냉장고", "SALES_COUNT" => 67, "SALES_PRICE" => 67000000);
    $items[1] = array("DATE" => "2021년 1월", "ITEM_NAME" => "TV", "SALES_COUNT" => 37, "SALES_PRICE" => 14000000);
    $items[2] = array("DATE" => "2021년 1월", "ITEM_NAME" => "노트북", "SALES_COUNT" => 67, "SALES_PRICE" => 9500000);
    $items[3] = array("DATE" => "2021년 1월", "ITEM_NAME" => "에어프라이어", "SALES_COUNT" => 32, "SALES_PRICE" => 9000000);
    $items[4] = array("DATE" => "2021년 1월", "ITEM_NAME" => "선풍기", "SALES_COUNT" => 300, "SALES_PRICE" => 5000000);
    $items[5] = array("DATE" => "2021년 1월", "ITEM_NAME" => "에어컨", "SALES_COUNT" => 42, "SALES_PRICE" => 42000000);
    $items[6] = array("DATE" => "2021년 1월", "ITEM_NAME" => "건조기", "SALES_COUNT" => 96, "SALES_PRICE" => 32000000);

    $items[7] = array("DATE" => "2021년 2월", "ITEM_NAME" => "냉장고", "SALES_COUNT" => 23, "SALES_PRICE" => 67000000);
    $items[8] = array("DATE" => "2021년 2월", "ITEM_NAME" => "TV", "SALES_COUNT" => 12, "SALES_PRICE" => 14000000);
    $items[9] = array("DATE" => "2021년 2월", "ITEM_NAME" => "노트북", "SALES_COUNT" => 24, "SALES_PRICE" => 9500000);
    $items[10] = array("DATE" => "2021년 2월", "ITEM_NAME" => "에어프라이어", "SALES_COUNT" => 12, "SALES_PRICE" => 9000000);
    $items[11] = array("DATE" => "2021년 2월", "ITEM_NAME" => "선풍기", "SALES_COUNT" => 43, "SALES_PRICE" => 5000000);
    $items[12] = array("DATE" => "2021년 2월", "ITEM_NAME" => "에어컨", "SALES_COUNT" => 23, "SALES_PRICE" => 42000000);
    $items[13] = array("DATE" => "2021년 2월", "ITEM_NAME" => "건조기", "SALES_COUNT" => 14, "SALES_PRICE" => 32000000);

    $items[14] = array("DATE" => "2021년 3월", "ITEM_NAME" => "냉장고", "SALES_COUNT" => 23, "SALES_PRICE" => 67000000);
    $items[15] = array("DATE" => "2021년 3월", "ITEM_NAME" => "TV", "SALES_COUNT" => 43, "SALES_PRICE" => 14000000);
    $items[16] = array("DATE" => "2021년 3월", "ITEM_NAME" => "노트북", "SALES_COUNT" => 12, "SALES_PRICE" => 9500000);
    $items[17] = array("DATE" => "2021년 3월", "ITEM_NAME" => "에어프라이어", "SALES_COUNT" => 34, "SALES_PRICE" => 9000000);
    $items[18] = array("DATE" => "2021년 3월", "ITEM_NAME" => "선풍기", "SALES_COUNT" => 23, "SALES_PRICE" => 5000000);
    $items[19] = array("DATE" => "2021년 3월", "ITEM_NAME" => "에어컨", "SALES_COUNT" => 14, "SALES_PRICE" => 42000000);
    $items[20] = array("DATE" => "2021년 3월", "ITEM_NAME" => "건조기", "SALES_COUNT" => 23, "SALES_PRICE" => 32000000);


?>
<!-- JQuery 필수 -->
<script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<link href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css" rel="stylesheet">
<script type="text/javascript" src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap-theme.min.css">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js"></script>
<!-- 파일서버 load -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.8/FileSaver.min.js"></script>
<!-- 엑셀 load -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.15.5/xlsx.full.min.js"></script>
<style>
    .green-btn {
        box-shadow:inset 0px 1px 0px 0px #9acc85;
        background:linear-gradient(to bottom, #74ad5a 5%, #68a54b 100%);
        background-color:#74ad5a;
        border:1px solid #3b6e22;
        display:inline-block;
        cursor:pointer;
        color:#ffffff;
        font-family:Arial;
        font-size:13px;
        font-weight:bold;
        padding:6px 12px;
        text-decoration:none;
    }
    .green-btn:hover {
        background:linear-gradient(to bottom, #68a54b 5%, #74ad5a 100%);
        background-color:#68a54b;
    }
    .green-btn:active {
        position:relative;
        top:1px;
    }

    .btn-group {
        width:100%;
        text-align: right;
        padding: 20px;
    }

</style>
<div style="width:90%;margin:0 auto;">
    <h1>월별 가전제품 판매현황</h1>
    <div class="btn-group">
        <a href="#" class="green-btn" id="excel-down-filtered" >엑셀 다운로드(필터적용)</a>
        <a href="#" class="green-btn" id="excel-down-all" >엑셀 다운로드(전체)</a>
    </div>
    <table id="dataTable" style="width:100%" class="table table-striped">
        <thead class="thead-dark">
            <tr>
                <th style="text-align:center;">판매월</th>
                <th style="text-align:center;">상품명</th>
                <th style="text-align:center;">판매대수</th>
                <th style="text-align:center;">총 판매금액</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach($items as $item){ ?>
                <tr>
                    <td style="text-align:center;"><?=$item['DATE']?></td>
                    <td style="text-align:center;"><?=$item['ITEM_NAME']?></td>
                    <td style="text-align:right;"><?=$item['SALES_COUNT']?></td>
                    <td style="text-align:right;"><?=$item['SALES_PRICE'] * $item['SALES_COUNT']?></td>
                </tr>
            <?php } ?>
        </tbody>
    </table>
</div>

 

완성된 화면입니다. dataTable.js를 사용했기 때문에 페이징이나 검색, 정렬 기능을 기본적으로 사용할 수 있습니다. 부가적으로 판매월 / 품목 부분을 보면 필터가 걸려있습니다. 이것도 dataTable.js에서 지원하는 기능으로, dataTable을 생성할 때 초기값으로 설정할 수 있습니다.

 

2️⃣  dataTable.js 설정

필터 기능을 사용하기 위해 dataTable 을 사용해서 테이블을 꾸며보겠습니다.

 

/* 필터 대상 */
    var dt_columns =  [
        { title: "판매월", width:100},
        { title: "품목", width:100},
    ] 


/* 테이블 load */
        dataTable = $("#dataTable").DataTable({
            lengthMenu: [[10, 25, 50, -1], [10, 25, 50, "전체"]],
            columnDefs: [
                { targets: 3 , render: $.fn.dataTable.render.number( ',' ) }
            ],
            "initComplete": function () {

                /* 판매월, 가전종류 filter setting */
                this.api().columns().every( function (e) {
                    if(e==0 || e==1){
                        var column = this;
                        col_name = dt_columns[Number(this[0].toString())].title;
                        
                        var select = $('<select><option value="">'+col_name+'</option></select>')
                        .appendTo( $(column.header()).empty() )
                        .on( 'change', function () {
                            var val = $.fn.dataTable.util.escapeRegex(
                                $(this).val()
                            );
                            column.search( val ? '^'+val+'$' : '', true, false ).draw();
                        } );

                        column.data().unique().sort().each( function ( d, j ) {
                            select.append( '<option value="'+d+'">'+d+'</option>' )
                        } );
                    }
                });
            }
        });
    } );

필터 대상을 보면, 필터에 사용할 이름과 select box의 크기를 설정할 수 있습니다.

dataTable 을 초기화하면서 initComplete 함수에 초기화 후 실행될 내용을 작성해주면 됩니다. 각 테이블의 칼럼을 읽어와서 유니크값으로 option을 만든 뒤, 선택시 select 함수가 실행되도록 이벤트리스너를 연결해주는 작업이 필요합니다.

 

3️⃣  Excel 다운로드 구현하기

이제 엑셀 다운로드를 구현하면 됩니다. 저는 excel 기능을 네임스페이스를 사용해서 객체로 관리할 수 있도록 만들었습니다.

 

/* 엑셀 객체로 관리 */
    var excel = {
        file_name: "",
        /* 파일명 설정 */
        setFileName: function(name){
            this.file_name = name;
        },
        /* 파일명 가져오기 */
        _getFileName: function(){
            return this.file_name;
        },
        /* 다운로드 함수 */
        download: function(target, sheet_name){
            var work_book = XLSX.utils.book_new(); 
        
            var work_sheet = XLSX.utils.table_to_sheet(target);
            XLSX.utils.book_append_sheet(work_book, work_sheet, sheet_name);
            var work_book_down = XLSX.write(work_book, {bookType:'xlsx',  type: 'binary'});
            
            /* 다운로드시 파일명이 겹치지 않기 위해 시간을 붙혀준다. */

            saveAs(new Blob([this._save(work_book_down)],{type:"application/octet-stream"}), this._getFileName() + "_" + this._getCurrent() + ".xlsx");
        },
        /* 현재 시간 */
        _getCurrent: function(){
            let today = new Date(); 
            let year = today.getFullYear();
            let month = today.getMonth() + 1;
            let date = today.getDate();
            let day = today.getDay();
            let hours = today.getHours();
            let minutes = today.getMinutes();
            let seconds = today.getSeconds();
            let milliseconds = today.getMilliseconds();

            return String(year) + String(month) + String(date) + String(hours) + String(minutes) + String(seconds) + String(milliseconds);
        },
        /* 파일 저장 */
        _save: function(s){
            var buf = new ArrayBuffer(s.length);
            var view = new Uint8Array(buf); 
            for (var i=0; i<s.length; i++) view[i] = s.charCodeAt(i) & 0xFF;
            return buf;    
        }
    }

    /* 필터 대상 */
    var dt_columns =  [
        { title: "판매월", width:100},
        { title: "품목", width:100},
    ] 

    $(document).ready(function() {


        /* 필터적용 엑셀 Download */
        $("#excel-down-filtered").click(function(){
            event.preventDefault();

            dataTable.page.len(-1).draw(); // 모든 페이지를 출력하기 위해 data table을 전체로 변경

            excel.setFileName("엑셀(필터적용)");
            excel.download($("#dataTable")[0], "Sheet1");

        });

        /* 전체 엑셀 Download */
        $("#excel-down-all").click(function(){
            event.preventDefault();

            /* 전체 다운로드를 위해 data table 필터 제거 */
            dataTable.column(0).search("").draw();
            dataTable.column(1).search("").draw();

            dataTable.page.len(-1).draw(); // 모든 페이지를 출력하기 위해 data table을 전체로 변경

            excel.setFileName("엑셀(전체)");
            excel.download($("#dataTable")[0], "Sheet1");
        });

필터를 적용한 버튼과 전체 다운로드 2가지 형태로 적용했습니다. dataTable을 사용하기 때문에 table을 가져왔을 때 필터가 걸려있기 때문에 전체 다운로드 시 필터를 제거하는 코드를 추가해주었습니다.

 

또한 엑셀 다운로드시 파일명을 정적으로 사용하면 파일명이 중복될 우려가 있어서 현재 날짜와 시간을 붙혀주는 함수를 만들어 중복되지 않도록 했습니다. 

 

다운로드시 정상적으로 다운로드 되는 것을 확인할 수 있습니다.

 

💻  Live-Demo

 

js 예제

 

nick-web.kr

⌨️. Source code 

 

GitHub - SEUNGJOOMOON/examples: Project of examples

Project of examples. Contribute to SEUNGJOOMOON/examples development by creating an account on GitHub.

github.com

❗️ 결론

Sheet.js를 사용하여 서버통신없이 엑셀을 만드는 방법을 알아봤습니다. client에서 엑셀을 생성하면 일단 서버와 통신하지 않기 때문에 서버 리소스를 줄일 수 있는 장점이 있습니다. 하지만 간단한 엑셀은 괜찮지만, 데이터가 많아질 경우 생성하는 시간이 오래걸리거나, 사용자의 컴퓨터 성능에 따라 메모리 부족 등의 오류가 날 수 있는 것이 단점일 것 같습니다.

 

따라서 자주 사용하면서, 간단한 엑셀을 생성할 때 사용하면 좋을 것 같습니다.

 

✅  참고자료

 

 

 

[개발자 멘토링]개발자 취업/이직/방통대/대학원/이력서/포트폴리오 등 멘토링 진행합니다.

개발자 취업이나 이직과 관련하여 멘토링을 진행합니다. 멘토링은 인프런 멘토링을 통해 신청하실 수 있으며, 진행 방식 및 주제는 아래 내용을 참고해주세요! 멘토링 소개 ✅ 커리어 소개 현)

nick901106.tistory.com

 

반응형

'개발자, 코딩' 카테고리의 다른 글

[RestFul API]SOAP과 REST의 차이  (0) 2022.06.06
[mysql]우분투 mysql 설치하기 + 삽질  (0) 2022.01.30
[php]Codeigniter 4 Layout 사용하기 - 메뉴바, 푸터 공통 사용  (0) 2022.01.07
[Spring]스프링 프로젝트 Heroku 에 deploy하기.  (0) 2021.12.18
Apache Log4j 원격코드 실행 취약점 발견.  (0) 2021.12.13

관련글

티스토리툴바