1-读取.csv文件为json数组对象,批量导入支付宝交易记录明细

使用xlsx模块导入、导出.csv文件

1. 解决无法读取编码为UTF-8的.csv文件错误
2. 解决Excel中文乱码问题
3. 解决日期数据错误问题

ExcelUpload文件上传组件

ExcelUpload.vue

<template>
    <!-- <div>
        <input
            ref="excel-upload-input"
            class="excel-upload-input"
            type="file"
            accept=".xlsx, .xls, .csv"
            @change="handleClick"
        />
        <div class="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> -->
    <div class="upload-excel" :class="customClass">
        <input
            multiple="multiple"
            ref="excel-upload-input"
            class="excel-upload-input"
            type="file"
            accept=".xlsx, .xls, .csv"
            @change="handleClick"
        />
        <el-button :loading="loading" @click="handleUpload" v-bind="$attrs" v-on="$listeners">
            <slot>ExcelUpload</slot>
        </el-button>
    </div>
</template>

<script>
import XLSX from 'xlsx';
import isUTF8 from '@/utils/isUTF8';
import objKeyTrim from './objKeyTrim';
const cptable = require('xlsx/dist/cpexcel.js');
export default {
    props: {
        // 上传文件之前的校验
        beforeUpload: {
            type: Function,
            required: false,
            default: file => {
                const isLt10M = file.size / 1024 / 1024 / 1024 < 1;
                if (isLt10M) {
                    return true;
                }
                this.$message({message: '上传文件的大小超过10M', type: 'warning'});
                return false;
            }
        },

        // 上传错误的回调
        onError: {
            type: Function,
            required: false
        },
        // 上传成功的回调
        onSuccess: {
            type: Function,
            required: false
        },

        // 自定义的类名
        customClass: {
            type: String,
            required: false,
            default: ''
        },

        // 读取excel表格的范围（默认全部）
        // [n, x] === ['从第n行开始', '倒是第x行结束']
        sheetrange: {
            type: Array,
            required: false,
            defualt: () => [],
            validator: value => Array.isArray(value) && value.length === 2
        },
        validSheetCell: {
            type: Array,
            required: false,
            // 第一个参数是需要校验的单元格，第二个是对应的校验条件文案
            // ['A1', '支付宝交易记录明细查询']
            defualt: () => [],
            validator: value => Array.isArray(value) && value.length === 2
        }
    },
    data() {
        return {
            loading: false,
            excelData: {
                header: null,
                results: null
            }
        };
    },
    methods: {
        generateData({header, results}) {
            this.excelData.header = header;
            this.excelData.results = results;
            this.onSuccess && this.onSuccess(this.excelData);
        },
        // 点击按钮时，触发input上传excel
        handleUpload() {
            this.$refs['excel-upload-input'].click();
        },
        // 选择文件发生改变时触发
        async handleClick(e) {
            try {
                this.loading = true;
                const files = Array.from(e.target.files);
                const output = {header: {}, results: []};
                for (const rawFile of files) {
                    if (!rawFile) continue;

                    const {header, results} = await this.upload(rawFile);
                    output.header = header;
                    output.results.push(...results);
                }
                // 给父组件传递数据
                this.generateData(output);
            } catch (error) {
                console.error('error', error);
            } finally {
                this.loading = false;
            }
        },
        // 选择好文件，确定上传
        async upload(rawFile) {
            try {
                // this.$refs['excel-upload-input'].value = null; // fix can't select the same excel
                let output;
                if (!this.beforeUpload) {
                    output = await this.readerData(rawFile).catch(error => this.onError(error));
                    return output;
                }

                // 校验上传文件的大小
                const before = this.beforeUpload(rawFile);

                // 读取excel表格文件
                if (before) {
                    output = await this.readerData(rawFile).catch(error => this.onError(error));
                }

                return output;
            } catch (error) {
                console.error('error', error);
                throw error;
            }
        },
        readerData(rawFile) {
            // this.loading = true;
            return new Promise((resolve, reject) => {
                // new FileReader 方法
                const reader = new FileReader();

                // 监听onload事件
                reader.onload = e => {
                    // 获取读取的文件数据
                    const data = e.target.result;

                    // typeof data.slice === 'function' ? data.slice() : data
                    // 该语句解决了 `new Uint8Array` 对中文字符串编码的影响

                    // 解决Excel中文乱码问题
                    // https://zhuanlan.zhihu.com/p/139246183
                    const buf = new Uint8Array(typeof data.slice === 'function' ? data.slice() : data);

                    const isUTF8File = isUTF8(buf);

                    const workbook = XLSX.read(isUTF8File ? e.target.result : cptable.utils.decode(936, buf), {
                        // 输入数据编码（请参见下面的输入类型）
                        // https://github.com/SheetJS/sheetjs#input-type
                        type: isUTF8File ? 'array' : 'binary',

                        // https://github.com/SheetJS/sheetjs#parsing-functions
                        // 来自于：https://github.com/SheetJS/sheetjs#parsing-options
                        // if true, plain text parsing will not parse values *
                        // 如果为true，直接读取而不解析表格里面的值
                        raw: true

                        // 解决日期数据错误问题
                        // https://github.com/SheetJS/sheetjs/issues/841
                        // 三个配置项，来自于上面issues，解决日期读取数据错误问题
                        // cellDates: true,
                        // cellNF: false,
                        // cellText: false
                    });

                    // const workbook = XLSX.read(data, {type: 'array'});
                    // 默认只读取第一个页签工作表的数据
                    const firstSheetName = workbook.SheetNames[0];

                    // 解决根据返回读取表格数据
                    // https://github.com/SheetJS/sheetjs

                    const worksheet = workbook.Sheets[firstSheetName];

                    const validSheetCell = this.validSheetCell;

                    if (Array.isArray(validSheetCell) && validSheetCell.length === 2) {
                        try {
                            let celldata = worksheet[validSheetCell[0]].v;
                            // console.log('celldata', celldata);
                            if (typeof celldata === 'string') {
                                celldata = celldata.trim();
                            }
                            if (celldata !== validSheetCell[1]) {
                                this.loading = false;
                                console.error('celldata', celldata);
                                console.error('validSheetCell', validSheetCell);
                                reject(new Error('上传模板不正确'));
                                return;
                            }
                        } catch (error) {
                            console.error('error', error);
                            reject(error);
                        }
                    }

                    // 获取需要读取表格的范围 比如："A1:Q26"
                    const contentRange = this.getSheetConentRange(worksheet);

                    // console.log('读取表格的范围contentRange', contentRange);

                    const header = this.getHeaderRow(worksheet, contentRange);

                    // const results = XLSX.utils.sheet_to_json(worksheet});
                    const results = XLSX.utils.sheet_to_json(worksheet, {range: contentRange});

                    // 去除对象key的空格 给父组件传传送数据

                    resolve({header, results: objKeyTrim(results)});
                };

                // 把获取到的文件读取出来
                reader.readAsArrayBuffer(rawFile);
            });
        },
        // 获取需要读取表格的范围 比如："A1:Q26"
        // 目前默认时，表格指定的范围（只兼容，像支付宝账单、微信账单，表的前面和后面都有不一样的文案的）
        getSheetConentRange(worksheet) {
            const sheetrange = this.sheetrange;

            // 默认读取整个表格
            if (!Array.isArray(sheetrange) || (Array.isArray(sheetrange) && sheetrange.length !== 2)) {
                return worksheet['!ref'];
            }

            const strs = worksheet['!ref'].split(':'); // worksheet['!ref'] === "A1:Q26"
            // const output = strs
            //     .map((item, index) => {
            //         const number = item.replace(/[A-z]/, '') - 0;
            //         if (index === 0) {
            //             return `${item.replace(number, '')}${number + 4}`;
            //         } else {
            //             return `${item.replace(number, '')}${number - 7}`;
            //         }
            //     })
            //     .join(':');
            const output = strs
                .map((item, index) => {
                    const number = item.replace(/[A-z]/g, '') - 0;

                    if (index === 0) {
                        // 从第n行开始
                        return `${item.replace(number, '')}${sheetrange[0]}`;
                    } else {
                        // 倒是第x行结束
                        return `${item.replace(number, '')}${number - sheetrange[1]}`;
                    }
                })
                .join(':');
            return output;
        },
        // 获取表头
        getHeaderRow(worksheet, contentRange) {
            const headers = [];
            // const range = XLSX.utils.decode_range(sheet['!ref']);
            const range = XLSX.utils.decode_range(contentRange);
            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 */
                const cell = worksheet[XLSX.utils.encode_cell({c: C, r: R})];
                /* find the cell in the first row */
                let hdr = 'UNKNOWN ' + C; // <-- replace with your desired default
                if (cell && cell.t) hdr = XLSX.utils.format_cell(cell);
                headers.push(hdr.trim());
            }
            return headers;
        },
        // 判断是否为表格执行的类型文件
        isExcel(file) {
            return /\.(xlsx|xls|csv)$/.test(file.name);
        },
        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';
        }
    }
};
</script>

<style scoped>
.upload-excel {
    display: inline-block;
}

.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>

组件的使用

<ExcelUpload
    :disabled="tableSuccessData.length !== 0"
    :key="uploadKey"
    :on-error="handleError"
    :on-success="handleSuccess"
    :validSheetCell="['A1', '支付宝交易记录明细查询']"
    :sheetrange="[5, 7]"
    type="primary"
    style="min-width: 200px;"
    ><i class="el-icon-upload2"></i>批量导入支付宝交易记录明细</ExcelUpload
>


<script>
methods: {
  handleError(error) {
      this.uploadKey = Math.random();
      console.log('error', error);
      this.$message({message: error, type: 'error'});
  },
  handleSuccess({results, header}) {
      console.log('results', results);
      console.log('header', header);
      this.uploadKey = Math.random();
      const newObj = this.objKeys;

      this.tableSuccessData = results.map(item => {
          const newChild = {};
          Object.keys(item).forEach(child => {
              newChild[newObj[child]] = item[child];
          });
          return newChild;
      });

      this.formSuccess = {
          ...this.formSuccess,
          count: this.tableSuccessData.length // 总条数
      };
  }
}
</script>