Apache POI 实现报表导入和导出

1、POI概述

在企业级应用开发中，Excel报表是一种最常见的报表需求。Excel报表开发一般分为两种形式：

• 基于Excel报表批量上传数据
• 通过Java代码生成Excel报表

Java中常见的用来操作Excel的方式一般有2种：JXL和POI

• JXL只能对Excel进行操作,属于比较老的框架，它只支持到Excel 95-2000的版本。现在已经停止更新和维护。
• POI是Apache的项目，可对微软的Word、Excel、PPT进行操作：包括office2003和2007,Excl2003和2007。POI现在一直有更新。

Apache POI是Apache软件基金会的开源项目，由Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java语言操作Microsoft Office的功能。使用场景：

• 数据报表生成
• 数据备份
• 数据批量上传

2、准备工作

2.1、创建项目导入依赖

<dependencies>
  <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
  </dependency>
  <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
  </dependency>
  <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.0.1</version>
  </dependency>
</dependencies>

2.2、POI常用API介绍

2.3、自定义工具类

自定义生成Excel报表文件有很多不尽如意的地方，特别是针对复杂报表头，单元格样式，字体等操作。手写这些代码不仅费时费力，有时候效果还不太理想。怎么样才能更方便的对报表样式，报表头进行处理呢？答：使用已经准备好的Excel模板，只需要关注模板中的数据即可。

2.3.1、准备模板文件，比如：

2.3.2、自定义注解

// 用来修饰实体类的字段：指定属性用来封装excel表的哪一列数据
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAttribute {
  /** 对应的列名称 */
  String name() default "";
  /** 列序号 */
  int sort();
  /** 字段类型对应的格式 */
  String format() default "";
}

2.3.3、创建实体类

// 用来封装数据库的每条记录：对应excel的一条记录
public class Employee {
        // 编号
        @ExcelAttribute(sort = 0)
        private String id;
        
        // 姓名
        @ExcelAttribute(sort = 1)
        private String username;
        
        // 手机
        @ExcelAttribute(sort = 2)
        private String mobile;
        
        // 最高学历
        @ExcelAttribute(sort = 3)
        private String edu;

        // 籍贯
        @ExcelAttribute(sort = 4)
        private String province;
        
        // 生日
        @ExcelAttribute(sort = 5)
        private String birthday;
        
        // 入职时间
        @ExcelAttribute(sort = 6)
        private Date joinDate;
      
        // 省略setters & getters 
}

2.3.4、导出工具类

// 基本模板的Excel导出工具类
public class ExcelExportUtil<T> {

    // 写入数据的起始行
    private int rowIndex;
    // 需要提取样式所在的行号
    private int styleIndex;
    // 对象的字节码
    private Class clazz;
    // 对象中的所有属性
    private  Field fields[];

    // 构造方法
    public ExcelExportUtil(Class clazz,int rowIndex,int styleIndex) {
        this.clazz = clazz;
        this.rowIndex = rowIndex;
        this.styleIndex = styleIndex;
        fields = clazz.getDeclaredFields();
    }

    /**
     * 基于注解导出
     * response：响应对象，用于往浏览器导出数据(文件下载)
     * is：模板文件对应的输入流
     * objs：要导出的数据，即从数据库中查询的对象数据集合
     * fileName：导出的文件名(文件下载名)
     */
    public void export(HttpServletResponse response,InputStream is, List<T> objs,String fileName) throws Exception {
	    // 根据模板创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(is);
        // 获得工作表
        Sheet sheet = workbook.getSheetAt(0);
	    // 获取公共样式
        CellStyle[] styles = getTemplateStyles(sheet.getRow(styleIndex));
        AtomicInteger datasAi = new AtomicInteger(rowIndex);
        // 遍历集合
        for (T t : objs) {
            // 创建每一行对象
            Row row = sheet.createRow(datasAi.getAndIncrement());
            for(int i=0;i <styles.length; i++) {
            	// 创建一个单元格对象(每一列)
                Cell cell = row.createCell(i);
                // 设置列的样式
                cell.setCellStyle(styles[i]);
                // 遍历成员变量对象
                for (Field field : fields) {
                	// 判断成员变量上是否使用了ExcelAttribute注解
                    if(field.isAnnotationPresent(ExcelAttribute.class)){
                    	// 暴露反射
                        field.setAccessible(true);
                        // 获得注解对象
                        ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
                        if(i == ea.sort()) {
                        	// 设置单元格内容
                            cell.setCellValue(field.get(t).toString());
                        }
                    }
                }
            }
        }
        // 将excel文件导出到浏览器
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/octet-stream");
        response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes("ISO8859-1")));
        response.setHeader("filename", fileName);
        workbook.write(response.getOutputStream());
    }

   /**
    * 获得指定行的单元格样式
   */
    public CellStyle[] getTemplateStyles(Row row) {
        CellStyle [] styles = new CellStyle[row.getLastCellNum()];
        for(int i=0;i<row.getLastCellNum();i++) {
            styles[i] = row.getCell(i).getCellStyle();
        }
        return styles;
    }

    // 省略 setters & getters ..................
}

2.3.5、导入工具类

// 基于注解实现Excel报表数据导入
public class ExcelImportUtil<T> {
 	// 对象的字节码
    private Class clazz;
    // 对象的所有属性
    private  Field fields[];
 
    public ExcelImportUtil(Class clazz) {
        this.clazz = clazz;
        fields = clazz.getDeclaredFields();
    }
 
    /**
     * 基于注解读取excel文档数据
     * 	is：上传文件的字节输入流
     *  rowIndex：读取数据的起始行
     *  cellIndex：读取数据的起始单元格索引
     */
    public List<T> readExcel(InputStream is, int rowIndex,int cellIndex) {
        // 创建集合：用来每一行记录(对象)
        List<T> list = new ArrayList<T>();
        // 实体对象
        T entity = null;
        try {
        	// 根据字节输入流创建工作簿
            XSSFWorkbook workbook = new XSSFWorkbook(is);
            //获得工作表
            Sheet sheet = workbook.getSheetAt(0);
            // 获得总行数
            int rowLength = sheet.getLastRowNum();
            // 遍历每行数据
            for (int rowNum = rowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) {
            	// 获得每一行的行对象
                Row row = sheet.getRow(rowNum);
            	// 通过反射创建实体对象：一行对应一个实体对象
                entity = (T) clazz.newInstance();
                // 遍历每一列
                for (int j = cellIndex; j < row.getLastCellNum(); j++) {
                	// 获得单元格对象
                    Cell cell = row.getCell(j);
                    // 遍历成员变量对象
                    for (Field field : fields) {
                    	// 判断成员变量上是否使用了注解：ExcelAttribute
                        if(field.isAnnotationPresent(ExcelAttribute.class)){
                        	// 暴露反射
                            field.setAccessible(true);
                            // 获得ExcelAttribute注解对象
                            ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
                            if(j == ea.sort()) {
                            	// 给对象的成员变量赋值
                                field.set(entity, covertAttrType(field, cell));
                                break;
                            }
                        }
                    }
                }
                // 将对象添加到集合中
                list.add(entity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 返回集合
        return list;
    }
 

    /**
     * 类型转换 将cell 单元格格式转为 字段类型
     */
    private Object covertAttrType(Field field, Cell cell) throws Exception {
        String fieldType = field.getType().getSimpleName();
        if ("String".equals(fieldType)) {
            return getValue(cell);
        }else if ("Date".equals(fieldType)) {
            return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(getValue(cell)) ;
        }else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {
            return Integer.parseInt(getValue(cell));
        }else if ("double".equals(fieldType) || "Double".equals(fieldType)) {
            return Double.parseDouble(getValue(cell));
        }else {
            return null;
        }
    }
 
 
    /**
     * 格式转为String
     * @param cell
     * @return
     */
    public String getValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getRichStringCellValue().getString().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date dt = DateUtil.getJavaDate(cell.getNumericCellValue());
                    return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(dt);
                } else {
                    // 防止数值变成科学计数法
                    String strCell = "";
                    Double num = cell.getNumericCellValue();
                    BigDecimal bd = new BigDecimal(num.toString());
                    if (bd != null) {
                        strCell = bd.toPlainString();
                    }
                    // 去除 浮点型 自动加的 .0
                    if (strCell.endsWith(".0")) {
                        strCell = strCell.substring(0, strCell.indexOf("."));
                    }
                    return strCell;
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }

     // 省略 setters & getters ..................
}

3、报表导入和导出测试

 /**
 * 导出Excel，导出员工
 */
@RequestMapping(value = "/emp/export", method = RequestMethod.GET)
public void export() throws Exception {
  // 1.构造数据
  List<Employee> list = null; // 查询数据库获得数据
  // 2.加载模板数据
  Resource resource = new ClassPathResource("excel.xlsx");
  FileInputStream fis = new FileInputStream(resource.getFile());
  // 3. 导出数据
  new ExcelExportUtil(Employee.class,2,2).
  export(response,fis,list,"员工报表.xlsx");
}

 /**
 * 导入Excel，添加员工
 */
@RequestMapping(value="/emp/import",method = RequestMethod.POST)
public Result importUser(@RequestParam(name="file") MultipartFile file) throws Exception {
  // 1.解析Excel
  List<Employee> list = new ExcelImportUtil(Employee.class).readExcel(file.getInputStream(),1,1)
  // 2.批量保存员工
  employeeService.saveAll(lis);
  return new Result(ResultCode.SUCCESS);
}