NodeJS에서 xlsx파일 merge 프로그램 백업

nodejs-xlsx-merge-test.js

//
// NodeJS에서 xlsx파일 merge 프로그램 백업
//   express.js랑 묶여있어서 참고용
//
//
const UPLOAD_TMPDIR = `${process.cwd()}/uploads_tmp/`;
const UPLOAD_DIR    = `${process.cwd()}/uploads/`;

const TypeAPPSTATUS = {
  WAIT: 0,
  PROCESSING: 1,
  DELIVERY: 2
}

let APP_STATUS      = TypeAPPSTATUS.WAIT;

const express = require('express');
const router  = express.Router();

const multer  = require('multer');
const upload  = multer({ dest: UPLOAD_TMPDIR });

const Excel = require('exceljs');
const moment = require('moment');
const util = require('util');
const fs = require('fs');
const { promisify } = require('util');

const fs_unlinkAsync = promisify(fs.unlink);
const fs_renameAsync = promisify(fs.rename);
const fs_readdirAsync = promisify(fs.readdir);
const fs_readfileAsync = promisify(fs.readFile);


router.get('/', (req, res) => {
  res.redirect('/xlsxmerge');
});

router.get('/xlsxmerge', (req, res) => {
  res.render('xlsx_upload');
});

const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    cb(null, UPLOAD_TMPDIR)
  },
  filename: function (req, file, cb) {
    cb(null, Date.now() + '.xlsx') //Appending .jpg
  }
})

router.put('/xlsxmerge/xlsxupload', upload.array('files[]', 30), async(req, res) => {
  
  const json = {
    status: 2,
    message: '정의되지 않은 오류'
  }
  
  try {
    const spreadSheetsMIMEs = [
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    'application/vnd.ms-excel.sheet.binary.macroEnabled.12',
    'application/vnd.ms-excel',
    'application/vnd.ms-excel.sheet.macroEnabled.12',
    'text/csv'
    ]
  
    const files = req.files;
  
    let reject = false;
  
    if (files.length) {
      
      for (let i = 0; i < files.length; i++) {
        const file = files[i];
        let thisReject = false;
        if (spreadSheetsMIMEs.indexOf(file.mimetype) === -1) {
          reject = true;
          thisReject = true;
          json.message = '스프레드시트 파일을 올려주세요.';
        }
        
        if (!thisReject) {
          await fs_renameAsync(file.path, `${UPLOAD_DIR}${file.originalname || Date.now()}`);
        } else {
          await fs_unlinkAsync(file.path);
        }
        
      }
      
    } else {
      reject = true;
      json.message = '파일을 올리세요';
    }
    
    if (!reject) {
      json.status = 1;  
    }
  } catch (e) {
    json.message = e.message;
    console.error(e);
  } finally {
    res.json(json);
  }
  
});

router.get('/xlsxmerge/getfilelist', async (req, res) => {
  
  const json = {
    status: 2,
    message: '정의되지 않은 오류'
  }
  
  try {
    const readdir = await fs_readdirAsync(UPLOAD_DIR);
    json.status = 1;
    json.message = 'OK';
    json.files = readdir;
  } catch (e) {
    json.status = 2;
    json.message = e.message;
    console.error(e);
  } finally {
    res.json(json);
  }
  
});

router.use(express.urlencoded({ extended: true }));

router.post('/xlsxmerge/merge', multer().none(), async (req, res) => {
  
  const json = {
    status: 2,
    message: '정의되지 않은 오류'
  }
  
  try {
  
    let offset = parseInt(req.body.userfile_offset) || 1;
    if (offset < 1) {
      offset = 1;
    }
    
    const readdir = await fs_readdirAsync(UPLOAD_DIR);
    
    if (readdir.length) {
      const workbookMap = {};
      const workbookFillMap = {};
      let addedRows = 0;
        
      for (let filename in readdir) {
        const path = `${UPLOAD_DIR}${readdir[filename]}`;
    
        const options = {
          sharedStrings: 'cache',
          hyperlinks: 'cache',
          worksheets: 'emit',
          styles: 'cache',
          entries: 'ignore',
        };
        const workbookReader = new Excel.stream.xlsx.WorkbookReader(path, options);
        const workbook = new Excel.stream.xlsx.WorkbookReader(path, options);
        
        for await (const worksheetReader of workbookReader) {
          
          // 하드코딩
          if (worksheetReader.id !== 1) continue;
          
          if (!workbookMap[worksheetReader.id]) {
            workbookMap[worksheetReader.id] = [];
          }
          const sheet = workbookMap[worksheetReader.id];
          
          if (!workbookFillMap[worksheetReader.id]) {
            workbookFillMap[worksheetReader.id] = [];
          }
          
          const fillSheet = workbookFillMap[worksheetReader.id];
          
          for await (const row of worksheetReader) {
            
            if (row.number < offset) continue;
            
            const cols = [];
            const fillCols = [];
      
            row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
              
              
              if (cell.fill) {
                fillCols.push(cell.fill);
              } else {
                fillCols.push(null);
              }
              
              if (cell.value) {
                if (typeof cell.value === 'string' || typeof cell.value === 'number') {
                //console.log(cell.address + ' : ' + cell.value);
                  cols.push(cell.value.toString());
                  
                } else if (cell.value && typeof cell.value === 'object') {
                  let value = '';
                  
                  if (!cell.value.richText) {
                    value = moment(cell.value).format('YYYY-MM-DD');
                  } else {
                    cell.value.richText.forEach(v => { value += v.text });
                  }
                  
                  cols.push(value);
                  
                } else {
                  cols.push(null);
                }
                
              } else {
                /*if (cell.address == 'A2')
                console.log(cell)*/
                cols.push(null);
              }
                
              
            });
            
            let isBlankRow = true;
            cols.forEach(i => { 
              if (!i && i != null && i != '') {
                isBlankRow = false;
              }
            })
            
           /*
            let notblankrow = 0;
            cols.forEach(i => { 
              if ((i != '' || i != null)) {
                notblankrow++;
              }
            })

            if (notblankrow >= 3) isBlankRow = false;
            */
            
            if (!isBlankRow) {
              // A~D열중 하나라도비어 있으면 무시함. offset추가
              sheet.push(cols);
              fillSheet.push(fillCols);
            }
            
          }
          
        }
      }
      
      const getFormulaMapID = () => {
        const alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
        const forArray = alphabet.split('');
        
        const gMap = {};
        let inc = 1;
        forArray.forEach(x => { gMap[x] = inc; inc++; });
        
        forArray.forEach(x => {
          forArray.forEach(y => {
            gMap[x + '' + y] = inc;
            inc++;
          })
        })
        
        return gMap;
        
      }
      
      const getA1ColumnById = (colID) => {
        
        const gMap = getFormulaMapID();
        
        for (let formula in gMap) {
          if (colID === gMap[formula]) {
            return formula;
          }
        }
        
        throw new Error('범위 에러!');
        
      }
      const getIdByA1Style = (a1var) => {
        
        const a1varParse = /^([A-Za-z]+)([0-9]+)$/.exec(a1var);
        const vColumns      = a1varParse[1].toUpperCase();
        const vRows   =  parseInt(a1varParse[2]);
        
        const gMap = getFormulaMapID();
        
        let ret = 1;
        if (gMap[vColumns]) {
          ret = gMap[vColumns];
        } else {
          throw new Error('범위초과!');
        }
        
        return {
          colId: ret,
          rowId: vRows
        }
        
      }
      
      let sheet = workbookMap[1];
      let fillSheet = workbookFillMap[1];
      
      const newWB = new Excel.Workbook();
      newWB.creator = 'Node.js';
      const newWS = newWB.addWorksheet('MERGED', {properties:{tabColor:{argb:'FF00FF00'}}});
      
      // 텍스트 입력
      for (let rows in sheet) {
        
        const rowValue = [];
        rowValue.push();
        
        for (let cols in sheet[rows]) {
          rowValue.push(sheet[rows][cols]);
        }
        
        newWS.addRow(rowValue);
        addedRows++;
        
      }
      
      // 배경색 입력
      let rowID = 1;
      for (let rows in fillSheet) {
        
        let colID = 1;
        
        for (let cols in fillSheet[rows]) {
          
          const colFormula = getA1ColumnById(colID);
          
          newWS.getCell(`${colFormula}${rowID}`).fill = fillSheet[rows][cols];
          
          colID++;
        }
        
        rowID++;
        
      }

      if (addedRows) {
        await newWB.xlsx.writeFile(`${process.cwd()}/public/merged.xlsx`);
        
        json.status = 1;
        json.downloadURL = '/merged.xlsx?' + Date.now();
      } else {
        json.message = '데이터가 비어있습니다.';
      }
      
    } else {
      json.message = '파일이 없습니다.';
    }
    
  } catch (e) {
    json.status = 2;
    json.message = e.message;
    console.error(e);
  }

  res.json(json);
  
});

router.post('/xlsxmerge/clearfiles', async(req, res) => {
  
  const readdir = await fs_readdirAsync(UPLOAD_DIR);
  
  for await (let filename of readdir) {
    await fs_unlinkAsync(`${UPLOAD_DIR}${filename}`);
  }
  
  res.json({ status: 1 });
  
});

module.exports = router;