First, look at the diagram after the implementation

Second, the technology

This table is mainly implemented in XLSX-style……

Introduction to three,

Since the documents on NPM and Git are in English, you don’t want to see them again after you’ve seen them once, so make a note here.

Xlsx-style is a pure javascript implementation of the NPM package for reading and generating Excel. Xlsx-style comes from SheetJs/ XLSX and adds many features to its non-paid version, the main one being excel styling.

PS: SheetJs/ XLSX paid version, SheetJs/ XLSX paid version

If you have any questions about using it, you can check it out or submit it.

1. Supported file types

  1. File types that can be read

    • Excel 2007+ XML Formats (XLSX/XLSM)
    • Excel 2007+ Binary Format (XLSB)
    • Excel 2003-2004 XML Format (XML “SpreadsheetML”)
    • Excel 97-2004 (XLS BIFF8)
    • 5.0/95 (Excel XLS BIFF5)
    • OpenDocument Spreadsheet (ODS)
  2. Supported file types

    • XLSX
    • CSV (and general DSV)
    • JSON and JS objects (various styles)

2, compatible

Es5-enabled browsers, if older, need to introduce polyfill

3, installation,

NPM install xlsx-style –save

Use in the browser: SRC =”dist/xlsx.full.min.js”> // include dependency packages

CDN introduced directly:

4. Read files

Used in node

if(typeof require! = ='undefined') 
	XLSX = require('xlsx')

var workbook = XLSX.readFile('test.xlsx');
Copy the code

Ajax for excel

var url = "test_files/formula_stress_test_ajax.xlsx";
var oReq = new XMLHttpRequest();"GET", url, true);
oReq.responseType = "arraybuffer";

oReq.onload = function(e) {
  var arraybuffer = oReq.response;

  var data = new Uint8Array(arraybuffer);
  var arr = new Array(a);for(var i = 0; i ! = data.length; ++i) arr[i] =String.fromCharCode(data[i]);
  var bstr = arr.join("");

  var workbook =, {type:"binary"});
  /* DO SOMETHING WITH workbook HERE */

Copy the code

H5 Drag upload

function handleDrop(e) {
  var files = e.dataTransfer.files;
  var i, f;
  for (i = 0, f = files[i]; i ! = files.length; ++i) {var reader = new FileReader();
    var name =;
    reader.onload = function(e) {
      var data =;

      /* if binary string, read with type 'binary' */
      var workbook =, {type: 'binary'});
      /* DO SOMETHING WITH workbook HERE */
drop_dom_element.addEventListener('drop', handleDrop, false);
Copy the code

H5 input to upload

function handleFile(e) {
  var files =;
  var i, f;
  for (i = 0, f = files[i]; i ! = files.length; ++i) {var reader = new FileReader();
    var name =;
    reader.onload = function(e) {
      var data =;

      var workbook =, {type: 'binary'});

      /* DO SOMETHING WITH workbook HERE */
input_dom_element.addEventListener('change', handleFile, false);
Copy the code

⚠️ xlsx. read(data, read_opts) is used to parse a piece of data. ReadFile (filename, read_opts) parses a piece of data from a file.

4.1 read_opts

Read_opts is an option to read Excel. It has the following optional configurations:

key describe The default value
cellFormula Save the formula to the.f field true
cellHTML Parse the rich text and save the HTML to the.h field true
cellNF Saves a numeric format string to the.z field false
cellStyles Save the style/theme information to the.s field false
cellDates Store the date as type D (default type is n) false
sheetStubs Creates a cell object for the stub cell false
sheetRows If >0, the first one is readsheetRows 0
bookDeps If true, parse the computation chain false
bookFiles If true, the original file is added to the Book Object false
bookProps If true, only the book metadata is parsed to get it false
bookSheets If true, only resolution is sufficient to get the worksheet name false
bookVBA If true, vbaproject. bin is exposed tovbarawfield false
password If it is defined and the file is encrypted, save the password

There are some points to note here that are not easy to translate, to avoid ambiguity, I put the original text below

5. Write to the file

Used in node

if(typeof require! = ='undefined') 
	XLSX = require('xlsx')
XLSX.writeFile(workbook, 'out.xlsx');
Copy the code

Use filesaver.js to write binary

/* bookType can be 'xlsx' or 'xlsm' or 'xlsb' */
var wopts = { bookType:'xlsx'.bookSST:false.type:'binary' };

var wbout = XLSX.write(workbook,wopts);

function s2ab(s) {
  var buf = new ArrayBuffer(s.length);
  var view = new Uint8Array(buf);
  for (var i=0; i! =s.length; ++i) view[i] = s.charCodeAt(i) &0xFF;
  return buf;

/* The saveAs function downloads a file on the local machine */
saveAs(new Blob([s2ab(wbout)],{type:""}), "test.xlsx")
Copy the code

⚠️ xlsx. write(data, read_opts) Is used to write a workbook.xlsx. writeFile(filename, read_opts) will write the workbook to the file.

6. Cell objects

In Excel, each cell can be treated as an object, and the user can fill in raw data, set text types, background colors, borders, and even draw, use rich text, mathematical formulas, and specify associations with other cells.

The address of a cell can be represented by {c: c, r: r}, where c represents the column and r represents the row

If we choose cell B5, what do we say?

{c:1, r:4} // First column fourth row

This is because columns and rows are counted from 0 by default, whereas A, B, C, D… Z… Letters occupy line 0, 1, 2, 3, 4…. It occupies the 0th column

6.1 Cell properties

attribute describe
v The original data
w Formatted text
t Cell text type: b Boolean, n Number, e Error, s String, d Date
f The formula
r Rich text encoding
h Rich text HTML
c A comment associated with a cell
z Numeric format string associated with the cell (deprecated)
l Cell hyperlink,.target holds link,.tooltip is tooltip
s Cell specific style Settings

⚠️ : The default export (such as CSV export) reads w. If you want to change the value, delete cell.w or cell.w=undefined before exporting

6.2 Cell styles

In the previous section, we said that cell.s is used to set cell styles.

In XLSX-style, the cell style has five top-level properties: fill, font, numFmt, alignment, and border.

Top attributes Child attributes describe Type or optional value The default value
fill patternType Fill mode “solid” or “none”
fgColor The foreground COLOR_SPEC
bgColor The background color COLOR_SPEC { indexed: 64}
font name The name of the font string “Calibri”
sz The font size number 12
color The font color COLOR_SPEC
bold bold boolean
underline The underline boolean
italic italics boolean
strike At side boolean
outline outline boolean
shadow shadow boolean
vertAlign The vertical alignment boolean
numFmt Number formatting “0” // Integer index in built-in format

“0.00%” // matches the string in the built-in format, see below

“0.0%” // A string formatted in a custom format

“0.00%; \ \ (0.00%); \ -; @” // Escape special characters when formatting

“M /dd/yy” // Format the date
alignment vertical The vertical alignment “Bottom”, “center”, “top”
horizontal Horizontal alignment “Left”, “center”, “right”
wrapText A newline boolean
readingOrder The text direction 1, 2 // for right-to-left
textRotation rotating Number from 0 to 180 or 255 0
border top On the border { style: BORDER_STYLE, color: COLOR_SPEC }
bottom Under the frame { style: BORDER_STYLE, color: COLOR_SPEC }
left The left margin { style: BORDER_STYLE, color: COLOR_SPEC }
right Right margin { style: BORDER_STYLE, color: COLOR_SPEC }
diagonal The diagonal { style: BORDER_STYLE, color: COLOR_SPEC }
diagonalUp On the diagonal boolean
diagonalDown The diagonal boolean

COLOR_SPEC is used to implement the font, border, and fill color. It can be written in one of the following ways:

  • {auto: 1} // Specify the automatic value
  • {RGB: “FFFFAA00”} // Specify the hex ARGB value
  • {theme: “1”, tint: “-0.25”} {theme: “1”, tint: “-0.25”} {theme: “1”, tint: “-0.25”}
  • {indexed: 64} // fill. BgColor default

Border_style specifies the border style. There are many border styles in Excel. The optional values are:

  • thin
  • medium
  • thick
  • dotted
  • hair
  • dashed
  • mediumDashed
  • dashDot
  • mediumDashDot
  • dashDotDot
  • mediumDashDotDot
  • slantDashDot
6.2.3 numFmt built-in options

The code has built-in formatting options that the user can select by index or value

var table_fmt = {
	0:  'General'.1:  '0'.2:  '0.00'.3:  '#, # # 0'.4:  '#, # # 0.00'.9:  '0%'.10: '0.00%'.11: '0.00 e+00'.12: '#? /? '.13: '#?? /?? '.14: 'm/d/yy'.15: 'd-mmm-yy'.16: 'd-mmm'.17: 'mmm-yy'.18: 'h:mm AM/PM'.19: 'h:mm:ss AM/PM'.20: 'h:mm'.21: 'h:mm:ss'.22: 'm/d/yy h:mm'.37: '#, # # 0; (#, # # 0) '.38: '#, # # 0; [Red](#,##0)'.39: '#, # # 0.00; (# # # 0.00), '.40: '#, # # 0.00; [Red] (#, # # 0.00) '.45: 'mm:ss'.46: '[h]:mm:ss'.47: 'mmss.0'.48: '# # 0.0 e+0'.49: The '@'.56: 'M/PM' hh 'm' mm 'm' SS 's'.65535: 'General'
Copy the code

7. Worksheet object

The Worksheet object refers to the current Excel table and contains some Settings for the current table, such as merge cells, render area, column width, print, and so on.

All properties of the Worksheet object must start with! At the beginning

key describe The sample
! ref Excel’s render area “A1: E12”
! cols Set the column width [{hpx: 40}]
! rows Set the line height [{hpx: 40}]
! merges Merge cells {s:{c:0, r:2}, e:{c:1, r:6}}
! printHeader Lines to repeat when printing, such as the table header [1:1] repeat the first line

Headers, footers, and pagings do not see relevant information on the document and may not be supported at this time.

If there is any follow-up, I will update in time

Fourth, the demo

If xLSX-style on Github does not support line height setting, or you need demo code, please download here.