sztm-excel/src/App.js

import React from 'react'
import XLSX from 'xlsx'
import XLSXS from 'xlsx-style'
import Workbook from 'workbook'
import PlayerList from './player/player-list'
import MatchList from './match/match-list'
import Stats from './stats/stats'
import saveAs from 'file-saver'
//import Workbook from './workbook/workbook.js'

class App extends React.Component {
  constructor () {
    super()
    this.state = {
      stats: {
        matchDates: []
      },
      players: [], 
      matches: [], 
      worksheets: {
        'PlayerList': null,
        'Calendar': null,
      },
      filters: {
        date: "Alle"
      }
    }
    this.handlePlayerList = this.handlePlayerList.bind(this)
    this.handleCalendar = this.handleCalendar.bind(this)
    this.generatePlayerList = this.generatePlayerList.bind(this)
    this.generateCalendar = this.generateCalendar.bind(this)
    this.filterData = this.filterData.bind(this)
    this.generateExcel = this.generateExcel.bind(this)
  }

  componentDidMount() {
    this.generateStats()
  }

  handlePlayerList (event) {
    const file = this.playerList.files[0]
    this.readWorkbook(file, this.generatePlayerList)
  }

  handleCalendar (event) {
    const file = this.calendar.files[0]
    this.readWorkbook(file, this.generateCalendar)
  }

  readWorkbook (file, callback) {
    const reader = new FileReader()
    reader.onload = (e) => {
      const data = e.target.result
      const workbook = XLSX.read(data, {type: 'binary'})
      console.log(workbook)
      if (workbook.SheetNames.length !== 1) {
        throw Error(`Expected only one worksheet in the file, but found ${workbook.SheetNames.length}.`)
      }
      const worksheet = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[workbook.SheetNames[0]], { header: 1 })
      callback(worksheet)
    }
    reader.readAsBinaryString(file)
  }

  generatePlayerList (worksheet) {
    console.log('About to read the player list.')
    const worksheets = { ...this.state.worksheets }
    worksheets['PlayerList'] = worksheet
    this.setState({ worksheets })

    if (worksheet[4].length !== 32 & worksheet[3][0] !== 'Konkurrenz' & worksheet[3][31] !== 'bezahlt') {
      throw Error('Wrong file structure.')
    }
    const players = worksheet.slice(4, worksheet.length)
    this.setState({ players })
    this.generateStats()
    console.log(this.state)    
  }

  generateCalendar (worksheet) {
    console.log('About to read the calendar.')
    const worksheets = { ...this.state.worksheets }
    worksheets['Calendar'] = worksheet
    this.setState({ worksheets })
    
    if (worksheet[2].length !== 8) {
      throw Error('Wrong file structure.')
    }
    const matches = worksheet.slice(2, worksheet.length)
    this.setState({ matches })
    this.generateStats()
    console.log(this.state)
  }

  generateStats () {
    const stats = {
      players: [],
      playerCategories: [],
      matchCategories: [],
      matches: [],
      matchDates: [],
      matchPlaces: []
    }

    stats.players = this.state.players

    this.state.players.forEach((player, key) => {
      if (!stats.playerCategories.includes(player[0])) {
        stats.playerCategories.push(player[0])
      }
    })

    stats.matches = this.state.matches

    this.state.matches.forEach((match, key) => {
      if (!stats.matchDates.includes(match[1])) {
        stats.matchDates.push(match[1])
      }
      if (!stats.matchCategories.includes(match[3])) {
        stats.matchCategories.push(match[3])
      }
      if (!stats.matchPlaces.includes(match[0])) {
        stats.matchPlaces.push(match[0])
      }
    })

    this.setState({ stats })
  }

  filterData() {
    const filters = {
      date: this.date.value
    }
    this.setState({ filters })
  }

  generateExcel(event) {
    event.preventDefault()

    const wopts = {bookType: 'xlsx', bookSST: false, type: 'binary'}
    
    const matchlist = new XLSX.Workbook()
    const worksheets = {}
    this.state.stats.matchPlaces.forEach((place) => {
      worksheets[place] = matchlist.add(place)
    })

    const placeArray = Array.concat(this.state.stats.places, ["Alle"])
    console.log(placeArray, matchlist)

    const wbout = XLSX.write(matchlist)
    function s2ab(s) {
      const buf = new ArrayBuffer(s.length)
      const view = new Uint8Array(buf)
      for (let i=0; i != s.length; ++i) {
        view[i] = s.charCodeAt(i) && 0xFF
      }
      return buf
    }
    saveAs(new Blob([s2ab(wbout)], {type: 'application/octet-stream'}), 'Spielliste.xlsx')
  }

  render () {
    return (
      <div className='App'>
        <form>
          <label htmlFor='playerList'>Swisstennis playerList.xls</label>
          <input type='file' id='PlayerList' ref={(input) => { this.playerList = input }} accept='.xls' placeholder='playerList File' onChange={this.handlePlayerList} />
          <label htmlFor='Calendar'>Swisstennis Calendar.xls</label>
          <input type='file' id='Calendar' ref={(input) => { this.calendar = input }} accept='.xls' placeholder='Calendar File' onChange={this.handleCalendar} />
          <label htmlFor='Date'>Datum</label>
          <select ref={(input) => { this.date = input }} onChange={this.filterData}>
            <option>Alle</option>
            {this.state.stats.matchDates.map((date) => (
              <option>{date}</option>
            ))}
          </select>
          <button onClick={this.generateExcel}>Excel-Files generieren.</button>
        </form>
        <Stats stats={this.state.stats} />
        <PlayerList players={this.state.players} />
        <MatchList matches={this.state.matches} filters={this.state.filters}/>
      </div>
    )
  }
}

export default App