@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

npm install @opuscapita/excel-service

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

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>
    );
  }
}

Changelog

1.0.1

• Fixed column headerless !ref range area