Javascript class version of https://github.com/egeriis/zipcelx

Spreadsheet.js

'use strict';

import FileSaver from 'file-saver';
import JSZip     from 'jszip';

class Spreadsheet {

  /**
   * Holds all replacement strings to escape
   *
   * @returns {Object<string>}
   */
  static get escapeReplacements () {
    return {
      '&':  '&amp',
      '<':  '&lt',
      '>':  '&gt',
      '"':  '&quot',
      '\'': '&#39'
    };
  }

  /**
   * Regex to test for unescaped HTML
   *
   * @returns {RegExp}
   */
  static get escapeRegex () {
    return /[&<>"']/g;
  }

  /**
   * Holds all valid cell types
   *
   * @returns {{cell_type_string: StringConstructor, cell_type_number: NumberConstructor}}
   */
  static get validTypes () {
    return {
      cell_type_string: String,
      cell_type_number: Number
    };
  }

  /**
   * Holds all error messages
   *
   * @returns {{missing_key_filename: string, invalid_type_filename: string, invalid_type_sheet: string, invalid_type_sheet_data: string}}
   */
  static get errors () {
    return {
      invalid_type_filename:   'zipclex missing or bad filename',
      invalid_type_sheet:      'zipcelx sheet data is not of type array',
      invalid_type_sheet_data: 'zipclex sheet data children are not of type array'
    };
  }

  /**
   * Holds all static XLSX template strings
   *
   * @returns {{workbookXML: String, workbookXMLRels: String, rels: String, contentTypes: String, sheet: String}}
   */
  static get templates () {
    return {
      workbookXML:     `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mx="http://schemas.microsoft.com/office/mac/excel/2008/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:mv="urn:schemas-microsoft-com:mac:vml" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main"><workbookPr/><sheets><sheet state="visible" name="Sheet1" sheetId="1" r:id="rId3"/></sheets><definedNames/><calcPr/></workbook>`,
      workbookXMLRels: `<?xml version="1.0" ?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId3" Target="worksheets/sheet1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"/>
</Relationships>`,
      rels:            `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/></Relationships>`,
      contentTypes:    `<?xml version="1.0" ?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default ContentType="application/xml" Extension="xml"/>
<Default ContentType="application/vnd.openxmlformats-package.relationships+xml" Extension="rels"/>
<Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" PartName="/xl/worksheets/sheet1.xml"/>
<Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml" PartName="/xl/workbook.xml"/>
</Types>`,
      sheet:           `<?xml version="1.0" ?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:mv="urn:schemas-microsoft-com:mac:vml" xmlns:mx="http://schemas.microsoft.com/office/mac/excel/2008/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main"><sheetData>{placeholder}</sheetData></worksheet>`
    };
  }

  /**
   * Creates a new spreadsheet object and analyzes the data
   *
   * @param {Array} data
   */
  constructor ( data ) {
    this.raw = data;

    this.analyze();
  }

  /**
   * Analyzes the sheet data
   */
  analyze () {
    if ( !Array.isArray( this.raw ) ) {
      throw new Error( this.constructor.errors.invalid_type_sheet );
    }

    if ( !this.constructor._validateChildren( this.raw ) ) {
      throw new Error( this.constructor.errors.invalid_type_sheet_data );
    }
  }

  /**
   * Builds the sheet markup
   */
  build () {
    const XMLRows = this.constructor._generateRows( this.raw );
    this.sheet    = this.constructor.templates.sheet.replace( '{placeholder}', XMLRows );
  }

  /**
   * Zips the sheet and downloads it
   *
   * @param   {String}     filename
   * @returns {Promise<*>}
   */
  async download ( filename ) {
    if ( !filename || typeof filename !== 'string' ) {
      throw new Error( this.constructor.errors.invalid_type_filename );
    }

    if ( !this.sheet ) {
      this.build();
    }

    const zip = new JSZip();
    const xl  = zip.folder( 'xl' );

    xl.file( 'workbook.xml', this.constructor.templates.workbookXML );
    xl.file( '_rels/workbook.xml.rels', this.constructor.templates.workbookXMLRels );
    zip.file( '_rels/.rels', this.constructor.templates.rels );
    zip.file( '[Content_Types].xml', this.constructor.templates.contentTypes );
    xl.file( 'worksheets/sheet1.xml', this.sheet );

    return FileSaver.saveAs(
      await zip.generateAsync( { type: 'blob' } ),
      `${filename}.xlsx`
    );
  }

  static _validateChildren ( parent ) {
    return parent.every( child => Array.isArray( child ) );
  }

  static _generateColumnLetter ( colIndex ) {
    if ( typeof colIndex !== 'number' ) {
      return '';
    }

    const prefix = Math.floor( colIndex / 26 );
    const letter = String.fromCharCode( 97 + ( colIndex % 26 ) ).toUpperCase();

    if ( prefix === 0 ) {
      return letter;
    }

    return this._generateColumnLetter( prefix - 1 ) + letter;
  }

  static _generateCellNumber ( index, rowNumber ) {
    return `${this._generateColumnLetter( index )}${rowNumber}`;
  }

  static _generateStringCell ( index, value, rowIndex ) {
    return `<c r="${this._generateCellNumber(
      index,
      rowIndex
    )}" t="inlineStr"><is><t>${this._escape( value )}</t></is></c>`;
  }

  static _generateNumberCell ( index, value, rowIndex ) {
    return `<c r="${this._generateCellNumber( index, rowIndex )}"><v>${value}</v></c>`;
  }

  static _formatCell ( cell, index, rowIndex ) {
    if ( Object.values( this.validTypes ).indexOf( cell.type ) === -1 ) {
      cell.type = this.validTypes.cell_type_string;
    }

    return (
      cell.type === this.validTypes.cell_type_string
      ? this._generateStringCell( index, cell.value, rowIndex )
      : this._generateNumberCell( index, cell.value, rowIndex )
    );
  }

  static _formatRow ( row, index ) {

    // To ensure the row number starts as in excel.
    const rowIndex = index + 1;
    const rowCells = row
      .map( ( cell, cellIndex ) => this._formatCell( cell, cellIndex, rowIndex ) )
      .join( '' );

    return `<row r="${rowIndex}">${rowCells}</row>`;
  }

  static _generateRows ( rows ) {
    return rows
      .map( ( row, index ) => this._formatRow( row, index ) )
      .join( '' );
  }

  /**
   * Escapes a string. Stolen from lodash
   *
   * @param   {string} data
   * @returns {string}
   * @private
   * @see https://github.com/lodash/lodash/blob/master/escape.js
   */
  static _escape ( data ) {
    return ( data && RegExp( this.escapeRegex.source ).test( data ) )
           ? data.replace( this.escapeRegex, ( char ) => this.escapeReplacements[ char ] )
           : data;

  }
}

export default Spreadsheet;

See updated version at https://gist.github.com/Radiergummi/29ad8a344e384031da05266833dbe2fd