js export xlsx with style
2018-11-29 本文已影响0人
静听你的微笑
基于react，xlsx, xlsx-style实现export xlsx文件，可以设置单元格合并，border，背景色等。
https://github.com/protobi/js-xlsx
https://github.com/SheetJS/js-xlsx/
- 在webpack.js 中设置： 解决fs编译问题
node: {
fs: 'empty',
},
- xlsx-style中dist/cpexcel.js中的部分代码注释掉，具体代码如下
// if(typeof cptable === "undefined") {
// if(typeof require !== "undefined"){
// var cpt = require('./cpt' + 'able');
// if (typeof module !== 'undefined' && module.exports) module.exports = factory(cpt);
// else root.cptable = factory(cpt);
// } else throw new Error("cptable not found");
// } else cptable = factory(cptable);
- main.js
import React, { Component } from 'react';
import PropTypes from 'prop-types'
import XLSX from 'xlsx-style'
const headerConfig = [
[{
name: '1111',
colspan: 3,
C: 0
},{
name: '2222',
rowspan: 2,
C: 3
},
...],[{
name: '3333',
colspan: 3,
C: 0
},{
name: '4444',
C: 4
},
...],
...
];
let dataConfig = ['element1','element1',...];
// cell style config
let borderStyle = {
top: {style: "thin", color: "000000"},
bottom: {style: "thin", color: "000000"},
left: {style: "thin", color: "000000"},
right: {style: "thin", color: "000000"}
};
// workbook
let ws = {
'!merges': []
};
let range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }};
function sheet_from_array_of_arrays(headerConfig, data) {
let cellWidth = [{wch:10},{wch:10},{wch:10},{wch:15},{wch:15},{wch:25},{wch:50},{wch:20},{wch:60},{wch:10},
{wch:40},{wch:10},{wch:15},{wch:15},{wch:10},{wch:20},{wch:30},{wch:20},{wch:30},{wch:10},{wch:40},{wch:20},{wch:10},{wch:10},{wch:70}];
let titleStyle = {
fill:{
fgColor: { rgb: "B22222" }
},
alignment: {
vertical: "center"
},
font: {
bold: true,
name: "Arial",
sz: "12",
color: { rgb: "FFFFFF" }
}
};
ws['!merges'].push({
s: { c: 0, r: 0 },
e: { c: 22, r: 4 }
});
setTitle(' ERISA Client Validation Form',titleStyle,5,0,2,23); // set title in workbook
setHeader(headerConfig,7);
addData(data,9,0,false); // add data in workbook
addData(Q6Options,0,23,true); //add Q6_options in workbook
ws['!cols'] = cellWidth; // set cell width
if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
}
function Workbook() {
if(!(this instanceof Workbook)) return new Workbook();
this.SheetNames = [];
this.Sheets = {};
}
function datenum(v, date1904) {
if(date1904) v+=1462;
let epoch = Date.parse(v);
return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}
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;
}
function setTitle(title,style,R,C,rowspan,colspan){
if(range.s.r > R) range.s.r = R;
if(range.s.c > C) range.s.c = C;
if(range.e.r < R) range.e.r = R;
if(range.e.c < C) range.e.c = C;
var cell = {v: title };
var cell_ref = XLSX.utils.encode_cell({c:C,r:R});
cell.t = 's';
cell.s = style;
ws[cell_ref] = cell;
ws['!merges'].push({
s: { c: C, r: R },
e: { c: C+colspan-1, r: R+rowspan-1 }
});
}
function setHeader(headerConfig,R){
headerConfig.map(item => {
let C = 0;
item.map(itm => {
if(range.s.r > R) range.s.r = R;
if(range.s.c > C) range.s.c = C;
if(range.e.r < R) range.e.r = R;
if(range.e.c < C) range.e.c = C;
let cell = {v: itm.name };
let cell_ref = XLSX.utils.encode_cell({c:itm.C,r:R});
cell.t = 's';
cell.s = {
alignment: {
wrapText: true,
vertical: "center",
horizontal: "center"
},
border: borderStyle,
font:{
bold:true,
name: "Arial",
sz: "10"
}
};
ws[cell_ref] = cell;
let currentC = C;
if(itm.colspan){
C += itm.colspan;
setMergeCellBorder("C",R,currentC,itm.colspan);
ws['!merges'].push({
s: { c: currentC, r: R },
e: { c: C - 1, r: R }
});
}else{
C++;
}
if(itm.rowspan){
setMergeCellBorder("R",R,currentC,itm.rowspan);
ws['!merges'].push({
s: { c: currentC, r: R },
e: { c: currentC, r: R + itm.rowspan - 1 }
});
}
});
R++;
});
}
function addData(data, startR, startC,wrapText){
let R = startR;
data.map(item => {
let C = startC;
item.map(itm => {
if(range.s.r > R) range.s.r = R;
if(range.s.c > C) range.s.c = C;
if(range.e.r < R) range.e.r = R;
if(range.e.c < C) range.e.c = C;
var cell = {v: itm };
var cell_ref = XLSX.utils.encode_cell({c:C,r:R});
cell.t = 's';
let borderStyle = {
bottom: {style: "thin", color: "000000"}
};
borderStyle.top = R === startR ? {style: "medium", color: "000000"} : {style: "thin", color: "000000"};
if([0,3,4,5,6,8,10,16,20,21].indexOf(C) >=0){
borderStyle.left = {style: "medium", color: "000000"};
borderStyle.right = {style: "thin", color: "000000"};
}else if(C === 22){
borderStyle.left = {style: "medium", color: "000000"};
borderStyle.right = {style: "medium", color: "000000"};
}else{
borderStyle.left = {style: "thin", color: "000000"};
borderStyle.right = {style: "thin", color: "000000"};
}
cell.s={
alignment: {
vertical: "center",
horizontal: "left",
wrapText: wrapText
},
border: borderStyle,
font: {
name: "Arial",
sz: "10"
}
};
ws[cell_ref] = cell;
C++;
});
R++;
});
}
function setMergeCellBorder(type,R,C,n){
let s = type === "C" ? C : R;
for(let i = s+1; i < s+n; i++){
let cell_ref_merge = type === "C" ? XLSX.utils.encode_cell({c:i,r:R}) : XLSX.utils.encode_cell({c:C,r:i});
let cell_merge = {v: ""};
cell_merge.t = 's';
cell_merge.s={
border: borderStyle
};
ws[cell_ref_merge] = cell_merge;
}
}
export default function save(data,filename){
let rows = preprocessData(data,dataConfig);
var ws_name = "ERISA Client Validation";
var wb = new Workbook(), ws = sheet_from_array_of_arrays(headerConfig,rows);
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, {bookType:'xlsx', bookSST:true, type: 'binary'});
var excelBlob = new Blob([s2ab(wbout)],{type:"application/octet-stream"});
var Sys = _browerVersion();
if(Sys.isIE){
navigator.msSaveBlob(excelBlob,filename + '.xlsx');
}else{
var oA = document.createElement('a');
oA.href = URL.createObjectURL(excelBlob);
oA.download = filename + '.xlsx';
oA.click();
}
}
function preprocessData(data,dataConfig){
let rows = [];
data.map(item => {
let row = [];
dataConfig.map(itm => {
let value = item[itm] || "";
row.push(value);
});
rows.push(row);
});
return rows;
}
function _browerVersion(){
var Sys = {};
//judge the brower version
var userAgent = navigator.userAgent;
console.log(userAgent);
var isOpera = userAgent.indexOf("Opera") > -1; //whether Opera
var isIE=window.ActiveXObject || "ActiveXObject" in window
// var isEdge = userAgent.indexOf("Windows NT 6.1; Trident/7.0;") > -1 && !isIE; //whether IE的Edge浏览器
var isEdge = userAgent.indexOf("Edge") > -1; //whether IE Edge
var isFF = userAgent.indexOf("Firefox") > -1; //whether Firefox
var isSafari = userAgent.indexOf("Safari") > -1 && userAgent.indexOf("Chrome") == -1; //whether Safari
var isChrome = userAgent.indexOf("Chrome") > -1 && userAgent.indexOf("Safari") > -1&&!isEdge; //whether Chrome
if(isChrome) Sys.isChrome = true;
if(isIE) Sys.isIE = true;
return Sys;
}
