Using the JS-XLSX library, the front-end reads Excel report files

In the actual development, often encounter the need to import Excel files, some product people think more, want to directly determine the front-end file content format is correct, required fields are filled

Depending on the HTML5 filereader, you can open a local file using the new API (refer to this article)

• Filereader.readasbinarystring (blob| File)
• Filereader.readastext (blob| File, opt_encoding)
• Filereader.readasdataurl (blob| File)
• Filereader.readasarraybuffer (blob| File)

Through readasbinarystring Although can open binary files (*.pdf, *.doc, *.xls, etc.), but if you want to directly through the JS to determine whether these binary data meet the requirements, it is not possible

So you need to convert, for the Excel file content analysis conversion, has a relatively mature solution: js-xlsx

The implementation of the new features are also used to see how to use the specific

1. First define a file upload item

<input type= "File" id= "Excel-file" >

2. After downloading the corresponding xlsx.core.min.js file for js-xlsx, introduce

<script type= "Text/javascript" src= "Xlsx.core.min.js" ></script>

3. Listening file selection, instant Open file to get the table content

Use the XLSX.utils.sheet_to_json method to parse a Table object to return the appropriate JSON data

    $ (' #excel-file '). Change (function (e) {var files = e.target.files;            var filereader = new FileReader ();                        Filereader.onload = function (ev) {try {var data = Ev.target.result, Workbook = Xlsx.read (data, {type: ' binary '}),//Read the entire copy in binary stream mode exc El Table Object persons = [];                    Store acquired data} catch (e) {console.log (' incorrect file type ');                Return                }//Table range can be used to determine whether the number of headers is correct var fromTo = '; Traverse each table to read for (var sheet in workbook. Sheets) {if (workbook. Sheets.hasownproperty (sheet)) {fromTo = workbook.                        sheets[sheet]['!ref '];                        Console.log (FromTo); Persons = Persons.concat (XLSX.utils.sheet_to_json (workbook.                        Sheets[sheet]));Break            Uncomment this line}} console.log (persons) If you only take the first table;            };        Open file in binary mode filereader.readasbinarystring (Files[0]); });

The above code simply gets the contents of the table file, and if you want to judge it, you have to add some judgment logic to the actual

JS-XLSX can only read the relevant files, otherwise it will error

4. Table File contents

Use an Excel table to define three tables

5. View the data obtained

The Red box section is the difference between the three table items and looks at the results after the code executes:

The first red box is the data range of each table, because js-xlsx this library itself problem, it is not directly get to the table header data saved, but directly put the table head to the last level of content object inside

You can step through the Read Table Workbook object content:

If you want to determine the number of headers, you need to use the!ref property

In an Excel file, the letters in the A1:D3 refer to the columns to be numbered, followed by the number of digits, so the number of headers is judged by the number of columns.

For example, the first table has four headers, so it is a-d; the third table has five headers, so for a-e, such as

if (fromto[0] = = = ' A ' && fromto[3] = = = ' D ') {     Excelisok = true;}

This is only to judge the number of headers, but also to determine the contents of the table header

        Console.log (persons);                var headstr = ' name, gender, age, school ';                for (var i = 0; i < persons.length; i++) {                    if (Object.keys (Persons[i]). Join (', ')!== headstr) {                        Persons.splice ( I, 1);                    }                }                Console.log (persons);

You can see the JSON data for the second-to-last item because the header content does not match and is filtered out

It is also important to note that the last item, although there is a table header, but because there is no content, so the JSON is met the requirements, so it is necessary to use the!ref attribute to jointly determine whether the table meets the requirements

6. Other

In addition, there are more uses such as Xlsx.readfile, Xlsx.writefile and so on, see the official documents.

Using the JS-XLSX library, the front-end reads Excel report files