Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you about how to analyze the excel file in JavaScript. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

After the js file is introduced into the page, there will be a global variable XLSX

This global variable has many property methods, as shown in the figure:

We only introduce the three most commonly used, the above picture has been drawn with red lines, read,utils,writeFile three methods.

This read method is used to read the contents of the excle file mentioned above, which requires passing a parameter, which is excle data of a binary data type.

So here's the question: how can we get the binary data from the excle file?

Here comes the question of how browsers read binary data from excel files, which requires a new feature of the HTML5 specification, FileReader. Api document address: https://developer.mozilla.org/zh-CN/docs/Web/API/FileReader

The following is a brief introduction:

The FileReader object allows Web applications to asynchronously read the contents of files (or raw data buffers) stored on the user's computer, using File or Blob objects to specify the files or data to read.

The File object can come from the FileList object returned after the user selects a file on an element, from the DataTransfer object generated by the drag-and-drop operation, or from the result returned after executing the mozGetAsFile () method on a HTMLCanvasElement.

In adult translation, FileReader allows us to read the contents of the user's computer and get the contents of the files on the computer through the File object or blob object, that is, FileReader, by reading the File object or the Blob object.

So how do you get the File object? You can use an input element to upload a file or drag and drop objects.

For simplicity here, we get the File object through an input element of the uploaded file. The code demonstrates that there is only one input element and listens to the change event of this element to try to pass an excel file:

The print result is as follows:

You get an array through the target.files of input's change event object, and the first item of the array is data of type File. (it's important to note here why files is an array. Because input can upload multiple files at the same time, we only upload one file here, so select the 0th element.

Now that you have the data of type File, you need to use FileReader to read the File to get the binary content.

How to read it? Look at the code:

According to the method of using FileReader documents, it is divided into three steps: the first step is to new a FileReader object, the second step is to listen to the onload function of the FileReader instance, the event object of the function stores the read result, and the third step is to read the File file into a binary file by calling the readAsBinaryString method of FIleReader. The result is as follows:

The above is the result of the binary data displayed in the browser. Note here that the readAsBinaryString in step 3 can be replaced with another method, as follows:

Replace readAsBinaryString with the readAsArrayBuffer method, and the print result is as follows:

Both methods are fine, but you need to note that what readAsBinaryString gets is data of type BinaryString, which can be understood as a binary string.

ReadAsArrayBuffer gets data of type ArrayBuffer, which can be understood as special binary data. The specific differences between the two are not discussed here.

Now that you have the binary data, you need to use the read method of the npm package XLSX to read the binary data. The code is as follows:

It should be noted that when calling XLSX to read the binary data type of excel, you must specify the data type. The binary data in the figure above is read through the readAsBinaryString method, so type should be passed into binary. If it is readAsArrayBuffer, the corresponding type should be changed to 'array'. The code is as follows:

The above print result is the same, as shown in the figure:

This includes all the data of excel, but can not be used for the time being. There are two more important attributes, SheetNames and Sheets, one that holds the names of all documents, and one that holds the data of documents.

Let's take a look at an excel shown in the figure:

We can see that the rows of excel are represented by ABCD, the columns are represented by numbers, and an excel can have multiple tables, each with a separate name, such as sheet1 and sheet2.

Let's re-upload the test excel on our page and observe the print result:

We can see that SheetNames can get the names of all the tables in excel, but the data of sheets doesn't seem to be right. What if this doesn't work?

There are many ways to print another property of XLSX, utils, as shown in the figure:

Many, very powerful, careful observation, found that there is a sheet_to_json method, then try, the code is as follows:

The print result is as follows:

After successfully getting the json, it is easy to get the json. It is rendered and passed to the backend, whatever you want. There are other ways you can also try, there is no demonstration here.

The above is the browser with the help of js to read excel files, a small amount of code but a little bit cumbersome, involving FileReader, a new feature of html5.

Next, let's demonstrate the operation of writing: with regard to the operation of writing, we also start with a requirement, for example, there is a table table on the page, there is a download button next to the form, and the user clicks the download button to download the table form to the user's computer in the form of excel. As shown in the figure:

The code is as follows:

The results of the demonstration are as follows:

Open the excel document as shown below:

The above is how to parse the excel file in the JavaScript shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.