Pure front-end JavaScript implementation Excel IO case sharing _javascript skills

The company recently made a * * statistics and management system for a state-owned enterprise,

Specific requirements include
Excel Import Export
• Presentation of reports based on imported data
• Chart display (including histogram, line chart, pie chart), but also require animation, flat style
Excel Export, and to provide clients to manage Excel files
•...

That's a lot of demands!

It was finally done, so I analyzed my experience.

--------------------------------------------------------------------------------

In the entire project architecture, you first need to solve the problem of Excel import.

Because the company does not have its own framework to do Excel IO, it is only through other channels.

Well, I found an open source library on the GitHub xlsx, installed via NPM.
npm Install xlsx--save
after that, add a reference to the JS file inside your HTML file
<script src= "./node_modules/xlsx/dist/jszip.js" ></script>
<script src= "./node_modules/xlsx/dist/xlsx.js" ></script>
loading data into memory in binary strings 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 we come down to the operation is to use the library to manipulate the data.

It exposes an object xlsx and can read the data as a JSON object through the xlsx read () method.

var workbook = xlsx.read (data, {type: ' binary '});
var sheetname = workbook. Sheetnames[0];
var sheet = workbook. Sheets[sheetname]; 

Then, use the key-value pair to remove the data from the sheet and put it in the table.

var table = document.createelement (' table ');
for (var row = 1; row++) {
 if (sheet[' + row] = = null) {break
  ;
 }
 var tr = document.createelement (' tr ');

 for (var col = col <=; 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's the complete code:
Index.html

<!
 DOCTYPE html>  

Below is the complete JS code  
index.js 

Window.addeventlistener (' Load ', function () {var target = document.queryselector (' #target ');
 Target.addeventlistener (' DragEnter ', function () {this.classList.remove (' hover ');
 });
 Target.addeventlistener (' DragLeave ', 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 '}), Sheetna me = workbook. Sheetnames[0], sheet = workbook.
  
  Sheets[sheetname], table = document.createelement (' table ');
   for (var row = 1; row++) {if (sheet[' + row] = = null) {break;

   var tr = document.createelement (' tr ');
   for (var col = col <=; 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 effect is as follows:

It seemed to work, but we soon gave it up.
There are too many abuses .
• This library is currently in the development phase, and there are a lot of bugs being put up in issues. There is no way to ensure the stability of the final website.
• This library does not have the means to import merged cell data, can only be very rigid in accordance with ' A ', ' B ', ' C ' ... and 1, 2, 3 coordinates to query the data, and it requires that the internal cell cannot be empty.
• More inconvenient is that it does not have the attributes of row and column counts.
• Because this is done for state-owned enterprises, it is not possible to rely on the key functionality of this star is not a lot of libraries, reduce risk, but also for the security of the site.
•...

--------------------------------------------------------------------------------

After a panel discussion, we decided to use another front-end control called Wijmo.
First, download the Wijmo package from the Web site, and this control does not provide NPM and bower.

And then import the packages I need to come in.

<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.grid.xlsx.min.js" ></script>
< Script src= "./wijmo/dist/controls/wijmo.xlsx.min.js" ></script> 

In addition, there is the introduction of a Jszip package, is the use of JS to extract the compressed package of a library. (Because of MS's Open XML technology, XLSX files can be uncompressed into XML files, App.xml contains the main data).
<script src= "./jszip.min.js" ></script>
the operation of reading the file is the same as the 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 an Excel file into an workbook object in memory.

Print Workbook objects

Printing This object found that the workbook contains a sheets array, each sheet contains rows array, each row contains an cells array, and the Vaule property inside each cell is the value of the cell.
This is so good.

The following implementation of a function Getcollectionview, an array of objects to get the data

 var Getcollectionview = function (workbook) {
 var collectionview = [];
 if (workbook) {
  var sheet = workbook.sheets[0],
   header = [];//column header array
  
  for (var i = 0, length = Sheet.rows.leng Th 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 this is the first row of data, it appears as a column heading, and it is placed in the header array
    if (i = = 0) {
     header.push (cell.value);
    }
    else {
     //The subsequent row array, which is the property store of the Rowarray object, is the title of the column.
     Rowarray[header[j]] = Cell.value;
    }
   if (I!== 0) {
    collectionview.push (rowarray);
   }
 }} return collectionview;
} 

Then I need a table to present the data, and here I use the Wijmo FlexGrid table directly.

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; 

Well, after a few steps, importing Excel to the table has been implemented

This 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

Oh, my.

Two-line code to implement Excel export function

Wijmo.grid.xlsx.FlexGridXlsxConverter.save (DataGrid,
{includecolumnheaders:true}, fileName);
This form also supports filtering, grouping, filtering, and editing.

area and column charts

After completing Excel IO, the control pack can also be used as an area chart, a histogram, and many other types of graphics.
So here's an example of an area chart and a columnar chart.
First, you want to introduce the package.
<script src= "./wijmo/dist/controls/wijmo.chart.min.js" ></script>
then, with the following code, you can insert a histogram into the page

Chart = new Wijmo.chart.FlexChart (' #chart ');
Chart.initialize ({
 itemssource:collectionview,
 bindingx: ' name ',
 options: {
  groupwidth:15
 } ,
 series: [
  {name: ' Ages ', binding: ' Age '},
 ]
}; 

Look at the effect below

Where the shape of the color and histogram can be adjusted. When you move the mouse over the element, there is also a small hint.

Here, you can switch to other types of charts simply by changing the type of chart

Chart.charttype = Chart.charttype = = Wijmo.chart.ChartType.Column?
 Wijmo.chart.ChartType.Area:
 wijmo.chart.ChartType.Column;

The code for this article has been uploaded Http://xiazai.jb51.net/201608/yuanma/js-xlsWijmo-IO (jb51.net). rar

The follow-up will be hosted to GitHub.

Finally, the task was completed relatively quickly.

The Excel IO on this project is simply introduced here, and the project is now complete, with some additional technical details to be shared.

I hope I can help you.

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.