精华内容
下载资源
问答
  • // JavaScript Document //调用方法 // var test=new PageToExcel("data",0,255,"测试.xls");//table id , 第几行开始,最后一行...// test.CreateExcel(false); // test.Exec(); // test.SaveAs(); // test

    // JavaScript Document
    //调用方法
    //   var test=new PageToExcel("data",0,255,"测试.xls");//table id , 第几行开始,最后一行颜色 ,保存的文件名
    //   test.CreateExcel(false);
    //   test.Exec();
    //   test.SaveAs();
    //   test.CloseExcel();
    //LastRowColor 0黑色 255红色
    //

    function PageToExcel(TableID,FirstRow,LastRowColor,SaveAsName){
    this.lastRowColor=LastRowColor==""?0:LastRowColor;
    var today=new Date();
    this.saveAsName=(SaveAsName==""?today.getYear()+"年"+(today.getMonth()+1)+"月"+today.getDate()+"日.xls":SaveAsName);
    this.tableId=TableID;
    this.table=document.getElementById(this.tableId);//导出的table 对象
    this.rows=this.table.rows.length;//导出的table总行数
    this.colSumCols=this.table.rows[0].cells.length;//第一行总列数
    this.fromrow=FirstRow;
    this.beginCol=0; //起始列数
    this.cols=this.colSumCols;
    this.oXL=null;
    this.oWB=null;
    this.oSheet=null;
    this.rowSpans=1; //行合并
        this.colSpans=1; //列合并
        this.colsName={0:"A",1:"B", 2:"C", 3:"D", 4:"E", 5:"F", 6:"G", 7:"H", 8:"I",9:"J", 10:"K", 11:"L", 12:"M", 13:"N", 14:"O", 15:"P", 16:"Q", 16:"R" ,18:"S", 19:"T", 20:"U", 21:"V", 22:"W", 23:"X", 24:"Y", 25:"Z"};
    }
    PageToExcel.prototype.DeleteExcelCols=function(NotShowColList){//数组NotShowColList
        //this.notShowColList=NotShowColList;//不显示列集合,1,2,3,1
        //删除excel中的列
       var m=0;
       for(var i=0;i<NotShowColList.length;i++){
             if(i>0){
                m++;
             }
            var temp=NotShowColList[i]- m;
            var index=this.colsName[temp];
       this.oSheet.Columns(index).Delete;//删除
       }
       m=0;
    }
    
        
    PageToExcel.prototype.CreateExcel=function(ExcelVisible)
    {
       try{
       this.oXL = new ActiveXObject("Excel.Application"); //创建应该对象
       this.oXL.Visible = ExcelVisible;
       this.oWB = this.oXL .Workbooks.Add();//新建一个Excel工作簿
        this.oSheet = this.oWB.ActiveSheet;//指定要写入内容的工作表为活动工作表
       //不显示网格线
       this.oXL.ActiveWindow.DisplayGridlines=false;
       }catch(e){
        alert("请确认安装了非绿色版本的excel!"+e.description);
        CloseExcel();
       }
    }
    
    PageToExcel.prototype.CloseExcel=function()
    {
        this.oXL.DisplayAlerts = false;   
                this.oXL.Quit();   
                this.oXL = null;   
                this.oWB=null;   
                this.oSheet=null; 
    }
    
    PageToExcel.prototype.ChangeElementToLabel=function (ElementObj){
       var GetText="";
       try{
       var childres=ElementObj.childNodes;
      
       }catch(e){ return GetText}
       if(childres.length<=0) return GetText;
       for(var i=0;i<childres.length;i++){
       try{if(childres[i].style.display=="none"||childres[i].type.toLowerCase()=="hidden"){continue;}}
       catch(e){}
        
         try{
          switch (childres[i].nodeName.toLowerCase()){
            case "#text" :
             GetText +=childres[i].nodeValue ;
             break;
            case "br" :
             GetText +="\n";
             break;
            case "img" :
             GetText +="";
             break;
            case "select" :
             GetText +=childres[i].options[childres[i].selectedIndex].innerText ;
             break;
            case "input" :
             if(childres[i].type.toLowerCase()=="submit"||childres[i].type.toLowerCase()=="button"){
              GetText +="";
             }else if(childres[i].type.toLowerCase()=="textarea"){
              GetText +=childres[i].innerText;
             }else{
              GetText +=childres[i].value;
             }
             break;
            default :
             GetText += this.ChangeElementToLabel(childres[i]);
             break;
          }
         
         }catch(e){}
       }
       return GetText;
    }
    PageToExcel.prototype.SaveAs=function (){
       //保存
       try{
        this.oXL.Visible =true;
        var fname = this.oXL.Application.GetSaveAsFilename(this.saveAsName, "Excel Spreadsheets (*.xls), *.xls"); 
        if(fname){ 
        this.oWB.SaveAs(fname);
         this.oXL.Visible =false;
        }
       }catch(e){}; 
    }
    PageToExcel.prototype.Exec=function()
    {
      
       //寻找列数,考虑到第一行可能存在
       for (var i=0; i<this.colSumCols;i++) {
        var tmpcolspan = this.table.rows(0).cells(i).colSpan;
        if ( tmpcolspan>1 ) {
         this.cols += tmpcolspan-1;
        }
       }
      
       //定义2维容器数据,1:行;2:列;值(0 可以填充,1 已被填充)
       var container=new Array(this.rows);
       for (var i=0;i<this.rows;i++) {
        container[i]=new Array(this.cols);
        for (j=0;j<this.cols;j++) {
         container[i][j]=0;
        }
       }
      
       //将所有单元置为文本,避免非数字列被自动变成科学计数法和丢失前缀的0
       this.oSheet.Range(this.oSheet.Cells(this.fromrow+1,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).NumberFormat = "@";
       // 循环行
       for (i=0;i<this.rows;i++){
        //循环列
        for (j=0;j<this.cols;j++){
         //寻找开始列
         for (k=j;k<this.cols;k++){
          if (container[i][k]==0) {
           this.beginCol=k;
           k=this.cols; //退出循环
          }
         }
    //try{
          //赋值
          //此处相应跟改 根据 标签的类型,替换相关参数
          this.oSheet.Cells(i+1+this.fromrow,this.beginCol+1).value = this.ChangeElementToLabel(this.table.rows(i).cells(j));
          
        
          //计算合并列
          try{
         this.colSpans = this.table.rows(i).cells(j).colSpan;
          }catch(e){
         this.colSpans=0   
         }
         if (this.colSpans>1) {
          //合并
          this.oSheet.Range(this.oSheet.Cells(i+1+this.fromrow,this.beginCol+1),this.oSheet.Cells(i+1+this.fromrow,this.beginCol+this.colSpans)).Merge();
         }
         //将当前table位置填写到对应的容器中
         for (k=0; k<this.colSpans;k++) {
          container[i][this.beginCol+k]= 1;
         }
         // 计算合并行
        
         try{
          this.rowSpans = this.table.rows(i).cells(j).rowSpan;
           }catch(e){
           this.rowSpans = 0;
         }
        
         if (this.rowSpans>1) { //行合并
          this.oSheet.Range(this.oSheet.Cells(i+1+this.fromrow,this.beginCol+1),this.oSheet.Cells(i+this.rowSpans+this.fromrow,this.beginCol+this.colSpans)).Merge();
          //将当前table位置填写到对应的容器中
          for (k=1; k<this.rowSpans;k++) { //由于第0行已经被colSpans对应的代码填充了,故这里从第1行开始
           for (l=0;l<this.colSpans;l++) {
            container[i+k][this.beginCol+l]=1;
           }
          }
         }
         //如果开始列+合并列已经等于列数了,故不需要再循环html table
         if (this.beginCol+this.colSpans>=this.cols) j=this.cols;
       
        }
        if(i==0)
        {
         //标题栏
         this.oSheet.Range(this.oSheet.Cells(1,1), this.oSheet.Cells(1,1)).Font.Size=20; 
         this.oSheet.Range(this.oSheet.Cells(1,1), this.oSheet.Cells(1,1)).Font.Bold = true; 
         this.oSheet.Range(this.oSheet.Cells(1,1), this.oSheet.Cells(1,1)).HorizontalAlignment = -4108; //居中
         this.oSheet.Range(this.oSheet.Cells(1,1), this.oSheet.Cells(1,1)).Rows.RowHeight = 40;
        }
         //自动调整行高
       }
      
      
       //最后一行是否空色
       try{
        this.oSheet.Range(this.oSheet.Cells(this.rows,1), this.oSheet.Cells(this.rows,1)).Font.Color=this.lastRowColor;
       }catch(e){}
       this.oSheet.Range(this.oSheet.Cells(this.fromrow+2,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).Rows.RowHeight=20; 
       this.oSheet.Range(this.oSheet.Cells(this.fromrow+2,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).Font.Size=10;
       //自动换行
       this.oSheet.Range(this.oSheet.Cells(this.fromrow+2,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).WrapText = true;
       //自动调整列宽
       this.oSheet.Range(this.oSheet.Cells(this.fromrow+1,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).Columns.AutoFit();
       //点虚线
       this.oSheet.Range(this.oSheet.Cells(this.fromrow+1,1), this.oSheet.Cells(this.fromrow+this.rows,this.cols)).Borders.LineStyle = -4118;
      
        
       return this.rows;
    }
    





    注意:要改IE浏览器安全设置


    展开全文
  • => { this.colData.push({ dataItem: res.warehouseId, dataName: res.warehouseName }) }) }, exportList() { const wscols = [ { wpx: 220 } // 第一列宽度设置单位px ] /** * 合并单元格元素(decode_range方法...

    页面数据如下图:

    export default {

    data() {

    return {

    tableData: [],

    colData: [],

    originData: [{"skuId":"00001","skuName":"nameSku0001","w00001":{"warehouseId":"w00001","warehouseName":"仓库1","quantity":50,"lockQuantity":5,"availableQuantity":45},"w00002":{"warehouseId":"w00002","warehouseName":"仓库2","quantity":200,"lockQuantity":5,"availableQuantity":195},"w00003":{"warehouseId":"w00003","warehouseName":"仓库3","quantity":40,"lockQuantity":1,"availableQuantity":39}},{"skuId":"00002","skuName":"nameSku0002","warehouseItem":[{"warehouseId":"w00001","warehouseName":"仓库1","quantity":100,"lockQuantity":5,"availableQuantity":95},{"warehouseId":"w00002","warehouseName":"仓库2","quantity":200,"lockQuantity":5,"availableQuantity":195},{"warehouseId":"w00003","warehouseName":"仓库3","quantity":5,"lockQuantity":5,"availableQuantity":0}],"w00001":{"warehouseId":"w00001","warehouseName":"仓库1","quantity":100,"lockQuantity":5,"availableQuantity":95},"w00002":{"warehouseId":"w00002","warehouseName":"仓库2","quantity":200,"lockQuantity":5,"availableQuantity":195},"w00003":{"warehouseId":"w00003","warehouseName":"仓库3","quantity":5,"lockQuantity":5,"availableQuantity":0}},{"skuId":"00003","skuName":"nameSku0003","warehouseItem":[{"warehouseId":"w00001","warehouseName":"仓库2","quantity":100,"lockQuantity":5,"availableQuantity":95},{"warehouseId":"w00002","warehouseName":"仓库2","quantity":200,"lockQuantity":5,"availableQuantity":195},{"warehouseId":"w00003","warehouseName":"仓库3","quantity":50,"lockQuantity":5,"availableQuantity":45}],"w00001":{"warehouseId":"w00001","warehouseName":"仓库2","quantity":100,"lockQuantity":5,"availableQuantity":95},"w00002":{"warehouseId":"w00002","warehouseName":"仓库2","quantity":200,"lockQuantity":5,"availableQuantity":195},"w00003":{"warehouseId":"w00003","warehouseName":"仓库3","quantity":50,"lockQuantity":5,"availableQuantity":45}}]

    }

    }

    methods: {

    initData() {

    this.tableData = this.originData.map(res => {

    res.warehouseItem.forEach(item => {

    res[item.warehouseId] = item

    })

    return res

    })

    this.originData[0].warehouseItem.forEach((res) => {

    this.colData.push({

    dataItem: res.warehouseId,

    dataName: res.warehouseName

    })

    })

    },

    exportList() {

    const wscols = [

    { wpx: 220 } // 第一列宽度设置单位px

    ]

    /**

    * 合并单元格元素(decode_range方法解析数据格式)

    {

    s: { //s start 开始

    c: 1,//cols 开始列

    r: 0 //rows 开始行

    },

    e: {//e end 结束

    c: 4,//cols 结束列

    r: 0 //rows 结束行

    }

    }

    */

    const wsMerge = [XLSX.utils.decode_range('A1:A2')]

    /** 头部-行列信息*/

    const header1 = this.colData.map(res => res.dataName).concat('合计')

    header1.map((res, idx, array) => {

    const hdMergeObj = {

    s: { r: 0 },

    e: { r: 0 }

    }

    if ((idx + 2) % 1 === 0) {

    array.splice(3 * idx + 1, 0, '', '')

    hdMergeObj['s']['c'] = 3 * idx + 1

    hdMergeObj['e']['c'] = hdMergeObj['s']['c'] + 2

    wsMerge.push(hdMergeObj)

    }

    return res

    })

    const header2 = this.colData.map(() => '库存数量').concat('库存数量')

    header2.map((res, idx, array) => {

    if ((idx + 2) % 1 === 0) {

    array.splice(3 * idx + 1, 0, '锁定数量', '可用数量')

    }

    return res

    })

    const ws = XLSX.utils.aoa_to_sheet([

    ['商品信息'].concat(header1),

    [''].concat(header2)

    ])

    // console.log('header1', ['商品信息'].concat(header1))

    // console.log(header2)

    /** 商品信息-行列信息*/

    const skuInfo = this.tableData.map(res => res.skuId + '\r\n' + res.skuName)

    XLSX.utils.sheet_add_aoa(ws, skuInfo.map(res => [res]), { origin: 'A3' })

    /** 仓库-行列信息(库存数量,锁定数量,可用数量)*/

    const unAllowed = ['skuId', 'skuName', 'warehouseItem']

    const sheetData = this.tableData.map((data) => {

    const filterItem = Object.assign({}, data)

    Object.keys(filterItem)

    .filter(key => unAllowed.includes(key))

    .forEach(key => delete filterItem[key])

    // 重构完数据

    let warehouseDatas = []

    Object.values(filterItem).forEach(res => {

    warehouseDatas = warehouseDatas.concat([res.quantity]).concat([res.lockQuantity]).concat([res.availableQuantity])

    return warehouseDatas

    })

    return warehouseDatas

    })

    // console.log('sheetData', sheetData)

    XLSX.utils.sheet_add_aoa(ws, sheetData, { origin: { r: 2, c: 1 }})

    /** 合计-行列信息*/

    const totalData = this.tableData.map(row => {

    const totlal_quantity = Object.values(row.warehouseItem).map(res => res.quantity).reduce((accumulator, currentValue) => accumulator + currentValue)

    const totlal_lockQuantity = Object.values(row.warehouseItem).map(res => res.lockQuantity).reduce((accumulator, currentValue) => accumulator + currentValue)

    const totlal_availableQuantity = Object.values(row.warehouseItem).map(res => res.availableQuantity).reduce((accumulator, currentValue) => accumulator + currentValue)

    return [totlal_quantity, totlal_lockQuantity, totlal_availableQuantity]

    })

    // console.log('total', totalData, this.colData.length * 3 + 1)

    XLSX.utils.sheet_add_aoa(ws, totalData, { origin: { r: 2, c: this.colData.length * 3 + 1 }})

    // 导出

    const wb = XLSX.utils.book_new()

    XLSX.utils.book_append_sheet(wb, ws, 'Sheet1')

    ws['!cols'] = wscols

    // wsMerge.push(XLSX.utils.decode_range('B1:D1')) // 测试数据 仓库1模拟数据

    ws['!merges'] = wsMerge

    console.log(wsMerge)

    XLSX.writeFile(wb, '库存报表按SKU.xlsx')

    }

    }

    }

    导出效果如下图:

    展开全文
  • npm install --save xlsx file-saver创建@/vendor/Export2Excel.js:require('script-loader!file-saver');require('script-loader!@/vendor/Blob'); //在vendor文件加下建立Blob.js,代码在最下方import XLSXfrom '...

    npm install --save xlsx file-saver

    创建@/vendor/Export2Excel.js:

    require('script-loader!file-saver');

    require('script-loader!@/vendor/Blob'); //在vendor文件加下建立Blob.js,代码在最下方

    import XLSXfrom 'xlsx'

    function datenum(v, date1904) {

    if (date1904) v +=1462;

    var epoch =Date.parse(v);

    return (epoch -new Date(Date.UTC(1899,11,30))) / (24 *60 *60 *1000);

    }

    function sheet_from_array_of_arrays(data, opts) {

    var ws = {};

    var range = {s: {c:10000000,r:10000000},e: {c:0,r:0}};

    for (var R =0;R != data.length; ++R) {

    for (var C =0;C != data[R].length; ++C) {

    if (range.s.r >R)range.s.r =R;

    if (range.s.c >C)range.s.c =C;

    if (range.e.r

    if (range.e.c

    var cell = {v: data[R][C]};

    if (cell.v ==null)continue;

    var cell_ref = XLSX.utils.encode_cell({c:C,r:R});

    if (typeof cell.v ==='number')cell.t ='n';

    else if (typeof cell.v ==='boolean')cell.t ='b';

    else if (cell.v instanceof Date) {

    cell.t ='n';

    cell.z = XLSX.SSF._table[14];

    cell.v =datenum(cell.v);

    }

    else cell.t ='s';

    ws[cell_ref] =cell;

    }

    }

    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 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;

    }

    // 单列合并数据加工

    //在tableData数据中对colName列进行加工(header:['xx','yy',...],mergeRule:[2,1,1,3,2,1...])

    // 返回格式 newTableDatas = [[列头s], ["abc", "ss", "yyy", 0], [null, "xx", "y", 1], ["bca", "xxx", "yy", 1], [null, "xxx", "yy", 1]]

    // 返回格式 newMergeRules = [{"e":{"r":2,"c":0}, "s":{"r":1,"c":0}},{"e":{"r":4,"c":0}, "s":{"r":3,"c":0}}]

    function formatTableData(header,tableDatas,mergeRules,colIndex,colName){

    var newTableDatas = [header]// 第一行为列头,

    var newMergeRules= []//加工合并规则

    var currRowIndex =1 // 因为第一行为列头所以起始为1

    var mergeIndexs = [currRowIndex]// 因为第一行为列头所以起始为1,为创建newTableDatas使用

    for(var i=0;i

    var endRowIndex =currRowIndex + mergeRules[i] -1

    var merge = {"e":{"r":endRowIndex,"c": colIndex},"s":{"r":currRowIndex,"c":colIndex}}

    newMergeRules.push(merge)

    currRowIndex =currRowIndex + mergeRules[i]

    mergeIndexs.push(currRowIndex)

    }

    for(var i=0;i

    var currRow = tableDatas[i]

    if(mergeIndexs.indexOf(i+1) === -1){

    currRow[colName] =null //合并的数据 但不是第一行时,此列赋值null

    }

    var newTabRow = []

    for (var j in currRow) {

    newTabRow.push(currRow[j])

    }

    newTableDatas.push(newTabRow)

    }

    return [newTableDatas,newMergeRules]

    }

    @/vendor/Blob.js:

    export function export_data_to_excel(header,tableDatas,mergeRules,colIndex,colName,excelName) {

    var oo =formatTableData(header,tableDatas,mergeRules,colIndex,colName);

    var ranges =oo[1];

    /* original data */

    var data =oo[0];

    var ws_name ="SheetJS";

    var wb =new Workbook(),ws =sheet_from_array_of_arrays(data);

    /* add ranges to worksheet */

    // ws['!cols'] = ['apple', 'banan'];

    ws['!merges'] =ranges;

    /* add worksheet to workbook */

    wb.SheetNames.push(ws_name);

    wb.Sheets[ws_name] =ws;

    var wbout = XLSX.write(wb, {bookType:'xlsx',bookSST:false,type:'binary'});

    saveAs(new Blob([s2ab(wbout)], {type:"application/octet-stream"}), excelName+".xlsx")

    }

    (function (view) {

    "use strict";

    view.URL = view.URL || view.webkitURL;

    if (view.Blob && view.URL) {

    try {

    new Blob;

    return;

    }catch (e) {}

    }

    // Internally we use a BlobBuilder implementation to base Blob off of

    // in order to support older browsers that only have BlobBuilder

    var BlobBuilder = view.BlobBuilder || view.WebKitBlobBuilder || view.MozBlobBuilder || (function(view) {

    var

    get_class =function(object) {

    return Object.prototype.toString.call(object).match(/^\[object\s(.*)\]$/)[1];

    }

    ,FakeBlobBuilder =function BlobBuilder() {

    this.data = [];

    }

    ,FakeBlob =function Blob(data, type, encoding) {

    this.data = data;

    this.size = data.length;

    this.type = type;

    this.encoding = encoding;

    }

    ,FBB_proto =FakeBlobBuilder.prototype

    ,FB_proto =FakeBlob.prototype

    ,FileReaderSync = view.FileReaderSync

    ,FileException =function(type) {

    this.code =this[this.name = type];

    }

    ,file_ex_codes = (

    "NOT_FOUND_ERR SECURITY_ERR ABORT_ERR NOT_READABLE_ERR ENCODING_ERR "

    +"NO_MODIFICATION_ALLOWED_ERR INVALID_STATE_ERR SYNTAX_ERR"

    ).split(" ")

    ,file_ex_code =file_ex_codes.length

    ,real_URL = view.URL || view.webkitURL || view

    ,real_create_object_URL =real_URL.createObjectURL

    ,real_revoke_object_URL =real_URL.revokeObjectURL

    ,URL =real_URL

    ,btoa = view.btoa

    ,atob = view.atob

    ,ArrayBuffer = view.ArrayBuffer

    ,Uint8Array = view.Uint8Array

    ;

    FakeBlob.fake =FB_proto.fake =true;

    while (file_ex_code--) {

    FileException.prototype[file_ex_codes[file_ex_code]] =file_ex_code +1;

    }

    if (!real_URL.createObjectURL) {

    URL = view.URL = {};

    }

    URL.createObjectURL =function(blob) {

    var

    type = blob.type

    ,data_URI_header

    ;

    if (type ===null) {

    type ="application/octet-stream";

    }

    if (blobinstanceof FakeBlob) {

    data_URI_header ="data:" +type;

    if (blob.encoding ==="base64") {

    return data_URI_header +";base64," + blob.data;

    }else if (blob.encoding ==="URI") {

    return data_URI_header +"," +decodeURIComponent(blob.data);

    }if (btoa) {

    return data_URI_header +";base64," +btoa(blob.data);

    }else {

    return data_URI_header +"," +encodeURIComponent(blob.data);

    }

    }else if (real_create_object_URL) {

    return real_create_object_URL.call(real_URL, blob);

    }

    };

    URL.revokeObjectURL =function(object_URL) {

    if (object_URL.substring(0,5) !=="data:" &&real_revoke_object_URL) {

    real_revoke_object_URL.call(real_URL, object_URL);

    }

    };

    FBB_proto.append =function(data/*, endings*/) {

    var bb =this.data;

    // decode data to a binary string

    if (Uint8Array && (datainstanceof ArrayBuffer || datainstanceof Uint8Array)) {

    var

    str =""

    ,buf =new Uint8Array(data)

    ,i =0

    ,buf_len =buf.length

    ;

    for (;i

    str +=String.fromCharCode(buf[i]);

    }

    bb.push(str);

    }else if (get_class(data) ==="Blob" ||get_class(data) ==="File") {

    if (FileReaderSync) {

    var fr =new FileReaderSync;

    bb.push(fr.readAsBinaryString(data));

    }else {

    // async FileReader won't work as BlobBuilder is sync

    throw new FileException("NOT_READABLE_ERR");

    }

    }else if (datainstanceof FakeBlob) {

    if (data.encoding ==="base64" &&atob) {

    bb.push(atob(data.data));

    }else if (data.encoding ==="URI") {

    bb.push(decodeURIComponent(data.data));

    }else if (data.encoding ==="raw") {

    bb.push(data.data);

    }

    }else {

    if (typeof data !=="string") {

    data +="";// convert unsupported types to strings

    }

    // decode UTF-16 to binary string

    bb.push(unescape(encodeURIComponent(data)));

    }

    };

    FBB_proto.getBlob =function(type) {

    if (!arguments.length) {

    type =null;

    }

    return new FakeBlob(this.data.join(""), type,"raw");

    };

    FBB_proto.toString =function() {

    return "[object BlobBuilder]";

    };

    FB_proto.slice =function(start, end, type) {

    var args =arguments.length;

    if (args <3) {

    type =null;

    }

    return new FakeBlob(

    this.data.slice(start,args >1 ? end :this.data.length)

    , type

    ,this.encoding

    );

    };

    FB_proto.toString =function() {

    return "[object Blob]";

    };

    FB_proto.close =function() {

    this.size =this.data.length =0;

    };

    return FakeBlobBuilder;

    }(view));

    view.Blob =function Blob(blobParts, options) {

    var type = options ? (options.type ||"") :"";

    var builder =new BlobBuilder();

    if (blobParts) {

    for (var i =0,len = blobParts.length;i

    builder.append(blobParts[i]);

    }

    }

    return builder.getBlob(type);

    };

    }(typeof self !=="undefined" &&self ||typeof window !=="undefined" &&window ||this.content ||this));

    展开全文
  • * 合并单元格元素(decode_range方法解析数据格式) { s: { //s start 开始 c: 1,//cols 开始列 r: 0 //rows 开始行 }, e: {//e end 结束 c: 4,//cols 结束列 r: 0 //rows 结束行 } } */ const wsMerge =...

    页面数据如下图:

    export default {
     data() {
        return {
    	tableData: [],
    	colData: [],
    	 originData: [{"skuId":"00001","skuName":"nameSku0001","w00001":{"warehouseId":"w00001","warehouseName":"仓库1","quantity":50,"lockQuantity":5,"availableQuantity":45},"w00002":{"warehouseId":"w00002","warehouseName":"仓库2","quantity":200,"lockQuantity":5,"availableQuantity":195},"w00003":{"warehouseId":"w00003","warehouseName":"仓库3","quantity":40,"lockQuantity":1,"availableQuantity":39}},{"skuId":"00002","skuName":"nameSku0002","warehouseItem":[{"warehouseId":"w00001","warehouseName":"仓库1","quantity":100,"lockQuantity":5,"availableQuantity":95},{"warehouseId":"w00002","warehouseName":"仓库2","quantity":200,"lockQuantity":5,"availableQuantity":195},{"warehouseId":"w00003","warehouseName":"仓库3","quantity":5,"lockQuantity":5,"availableQuantity":0}],"w00001":{"warehouseId":"w00001","warehouseName":"仓库1","quantity":100,"lockQuantity":5,"availableQuantity":95},"w00002":{"warehouseId":"w00002","warehouseName":"仓库2","quantity":200,"lockQuantity":5,"availableQuantity":195},"w00003":{"warehouseId":"w00003","warehouseName":"仓库3","quantity":5,"lockQuantity":5,"availableQuantity":0}},{"skuId":"00003","skuName":"nameSku0003","warehouseItem":[{"warehouseId":"w00001","warehouseName":"仓库2","quantity":100,"lockQuantity":5,"availableQuantity":95},{"warehouseId":"w00002","warehouseName":"仓库2","quantity":200,"lockQuantity":5,"availableQuantity":195},{"warehouseId":"w00003","warehouseName":"仓库3","quantity":50,"lockQuantity":5,"availableQuantity":45}],"w00001":{"warehouseId":"w00001","warehouseName":"仓库2","quantity":100,"lockQuantity":5,"availableQuantity":95},"w00002":{"warehouseId":"w00002","warehouseName":"仓库2","quantity":200,"lockQuantity":5,"availableQuantity":195},"w00003":{"warehouseId":"w00003","warehouseName":"仓库3","quantity":50,"lockQuantity":5,"availableQuantity":45}}]
    	}
     }
     methods: {
      initData() {
          this.tableData = this.originData.map(res => {
            res.warehouseItem.forEach(item => {
              res[item.warehouseId] = item
            })
            return res
          })
          this.originData[0].warehouseItem.forEach((res) => {
            this.colData.push({
              dataItem: res.warehouseId,
              dataName: res.warehouseName
            })
          })
        },
     exportList() {
          const wscols = [
            { wpx: 220 } // 第一列宽度设置单位px
          ]
          /**
           * 合并单元格元素(decode_range方法解析数据格式)
           {
              s: { //s start 开始
                c: 1,//cols 开始列
                r: 0 //rows 开始行
              },
              e: {//e end  结束
                c: 4,//cols 结束列
                r: 0 //rows 结束行
              }
            }
           */
          const wsMerge = [XLSX.utils.decode_range('A1:A2')]
          /** 头部-行列信息*/
          const header1 = this.colData.map(res => res.dataName).concat('合计')
    
          header1.map((res, idx, array) => {
            const hdMergeObj = {
              s: { r: 0 },
              e: { r: 0 }
            }
            if ((idx + 2) % 1 === 0) {
              array.splice(3 * idx + 1, 0, '', '')
              hdMergeObj['s']['c'] = 3 * idx + 1
              hdMergeObj['e']['c'] = hdMergeObj['s']['c'] + 2
              wsMerge.push(hdMergeObj)
            }
            return res
          })
          const header2 = this.colData.map(() => '库存数量').concat('库存数量')
          header2.map((res, idx, array) => {
            if ((idx + 2) % 1 === 0) {
              array.splice(3 * idx + 1, 0, '锁定数量', '可用数量')
            }
            return res
          })
          const ws = XLSX.utils.aoa_to_sheet([
            ['商品信息'].concat(header1),
            [''].concat(header2)
          ])
          // console.log('header1', ['商品信息'].concat(header1))
          // console.log(header2)
          /** 商品信息-行列信息*/
          const skuInfo = this.tableData.map(res => res.skuId + '\r\n' + res.skuName)
          XLSX.utils.sheet_add_aoa(ws, skuInfo.map(res => [res]), { origin: 'A3' })
          /** 仓库-行列信息(库存数量,锁定数量,可用数量)*/
          const unAllowed = ['skuId', 'skuName', 'warehouseItem']
          const sheetData = this.tableData.map((data) => {
            const filterItem = Object.assign({}, data)
            Object.keys(filterItem)
              .filter(key => unAllowed.includes(key))
              .forEach(key => delete filterItem[key])
            // 重构完数据
            let warehouseDatas = []
            Object.values(filterItem).forEach(res => {
              warehouseDatas = warehouseDatas.concat([res.quantity]).concat([res.lockQuantity]).concat([res.availableQuantity])
              return warehouseDatas
            })
            return warehouseDatas
          })
          // console.log('sheetData', sheetData)
          XLSX.utils.sheet_add_aoa(ws, sheetData, { origin: { r: 2, c: 1 }})
          /** 合计-行列信息*/
          const totalData = this.tableData.map(row => {
            const totlal_quantity = Object.values(row.warehouseItem).map(res => res.quantity).reduce((accumulator, currentValue) => accumulator + currentValue)
            const totlal_lockQuantity = Object.values(row.warehouseItem).map(res => res.lockQuantity).reduce((accumulator, currentValue) => accumulator + currentValue)
            const totlal_availableQuantity = Object.values(row.warehouseItem).map(res => res.availableQuantity).reduce((accumulator, currentValue) => accumulator + currentValue)
            return [totlal_quantity, totlal_lockQuantity, totlal_availableQuantity]
          })
          // console.log('total', totalData, this.colData.length * 3 + 1)
          XLSX.utils.sheet_add_aoa(ws, totalData, { origin: { r: 2, c: this.colData.length * 3 + 1 }})
          // 导出
          const wb = XLSX.utils.book_new()
          XLSX.utils.book_append_sheet(wb, ws, 'Sheet1')
          ws['!cols'] = wscols
          // wsMerge.push(XLSX.utils.decode_range('B1:D1')) // 测试数据 仓库1模拟数据
          ws['!merges'] = wsMerge
          console.log(wsMerge)
          XLSX.writeFile(wb, '库存报表按SKU.xlsx')
        }
     }
    }
    

    导出效果如下图:

    转载于:https://my.oschina.net/u/3734107/blog/3072645

    展开全文
  • vue导出excel表格及合并单元格

    千次阅读 2020-04-05 21:30:16
    这里写自定义目录标题安装导入两个文件Blob.jsExport2Excel.js定义click函数合并单元格 安装 npm install file-saver -S npm install xlsx -S npm install -D script-loader 导入两个文件 在src目录下新建一个...
  • JS 前端Table导出excel 支持多sheet页并且支持合并单元格 资源下载地址:https://download.csdn.net/download/qq_27946017/14890680 <!DOCTYPE html> <html> <head> <meta charset="utf-8...
  • 因为需求变更,最后决定使用做JS导出Excel,网上看了很多的帖子和例子,很多的例子并不能满足需求(处理复杂表头,兼容主流浏览器,兼容IE等等)。所以,自己找了几个比较不错的例子,在其基础上结合自己的需求进行...
  • js导出execl,自动合并单元格,自动增行,增列
  • JS导出Excel

    2018-07-25 11:51:25
    Javascript导出Excel,自动合并单元格、自动列宽、自动获取table页面数据进行合并单元格。列
  • Javascript导出excel为xlsx格式,兼容IE6+和主流浏览器,下载下来直接可以使用。 其他javascript导出excel插件可看作者文章:https://blog.csdn.net/qq_21693027/article/details/80459677
  • winform使用Microsoft.Office.Interop.Excel读取带有合并单元格Excel的demo,Excel版本不限,可以是.xls可以是.xlsx版本。本程序采用webbrowser显示读取的数据,使用bootstrap的css样式美化table表格,使用Json...
  • 前段时间接到一个做报表的项目,需要将数据导出成...由SheetJS出品的js-xlsx是一款非常方便的只需要纯JS即可读取和导出excel的工具库,功能强大,支持格式众多,支持xls、xlsx、ods(一种OpenOffice专有表格文件格...
  • 要求导出这样形式的表格,需要列出哪些单元格需要合并再处理。 以下JS: // 进行所有表头的单元格合并 let merges = [ 'A1:A2', 'B1:B2', 'C1:C2', 'D1:D2', 'E1:E2' ]; for (let i = 0; i < this....
  • 后台系统开发中,统一功能经常遇到需要将统计的数据以EXCEL的形式导出。针对这个需求,我遍在网上搜寻相应的解决方案。最终选择了大多数的建议: 使用js-xlsx插件(https://www.npmjs.com/package/xlsx)。 js-xlsx...
  • js导出Excel

    2019-07-10 15:40:00
    项目中遇到了使用js导出Excel,常规表格(不要求样式、合并单元格)的好做,但是遇到要求携带样式、合并单元格的应该怎么办,我把找到的方法记录了下来: 1、导出常规表格 参考地址:...
  • JS获取table页面数据自动获取,并进行单元格合并行,列 自己在百度上看的,感觉还可以 自己吧下面的代码复制就可以用了 (关于无法启动Excel问题,)   这是HTML页面 &lt;!DOCTYPE html PUBLIC "-...
  • src/libs/目录下增加excel.js excel.js /* eslint-disable */ import XLSX from 'xlsx'; function auto_width(ws, data){ /*set worksheet max width per col*/ const colWidth = data.map(row => row.m...
  • Egg.js中封装exceljs导出excel

    千次阅读 2018-10-10 17:33:50
    excel导出功能时诸多后台管理系统必备的功能,本文主要研究在基于egg.js的系统中,封装exceljs,从而大大简化excel导出。 这里我们扩展helper,直接上代码 const Excel = require('exceljs'); /** * 请求接口...
  • 1、jsp页面代码:导出Excel </button>2、js中的请求代码:$(".oper-box-btn-export-common").click(function(){var flag=confirm("是否确定将查询的通
  • 步骤: 一、前台JS取HtmlTable数据,根据设定的分隔符把数据拼接...--导出Excel--> <script type="text/javascript"> //导出Excel function exportExcel() { var data = ""; $("#divRptTable")...
  • //这个可以单独搞个文件,文件名excelOut,在这个文件夹下vendor/excelOut.js/*eslint-disable*/require('script-loader!file-saver');import XLSXfrom 'xlsx-style'function generateArray(table) {var out =[];var...
  • 步骤: 一、前台JS取HtmlTable数据,根据设定的分隔符把数据拼接起来 ...--导出Excel--> <script type="text/javascript"> //导出Excel function exportExcel() { var data = ""; $("#divRptTabl...
  • jquery.table2excel.js 可以很方便的将table导出excel 大部分table都能轻松导出,但也有一些table导出时会有问题 比如这种 因为thead有三个tr 然后进行合并单元格,colspan是没有问题的 但是rowspan会有问题 ...
  • 纯前端利用 js-xlsx 之合并单元格(3)

    千次阅读 2017-07-17 21:51:00
    前言 前两篇文章主要基本导入导出导出不同格式文件,这次是因为有小伙伴问我怎么合并单元格。其实吧很多东西官网https://github.com/SheetJS/js-xlsx讲的比我清楚多了,不过既然问了我也就讲一下吧!! 其他文章传送门...
  • import XLSX from "xlsx-style"报错:This relative module was not found: ./cptable in ./node_modules/xlsx-style@0.8.13@xlsx-style/dist/cpexcel.js 需要修改源码: 在\node_modules\xlsx-style\dist\cpexcel....
  • 一、js导出EXCEl单元格合并【已验证,比较好用】 [javascript] view plain copy // JavaScript Document //调用方法 // var test=new PageToExcel("data",0,255,"测试.xls");//table ...

空空如也

空空如也

1 2 3 4
收藏数 69
精华内容 27
关键字:

js导出excel合并单元格