엑셀에 차트를 포함해서 Export 하기 (NodeJS 소스 포함)

오래전에 NodeJS 환경에서 엑셀에 차트 포함시키는 방법에 대해서 포스팅을 올린적이 있었다.

오랜만에 블로그에 들어와보니 기대이상의 조회수가 나와서(정말 기록용있었는데..) 나와 같은 고민을 하는 사람들이 있나 싶어

이번에는 조금 구체적인 내용으로 정리를 해보려고 한다.

 

1. 개요

- NodeJS에서 엑셀을 만드는 건 가능!

- xlsx-chart 모듈로 차트를 만드는 것도 가능!

- 그러나 Raw 데이터랑 같은 파일에 차트를 만드는건 불가능,,,

  (보고서 만드는 용이면 당연히 차트랑 테이블이 같이 있어야되는거 아니예요? 왜 차트만 또르륵 나오는지 노이해)

 

2. 본론

내가 한 방법은 한 마디로 정리하면,

"구글 API로 구글 드라이브에 엑셀 파일 하나 만들고, Raw데이터만 있는 시트를 만들고, 그 시트에 데이터 범위를 가지고 엑셀에 차트를 만든 다음, 구글 API에 Export API 사용해서 추출"

 

굉장히 어려울 것 같지만 구글 API가 편리해서 오래 걸리지 않고 개발했다.

대신 모든 엑셀 작업이 그렇듯 조금 귀찮고, 조금 없어보인다.

 

1) Google API Token 만들기

가장 첫번째로, Google API Credentials 페이지에서 OAuth 2.0 Client Id을 만들어야한다.

 

Client Id는 데스크톱 ID로 만들어도 잘된다. 만든 후 credentials.json 파일을 프로젝트 폴더에 넣고 아래 소스 코드와 같이 작성된 js 파일을 실행하면 아래 이미지처럼 url 접속해서 인증하라고 한다.

인증하고 다시 실행하면 token.json 파일 만들고, 서버 종료 후 다시 실행하면 구글 API을 사용할 수 있는 상태가 된다.

인증 소스 코드는 구글 문서에도 있어서 참고하고 작성했다.

fs.readFile('credentials.json', (err, content) => {
	if (err) return console.log('[Error] Error loading client secret file:', err)
	authorize(JSON.parse(content), initialization)
})

function authorize(credentials, callback) {
  const { client_secret, client_id, redirect_uris } = credentials.installed
  const oAuth2Client = new google.auth.OAuth2 (client_id, client_secret, redirect_uris[0])
  fs.readFile(TOKEN_PATH, (err, token) => {
    if (err) return getNewToken(oAuth2Client, callback)
    oAuth2Client.setCredentials(JSON.parse(token))
    callback(oAuth2Client)
  })
}

function getNewToken(oAuth2Client, callback) {
  const authUrl = oAuth2Client.generateAuthUrl({
    access_type: 'offline',
    scope: SCOPES,
  })
  console.log('Authorize this app by visiting this url:', authUrl)
  const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout,
  })
  rl.question('Enter the code from that page here: ', (code) => {
    rl.close()
    oAuth2Client.getToken(code, (err, token) => {
      if (err) return console.error('Error retrieving access token', err)
      oAuth2Client.setCredentials(token)
      fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
        if (err) return console.error(err)
        console.log('Token stored to', TOKEN_PATH)
      })
      callback(oAuth2Client)
    })
  })
}

function initialization(auth) {
  GoogleDrivers = google.drive({ version: 'v3', auth })
  GoogleSheets = google.sheets({ version: 'v4', auth })
}

 

https://console.cloud.google.com/apis/credentials

Google Cloud Platform

 

https://cloud.google.com/compute/docs/tutorials/nodejs-guide?hl=ko 

Node.js 클라이언트 라이브러리 사용  |  Compute Engine 문서  |  Google Cloud

 

2) Google Drive에 타겟 폴더 만들기

Credential을 받은 계정으로 구글 드라이브에 폴더를 만들어준다.

그리고 폴더를 클릭하면 상단에 폴더 Key가 있다. 복사해서 상수로 적어두거나, env 파일에 적어둔다.

파일이 이 폴더에 쌓이도록 하기 위함이다. 지정안하면 Root 폴더에 생길거다.

https://drive.google.com/drive/u/1/folders/폴더Key

 

3) excel4node 모듈로 엑셀 파일 만들기

아래와 같이 excel4node 모듈로 workbook을 셋팅해준다. renderChart에서는 키포인트가 컬럼의 Scope을 데이터 정제하면서 미리 구해놓는게 좋다. 나중에 차트 config 작성할때 필요하다.

 

tmi) 그냥 Raw 데이터는 loop로 처리하면 되는데, 디테일 내용은 하나하나 컬럼을 그려줬다ㅠㅠ

const workbook = new XLSX.Workbook()
const worksheets = []
const chartScope = new Map()

const convert = async () => {
 SHEETS_OPTION.forEach(async sheetOpt => {
  const worksheet = workbook.addWorksheet(sheetOpt.name)
  const style = SHEET_STYLE[sheetOpt.theme]

  if (sheetOpt.theme === 'data') {
   await renderRaw(workbook, worksheet, data[sheetOpt.key], style)
  } else if (sheetOpt.theme === 'detail') {
   await renderSummary(workbook, worksheet, data[sheetOpt.key], style)
  } else {
   await renderChart(workbook, worksheet, sheetOpt.key, primary.data, chartScope)
  }

  console.log('4. Convent Sheet Data [' + sheetOpt.name + ']')
  worksheets.push(worksheet)
 })
}

await convert()

async function renderRaw (wb, ws, raw, style) {
  try {
    serialize(raw.data).then(result => {
      const { serialized, scope } = result
      const haederStyle = wb.createStyle(style.header)
      const bodyStyle = wb.createStyle(style.body)
      const startRow = 2
      const startColumn = 2
      
      ws.cell(1, 1).string('날짜').style(haederStyle)
      
      let colIndex = startColumn
      for (let start = scope.start; start <= scope.end; start++) {
        ws.cell(1, colIndex).string((start >= 10 ? start : '0' + start) + ':00').style(haederStyle)
        colIndex++
      }
  
      if (serialized && serialized.length > 0) {
        serialized.map((row, rowIndex) => {
          ws.cell(rowIndex + startRow, 1).string(row.timestamp).style(bodyStyle)
          row.values.map((value, valIndex) => {
            ws.cell(rowIndex + startRow, valIndex + startColumn).number(value ? value : 0).style(bodyStyle)
          })
        })
      }
    })
  } catch (e) {
    console.log(e)
    console.log('[ Error ] ' + e.message)
  }
}

async function renderSummary (wb, ws, data, style) {
  try {
    const defaultHeight = 40
    const chartHeight = 400
    const titleStyle = wb.createStyle(style.title)
    const headerStyle = wb.createStyle(style.header)
    const subHeaderStyle = wb.createStyle(style.subHeader)
    const importHeaderStyle = wb.createStyle(style.importHeader)
    const bodyStyle = wb.createStyle(style.body)
    const percentBodyStyle = wb.createStyle(Object.assign(style.body, SHEET_STYLE['common'].percent.integer))
    const thuosandBodyStyle = wb.createStyle(Object.assign(style.body, SHEET_STYLE['common'].thuosand.default))
    const importBodyStyle = wb.createStyle(style.importBody)
    const thousandImportBodyStyle = wb.createStyle(Object.assign(style.importBody, SHEET_STYLE['common'].thuosand.default))
    const thousandImportMidBodyStyle = wb.createStyle(Object.assign(style.importMidBody, SHEET_STYLE['common'].thuosand.default))
    const pointStyle = wb.createStyle(style.point)
    const dateStyle = wb.createStyle(style.date)
    const tableHeaderStyle = wb.createStyle(style.table.header)
    const tableBodyStyle = wb.createStyle(style.table.body)

    ws.row(1).setHeight(defaultHeight)
    ws.row(4).setHeight(defaultHeight)
    ws.row(5).setHeight(defaultHeight)
    ws.row(6).setHeight(defaultHeight)
    ws.row(9).setHeight(defaultHeight)
    ws.row(11).setHeight(defaultHeight)
    
    ws.column(COL.D).setWidth(15)
    ws.column(COL.E).setWidth(15)
    ws.column(COL.L).setWidth(20)
    ws.column(COL.M).setWidth(20)
    ws.column(COL.N).setWidth(20)

    ....(생략)

    const seasonalAvgUseageRowStart = 25
    const seasonalAvgUseageColumnStart = COL.A
    const seasonalAvgUseage = data.data.seasonal.avg

    ws.cell(seasonalAvgUseageRowStart, seasonalAvgUseageColumnStart).string('평균 사용량').style(tableHeaderStyle)
    ws.cell(seasonalAvgUseageRowStart + 1, seasonalAvgUseageColumnStart).string('12~2월').style(tableBodyStyle)
    ws.cell(seasonalAvgUseageRowStart + 2, seasonalAvgUseageColumnStart).string('3~5월').style(tableBodyStyle)
    ws.cell(seasonalAvgUseageRowStart + 3, seasonalAvgUseageColumnStart).string('6~8월').style(tableBodyStyle)
    ws.cell(seasonalAvgUseageRowStart + 4, seasonalAvgUseageColumnStart).string('9~11월').style(tableBodyStyle)

    _.range(1, 25).map((hour, hourIndex) => {
      ws.cell(seasonalAvgUseageRowStart, seasonalAvgUseageColumnStart + hour).string((hour < 10 ? '0' + hour : '' + hour) + '시').style(tableHeaderStyle)
    })

    // winter
    _.range(1, 25).map((hour, hourIndex) => {
      ws.cell(seasonalAvgUseageRowStart + 1, seasonalAvgUseageColumnStart + hour).string(_.floor(seasonalAvgUseage.winter[hourIndex].value, 1) + '').style(tableBodyStyle)
    })

    // spring
    _.range(1, 25).map((hour, hourIndex) => {
      ws.cell(seasonalAvgUseageRowStart + 2, seasonalAvgUseageColumnStart + hour).string(_.floor(seasonalAvgUseage.spring[hourIndex].value, 1) + '').style(tableBodyStyle)
    })

    // summer
    _.range(1, 25).map((hour, hourIndex) => {
      ws.cell(seasonalAvgUseageRowStart + 3, seasonalAvgUseageColumnStart + hour).string(_.floor(seasonalAvgUseage.summer[hourIndex].value, 1) + '').style(tableBodyStyle)
    })

    // autumn
    _.range(1, 25).map((hour, hourIndex) => {
      ws.cell(seasonalAvgUseageRowStart + 4, seasonalAvgUseageColumnStart + hour).string(_.floor(seasonalAvgUseage.autumn[hourIndex].value, 1) + '').style(tableBodyStyle)
    })
  } catch (e) {
    console.log(e)
    console.log('[ Error ] ' + e.message)
  }
}

async function renderChart (wb, ws, key, data, info) {
  try {
    const dataMap = fromJS(data)
    const keys = key.split('.')
    const array = dataMap.getIn(keys).toJS()

    serialize(array).then(result => {
      const { serialized, scope } = result
      const startRow = 2
      const startColumn = 2
      
      ws.cell(1, 1).string('날짜')
      
      let colIndex = startColumn
      for (let start = scope.start; start <= scope.end; start++) {
        ws.cell(1, colIndex).number(start)
        colIndex++
      }
  
      if (serialized && serialized.length > 0) {
        serialized.map((row, rowIndex) => {
          ws.cell(rowIndex + startRow, 1).string(row.timestamp)
          row.values.map((value, valIndex) => {
            ws.cell(rowIndex + startRow, valIndex + startColumn).number(value ? value : 0)
          })
        })
      }

      info.set(key, {
        column: scope,
        row: {
          start: startRow,
          end: serialized.length
        }
      })
    })
  } catch (e) {
    console.log('[ Error ] ' + e.message)
  }
}

엑셀 파일안에 내용을 채웠으면 지정된 폴더에 저장해준다.

여러 사용자가 접속할때는 대비해 serial(random string) 폴더를 미리 만들어 뒀다.

workbook.write(uploadDir + serial + extension, async (error, status) => {
  if (error) {
   res.json({ status: 'error', message: '분석 결과를 엑셀로 변환하지 못했습니다.' })
   console.log('[Error] 분석 결과를 엑셀로 변환하지 못했습니다.')
  } else {
  	..chart 작업
  }
 })

 

4) Google Drive에 엑셀 파일 올리기

이제 만든 엑셀 파일을 구글 드라이브 API로 업로드한다.

여기서는 mimeType이 제일 중요한것 같다. spreadsheet 타입으로 보내야 엑셀 파일이 아니라 sheet api에서 수정할 수 있는 파일로 저장된다. 아래처럼 작성하면 자동으로 변환되어 저장된다.

async function createSheetFile(uploadDir, serial, extension) {
  try {
    const result = await GoogleDrivers.files.create({
      resource: {
        name: serial,
        mimeType: 'application/vnd.google-apps.spreadsheet',
        parents: [ROOT_FOLDER]
      },
      media: {
        mimeType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        body: fs.createReadStream(uploadDir + serial + extension)
      }
    })
  
    if (result.status !== CODE.SUCCESS) {
      console.log('[Error] Cannot Created Excel File  [ ' + serial + ' ]')
      return null
    } else {
      console.log('[Success] Created Excel File [ ' + result.data.id + ' ]')
      const excelDetail = await GoogleSheets.spreadsheets.get({
        spreadsheetId: result.data.id
      })
      if (excelDetail.status === CODE.SUCCESS) {
        return {
          id: result.data.id,
          sheets: excelDetail.data.sheets
        }
      } else {
        return null
      }
    }
  } catch (e) {
    console.log('[Error] ' + e.message)
    return null
  }
}

 

5) 차트 Config을 만들어서 엑셀 파일에 업데이트 시키기

이 부분에서 조금 시행착오가 있었다. 차트는 엑셀 데이터를 참조하고 있는데, 엑셀에서 나와야되는 데이터랑 보이는 데이터가 달라 차트를 위한 시트를 만들고, 가려주는 작업을 추가로 했다. 

그리고 차트를 추가할때 넣어줬던 부가 정보는 표준에 맞지 않기 때문에 프로퍼티를 업데이트하기 전 다시 없애서 update batch을 실행했다.

async function createSheetsChart(sheetData, chartScope) {
  const fileId = sheetData.id
  const sheetArray = sheetData.sheets
  const targetArray = []
  const toArray = []
  const chartArray = []

  try {
    await Promise.all(
      SHEETS_OPTION.map((option, optionIndex) => {
        if (option.theme === 'chart') {
          const toIndex = SHEETS_OPTION.findIndex(otp => otp.key === option.to)
          toArray.push(sheetArray[toIndex].properties)
          targetArray.push(_.assign(sheetArray[optionIndex].properties, {
            chartKey: option.key,
            chartTitle: option.name,
            chartRow: option.r,
            chartColumn: option.c,
            chartX: option.x,
            chartY: option.y
          }))
        }
      })
    )

    await Promise.all(
      targetArray.map(async (target, index) => {
        const to = toArray[index]
        const scope = chartScope.get(target.chartKey)
        const series = await getChartSeries(target.sheetId, scope)

        chartArray.push({
          "addChart": {
            "chart": {
              "spec": {
                "title": target.chartTitle,
                "basicChart": {
                  "chartType": "LINE",
                  "legendPosition": "NO_LEGEND",
                  "axis": [
                    {
                      "position": "BOTTOM_AXIS"
                    },
                    {
                      "position": "LEFT_AXIS"
                    }
                  ],
                  "domains": [{
                    "domain": {
                      "sourceRange": {
                        "sources": [{
                          "sheetId": target.sheetId,
                          "startRowIndex": 0,
                          "endRowIndex": 1,
                          "startColumnIndex": 0,
                          "endColumnIndex": 25
                        }]
                      }
                    }
                  }],
                  "series": series,
                  "headerCount": 1
                },
                "titleTextFormat": {
                  "fontFamily": "Roboto"
                },
                "fontName": "Roboto"
              },
              "position": {
                "overlayPosition": {
                  "anchorCell": {
                    "sheetId": to.sheetId,
                    "rowIndex": target.chartRow - 1,
                    "columnIndex": target.chartColumn - 1
                  },
                  "offsetXPixels": target.chartX,
                  "offsetYPixels": target.chartY,
                  "widthPixels": 500,
                  "heightPixels": 400
                }
              },
              "border":{
                "color":{
                   
                },
                "colorStyle":{
                   
                }
              }
            }
          }
        })
      })
    )

    const result = await GoogleSheets.spreadsheets.batchUpdate({
      spreadsheetId: fileId,
      resource: {
        requests: chartArray
      }
    })

    if (result.status !== CODE.SUCCESS) {
      console.log('[Error] Cannot Update Google Sheets [ ' + fileId + ' ]')
      return false
    } else {
      console.log('[Success] Updated Google Sheets [ ' + fileId + ' ]')
      return true
    }
  } catch (e) {
    console.log('[Error] ' + e.message)
    return false
  }
}

async function updateSheetProperties(sheetData) {
  const fileId = sheetData.id
  const sheetArray = sheetData.sheets
  const propertyArray = []
  const requestArray = []

  try {
    await Promise.all(
      SHEETS_OPTION.map((option, optionIndex) => {
        if (option.theme === 'chart') {
          const filterd = fromJS(sheetArray[optionIndex].properties)
            .delete('chartKey')
            .delete('chartTitle')
            .delete('chartRow')
            .delete('chartColumn')
            .delete('chartX')
            .delete('chartY')
            .toJS()
          propertyArray.push(_.assign(filterd, {
            "hidden": true
          }))
        }
      })
    )

    await Promise.all(
      propertyArray.map(async property => {
        requestArray.push({
          "updateSheetProperties": {
            "properties": property,
            "fields": "hidden"
          }
        })
      })
    )

    const result = await GoogleSheets.spreadsheets.batchUpdate({
      spreadsheetId: fileId,
      resource: {
        requests: requestArray
      }
    })

    if (result.status !== CODE.SUCCESS) {
      console.log('[Error] Cannot Update Google Sheets [ ' + fileId + ' ]')
      return false
    } else {
      console.log('[Success] Updated Google Sheets [ ' + fileId + ' ]')
      return true
    }
  } catch (e) {
    console.log('[Error] ' + e.message)
    return false
  }
}

 

6) 엑셀 파일을 서버 폴더에 Export -> 서버 폴더에 파일 download

드디어 마지막 단계! 엑셀 파일 업데이트까지 완료되면 구글 드라이브에서 Export하고(이때는 일반 엑셀 파일 mimeType)

서버 폴더에 저장시킨다.

그리고 서버 폴더에 생성된 파일을 response로 보내주면 끝~~

async function exportSheetFile(fileId) {
  try {
    const result = await GoogleDrivers.files.export({
      fileId,
      mimeType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    }, {
      responseType: 'stream'
    })

    if (result.status !== CODE.SUCCESS) {
      console.log('[Error] Cannot Export Excel [ ' + fileId + ' ]')
      return null
    } else {
      console.log('[Success] Exported Excel [ ' + fileId + ' ]')
      return result.data
    }
  } catch (e) {
    console.log('[Error] ' + e.message)
    return null
  }
}

const isUploaded = await createSheetsChart(sheetData, chartScope)
const isUpdated = await updateSheetProperties(sheetData)

if (isUploaded && isUpdated) {
  console.log('')
  console.log('07. Export Excel')
  
  const dest = fs.createWriteStream(uploadDir + fileName + extension)
  const sheetStream = await exportSheetFile(sheetData.id)

  if (sheetStream) {
   sheetStream.on('error', err => {
    res.json({ status: 'error', message: '분석 결과를 엑셀로 다운로드하지 못했습니다.' })
   }).on('end', () => {
  
   console.log('')
   console.log('08. Done')
   setTimeout(() => {
    res.download(uploadDir + fileName + extension)
   }, 100)
  }).pipe(dest)
 } else {
  res.json({ status: 'error', message: '분석 결과를 엑셀로 다운로드하지 못했습니다.' })
 }
} else {
 res.json({ status: 'error', message: '분석 결과를 엑셀로 업로드하지 못했습니다.' })
}

 

3. 팁 TIP

기억나는 팁 하나가 있다면 구글 API로 보내야되는 값을 작성하기 어려워서 템플릿 파일을 만들어 구글 드라이브로 읽어 셋팅된 값을 수정해서 작업했다. 이렇게 했더니 Config을 다 찾아보지 않아도 되서 편하고 오류도 발생하지 않았다.

 

다 작성하고 보니 이렇게 길어 질 줄 몰랐다.

좋은 방법이라고 자신있게 얘기할 수는 없지만 방법을 못찾는 someone에게 도움이 되었으면 좋겠다.

 

https://github.com/yelling95/node-excel

GitHub - yelling95/node-excel: Excel Chart with Google API (use NodeJS)