前端将页面table或数据导出为Excel(.xlsx)&&导入Excel为json数组

今天药忘吃喽~ 2022-09-10 01:20 212阅读 0赞

老生常谈了，但还是谈谈  
最近上新项目（react），里面有导出功能 ，[就这个库][Link 1]，这是[演示demo][demo].

--------------------

import {  export_table_to_excel, export_json_to_excel, read} from '@/utils/excel'
    <div><Button @click="exportExcel">点我导出Excel</Button></div>
    <div><Button>点我上传excel<input @change="uploadFileFn($event)" type="file" name="" id=""></Button></div>
    <div>
    	<el-table
    		  id='zhhTable'
    		  :data="tableData"
    		  style="width: 100%">
    	  <el-table-column
    		    prop="性別"
    		    label="性別"
    		    width="180">
    	  </el-table-column>
    	  <el-table-column
    		    prop="姓名"
    		    label="姓名"
    		    width="180">
    	  </el-table-column>
    	  <el-table-column
    		    prop="身高"
    		    label="身高">
    	  </el-table-column>
    	</el-table>
    </div>
    data(){ 
    	return { 
         tableData: [
             { 
               姓名: '2345',
               性別: '4567',
               身高: '34567'
             }
         ]
    	};
    }
    // 导出
    exportExcel () { 
        export_table_to_excel('zhhTable', 'my name is zhh.xlsx')   // 导出页面id为‘zhhTable’的table数据
        export_json_to_excel({ data: [{  姓名: "zhanghuanhuan", 性別: "女", 身高: "162" }],key: ["姓名", "性別", "身高"], title: '我是title', filename: '我是zhh', autoWidth: 300})    // 导出自己想要的数据
    }
    // 上传
    uploadFileFn (e) { 
        var files = e.target.files, f = files[0];
        var reader = new FileReader();
        var _this = this
        reader.onload = function(e) { 
          var data = new Uint8Array(e.target.result);
          console.log(read(data, 'array'))
          _this.tableData = read(data, 'array').results
        };
        reader.readAsArrayBuffer(f);
    }
    // 方法封装 utils/excel
    import XLSX from 'xlsx';
    export const export_table_to_excel= (id, filename) => { 
        const table = document.getElementById(id);
        const wb = XLSX.utils.table_to_book(table);
        XLSX.writeFile(wb, filename);
    }
    export const export_json_to_excel = ({ data, key, title, filename, autoWidth}) => { 
        const wb = XLSX.utils.book_new();
        data.unshift(title);
        const ws = XLSX.utils.json_to_sheet(data, { header: key, skipHeader: true});
        if(autoWidth){ 
            const arr = json_to_array(key, data);
            auto_width(ws, arr);
        }
        XLSX.utils.book_append_sheet(wb, ws, filename);
        XLSX.writeFile(wb, filename + '.xlsx');
    }
    function json_to_array(key, jsonData){ 
        return jsonData.map(v => key.map(j => {  return v[j] }));
    }
    function auto_width(ws, data){ 
        /*set worksheet max width per col*/
        const colWidth = data.map(row => row.map(val => { 
            /*if null/undefined*/
            if (val == null) { 
                return { 'wch': 10};
            }
            /*if chinese*/
            else if (val.toString().charCodeAt(0) > 255) { 
                return { 'wch': val.toString().length * 2};
            } else { 
                return { 'wch': val.toString().length};
            }
        }))
        /*start in the first row*/
        let result = colWidth[0];
        for (let i = 1; i < colWidth.length; i++) { 
            for (let j = 0; j < colWidth[i].length; j++) { 
                if (result[j]['wch'] < colWidth[i][j]['wch']) { 
                    result[j]['wch'] = colWidth[i][j]['wch'];
                }
            }
        }
        ws['!cols'] = result;
    }
    function 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
    }
    export const read = (data, type) => { 
        /* if type == 'base64' must fix data first */
        // const fixedData = fixdata(data)
        // const workbook = XLSX.read(btoa(fixedData), { type: 'base64' })
        const workbook = XLSX.read(data, {  type: type });
        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];
        const header = get_header_row(worksheet);
        const results = XLSX.utils.sheet_to_json(worksheet);
        return { header, results};
    }
    export default { 
      export_table_to_excel,
      export_json_to_excel,
      read
    }

[Link 1]: https://github.com/SheetJS/sheetjs
[demo]: https://sheetjs.com/demo