Front-end Read Excel report file

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

<type= "file"  ID= "Excel-file">

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

<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) {varFiles =E.target.files; varFileReader =NewFileReader (); Filereader.onload=function(EV) {Try {                    vardata =Ev.target.result, Workbook=Xlsx.read (data, {type:' Binary '                        }), //read the entire Excel table object in binary stream modepersons = [];//Store the acquired data}Catch(e) {Console.log (' File type is incorrect '); return; }                //Table range of tables, which can be used to determine if the header quantity is correct                varFromTo = ' '; //iterate through each table read                 for(varSheetinchworkbook. 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 if you only take the first table}} console.log (persons);            }; //open a file in binary modeFilereader.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 ') {     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) {                        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 .

Front-end Read Excel report file