POI报表导入导出

POI报表导入

搭建环境

<!-- POI2003 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <!--高版本poi2007-->
        <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>

用户实体类配置构造方法

//objs数据位置和excel上传位置一致。
    public User(Object[] objs, String companyId, String companyName) {
        //默认手机号excel读取为字符串会存在科学记数法问题，转化处理
        this.mobile = new DecimalFormat("#").format(objs[2]);
        this.username = objs[1].toString();
        this.createTime = new Date();
        this.timeOfEntry = (Date) objs[5];
        this.formOfEmployment = ((Double) objs[4]).intValue();
        this.workNumber = new DecimalFormat("#").format(objs[3]).toString();
        this.companyId = companyId;
        this.companyName = companyName;
    }

自定义注解

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAttribute {
    /** 对应的列名称 */
    String name() default "";

    /** 列序号 */
    int sort();

    /** 字段类型对应的格式 */
    String format() default "";
}

在实体类上使用上面的注解

@Entity
@Table(name = "bs_user")
@Getter
@Setter
@NoArgsConstructor
public class User implements Serializable {
    private static final long serialVersionUID = -7611123338481573454L;
    @Id
    private String id;
    /*手机号码（最新版idea2018.2.5版）超详细超简单版  16841*/
    @ExcelAttribute(sort = 2)
    private String mobile;
    @ExcelAttribute(sort = 1)
    private String username;//用户名
    private String password;//密码

    private Integer enableState;//启用状态 0为禁用 1为启用
    private Date createTime;//创建时间
    private String companyId;
    private String companyName;
    //部门
    @ExcelAttribute(sort = 6)
    private String departmentId;
    //入职时间
    @ExcelAttribute(sort = 5)
    private Date timeOfEntry;
    //聘用形式
    @ExcelAttribute(sort = 4)
    private Integer formOfEmployment;
    //工号
    @ExcelAttribute(sort = 3)
    private String workNumber;
    private String formOfManagement;//管理形式
    private String workingCity;//工作城市
    private Date correctionTime;  //转正时间
    private Integer inServiceStatus; //在职状态 1.在职  2.离职
    private String departmentName;//部门名称
    /**
     * level 水平，级，级别
     * String
     * saasAdmin：saas管理员具备所有权限
     * coAdmin：企业管理（创建租户企业的时候添加）
     * user：普通用户（需要分配角色）
     */
    private String level;
    private String staffPhoto;  //用户头像

导入的工具类

public class ExcelImportUtil<T> {

    private Class clazz; //T的类型
    private Field fields[];//T类的字段数组

    public ExcelImportUtil(Class clazz) {
        this.clazz = clazz;
        fields=clazz.getDeclaredFields();
    }

    /*基于注解读取excel*/
    public List<T> readExcel(InputStream is, int rowIndex, int cellIndex) {
        ArrayList<T> list = new ArrayList<>();
        T entity = null;
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(is);
            XSSFSheet sheet = workbook.getSheetAt(0);
            //不准确
            int lastLength = sheet.getLastRowNum();
            for (int rowNum = rowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) {
                //每行的数据
                Row row = sheet.getRow(rowNum);
                //创建每行对应的java对象
                entity = (T) clazz.newInstance();
                for (int j = cellIndex; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    for (Field field : fields) {//遍历对象的每个属性
                        if (field.isAnnotationPresent(ExcelAttribute.class)) {//如果属性带有ExcelAttribute是否存在注解
                            field.setAccessible(true);
                            ExcelAttribute excelAttribute = field.getAnnotation(ExcelAttribute.class);
                            if (j == excelAttribute.sort()) {//如果这个单元格等于注解的sort属性时
                                //把这entity对象的field设置值。
                                field.set(entity, covertAttrType(field, cell));
                            }
                        }
                    }

                }
                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)) {
            System.out.println(getValue(cell));
           return Integer.parseInt(getValue(cell));
        } else if ("double".equals(fieldType) || "Double".equals(fieldType)) {
            return Double.parseDouble(getValue(cell));
        } else {
            return null;
        }
    }

    //格式转换为String
    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 javaDate = DateUtil.getJavaDate(cell.getNumericCellValue());
                    return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(javaDate);
                } else {//如果不是日期就是数字
                    String strCell = "";
                    Double nun = cell.getNumericCellValue();
                    BigDecimal bigDecimal = new BigDecimal(nun.toString());
                    if (bigDecimal != null) {
                        strCell = bigDecimal.toPlainString();
                    }
                    //去掉 浮点型 自动加 .0
                    if (strCell.endsWith(".0")) {
                        strCell = strCell.substring(0, strCell.indexOf("."));
                    }
                    return strCell;
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }
}

要导入的excel格式

POI报表导出

模板打印

概述：

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

模板打印的操作步骤

1. 制作模版文件（模版文件的路径）
2. 导入（加载）模版文件，从而得到一个工作簿
3. 读取工作表
4. 读取行
5. 读取单元格
6. 读取单元格样式
7. 设置单元格内容
8. 其他单元格就可以使用读到的样式了

//1.构造数据
List<需要的类型>list=..
//2.加载模板流数据
Resource resource = new ClassPathResource("excel-template/hr-demo.xlsx");
FileInputStream fis = new FileInputStream(resource.getFile());
//3.根据文件流，加载指定的工作簿
XSSFWorkbook wb = new XSSFWorkbook(fis);
//4.读取工作表
Sheet sheet = wb.getSheetAt(0);
//5.抽取公共的样式
Row styleRow = sheet.getRow(2);
CellStyle [] styles = new CellStyle[styleRow.getLastCellNum()];
    for(int i=0;i<styleRow.getLastCellNum();i++) {
      styles[i] = styleRow.getCell(i).getCellStyle();
   }
    //6.构造每行和单元格数据
    AtomicInteger datasAi = new AtomicInteger(2);
    Cell cell = null;
    for (EmployeeReportResult report : list) {
Row dataRow = sheet.createRow(datasAi.getAndIncrement());
....
}
String fileName = URLEncoder.encode(month+"人员信息.xlsx", "UTF-8");
    response.setContentType("application/octet-stream");
    response.setHeader("content-disposition", "attachment;filename=" + new
String(fileName.getBytes("ISO8859-1")));
    response.setHeader("filename", fileName);
    wb.write(response.getOutputStream());

把上面的设置成导出工具类

@Getter
@Setter
public class ExcelExportUtil<T> {
    private int rowIndex;
    private int styleIndex;
    private String templatePath;
    private Class clazz;
    private Field fields[];

    public ExcelExportUtil(Class clazz, int rowIndex, int styleIndex) {
        this.rowIndex = rowIndex;
        this.styleIndex = styleIndex;
        this.clazz = clazz;
        fields = clazz.getDeclaredFields();
    }

    /*基于注解导出*/
    public void export(HttpServletResponse response, InputStream is, List<T> objs, String fileName) throws Exception {
        //Apache Poi提供了SXSSFWork对象，专门用于处理大数据量Excel报表导出
        XSSFWorkbook workbook = new XSSFWorkbook(is);
        //SXSSFWorkbook workbook = new SXSSFWorkbook(workbook1);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(styleIndex);
        //单元格的类型
        CellStyle[] styles = new CellStyle[row.getLastCellNum()];
        for (int i = 0; i < row.getLastCellNum(); i++) {
            styles[i] = row.getCell(i).getCellStyle();
        }
        AtomicInteger atomicInteger = new AtomicInteger(rowIndex);
        for (T obj : objs) {
            Row row1 = sheet.createRow(atomicInteger.getAndIncrement());
            for (int i = 0; i < styles.length; i++) {
                Cell cell = row1.createCell(i);
                cell.setCellStyle(styles[i]);
                for (Field field : fields) {
                    if (field.isAnnotationPresent(ExcelAttribute.class)) {
                        field.setAccessible(true);
                        ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
                        if (i == ea.sort()) {
                            if (field.get(obj) != null) {
                                cell.setCellValue(field.get(obj).toString());
                            }
                        }
                    }
                }

            }
        }
        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());
    }

    /*Apache Poi提供了SXSSFWork对象，专门用于处理大数据量Excel报表导出*/
    public void exportBySXSSF(HttpServletResponse response, InputStream is, List<T> objs, String fileName) throws Exception {
        SXSSFWorkbook workbook = new SXSSFWorkbook(new XSSFWorkbook(is));
        Sheet sheet = workbook.getSheetAt(0);
        //单元格的类型
        CellStyle[] styles = getTemplateStyles(sheet.getRow(styleIndex));
        AtomicInteger atomicInteger = new AtomicInteger(rowIndex);
        for (T obj : objs) {
            Row row1 = sheet.createRow(atomicInteger.getAndIncrement());
            for (int i = 0; i < styles.length; i++) {
                Cell cell = row1.createCell(i);
                cell.setCellStyle(styles[i]);
                for (Field field : fields) {
                    if (field.isAnnotationPresent(ExcelAttribute.class)) {
                        field.setAccessible(true);
                        ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
                        if (i == ea.sort()) {
                            if (field.get(obj) != null) {
                                cell.setCellValue(field.get(obj).toString());
                            }
                        }
                    }
                }

            }
        }
        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;
    }
}

完成导入

/**
     *  采用模版打印的形式完成报表导出,sxssf不支持模版导出
     *  根据企业id和年月查询
     */
    @RequestMapping(value = "/export/{month}",method = RequestMethod.GET)
    public void export(@PathVariable(name = "month") String month) throws Exception {
        //1.构造表格数据
        List<EmployeeReportResult> list = userCompanyPersonalService.findByReport(companyId, month);
        //2.加载模板
        ClassPathResource resource = new ClassPathResource("excel-template/hr-demo.xlsx");
        FileInputStream fis = new FileInputStream(resource.getFile());

        //3.通过工具类下载文件
        new ExcelExportUtil(EmployeeReportResult.class,2,2).exportBySXSSF(response,fis,list,month+"人事报表.xlsx");
    }

百万数据报表概述

我们都知道Excel可以分为早期的Excel2003版本（使用POI的HSSF对象操作）和Excel2007版本（使用POI的XSSF操作），两者对百万数据的支持如下：
Excel 2003：在POI中使用HSSF对象时，excel 2003最多只允许存储65536条数据，一般用来处理较少的数据量。这时对于百万级别数据，Excel肯定容纳不了。
Excel 2007：当POI升级到XSSF对象时，它可以直接支持excel2007以上版本，因为它采用ooxml格式。这时excel可以支持1048576条数据，单个sheet表就支持近百万条数据。但实际运行时还可能存在问题，原因是执
行POI报表所产生的行对象，单元格对象，字体对象，他们都不会销毁，这就导致OOM的风险。

这时我们可以使用java的jdk性能监控工具

没有性能监控工具一切推论都只能停留在理论阶段，我们可以使用Java的性能监控工具来监视程序的运行情况，包括CUP,垃圾回收，内存的分配和使用情况，这让程序的运行阶段变得更加可控，也可以用来证明我们的推测。这里
我们使用JDK提供的性能工具Jvisualvm来监控程序运行。或者使用在线分析诊断工具Arthas。

Jvisualvm的使用

Jvisualvm位于JAVA_HOME/bin目录下，直接双击就可以打开该程序。如果只是监控本地的java进程，是不需要配置参数的，直接打开就能够进行监控。首先我们需要在本地打开一个Java程序，例如我打开员工微服务进程，这时
在jvisualvm界面就可以看到与IDEA相关的Java进程了：

Jvisualvm使用起来比较简单，双击点击当前运行的进程即可进入到程序的监控界面

解决方案分析

对于百万数据量的Excel导入导出，只讨论基于Excel2007的解决方法。在ApachePoi 官方提供了对操作大数据量的导入导出的工具和解决办法，操作Excel2007使用XSSF对象，可以分为三种模式：
用户模式：用户模式有许多封装好的方法操作简单，但创建太多的对象，非常耗内存（之前使用的方法）
事件模式：基于SAX方式解析XML，SAX全称Simple API for XML，它是一个接口，也是一个软件包。它是一
种XML解析的替代方法，不同于DOM解析XML文档时把所有内容一次性加载到内存中的方式，它逐行扫描文
档，一边扫描，一边解析。
SXSSF对象：是用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel

这是一张Apache POI官方提供的图片，描述了基于用户模式，事件模式，以及使用SXSSF三种方式操作Excel的特性以及CUP和内存占用情况。

使用Apache POI完成百万数据量的Excel报表导出解决方案

基于XSSFWork导出Excel报表，是通过将所有单元格对象保存到内存中，当所有的Excel单元格全部创建完成之后一次性写入到Excel并导出。当百万数据级别的Excel导出时，随着表格的不断创建，内存中对象越来越多，直至内
存溢出。Apache Poi提供了SXSSFWork对象，专门用于处理大数据量Excel报表导出。

在实例化SXSSFWork这个对象时，可以指定在内存中所产生的POI导出相关对象的数量（默认100），一旦内存中的对象的个数达到这个指定值时，就将内存中的这些对象的内容写入到磁盘中（XML的文件格式），就可以将这些
对象从内存中销毁，以后只要达到这个值，就会以类似的处理方式处理，直至Excel导出完成。

代码实现

// SXSSFWorkbook使用创建工作簿
    SXSSFWorkbook workbook = new SXSSFWorkbook();

对比测试

百万数据报表读取

用户模式：加载并读取Excel时，是通过一次性的将所有数据加载到内存中再去解析每个单元格内容。当Excel数据量较大时，由于不同的运行环境可能会造成内存不足甚至OOM异常。
事件模式：它逐行扫描文档，一边扫描一边解析。由于应用程序只是在读取数据时检查数据，因此不需要将数据存储在内存中，这对于大型文档的解析是个巨大优势。

步骤分析：

（1）设置POI的事件模式
根据Excel获取文件流
根据文件流创建OPCPackage
创建XSSFReader对象
（2）Sax解析
自定义Sheet处理器
创建Sax的XmlReader对象
设置Sheet的事件处理器
逐行读取

原理分析

我们都知道对于Excel2007的实质是一种特殊的XML存储数据，那就可以使用基于SAX的方式解析XML完成Excel的读取。SAX提供了一种从XML文档中读取数据的机制。它逐行扫描文档，一边扫描一边解析。由于应用程序只是在
读取数据时检查数据，因此不需要将数据存储在内存中，这对于大型文档的解析是个巨大优势

代码实现

//自定义Sheet基于Sax的解析处理器
public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
  //封装实体对象
  private PoiEntity entity;
  /**
  * 解析行开始
  */
  @Override
  public void startRow(int rowNum) {
    if (rowNum >0 ) {
      entity = new PoiEntity();
   }
 }
  /**
  * 解析每一个单元格
  */
  @Override
  public void cell(String cellReference, String formattedValue, XSSFComment comment)
{
    if(entity != null) {
      switch (cellReference.substring(0, 1)) {
        case "A":
          entity.setId(formattedValue);
          break;
        case "B":
          entity.setBreast(formattedValue);
          break;
        case "C":
          entity.setAdipocytes(formattedValue);
          break;
        case "D":
          entity.setNegative(formattedValue);
          break;
        case "E":
          entity.setStaining(formattedValue);
          break;
        case "F":
          entity.setSupportive(formattedValue);
          break;
        default:
          break;
     }
   }
 }
  /**
  * 解析行结束
  */
  public void endRow(int rowNum) {
    System.out.println(entity);
 }
  //处理头尾
  public void headerFooter(String text, boolean isHeader, String tagName) {
 }
}

自定义解析

/**
* 自定义Excel解析器
*/
public class ExcelParser {
  public void parse (String path) throws Exception {
    //1.根据Excel获取OPCPackage对象
    OPCPackage pkg = OPCPackage.open(path, PackageAccess.READ);
    try {
      //2.创建XSSFReader对象
      XSSFReader reader = new XSSFReader(pkg);
      //3.获取SharedStringsTable对象
      SharedStringsTable sst = reader.getSharedStringsTable();
      //4.获取StylesTable对象
      StylesTable styles = reader.getStylesTable();
      //5.创建Sax的XmlReader对象
      XMLReader parser = XMLReaderFactory.createXMLReader();
      //6.设置处理器
      parser.setContentHandler(new XSSFSheetXMLHandler(styles,sst, new
SheetHandler(), false));
      XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator)
reader.getSheetsData();
      //7.逐行读取
      while (sheets.hasNext()) {
        InputStream sheetstream = sheets.next();
        InputSource sheetSource = new InputSource(sheetstream);
        try {
          parser.parse(sheetSource);
       } finally {
          sheetstream.close();
       }
     }
   } finally {
      pkg.close();
   }
 }
}