current position:Home>Vue uses the online form of Excel in the front end of lucky sheet to import, display and export Excel files

Vue uses the online form of Excel in the front end of lucky sheet to import, display and export Excel files

2022-04-29 14:35:32_ Xiao Zheng is a little sleepy

scene :

When the interface is ordinary element When the table is not enough to show the data , Or for a better experience . The back-end processes the data into binary document streams , Return to the front end and display directly as excel.

design sketch :

 Insert picture description here

Code implementation :

Installation and introduction :

index.html Something else has to be introduced , My project does not index.html, I was there mounted This introduces

 const script= document.createElement('script');
 script.type = 'text/javascript';
 script.src = `https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/js/plugin.js`;
 document.head.appendChild(script);
 const script1= document.createElement('script');
 script1.type = 'text/javascript';
 script1.src = `https://cdn.jsdelivr.net/npm/luckysheet/dist/luckysheet.umd.js`;
 document.head.appendChild(script1);
npm install luckyexcel

import LuckyExcel from 'luckyexcel'

export.js

// import {
     createCellPos } from './translateNumToLetter'
const Excel = require('exceljs')

import FileSaver from 'file-saver'
export var testaaa = function (){
    
  console.log("...");
}
export var exportExcel = function(luckysheet, value) {
    
  //  Parameter is luckysheet.getluckysheetfile() The object of acquisition 
  // 1. Create Workbook , You can add properties to the workbook 
  const workbook = new Excel.Workbook()
  // 2. Create a table , The second parameter can configure what kind of worksheet to create 
  if (Object.prototype.toString.call(luckysheet) === '[object Object]') {
    
    luckysheet = [luckysheet]
  }
  luckysheet.forEach(function(table) {
    
    if (table.data.length === 0) return  true
    // ws.getCell('B2').fill = fills.
    const worksheet = workbook.addWorksheet(table.name)
    const merge = (table.config && table.config.merge) || {
    }
    const borderInfo = (table.config && table.config.borderInfo) || {
    }
    // 3. Set cell merge , Set cell borders , Set cell style , Set the value 
    setStyleAndValue(table.data, worksheet)
    setMerge(merge, worksheet)
    setBorder(borderInfo, worksheet)
    return true
  })

  // return
  // 4. write in  buffer
  const buffer = workbook.xlsx.writeBuffer().then(data => {
    
    // console.log('data', data)
    const blob = new Blob([data], {
    
      type: 'application/vnd.ms-excel;charset=utf-8'
    })
    console.log(" Export succeeded !")
    FileSaver.saveAs(blob, `${
     value}.xlsx`)
  })
  return buffer
}

var setMerge = function(luckyMerge = {
    }, worksheet) {
    
  const mergearr = Object.values(luckyMerge)
  mergearr.forEach(function(elem) {
    
    // elem Format :{
    r: 0, c: 0, rs: 1, cs: 2}
    //  Press start line , Start the column , End line , End column merge ( amount to  K10:M12)
    worksheet.mergeCells(
      elem.r + 1,
      elem.c + 1,
      elem.r + elem.rs,
      elem.c + elem.cs
    )
  })
}

var setBorder = function(luckyBorderInfo, worksheet) {
    
  if (!Array.isArray(luckyBorderInfo)) return
  // console.log('luckyBorderInfo', luckyBorderInfo)
  luckyBorderInfo.forEach(function(elem) {
    
    //  Now only compatible to borderType  by range The situation of 
    // console.log('ele', elem)
    if (elem.rangeType === 'range') {
    
      let border = borderConvert(elem.borderType, elem.style, elem.color)
      let rang = elem.range[0]
      // console.log('range', rang)
      let row = rang.row
      let column = rang.column
      for (let i = row[0] + 1; i < row[1] + 2; i++) {
    
        for (let y = column[0] + 1; y < column[1] + 2; y++) {
    
          worksheet.getCell(i, y).border = border
        }
      }
    }
    if (elem.rangeType === 'cell') {
    
      // col_index: 2
      // row_index: 1
      // b: {
    
      //   color: '#d0d4e3'
      //   style: 1
      // }
      const {
     col_index, row_index } = elem.value
      const borderData = Object.assign({
    }, elem.value)
      delete borderData.col_index
      delete borderData.row_index
      let border = addborderToCell(borderData, row_index, col_index)
      // console.log('bordre', border, borderData)
      worksheet.getCell(row_index + 1, col_index + 1).border = border
    }
    // console.log(rang.column_focus + 1, rang.row_focus + 1)
    // worksheet.getCell(rang.row_focus + 1, rang.column_focus + 1).border = border
  })
}
var setStyleAndValue = function(cellArr, worksheet) {
    
  if (!Array.isArray(cellArr)) return
  cellArr.forEach(function(row, rowid) {
    
    row.every(function(cell, columnid) {
    
      if (!cell) return true
      let fill = fillConvert(cell.bg)

      let font = fontConvert(
        cell.ff,
        cell.fc,
        cell.bl,
        cell.it,
        cell.fs,
        cell.cl,
        cell.ul
      )
      let alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr)
      let value = ''

      if (cell.f) {
    
        value = {
     formula: cell.f, result: cell.v }
      } else if (!cell.v && cell.ct && cell.ct.s) {
    
        // xls To xlsx after , There are different formats inside , Will go into rich text , That is, the value does not exist and cell.v, It exists in cell.ct.s after 
        // value = cell.ct.s[0].v
        cell.ct.s.forEach(arr => {
    
          value += arr.v
        })
      } else {
    
        value = cell.v
      }
      //  style  Fill in to _value Filling color can be realized in 
      let letter = createCellPos(columnid)
      let target = worksheet.getCell(letter + (rowid + 1))
      // console.log('1233', letter + (rowid + 1))
      for (const key in fill) {
    
        target.fill = fill
        break
      }
      target.font = font
      target.alignment = alignment
      target.value = value

      return true
    })
  })
}

var fillConvert = function(bg) {
    
  if (!bg) {
    
    return {
    }
  }
  // const bgc = bg.replace('#', '')
  let fill = {
    
    type: 'pattern',
    pattern: 'solid',
    fgColor: {
     argb: bg.replace('#', '') }
  }
  return fill
}

var fontConvert = function(
  ff = 0,
  fc = '#000000',
  bl = 0,
  it = 0,
  fs = 10,
  cl = 0,
  ul = 0
) {
    
  // luckysheet:ff( style ), fc( Color ), bl( bold ), it( Italics ), fs( size ), cl( Delete line ), ul( Underline )
  const luckyToExcel = {
    
    0: ' Microsoft YaHei ',
    1: ' Song style (Song)',
    2: ' In black (ST Heiti)',
    3: ' Regular script (ST Kaiti)',
    4: ' Imitation song (ST FangSong)',
    5: ' NSimSun (ST Song)',
    6: ' Chinese New Wei ',
    7: ' Chinese Xingkai ',
    8: ' Chinese official script ',
    9: 'Arial',
    10: 'Times New Roman ',
    11: 'Tahoma ',
    12: 'Verdana',
    num2bl: function(num) {
    
      return num === 0 ? false : true
    }
  }
  //  appear Bug, When you import it ff by luckyToExcel Of val

  let font = {
    
    name: typeof ff === 'number' ? luckyToExcel[ff] : ff,
    family: 1,
    size: fs,
    color: {
     argb: fc.replace('#', '') },
    bold: luckyToExcel.num2bl(bl),
    italic: luckyToExcel.num2bl(it),
    underline: luckyToExcel.num2bl(ul),
    strike: luckyToExcel.num2bl(cl)
  }

  return font
}

var alignmentConvert = function(
  vt = 'default',
  ht = 'default',
  tb = 'default',
  tr = 'default'
) {
    
  // luckysheet:vt( vertical ), ht( level ), tb( Line break ), tr( rotate )
  const luckyToExcel = {
    
    vertical: {
    
      0: 'middle',
      1: 'top',
      2: 'bottom',
      default: 'top'
    },
    horizontal: {
    
      0: 'center',
      1: 'left',
      2: 'right',
      default: 'left'
    },
    wrapText: {
    
      0: false,
      1: false,
      2: true,
      default: false
    },
    textRotation: {
    
      0: 0,
      1: 45,
      2: -45,
      3: 'vertical',
      4: 90,
      5: -90,
      default: 0
    }
  }

  let alignment = {
    
    vertical: luckyToExcel.vertical[vt],
    horizontal: luckyToExcel.horizontal[ht],
    wrapText: luckyToExcel.wrapText[tb],
    textRotation: luckyToExcel.textRotation[tr]
  }
  return alignment
}

var borderConvert = function(borderType, style = 1, color = '#000') {
    
  //  Corresponding luckysheet Of config in borderinfo The parameters of 
  if (!borderType) {
    
    return {
    }
  }
  const luckyToExcel = {
    
    type: {
    
      'border-all': 'all',
      'border-top': 'top',
      'border-right': 'right',
      'border-bottom': 'bottom',
      'border-left': 'left'
    },
    style: {
    
      0: 'none',
      1: 'thin',
      2: 'hair',
      3: 'dotted',
      4: 'dashDot', // 'Dashed',
      5: 'dashDot',
      6: 'dashDotDot',
      7: 'double',
      8: 'medium',
      9: 'mediumDashed',
      10: 'mediumDashDot',
      11: 'mediumDashDotDot',
      12: 'slantDashDot',
      13: 'thick'
    }
  }
  let template = {
    
    style: luckyToExcel.style[style],
    color: {
     argb: color.replace('#', '') }
  }
  let border = {
    }
  if (luckyToExcel.type[borderType] === 'all') {
    
    border['top'] = template
    border['right'] = template
    border['bottom'] = template
    border['left'] = template
  } else {
    
    border[luckyToExcel.type[borderType]] = template
  }
  // console.log('border', border)
  return border
}

function addborderToCell(borders, row_index, col_index) {
    
  let border = {
    }
  const luckyExcel = {
    
    type: {
    
      l: 'left',
      r: 'right',
      b: 'bottom',
      t: 'top'
    },
    style: {
    
      0: 'none',
      1: 'thin',
      2: 'hair',
      3: 'dotted',
      4: 'dashDot', // 'Dashed',
      5: 'dashDot',
      6: 'dashDotDot',
      7: 'double',
      8: 'medium',
      9: 'mediumDashed',
      10: 'mediumDashDot',
      11: 'mediumDashDotDot',
      12: 'slantDashDot',
      13: 'thick'
    }
  }
  // console.log('borders', borders)
  for (const bor in borders) {
    
    // console.log(bor)
    if (borders[bor].color.indexOf('rgb') === -1) {
    
      border[luckyExcel.type[bor]] = {
    
        style: luckyExcel.style[borders[bor].style],
        color: {
     argb: borders[bor].color.replace('#', '') }
      }
    } else {
    
      border[luckyExcel.type[bor]] = {
    
        style: luckyExcel.style[borders[bor].style],
        color: {
     argb: borders[bor].color }
      }
    }
  }

  return border
}

function createCellPos(n) {
    
  let ordA = 'A'.charCodeAt(0)

  let ordZ = 'Z'.charCodeAt(0)
  let len = ordZ - ordA + 1
  let s = ''
  while (n >= 0) {
    
    s = String.fromCharCode((n % len) + ordA) + s

    n = Math.floor(n / len) - 1
  }
  return s
}

<!--excel Show function examples -->
<template>
  <div class="luckysheet-container">
    <div>
      <button @click="downloadCurrent"> Export this page </button>

      <input style="font-size:16px;width: 80px " type="file" @change="uploadExcel"/>
    </div>

    <!-- Table container -->
    <div id="luckysheet"></div>

  </div>
</template>

<script>
  
  import LuckyExcel from 'luckyexcel'
  // Import library export.js  This file is es6 Of , Not in the ordinary HTML File direct import js file ( Although it's all js file , But there's a difference , Please Baidu es6 And es5)! Need to put es6 turn es5 Before you can directly introduce and use !
  import {
    exportExcel} from './excelexport/export'

//   import api_test from "../../api/api_test_example"
    let api_test
  export default {
    
    name: "excel_demo",
    data() {
    
      return {
    
        sheetOption:{
    
          allowEdit:false,
          container: 'luckysheet', //luckysheet For containers id
          title:"gykj form ",
          //column: 10,// Number of columns 
          //row: 30,// Row number 
          lang: 'zh', // chinese 
          showtoolbar: false,// Show toolbar or not 
          showinfobar: false,// Whether to display the top information bar 
          showsheetbar: false,// Whether to show the bottom sheet Button 
          showsheetbarConfig:{
    
            add: false, // newly added sheet
            menu: false, //sheet Manage the menu 
            sheet: false //sheet Page display 
          },
          showstatisticBar:false,// Bottom counter bar 
          enableAddRow:false,// Allow adding rows 
          enableAddBackTop:false,// Allow to go back to the top 
          sheetFormulaBar:false,// Whether to display formula bar 
          cellRightClickConfig: {
    
            copy: false, //  Copy 
            copyAs: false, //  Copy to 
            paste: false, //  Paste 
            insertRow: false, //  Insert row 
            insertColumn: false, //  Insert column 
            deleteRow: false, //  Delete the selected line 
            deleteColumn: false, //  Delete the selected column 
            deleteCell: false, //  Delete cells 
            hideRow: false, //  Hide and show selected rows 
            hideColumn: false, //  Hide and show selected columns 
            rowHeight: false, //  Row height 
            columnWidth: false, //  Column width 
            clear: false, //  Clear content 
            matrix: false, //  Matrix operation 
            sort: false, //  Sort selection 
            filter: false, //  Screening selection 
            chart: false, //  Chart generation 
            image: false, //  Insert a picture 
            link: false, //  Insert link 
            data: false, //  data validation 
            cellFormat: false //  Format cell 
          },// Right click to edit the configuration 
        },
      }
    },
    mounted() {
    
      this.init();
    },
    methods: {
    
      /* Initialize load excel*/
      init() {
    
        // window.luckysheet.create(this.sheetOption)
      },

      /* assignment excel*/
      assignment(fileData) {
    
        let that = this;
        LuckyExcel.transformExcelToLucky(fileData, 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;
          }
          that.sheetOption.title = exportJson.info.name;
          that.sheetOption.data = exportJson.sheets;

          //console.log(exportJson.sheets);
          window.luckysheet.destroy();
          window.luckysheet.create(that.sheetOption);
        });
      },

      /* download excel*/
      downloadCurrent() {
    
        exportExcel(window.luckysheet.getAllSheets(), window.luckysheet.getAllSheets()[0].name)
      },

      /* Download all */
      async downloadExcel() {
    
        let that = this;
        let params = {
    "name":" Detailed statistical table of cultivated land survey ","unit":" ha ","xzqdm":"36","sjjb":[0,1,2,3,4],"year":2019,"dicCode":"sd_20191231_sdsj","hzly":"1","limit":50,"offset":0};
        try {
    
          let fileData = await api_test.previewExcel(params)
          console.log(' Look at all the data structures ',fileData)
          LuckyExcel.transformExcelToLucky(fileData, 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;
            }
            let data = window.luckysheet.toJson(exportJson.sheets).data;
            let name = " All ";
            exportExcel(data, name)
          });
        }catch (e) {
    
            console.log(e)
        }
      },

      /* Upload excel*/
      uploadExcel(evt) {
    
        const files = evt.target.files;
        if (files == null || files.length == 0) {
    
          alert("No files wait for import");
          return;
        }

        let name = files[0].name;
        let that = this
        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();
          that.sheetOption.title = exportJson.info.name;
          that.sheetOption.data = exportJson.sheets;
          console.log(' Look at this structure ',that.sheetOption)

          window.luckysheet.create(that.sheetOption);
        });
      },

      transmission() {
    
        console.log(window.luckysheet.getAllSheets())
      }
    }
  }
</script>

<style lang="scss">
  .luckysheet-container {
    
    width: 100%;
    height: 100%;
    padding: 10px;
    box-sizing: border-box;
    overflow: hidden;
    display: flex;
    flex-direction: column;
    position: relative;
  }
  .luckysheet-input-box {
    
    color: #000;
    z-index: 1000; /* The specific value depends on the actual situation , Need more than the other elements below z-index Big */
  }
  #luckysheet {
    
    /*margin: 0px;*/
    /*padding: 0px;*/
    /*position: absolute;*/
    width: 100%;
    flex: 1;
    /*left: 0px;*/
    /*top: 30px;*/
    /*bottom: 0px;*/
  }
</style>

copyright notice
author[_ Xiao Zheng is a little sleepy],Please bring the original link to reprint, thank you.
https://en.qdmana.com/2022/119/202204291256537357.html

Random recommended