The company recently wants to build a ** statistics and management system for a state-owned enterprise.
Specific requirements include
•Excel import and export
•Display reports based on imported data
• Chart display (including bar charts, line charts, pie charts), and also requires animation effects and flat style
•Excel export and provide clients to manage Excel files
•...
There are so many requirements!
Now it's finally done, so I analyzed my experience.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
In the entire project architecture, the first thing to do is to solve the problem of Excel import.
Since the company does not have its own framework to do Excel IO, it can only go through other channels.
Well, I found an open source library xlsx on github to install it through npm.
npm install xlsx --save
After that, add a reference to the js file in your html file
<script src="./node_modules/xlsx/dist/jszip.js"></script>
<script src="./node_modules/xlsx/dist/xlsx.js"></script>
Load data into memory as a binary string through the FileReader object.
target.addEventListener('drop', function (e) { e.preventDefault(); handleDrop(e.dataTransfer.files[0]);}); handleDrop = function(){ var reader = new FileReader(); reader.onload = function (e) { var data = e.target.result; ... ... }; reader.readAsBinaryString(f);}Then what we are going to do is to use the library to operate on the data data.
It exposes an object XLSX, and the data can be read as a JSON object through XLSX's read() method.
var workbook = XLSX.read(data, { type: 'binary' });var sheetName = workbook.SheetNames[0];var sheet = workbook.Sheets[sheetName];After that, use the key-value pair to take the data out of the sheet and put it in the table.
var table = document.createElement('table');for (var row = 1; ; row++) { if (sheet['A' + row] == null) { break; } var tr = document.createElement('tr'); for (var col = 65; col <= 90; col++) { var c = String.fromCharCode(col);// get 'A', 'B', 'C' ... var key = '' + c + row; if (sheet[key] == null) { break; } var td = document.createElement('td'); td.innerHTML = sheet[key]['w']; tr.appendChild(td); } table.appendChild(tr);}document.querySelector('#target').appendChild(table);Here is the complete code:
index.html
<!DOCTYPE html><html lang="en"><head> <meta charset="UTF-8"> <title>Document</title> <style> #target { height: 400px; width: 700px; background-color: #f8f8f8; margin: 200px auto; overflow:hidden; border-radius:5px; box-shadow:2px 2px 5px #888; } .hover::before { content: 'Please drag the excel file here'; width: 100%; height: 100%; display: block; text-align: center; line-height: 400px; font-size: 24px; font-family: 'Microsoft Yahei'; } #target>table{ height:250px; width:400px; border:1px solid #ccc; border-radius:3px; margin:75px auto; } #target>table td{ text-align:center; border-top:1px solid #ccc; border-left:1px solid #ccc; } #target>table tr:first-child>td{ border-top:0px solid #ccc; } #target>table tr>td:first-child{ border-left:0px solid #ccc; } </style></head><body> <div id="target"> </div> <script src="./node_modules/xlsx/dist/jszip.js"></script> <script src="./node_modules/xlsx/dist/xlsx.js"></script> <script src="index.js"></script></body></html>Below is the complete js code
index.js
window.addEventListener('load', function () { var target = document.querySelector('#target'); target.addEventListener('drager', function () { this.classList.remove('hover'); }); target.addEventListener('dragover', function () { this.classList.add('hover'); }); target.addEventListener('dragover', function (e) { this.classList.remove('hover'); e.preventDefault(); }); target.addEventListener('drop', function (e) { e.preventDefault(); handleDrop(e.dataTransfer.files[0]); });}); var handleDrop = function (f) { var reader = new FileReader(), name = f.name; reader.onload = function (e) { var data = e.target.result, workbook = XLSX.read(data, { type: 'binary' }), sheetName = workbook.SheetNames[0], sheet = workbook.Sheets[sheetName], table = document.createElement('table'); for (var row = 1; ; row++) { if (sheet['A' + row] == null) { break; } var tr = document.createElement('tr'); for (var col = 65; col <= 90; col++) { var c = String.fromCharCode(col);// get 'A', 'B', 'C' ... var key = '' + c + row; if (sheet[key] == null) { break; } var td = document.createElement('td'); td.innerHTML = sheet[key]['w']; tr.appendChild(td); } table.appendChild(tr); } document.querySelector('#target').appendChild(table); }; reader.readAsBinaryString(f);}The effects are as follows:
This seems to work, but we gave up very quickly.
There are too many disadvantages .
•This library is still in the development stage, and there are still many bugs in issues that have been raised. There is no way to ensure the stability of the final website.
•This library has no way to import data from merged cells, it can only query data in a rigid manner according to the 'A', 'B', 'C'... and 1, 2, 3 coordinates, and it requires that the internal cells cannot be empty.
• What is more inconvenient is that it does not have the count properties of rows and columns.
•Since this is done for state-owned enterprises, it is impossible to rely on libraries with not many stars, reducing risks is also for the security of the website.
•...
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
After a group discussion, we decided to use another front-end control called Wijmo.
First, download the Wijmo package from the website. This control does not provide npm and bower methods.
Then import the package I need
<script src="./wijmo/dist/controls/wijmo.min.js"></script><script src="./wijmo/dist/controls/wijmo.grid.min.js"></script><script src="./wijmo/dist/controls/wijmo.grid.detail.min.js"></script><script src="./wijmo/dist/controls/wijmo.xlsx.min.js"></script>
In addition, there is also a JSZIP package, which is a library that uses js to decompress the compressed package. (Because of MS's open xml technology, the xlsx file can be decompressed into an xml file, and the app.xml contains the main data).
<script src="./jszip.min.js"></script>
The operation of reading files is the same as above
var handleDrop = function (file) { var reader, workbook; if (file) { reader = new FileReader; reader.onload = function (e) { workbook = new wijmo.xlsx.Workbook(), workbook.load(reader.result); }; reader.readAsDataURL(file); }}pass
workbook = new wijmo.xlsx.Workbook();
workbook.load(reader.result);
These two lines of code load the excel file into the workbook object in memory.
Print workbook object
Printing this object shows that the workbook contains the sheets array, each sheet contains the rows array, each row contains the cells array, and the vaule attribute in each cell is the value of the cell.
This is simply too good
The following implements a function getCollectionView to obtain data in an array of objects
var getCollectionView = function (workbook) { var collectionView = []; if (workbook) { var sheet = workbook.sheets[0], header = []; // Column header array for (var i = 0, length = sheet.rows.length; i < length; i++) { var row = sheet.rows[i], rowArray = {}; for (var j = 0, jLength = row.cells.length; j < jLength; j++) { var cell = row.cells[j]; // If it is the first row of data, it appears as the column title and put it in the title array if (i === 0) { header.push(cell.value); } else { // The following row array is stored as the attribute of the rowArray object, and the attribute name is the title of the column. rowArray[header[j]] = cell.value; } } if (i !== 0) { collectionView.push(rowArray); } } } return collectionView;}Then a table needs to be used to present the data, here I directly use Wijmo's FlexGrid table.
gridDiv = document.createElement('div');gridDiv.classList.add('grid');dataGrid = new wijmo.grid.FlexGrid(gridDiv);// Construct a FlexGrid form by passing in the container. var collectionView = new wijmo.collections.CollectionView(getCollectionView(workbook));dataGrid.itemsSource = collectionView;OK, after the above steps, importing Excel into the table has been implemented
Here is the complete js code:
index.js
(function () { var dataGrid = null, gridDiv = null, workbook = null; window.addEventListener('load', function () { gridDiv = document.createElement('div'); gridDiv.classList.add('grid'); dataGrid = new wijmo.grid.FlexGrid(gridDiv); var target = document.querySelector('#target'); target.addEventListener('dragenter', function (e) { e.preventDefault(); this.classList.remove('hover'); }); target.addEventListener('dragleave', function (e) { e.preventDefault(); this.classList.add('hover'); }); target.addEventListener('dragover', function (e) { e.preventDefault(); this.classList.remove('hover'); }); target.addEventListener('drop', function (e) { e.preventDefault(); handleDrop(e.dataTransfer.files[0]); // Add this form to the page this.appendChild(gridDiv); }); }); var handleDrop = function (file) { var reader; var workbook; if (file) { reader = new FileReader; reader.onload = function (e) { workbook = new wijmo.xlsx.Workbook(); workbook.load(reader.result); var collectionView = new wijmo.collections.CollectionView(getCollectionView(workbook)); dataGrid.itemsSource = collectionView; // console.log(dataGrid.collectionView); }; reader.readAsDataURL(file); } } var getCollectionView = function (workbook) { var collectionView = []; if (workbook) { var sheet = workbook.sheets[0]; var title = []; for (var i = 0, length = sheet.rows.length; i < length; i++) { var row = sheet.rows[i]; var rowArray = {}; for (var j = 0, jLength = row.cells.length; j < jLength; j++) { var cell = row.cells[j]; if (i === 0) { header.push(cell.value); } else { rowArray[header[j]] = cell.value; } } if (i !== 0) { collectionView.push(rowArray); } } } return collectionView; }})(window);Here is the effect
Excel export
It's already euro
Two sentences of code to implement Excel export function
wijmo.grid.xlsx.FlexGridXlsxConverter.save(dataGrid,
{ includeColumnHeaders: true }, fileName);
This table also supports filtering, grouping, filtering, editing.
Area chart and bar chart
Just after completing Excel IO, I found that this control package can also make area charts, bar charts and many other types of graphics.
So here I will demonstrate an example of an area chart and a bar chart.
First, bring in the bag.
<script src="./wijmo/dist/controls/wijmo.chart.min.js"></script>
Then after the following code, you can insert a bar chart into the page by inserting it
chart = new wijmo.chart.FlexChart('#chart');chart.initialize({ itemsSource: collectionView, bindingX: 'name', options: { groupWidth: 15 }, series: [ { name: 'age', binding: 'age' }, ]});See the effect below
Among them, the color and the shape of the bar chart can be adjusted. When the mouse moves to the element, there will be a small prompt.
Here, you just need to change the type of chart and you can switch to other types of charts
chart.chartType = chart.chartType === wijmo.chart.ChartType.Column? wijmo.chart.ChartType.Area : wijmo.chart.ChartType.Column;
The code about this article has been uploaded http://xiazai.VeVB.COM/201608/yuanma/js-xlsWijmo-IO(VeVB.COM).rar
It will be hosted to github later.
In the end, the task was completed relatively quickly.
This is the brief introduction to Excel IO of this project. This project has been completed now, and some other technical details will be shared in the future.
Hope it can help you.
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.