java+vue前后端分离：关于excel导入的几种方式

一、后端处理

前置处理

将MultipartFile转化成File，需要先将文件保存至本地。【是否需要转化视情况而定】

public File saveFile(MultipartFile file){
        String dirName = uploadPath; //本地上传路径
        File dirUrl = new File(dirName);
        if (!dirUrl.exists()) {
            dirUrl.mkdirs();
        }
        String fileUrl = dirUrl + "/" + file.getOriginalFilename();

		//将文件保存至本地
        File serverFile = new File(fileUrl);
        FileUtil.uploadFile(file.getBytes(), serverFile);
        return serverFile;
}

1、.xlsx (XSSF)

使用apache.poi，获取单个单元格的值

需要先将MultipartFile 转成File

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

 public String importFile(MultipartFile file) throws Exception {
 		File serverFile = saveFile(file);
        /*解析xlxs*/
        XSSFWorkbook book;

        InputStream is = new FileInputStream(serverFile);
        try{
            book = new XSSFWorkbook(is);
        }catch (Exception e){
            return "请导入.xlxs类型的文件！";
        }

		//基本操作
		XSSFSheet sheet = book.getSheetAt(0);  //获取sheet
		XSSFRow row = sheet.getRow(0)   //获取某行
		XSSFCell firColCell = sheet.getRow(0).getCell(0).getStringCellValue();  //获取0行0列的单元格数据
}

2、.xls (HSSF)

解析方法1：使用jxl，获取单个单元格的值

 public void batchQuery(MultipartFile file) throws Exception {
        Workbook workbook = Workbook.getWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheet(0);
        int rows = sheet.getRows();
        sheet.getCell(0, 0).getContents(); //获取0行0列的单元格内容
    }

解析方法2：使用hutool直接解析成对象

需要先将MultipartFile 转成File

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;

 public String importFile(MultipartFile file) throws Exception {
 		File serverFile = saveFile(file);
 		
 		InputStream is = new FileInputStream(serverFile);
        ExcelReader reader = ExcelUtil.getReader(is);
        //将列名与对象属性相关联
        reader.addHeaderAlias("车牌号", "carNumber");
        reader.addHeaderAlias("VIN号", "vin");
        reader.addHeaderAlias("IMEI号", "imei");
        //转化为对象List
        List<Equipment> all = reader.readAll(Equipment.class);
}

二、前端读取

前端将excel转化为对象数组

importf(obj) {

                const self= this;

                let _this = this;
                _this.exportData = [];
                let inputDOM = this.$refs.inputer;   // 通过DOM取文件数据
                this.file = event.currentTarget.files[0];
                var rABS = false; //是否将文件读取为二进制字符串
                var f = this.file;
                var reader = new FileReader();

                FileReader.prototype.readAsBinaryString = function(f) {
                    var binary = "";
                    var rABS = false; //是否将文件读取为二进制字符串
                    var pt = this;
                    var wb; //读取完成的数据
                    var outdata;
                    var reader = new FileReader();
                    var exp = [];

                    reader.onload = function(e) {
                        console.info(1);
                        var bytes = new Uint8Array(reader.result);
                        var length = bytes.byteLength;
                        for(var i = 0; i < length; i++) {
                            binary += String.fromCharCode(bytes[i]);
                        }

                        var XLSX = require('xlsx');

                        if(rABS) {
                            console.info(2);
                            wb = XLSX.read(btoa(fixdata(binary)), { //手动转化
                                type: 'base64'
                            });
                        } else {
                            console.info(3);
                            wb = XLSX.read(binary, {
                                type: 'binary'
                            });
                        }

                        // outdata就是你想要的东西 excel导入的数据
                        outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
                        
                        // excel 数据再处理,转化为对象数组
                        let arr = []
                        outdata.map(v => {
                            let obj = {}
                            obj.hphm=v.号牌号码  //v.[列名]
                            arr.push(obj)
                        })
                        _this.accountList = [...arr];
                    }
                    reader.readAsArrayBuffer(f);
                }
                if(rABS) {
                    reader.readAsArrayBuffer(f);
                } else {
                    reader.readAsBinaryString(f);
                }
            },