@opuscapita/excel-service

excel-service

Description

Excel service contains a react component for button to open import file window and JS methods to export data to Excel and import data from Excel

Installation

It seems, that npm handles xlsx and xlsx-styles as different versions of the same component. But xlsx-style requires xlsx to be installed in the same node module tree folder with it. In order to make that happen, please follow the instructions below.

Add

externals: [
  { './cptable': 'var cptable' },
]

to webpack.config.js and install

npm install --save file-saver xlsx xlsx-styles @opuscapita/excel-service

It seems, that npm handles xlsx and xlsx-styles as different versions of the same component. But xlsx-style requires xlsx to be installed in the same node module tree folder with it. In order to make that happen, please follow the instructions below.

If xlsx-styles is installed into node_modules/@opuscapita/excel-service/node_modules do the following:

• Delete package-lock.json
• Delete node_modules
• Run npm install
• Verify that xlsx-styles doesn't exist in node_modules/@opuscapita/excel-service/node_modules and both xlsx and xlsx-styles exist in the root level of the tree. If xlsx is installed into node_modules/@opuscapita/excel-service/node_modules do the above steps for it, but replace xlsx-styles with xlsx.

Demo

View the DEMO

Builds

UMD

The default build with compiled styles in the .js file. Also minified version available in the lib/umd directory.

CommonJS/ES Module

You need to configure your module loader to use cjs or es fields of the package.json to use these module types. Also you need to configure sass loader, since all the styles are in sass format.

• With webpack use resolve.mainFields to configure the module type.
• Add SASS loader to support importing of SASS styles.

API FileInputLabel

Prop name	Type	Default	Description
acceptedFiles	string	''	String with file formats
label	[element, string]	'Select file'	Label for the button
onChange	function	() => {}	Callback on file import

API Excel

Method	Input	Description
exportToExcel	data :: List, columns :: array of objects, fileName :: string (optional), digits :: [number, array] (optional), visibleColumns :: List	Exports data with specified columns to an Excel file.
importFromExcel	files :: array, callback :: function	Imports data from an Excel file. Use alert callabck for a failed import operation.
onLoadCallback	e :: event object, columns :: array of objects, visibleColumns :: List (optional)	Callback on data import
exportSheetsToExcel	sheets :: array of Sheet, fileName :: string	Exports data with specified columns to an Excel file.

Sheet

Prop name	Type	Default	Description
columns	array of Columns or arrays	[]	Column headers. In case of one column hedaer row, array contains Column objects, and in case of several column header rows, array contains arrays of Column objects.
data	array of arrays or objects	[]	Either array of arrays, which contains objects, with value prop (to be used when no column headers). Or array of (row) objects, which contains props, which match to column valueKeyPath props values.
dataStyle	object		Style for data, for more information refer to [xlsx-styles cell style syntax] (https://www.npmjs.com/package/xlsx-styles#cell-styles)
formatter	function		Formatting function for data values
name	string	'Sheet x', where x is sheet's order number	Sheet's name
noBorders	boolean	false	True, if cell borders
rows	array of Rows	[]	Row header.

Column

Prop name	Type	Default	Description
header	string		Column header label
merge	number		Count of cells to be merged
valueKeyPath	array		path to column value
valueOptions	object		If valueOptions.multiplier is defined for numeric column, then each values in that column are multiplied by the multiplier

Row

Prop name	Type	Default	Description
header	string		Row header label

Code example

import React from 'react';
import { fromJS } from 'immutable';
import { Button, ControlLabel, Grid, Row, Col } from 'react-bootstrap';

import { Excel, FileInputLabel } from '@opuscapita/excel-service';

export default class ExampleView extends React.PureComponent {
  constructor(props) {
    super(props);
    this.columns = this.initializeColumns();
    this.state = { data: this.initializeData() };
  }

  initializeColumns = () => ([
    {
      header: 'String',
      valueKeyPath: ['string'],
      width: 200,
    },
    {
      header: 'Number',
      valueKeyPath: ['number'],
      width: 200,
    },
    {
      header: 'Float',
      valueKeyPath: ['float'],
      width: 200,
    },
  ])

  initializeData = () => {
    const data = [];
    for (let i = 0; i < 10; i += 1) {
      data.push({ string: `Item ${i}`, number: i, float: `${i}.00` });
    }
    return data;
  }

  readExcelData = (e) => {
    const data = Excel.onLoadCallback(e, this.columns);
    this.setState({ data });
  }

  handleExportToExcelClick = () => {
    Excel.exportToExcel(fromJS(this.state.data), this.columns, 'ExampleExport');
  }

  handleImportFromExcelClick = (e) => {
    Excel.importFromExcel(e.target.files, this.readExcelData);
  }

  render() {
    return (
      <Grid fluid>
        <Row>
          {this.columns.map(column => (
            <Col xs={4} key={column.header}>
              <ControlLabel>
                {column.header}
              </ControlLabel>
            </Col>
          ))}
        </Row>
        {this.state.data.map(row => (
          <Row key={row.number}>
            <Col xs={4}>
              {row.string}
            </Col>
            <Col xs={4}>
              {row.number}
            </Col>
            <Col xs={4}>
              {row.float}
            </Col>
          </Row>
        ))}
        <Row>
          <Col xs={12}>
            <Button onClick={this.handleExportToExcelClick}>
              Export to Excel
            </Button>
          </Col>
        </Row>
        <Row>
          <Col xs={12}>
            <Button>
              <FileInputLabel
                acceptedFiles=".xlsx"
                label="Import from Excel"
                onChange={this.handleImportFromExcelClick}
              />
            </Button>
          </Col>
        </Row>
      </Grid>
    );
  }
}