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.
app.jsx
index.html
app.css
Copy to CodeMine
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 />);
}
Feedback
Submit and view feedback for