web前端操作Excel

Posted on |

1、上传并预览Excel

效果图：

vue.js上传组件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
<template>
  <div>
    <input id="excel-upload-input" ref="excel-upload-input" type="file" accept=".xlsx, .xls" @change="handleClick">
    <div id="drop" @drop="handleDrop" @dragover="handleDragover" @dragenter="handleDragover">
      Drop excel file here or
      <el-button :loading="loading" style="margin-left:16px;" size="mini" type="primary" @click="handleUpload">Browse</el-button>
    </div>
  </div>
</template>
<script>
import XLSX from 'xlsx'

export default {
  props: {
    beforeUpload: Function,
    onSuccess: Function
  },
  data() {
    return {
      loading: false,
      excelData: {
        header: null,
        results: null
      }
    }
  },
  methods: {
    generateDate({ header, results }) {
      this.excelData.header = header
      this.excelData.results = results
      this.onSuccess && this.onSuccess(this.excelData)
    },
    handleDrop(e) {
      e.stopPropagation()
      e.preventDefault()
      if (this.loading) return
      const files = e.dataTransfer.files
      if (files.length !== 1) {
        this.$message.error('Only support uploading one file!')
        return
      }
      const rawFile = files[0] // only use files[0]

      if (!this.isExcel(rawFile)) {
        this.$message.error('Only supports upload .xlsx, .xls, .csv suffix files')
        return false
      }
      this.upload(rawFile)
      e.stopPropagation()
      e.preventDefault()
    },
    handleDragover(e) {
      e.stopPropagation()
      e.preventDefault()
      e.dataTransfer.dropEffect = 'copy'
    },
    handleUpload() {
      document.getElementById('excel-upload-input').click()
    },
    handleClick(e) {
      const files = e.target.files
      const rawFile = files[0] // only use files[0]
      if (!rawFile) return
      this.upload(rawFile)
    },
    upload(rawFile) {
      this.$refs['excel-upload-input'].value = null // fix can't select the same excel

      if (!this.beforeUpload) {
        this.readerData(rawFile)
        return
      }
      const before = this.beforeUpload(rawFile)
      if (before) {
        this.readerData(rawFile)
      }
    },

    // https://developer.mozilla.org/zh-CN/docs/Web/API/FormData/Using_FormData_Objects
    // https://stackoverflow.com/questions/43013858/ajax-post-a-file-from-a-form-with-axios
    readerData(rawFile) {
      this.loading = true
      return new Promise((resolve, reject) => {
        const reader = new FileReader()
        reader.onload = e => {
          const data = e.target.result
          const fixedData = this.fixdata(data)
          const workbook = XLSX.read(btoa(fixedData), { type: 'base64' })
          const firstSheetName = workbook.SheetNames[0]
          const worksheet = workbook.Sheets[firstSheetName]
          const header = this.get_header_row(worksheet)
          const results = XLSX.utils.sheet_to_json(worksheet)
          this.generateDate({ header, results })
          this.loading = false
          resolve()
        }
        reader.readAsArrayBuffer(rawFile)
      })
    },
    fixdata(data) {
      let o = ''
      let l = 0
      const w = 10240
      for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)))
      o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)))
      return o
    },
    get_header_row(sheet) {
      const headers = []
      const range = XLSX.utils.decode_range(sheet['!ref'])
      let C
      const R = range.s.r /* start in the first row */
      for (C = range.s.c; C <= range.e.c; ++C) { /* walk every column in the range */
        var cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })] /* find the cell in the first row */
        var hdr = 'UNKNOWN ' + C // <-- replace with your desired default
        if (cell && cell.t) hdr = XLSX.utils.format_cell(cell)
        headers.push(hdr)
      }
      return headers
    },
    isExcel(file) {
      return /\.(xlsx|xls|csv)$/.test(file.name)
    }
  }
}
</script>

<style scoped>
#excel-upload-input{
  display: none;
  z-index: -9999;
}
#drop{
  border: 2px dashed #bbb;
  width: 600px;
  height: 160px;
  line-height: 160px;
  margin: 0 auto;
  font-size: 24px;
  border-radius: 5px;
  text-align: center;
  color: #bbb;
  position: relative;
}
</style>

引用上面组件进行上传和预览

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
<template>
    <div class="upload-container">
      <label>上传附件: {{fileName}}</label>
      <upload-excel-component :on-success='handleSuccess' :before-upload="beforeUpload"></upload-excel-component>
      <!-- <el-table :data="tableData" border highlight-current-row style="width: 100%;margin-top:20px;">
        <el-table-column v-for='item of tableHeader' :prop="item" :label="item" :key='item'>
        </el-table-column>
      </el-table> -->
    </div>
    <el-row>
      <el-button type="success" @click="submitHandler">确定</el-button>
      <el-button type="info" @click="cancelHandler">取消</el-button>
    </el-row>
  </div>
</template>

<script>
  import UploadExcelComponent from '@/components/UploadExcel/index.vue'
  import { createFilm } from '@/api/movie'

  export default {
    components: {
      UploadExcelComponent
    },
    data() {
      return {
        tableData: [],
        tableHeader: [],
        filmName: '',
        fileName: '',
        goal: '',
        releaseTime: '',
        enantiomerTime: ''
      }
    },
    methods: {
      beforeUpload(file) {
        const isLt1M = file.size / 1024 / 1024 < 1

        if (isLt1M) {
          this.fileName = file.name
          this.file = file
          return true
        }

        this.$message({
          message: 'Please do not upload files larger than 1m in size.',
          type: 'warning'
        })
        return false
      },
      handleSuccess({
        results,
        header
      }) {
        this.tableData = results
        this.tableHeader = header
      },
      submitHandler() {
        const { filmName, goal, file } = this
        let { releaseTime, enantiomerTime } = this

        if (!filmName || !goal || !releaseTime || !enantiomerTime || !file) {
          this.$message.error('表单数据未填写完~')
          return false
        }

        const currentTime = Math.floor(new Date().getTime() / 1000)
        releaseTime = Math.floor(new Date(releaseTime).getTime() / 1000)
        enantiomerTime = Math.floor(new Date(enantiomerTime).getTime() / 1000)

        if (enantiomerTime < currentTime || enantiomerTime < releaseTime) {
          this.$message.error('【下映时间】不得早于当前日期，且下映时间不能早于上映时间')
          return
        }

        const formData = new FormData()
        formData.append('film_name', filmName)
        formData.append('film_row_piece_volume', goal)
        formData.append('start_show_time', releaseTime)
        formData.append('end_show_time', enantiomerTime)
        formData.append('file', file)

        createFilm(formData).then(res => {
          if (res.ret === 200) {
            this.$message({
              message: '新建成功~',
              type: 'success'
            })
          } else {
            this.$message.error(res.message)
            console.log(res)
          }
          this.filmName = ''
          this.goal = ''
          this.file = ''
          this.fileName = ''
        }, err => {
          this.$message.error(err)
          console.log(err)
        })
      },
      cancelHandler() {}
    }
  }

</script>

<style lang="scss" scoped>
  .film-form-box {
    padding: 20px;
  }

  .row {
    margin-bottom: 20px;
  }

  .label {
    font-size: 15px;
    margin-right: 10px;
  }

  .goal {
    width: 50%;
  }

</style>

2、预览Excel

3、下载Excel