layui excel 导入 导出

前端导出

导出按钮点击事件

 $("#LAY-excel-export").click(function (e) {
        // id选择器获取搜索值
        var SearchCompanyName = $("#SearchCompanyName").val();
        var SearchCompanyArea = $("#SearchCompanyArea").val();
        //ProjectType 用于区分导出数据项
        var dataSend = {
          CompanyName: SearchCompanyName,
          City: SearchCompanyArea,
          ProjectType: getUrlParam("ProjectType"),
        };
        ajaxRequest(
          "post",
          REQUEST_URL +
            "/api/Company/ExporProjectCompany?projectId=" +
            ProjectId +
            "",
          "blob",
          dataSend
        ).then(function (res) {
          downloadFile(
            res.data,
            "blob",
            "项目_企业信息" + moment().format("YYYYMMDD_HHmmss") + ".xls"
          );
        });
      });


// http请求函数,支持配置请求和响应参数
function ajaxRequest(method, url, type, params) {
    return new Promise((resolve, reject) => {
        const req = new XMLHttpRequest();
        req.open(method, url, true);
        req.setRequestHeader("Content-Type", "application/json");
        req.responseType = type;
       
        req.onreadystatechange = () => {
           
            // readyState == 4说明请求已完成
            if (req.readyState === 4) {
               //content-disposition
                const data = req.response;
                // 获取文件名
                //const content =req.getResponseHeader('Content-Disposition');
                
                //const fileName =content.split(';')[1].split('filename=')[1];// content && content.split(';')[1].split('filename=')[1];
                if (req.status === 200 || req.status === 304) {
                    resolve({ data });
                }
                else {
                    reject(data);
                }
            }
        };
        req.onload=()=>{
        }
        // 发送数据
         req.send(JSON.stringify(params));
    });
}


// 文件流转blob对象下载
function downloadFile(data, type, fileName) {
    
    let blob = new Blob([data], { type: `application/${type};charset=utf-8` });
    // 获取heads中的filename文件名
    let downloadElement = document.createElement('a');
    // 创建下载的链接
    let href = window.URL.createObjectURL(blob);
    downloadElement.href = href;
    // 下载后文件名
    downloadElement.download = fileName;
    document.body.appendChild(downloadElement);
    // 点击下载
    downloadElement.click();
    // 下载完成移除元素
    document.body.removeChild(downloadElement);
    // 释放掉blob对象
    window.URL.revokeObjectURL(href);
}

导入服务器端

      //执行实例  layui  上传
      var uploadInst = upload.render({
        elem: "#LAY-excel-upload", //绑定元素
        url:
          REQUEST_URL +
          "/api/Company/UploadProjectCompany?ProjectId=" +
          ProjectId +
          "", //上传接口
        auto: true, //选择文件后不自动上传
        exts: "xls|xlsx",//文件类型
        accept: "file",
        before: function (obj) {
          //obj参数包含的信息， 上传前的事件
          layer.load(2); //上传loading
        },
        choose: function (obj) {
          // 选择文件回调

          var files = obj.pushFile();
          var fileArr = Object.values(files); // 注意这里的数据需要是数组，所以需要转换一下

          // 用完就清理掉，避免多次选中相同文件时出现问题
          for (var index in files) {
            if (files.hasOwnProperty(index)) {
              delete files[index];
            }
          }
          $("#LAY-excel-upload").next().val("");
          
          obj.preview(function (index, file) {
            var form_data = new FormData();
            // var file_data = $("#file_b").prop("files")[0];
            form_data.append("fileinput", file);
            form_data.append("ProjectId", getUrlParam("ProjectId"));

            $.ajax({
              url:
                REQUEST_URL +
                "/api/Company/UploadProjectCompany?ProjectId=" +
                ProjectId +
                "", //上传接口
              type: "post",
              dataType: "json",
              contentType: false,
              processData: false,
              data: form_data,
              success: function (res) {
                console.log(res);
                layer.closeAll("loading");
                //{count: 0, code: 200, msg: '上传成功', data: null}
                if (res.code == 200) {
                  layer.msg("导入成功", { icon: 1 });
                  table.reload("tableData");
                } else {
                  // layer.msg(res.msg, { icon: 0 });
                  layer.confirm(
                    res.msg,
                    { title: "提示", btn: ["确定"] },
                    (index) => {
                      layer.close(index);
                    }
                  );
                  //将返回数据的data 转为excel   此处因为上传失败服务器端返回一个base64的execl 下载 转base64 转以后下载
                  base64ConversionExcel(res.data, "重复数据" + file.name);
                }
              },
              error: function (err) {
                layer.closeAll("loading");
                layer.msg("导入失败", { icon: 0 });
              },
            });
          });
        },
        done: function (res) {
          layer.closeAll("loading");

          //上传完毕回调
        },
        error: function (e) {
          layer.closeAll("loading"); //关闭loading

          //请求异常回调
        },
      });

//需要引用jquery-1.8.min.js 和jquery.base64.js
function base64ConversionExcel(base64, titleName) {
    var raw = window.atob(base64)
    var uInt8Array = new Uint8Array(base64.length)
    for (var i = 0; i < raw.length; i++) {
    uInt8Array[i] = raw.charCodeAt(i)
    }
    
    const link = document.createElement("a")
    const blob = new Blob([uInt8Array], {
    type: "application/vnd.ms-excel",
    })
    
    link.style.display = "none"
    link.href = URL.createObjectURL(blob)
    link.setAttribute("download", titleName + ".xls")
    
    document.body.appendChild(link)
    link.click()
    
    document.body.removeChild(link)
 } 

最后附上base64.js源码

/*jslint adsafe: false, bitwise: true, browser: true, cap: false, css: false,
  debug: false, devel: true, eqeqeq: true, es5: false, evil: false,
  forin: false, fragment: false, immed: true, laxbreak: false, newcap: true,
  nomen: false, on: false, onevar: true, passfail: false, plusplus: true,
  regexp: false, rhino: true, safe: false, strict: false, sub: false,
  undef: true, white: false, widget: false, windows: false */
/*global jQuery: false, window: false */
//"use strict";

/*
 * Original code (c) 2010 Nick Galbreath
 * http://code.google.com/p/stringencoders/source/browse/#svn/trunk/javascript
 *
 * jQuery port (c) 2010 Carlo Zottmann
 * http://github.com/carlo/jquery-base64
 *
 * Permission is hereby granted, free of charge, to any person
 * obtaining a copy of this software and associated documentation
 * files (the "Software"), to deal in the Software without
 * restriction, including without limitation the rights to use,
 * copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the
 * Software is furnished to do so, subject to the following
 * conditions:
 *
 * The above copyright notice and this permission notice shall be
 * included in all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
 * OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
 * HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
 * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 * OTHER DEALINGS IN THE SOFTWARE.
*/

/* base64 encode/decode compatible with window.btoa/atob
 *
 * window.atob/btoa is a Firefox extension to convert binary data (the "b")
 * to base64 (ascii, the "a").
 *
 * It is also found in Safari and Chrome.  It is not available in IE.
 *
 * if (!window.btoa) window.btoa = $.base64.encode
 * if (!window.atob) window.atob = $.base64.decode
 *
 * The original spec's for atob/btoa are a bit lacking
 * https://developer.mozilla.org/en/DOM/window.atob
 * https://developer.mozilla.org/en/DOM/window.btoa
 *
 * window.btoa and $.base64.encode takes a string where charCodeAt is [0,255]
 * If any character is not [0,255], then an exception is thrown.
 *
 * window.atob and $.base64.decode take a base64-encoded string
 * If the input length is not a multiple of 4, or contains invalid characters
 *   then an exception is thrown.
 */
 
jQuery.base64 = ( function( $ ) {
  
  var _PADCHAR = "=",
    _ALPHA = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/",
    _VERSION = "1.0";


  function _getbyte64( s, i ) {
    // This is oddly fast, except on Chrome/V8.
    // Minimal or no improvement in performance by using a
    // object with properties mapping chars to value (eg. 'A': 0)

    var idx = _ALPHA.indexOf( s.charAt( i ) );

    if ( idx === -1 ) {
      throw "Cannot decode base64";
    }

    return idx;
  }
  
  
  function _decode( s ) {
    var pads = 0,
      i,
      b10,
      imax = s.length,
      x = [];

    s = String( s );
    
    if ( imax === 0 ) {
      return s;
    }

    if ( imax % 4 !== 0 ) {
      throw "Cannot decode base64";
    }

    if ( s.charAt( imax - 1 ) === _PADCHAR ) {
      pads = 1;

      if ( s.charAt( imax - 2 ) === _PADCHAR ) {
        pads = 2;
      }

      // either way, we want to ignore this last block
      imax -= 4;
    }

    for ( i = 0; i < imax; i += 4 ) {
      b10 = ( _getbyte64( s, i ) << 18 ) | ( _getbyte64( s, i + 1 ) << 12 ) | ( _getbyte64( s, i + 2 ) << 6 ) | _getbyte64( s, i + 3 );
      x.push( String.fromCharCode( b10 >> 16, ( b10 >> 8 ) & 0xff, b10 & 0xff ) );
    }

    switch ( pads ) {
      case 1:
        b10 = ( _getbyte64( s, i ) << 18 ) | ( _getbyte64( s, i + 1 ) << 12 ) | ( _getbyte64( s, i + 2 ) << 6 );
        x.push( String.fromCharCode( b10 >> 16, ( b10 >> 8 ) & 0xff ) );
        break;

      case 2:
        b10 = ( _getbyte64( s, i ) << 18) | ( _getbyte64( s, i + 1 ) << 12 );
        x.push( String.fromCharCode( b10 >> 16 ) );
        break;
    }

    return x.join( "" );
  }
  
  
  function _getbyte( s, i ) {
    var x = s.charCodeAt( i );

    if ( x > 255 ) {
      throw "INVALID_CHARACTER_ERR: DOM Exception 5";
    }
    
    return x;
  }


  function _encode( s ) {
    if ( arguments.length !== 1 ) {
      throw "SyntaxError: exactly one argument required";
    }

    s = String( s );

    var i,
      b10,
      x = [],
      imax = s.length - s.length % 3;

    if ( s.length === 0 ) {
      return s;
    }

    for ( i = 0; i < imax; i += 3 ) {
      b10 = ( _getbyte( s, i ) << 16 ) | ( _getbyte( s, i + 1 ) << 8 ) | _getbyte( s, i + 2 );
      x.push( _ALPHA.charAt( b10 >> 18 ) );
      x.push( _ALPHA.charAt( ( b10 >> 12 ) & 0x3F ) );
      x.push( _ALPHA.charAt( ( b10 >> 6 ) & 0x3f ) );
      x.push( _ALPHA.charAt( b10 & 0x3f ) );
    }

    switch ( s.length - imax ) {
      case 1:
        b10 = _getbyte( s, i ) << 16;
        x.push( _ALPHA.charAt( b10 >> 18 ) + _ALPHA.charAt( ( b10 >> 12 ) & 0x3F ) + _PADCHAR + _PADCHAR );
        break;

      case 2:
        b10 = ( _getbyte( s, i ) << 16 ) | ( _getbyte( s, i + 1 ) << 8 );
        x.push( _ALPHA.charAt( b10 >> 18 ) + _ALPHA.charAt( ( b10 >> 12 ) & 0x3F ) + _ALPHA.charAt( ( b10 >> 6 ) & 0x3f ) + _PADCHAR );
        break;
    }

    return x.join( "" );
  }


  return {
    decode: _decode,
    encode: _encode,
    VERSION: _VERSION
  };
      
}( jQuery ) );