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
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');
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);
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
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
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:
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); });
write method documentation and output options
Other excellent articles about JS xlsx
https://www.cnblogs.com/liuxi...quote
https://github.com/SheetJS/js...This article is reproduced from: https://www.cnblogs.com/vicky-li/p/11469100.html