Springboot POI Excel的导入与导出

  后台管理系统经常会用到表格来展示数据，如用户基本信息，若管理员需要将用户信息保存到本地，则需要对用户信息进行导出，导出的文件大多采用excel。java中对于excel的读写可以采用POI。

一、POI简介

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

HSSF 是Horrible SpreadSheet Format的缩写，通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。

二、POI常用类的说明

类名	说明
HSSFWorkbook	Excel的文档对象
HSSFSheet	Excel的表单
HSSFRow	Excel的行
HSSFCell	Excel的格子单元
HSSFFont	Excel字体
HSSFDataFormat	格子单元的日期格式
HSSFHeader	Excel文档Sheet的页眉
HSSFFooter	Excel文档Sheet的页脚
HSSFCellStyle	格子单元样式
HSSFDateUtil	日期
HSSFPrintSetup	打印
HSSFErrorConstants	错误信息表

三、POI在springboot中如何使用

（1）导入maven依赖

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>4.1.2</version>
		</dependency>

（2）创建工具类POIUtils实现excel和list的相互转换，list里存放的就是多个用户对象Employee（每个对象中有5个属性）

public class Employee {
    private Integer id;

    private String name;

    private String gender;
    @JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai")
    private Date birthday;

    private String idCard;

list转换成excel方法如下

 public static ResponseEntity<byte[]> employee2Excel(List<Employee> list) {
        //1.创建一个Excel文档 以.xls结尾
        HSSFWorkbook workbook = new HSSFWorkbook();
        //2.创建文档摘要
        workbook.createInformationProperties();
        //3.获取并配置文档信息
        DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation();
        docInfo.setCategory("员工信息");
        docInfo.setManager("fyf");
        docInfo.setCompany("ff公司");

        //4.获取文档摘要信息
        SummaryInformation summInfo = workbook.getSummaryInformation();
        summInfo.setTitle("员工信息表");
        summInfo.setAuthor("fyf");
        //设置备注
        summInfo.setComments("文档由java提供");

        //创建标题行的样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        //setFillForegroundColor setFillPattern要同时设置才有效 单元格颜色的设置
        headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        //设置日期行的样式
        HSSFCellStyle dateCellStyle = workbook.createCellStyle();
        dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

        //创建sheet
        HSSFSheet sheet = workbook.createSheet("员工信息表");

        //创建列的宽度 单位是bit
        sheet.setColumnWidth(0, 5 * 256);
        sheet.setColumnWidth(1, 12 * 256);
        sheet.setColumnWidth(2, 10 * 256);
        sheet.setColumnWidth(3, 5 * 256);
        sheet.setColumnWidth(4, 12 * 256);

        //6.创建标题行
        HSSFRow r0 = sheet.createRow(0);
        HSSFCell c0 = r0.createCell(0);
        c0.setCellValue("编号");
        c0.setCellStyle(headerStyle);
        HSSFCell c1 = r0.createCell(1);
        c1.setCellStyle(headerStyle);
        c1.setCellValue("姓名");
        HSSFCell c2 = r0.createCell(2);
        c2.setCellStyle(headerStyle);
        c2.setCellValue("工号");
        HSSFCell c3 = r0.createCell(3);
        c3.setCellStyle(headerStyle);
        c3.setCellValue("性别");
        HSSFCell c4 = r0.createCell(4);
        c4.setCellStyle(headerStyle);
        c4.setCellValue("出生日期");

        //填充行数据
        for (int i = 0; i < list.size(); i++) {
            Employee emp = list.get(i);
            //第一行是字段
            HSSFRow row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(emp.getId());
            row.createCell(1).setCellValue(emp.getName());
            row.createCell(2).setCellValue(emp.getWorkID());
            row.createCell(3).setCellValue(emp.getGender());
            HSSFCell cell4 = row.createCell(4);
            cell4.setCellStyle(dateCellStyle);
            cell4.setCellValue(emp.getBirthday());
        }
        //将execel输出成字节流，再转换成字节流数组
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        //设置http头部
        HttpHeaders headers = new HttpHeaders();
        try {
            headers.setContentDispositionFormData("attachment",
                    new String("员工表.xls".getBytes("UTF-8"), "ISO-8859-1"));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            workbook.write(baos);
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(),headers, HttpStatus.CREATED);
    }

controller测试接口：

 @GetMapping("/export")
    public ResponseEntity<byte[]> exportData(){
    	//data是从数据库查询得到的
        List<Employee> data = (List<Employee>) employeeService.getEmployeeByPage(null, null, null,null).getData();
        return POIUtils.employee2Excel(data);
    }

前端测试：导出按钮的点击事件为

 exportData(){
      window.open("/export",'_parent');
    },

效果

（3）excel解析成list
前端需要实现文件的上传，后端利用MultipartFile进行接收

 @PostMapping("/import")
    public RespBean importData(MultipartFile file) throws IOException {
        List<Employee> list = POIUtils.excel2Employee(file);
        //打印list集合
        System.out.println(list);
        //插入至数据库
        if (employeeService.addEmps(list) == list.size()) {
            return RespBean.ok("上传成功");
        }
        return RespBean.error("上传失败");
    }

POIUtils.excel2Employee方法实现如下

 public static List<Employee> excel2Employee(MultipartFile file){
        List<Employee> list = new ArrayList<>();
        Employee employee = null;
        try {
            //1. 创建一个 workbook 对象
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
             //2. 获取 workbook 中表单的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                HSSFSheet sheet = workbook.getSheetAt(i);
                //4. 获取表单中的行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    //5. 跳过标题行
                    if(j == 0){
                        continue;
                    }
                    HSSFRow row = sheet.getRow(j);
                    //6. 获取行
                    if(row == null){
                        continue;//防止数据中间有空行
                    }
                    //7. 获取列数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    employee = new Employee();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);
                        switch (cell.getCellType()){
                            case STRING:
                                //分两种 一种是字符串
                                String cellValue = cell.getStringCellValue();
                                switch (k){
                                    case 1:
                                        employee.setName(cellValue);
                                        break;
                                    case 2:
                                        employee.setWorkID(cellValue);
                                        break;
                                    case 3:
                                        employee.setGender(cellValue);
                                        break;
                                }
                                break;
                             default:
                                 // 字符串为日期格式
                                 switch (k){
                                     case 4:
                                         employee.setBirthday(cell.getDateCellValue());
                                         break;
                                 }
                                 break;
                        }
                    }
                    list.add(employee);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

测试查看控制台打印的list是否与excel中数据一致即可。