JavaScript export to Excel, set fonts, column widths, row heights, ways to do it, merge cells and other effects | August more text challenges

First, look at the diagram after the implementation

Second, the technology

This table is mainly implemented in XLSX-style

www.npmjs.com/package/xls… Github.com/protobi/js-…

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: http://cdnjs.com/libraries/xlsx

4. Read files

Used in node

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

var workbook = XLSX.readFile('test.xlsx');
/* DO SOMETHING WITH workbook HERE */
Copy the code

Ajax for excel

var url = "test_files/formula_stress_test_ajax.xlsx";
var oReq = new XMLHttpRequest();

oReq.open("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 = XLSX.read(bstr, {type:"binary"});
  /* DO SOMETHING WITH workbook HERE */
}

oReq.send();
Copy the code

H5 Drag upload

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

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

H5 input to upload

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

      var workbook = XLSX.read(data, {type: 'binary'});

      /* DO SOMETHING WITH workbook HERE */
    };
    reader.readAsBinaryString(f);
  }
}
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:

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

⚠️ : 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.

6.2.1 COLOR_SPEC

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

6.2.2 BORDER_STYLE

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

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.