CSV and Excel read and download

In HTML5, DOM adds a collection of files to the File. In the selected File, files contains a File object, each object has the following properties:

Name: indicates the name of the local file
Size: indicates the file size
Type: string, MIME type of the file
LastModifiedDate: string time when the file was last modified

Also, using FileReader objects, web applications can asynchronously read the contents of files (or raw data buffers) stored on the user’s computer, and can use File objects or Blob objects to specify files or data to be processed. FileReader provides the following methods.

ReadAsText (file,encoding) : Reads the file as plain text and saves the read text in the Result property. The second parameter, which specifies the encoding type, is optional.
ReadAsDataURL (file) : Reads the file and stores the file as a data URI in the Result property.
ReadAsBinaryString (file) : Reads the file and stores a string in the Result property, where each character represents a byte.
ReadAsArrayBuffer (file) : Reads the file and stores an ArrayBuffer containing the contents of the file in the Result property. These methods of reading files provide great convenience for flexible processing of file data. For example, an image file can be read and saved as a data URI for display to the user, or a file can be read as text for parsing convenience.

Let’s start with an example

Requirement: if the file is read as an image, it will be displayed as IMG, otherwise it will be output as text.

To read a file, you first need an input element

<input type="file" id="files-list">
Copy the code

Next, use readAsDataURL to convert the file to a picture if it is read, or text if not. The corresponding transformation result is saved in result.

reader = new FileReader();
if (/image/.test(files[0].type)) {
    // Set the file type to image
    reader.readAsDataURL(files[0]);
    type = "image";
} else {
    // Other file type and specify encoding type
    reader.readAsText(files[0].'gb2312');
    type = "text";
}

Copy the code

After reading, the image is displayed with the IMG tag. If it is text, mount it on the page

reader.onload = function () {
    var html = "";
    switch (type) {
        case "image":
            html = "<img src=\"" + reader.result + "\" >";
            break;
        case "text":
            html = reader.result;
            console.log(html);
            break;
    }
    output.innerHTML = html;
};
Copy the code

The demo link

Read the pictures

Reading a CSV file

When reading with readAsText, you specify the encoding. Such as readAsText (files [0], 'gb2312);Copy the code

CSV import

Features of a CSV file: Each line is separated by a newline character, and each data item in each line is separated by a comma.Copy the code

You can already output CSV as text. Come down and get more data.

Using the features of CSV mentioned above, you can use JS to cycle through and get each item of data.

The demo link

Where, I converted the data in CVS and put it in the table element:

// Convert the read data to a table
function textToCsv(data) {
    var allRows = data.split(/\n/);
    var table = '<table>';
    for (var singleRow = 0; singleRow < allRows.length - 1; singleRow++) {
        if (singleRow === 0) {
            table += '<thead>';
            table += '<tr>';
        } else {
            table += '<tr>';
        }
        var rowCells = allRows[singleRow].split(', ');
        for (var rowCell = 0; rowCell < rowCells.length; rowCell++) {
            if (singleRow === 0) {
                // Table title
                table += '<th>';
                table += rowCells[rowCell];
                table += '</th>';
            } else {
                // Table contents
                table += '<td>';
                table += rowCells[rowCell];
                table += '</td>'; }}if (singleRow === 0) {
            table += '</tr>';
            table += '</thead>';
            table += '<tbody>';
        } else {
            table += '</tr>';
        }
    }
    table += '</tbody>';
    table += '</table>';
    console.log(table);
    document.getElementById('table').innerHTML = table;
}
Copy the code

The renderings are as follows:

Now that it’s all read out, download it?

For downloading operations, you can use the A tag. Let’s start with the related attributes:

Download: Specifies the file name for the download
Href: link for the file to download (local link required)

The previous example of the download is to use the above approach:

<a href=". / file/members. CSV" download="demo.csv">Download the test file</a>
Copy the code

Prepare the data. CSV file header data:

var head = [
    ['Name (Max. 10 words)'.'Mobile phone Number (required)'.'class'.'Birthday (e.g. 1989/08/08 or 1989-08-08)'.'Integral (integer)']].Copy the code

The main data of the CSV file:

var people = [{
    "name": "Wu Sangui"."phone": "18709237410"."level": "Gold"."birthday": "1989/8/5"."points": "100"
}, {
    "name": "Stallone"."phone": "18709237401"."level": "Bronze 2"."birthday": "1993/9/6"."points": "300"
}, {
    "name": O "super"."phone": "18883920287"."level": "Platinum"."birthday": "1993/9/3"."points": "500"
}];
Copy the code

The following is the data format conversion:

//. Push data into a large array
var p = people;
for (var i = 0; i < p.length; i++) {
    head.push([p[i].name, p[i].phone, p[i].level, p[i].birthday, p[i].points]);
}
// Add each array to a new array by concatenating it into a single line
var csvRows = [];
for (var j = 0; j < head.length; j++) {
    csvRows.push(head[j].join(', '))}//s4. Connect the new array with \n carriage return
var csvString = csvRows.join('\n'); 
Copy the code

Finally, download:

// Use the a tag to download
function saveAs(obj, fileName) { 
    var tmpa = document.createElement("a");
    tmpa.download = fileName || "Download CSV".;
    tmpa.href ='data:attachment/csv,' + encodeURI(obj); // Bind the A tag
    tmpa.click(); // Simulate click to download
    setTimeout(function () { // Delay release
        URL.revokeObjectURL(obj); RevokeObjectURL () releases the object URL
    }, 100);
}
Copy the code

Demo link renderings

json2csv

The following will use JS-xlsx to operate on the CSV file:

Excel file reading

XLSX related API:

The xlsx.utils.sheet_to_json method parses the table object and returns the corresponding JSON data
Xlsx.read () reads Excel data as a binary stream

In JavaScript, there are two functions for decoding and encoding base64 strings, respectively:

The atob() function decodes base-64 encoded string data.
The btoa() function creates a BASE-64 encoded ASCII string from the binary data “string”.

Steps:

ReadAsArrayBuffer converts read data into binary data;
Convert the data to base64 format via Btoa ();
It is then converted to JSON with xlsx.utils.sheet_to_JSON

var wb;// Read the finished data
 / / import
function importf(obj) {
    if(! obj.files) {return;
    }
    var f = obj.files[0];
    var reader = new FileReader();
    reader.readAsArrayBuffer(f);
    reader.onload = function (e) {
        var data = e.target.result;
        var wb =XLSX.read(btoa(fixedData(data)), { type: 'base64' });// Convert data to binary
        // Wb. SheetNames[0] is the name of the first Sheet to get in Sheets
        //wb.Sheets[Sheet name] get data from the first Sheet
        document.getElementById("excel").innerHTML = JSON.stringify(XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]));
    };

}
Copy the code

// File flow BinaryString
function fixedData(data) {
    let o = ' '
    let l = 0
    const w = 10240
    for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null.new Uint8Array(data.slice(l * w,
        l * w + w)))
    o += String.fromCharCode.apply(null.new Uint8Array(data.slice(l * w)))
    return o
}
Copy the code

Preview of effect drawing:

View the demo

Attached is a vue-element-admin source for Excel file reading: view the link

Excel File Export

Js-xlsx will still be used here, and FileSaver

 <button onclick="downloadExl(people)">export</button>
Copy the code

Data preparation

 var people = [{
    "name": "Wu Sangui"."phone": "18709237410"."level": "Gold"."birthday": "1989/8/5"."points": "100"
}, {
    "name": "Stallone"."phone": "18709237401"."level": "Bronze 2"."birthday": "1993/9/6"."points": "300"
}, {
    "name": O "super"."phone": "18883920287"."level": "Platinum"."birthday": "1993/9/3"."points": "500"
}];
Copy the code

Define a WB to hold the exported data

var wb = {
  SheetNames: ["Sheet1"]./ / title name
  Sheets: { }               // Save the table data
}
Copy the code

Xlsx.utils.json_to_sheet (people) : Converts JSON objects into worksheets; Assign non-Wb. Sheets[‘Sheet1’] to converted results

! Ref: indicates the output range of the table a1-zx: these correspond to the positions of cells in Excel;

Sheet1 Related parameters:

Key name	value
v	The original value
t	B Boolean, E error, N number, D date, S text

See the official documentation for more attributes

Specify the export format, in this case XLSX format

 const wopts = { bookType: 'xlsx'.bookSST: false.type: 'binary' };
// The data here is used to define the format type of the export
// const wopts = { bookType: 'csv', bookSST: false, type: 'binary' }; / / ods format
// const wopts = { bookType: 'ods', bookSST: false, type: 'binary' }; / / ods format
// const wopts = { bookType: 'xlsb', bookSST: false, type: 'binary' }; / / XLSB format
// const wopts = { bookType: 'fods', bookSST: false, type: 'binary' }; / / fods format
// const wopts = { bookType: 'biff2', bookSST: false, type: 'binary' }; / / XLS format
Copy the code

Set the table header to replace the worksheet a1-Z1 and so on with Chinese

// Converts the specified natural number to a base 26 representation. Mapping: [0-25] -> [a-z].
function getCharCol(n) {
    var temCol = ' ',
        s = ' ',
        m = 0
    while (n >= 0) {
        m = n % 26 + 1
        s = String.fromCharCode(m + 64) + s
        n = (n - m) / 26
    }
    return s
};
["ID"."Mobile phone Number"."Level" ,"Birthday"."Points"].forEach((v,i) = >{data[getCharCol(i)+1] = { t: "s".v: v ,w:v}; })Copy the code

Download and save,

Using the XLSX. Write (wb, wopts); Wb: binary data stream WOPTS: specified export format

Download and save this time without using the A tag. Using FileSaver. Js

saveAs(new Blob([s2ab(XLSX.write(wb, wopts))], {type: "application/octet-stream"}), "Js-xlsx file download example" + '. ' + wopts.bookType);
Copy the code

View the demo

New person first post, everyone do not spray!!

github

Reference article:

Understand DOMString, Document, FormData, Blob, File, ArrayBuffer data types — Zhang Xinxu
Operation of files and binary data — Ruan Yifeng

Let’s start with an example

CSV import

Now that it’s all read out, download it?

Excel file reading

Excel File Export

Related Posts

Why override the equals method must override the hashCode method

React Context

Front-end algorithm must brush problem series [76] | August more article challenge