introduce

Sheetjs is a very convenient just pure JavaScript can read and export Excel js library making address: https://github.com/sheetjs/sheetjs

Reading excel

The table is as follows

Excel is read primarily through the xlsx.read method, which returns an object called WorkBook

An important parameter in the configuration object ParsingOptions is type, which determines how to read

Go straight to code

import * as XLSX from 'xlsx'; ; (function (doc) {
    function readExcel(e) {
        return new Promise((resolve) = > {
            const files = e.target.files;
            const f = files[0];
            const reader = new FileReader();
            reader.onload = function (e) {
                const data = new Uint8Array(e.target.result);
                const workbook = XLSX.read(data, {type: 'array'});
                resolve(workbook);
            };
            reader.readAsArrayBuffer(f);
        })
    }
    doc.querySelector('#file_input').addEventListener('change'.function (e) {
        readExcel(e).then(res= > {
            console.log(res); })})}) (document)



Copy the code

Let’s print the workbook and look at it

Convert the table contents into the data format we want

For data conversion, the plugin has integrated the utility class xlsx.utils, which uses several methods:

XLSX.utils.sheet_to_txt // Generate plain text format
XLSX.utils.sheet_to_html // Generate HTML format
XLSX.utils.sheet_to_json // Output in JSON format
XLSX.utils.sheet_to_csv // Generate CSV format
Copy the code

The usage is as follows:

const sheets = workbook.Sheets;
for (const key in sheets) {
    if (sheets.hasOwnProperty(key)) {
        console.log(XLSX.utils.sheet_to_json(sheets[key], {range: "A2:C6"})); // Specify the range of cells for the transformation}}Copy the code

The print result is as follows:

Export the data to Excel

The utility class functions provided by the official can convert a variety of data into a sheet, including:

aoa_to_sheet // Convert a two-dimensional array to sheet
table_to_sheet // Convert a table to a sheet
json_to_sheet // Convert an array of objects to sheet
Copy the code

Json_to_sheet is used as an example:

  1. First convert the data to sheet
function generateSheet() {
    const info_json = [
        {'name': 'Joe'.'gender': 'male'.'address': 'Beijing'},
        {'name': 'bill'.'gender': 'male'.'address': 'Shanghai'},
        {'name': 'the king 2'.'gender': 'male'.'address': 'the changsha'},]const sheet = XLSX.utils.json_to_sheet(info_json);
    return sheet;
}
Copy the code
  1. The xlsx. writeFile method downloads the file to the local PC
 function download(name) {
    const sheetName = name || 'sheet1';
    const workbook = {
        SheetNames: [sheetName],
        Sheets: {}}; workbook.Sheets[sheetName] = generateSheet(); XLSX.writeFile(workbook,'test.xlsx');
}
Copy the code
  1. Click the button to trigger the download operation
doc.querySelector('#download').addEventListener('click'.function () {
    download()
})
Copy the code

Exported content:

That’s a simple use of SheetJS. For more, visit SheetJS