Workbook Viewer (React)

This sample implements a simple Excel workbook viewer that loads xlsx file content and displays it on the page.

The sample creates a Workbook instance, then loads an xlsx file content to the workbook instance using the Workbook.load method. After that, it walks through the content of the current workbook sheet provided by WorkSheet class, and creates an HTML table that visualizes the content.

The sample also creates tabs that allow switching between workbook sheets. It is implemented using the BootstrapJS tab-like styling of the <li> elements with the applied ng-repeat directive that iterates through the Workbook.sheets collection.

This example uses React.

import 'bootstrap.css'; import '@mescius/wijmo.styles/wijmo.css'; import ReactDOM from 'react-dom/client'; import React, { useEffect, useState } from 'react'; import * as wjcCore from '@mescius/wijmo'; import * as wjcXlsx from '@mescius/wijmo.xlsx'; import './app.css'; function App() { const [workbook, setWorkbook] = useState(null); const [sheetIndex, setSheetIndex] = useState(0); const drawSheet = (index) => { const drawRoot = document.getElementById('tableHost'); if (drawRoot) { drawRoot.textContent = ''; setSheetIndex(index); if (workbook) { drawWorksheet(workbook, index, drawRoot, 200, 100); } } }; const drawWorksheet = (workbook, sheetIndex, rootElement, maxRows, maxColumns) => { //NOTES: //Empty cells' values are numeric NaN, format is "General" // //Excessive empty properties: //fill.color = undefined // // netFormat should return '' for ''. What is 'General'? // font.color should start with '#'? // Column/row styles are applied to each cell style, this is convenient, but Column/row style info should be kept, // for column/row level styling // formats conversion is incorrect - dates and virtually everything; netFormat - return array of formats? // ?row heights - see hello.xlsx if (!workbook || !workbook.sheets || sheetIndex < 0 || workbook.sheets.length == 0) { return; } sheetIndex = Math.min(sheetIndex, workbook.sheets.length - 1); if (maxRows == null) { maxRows = 200; } if (maxColumns == null) { maxColumns = 100; } // Namespace and XlsxConverter shortcuts. let sheet = workbook.sheets[sheetIndex], defaultRowHeight = 20, defaultColumnWidth = 60, tableEl = document.createElement('table'); tableEl.border = '1'; tableEl.style.borderCollapse = 'collapse'; let maxRowCells = 0; for (let r = 0; sheet.rows && r < sheet.rows.length; r++) { const cells = sheet.rows[r].cells; if (sheet.rows[r] && cells) { maxRowCells = Math.max(maxRowCells, cells.length); } } // add columns let columns = sheet.columns || [], invisColCnt = columns.filter(col => col.visible === false).length; if (sheet.columns) { maxRowCells = Math.min(Math.max(maxRowCells, columns.length), maxColumns); for (let c = 0; c < maxRowCells; c++) { let col = columns[c]; if (col && !col.visible) { continue; } let colEl = document.createElement('col'); tableEl.appendChild(colEl); let colWidth = defaultColumnWidth + 'px'; if (col) { importStyle(colEl.style, col.style); if (col.autoWidth) { colWidth = ''; } else if (col.width != null) { colWidth = col.width + 'px'; } } colEl.style.width = colWidth; } } if (sheet.rows) { const rowCount = Math.min(maxRows, sheet.rows.length); for (let r = 0; sheet.rows && r < rowCount; r++) { let row = sheet.rows[r], cellsCnt = 0; // including colspan if (row && !row.visible) { continue; } let rowEl = document.createElement('tr'); tableEl.appendChild(rowEl); if (row) { importStyle(rowEl.style, row.style); if (row.height != null) { rowEl.style.height = row.height + 'px'; } for (let c = 0; row.cells && c < row.cells.length; c++) { let cell = row.cells[c], cellEl = document.createElement('td'), col = columns[c]; if (col && !col.visible) { continue; } cellsCnt++; rowEl.appendChild(cellEl); if (cell) { importStyle(cellEl.style, cell.style); let value = cell.value; if (!(value == null || value !== value)) { // TBD: check for NaN should be eliminated if (wjcCore.isString(value) && value.charAt(0) == "'") { value = value.substr(1); } let netFormat = ''; if (cell.style && cell.style.format) { netFormat = wjcXlsx.Workbook.fromXlsxFormat(cell.style.format)[0]; } let fmtValue = netFormat ? wjcCore.Globalize.format(value, netFormat) : value; cellEl.innerHTML = wjcCore.escapeHtml(fmtValue); } if (cell.colSpan && cell.colSpan > 1) { cellEl.colSpan = getVisColSpan(columns, c, cell.colSpan); cellsCnt += cellEl.colSpan - 1; c += cell.colSpan - 1; } if (cell.note) { wjcCore.addClass(cellEl, 'cell-note'); if (cell.note.text) { cellEl.title = cell.note.text; } } } } } // // pad with empty cells let padCellsCount = maxRowCells - cellsCnt - invisColCnt; for (let i = 0; i < padCellsCount; i++) { rowEl.appendChild(document.createElement('td')); } // if (!rowEl.style.height) { rowEl.style.height = defaultRowHeight + 'px'; } } } // // do it at the end for performance rootElement.appendChild(tableEl); }; const getVisColSpan = (columns, startFrom, colSpan) => { let res = colSpan; for (let i = startFrom; i < columns.length && i < startFrom + colSpan; i++) { let col = columns[i]; if (col && !col.visible) { res--; } } return res; }; const importStyle = (cssStyle, xlsxStyle) => { if (!xlsxStyle) { return; } if (xlsxStyle.fill) { if (xlsxStyle.fill.color) { cssStyle.backgroundColor = xlsxStyle.fill.color; } } if (xlsxStyle.hAlign && xlsxStyle.hAlign != wjcXlsx.HAlign.Fill) { cssStyle.textAlign = wjcXlsx.HAlign[xlsxStyle.hAlign].toLowerCase(); } let font = xlsxStyle.font; if (font) { if (font.family) { cssStyle.fontFamily = font.family; } if (font.bold) { cssStyle.fontWeight = 'bold'; } if (font.italic) { cssStyle.fontStyle = 'italic'; } if (font.size != null) { cssStyle.fontSize = font.size + 'px'; } if (font.underline) { cssStyle.textDecoration = 'underline'; } if (font.color) { cssStyle.color = font.color; } } }; const handleFileInputChange = (event) => { const files = event.currentTarget.files; if (files && files.length) { const file = files[0]; const reader = new FileReader(); reader.onload = () => { const workbook = new wjcXlsx.Workbook(); workbook.loadAsync(reader.result, (result) => { setWorkbook(result); }); }; reader.readAsDataURL(file); } }; useEffect(() => { if (workbook) { drawSheet(workbook.activeWorksheet || 0); } }, [workbook]); const renderList = () => { let lists = []; if (workbook) { for (let i = 0; i < workbook.sheets.length; i++) { const sheet = workbook.sheets[i]; lists.push(<li key={i} role="presentation" className={i === sheetIndex ? 'active' : ''}> <a href="#" onClick={e => { e.preventDefault(); drawSheet(i); }}> {sheet.name} </a> </li>); } } return (<ul className="nav nav-tabs" style={{ marginTop: 40 }}> {lists} </ul>); }; return (<div className="container-fluid"> <div className="row"> <input id="importFile" type="file" className="form-control" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel.sheet.macroEnabled.12" style={{ width: 300 }} onChange={handleFileInputChange}/> </div> {renderList()} <div id="tableHost"></div> </div>); } const container = document.getElementById('app'); if (container) { const root = ReactDOM.createRoot(container); root.render(<App />); }
<!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <title>MESCIUS Wijmo Wijmo Workbook Viewer</title> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <!-- SystemJS --> <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.40/system.src.js" integrity="sha512-G6mEj6h18+m3MvzdviSDfPle/TfH0//cXcB33AKlNR/Rha0yQsKefDZKRTkIZos97HEGq2JMV1RT5ybMoQ3WsQ==" crossorigin="anonymous"></script> <script src="systemjs.config.js"></script> <script> System.import('./src/app'); </script> </head> <body> <div id="app"></div> </body> </html>
.cell-note { position: relative; } .cell-note:after { content: ""; position: absolute; display: block; top: 0; right: 0; border-left: 7px solid transparent; border-top: 7px solid red; }
(function (global) { System.config({ transpiler: 'plugin-babel', babelOptions: { es2015: true, react: true }, meta: { '*.css': { loader: 'css' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { 'jszip': 'npm:jszip/dist/jszip.js', '@mescius/wijmo': 'npm:@mescius/wijmo/index.js', '@mescius/wijmo.input': 'npm:@mescius/wijmo.input/index.js', '@mescius/wijmo.styles': 'npm:@mescius/wijmo.styles', '@mescius/wijmo.cultures': 'npm:@mescius/wijmo.cultures', '@mescius/wijmo.chart': 'npm:@mescius/wijmo.chart/index.js', '@mescius/wijmo.chart.analytics': 'npm:@mescius/wijmo.chart.analytics/index.js', '@mescius/wijmo.chart.animation': 'npm:@mescius/wijmo.chart.animation/index.js', '@mescius/wijmo.chart.annotation': 'npm:@mescius/wijmo.chart.annotation/index.js', '@mescius/wijmo.chart.finance': 'npm:@mescius/wijmo.chart.finance/index.js', '@mescius/wijmo.chart.finance.analytics': 'npm:@mescius/wijmo.chart.finance.analytics/index.js', '@mescius/wijmo.chart.hierarchical': 'npm:@mescius/wijmo.chart.hierarchical/index.js', '@mescius/wijmo.chart.interaction': 'npm:@mescius/wijmo.chart.interaction/index.js', '@mescius/wijmo.chart.radar': 'npm:@mescius/wijmo.chart.radar/index.js', '@mescius/wijmo.chart.render': 'npm:@mescius/wijmo.chart.render/index.js', '@mescius/wijmo.chart.webgl': 'npm:@mescius/wijmo.chart.webgl/index.js', '@mescius/wijmo.chart.map': 'npm:@mescius/wijmo.chart.map/index.js', '@mescius/wijmo.gauge': 'npm:@mescius/wijmo.gauge/index.js', '@mescius/wijmo.grid': 'npm:@mescius/wijmo.grid/index.js', '@mescius/wijmo.grid.detail': 'npm:@mescius/wijmo.grid.detail/index.js', '@mescius/wijmo.grid.filter': 'npm:@mescius/wijmo.grid.filter/index.js', '@mescius/wijmo.grid.search': 'npm:@mescius/wijmo.grid.search/index.js', '@mescius/wijmo.grid.grouppanel': 'npm:@mescius/wijmo.grid.grouppanel/index.js', '@mescius/wijmo.grid.multirow': 'npm:@mescius/wijmo.grid.multirow/index.js', '@mescius/wijmo.grid.transposed': 'npm:@mescius/wijmo.grid.transposed/index.js', '@mescius/wijmo.grid.transposedmultirow': 'npm:@mescius/wijmo.grid.transposedmultirow/index.js', '@mescius/wijmo.grid.pdf': 'npm:@mescius/wijmo.grid.pdf/index.js', '@mescius/wijmo.grid.sheet': 'npm:@mescius/wijmo.grid.sheet/index.js', '@mescius/wijmo.grid.xlsx': 'npm:@mescius/wijmo.grid.xlsx/index.js', '@mescius/wijmo.grid.selector': 'npm:@mescius/wijmo.grid.selector/index.js', '@mescius/wijmo.grid.cellmaker': 'npm:@mescius/wijmo.grid.cellmaker/index.js', '@mescius/wijmo.grid.immutable': 'npm:@mescius/wijmo.grid.immutable/index.js', '@mescius/wijmo.touch': 'npm:@mescius/wijmo.touch/index.js', '@mescius/wijmo.cloud': 'npm:@mescius/wijmo.cloud/index.js', '@mescius/wijmo.nav': 'npm:@mescius/wijmo.nav/index.js', '@mescius/wijmo.odata': 'npm:@mescius/wijmo.odata/index.js', '@mescius/wijmo.olap': 'npm:@mescius/wijmo.olap/index.js', '@mescius/wijmo.rest': 'npm:@mescius/wijmo.rest/index.js', '@mescius/wijmo.pdf': 'npm:@mescius/wijmo.pdf/index.js', '@mescius/wijmo.pdf.security': 'npm:@mescius/wijmo.pdf.security/index.js', '@mescius/wijmo.viewer': 'npm:@mescius/wijmo.viewer/index.js', '@mescius/wijmo.xlsx': 'npm:@mescius/wijmo.xlsx/index.js', '@mescius/wijmo.undo': 'npm:@mescius/wijmo.undo/index.js', '@mescius/wijmo.interop.grid': 'npm:@mescius/wijmo.interop.grid/index.js', '@mescius/wijmo.barcode': 'npm:@mescius/wijmo.barcode/index.js', '@mescius/wijmo.barcode.common': 'npm:@mescius/wijmo.barcode.common/index.js', '@mescius/wijmo.barcode.composite': 'npm:@mescius/wijmo.barcode.composite/index.js', '@mescius/wijmo.barcode.specialized': 'npm:@mescius/wijmo.barcode.specialized/index.js', "@mescius/wijmo.react.chart.analytics": "npm:@mescius/wijmo.react.chart.analytics/index.js", "@mescius/wijmo.react.chart.animation": "npm:@mescius/wijmo.react.chart.animation/index.js", "@mescius/wijmo.react.chart.annotation": "npm:@mescius/wijmo.react.chart.annotation/index.js", "@mescius/wijmo.react.chart.finance.analytics": "npm:@mescius/wijmo.react.chart.finance.analytics/index.js", "@mescius/wijmo.react.chart.finance": "npm:@mescius/wijmo.react.chart.finance/index.js", "@mescius/wijmo.react.chart.hierarchical": "npm:@mescius/wijmo.react.chart.hierarchical/index.js", "@mescius/wijmo.react.chart.interaction": "npm:@mescius/wijmo.react.chart.interaction/index.js", "@mescius/wijmo.react.chart.radar": "npm:@mescius/wijmo.react.chart.radar/index.js", "@mescius/wijmo.react.chart": "npm:@mescius/wijmo.react.chart/index.js", "@mescius/wijmo.react.core": "npm:@mescius/wijmo.react.core/index.js", '@mescius/wijmo.react.chart.map': 'npm:@mescius/wijmo.react.chart.map/index.js', "@mescius/wijmo.react.gauge": "npm:@mescius/wijmo.react.gauge/index.js", "@mescius/wijmo.react.grid.detail": "npm:@mescius/wijmo.react.grid.detail/index.js", "@mescius/wijmo.react.grid.filter": "npm:@mescius/wijmo.react.grid.filter/index.js", "@mescius/wijmo.react.grid.grouppanel": "npm:@mescius/wijmo.react.grid.grouppanel/index.js", '@mescius/wijmo.react.grid.search': 'npm:@mescius/wijmo.react.grid.search/index.js', "@mescius/wijmo.react.grid.multirow": "npm:@mescius/wijmo.react.grid.multirow/index.js", "@mescius/wijmo.react.grid.sheet": "npm:@mescius/wijmo.react.grid.sheet/index.js", '@mescius/wijmo.react.grid.transposed': 'npm:@mescius/wijmo.react.grid.transposed/index.js', '@mescius/wijmo.react.grid.transposedmultirow': 'npm:@mescius/wijmo.react.grid.transposedmultirow/index.js', '@mescius/wijmo.react.grid.immutable': 'npm:@mescius/wijmo.react.grid.immutable/index.js', "@mescius/wijmo.react.grid": "npm:@mescius/wijmo.react.grid/index.js", "@mescius/wijmo.react.input": "npm:@mescius/wijmo.react.input/index.js", "@mescius/wijmo.react.olap": "npm:@mescius/wijmo.react.olap/index.js", "@mescius/wijmo.react.viewer": "npm:@mescius/wijmo.react.viewer/index.js", "@mescius/wijmo.react.nav": "npm:@mescius/wijmo.react.nav/index.js", "@mescius/wijmo.react.base": "npm:@mescius/wijmo.react.base/index.js", '@mescius/wijmo.react.barcode.common': 'npm:@mescius/wijmo.react.barcode.common/index.js', '@mescius/wijmo.react.barcode.composite': 'npm:@mescius/wijmo.react.barcode.composite/index.js', '@mescius/wijmo.react.barcode.specialized': 'npm:@mescius/wijmo.react.barcode.specialized/index.js', 'jszip': 'npm:jszip/dist/jszip.js', 'react': 'npm:react/umd/react.production.min.js', 'react-dom': 'npm:react-dom/umd/react-dom.production.min.js', 'react-dom/client': 'npm:react-dom/umd/react-dom.production.min.js', 'redux': 'npm:redux/dist/redux.min.js', 'react-redux': 'npm:react-redux/dist/react-redux.min.js', 'bootstrap.css': 'npm:bootstrap/dist/css/bootstrap.min.css', 'css': 'npm:systemjs-plugin-css/css.js', 'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js', 'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js', "react-use-event-hook": "npm:react-use-event-hook/dist/esm/useEvent.js", }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'jsx' }, "node_modules": { defaultExtension: 'js' }, } }); })(this);