基于luckysheet在线表格的excel下载功能开发(代码片段)

let allSheetData = luckysheet.getluckysheetfile();
let sheet1 = allSheetData[0];

let downOriginData = sheet1.data;  

// 该方法会返回一个数组结构，其中包含了我们所创建的所有的sheet数据
// allSheetData = [sheet1,sheet2,sheet3]

// 每个sheet的数据结果如下

    "name": "sheet1",
    "color": "",
    "chart": [],
    "status": 0,
    "order": 0,
    "celldata": [],
　　 "data":[],
    "row":90,
    "column":100,
    "index": 0,
    "visibledatarow": [],
    "visibledatacolumn": [],
    "rowsplit": [],
    "ch_width": 4629,
    "rh_height": 1681,
    "luckysheet_select_save": ,
    "luckysheet_selection_range": ,
    "scrollLeft": 0,
    "scrollTop": 0,
    "load": "1",
    "config": 
        "columlen": ,
        "rowhidden": 
    
    ,
    "pivotTable": ,
    "isPivotTable": true,
    "calcChain": [],
    "filter":key1:value1, key2:value2,
    "filter_select": 

let arr = [];  // 所有的单元格数据组成的二维数组
let bgConfig = ;
let percentageReg = /%$/;
let cellValue = null;
// 获取单元格的背景色
function setBackground(row, col, bg) 
    var colA = luckysheet.luckysheetchatatABC(col);
    var key = colA + (row + 1);
    bgConfig[key] = bg.replace(/#?/, ‘‘);


// 判断值类型是否为百分比 %
function isPercentage(value) 
    return percentageReg.test(value.m) && value.ct && value.ct.t === ‘n‘


// 获取二维数组
for (let row = 0; row < downOriginData.length; row++) 
    let arrRow = [];
    for (let col = 0; col < downOriginData[row].length; col++) 
        if (cellValue = downOriginData[row][col]) 
            // 处理单元格的背景颜色
            if (cellValue.bg) 
                setBackground(row, col, cellValue.bg)
            
            if (cellValue.ct != null && cellValue.ct.t == ‘d‘) 
                //  d为时间格式  2019-01-01   或者2019-01-01 10:10:10
                arrRow.push(new Date(cellValue.m.replace(/-/g, ‘/‘))) //兼容IE
             else if (cellValue.m && isPercentage(cellValue)) 
                //百分比问题
                arrRow.push(cellValue.m)
             else 
                arrRow.push(cellValue.v)
            
        
    
    arr.push(arrRow)

let opts = 
    dateNF: ‘m/d/yy h:mm‘,
    cellDates: true,
    cellStyles: true

let ws = XLSX.utils.aoa_to_sheet(arr, opts)

 1 let reg = /[u4e00-u9fa5]/g;
 2 for (let key in ws) 
 3     let item = ws[key]
 4     if (item.t === ‘d‘) 
 5         if (item.w) 
 6             //时间格式的设置
 7             let arr = item.w.split(‘ ‘)
 8             if (arr[1] && arr[1] == ‘0:00‘) 
 9                 ws[key].z = ‘m/d/yy‘
10              else 
11                 item.z = ‘yyyy/m/d h:mm:ss‘
12             
13         
14      else if (item.t === ‘s‘) 
15         //百分比设置格式
16         if (item.v && !item.v.match(reg) && item.v.indexOf(‘%‘) > -1) 
17             item.t = ‘n‘
18             item.z = ‘0.00%‘
19             item.v = Number.parseFloat(item.v) / 100
20         
21         else if (item.v && item.v.match(reg)) 
22             //含有中文的设置居中样式
23             item[‘s‘] = 
24                 alignment:  vertical: ‘center‘, horizontal: ‘center‘ 
25             
26         
27     
28     // 设置单元格样式
29     if (bgConfig[key]) 
30         ws[key][‘s‘] = 
31             alignment:  vertical: ‘center‘, horizontal: ‘center‘ ,
32             fill:  bgColor:  indexed: 32 , fgColor:  rgb: bgConfig[key]  ,
33             border: 
34                 top:  style: ‘thin‘, color:  rgb: ‘999999‘  ,
35                 bottom:  style: ‘thin‘, color:  rgb: ‘999999‘  ,
36                 left:  style: ‘thin‘, color:  rgb: ‘999999‘  ,
37                 right:  style: ‘thin‘, color:  rgb: ‘999999‘  
38             
39         
40     
41 

    let name = ‘sheet1‘;
        let tmpWB = 
          SheetNames: [name], //保存的表标题
          Sheets: 
            [name]: Object.assign(,ws)//内容 
          
        

let mergeConfig = sheet1.config.merge
let mergeArr = [];
if (JSON.stringify(mergeConfig) !== ‘‘) 
    mergeArr = handleMergeData(mergeConfig)

//处理合并单元格config数据
function handleMergeData(origin) 

　　let result = []
    if (origin instanceof Object) 
        var r = "r",
            c = "c",
            cs = "cs",
            rs = "rs";
        for (var key in origin) 
            var startR = origin[key][r];
            var endR = origin[key][r];
            var startC = origin[key][c];
            var endC = origin[key][c];

            // 如果只占一行 为1 如果占两行 为2
            if (origin[key][cs] > 0) 
                endC = startC + (origin[key][cs] - 1);
            
            if (origin[key][rs] > 0) 
                endR = startR + (origin[key][rs] - 1);
            
            // s为合并单元格的开始坐标  e为结束坐标
            var obj =  s:  "r": startR, "c": startC , e:  "r": endR, "c": endC  
            result.push(obj)
        
    
　　return result

    
let fileName = ‘test‘;
// sheetjs js-xlsx 的方法 ，不能设置单元格格式
 XLSX.writeFile(tmpWB,fileName + ".xlsx");

function s2ab(s) 
    if (typeof ArrayBuffer !== ‘undefined‘) 
        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;
     else 
        var buf = new Array(s.length);
        for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF;
        return buf;
    

function saveAs(obj, fileName) 
    var tmpa = document.createElement("a");
    tmpa.download = fileName || "download";
    tmpa.href = URL.createObjectURL(obj);
    tmpa.click();
    setTimeout(function () 
        URL.revokeObjectURL(obj);
    , 100);


ws = new Blob(
    [
        s2ab(
            S_XLSX.write(tmpWB,  bookType: ‘xlsx‘, bookSST: false, type: ‘binary‘ )//这里的数据是用来定义导出的格式类型
        )
    ]
)
saveAs(ws, fileName + ‘.xlsx‘)