欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-20 20:31:22
...

一、后端处理

前置处理

将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);
                }
            },