[sheetjs] JS xlsx module learning guide

brief introduction

SheetJS is one of the best choices for front-end operation of Excel and similar two-dimensional tables, and JS xlsx is its community version

JS xlsx focuses on data transformation and export, so it supports a considerable variety of data parsing and export It is not limited to supporting xlsx format

Supported import formats

Supported export formats

It can:

Parsing formatted data
Export formatted data
Use the m-server to manipulate data

Can run on:

Browser side
Node end

Browser side features

Browser only parsing data
Browser only data export

Node end features

Read / write file
Streaming read / write

This article tries to simplify and mainly discusses the workflow, basic concepts and usage of JS xlsx

concept

js xlsx provides an intermediate layer for operating data. It abstracts different types of files into the same js object, thus avoiding the complexity of operating different types of data

A series of abstract functions are provided around this object. This section mainly discusses the relationship between these data objects and Excel data

The difference between the browser side and the Node side lies only in how to import and export files. For data operations, the interfaces of both sides are the same

lead into

The introduction of JS xlsx is very simple. The browser side introduction can be the most basic form of script tags

<script lang="javascript" src="dist/xlsx.full.min.js"></script>

On the node side, use npm to install the following modules:

npm install xlsx --save

The Node is introduced as follows:

const xlsx = require('xlsx');

Detailed document address

Correspondence

In this table, I listed the relationship between Excel and JS xlsx:

Excel NOUN	Abstract types in JS xlsx
workbook	workBook
Worksheet	Sheets
Excel reference style (cell address)	cellAddress
Cell	cell

With this basic correspondence, we can easily understand the subsequent operations. For example, in the process of using Excel, the process of obtaining a data is as follows:

Open workbook
Open a worksheet
Select a range or cell
Operate on data
Save (save as)

Then get the contents of a cell in JS xlsx The operation is as follows:

// Don't care about us yet workbook Where does the object come from
var first_sheet_name = workbook.SheetNames[0]; // Get the sheet name in the workbook
var address_of_cell = 'A1'; // Provide a reference style(Cell subscript)

var worksheet = workbook.Sheets[first_sheet_name]; // Get the corresponding worksheet object

var desired_cell = worksheet[address_of_cell]; // Get the corresponding cell object

var desired_value = (desired_cell ? desired_cell.v : undefined);// Get the data in the corresponding cell

data format

Picture: data structure of Workbook

Once our Excel file is parsed, all contents in the Excel table will be parsed into the above object And the whole process is completed synchronously

So we can use keys to directly obtain data. In the above example, we use keys to obtain data layer by layer

There are two common keys in the figure above:

SheetNames saves the names of all worksheets as a string array
The contents under Sheets are all worksheet objects, and the key name is the name contained in SheetNames

The data units of Excel are sorted from small to large as follows:

Cell
Worksheet
workbook

Format cells

There are many formats for cells in Excel, and JS xlsx will parse them into the corresponding JavaScript format

Common formats are as follows:

key	describe
v	Source data (unprocessed data)
w	Formatted text (if it can be formatted)
t	Cell type (see the table below for specific types)
r	Decoded rich text (if it can be decoded)
h	Render rich text in HTML format (if it can be decoded)
c	Cell comment
z	Numeric values formatted as strings (if required)

Full format link.

Cell data format after parsing:

This data is saved in Excel at A1, with text type and cell content of xm

cell address

JS xlsx uses two ways to describe a cell range in an operation

One is Cell address object (Cell address object) the other is the cell range

The address object format is as follows:

const start = { c: 0, r: 0 };
const end = { c: 1, r: 1 };

The address range corresponding to the above address object is as follows:

const range = 'A1:B2';

It is not difficult for us to find the corresponding relationship between the two:

The address object describes a range from the start coordinate (starting from 0) to the end coordinate
The address range is the reference style in Excel

Note: these two concepts will be used in worksheet reading and writing

API

The interfaces provided by JS xlsx are very clear and can be divided into two types:

Functions provided by the xlsx object itself
- Parse data
- Export data
utils tool class
- Add data to a data table object
- Convert 2D arrays and formatting objects or HTML to worksheet objects
- Convert workbook to another data format
- Transcoding and decoding between rows, columns, and ranges
- Workbook actions
- Cell operation

Read data and parse

Here is a simple Node example (Node10+):

const xlxs = require('xlsx');
const {readFile} = require('fs').promises;

(async function (params) {
    
    // get data
    const excelBuffer = await readFile('./books.xlsx');
    
    // Parse data
    const result = xlxs.read(excelBuffer,{
        type:'buffer',
        cellHTML:false,
    });
    
    console.log('TCL: result', result);

})();

You can also use utils Book_ New() creates a new workbook object:

const
    xlsx = require('xlsx'),
    { utils } = xlsx;

const workBook= utils.book_new(); // Create a workbook

Then use the following tools to manipulate workbook objects:

// Follow the example above

const ws_data = [
  [ "S", "h", "e", "e", "t", "J", "S" ],
  [  1 ,  2 ,  3 ,  4 ,  5 ]
];

const workSheet = XLSX.utils.aoa_to_sheet(ws_data);// Create a worksheet object using a two-dimensional array

utils.book_append_sheet(workBook,workSheet,'Worksheet name');// Append a worksheet to the workbook

console.log(workBook);

Detailed parsing document

Detailed parsing options

Data filling

A worksheet is a place where data is actually stored. In most cases, our operations are on worksheet objects

JS xlsx provides a variety of ways to operate data. Here are the most common operations:

Create worksheets from existing data structures
- 2D array as data source
- JSON as data source
Modify sheet data
- 2D array as data source
- JSON as data source

Create sheet

const workSheet = utils.aoa_to_sheet([[1,2,3,new Date()],[1,2,,4]],{
    sheetStubs:false,
    cellStyles:false,
    cellDates:true // Resolve to native time
});

console.log(workSheet);

The relationship between two-dimensional arrays is very easy to understand. Each array in the array represents a row

Picture: 2D array results

const workSheet = 
utils.json_to_sheet([
{ 'Column 1': 1, 'Column 2': 2, 'Column 3': 3 },
{ 'Column 1': 4, 'Column 2': 5, 'Column 3': 6 }
],{
    header:['Column 1','Column 2','Column 3'],
    skipHeader:true// Skip header line above
})

console.log(workSheet);

Picture: JSON effect

Detailed document address

Modify datasheet data

const workSheet = 
utils.json_to_sheet([
{ 'Column 1': 1, 'Column 2': 2, 'Column 3': 3 },
{ 'Column 1': 4, 'Column 2': 5, 'Column 3': 6 }
],{
    header:['Column 1','Column 2','Column 3'],
    skipHeader:true// Skip header line above
})

utils.sheet_add_aoa(workSheet,[
    [7,8,9],
    ['A','B','C']
],{
    origin:'A1' // from A1 Start adding content
});

console.log(workSheet);

Picture: 2D array results

const workSheet = 
utils.json_to_sheet([
{ 'Column 1': 1, 'Column 2': 2, 'Column 3': 3 },
{ 'Column 1': 4, 'Column 2': 5, 'Column 3': 6 }
],{
    header:['Column 1','Column 2','Column 3'],
    skipHeader:true// Skip header line above
})

utils.sheet_add_json(workSheet,[
    { 'Column 1': 7, 'Column 2': 8, 'Column 3': 9 },
    { 'Column 1': 'A', 'Column 2': 'B', 'Column 3': 'C' }
],{
    origin:'A1',// from A1 Start adding content
    header: ['Column 1', 'Column 2', 'Column 3'],
    skipHeader: true// Skip header line above
});

console.log(workSheet);

Picture: JSON effect

Detailed document address

Data export

Data export is divided into two parts:

Use tool classes to convert workbook objects to other data structures
Call the write or writeFile method

Convert to other data structures

The detailed use cases are not provided here. The formats that can be converted are as follows:

Detailed document address

output file

Here is a simple Node example (Node10+):

const
    xlsx = require('xlsx'),
    { utils } = xlsx;

const {writeFile} =require('fs').promises;

const workBook= utils.book_new();
const workSheet = utils.aoa_to_sheet([[1,2,3]],{
    cellDates:true,
});

// Append sheet to Workbook
utils.book_append_sheet(workBook, workSheet,'helloWorld');

// Browser side and node Shared API,actually node Can be used directly xlsx.writeFile To write files,But the browser does not have this API
const result = xlsx.write(workBook, {
    bookType: 'xlsx', // Output file type
    type: 'buffer', // Output data type
    compression:true // open zip compress
});

// write file
writeFile('./hello.xlsx',result)
.catch((error)=>{
    console.log(error);
});