基于SpringBoot-Vue-EasyExcel实现的Excel文件导出+导入

一：easyExcel依赖

         <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>

二：javaBean

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Users {
    
    private String id;

    @ExcelProperty(value = {"用户姓名"}, index = 1)
    private String name;

    @ExcelProperty(value = {"用户年龄"}, index = 2)
    private String age;

}

三：Controller

@PostMapping ("/excel")
    public void export(HttpServletResponse response, HttpServletRequest request) {
        ServletOutputStream outputStream = null;

        // excel头策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 11);
        headWriteFont.setBold(false);
        headWriteCellStyle.setWriteFont(headWriteFont);

        // excel内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 11);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        // 设置handler
        HorizontalCellStyleStrategy styleStrategy =
            new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        // Data
        List<Users> list = new ArrayList<>();
        list.add(new Users("1", "小明", "11"));
        list.add(new Users("2", "小红", "11"));
        list.add(new Users("3", "小白", "11"));
        list.add(new Users("4", "小绿", "11"));
        list.add(new Users("5", "小灰", "11"));

        try {
            outputStream = response.getOutputStream();
            EasyExcel.write(outputStream, Users.class)
                .sheet()
                .registerWriteHandler(styleStrategy)
                .doWrite(list);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

四：vue代码

       exportExcel() {
            axios({
                    method: 'post',
                    url: '/excel',
                    responseType: 'blob',
                }
            ).then(function (response) {
                let blob = new Blob([response.data], { type: 'application/vnd.ms-excel;charset=utf-8' })
                let downloadElement = document.createElement('a');
                let href = window.URL.createObjectURL(blob); //创建下载的链接
                downloadElement.href = href;
                downloadElement.download = '哈哈哈.xlsx'; //下载后文件名
                document.body.appendChild(downloadElement);
                downloadElement.click(); //点击下载
                document.body.removeChild(downloadElement); //下载完成移除元素
                window.URL.revokeObjectURL(href); //释放掉blob对象

            }).catch(function (error) {
                this.$message.error(error)
            });
        }

五：导入

5.1：excel监听器

public class WebStudentListener<T> extends AnalysisEventListener<T> {
    public final Logger log = LoggerFactory.getLogger(this.getClass());

    public List<T> list = new ArrayList<>();
    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        log.info("读取表格[{}]",t);
        System.out.println(t.toString()+"t");
        list.add(t);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
    public List<T> getExcelData(){
        return list;
    }
}

5.2 接口

    @PostMapping("/redexcel")
    @ResponseBody
    public List<Users> redExcel(HttpServletResponse response, HttpServletRequest request, MultipartFile multipartFile) throws IOException {
        InputStream inputStream = multipartFile.getInputStream();
        WebStudentListener<Users> studentWebStudentListener = new WebStudentListener<>();
        EasyExcel.read(inputStream,Users.class,studentWebStudentListener).sheet().doRead();
        List<Users> excelData = studentWebStudentListener.getExcelData();
        return excelData;
    }

5.3 前端组件element upload

        <el-upload
            class="upload-demo"
            action="http://localhost:8082/redexcel"
            :on-preview="handlePreview"
            :on-remove="handleRemove"
            :on-success="uploadSuccess"
            multiple
            name="multipartFile"
            :limit="3">
            <el-button size="small" type="primary">点击上传</el-button>
            <div slot="tip" class="el-upload__tip">只能上传jpg/png文件，且不超过500kb</div>
        </el-upload>

           uploadSuccess (response, file, fileList) {
            console.log(response)
           },
           
           handleRemove (file, fileList) {
            console.log(file, fileList)
           },
           
           handlePreview (file) {
            console.log(file)
           },