Home>

The company recently wants to make a statistics and management system for a state-owned enterprise.

Specific requirements include

•excel import and export

•Display report based on imported data

•chart display (including histograms,Line chart, pie chart), and also requires an animation effect,Flat style

•excel export, and want to provide clients to manage excel files

•...

So many requests!

It's finally done now,Then analyze my experience.

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

Throughout the project architecture,First of all, we must solve the problem of excel import.

Since the company does not have its own framework for doing excel io, it only has to go through other channels.

Well, I found an open source library on githubxlsxto install by npm.

npm install xlsx --save

After that, add a reference to the js file in your own 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 a 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 our operation is to use the library to operate on the data.

It exposes an object xlsx, and the data can be read as a json object through the read () method of xlsx.

var workbook=xlsx.read (data, {type:"binary"});
var sheetname=workbook.sheetnames [0];
var sheet=workbook.sheets [sheetname];

After that, the key-value pairs are used to extract the data from the sheet into 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&t;td {
    border-top:0px solid #ccc;
   }
   #target>table tr&t;td:first-child {
    border-left:0px solid #ccc;
   }
 </style>
</head>
<body>
 <div>
 </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>

Here is the full 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"}),   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 effect is as follows:

This seems to work,But we quickly gave up.

DisadvantageToo many.

•This library is currently under development,There are also many bugs raised in issues. There is no way to guarantee the stability of the final website.

• This library has no way to import data from merged cells,Can only be very rigid to query the data according to "a", "b", "c" ... and 1, 2, 3 coordinates,And it requires that the internal cells cannot be empty.

•The more inconvenient is thatIt does not have a row and column count property.

•Since this is done for state-owned enterprises,Therefore, it is impossible to rely on a key function for a library with a small number of stars.reduce risk,Also for website security.

•...

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

After a group discussion,We decided to use another front-end control,Calledwijmo.

First, download the wijmo package from the website. This control does not provide npm and bower.

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

In addition, there is a jszip package introduced, which is a library that uses js to decompress compressed packages.(Due to ms open xml technology, xlsx files can be decompressed into xml files, and app.xml contains the main data).

<script src="./jszip.min.js"></script>

The operations for reading files are 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);
 }
}

by

workbook=new wijmo.xlsx.workbook ();

workbook.load (reader.result);

These two lines of code load the excel file into a workbook object in memory.

Print workbook object

Print this object and find thatThe workbook contains an array of sheets, each sheet contains an array of rows, each row contains an array of cells, and the vaule attribute in each cell is the value of the cell.

This is so good

The following implements a function getcollectionview to get data as an array of objects

var getcollectionview=function (workbook) {
 var collectionview=[];
 if (workbook) {
  var sheet=workbook.sheets [0],   header=[];//array of column headers
  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 line of data,Then it appears as a column header,Into the title array.
    if (i === 0) {
     header.push (cell.value);
    }
    else {
     //the following row array,Is stored as a property of the rowarray object,The attribute name is the title of the column.
     rowarray [header [j]]=cell.value;
    }
   }
   if (i! == 0) {
    collectionview.push (rowarray);
   }
  }
 }
 return collectionview;
}

Then you need a table to present the data,Here I used wijmo's flexgrid form 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;

Okay, after the above steps,Importing excel into a 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

over

Two lines of code to implement the excel export function

wijmo.grid.xlsx.flexgridxlsxconverter.save (datagrid,

{includecolumnheaders:true}, filename);

This form also supports filtering,Group, filter, edit.

Area chart and histogram

After completing excel io, I found that this control package can also make area maps.Histograms and many other types of graphs.

So here is an example of an area chart and a histogram.

First, bring the package in.

<script src="./wijmo/dist/controls/wijmo.chart.min.js"></script>

Then after a few lines of code,You can use to insert a histogram in the page

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 histogram can be adjusted.When the mouse moves over the element,There will be small tips.

Here, you only need to change the chart type to switch to other types of charts.

chart.charttype=chart.charttype === wijmo.chart.charttype.column? wijmo.chart.charttype.area: wijmo.chart.charttype.column;

It will be hosted on github.

In the end, the task was completed relatively quickly.