SpringBoot怎么实现导入导出Excel文档

这篇文章主要介绍了SpringBoot怎么实现导入导出Excel文档的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇SpringBoot怎么实现导入导出Excel文档文章都会有所收获，下面我们一起来看看吧。

一、添加poi的maven依赖

    org.apache.poi    poi    3.13    org.apache.poi    poi-ooxml    3.13

二、自定义注解(Excel属性标题、位置等)

package com.cloud.core.annotation;import java.lang.annotation.*;/** * 自定义实体类所需要的bean(Excel属性标题、位置等) * Copyright: Copyright (C) 2021 DLANGEL, Inc. All rights reserved. * Company: 大连安琪科技有限公司 * * @author Rex * @since 2021/5/19 9:30 */@Target({ElementType.FIELD})@Retention(RetentionPolicy.RUNTIME)@Documentedpublic @interface ExcelColumn {    /**     * Excel标题     *     * @return     * @author Rex     */    String value() default "";    /**     * Excel从左往右排列位置，第一个是0     *     * @return     * @author Rex     */    int col() default 0;}

三、CustomExcelUtils编写

package com.cloud.core.utils;import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;import com.cloud.core.annotation.ExcelColumn;import com.cloud.core.common.CommonConst;import org.apache.commons.lang.BooleanUtils;import org.apache.commons.lang.CharUtils;import org.apache.commons.lang.StringUtils;import org.apache.commons.lang.math.NumberUtils;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.http.MediaType;import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Constructor;import java.lang.reflect.Field;import java.math.BigDecimal;import java.net.URLEncoder;import java.util.*;import java.util.concurrent.atomic.AtomicInteger;import java.util.stream.Collectors;import java.util.stream.Stream;/** * 自定义导入导出Excel文件类 * Copyright: Copyright (C) 2021 DLANGEL, Inc. All rights reserved. * Company: 大连安琪科技有限公司 * * @author Rex * @since 2021/5/19 9:31 */public class CustomExcelUtils {    private final static Logger log = LoggerFactory.getLogger(CustomExcelUtils.class);    private final static String EXCEL2003 = "xls";    private final static String EXCEL2007 = "xlsx";    /**     * 读取Excel     *     * @param path     为了测试文件用，实际为空     * @param cls      类     * @param startRow 起始行     * @param file     文件     * @return     * @author Rex     */    public static  List readExcel(String path, Class cls, int startRow, MultipartFile file) {        String fileName = file.getOriginalFilename();        if (!fileName.matches(CommonConst.Regex.FILE_EXT_XLS) && !fileName.matches(CommonConst.Regex.FILE_EXT_XLSX)) {            log.error("上传文件格式不正确");        }        List dataList = new ArrayList<>();        Workbook workbook = null;        try {            InputStream is = file.getInputStream();            if (fileName.endsWith(EXCEL2007)) {//                FileInputStream is = new FileInputStream(new File(path));                workbook = new XSSFWorkbook(is);            }            if (fileName.endsWith(EXCEL2003)) {//                FileInputStream is = new FileInputStream(new File(path));                workbook = new HSSFWorkbook(is);            }            if (workbook != null) {                //类映射  注解 value-->bean columns                Map> classMap = new HashMap<>();                List fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());                fields.forEach(                        field -> {                            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);                            if (annotation != null) {                                String value = annotation.value();                                if (StringUtils.isBlank(value)) {                                    // return起到的作用和continue是相同的 语法                                    return;                                }                                if (!classMap.containsKey(value)) {                                    classMap.put(value, new ArrayList<>());                                }                                field.setAccessible(true);                                classMap.get(value).add(field);                            }                        }                );                //索引-->columns                Map> reflectionMap = new HashMap<>(16);                //默认读取第一个sheet                Sheet sheet = workbook.getSheetAt(0);                boolean firstRow = true;                for (int i = startRow; i <= sheet.getLastRowNum(); i++) {                    Row row = sheet.getRow(i);                    // 提取注解                    if (firstRow) {                        for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {                            Cell cell = row.getCell(j);                            String cellValue = getCellValue(cell);                            if (classMap.containsKey(cellValue)) {                                reflectionMap.put(j, classMap.get(cellValue));                            }                        }                        if (reflectionMap.size() > 0) {                            firstRow = false;                        }                    } else {                        //忽略空白行                        if (row == null) {                            continue;                        }                        try {                            T t = cls.newInstance();                            //判断是否为空白行                            boolean allBlank = true;                            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {                                if (reflectionMap.containsKey(j)) {                                    Cell cell = row.getCell(j);                                    String cellValue = getCellValue(cell);                                    if (StringUtils.isNotBlank(cellValue)) {                                        allBlank = false;                                    }                                    List fieldList = reflectionMap.get(j);                                    fieldList.forEach(                                            x -> {                                                try {                                                    handleField(t, cellValue, x);                                                } catch (Exception e) {                                                    log.error(String.format("reflect field:%s value:%s exception!", x.getName(), cellValue), e);                                                }                                            }                                    );                                }                            }                            if (!allBlank) {                                dataList.add(t);                            } else {                                log.warn(String.format("row:%s is blank ignore!", i));                            }                        } catch (Exception e) {                            log.error(String.format("parse row:%s exception!", i), e);                        }                    }                }            }        } catch (Exception e) {            log.error(String.format("parse excel exception!"), e);        } finally {            if (workbook != null) {                try {                    workbook.close();                } catch (Exception e) {                    log.error(String.format("parse excel exception!"), e);                }            }        }        return dataList;    }    private static  void handleField(T t, String value, Field field) throws Exception {        Class type = field.getType();        if (type == null || type == void.class || StringUtils.isBlank(value)) {            return;        }        if (type == Object.class) {            field.set(t, value);            //数字类型        } else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) {            if (type == int.class || type == Integer.class) {                field.set(t, NumberUtils.toInt(value));            } else if (type == long.class || type == Long.class) {                field.set(t, NumberUtils.toLong(value));            } else if (type == byte.class || type == Byte.class) {                field.set(t, NumberUtils.toByte(value));            } else if (type == short.class || type == Short.class) {                field.set(t, NumberUtils.toShort(value));            } else if (type == double.class || type == Double.class) {                field.set(t, NumberUtils.toDouble(value));            } else if (type == float.class || type == Float.class) {                field.set(t, NumberUtils.toFloat(value));            } else if (type == char.class || type == Character.class) {                field.set(t, CharUtils.toChar(value));            } else if (type == boolean.class) {                field.set(t, BooleanUtils.toBoolean(value));            } else if (type == BigDecimal.class) {                field.set(t, new BigDecimal(value));            }        } else if (type == Boolean.class) {            field.set(t, BooleanUtils.toBoolean(value));        } else if (type == Date.class) {            //            field.set(t, value);        } else if (type == String.class) {            field.set(t, value);        } else {            Constructor constructor = type.getConstructor(String.class);            field.set(t, constructor.newInstance(value));        }    }    private static String getCellValue(Cell cell) {        if (cell == null) {            return "";        }        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {            if (HSSFDateUtil.isCellDateFormatted(cell)) {                return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();            } else {                return new BigDecimal(cell.getNumericCellValue()).toString();            }        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {            return StringUtils.trimToEmpty(cell.getStringCellValue());        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {            return StringUtils.trimToEmpty(cell.getCellFormula());        } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {            return "";        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {            return String.valueOf(cell.getBooleanCellValue());        } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {            return "ERROR";        } else {            return cell.toString().trim();        }    }    public static  void writeExcel(HttpServletResponse response, List dataList, Class cls) {        Field[] fields = cls.getDeclaredFields();        List fieldList = Arrays.stream(fields)                .filter(field -> {                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);                    if (annotation != null && annotation.col() > 0) {                        field.setAccessible(true);                        return true;                    }                    return false;                }).sorted(Comparator.comparing(field -> {                    int col = 0;                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);                    if (annotation != null) {                        col = annotation.col();                    }                    return col;                })).collect(Collectors.toList());        Workbook wb = new XSSFWorkbook();        Sheet sheet = wb.createSheet("Sheet1");        AtomicInteger ai = new AtomicInteger();        {            Row row = sheet.createRow(ai.getAndIncrement());            AtomicInteger aj = new AtomicInteger();            //写入头部            fieldList.forEach(field -> {                ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);                String columnName = "";                if (annotation != null) {                    columnName = annotation.value();                }                Cell cell = row.createCell(aj.getAndIncrement());                CellStyle cellStyle = wb.createCellStyle();                cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());                cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);                cellStyle.setAlignment(CellStyle.ALIGN_CENTER);                Font font = wb.createFont();                font.setBoldweight(Font.BOLDWEIGHT_NORMAL);                cellStyle.setFont(font);                cell.setCellStyle(cellStyle);                cell.setCellValue(columnName);            });        }        if (CollectionUtils.isNotEmpty(dataList)) {            dataList.forEach(t -> {                Row row1 = sheet.createRow(ai.getAndIncrement());                AtomicInteger aj = new AtomicInteger();                fieldList.forEach(field -> {                    Class type = field.getType();                    Object value = "";                    try {                        value = field.get(t);                    } catch (Exception e) {                        e.printStackTrace();                    }                    Cell cell = row1.createCell(aj.getAndIncrement());                    if (value != null) {                        if (type == Date.class) {                            cell.setCellValue(value.toString());                        } else {                            cell.setCellValue(value.toString());                        }                        cell.setCellValue(value.toString());                    }                });            });        }        //冻结窗格        wb.getSheet("Sheet1").createFreezePane(0, 1, 0, 1);        //浏览器下载excel        buildExcelDocument("导出数据.xlsx", wb, response);        //生成excel文件//        buildExcelFile(".\default.xlsx", wb);    }    /**     * 浏览器下载excel     *     * @param fileName     * @param wb     * @param response     */    private static void buildExcelDocument(String fileName, Workbook wb, HttpServletResponse response) {        try {            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));            response.flushBuffer();            wb.write(response.getOutputStream());        } catch (IOException e) {            e.printStackTrace();        }    }    /**     * 生成excel文件     *     * @param path 生成excel路径     * @param wb     */    private static void buildExcelFile(String path, Workbook wb) {        File file = new File(path);        if (file.exists()) {            file.delete();        }        try {            wb.write(new FileOutputStream(file));        } catch (Exception e) {            e.printStackTrace();        }    }}

四、定义导出实体类

主要是使用这里的@ExcelColumn注解，其中的col从0开始的。

package com.cloud.library.model.role;import com.cloud.core.annotation.ExcelColumn;import lombok.Data;/** * 导入角色用 * Copyright: Copyright (C) 2021 DLANGEL, Inc. All rights reserved. * Company: 大连安琪科技有限公司 * * @author Rex * @since 2021/5/19 16:13 */@Datapublic class SysRoleExcel {    @ExcelColumn(value = "姓名", col = 1)    private String name;    @ExcelColumn(value = "描述", col = 2)    private String description;}

五、Controller层代码编写

//region 导入数据    /**     * 导入数据     *     * @param file     * @return     * @author Rex     */    @RequestMapping(value = "/readExcel", method = RequestMethod.POST)    public void readExcel(@RequestParam(value = "uploadFile", required = false) MultipartFile file) {        List list = CustomExcelUtils.readExcel("", SysRoleExcel.class, 0, file);        List sysRoleList = new ArrayList<>();        list.forEach(e -> {            SysRole sysRole = new SysRole();            BeanUtils.copyProperties(e, sysRole);            sysRoleList.add(sysRole);        });        sysRoleService.saveBatch(sysRoleList);    }    // endregion

这里发现了，这个saveBatch可以直接使用雪花的id来保存数据，因为这里用的是mybatis-plus，单条数据保存使用的是它的配置。然后试了下，批量导入也是可以的，另外，这个批量保存，理论上没有条数限制，这个还等待后续测试。

关于"SpringBoot怎么实现导入导出Excel文档"这篇文章的内容就介绍到这里，感谢各位的阅读！相信大家对"SpringBoot怎么实现导入导出Excel文档"知识都有一定的了解，大家如果还想学习更多知识，欢迎关注行业资讯频道。