java 工具｜使用 POI 导入导出 EXCEL

javaDEMO

Java 基础 Demo 站: https://www.javastudy.cloud
Java 中高级开发博客: https://www.lixiang.red
Java 学习公众号: java 技术大本营

POI 简介

在 java 中,对 word/Excel 的处理,基本上都是通过 poi 来完成的
poi 地址:
https://poi.apache.org/components/spreadsheet/index.html
Word/Excel 分两大版本,对应着我们 doc/xls 和 docx/xlsx 这些后缀名,其中的 x 是代表 xml 的意思. 就是说, 新版的 Word/Excel 都可以用 XML 来表式.
使用场景:在用 word+freemarker 做模板时, 先在 word 中,用{model}占位符. 然后把 word 另存为 xml 文档. 然后在 freemarker 中,读取这个 xml 文档,然后传入 map,最后输出流再转成 word 就可以了.
当然在实际开发中,还是 Excel 用的多. 用来导入和导出数据.要使用 poi 处理 EXCEL 需要以下步骤:
1.引入相关的依赖
2. 定义实体类和 Excel 表头的关联
3. 编写 Excel 操作的工具类
3. 测试

POI 导入/导出 EXCEL

引入相关的依赖

	compile group: 'org.apache.poi', name: 'poi', version: '4.1.1'
	compile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.1'
​

定义实体类和 Excel 表头的关联

这里我定义一个 CellField 来描述这种关系,其结构如下所示

public class CellField {

    /** cell 值,字符串 */
    public static final int FIELD_STRING = 1;

    /** cell类型, 普通的 */
    public static final int CELL_NORMAL = 1;

    /** cell类型, 下拉框
     * 下拉框的中的java字段一定要和实体类的值对应,不然不好set进去,而且最好是和String类型的对应
     */
    public static final int CELL_DROP = 2;


    /** java实体类名字 */
    private String javaName;

    /** excel表头的东西 */
    private String cellName;

    /** cell值的类型,有字符串,日期,等等,默认是字符串的 */
    private Integer fieldType = 1;
    /** cell 的类型, 有普通的,下拉框,等等,默认是普通的 */
    private Integer cellType = 1;

    /** 排序值,默认是0 */
    private Integer order = 0;

    /** 默认值 */
    private Object defaultValue;

    public CellField() {
    }

    public CellField(String javaName, String cellName) {
        this.javaName = javaName;
        this.cellName = cellName;
    }

    public CellField(String javaName, String cellName, Integer cellType) {
        this.javaName = javaName;
        this.cellName = cellName;
        this.cellType = cellType;
    }

    public String getJavaName() {
        return javaName;
    }

    public CellField setJavaName(String javaName) {
        this.javaName = javaName;
        return this;
    }

    public String getCellName() {
        return cellName;
    }

    public CellField setCellName(String cellName) {
        this.cellName = cellName;
        return this;
    }

    public Integer getFieldType() {
        return fieldType;
    }

    public CellField setFieldType(Integer fieldType) {
        this.fieldType = fieldType;
        return this;
    }

    public Integer getCellType() {
        return cellType;
    }

    public CellField setCellType(Integer cellType) {
        this.cellType = cellType;
        return this;
    }

    public Integer getOrder() {
        return order;
    }

    public CellField setOrder(Integer order) {
        this.order = order;
        return this;
    }

    public Object getDefaultValue() {
        return defaultValue;
    }

    public CellField setDefaultValue(Object defaultValue) {
        this.defaultValue = defaultValue;
        return this;
    }

}
​

POI 操作 Excel 的几个基础方法

 /**
     * 创建Excel的表头
     */
    public static Row createExcelHeader(List<CellField> cellFields){
        Workbook wb = new XSSFWorkbook();
        return createExcelHeader( wb.createSheet(),cellFields);
    }

    public static Row createExcelHeader(Sheet sheet,List<CellField> cellFields){
        cellFields.sort(Comparator.comparingInt(CellField::getOrder));
        Map<String, CellField> paramMap = cellFields.stream().collect(Collectors.toMap(CellField::getJavaName, x -> x,(x,y)-> x,LinkedHashMap::new));

        int colPointer = 0;
        // 渲染第一行的表头
        Row firstRow = accessRow(sheet, 0);
        for (CellField field : cellFields) {
            String fieldName = field.getJavaName();
            if (paramMap.get(fieldName) != null) {
                Cell cell = accessCell(firstRow, colPointer++);
                // System.out.println(fieldName + "!!!!" + paramMap.get(fieldName));
                cell.setCellValue(paramMap.get(fieldName).getCellName());
                if(CELL_DROP==field.getCellType()){
                    createDropList((List<String>) field.getDefaultValue(),cell,sheet);
                }
            }
        }
        return firstRow;
    }

    /**
     * 对excel创建下拉框
     * @param list
     * @param cell
     * @param sheet
     */
    public static void createDropList(List<String> list, Cell cell, Sheet sheet) {
        CellRangeAddressList addressList = new CellRangeAddressList(1, 2000, cell.getColumnIndex(), cell.getColumnIndex());
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(list.toArray(new String[0]));
        DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
        sheet.addValidationData(validation);
    }
    /**
     * 导出Excel文件
     * @param wb
     * @return
     */
    public static void exportExcelFile(Workbook wb, String partFileName) {
        if (wb != null) {
            String fileName = "Excel-" + partFileName + "-"
                    + new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss").format(new Date()) + ".xlsx";
            OutputStream out = null;
            try {
                out = new FileOutputStream(new File(fileName));
                wb.write(out);

            } catch (IOException e) {
                e.printStackTrace();
            }
            finally {
                try {
                    if(out!=null){
                        out.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /***
     * 创建一行
     * @param sheet
     * @param rowNum
     * @return
     */
    private static Row accessRow(Sheet sheet, int rowNum) {
        Row row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }
        return row;
    }

    /**
     * 创建一个单元格
     *
     * @param row
     * @param column
     * @return
     */
    private static Cell accessCell(Row row, int column) {
        Cell cell = row.getCell(column);
        if (cell == null) {
            cell = row.createCell(column);
        }
        return cell;
    }

​

POI 把 List 导出成 Excel

/**
     * 将List对象转换为excel文档.
     *
     * @param list       POJO对象列表
     * @param cellFields 表头的描述项
     * @throws Exception
     */
    public static <T> Workbook beanToExcel(List<T> list, List<CellField> cellFields, Class<T> clazz) {
        cellFields.sort(Comparator.comparingInt(CellField::getOrder));
        Map<String, CellField> paramMap = cellFields.stream().collect(Collectors.toMap(CellField::getJavaName, x -> x,(x,y)-> x,LinkedHashMap::new));

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();

        // 处理要反射的字段
        Field[] fields = clazz.getDeclaredFields();
        Map<String, Field> fieldMap = new HashMap<>(fields.length);
        for (Field field : fields) {
            field.setAccessible(true);
            String fieldName = field.getName();
            fieldMap.put(fieldName, field);
        }

        // 渲染第一行的表头
        Row firstRow = createExcelHeader(sheet,cellFields);

        int beginNum = 1;
        for (T d : list) {

            Row row = accessRow(sheet, beginNum++);

            for (int i = 0; i < paramMap.size(); i++) {
                //开始为每行写入值
                try {
                    for (int i1 = 0; i1 < cellFields.size(); i1++) {
                        //获取表头对应的java名
                        CellField cellField = cellFields.get(i1);
                        String javaName = cellField.getJavaName();
                        String cellHeaderName = cellField.getCellName();

                        if (javaName != null && cellHeaderName.equals(firstRow.getCell(i).getStringCellValue())) {
                            Field field = fieldMap.get(javaName);
                            Cell cell = accessCell(row, i);
                            if(field ==null){
                                //对于CellFieldList里面有的字段,实体类中没有的,跳过这一列
                                cell.setBlank();
                                continue;
                            }
                            Object obj = field.get(d);
                            if (obj == null) {
                                continue;
                            } else if (String.class.isAssignableFrom(obj.getClass())) {
                                cell.setCellValue((String) obj);
                            } else if (Date.class.isAssignableFrom(obj.getClass())) {
                                // 日期存储为Number，显示需求依赖CellStyle
                                cell.setCellValue((Date) obj);
                            } else if (Number.class.isAssignableFrom(obj.getClass())) {
                                // 数字类型的也都按字符串方式来存
                                cell.setCellValue(String.valueOf(obj));
                            } else if (Boolean.class.isAssignableFrom(obj.getClass())) {
                                cell.setCellValue(((Boolean) obj));
                            } else {
                                cell.setCellValue(obj.toString());
                            }
                        }
                    }

                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        }
        return workbook;

    }

​

POI 读取 Excel 为 List

public static <T> List<T> excelToList(InputStream inputStream,List<CellField> cellFieldList, Class<T> clazz){
        List<T> list = new ArrayList<>();
        //hashMap是以键值对(key and value)来存储的
        //<列名，单元格值>
        HashMap<String, String> indexMap = new HashMap<>();
        try {
            //fileInputStream就是一个book
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
            //从book中获取第一个sheet
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
            //获取sheet的总行数
            int rows = xssfSheet.getPhysicalNumberOfRows();
            //规定第1(引索是0)行是字段名行
            XSSFRow firstRow = xssfSheet.getRow(0);
            //遍历是从第2(引索是1)行开始
            for (int i = 1; i < rows; i++) {
                //从整个sheet里面获取第i行的数据
                XSSFRow xssfRow = xssfSheet.getRow(i);
                if (xssfRow == null) {
                    //如果某一行为空 就跳过读取下一行
                    continue;
                }
                //读取一行中的每一个单元格
                for (int j = 0; j < xssfRow.getLastCellNum(); j++) {
                    //获取该单元格的列名
                    //getStringCellValue从单元格取出类型为String的值
                    String  columnCellName = firstRow.getCell(j).getStringCellValue();
                    //开始从第一列开始遍历单元格
                    XSSFCell cell = xssfRow.getCell(j);
                    String value = "";
                    if (cell != null) {
                        switch (cell.getCellType()) {
                            case FORMULA:
                                value = cell.getCellFormula();
                                break;

                            case NUMERIC:
                                value = "" + cell.getNumericCellValue();
                                break;

                            case STRING:
                                value = cell.getStringCellValue();
                                break;

                            case BLANK:
                                value = "<BLANK>";
                                break;

                            case BOOLEAN:
                                value = "" + cell.getBooleanCellValue();
                                break;

                            case ERROR:
                                value = "" + cell.getErrorCellValue();
                                break;

                            default:
                                value = "" + cell.getStringCellValue();
                        }
                    }
                    indexMap.put(columnCellName,value);
                }
                //一行循环之后，将值注入到新生成的实例中，然后放到list中
                try {

                    T t = clazz.newInstance();

                    //获取到传入Class的全部变量名
                    // 对CellList进行循环, 然后赋值
                    for (CellField cellField : cellFieldList) {
                        String javaName  = cellField.getJavaName();
                        String columnName  = cellField.getCellName();
                        try{
                            Field  field = clazz.getDeclaredField(javaName);
                            field.setAccessible(true);
                            if(field.getType()==Integer.class){
                                if(indexMap.get(columnName)!=null){
                                    double d = Double.valueOf(indexMap.get(columnName));
                                    field.set(t, (int)d);
                                }

                            }else if(field.getType()==Double.class) {
                                if(indexMap.get(columnName)!=null){
                                    double d = Double.valueOf(indexMap.get(columnName));
                                    field.set(t, d);
                                }
                            }else if(field.getType()== BigDecimal.class) {
                                if(indexMap.get(columnName)!=null){
                                    BigDecimal b = new BigDecimal(indexMap.get(columnName));
                                    field.set(t, b);
                                }

                            }else {
                                field.set(t, indexMap.get(columnName));
                            }
                        }catch (NoSuchFieldException e){
                            // 如果发生了这个异常,说明没有对应的java字段,可能是下拉框
                            // 对异常不做处理,也就是对这个不做处理

                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        }
                    }

                    list.add(t);

                } catch (InstantiationException | IllegalAccessException e) {
                    e.printStackTrace();
                }

            }

        } catch (IOException e) {
            e.printStackTrace();
        }

        return list;
    }

​

DEMO 总评

Java 操作 Excel 基本上都是通过 POI,有很多人有不同的封装, 大体上思路都是差不多的.本 DEMO 的思路也仅供参考,两三天简单封装了一下, 考虑的也不是太全面.欢迎小伙伴们一起完善,加油!