Springboot使用EasyExcel导入导出Excel文件

1，准备Excel文件和数据库表结果

在这里插入图片描述

2，导入代码

1，引入依赖

        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.0</version>
        </dependency>

2，创建请求body

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 * Description
 *
 * @author WangYaoLong
 * @createdate 2023/11/01 0001 11:43
 */
@Data
public class StudentImportExcelForm {
    @ExcelProperty(value = "学生姓名", index = 0)
    private String name;

    @ExcelProperty(value = "性别", index = 1)
    private String sex;

    @ExcelProperty(value = "学号", index = 2)
    private String stuId;

    @ExcelProperty(value = "身份证号", index = 3)
    private String identityNum;

    @ExcelProperty(value = "所在班级", index = 4)
    private String classesId;

    @ExcelProperty(value = "简介", index = 5)
    private String remarks;

    @ExcelProperty(value = "生日", index = 6)
    private String birthday;
}

3，Excel文件数据解析

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.wang.dog.exception.BusinessException;
import com.wang.dog.pojo.form.StudentImportExcelForm;
import com.wang.dog.service.StudentService;
import com.wang.dog.utils.SpringContextHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.transaction.annotation.Transactional;
import java.util.ArrayList;
import java.util.List;

/**
 * Description
 *
 * @author WangYaoLong
 * @createdate 2023/11/01 0001 11:42
 */
@Slf4j
public class StudentImportExcelListener extends AnalysisEventListener<StudentImportExcelForm> {

    private final List<StudentImportExcelForm> list = new ArrayList<>();

    private final StudentService studentService;

    public StudentImportExcelListener() {
        this.studentService = SpringContextHolder.getBean(StudentService.class);
    }

    private StringBuilder msg = new StringBuilder();

    @Override
    public void invoke(StudentImportExcelForm studentExcelForm, AnalysisContext analysisContext) {
        log.info("学生信息" + studentExcelForm);
        checkStudentFiled(studentExcelForm);
        list.add(studentExcelForm);
    }

    /**
     * 校验学生信息
     *
     * @param studentExcelForm 学生导入表单
     */
    private void checkStudentFiled(StudentImportExcelForm studentExcelForm) {
        String name = studentExcelForm.getName();
        String stuId = studentExcelForm.getStuId();
        String classesId = studentExcelForm.getClassesId();
        String sex = studentExcelForm.getSex();
        String identityNum = studentExcelForm.getIdentityNum();

        if (StringUtils.isBlank(name)) {
            msg.append("学生姓名不能为空！");
        }
        if (StringUtils.isBlank(stuId)) {
            msg.append("学生学号不能为空！");
        }
        if (StringUtils.isBlank(classesId)) {
            msg.append("学生所在班级不能为空！");
        }
        if (StringUtils.isBlank(sex)) {
            msg.append("学生性别不能为空！");
        }
        if (StringUtils.isBlank(identityNum)) {
            msg.append("学生身份证信息不能为空！");
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        if (StringUtils.isNotBlank(msg)) {
            throw new ExcelAnalysisException("导入失败!<br/>" + msg.toString());
        }
        if (CollectionUtils.isEmpty(list)) {
            throw new BusinessException("导入文件为空");
        }
        // 保存学生信息
        saveData();
        // 清空集合和异常信息
        msg = null;
        list.clear();
    }
    
    /**
     * 保存 加上存储数据库
     */
    @Transactional(rollbackFor = Exception.class)
    public void saveData() {
        studentService.importExcelData(list);
    }
}

4，在StudentService中新增接口以及实现类保存数据

接口方法

   /**
     * 导入Excel学生信息
     *
     * @param list 学生信息集合
     */
    void importExcelData(List<StudentImportExcelForm> list);

实现类

    @Override
    public void importExcelData(List<StudentImportExcelForm> list) {
        List<Student> studentList = new ArrayList<>();
        list.forEach(s -> {
            Student student = new Student();
            student.setName(s.getName());
            student.setSex(Integer.parseInt(String.valueOf(s.getSex().equals("男") ? 0 : 1)));
            student.setStuId(s.getStuId());
            student.setIdentityNum(s.getIdentityNum());
            student.setClassesId(s.getClassesId());
            student.setCreateTime(new Date());
            student.setUpdateTime(new Date());
            student.setCreateBy(s.getName());
            student.setUpdateBy(s.getName());
            student.setDelFlag(0);
            student.setRemarks(s.getRemarks());
            student.setBirthday(s.getBirthday());
            studentList.add(student);
        });
        // 批量保存学生信息
        this.saveBatch(studentList);
    }

5，新增导入Excel接口

@Slf4j
@RequestMapping("/student")
@RestController
public class StudentController {

    @PostMapping("/import")
    public Result uploadStudentInfo(MultipartFile file) throws IOException {
        InputStream is = file.getInputStream();
        EasyExcel.read(is, StudentImportExcelForm.class, new StudentImportExcelListener())
                .sheet(0)
                .headRowNumber(1)
                .doRead();
        return Result.ok("success");
    }
}

6，测试

在这里插入图片描述

3，导出Excel

1，导出Excel表单请求

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * Description
 *
 * @author WangYaoLong
 * @createdate 2023/11/01 0001 13:03
 */
@Data
public class StudentExportExcelForm {
    @ApiModelProperty(value = "姓名", example = "")
    private String name;

    @ApiModelProperty(value = "身份证号", example = "")
    private String identityNum;

    @ApiModelProperty(value = "所在班级", example = "")
    private String classesId;

    @ApiModelProperty(value = "性别", example = "")
    private String sex;

}

2，返回表单

@Data
@HeadRowHeight(15)	// 高度
@HeadFontStyle(fontHeightInPoints = 10)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)	// 字体居中显示
public class StudentExportExcelVo {
    @ExcelProperty(value = "学生姓名", order = 1)
    @ColumnWidth(20)	// excel 单元格间距
    private String name;

    @ExcelProperty(value = "性别", order = 2)
    @ColumnWidth(20)
    private String sex;

    @ExcelProperty(value = "学号", order = 3)
    @ColumnWidth(20)
    private String stuId;

    @ExcelProperty(value = "身份证号", order = 4)
    @ColumnWidth(20)
    private String identityNum;

    @ExcelProperty(value = "所在班级", order = 5)
    @ColumnWidth(20)
    private String classesId;

    @ExcelProperty(value = "简介", order = 6)
    @ColumnWidth(20)
    private String remarks;

    @ExcelProperty(value = "生日", order = 7)
    @ColumnWidth(20)
    private String birthday;
}

3，导出查询接口

    @GetMapping("/export")
    public void exportStudentExcel(StudentExportExcelForm studentExportExcelForm, HttpServletResponse response) throws Exception {
        List<StudentExportExcelVo> list = studentService.exportStudent(studentExportExcelForm);
        // 不带表头
        //DownExcelUtils.download(response, StudentExportExcelVo.class, list, "学生信息导出");
        // 增加表头
        DownExcelUtils.download(response, StudentExportExcelVo.class, list, "学生信息导出", ExportStudentExcelHandler.class);
    }

4，学生数据查询

    /**
     * 查询学生信息
     *
     * @param studentExportExcelForm 学生请求表单
     * @return List
     */
    List<StudentExportExcelVo> exportStudent(StudentExportExcelForm studentExportExcelForm);

实现类：

    @Override
    public List<StudentExportExcelVo> exportStudent(StudentExportExcelForm form) {
        List<StudentExportExcelVo> list = Lists.newArrayList();
        LambdaQueryWrapper<Student> studentQueryWrapper = Wrappers.<Student>lambdaQuery()
                .orderByAsc(Student::getBirthday);
        if (StringUtils.isNotBlank(form.getName())) {
            studentQueryWrapper.like(Student::getName, form.getName());
        }
        if (StringUtils.isNotBlank(form.getIdentityNum())) {
            studentQueryWrapper.eq(Student::getIdentityNum, form.getIdentityNum());
        }
        if (StringUtils.isNotBlank(form.getClassesId())) {
            studentQueryWrapper.eq(Student::getClassesId, form.getClassesId());
        }
        if (StringUtils.isNotBlank(form.getSex())) {
            studentQueryWrapper.eq(Student::getSex, form.getSex().equals("男") ? 0 : 1);
        }
        List<Student> studentList = studentMapper.selectList(studentQueryWrapper);
        if (CollectionUtils.isNotEmpty(studentList)) {
            studentList.forEach(s -> {
                StudentExportExcelVo vo = new StudentExportExcelVo();
                BeanUtils.copyProperties(s, vo);
                vo.setSex(s.getSex() == 0 ? "男" : "女");
                list.add(vo);
            });
        }
        return list;
    }

5，导出工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.handler.WriteHandler;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

/**
 * @author: WangYaoLong
 * @date: 2022/8/1
 * @description: excel导出工具类
 */
public class DownExcelUtils {

	/**
	 * 带有表头提示
	 *
	 * @param response
	 * @param t
	 * @param list
	 * @param fileName
	 * @param z
	 * @throws IOException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 */
	public static void download(HttpServletResponse response, Class t, List list, String fileName, Class z) throws IOException, IllegalAccessException, InstantiationException {
		// 设置文本内省
		response.setContentType("application/vnd.ms-excel");
		response.setCharacterEncoding("utf-8");
		response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
		EasyExcel.write(response.getOutputStream(), t)
				.sheet(fileName)
				//设置拦截器或自定义样式
				.registerWriteHandler((WriteHandler) z.newInstance())
				//这里1代表第二行开始
				.relativeHeadRowIndex(1)
				.doWrite(list);
	}

	/**
	 * 不带表头提示
	 *
	 * @param response
	 * @param t
	 * @param list
	 * @param fileName
	 * @throws IOException
	 */
	public static void download(HttpServletResponse response, Class t, List list, String fileName) throws IOException {
		response.setContentType("application/vnd.ms-excel");
		response.setCharacterEncoding("utf-8");
		response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
		EasyExcel.write(response.getOutputStream(), t)
				.sheet(fileName)
				.doWrite(list);
	}
}

6，表头类

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExportStudentExcelHandler implements SheetWriteHandler {
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);

        CellStyle cellStyle = workbook.createCellStyle();
        // 自动换行
        cellStyle.setWrapText(true);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //第一行
        //设置标题
        Row row = sheet.createRow(0);
        row.setHeight((short) 900);
        Cell cell = row.createCell(0);
        cell.setCellStyle(cellStyle);
        cell.setCellValue("这是学生信息导出表格(注:xxxxxxxxx数据内容不可更改)");
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 7));
    }
}