Workbook Viewer

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.

import 'bootstrap.css'; import '@mescius/wijmo.styles/wijmo.css'; import * as wjcCore from '@mescius/wijmo'; import * as wjcXlsx from '@mescius/wijmo.xlsx'; import './styles.css'; // document.readyState === 'complete' ? init() : window.onload = init; // function init() { let workbook; // document.querySelector('#importFile').addEventListener('change', () => { loadWorkbook(); }); // document.querySelector('#sheetNav').addEventListener('click', (e) => { e.preventDefault(); // let navEle = e.target.parentElement; let activeIndex = toggleNavActiveStatus(navEle); if (activeIndex !== -1) { drawSheet(activeIndex); } }); // function loadWorkbook() { let reader = new FileReader(); // reader.onload = (e) => { let tmp = new wjcXlsx.Workbook(); tmp.loadAsync(reader.result, (result) => { workbook = result; drawSheetNav(workbook.activeWorksheet || 0); drawSheet(workbook.activeWorksheet || 0); }); }; // let file = document.getElementById('importFile').files[0]; if (file) { reader.readAsDataURL(file); } } // function drawSheet(sheetIndex) { let drawRoot = document.getElementById('tableHost'); drawRoot.textContent = ''; drawWorksheet(workbook, sheetIndex, drawRoot, 200, 100); } // function drawSheetNav(activeIndex) { let contianer = document.querySelector('#sheetNav'); contianer.innerHTML = ''; let navItem = ''; for (let i = 0; i < workbook.sheets.length; i++) { let sheet = workbook.sheets[i]; navItem += `<li role="presentation" class="${i === activeIndex ? 'active' : ''}"><a href="#">${sheet.name}</a></li>`; } contianer.innerHTML = navItem; } // function 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++) { if (sheet.rows[r] && sheet.rows[r].cells) { maxRowCells = Math.max(maxRowCells, sheet.rows[r].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; } } // // generate rows let 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'); 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); } // function 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; } // function 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; } } } // function toggleNavActiveStatus(navEle) { let activeIndex = -1; let navEles = document.querySelectorAll('#sheetNav li'); for (let i = 0; i < navEles.length; i++) { let currentItem = navEles[i]; wjcCore.removeClass(currentItem, 'active'); if (currentItem === navEle) { wjcCore.addClass(currentItem, 'active'); activeIndex = i; } } return activeIndex; } }
<!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <title>MESCIUS 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.21.5/system.src.js" integrity="sha512-skZbMyvYdNoZfLmiGn5ii6KmklM82rYX2uWctBhzaXPxJgiv4XBwJnFGr5k8s+6tE1pcR1nuTKghozJHyzMcoA==" crossorigin="anonymous"></script> <script src="systemjs.config.js"></script> <script> System.import('./src/app'); </script> </head> <body> <div class="container-fluid"> <div class="row"> <input type="file" class="form-control" id="importFile" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel.sheet.macroEnabled.12" style="width:300px" /> </div> <!-- Sheet tabs --> <ul id="sheetNav" class="nav nav-tabs" style="margin-top:40px"> </ul> <!-- Current sheet view --> <div id="tableHost"></div> </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 }, 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.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.touch': 'npm:@mescius/wijmo.touch/index.js', '@mescius/wijmo.cloud': 'npm:@mescius/wijmo.cloud/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', 'jszip': 'npm:jszip/dist/jszip.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' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'js' }, "node_modules": { defaultExtension: 'js' }, } }); })(this);