利用poi 导出excel通用工具类

poi 导出excel通用工具

| 这一行是废话 去年写的一个工具类,感觉还算通用,分享到博客,欢迎各位大神来吐槽;
配合使用poi工具组件,利用java反射动态获对象数据。当然代码中只实现了普通的导出 如果想要美化excel导出数据,当然强大的poi组件是支持的。

1. maven pom.xml 导入poi依赖

            <!-- 2007-->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>${poi.version}</version> 
            </dependency>
            <!-- 2003-->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>${poi.version}</version> 
            </dependency>

2. 导出配置类 配置导出字段等信息

/**
* @author liweigao
* @date 2018/12/28 下午6:57
*/
@Getter
public class ExportConfig {

  /**
   * 字段
   */
  private List<Column> column = Lists.newArrayList();

  /**
   * 时间类型格式
   */
  private String dataFormat = "yyyy-MM-dd HH:mm:ss";

  /**
   * sheet 名称
   */
  private String title = "Sheet";

  private ExcelVersion version = ExcelVersion.XLSX;

  public void setColumn(String files, String name) {
      this.column.add(new Column(files, name));
  }

  public void setTitle(String title) {
      if (!Strings.isNullOrEmpty(title)) {
          this.title = title;
      }
  }

  public void setDataFormat(String dataFormat) {
      if (!Strings.isNullOrEmpty(title)) {
          this.dataFormat = dataFormat;
      }
  }

  public void setVersion(ExcelVersion excelVersion) {
      if (Objects.nonNull(excelVersion)) {
          this.version = excelVersion;
      }
  }


  @Data
  @NoArgsConstructor
  @AllArgsConstructor
  public static class Column {
      private String field;

      private String name;
  }

}

3.excel version枚举类

/**
 * @author liweigao
 * @date 2018/12/28 下午6:57
 */
@AllArgsConstructor
@Getter
public enum ExcelVersion {

    /**
     *
     */
    XLS("2003", ".xls"),
    /**
     *
     */
    XLSX("2007", ".xlsx");


    private String version;
    private String fileSuffix;
}

4. 导出工具类

/**
 * @author liweigao
 * @date 2018/12/28 下午6:57
 */
@Slf4j
public class ExportExcel<T> {

    /**
     * 导出文件
     *
     * @param exportConfig 导出配置
     * @param dataSet      数据集
     * @param out          输出流
     */
    public void export(ExportConfig exportConfig, Collection<T> dataSet, OutputStream out) {

        switch (exportConfig.getVersion()) {
            case XLSX:
                exportXlsx(exportConfig, dataSet, out);
                break;
            case XLS:
                exportXls(exportConfig, dataSet, out);
                break;
            default:
                exportXlsx(exportConfig, dataSet, out);
                break;
        }
    }

    /**
     * 2003  xls
     *
     * @param exportConfig 导出配置
     * @param dataSet      数据
     * @param out          输出流  文件or网络
     */
    private void exportXls(ExportConfig exportConfig, Collection<T> dataSet, OutputStream out) {

        if (Objects.isNull(exportConfig) || CollectionUtils.isEmpty(exportConfig.getColumn())) {
            throw new IllegalArgumentException("The workbook config is error ");
        }
        HSSFWorkbook workbook = new HSSFWorkbook();

        String sheetName = exportConfig.getTitle();
        if (Strings.isNullOrEmpty(sheetName)) {
            sheetName = "Sheet";
        }
        HSSFSheet sheet = workbook.createSheet(sheetName);
        SimpleDateFormat sdf = new SimpleDateFormat(exportConfig.getDataFormat());
        /**
         * 读取配置中的 头和字段信息
         */
        String[] headers = new String[exportConfig.getColumn().size()];
        String[] fields = new String[exportConfig.getColumn().size()];
        for (int i = 0; i < exportConfig.getColumn().size(); i++) {
            headers[i] = exportConfig.getColumn().get(i).getName();
            fields[i] = exportConfig.getColumn().get(i).getField();
        }

        /**
         *创建Excel 第一行 HSSFRichTextString富文本字体
         */
        HSSFRow row = sheet.createRow(0);

        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(headers[i]);
        }
        Iterator<T> it = dataSet.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = it.next();
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            for (int j = 0; j < fields.length; j++) {
                HSSFCell cell = row.createCell(j);
                try {
                    cell.setCellValue(getStringValue(sdf, getValue(t, fields[j])));

                } catch (NoSuchMethodException e) {

                    log.error(e.getMessage());
                } catch (IllegalAccessException e) {

                    log.error(e.getMessage());
                } catch (InvocationTargetException e) {

                    log.error(e.getMessage());
                } catch (NoSuchFieldException e) {
                    log.error(e.getMessage());
                }
            }
        }
        close(out, workbook);
    }

    private void close(OutputStream out, Workbook workbook) {
        try {
            workbook.write(out);
        } catch (IOException e) {
            log.error(e.getMessage());
        } finally {
            try {
                workbook.close();
                out.close();
            } catch (IOException e) {
                log.error(e.getMessage());
            }

        }
    }


    /**
     * 2007  xlsx
     *
     * @param exportConfig 导出配置
     * @param dataSet      数据
     * @param out          输出流  文件or网络
     */
    private void exportXlsx(ExportConfig exportConfig, Collection<T> dataSet, OutputStream out) {

        if (Objects.isNull(exportConfig) || CollectionUtils.isEmpty(exportConfig.getColumn())) {
            throw new IllegalArgumentException("The workbook config is error ");
        }
        XSSFWorkbook workbook = new XSSFWorkbook();

        String sheetName = exportConfig.getTitle();
        if (Strings.isNullOrEmpty(sheetName)) {
            sheetName = "Sheet";
        }
        XSSFSheet sheet = workbook.createSheet(sheetName);
        SimpleDateFormat sdf = new SimpleDateFormat(exportConfig.getDataFormat());
        /**
         * 读取配置中的 头和字段信息
         */
        String[] headers = new String[exportConfig.getColumn().size()];
        String[] fields = new String[exportConfig.getColumn().size()];
        for (int i = 0; i < exportConfig.getColumn().size(); i++) {
            headers[i] = exportConfig.getColumn().get(i).getName();
            fields[i] = exportConfig.getColumn().get(i).getField();
        }

        /**
         *创建Excel 第一行 HSSFRichTextString富文本字体
         */
        XSSFRow row = sheet.createRow(0);

        for (int i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(headers[i]);
        }
        Iterator<T> it = dataSet.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = it.next();
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            for (int j = 0; j < fields.length; j++) {
                XSSFCell cell = row.createCell(j);

                try {
                    String strValue = getStringValue(sdf, getValue(t, fields[j]));

                    cell.setCellValue(strValue);

                } catch (NoSuchMethodException e) {

                    log.error(e.getMessage());
                } catch (IllegalAccessException e) {

                    log.error(e.getMessage());
                } catch (InvocationTargetException e) {

                    log.error(e.getMessage());
                } catch (NoSuchFieldException e) {
                    log.error(e.getMessage());
                }
            }
        }
        close(out, workbook);

    }

    private String getStringValue(SimpleDateFormat sdf, Object value) {
        String strValue;
        if (value instanceof Date) {
            strValue = sdf.format(value);
        } else {
            // 其它数据类型都当作字符串简单处理
            strValue = (value == null) ? "" : value.toString();
        }
        return strValue;
    }

    /**
     * 获取 对象值
     *
     * @param cla
     * @param fieldName
     * @return
     * @throws NoSuchMethodException
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     * @throws InvocationTargetException
     */
    @SuppressWarnings("unchecked")
    private Object getValue(Object cla, String fieldName) throws NoSuchFieldException, NoSuchMethodException,
            IllegalAccessException,
            IllegalArgumentException, InvocationTargetException {

        Field field = cla.getClass().getDeclaredField(fieldName);
        field.setAccessible(true);

        //两种方法都可以获取字段信息
        //        String getMethod = "get"
        //                + fieldName.substring(0, 1).toUpperCase()
        //                + fieldName.substring(1);
        //        Method method = cla.getClass().getMethod(getMethod, new Class[]{});
        //
        //        return method.invoke(cla, new Object[]{});
        return field.get(cla);
    }

    public static void main(String[] args) throws IOException {
        ExportConfig exportConfig = new ExportConfig();
        List<ExportConfig.Column> list = Lists.newArrayList(new ExportConfig.Column("11", "21"),
                new ExportConfig.Column("12", "22"),
                new ExportConfig.Column("13", "23"),
                new ExportConfig.Column("14", "24"),
                new ExportConfig.Column("15", "25"),
                new ExportConfig.Column("16", "26"));

        exportConfig.setColumn("field", "字段");
        exportConfig.setColumn("name", "名称");

        File file = new File("/data/logs/test.xlsx");

        if (!file.exists()) {
            file.createNewFile();
        }
        FileOutputStream fileOutputStream = new FileOutputStream(file, false);

        ExportExcel<ExportConfig.Column> exportExcel = new ExportExcel<>();

        exportExcel.export(exportConfig, list, fileOutputStream);

        fileOutputStream.flush();
        fileOutputStream.close();
    }
}

运行main 方法即可导入到相应的位置

工具类封装的比较简单。欢迎吐槽