wisdomsell-day8-导入导出_wisdom dmain-优快云博客

本文链接：https://blog.youkuaiyun.com/wgl04193410/article/details/96467927

本文介绍如何使用Java操作Excel文件，包括创建和读取Excel文件，并详细讲解了使用EasyPOI简化这些操作的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、引入poi需要的jar包

org.apache.poi poi 3.11 org.apache.poi poi-ooxml 3.11

1.创建Excel(完成99乘法表)
package cn.itsource.pss.common;
…
public class ExcelTest {

//创建一个Excel文件
//在文件里加一个99乘法法
//把这文件保存在项目中
@Test
public void testCreateExcel() throws Exception{
    //1.创建一个Excel文件(内存中)
    SXSSFWorkbook wb = new SXSSFWorkbook();
    //2.创建一张表
    Sheet sheet = wb.createSheet("99乘法表");
    //3.创建行
    for (int i = 1; i <= 9; i++) {
        Row row = sheet.createRow(i-1);
        //4.创建列(格子)
        for (int j = 1; j <= i; j++) {
            Cell cell = row.createCell(j-1);
            //5.格子中加数据
            cell.setCellValue(i+"*"+j+"="+(i*j));
        }
    }
    //从内存中写出来
    FileOutputStream out = new FileOutputStream("99.xlsx");
    wb.write(out);
    out.close();
}

}

2.读取Excel
可以使用我们已经准备好的excel文件做测试
/**

读取我们使用相应的方案
@throws Exception
*/
@Test
public void readExcel() throws Exception{
File file = new File(“employee-3.xlsx”);
FileInputStream fis = new FileInputStream(file);
//1.读取一个Excel文件(内存中)
Workbook wb = new XSSFWorkbook(fis);
//2.拿到第个sheet表
Sheet sheet = wb.getSheetAt(0);
//3.拿到wb中的行(不要拿头部)
int lastRowNum = sheet.getLastRowNum();
for (int i = 1; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
//4.拿到每一列(格子)
short lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
Cell cell = row.getCell(j);
System.out.print(cell.getStringCellValue()+" ");
}
System.out.println();
}
}

二、EasyPOI使用
文档:http://easypoi.mydoc.io/
引入easypoi的jar包
特别注意:把之前咱们的POI引入去掉(有冲突)

cn.afterturn easypoi-base 3.2.0 cn.afterturn easypoi-web 3.2.0 cn.afterturn easypoi-annotation 3.2.0

1.准备domain
2.POIEmployee
@ExcelTarget(“emp”)public class PoiEmployee {
@Excel(name = “名称”)
private String name;
@Excel(name=“邮件”,width = 25)
private String email;
@Excel(name=“年龄”)
private Integer age;
@Excel(name=“性别”,replace = {“男_true”,“女_false”})
private Boolean sex;
@Excel(name=“出生日期”,format = “yyyy-MM-dd”)
private Date bornDate = new Date();
//type=2:代表这是一张图片
@Excel(name = “头像”,type = 2,height = 25)
private String headImage;
@ExcelEntity
private PoiDepartment department;
// 不要忘了加上getter,setter
}
3.POIDepartment
@ExcelTarget(“dept”)public class PoiDepartment {

@Excel(name="部门名称_emp,名称_dept")
private String name;
@Excel(name="部门地址_emp,地址_dept")
private String address;
@Excel(name="邮件_dept")
private String email;
// 不要忘了加上getter,setter

}
4. EasyPOI创建excel文件
/**list的值自己准备一下

new ExportParams(title,sheetName):导出的属性设置
title:表头名称
sheetName：sheet表的名称
PoiEmployee .class：导出的实体类型
list:导出的数据（List）
*/Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(“部门名称”,“bbb”),
PoiEmployee.class, list);FileOutputStream fos = new FileOutputStream(“poidept.xlsx”);
workbook.write(fos);
fos.close();
5.EasyPOI读取excel文件
ImportParams params = new ImportParams();
//params.setTitleRows(1);
params.setHeadRows(1);
List list = ExcelImportUtil.importExcel(
new File(“poiemp.xlsx”),
PoiEmployee.class, params);
list.forEach(e -> {
System.out.println(e+","+e.getDepartment());
});
三、Easypoi导出功能
EasyPOI支持SpringMVC
1.扫描view
<context:component-scan base-package=“cn.afterturn.easypoi.view” />
2.配置视图解析器
p:order=“0” 先找这个bean的解析，再找其它的
3.前台传入相应的查询数据

用户名: 邮件: 部门: 查询导出 4.4.1.4 后台接收参数进行导出注：头像路径必需是真实路径 @RequestMapping("/download")public String download(ModelMap map, HttpServletRequest request,EmployeeQuery query){ //拿到所有员工 List employeeList = employeeService.findByQuery(query); //拿到当前项目的路径 String realPath = request.getServletContext().getRealPath(""); //修改(拼接)员工头像的路径 employeeList.forEach(e -> { e.setHeadImage(realPath+e.getHeadImage()); }); ExportParams params = new ExportParams("员工信息", "列表", ExcelType.XSSF); params.setFreezeCol(5); map.put(NormalExcelConstants.DATA_LIST, employeeList); // 数据集合 map.put(NormalExcelConstants.CLASS, Employee.class);//导出实体 map.put(NormalExcelConstants.PARAMS, params);//参数 map.put(NormalExcelConstants.FILE_NAME, "employee");//文件名称 //easypoiExcelView return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称 } 四、Easypoi导入 1.访问页面 2.ImportController @RequestMapping("/import")public class ImportController { @RequestMapping("/index") public String index(){ return "import"; } } 3.import.jsp 导入 4.导入实现上传解析器一定要有名字：multipartResolver 必需加一个默认密码员工对应的部门必需有id(根据名称拿到对应的部门) @Controller @RequestMapping("/import") public class ImportController { @Autowired private IEmployeeService employeeService; @Autowired IDepartmentService departmentService; @Autowired EmployeeExcelVerifyHandler excelVerifyHandler; //返回路径 @RequestMapping("/index") public String index(){ return "import"; } @RequestMapping("/empxlsx") public String empxlsx (MultipartFile multipartFile , HttpServletResponse response) throws Exception{ //multipartFile 这个名称必须跟import.jsp中name="multipartFile " 名称必须一样 System.out.println(multipartFile); InputStream inputStream = multipartFile.getInputStream(); //基本参数 ImportParams params = new ImportParams(); //上传时做文件验证 params.setNeedVerfiy(true); //设置自定义验证处理器 params.setVerifyHandler(excelVerifyHandler); //导入Excel ExcelImportResult result = ExcelImportUtil.importExcelMore(inputStream, Employee.class, params); //获取上传成功的数据 List list = result.getList(); list.forEach(e->{ System.out.println(e); //设置初始密码 e.setPassword("123456"); //1.拿到部门名称 String deptName = e.getDepartment().getName(); //2.拿到部门名称拿到部门 Department byName = departmentService.findByName(deptName); //3.把部门放进去 e.setDepartment(byName); //保存到数据库 employeeService.save(e); }); if (result.isVerfiyFail()) { //错误文件返回到前台 Workbook workbook = result.getFailWorkbook(); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");//mime类型 response.setHeader("Content-disposition", "attachment;filename=error.xlsx"); response.setHeader("Pragma", "No-cache");//不要缓存 ServletOutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); } return "import"; } } 5.导入验证使用的是JSR 303 规范 6.导入验证包 org.hibernate hibernate-validator 5.2.4.Final 7.加上验证的注解 @Excel(name = "用户名") @NotNull(message = "用户名不能为空") private String username; private String password; @Excel(name = "邮箱",width = 25) private String email; @Excel(name = "年龄") @Max(value=80,message = "年龄不能超过80岁") @Min(value = 20,message = "年龄不能小于20岁") private Integer age; @Excel(name="头像",type = 2,height = 25) private String headImage; //头像 //jpa在管理的懒加载对象 @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name="department_id") @ExcelEntity private Department department; 8.加上自定义注解这个类型是需要Spring扫描 @Componentpublic class EmployeeExcelVerifyHandler implements IExcelVerifyHandler {

@Autowired
private IEmployeeService employeeService;
@Override
public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
    //如果用户存在，我们就返回一个false
    //  checkUsername:这个用户名是否可以使用
    if(!employeeService.checkUsername(employee.getUsername())){
        ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(false);
        result.setMsg("用户名已经存在！");
        return result;
    }
    return  new ExcelVerifyHandlerResult(true);
}

}
9.设置部门
public interface DepartmentRepository extends BaseRepository<Department,Long> {
//根据名称拿到部门对象
Department findByName(String name);
}

public interface IDepartmentService extends IBaseService<Department,Long> {
Department findByName(String name);
}
@Service
public class DepartmentServiceImpl extends BaseServiceImpl<Department,Long> implements IDepartmentService {
@Autowired
DepartmentRepository departmentRepository;
@Override
public Department findByName(String name) {
return departmentRepository.findByName(name);
}
}
10.导入功能修改
要验证，必需设置 params.setNeedVerfiy(true);
自定义验证:params.setVerifyHandler(employeeExcelVerifyHandler);
result.getList(); 拿到所有通过验证的数据 result.getFailList():拿到所有错误的数据 result.getFailWorkbook();拿到错误的文本
@Autowiredprivate EmployeeExcelVerifyHandler employeeExcelVerifyHandler;…
@RequestMapping("/empXlsx")
public String empXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception{
//准备一些导入的参数
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setHeadRows(1);
params.setNeedVerfiy(true); //需要做验证
//设置验证处理器
params.setVerifyHandler(employeeExcelVerifyHandler);

//把上传的excel文件中的数据变成Employee
ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
        empFile.getInputStream(),
        Employee.class, params);
//拿到相应的值
List<Employee> list = result.getList();
//把员工进行保存
list.forEach(e -> {
    //设置一个默认密码
    e.setPassword("123456");
    //根据名称到数据库中拿到部门
    Department department = e.getDepartment();
    if(department!=null){
        Department dbDept =  departmentService.findByName(department.getName());
        e.setDepartment(dbDept);
    }
    employeeService.save(e);
});

//拿到错误的值
// List errList = result.getFailList();
// errList.forEach(e -> System.out.println(“错误的：”+e));
//有错误的情况进行导出
if(result.isVerfiyFail()){
//拿到错误的文件
Workbook failWorkbook = result.getFailWorkbook();
response.setContentType(“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”); //mime类型
response.setHeader(“Content-disposition”, “attachment;filename=error.xlsx”);
response.setHeader(“Pragma”, “No-cache”);//设置不要缓存
OutputStream ouputStream = response.getOutputStream();
failWorkbook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}
return “import”;
}