Excel form import and export, the realization of merged cells, form custom style, etc

Table data import

Read and import Excel table data here is the USE of XLSX plug-in

  npm i xlsx
Copy the code

Base64: Reads data in base64 mode.
Binary: byte n is data.charcodeat (n)
String: UTF8 encoded character string.
Buffer: nodejs buffer;
Array: Uint8Array, 8-bit unsigned array;
File: indicates the path of a file (supported only under nodejs).

You also need to use the plug-in’s own utility class xlsx. utils to parse the worksheet

Xlsx.utils. sheet_to_csv: Generates the CSV format
Xlsx.utils.sheet_to_txt: Generates plain text format
Xlsx.utils.sheet_to_html: Generates HTML format
Xlsx.utils. sheet_TO_json: Outputs the JSON format

<template>
  <div class="read_excel_file">
    <slot></slot>
    <input
      class="file-input"
      ref="readexcel_input"
      type="file"
      :accept="SheetJSFT"
      @change="change"
    />
  </div>
</template>
<script>
import XLSX from 'xlsx'
export default {
  data() {
    return {
      SheetJSFT: '.xlsx',}},mounted() {
    // Bind slot click to trigger import file
    if (this.$slots && this.$slots.default && this.$slots.default.length > 0) {
      this.$slots.default[0].elm.addEventListener(
        'click'.this.openExcel.bind(this),)}},methods: {
    // Click open import Excel
    openExcel() {
      let uploadBtn = this.$refs['readexcel_input']
      uploadBtn.click()
    },
    // File import
    change(evt) {
      const files = evt.target.files
      if (!/\.xlsx$/.test(files[0].name)) {
        this.$emit('validate'.false)
        this.$emit('file-change', {
          name: files[0].name,
        })
        console.error('Please select XLSX format file')
        return false
      } else {
        this.$emit('validate'.true)}if (files && files[0]) this.file(files[0])},// Data read
    file(file) {
      const reader = new FileReader()
      reader.onload = e= > {
        const bstr = e.target.result
        const wb = XLSX.read(bstr, { type: 'binary' })  // Type is binary
        const wsname = wb.SheetNames[0]
        const ws = wb.Sheets[wsname]
        const data = XLSX.utils.sheet_to_json(ws, {
          header: 1.// blankrows: false,
        })  // Read the json format
        this.formatData(data, file.name, ws['! merges'])
      }
      reader.readAsBinaryString(file)
    },
    // Data formatting
    formatData(list, name, merges) {
      let arr = []
      for (let i = 1; i < list.length; i++) {
        if (list[i].length > 0) {
          let obj = {}
          list[i].map((v, j) = > {
            obj[list[0][j]] = v
          })
          arr.push(obj)
        }
      }
      this.$emit('file-change', {
        header: list[0].body: arr,
        name: name,
        merges: merges,
      })
      // The value property of the input must be emptied, otherwise the second selection of the same file will not trigger the change event.
      this.$refs['readexcel_input'].value = ' '}},}</script>
<style lang="scss" scoped>
.read_excel_file {
  display: inline-block;
  .file-input {
    width: 0;
    height: 0; }}</style>
Copy the code

The specific uses of components are as follows

<template>
  <div class="cs">
    <ReadExcel @file-change="excelFileChange">
      <div class="import-button">The import</div>
    </ReadExcel>
  </div>
</template>

<script>
import ReadExcel from './components/ReadExcel.vue'
export default {
  components: {
    ReadExcel,
  },
  methods: {
    excelFileChange(data) {
      let { merges, body, name, header } = data
      console.log(merges, body, name, header)
    },
  },
}
</script>
Copy the code

Next, let’s try importing the following table

The exported data (merges, body, Name, header) is as follows

So you get the data in the table

Export Excel table

Here are two ways to export a table

1. Export the XLSX plug-in

The XLSX plug-in is used again

I’m going to do a little demo here

      function exportExcel(header, body, merges, wscols, wsrows, fileName) {
        body.unshift(header)
        const ws = XLSX.utils.aoa_to_sheet(body)
        const wb = XLSX.utils.book_new()
        ws['! cols'] = wscols
        ws['! rows'] = wsrows
        ws['! merges'] = merges
        XLSX.utils.book_append_sheet(wb, ws, 'Sheet1')
        / / generates excel
        XLSX.writeFile(wb, `${fileName}.xlsx`)}// Set the table header
      let header = [
        '* Mobile phone number '.* Order No..'* Commodity No. '.Number of '*'.'* Order Payment type '.* Total order amount ¥.* Actual payment amount of order ¥',]// Set the table data
      let body = [
        ['18600000002'.'svp000002'.'sp002'.'1'.'wechat Pay'.'1100'.'1100'],
        [undefined.undefined.'sp003'.'2'.undefined.undefined.undefined],
        [undefined.undefined.'sp004'.'1'.undefined.undefined.undefined]]// Set the merged cells
      let merges = [
        { e: { c: 0.r: 3 }, s: { c: 0.r: 1}}, {e: { c: 1.r: 3 }, s: { c: 1.r: 1}}, {e: { c: 4.r: 3 }, s: { c: 4.r: 1}}, {e: { c: 5.r: 3 }, s: { c: 5.r: 1}}, {e: { c: 6.r: 3 }, s: { c: 6.r: 1}},]// Specify the width of each column
      let wscols = [
        { wch: 20 },
        { wch: 20 },
        { wch: 30 },
        { wch: 30 },
        { wch: 30 },
        { wch: 30 },
        { wch: 30},]// Specify the height of each line
      let wsrows = [{ hpx: 20 }]
      exportExcel(header, body, merges, wscols, wsrows, 'Generate Excel file')
Copy the code

The result is as follows

Exporting is exporting success, but only bare data

So what do I do here if I want to style the cell?

If you use XLSX plug-in alone is not able to set the style of cells, it seems that there is a Pro XLSX can do this, but is charged; There is also the free XLSX-style implementation for setting styles

npm i xlsx-style
Copy the code

It is possible to use XlsX-style to set the style, but for example, in the header of the Excel file we imported at the beginning (as shown below), there are two different colors of text in one cell. In this case, the author cannot use XLSX-style to set the style. So we don’t know how to use xLSX-style here, so let’s try the second way to export Excel

2. Use HTML table tag to export Excel

Here we use the Table tag to generate the Excel file directly

Go straight to code

      function tableHtmlCompute(str) {
        return (
          "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'><head><! --[if gte mso 9]><xml>" +
          '<x:ExcelWorkbook>' +
          '<x:ExcelWorksheets>' +
          '<x:ExcelWorksheet>' +
          '<x:WorksheetOptions><x:Print><x:ValidPrinterInfo /></x:Print></x:WorksheetOptions>' +
          '</x:ExcelWorksheet>' +
          '</x:ExcelWorksheets>' +
          '
       ' +
          '<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"></head><body>' +
          '<table border="1" cellspacing="1" cellpadding="1">' +
          '<tr>' + // Here is the header
          '< td style = "background: RGB (217225242); The oledata.mso - number - format: @ "> < span style =" color: RGB (0, 255) "> * < / span > < span > phone number < / span > < / td > ' +
          '< td style = "background: RGB (217225242); The oledata.mso - number - format: @ "> < span style =" color: RGB (0, 255) "> * < / span > < span > order no. < / span > < / td > ' +
          '< td style = "background: RGB (217225242); The oledata.mso - number - format: @ "> < span style =" color: RGB (0, 255) "> * < / span > < span > product id < / span > < / td > ' +
          '< td style = "background: RGB (217225242); The oledata.mso - number - format: @ "> < span style =" color: RGB (0, 255) "> * < / span > < span > < / span > < / td > ' +
          '< td style = "background: RGB (217225242); The oledata.mso - number - format: @ "> < span style =" color: RGB (0, 255) "> * < / span > < span > payment type < / span > < / td > ' +
          '< td style = "background: RGB (217225242); The oledata.mso - number - format: @ "> < span style =" color: RGB (0, 255) "> * < / span > < span > order selections total amount < / span > < / td > ' +
          '< td style = "background: RGB (217225242); The oledata.mso - number - format: @ "> < span style =" color: RGB (0, 255) "> * < / span > < span > order amount actually paid selections < / span > < / td > ' +
          '</tr>' +
          '<tr>' +
          '< td style = "background: RGB (217225242); mso-number-format:@"> required 

 +
          '< td style = "background: RGB (217225242); mso-number-format:@"> required 

 +
          '< td style = "background: RGB (217225242); mso-number-format:@"> required 

 +
          '< td style = "background: RGB (217225242); mso-number-format:@"> mandatory 

2. ' +
          '< td style = "background: RGB (217225242); mso-number-format:@"> mandatory + single 

 wechat pay 

 alipay pay 

 online payment 

 POS payment 

 ' +
          '< td style = "background: RGB (217225242); The oledata.mso - number - format: @ "> < span style =" color: RGB (0, 255) "> get < / span > < / td > ' +
          '< td style = "background: RGB (217225242); The oledata.mso - number - format: @ "> < span style =" color: RGB (0, 255) "> get < / span > < / td > ' +
          '</tr>' +
          str +
          '</table></body></html>')}function excelExport(str) {
        let html = tableHtmlCompute(str)
        let blob = new Blob([html], {
          type: 'text/plain; charset=utf-8',})// Solve the problem of Chinese garbled characters
        blob = new Blob([String.fromCharCode(0xfeff), blob], {
          type: blob.type,
        })
        let a = document.createElement('a')
        a.style.display = 'none'
        // Generate a BLOb URL for element A using the url.createObjecturl () method
        a.href = URL.createObjectURL(blob)
        // Set the file name
        a.download = 'the excel name. XLSX'
        document.body.appendChild(a)
        a.click()
        document.body.removeChild(a)
      }
      let body = [
        {
          mobile: '18600000002'.orderNumber: 'svp000002'.products: [{productNumber: 'sp002'.quantity: 1}, {productNumber: 'sp003'.quantity: 2}, {productNumber: 'sp004'.quantity: 1],},paymentType: 'wechat Pay'.total: 1100.paymenteds: 1100,},]let header = [
        'mobile'.'orderNumber'.'productNumber'.'quantity'.'paymentType'.'total'.'paymenteds',]let productsHeader = ['productNumber'.'quantity']
      let html = ' '
      body.forEach(e= > {
        if (e.products && e.products.length) {
          e.products.forEach((item, i) = > {
            let str = '<tr>'
            if (i) {
              productsHeader.forEach(key= > {
                str += `<td style="mso-number-format:\\@"><span>${
                  item[key] ? item[key] : ' '
                }</span></td>`
              })
              str += '</tr>'
            } else {
              / / I = = = 0; The header of each piece of data
              header.forEach(key= > {
                if (productsHeader.includes(key)) {
                  str += `<td style="mso-number-format:\\@"><span>${
                    item[key] ? item[key] : ' '
                  }</span></td>`
                } else {
                  let val = e[key] ? e[key] : ' '
                  str += `<td style="mso-number-format:\\@"  rowspan=${e.products.length}><span>${val}</span></td>`
                }
              })
              str += '</tr>'
            }
            html += str
          })
        }
      })
      excelExport(html)
Copy the code

The result is as follows

Here are a few caveats

1, The above shows how to wrap a line in the same cell. If you need to wrap a line in the same cell, you need to use the following code

  <br style="mso-data-placement:same-cell"/>
Copy the code

If you simply write the BR tag without the style=” SO-data-placement :same-cell”, it will result in two cells being merged together

2, mso-number-format:\@” style=”mso-number-format:\@” style=”mso-number-format:\@

Excel form import and export, the realization of merged cells, form custom style, etc

Table data import

Export Excel table

1. Export the XLSX plug-in

2. Use HTML table tag to export Excel

Related Posts

React Admin is the front-end development framework for mobile payment micro services

How to use Android emulator to develop UniApp on Mac/Windows

We know that 0.1+0.2 = 0.30000000000000004, so how do we make it equal to 0.3