JavaScript Excel 시트에 실시간 데이터를 통합하는 방법

추천0

페이지 정보

작성자 GrapeCity

작성일 2019-03-06 00:00 조회 4,021회 댓글 0건

본문

응용 프로그램 설정

응용 프로그램의 폴더를 만들어 시작합니다. 이 경우 이름을 "Real Time Data"로 지정했습니다.

다음으로, 해당 폴더에 프로젝트의 매니페스트 파일로 사용될 package.json 파일을 생성하려고 합니다. 여기에는 다음과 비슷한 내용이 포함될 수 있습니다.

{
    "name": "real-time-data",
    "version": "0.0.1",
    "description": "An app that imports real-time data into Spread JS",
    "dependencies": {}
}

이 응용 프로그램에서는 Express를 웹 프레임 워크로 사용하고 실시간 데이터를 위해 Socket.IO를 사용합니다. 이는 npm으로 간단히 설치할 수 있습니다. Visual Studio Code 터미널에서 다음과 입력합니다.

npm install --save express@4.15.2 socket.io

설치가 완료되면 애플리케이션 설정에 사용될 "index.js"라는 파일을 만듭니다. 여기에는 다음이 포함되어야합니다.

var app = require('express')();
var http = require('http').Server(app);
var io = require('socket.io')(http);

//Add code here

http.listen(3000, function(){
    console.log('Stock Ticker Started, connect to localhost:3000');
});

이제 애플리케이션에 HTML 파일을 추가합니다. 이 경우 파일 이름을 "index.html"로 지정합니다. 계속해서 몇 가지 기본 초기화 코드뿐만 아니라 SpreadJS에 대한 스크립트 및 CSS 참조를 포함하여 HTML 파일에 아래와 같이 코드를 추가합니다.

<!doctype html>
<html>
  <head>
    <title>Real Time Data</title>
  </head>

  <script src=“/socket.io/socket.io.js“></script>
  <script src=“https://code.jquery.com/jquery-3.3.1.min.js“></script>
  <link href=“http://cdn.grapecity.com/spreadjs/hosted/css/gc.spread.sheets.excel2013white.12.0.7.css“ rel=“stylesheet“ type=“text/css“ />
  <script type=“text/javascript“ src=“http://cdn.grapecity.com/spreadjs/hosted/scripts/gc.spread.sheets.all.12.0.7.min.js“></script>
  <script src=“http://cdn.grapecity.com/spreadjs/hosted/scripts/plugins/gc.spread.sheets.charts.12.0.7.min.js“ type=“text/javascript“></script>

  <script>
    window.onload = function() {
      // Initialize spread variables
      var spread = new GC.Spread.Sheets.Workbook(document.getElementById(“spreadSheet“), { sheetCount: 1 });
      spread.fromJSON(stockTemplate);
      var activeSheet = spread.getActiveSheet();
      var dataSheet = spread.getSheet(1);
      activeSheet.clearSelection();
    }
  </script>
  <body>
    <div id=“spreadSheet“ style=“width: 650px; height: 600px; border: 1px solid gray“></div>
  </body>
</html>

이전 코드 스니펫에서는 spread.fromJSON()을 사용하여 템플릿 파일을로드했습니다. 이 프로젝트를 위해 주식 시세 표시기의 기준으로 사용할 템플릿 파일을 만들었습니다. SpreadJS Designer 만 사용하여 데이터 소스에 대한 데이터 레이블 및 바인딩을 작성하고 셀을 형식화하고 눈금선 및 헤더를 제거하고 차트 영역을 추가했습니다. 이 튜토리얼에서 "stockTemplate.js"라는 파일을 제공합니다.(첨부파일).

Designer에서 JS로 내보내려면 "파일> 내보내기"를 클릭하고 "JavaScript 파일 내보내기"를 선택하십시오. 이 포스팅에서는 해당 템플릿 파일을 index.js 및 index.html 파일과 같은 폴더에 배치했습니다.

index.js 파일로 돌아가서 다음 코드를 사용하여 프로그램에 HTML 파일과 템플릿을 제공하도록 지시해야합니다.

app.get('/', function(req, res){
    res.sendFile(__dirname + '/index.html');
});
// Required to load template file
app.get('/stockTemplate.js', function(req, res){
    res.sendFile(__dirname + '/stockTemplate.js');
});

index.html 파일로 돌아가서 템플릿 파일을 로드하는 스크립트를 추가합니다.

<script type=“text/javascript“ src=“stockTemplate.js“></script>

설정을 마치기 위해, 나중에 작성할 변수들을 초기화하고, 드롭 다운 셀을 만들어 스톡을 선택할 수 있도록 만들었습니다.

// Initialize variables
var stockSymbolLookup = [{text:'Apple Inc.', value:'AAPL'}, {text:'Microsoft Inc.', value:'MSFT'}, {text:'Google', value:'GOOGL'}];
var dataSource = [],
    openPrice = 0,
    closePrice = 0,
    stockCounter = 0,
    chart = null,
    chartAxisRange = 3,
    lineDataMaxSize = 20,
    lineData = new Array(lineDataMaxSize),
    socket,
    stock;
// Create a drop down for selecting a stock
var stockDropDown = new GC.Spread.Sheets.CellTypes.ComboBox().items(stockSymbolLookup);
activeSheet.getCell(2,1).cellType(stockDropDown);

또한, 공개 가격 변경에 대해 특정 조건부 서식을 설정할 수 있습니다. 양수이면 녹색이고 음수이면 빨간색입니다.

// Set conditional formatting 
function setConditionalFormatting() {
  var ranges = [new GC.Spread.Sheets.Range(8,1,1,1)];
  var lowerStyle = new GC.Spread.Sheets.Style();
  lowerStyle.foreColor = "red";
  activeSheet.conditionalFormats.addCellValueRule(
    GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.lessThan,
    -0.001,
    -0.001,
    lowerStyle, 
    ranges
  );
  var upperStyle = new GC.Spread.Sheets.Style();
  upperStyle.foreColor = "green";
  activeSheet.conditionalFormats.addCellValueRule(
    GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.greaterThanOrEqualsTo,
    0.001,
    0.001,
    upperStyle,
    ranges
  );
}

데이터베이스에 연결

실제로 데이터 소스에 연결하기위한 코드를 작성하기 전에, 사용자가 스프레드의 드롭 다운 목록에서 주식을 선택할 때 처리 할 코드를 추가해야합니다. 그래야만 원하는 데이터를 연결하고 원하는 데이터를 얻을 수 있습니다.

이를 EditEnded 이벤트에 바인딩를 함으로써 구현할 수 있습니다. 조회 배열에서 주식 기호를 찾은 다음 해당 주식에 연결하면됩니다.

// Bind an event for changing the stock in the drop down menu
// Set the stock variable to the newly selected stock from the list
activeSheet.bind(GC.Spread.Sheets.Events.EditEnded, function(e, info) {
  if(info.row === 2 && info.col === 1) {
    stock = stockSymbolLookup.find(stockLookup => stockLookup.text === activeSheet.getValue(2,1));
    connectToDatabase();
  }
});

"connectToDatabase"라는 새로운 함수를 호출합니다.

// Handle connecting to the database to get new stock information when the selected stock is changed
function connectToDatabase() {
  // Receive JSON from the IEX Trading server
  // Reset the data source and remove the chart
  socket = io.connect('https://ws-api.iextrading.com/1.0/tops');
  socket.on('connect', function(data) {
    dataSource.length = 0;
    if (activeSheet.charts.get('line') != null)
      activeSheet.charts.remove('line');
    socket.emit('unsubscribe', 'AAPL,MSFT,GOOGL');
    socket.emit('subscribe', stock.value);
  });
}

이 코드는 socket.io를 사용하여 데이터 소스에 연결하고 주식 기호를 전달하여 구독(subscribe)합니다. 또한 "Line"이라는 차트를 제거하는 방법에 대한 섹션이 있습니다. 이 차트는 나중에 차트를 추가한 후, 초기화하는 데 사용됩니다. 이 기능은 주식 선택이 변경 될 때마다 호출되기 때문입니다.

프로그램이 데이터 소스에 연결되어 있고, 특정 주식 값을 지속적으로 가져오는 경우, 프로그램은 해당 데이터 소스로부터 JSON 데이터 형식으로 업데이트를 수신하며, 스프레드에서 파싱해야합니다. 이를 위해 socket.on 함수를 사용할 수 있습니다.


// Parse the JSON and set the data in Spread
socket.on('message', function(message) {
  spread.suspendPaint();
  var obj = JSON.parse(message);
  if (obj.symbol === stock.value) {
    // Set the open price to the last price when the program starts,
    // and the close price to an arbitrary number for testing.
    if (dataSource.length == 0) {
      dataSource = obj;
      openPrice = obj.lastSalePrice;
      closePrice = openPrice - 10;
      // Fill in starting data for the line chart
      lineData.fill({Value:openPrice});
      activeSheet.setValue(2, 1, stock.text)
      activeSheet.setValue(2, 7, openPrice);
      activeSheet.setValue(3, 7, closePrice);
      activeSheet.setFormula(8, 1, "B6-H4");
      addChart();
      setConditionalFormatting();
    } else {
      dataSource = obj;
    }
    addLineData();
    bindData();
  }
  spread.resumePaint();
});

위 코드에서 데이터 소스를 통해, 시트에 샘플 데이터를 채웁니다. 또한 bindData, addLineData, addChart 및 setConditionalFormatting과 같이 추후에 정의할 함수를 호출합니다.

스프레드에서 데이터 사용

각 기능을 수행하기 전에 프로그램의 주요 구조를 설명하도록 하겠습니다. 기본적으로 데이터는 셀 수준 데이터 바인딩을 통해 스프레드 인스턴스 "Stock_Ticker"의 첫 번째 시트에 직접 바인딩됩니다. 이에 대한 설명은 여기를 참조하십시오 .

두 번째 시트는 프로그램이 시작된 이래로 주가의 시가입니다. 일반적으로 특정 날짜 이후에 기록된 값을 추적하는 것이 가장 좋지만이 프로그램을 단순화하기 위해 프로그램 시작 시간을 기준으로합니다. 이 경우에는 가장 최근 20 개의 값만 사용합니다. 이 값의 백로그는 꺾은 선형 차트가 가리키는 것인데, 본질적으로 프로그램이 시작된 이후 값의 변화를 보여줍니다.

템플릿이 디자이너에 정의되어 있고, 형식(format)이 데이터 소스의 형식과 일치하면, "bindData"함수에서 호출되는 setDataSource 함수를 사용하여 시트에서 간단하게 설정할 수 있습니다. 또한 두 번째 시트인 "Data_Sheet"에 대한 데이터 소스를 설정할 수 있습니다. 해당 시트의 열이 데이터를 기반으로 자동으로 생성되도록 합니다.

// Bind the data source for both of the sheets
function bindData() {
  activeSheet.setDataSource(new GC.Spread.Sheets.Bindings.CellBindingSource(dataSource));
  dataSheet.autoGenerateColumns = true;
  dataSheet.setDataSource(lineData);
}

꺾은 선형 차트에 데이터 추가

다음에 정의 할 다음 함수는 "addLineData"함수입니다. 이 함수는 이 앞부분에서 정의한 배열을 사용하고 값이 이전과 다른 경우, 데이터 소스에서 새 값을 수신 할 때마다 값을 추가합니다.

// Add data with each update for the line chart
function addLineData() {
  if (lineData.length >= lineDataMaxSize)
    lineData.shift();
  stockCounter++;
  // Only add the data to the list for the line chart if the data has changed
  if (dataSource.lastSalePrice != lineData[lineData.length-1].Value) {
    lineData.push({ Value:dataSource.lastSalePrice });
  }
}

꺾은 선형 차트 추가

꺾은 선형 차트의 경우 lineData 데이터 소스에 바인딩된 "Data_Sheet"시트에서 셀 범위를 지정하여 만들 수 있습니다. "addChart" 함수 내에서 제목, 축, 데이터 레이블, 범례 및 차트 영역의 서식을 변경할 수도 있습니다.

// Add the line chart
function addChart() {
  // Define the area to load the chart into
  var startCellRect = activeSheet.getCellRect(11, 1);
  var endCellRect = activeSheet.getCellRect(24, 9);
  var chartStart = {
    x: startCellRect.x,
    y: startCellRect.y
  };
  var chartArea = {
    width: endCellRect.x-startCellRect.x,
    height: endCellRect.y-chartStart.y
  }
  chart = activeSheet.charts.add('line', 
    GC.Spread.Sheets.Charts.ChartType.lineMarkers, 
    chartStart.x, 
    chartStart.y,
    chartArea.width, 
    chartArea.height,
    'Data_Sheet!$A$1:$A$' + lineDataMaxSize
  );

  chart.allowMove(false);

  // Set the title of the chart
  chart.title({
    text: activeSheet.getValue(2,1),
    color: "white"
  });

  // Change the values on the y-axis to show changes easier
  // Hide the x-axis values, we only care about changes, not specific time values
  chart.axes({
    primaryValue: {
      min: openPrice - chartAxisRange,
      max: openPrice + chartAxisRange
    },
    primaryCategory: {
      visible: false
    }
  });

  // Add data labels to the chart
  chart.dataLabels({
    color: "white",
    format: "$#.##",
    position: GC.Spread.Sheets.Charts.DataLabelPosition.above,
    showValue: true
  });

  // Hide the legend; there is only one series used in this chart
  chart.legend({
    visible: false
  });

  // Change the color of the chart
  chart.chartArea({
    backColor: "black",
    color: "white"
  })
}