aisell08 导入与导出

一. java操作Excel

1.1 简单认识

	在开发中，我们经常需要写程序还操作办公软件(其中操作得最多的就是Word与Excel)！
	java操作Excel有两种方式：
	1) poi(我们用poi)
	2) jxl
	办公软件分两个版本: 03及以前，07及以后

1.2 poi 相应的jar包

<!-- poi支持的jar包 -->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.11</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.11</version>
</dependency>

1.3 创建Excel

	创建Excel完成99乘法表

public class PoiTest {
    @Test
    public void testCreateExcel() throws Exception{
        //创建一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        //创建一个工作表
        Sheet sheet = workbook.createSheet("99乘法表");
        //创建行
        for (int i = 1;i<=9;i++){
            Row row = sheet.createRow(i - 1);
            //创建单元格
            for (int j = 1;j<=i;j++){
                Cell cell = row.createCell(j-1);
                cell.setCellValue(j+"*"+i+"="+(j*i));
            }
        }
        //输出流写出Excel
        FileOutputStream fos = new FileOutputStream("99乘法表.xlsx");
        workbook.write(fos);
        fos.close();
    }
}

1.4 读取Excel

@Test
    public void testReadExcel() throws Exception{
        //读取Excle文件
        Workbook wb = WorkbookFactory.create(new FileInputStream("emp-poi.xlsx"));
        //读取第一张表
        Sheet sheet = wb.getSheetAt(0);
        //读取行，获取总行数
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 1; i <=lastRowNum ; i++) {
            //获取行
            Row row = sheet.getRow(i);
            //获取总列数
            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

	使我们操作Excel变得更加简单

2.1 相应的jar包

	如果之前引入的 POI的jar包，需要把以前的jar包给删掉，再引入EasyPOI的jar包

<!-- easypoi的支持 -->
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-base</artifactId>
      <version>3.2.0</version>
    </dependency>
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-web</artifactId>
      <version>3.2.0</version>
    </dependency>
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-annotation</artifactId>
      <version>3.2.0</version>
    </dependency>

2.2 导出

	配置视图解析器

<!-- 配置视图解析器 p:order顺序 -->
    <bean class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="1" />
    <!-- 扫描easypoi的view -->
    <context:component-scan base-package="cn.afterturn.easypoi.view" />

//导出
    @RequestMapping("/export")
    public String export(EmployeeQuery query, ModelMap map, HttpServletRequest request){
        System.out.println(query.getDepartmentId());
        //获取数据
        List<Employee> list = employeeService.queryAll(query);
        //获取真实路径
        String realPath = request.getServletContext().getRealPath("");
        //遍历
        list.forEach(e->{
            //拼接图片路径
            e.setHeadImage(realPath+e.getHeadImage());
        });
        //设置基本参数
        ExportParams params = new ExportParams("员工数据", "员工表", ExcelType.XSSF);
        map.put(NormalExcelConstants.DATA_LIST, list); // 数据集合
        map.put(NormalExcelConstants.CLASS, Employee.class);//导出实体
        map.put(NormalExcelConstants.PARAMS, params);//参数
        map.put(NormalExcelConstants.FILE_NAME, "员工");//文件名称
        //easypoiExcelView 视图
        return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称


    }

2.3 导入(不带验证)

2.3.1 Employee 实体类

	@Excel 注解 导出的字段

@Entity
@Table(name = "employee")
public class Employee extends BaseDomain {

    @Excel(name = "用户名")
    @NotNull
    private String username;
    private String password;
    @Excel(name = "邮箱")
    private String email;
    @Max(60)
    @Min(value = 18,message = "年龄必需大于等于18岁")
    @Excel(name = "年龄")
    private Integer age;
    @Excel(name = "头像",type = 2)
    private String headImage;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "department_id")
    //关联对象
    @ExcelEntity
    private Department department;

    @ManyToMany
    @JoinTable(name = "employee_role",joinColumns = @JoinColumn(name = "employee_id"),
                inverseJoinColumns = @JoinColumn(name = "role_id"))
    private List<Role> roles = new ArrayList<>();
	......
	get and set 方法
}

2.3.2 ImportController

@Controller
@RequestMapping("/import")
public class ImportController {

    @Autowired
    private IEmployeeService employeeService;
    @Autowired
    private IDepartmentService departmentService;
    @Autowired
    private EmployeeVerifyHandler employeeVerifyHandler;

    @RequestMapping("/index")
    public String index(){
        return "import";
    }

    //导入，不带用户名唯一性验证
    @RequestMapping("/employeeXlsx")
    public String employeeXlsx(MultipartFile empFile) throws Exception {
        ImportParams params = new ImportParams();

        params.setHeadRows(1);
        long start = new Date().getTime();
        List<Employee> list = ExcelImportUtil.importExcel(
                empFile.getInputStream(),
                Employee.class, params);

        list.forEach(e->{
            e.setPassword("123456");
            if (e.getDepartment()!=null){
                //通过名称获取部门
                Department department = departmentService.findByName(e.getDepartment().getName());
                e.setDepartment(department);
                employeeService.save(e);
            }
        });
        return "import";
    }
}

2.4 导入(带验证)

2.4.1 自定义验证

	实现IExcelVerifyHandler接口
	扫描它，把他交给Spring管理

!-- 扫描common -->
    <context:component-scan base-package="com.yangrui.aisell.common"/>

	准备验证规则

//用户名的唯一验证
@Component
public class EmployeeVerifyHandler implements IExcelVerifyHandler<Employee>{
    @Autowired
    IEmployeeService employeeService;

    @Override
    public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
        Employee emp = employeeService.findByUsername(employee.getUsername());
        if(emp!=null){
            //如果emp不等于null，证明用户名已经存在了
            return new ExcelVerifyHandlerResult(false,"用户名已存在");
        }
        return new ExcelVerifyHandlerResult(true);
    }
}

2.4.2 ImportController

@Controller
@RequestMapping("/import")
public class ImportController {

    @Autowired
    private IEmployeeService employeeService;
    @Autowired
    private IDepartmentService departmentService;
    @Autowired
    private EmployeeVerifyHandler employeeVerifyHandler;
    //导入，带用户名唯一验证
    @RequestMapping("/employeeXlsx")
    public String employeeXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception {
        //导入参数
        ImportParams params = new ImportParams();
        //开启验证
        params.setNeedVerfiy(true);
        //验证规则
        params.setVerifyHandler(employeeVerifyHandler);
        //表头行
        params.setHeadRows(1);
        ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
                empFile.getInputStream(),
                Employee.class, params);
        //数据正确直接保存
        result.getList().forEach(e->{
            //设置初始默认密码
            e.setPassword("123456");
            if(e.getDepartment()!=null){
                Department department = departmentService.findByName(e.getDepartment().getName());
                e.setDepartment(department);
            }
            employeeService.save(e);
        });
        //有错的数据返回工作薄到前台
        if(result.isVerfiyFail()){
            Workbook workbook = result.getFailWorkbook();
            response.setHeader("content-disposition", "attachment;filename=error.xlsx");
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.close();
        }
        return "import";
    }
}