js实现excel导出并附带样式demo保姆级

js xlsx [xlsx-js-style] 附带样式导出excel

目录:一、导出样式    二、实现代码    三、使用细节说明

---

一、导出样式

二、实现代码

1、npm install xlsx-js-style

“xlsx-js-style”: “^1.2.0”

2、实现的所有代码

import XLSXS from "xlsx-js-style";

function exportcc(){
	// STEP 1: Create a new workbook
    const wb = XLSXS.utils.book_new();
    //sheet工作簿标题
    const sheetName = "xlsx导出带样式";
    // STEP 2: Create data rows and styles
    let rowArray = [
      [{
        v: "xlsx导出带样式",
        t: "s",
        s: {
          font: {
            name: "Courier",
            sz: 24,
            bold: true, 
            color:'b8ddb0'
          },
          fill: { fgColor: { rgb: '9e9e9e' } } 
        }
      }],
      [{
          v: "Date",
          t: "s",
          s: {
            font: {
              name: "Courier"
            }
          }
        },
        {
          v: "Disburse Amount",
          t: "s",
          s: {
            font: {
              bold: true,
              color: {
                rgb: "FF0000"
              }
            }
          }
        },
        {
          v: "Disburse New Amount",
          t: "s",
          s: {
            fill: {
              fgColor: {
                rgb: "E9E9E9"
              }
            }
          }
        },
        {
          v: "line\nbreak",
          t: "s",
          s: {
            alignment: {
              wrapText: true
            }
          }
        },
      ]
    ];
    // STEP 3: Create worksheet with rows; Add worksheet to workbook
    const ws = XLSXS.utils.aoa_to_sheet(rowArray);
    XLSXS.utils.book_append_sheet(wb, ws, sheetName);
    
    let maxColumnNumber = 1; //默认最大列数
    rowArray.map(item=>item.length>maxColumnNumber?maxColumnNumber=item.length:'');
    //合并  #将第一行标题列合并
    let merges = ['A1:'+String.fromCharCode(64 + parseInt(maxColumnNumber))+'1'];
    let wsMerge = [];
    merges.map((item)=>{
      wsMerge.push(
        XLSXS.utils.decode_range(item)
      );
    })
    
    ws['!merges'] = wsMerge;
    console.log(ws);
    //边框样式
    let borderStyle = {
      top: {
        style: "thin",
        color: {
          rgb: "000000"
        }
      },
      bottom: {
        style: "thin",
        color: {
          rgb: "000000"
        }
      },
      left: {
        style: "thin",
        color: {
          rgb: "000000"
        }
      },
      right: {
        style: "thin",
        color: {
          rgb: "000000"
        }
      }
    }
    //添加外边框
    rowArray.map((item,index)=>{
        for(let i=1;i<=maxColumnNumber;i++){
          if(!ws[''+String.fromCharCode(64 + parseInt(i))+(index+1)]){
            ws[''+String.fromCharCode(64 + parseInt(i))+(index+1)] = {v:'',t:'s',s:{border:borderStyle}};
            continue ;
          }
          //边框
          ws[''+String.fromCharCode(64 + parseInt(i))+(index+1)].s.border = borderStyle;

          //字体居中
          ws[''+String.fromCharCode(64 + parseInt(i))+(index+1)].s.alignment = { vertical: 'center', horizontal: 'center' } 
        }
    })

    //添加列宽
    ws['!cols'] = ([{
      width: 40
    }, {
      width: 40
    }]);
    //添加行高
    ws['!rows'] = [{ 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }];
    // STEP 4: Write Excel file to browser  #导出
    XLSXS.writeFile(wb, sheetName+".xlsx");
}

三、使用细节说明

1、修改列宽

 //接受参数为 数组，数组对应从A开始往后内推
 ws['!cols'] = ([{
      width: 40
    }, {
      width: 40
    }]);

2、修改行高

//接受参数为 数组，数组对应从第一开始往后内推
ws['!rows'] = [{ 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }, { 'hpt': 40 }];

3、修改边框

let borderStyle = {
      top: {
        style: "thin",
        color: {
          rgb: "000000"
        }
      },
      bottom: {
        style: "thin",
        color: {
          rgb: "000000"
        }
      },
      left: {
        style: "thin",
        color: {
          rgb: "000000"
        }
      },
      right: {
        style: "thin",
        color: {
          rgb: "000000"
        }
      }
    }
ws['A1'].s.border = borderStyle;

4、字体居中

ws['B1'].s.alignment = { vertical: 'center', horizontal: 'center' }

5、合并

//将A1和A2合并,后面再次合并可以直接push
const wsMerge = [XLSXS.utils.decode_range('A1:A2')]
ws['!merges'] = wsMerge;

6、字体样式处理
请看依赖库redme.md

github依赖库:https://github.com/gitbrent/xlsx-js-style

说明与疑问：

参考依赖库
https://github.com/SheetJS/sheetjs
https://sheetjs.com/demo/table.html
xlsx-style