Store the contents of the Excel file in a database and view it in real time (without having to generate files)

Source: Internet
Author: User
Tags base64 file upload

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced

This article is mainly about front-end content, the backend involves less, can be considered to use Java.

The first is the Excel file upload, this is relatively simple, you can HTML5 the Data Interface Formdata () to operate. The specific code is as follows:

<!DOCTYPE HTML><HTML>    <Head>    </Head>    <Body>        <inputtype= "File"ID= "_file"value=""/><P for= "File">Or click here to select a file</P>    
<button id = "File_button" > button </button></Body></HTML>

Then it's the corresponding JavaScript.

$ ("#button"). Click (function () {  var files = $ (' #_file '). Prop (' files ');  var data = new FormData ();  Data.append ("Upload", files[0]); Because it is only select a file, so only take file[0]  $.ajax ({    url:yourpath,    type: ' POST ',    data:data,     Cache:false,    dataType: ' text ',    processdata:false,    contenttype:false,    success:function (Result) {                // Do something with the result        alert ("Success")    ,    error:function (result) {        alert ("Failed" + Result.tostring ());});  

After the backend receives the file, it is stored as a binary array, in the database, such as PostgreSQL, using the BLOB data type, and then using a byte array mapping in Java.

So how to store the contents of the file from the backend directly on the page Excel? Here need to use Sheetjs, official website: http://sheetjs.com/, can directly take its demo to use,

The demo is on GitHub: Https://github.com/SheetJS/SheetJS.github.io.

Here is the direct use of some of its code, mainly talking about ideas.

Download after decompression is the Sheetjs.github.io-master folder, while the display of the generated Excel JS code is mainly in the sheetjs.github.io-master\assets\js\dropsheet.js. Where the following code is sent in its final pass by calling the file

functionHandledrop (e) {e.stoppropagation ();        E.preventdefault (); if(pending)returnopts.errors.pending (); var files = e.datatransfer.files; vari,f; for (i = 0, f = files[i]; I! = Files.length; ++i) {var reader = new FileReader (); varName =F.name; Reader.onload=function(e) {vardata =E.target.result; varWB, arr; var readtype = {Type:rabs?                ' binary ': ' base64 '}; if(!RABS) {arr=fixdata (data); Data=Btoa (arr); }                functiondoit () {Try {                        if(Useworker) {SHEETJSW (data, PROCESS_WB, Readtype);return; } WB=xlsx.read (data, readtype);                    PROCESS_WB (WB); } Catch(e) {console.log (e); opts.errors.failed (e);} }                if(E.target.result.length > 1e6) opts.errors.large (E.target.result.length,function(e) {if(e) doit ();}); Else{doit ();}            }; if(RABS) reader.readasbinarystring (f); ElseReader.readasarraybuffer (f); }    }

Description: Sheetjs This demo, the analysis of Excel using JS-XLSX This library, the Library of Excel operations, parsing is only one aspect, specifically can be seen on GitHub. The table control is canvas-datagrid.js based on parsing the Excel table that is drawn on the HTML.

You can see that it reads each selected file through the Readasbinarystring method of FileReader, drawing an Excel table in HTML based on the contents of the file, so we just have to receive the binary data from the backend here and generate a file object. Run again the same code will be OK.

Two problems are encountered in this process:

One is that the backend byte array is very inconvenient to pass to the front end, and when I pass it with Ajax, the binary array parameter becomes a string type.

The other is that JavaScript cannot create a new file object directly ...

First of all , the binary array cannot be passed, then the string can only be passed, but not directly to the string, then what is the method? And then the code above saw that there was a sentence

var readtype = {Type:rabs? ' binary ': ' base64 '};

Description This operation can manipulate base64 encoded string, that is to turn into Base64, this need to introduce Apache a package, the package named Commons-codec, and then maven referenced as follows,

<!--https://mvnrepository.com/artifact/commons-codec/commons-codec-<dependency >    <groupId>commons-codec</groupId>    <artifactId>commons-codec</artifactId>    <version>1.10</version></dependency>

And then call one of its methods,

Base64.encodebase64string (ByteArray);   Convert ByteArray to Base64 string

This makes it possible to transfer to the front end.

The second question , Baidu found that JavaScript has a data type blob, and file is based on this blob.

A Blob object is a class file object that contains read-only raw data

But blobs can be initialized to generate,

var blob = new Blob ([Base64data], {type: "MIME"}) can then be read through FileReader, then just follow the demo code to call it ... Reader.readasbinarystring (BLOB);..

Store the contents of the Excel file in a database and view it in real time (without having to generate files)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.