Vue2预览xlsx文件——插件LuckySheet和LuckyExcel

3,418 阅读1分钟

Luckyexcel官网: 一个适配 Luckysheet 的excel导入导出库,只支持.xlsx格式文件(不支持.xls)

1. 在HTML中预览xlsx

<!--引入LuckyExcel-->
<script src="https://cdn.jsdelivr.net/npm/luckyexcel/dist/luckyexcel.umd.js"></script>
<script>
    // 先确保获取到了xlsx文件file,再使用全局方法window.LuckyExcel转化
    LuckyExcel.transformExcelToLucky(
        file, 
        function(exportJson, luckysheetfile){
            // 获得转化后的表格数据后,使用luckysheet初始化,或者更新已有的luckysheet工作簿
            // 注:luckysheet需要引入依赖包和初始化表格容器才可以使用
            luckysheet.create({
                container: 'luckysheet', // luckysheet is the container id
                data:exportJson.sheets,
                title:exportJson.info.name,
                userInfo:exportJson.info.name.creator
            });
        },
        function(err){
            logger.error('Import failed. Is your fail a valid xlsx?');
        }
    );
</script>

UMD 版本可以通过 <script> 标签直接用在浏览器中

1.1 预览效果

1.gif

1.2 代码

<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8" />
    <title>Hello xlsx!</title>
    <!-- <link rel="stylesheet" href="./luckysheet/plugins/css/pluginsCss.css" />
    <link rel="stylesheet" href="./luckysheet/plugins/plugins.css" />
    <link rel="stylesheet" href="./luckysheet/css/luckysheet.css" />
    <link rel="stylesheet" href="./luckysheet/assets/iconfont/iconfont.css" />
    <script src="./luckysheet/plugins/js/plugin.js"></script>
    <script src="./luckysheet/luckysheet.umd.js"></script> -->
    
   
    <link
      rel="stylesheet"
      href="https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/css/pluginsCss.css"
    />
    <link
      rel="stylesheet"
      href="https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/plugins.css"
    />
    <link
      rel="stylesheet"
      href="https://cdn.jsdelivr.net/npm/luckysheet/dist/css/luckysheet.css"
    />
    <link
      rel="stylesheet"
      href="https://cdn.jsdelivr.net/npm/luckysheet/dist/assets/iconfont/iconfont.css"
    />
    <script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/js/plugin.js"></script>
    <!--引入lucksheet变量,绑定在window的对象中-->
    <script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/luckysheet.umd.js"></script>

    <script>
      $(function () {
        //Configuration item
        var options = {
          container: 'luckysheet', //luckysheet is the container id
          showinfobar: false
        }
        luckysheet.create(options)
      })
    </script>
  </head>
  <body>
    <div
      id="lucky-mask-demo"
      style="
        position: absolute;
        z-index: 1000000;
        left: 0px;
        top: 0px;
        bottom: 0px;
        right: 0px;
        background: rgba(255, 255, 255, 0.8);
        text-align: center;
        font-size: 40px;
        align-items: center;
        justify-content: center;
        display: none;
      "
    >
      Downloading
    </div>
    <p style="text-align: center">
      <input
        style="font-size: 16px"
        type="file"
        id="Luckyexcel-demo-file"
        name="Luckyexcel-demo-file"
        change="demoHandler"
      />
      Or Load remote xlsx file:
      <select
        style="height: 27px; top: -2px; position: relative"
        id="Luckyexcel-select-demo"
      >
        <option value="">select a demo</option>
        <option
          value="https://minio.cnbabylon.com/public/luckysheet/money-manager-2.xlsx"
        >
          Money Manager.xlsx
        </option>
        <option
          value="https://minio.cnbabylon.com/public/luckysheet/Activity%20costs%20tracker.xlsx"
        >
          Activity costs tracker.xlsx
        </option>
        <option
          value="https://minio.cnbabylon.com/public/luckysheet/House%20cleaning%20checklist.xlsx"
        >
          House cleaning checklist.xlsx
        </option>
        <option
          value="https://minio.cnbabylon.com/public/luckysheet/Student%20assignment%20planner.xlsx"
        >
          Student assignment planner.xlsx
        </option>
        <option
          value="https://minio.cnbabylon.com/public/luckysheet/Credit%20card%20tracker.xlsx"
        >
          Credit card tracker.xlsx
        </option>
        <option
          value="https://minio.cnbabylon.com/public/luckysheet/Blue%20timesheet.xlsx"
        >
          Blue timesheet.xlsx
        </option>
        <option
          value="https://minio.cnbabylon.com/public/luckysheet/Student%20calendar%20%28Mon%29.xlsx"
        >
          Student calendar (Mon).xlsx
        </option>
        <option
          value="https://minio.cnbabylon.com/public/luckysheet/Blue%20mileage%20and%20expense%20report.xlsx"
        >
          Blue mileage and expense report.xlsx
        </option>
      </select>
      <a href="javascript:void(0)" id="Luckyexcel-downlod-file"
        >Download source xlsx file</a
      >
    </p>
    <div
      id="luckysheet"
      style="
        margin: 0px;
        padding: 0px;
        position: absolute;
        width: 100%;
        left: 0px;
        top: 50px;
        bottom: 0px;
        outline: none;
      "
    ></div>
    <!-- <script src="./luckysheet/luckyexcel.umd.js"></script> -->
    <script src="https://cdn.jsdelivr.net/npm/luckyexcel/dist/luckyexcel.umd.js"></script>

    <script>
      function demoHandler() {
        let upload = document.getElementById('Luckyexcel-demo-file')
        let selectADemo = document.getElementById('Luckyexcel-select-demo')
        let downlodDemo = document.getElementById('Luckyexcel-downlod-file')
        let mask = document.getElementById('lucky-mask-demo')
        if (upload) {
          window.onload = () => {
            upload.addEventListener('change', function (evt) {
              var files = evt.target.files
              if (files == null || files.length == 0) {
                alert('No files wait for import')
                return
              }

              let name = files[0].name
              let suffixArr = name.split('.'),
                suffix = suffixArr[suffixArr.length - 1]
              if (suffix != 'xlsx') {
                alert('Currently only supports the import of xlsx files')
                return
              }
              LuckyExcel.transformExcelToLucky(
                files[0],
                function (exportJson, luckysheetfile) {
                  if (
                    exportJson.sheets == null ||
                    exportJson.sheets.length == 0
                  ) {
                    alert(
                      'Failed to read the content of the excel file, currently does not support xls files!'
                    )
                    return
                  }
                  window.luckysheet.destroy()

                  window.luckysheet.create({
                    container: 'luckysheet', //luckysheet is the container id
                    showinfobar: false,
                    data: exportJson.sheets,
                    title: exportJson.info.name,
                    userInfo: exportJson.info.name.creator
                  })
                }
              )
            })

            selectADemo.addEventListener('change', function (evt) {
              var obj = selectADemo
              var index = obj.selectedIndex
              var value = obj.options[index].value
              var name = obj.options[index].innerHTML
              if (value == '') {
                return
              }
              mask.style.display = 'flex'
              LuckyExcel.transformExcelToLuckyByUrl(
                value,
                name,
                function (exportJson, luckysheetfile) {
                  if (
                    exportJson.sheets == null ||
                    exportJson.sheets.length == 0
                  ) {
                    alert(
                      'Failed to read the content of the excel file, currently does not support xls files!'
                    )
                    return
                  }
                  console.log(exportJson, luckysheetfile)
                  mask.style.display = 'none'
                  window.luckysheet.destroy()

                  window.luckysheet.create({
                    container: 'luckysheet', //luckysheet is the container id
                    showinfobar: false,
                    data: exportJson.sheets,
                    title: exportJson.info.name,
                    userInfo: exportJson.info.name.creator
                  })
                }
              )
            })

            downlodDemo.addEventListener('click', function (evt) {
              var obj = selectADemo
              var index = obj.selectedIndex
              var value = obj.options[index].value

              if (value.length == 0) {
                alert('Please select a demo file')
                return
              }

              var elemIF = document.getElementById('Lucky-download-frame')
              if (elemIF == null) {
                elemIF = document.createElement('iframe')
                elemIF.style.display = 'none'
                elemIF.id = 'Lucky-download-frame'
                document.body.appendChild(elemIF)
              }
              elemIF.src = value
            })
          }
        }
      }
      demoHandler()
    </script>
  </body>
</html>

2. Vue项目 引入LuckySheet

  • 1、先从官网下载:LuckySheet源码,npm i 依赖安装
  • 2、再使用 npm run build,将LuckySheet源码进行打包,打包完之后会出现dist文件
  • 3、打包好的 dist 文件夹下的文件移动到Vue项目的public 文件下(也就是跟index.html文件同目录下)

image.png

2.1. 在public/index.html文件中引入js

<link rel='stylesheet' href='./plugins/css/pluginsCss.css' />
<link rel='stylesheet' href='./plugins/plugins.css' />
<link rel='stylesheet' href='./css/luckysheet.css' />
<link rel='stylesheet' href='./assets/iconfont/iconfont.css' />
<script src="./plugins/js/plugin.js"></script>
<script src="./luckysheet.umd.js"></script>
<!---public/index.html-->
<!DOCTYPE html>
<html lang="">
  <head>
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta name="viewport" content="width=device-width,initial-scale=1.0" />
    <link rel="icon" href="<%= BASE_URL %>favicon.ico" />
    <title><%= htmlWebpackPlugin.options.title %></title>
    <!-- <script src="./tinymce/js/tinymce/tinymce.min.js""></script> -->
    <link rel="stylesheet" href="./luckysheet/plugins/css/pluginsCss.css" />
    <link rel="stylesheet" href="./luckysheet/plugins/plugins.css" />
    <link rel="stylesheet" href="./luckysheet/css/luckysheet.css" />
    <link rel="stylesheet" href="./luckysheet/assets/iconfont/iconfont.css" />
    <script src="./luckysheet/plugins/js/plugin.js"></script>
    <script src="./luckysheet/luckysheet.umd.js"></script>
  </head>
  <body>
    <noscript>
      <strong
        >We're sorry but <%= htmlWebpackPlugin.options.title %> doesn't work
        properly without JavaScript enabled. Please enable it to
        continue.</strong
      >
    </noscript>
    <div id="app"></div>
    <!-- built files will be auto injected -->
  </body>
</html>

2.2 在Vue组件中使用

image.png

<template>
  <div class="hello">
    <el-upload class="upload-demo" action="" :before-upload="beforeUpload">
      <i class="el-icon-upload"></i><em>点击上传</em>
      <div class="el-upload__tip" slot="tip">只能上传xlsx文件,</div>
    </el-upload>
    <div id="luckysheet" class="luckysheet-content"></div>
  </div>
</template>

<script>
import LuckyExcel from 'luckyexcel'
export default {
  name: 'HelloWorld',
  data() {
    return {}
  },
  mounted() {},
  methods: {
    beforeUpload(file) {
      this.init(file)
    },
    init(file) {
      LuckyExcel.transformExcelToLucky(
        file,
        function (exportJson, luckysheetfile) {
          // 获得转化后的表格数据后,使用luckysheet初始化,或者更新已有的luckysheet工作簿
          // 注:luckysheet需要引入依赖包和初始化表格容器才可以使用
          console.log(exportJson)
          luckysheet.create({
            container: 'luckysheet', // luckysheet is the container id
            data: exportJson.sheets,
            title: exportJson.info.name,
            userInfo: exportJson.info.name.creator,
            lang: 'zh', // 设定表格语言
            showinfobar: false, //是否显示顶部信息栏
            showtoolbar: false, //是否显示顶部工具栏
            sheetFormulaBar: false //是否显示公式栏
          })
          // this.createExcel()
        },
        function (err) {
          logger.error('Import failed. Is your fail a valid xlsx?')
        }
      )
    },
    // LuckyExcel可选配置项
    createExcel() {
      LuckyExcel.create({
        container: 'luckysheet', // 设定DOM容器的id
        title: 'Luckysheet Demo', // 设定表格名称
        lang: 'zh', // 设定表格语言
        plugins: ['chart'],
        data: [
          {
            name: '表1', //工作表名称
            color: '#eee333', //工作表(工作表名称底部边框线)颜色
            index: 0, //工作表索引(新增一个工作表时该值是一个随机字符串)
            status: 1, //激活状态
            order: 0, //工作表的下标
            hide: 0, //是否隐藏
            row: 36, //行数
            column: 10, //列数
            defaultRowHeight: 28, //自定义行高,单位px
            defaultColWidth: 100, //自定义列宽,单位px
            celldata: [], //初始化使用的单元格数据,r代表行,c代表列,v代表该单元格的值,最后展示的是value1,value2
            config: {
              merge: {}, //合并单元格
              rowlen: {}, //表格行高
              columnlen: {}, //表格列宽
              rowhidden: {}, //隐藏行
              colhidden: {}, //隐藏列
              borderInfo: {}, //边框
              authority: {} //工作表保护
            },

            scrollLeft: 0, //左右滚动条位置
            scrollTop: 0, //上下滚动条位置
            luckysheet_select_save: [], //选中的区域
            calcChain: [], //公式链
            isPivotTable: false, //是否数据透视表
            pivotTable: {}, //数据透视表设置
            filter_select: {}, //筛选范围
            filter: null, //筛选配置
            luckysheet_alternateformat_save: [], //交替颜色
            luckysheet_alternateformat_save_modelCustom: [], //自定义交替颜色
            luckysheet_conditionformat_save: {}, //条件格式
            frozen: {}, //冻结行列配置
            chart: [], //图表配置
            zoomRatio: 1, // 缩放比例
            image: [], //图片
            showGridLines: 1, //是否显示网格线
            dataVerification: {} //数据验证配置
          }
        ],
        showtoolbar: false,
        showtoolbarConfig: {
          undoRedo: false, //撤销重做,注意撤消重做是两个按钮,由这一个配置决定显示还是隐藏
          paintFormat: false, //格式刷
          currencyFormat: false, //货币格式
          percentageFormat: false, //百分比格式
          numberDecrease: false, // '减少小数位数'
          numberIncrease: false, // '增加小数位数
          moreFormats: false, // '更多格式'
          font: false, // '字体'
          fontSize: false, // '字号大小'
          bold: false, // '粗体 (Ctrl+B)'
          italic: false, // '斜体 (Ctrl+I)'
          strikethrough: false, // '删除线 (Alt+Shift+5)'
          underline: false, // '下划线 (Alt+Shift+6)'
          textColor: false, // '文本颜色'
          fillColor: false, // '单元格颜色'
          border: false, // '边框'
          mergeCell: false, // '合并单元格'
          horizontalAlignMode: false, // '水平对齐方式'
          verticalAlignMode: false, // '垂直对齐方式'
          textWrapMode: false, // '换行方式'
          textRotateMode: false, // '文本旋转方式'
          image: false, // '插入图片'
          link: false, // '插入链接'
          chart: false, // '图表'(图标隐藏,但是如果配置了chart插件,右击仍然可以新建图表)
          postil: false, //'批注'
          pivotTable: false, //'数据透视表'
          function: false, // '公式'
          frozenMode: false, // '冻结方式'
          sortAndFilter: false, // '排序和筛选'
          conditionalFormat: false, // '条件格式'
          dataVerification: false, // '数据验证'
          splitColumn: false, // '分列'
          screenshot: false, // '截图'
          findAndReplace: false, // '查找替换'
          protection: false, // '工作表保护'
          print: false // '打印'
        },
        showsheetbar: false, //是否显示底部sheet页按钮
        showsheetbarConfig: {
          add: false, //新增sheet
          menu: false, //sheet管理菜单
          sheet: false //sheet页显示
        },
        showinfobar: false, //是否显示顶部信息栏
        showstatisticBar: false, //是否显示底部计数栏
        showstatisticBarConfig: {
          count: false, // 计数栏
          view: false, // 打印视图
          zoom: false // 缩放
        },
        sheetFormulaBar: false, //是否显示公式栏
        allowCopy: false, //是否允许拷贝
        enableAddRow: true //允许添加行
      })
    }
  }
}
</script>
<style lang="css" scoped>
.luckysheet-content {
  margin: 0px;
  padding: 0px;
  position: absolute;
  width: 100%;
  height: 500px;
  left: 0px;
  top: 40px;
  bottom: 0px;
}
</style>