Java实现excel表格读写

目录

一、创建注解

二、根据excel表格内容创建相关类

三、创建ExcelUtils类实现读写功能

四、在mian中调用ExcelUtils类函数

一、创建注解

//使用元注解，表示该注解不仅被保存到class文件中，jvm加载class文件之后，仍然存在
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {
    //列数
    public int columnIndex() default 0;
    //列名
    public String columnName() default "";
}

二、根据excel表格内容创建实体类

以下表格为例，创建相应实体类。

实现代码：

@Data
@ToString
public class EmployeeProvidentRatio {
    //使用注解说明列数和列名
    @ExcelField(columnIndex = 0, columnName = "工号")
    private String empNo;
    @ExcelField(columnIndex = 1, columnName = "姓名")
    private String name;
    @ExcelField(columnIndex = 2, columnName = "工资基数")
    private double wageBase;
    @ExcelField(columnIndex = 3, columnName = "公积金比例")
    private double providentRatio;
}

三、创建ExcelUtils类实现读写功能

public class ExcelUtils {


    /**
     * 按照classpath:template_enterprise_monthy_cost.xlsx格式输出excel文件
     * @param data 数据集合
     * @param target 输出路径
     * @param startRow 开始输出行
     */
    public static void writeExcelFile(List<?> data, String target, int startRow) {
        
        //读取导出表格的表头格式 将template_enterprise_monthy_cost.xlsx放至resource文件夹中
        Workbook wb =  workBookFromClasspath("/template_enterprise_monthy_cost.xlsx");

        // 创建sheet
        Sheet sheet = wb.getSheetAt(0);

        // 行
        Row row = null;

        // 创建单元格样式
        CellStyle style = wb.createCellStyle();
        // 居中显示
        style.setAlignment(HorizontalAlignment.CENTER);

        // 获取实体所有属性
        Field[] fields = data.get(0).getClass().getDeclaredFields();
        // 列索引
        int index = 0;
        //字段注解
        ExcelField myAnnotation;

        int rowIndex = startRow;
        for (Object obj : data) {
            // 创建新行，索引加1,为创建下一行做准备
            row = sheet.createRow(rowIndex++);
            for (Field f : fields) {
                // 设置属性可访问
                f.setAccessible(true);
                // 判断是否是注解
                if (f.isAnnotationPresent(ExcelField.class)) {
                    // 获取注解
                    myAnnotation = f.getAnnotation(ExcelField.class);
                    // 获取列索引
                    index = myAnnotation.columnIndex();
                    try {
                        // 创建单元格     f.get(obj)从obj对象中获取值设置到单元格中
                        createCell(row, index, (f.get(obj)), style);
                    } catch (IllegalArgumentException e) {
                        e.printStackTrace();
                    } 
                }
            }
        }

        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(target);
            wb.write(outputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            try {
                if (wb != null) {
                    try {
                        wb.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 读取excel文件，并把读取到的数据封装到clazz中
     *
     * @param path
     *            文件路径
     * @param clazz
     *            实体类
     * @return 返回clazz集合
     */
    public static <T extends Object> List<T> readExcelFile(String path, Class<T> clazz) {
        // 存储excel数据
        List<T> list = new ArrayList<>();

        Workbook wookbook = workBookFromFile(path);
        // 得到一个工作表
        Sheet sheet = wookbook.getSheetAt(0);

        // 获取行总数
        int rows = sheet.getLastRowNum() + 1;

        Row row;

        // 获取类所有属性
        Field[] fields = clazz.getDeclaredFields();

        T obj = null;
        int coumnIndex = 0;
        Cell cell = null;
        ExcelField myAnnotation = null;
        for (int i = 1; i < rows; i++) {
            // 获取excel行
            row = sheet.getRow(i);
            try {
                // 创建实体
                obj = clazz.getDeclaredConstructor().newInstance();
                for (Field f : fields) {
                    // 设置属性可访问
                    f.setAccessible(true);
                    // 判断是否是注解
                    if (f.isAnnotationPresent(ExcelField.class)) {
                        // 获取注解
                        myAnnotation = f.getAnnotation(ExcelField.class);
                        // 获取列索引
                        coumnIndex = myAnnotation.columnIndex();
                        // 获取单元格
                        cell = row.getCell(coumnIndex);
                        // 设置属性
                        setFieldValue(obj, f, wookbook, cell);
                    }
                }
                // 添加到集合中
                list.add(obj);
            } catch (InstantiationException e1) {
                e1.printStackTrace();
            } catch (IllegalAccessException e1) {
                e1.printStackTrace();
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }

        }

        try {
            //释放资源
            wookbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 设置属性值
     *
     * @param obj
     *            操作对象
     * @param f
     *            对象属性
     * @param cell
     *            excel单元格
     */
    private static void setFieldValue(Object obj, Field f, Workbook wookbook, Cell cell) {
        try {
            if (f.getType() == int.class || f.getType() == Integer.class) {
                f.set(obj, getInt(cell));
            } else if (f.getType() == Double.class || f.getType() == double.class) {
                f.set(obj, getDouble(null, cell));
            } else {
                f.set(obj, getString(cell));
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取正数
     *
     * @param cell
     * @return
     */
    @SuppressWarnings("deprecation")
    private static int getInt(Cell cell) {
        if (cell == null) {
            return 0;
        }
        if (cell.getCellType() == CellType.BLANK) {
            return 0;
        }
        if (cell.getCellType() == CellType.STRING) {
            return Integer.parseInt(getString(cell));
        }
        return Integer.parseInt(NumberToTextConverter.toText(cell.getNumericCellValue()));
    }

    /**
     * 获取double stringpute svc
     *
     * @param cell
     * @return
     */
    @SuppressWarnings("deprecation")
    private static double getDouble(Workbook wookbook, Cell cell) {
        double d = 0;
        switch (cell.getCellType()) {
            case BLANK:
                // 空白字符 返回0
                d = 0;
                break;
            case FORMULA:
                // 公式
                FormulaEvaluator formulaEval = wookbook.getCreationHelper().createFormulaEvaluator();
                d = formulaEval.evaluate(cell).getNumberValue();
                break;
            case NUMERIC:
                // 数字格式
                d = Double.parseDouble(NumberToTextConverter.toText(cell.getNumericCellValue()));
                break;
            case STRING:
                d = Double.parseDouble(getString(cell));
                break;
            case BOOLEAN:
                break;
            case ERROR:
                break;
            default:
                d = 0;
                break;
        }
        return d;
    }

    /**
     * 获取字符串
     *
     * @param cell
     * @return
     */
    @SuppressWarnings("deprecation")
    private static String getString(Cell cell) {
        if (cell == null) {
            return "";
        }
        if (cell.getCellType() == CellType.BLANK) {
            return "";
        } else if (cell.getCellType() == CellType.NUMERIC) {
            return NumberToTextConverter.toText(cell.getNumericCellValue());
        }
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue().toString();
    }

    /**
     * 从classpath下的模板构建一个Workbook
     * @param templateName
     * @return
     */
    private static Workbook workBookFromClasspath(String templateName) {
        InputStream is = ExcelUtils.class.getResourceAsStream(templateName);
        return createWorkbook(is, templateName);
    }

    /**
     * 从一个外部路径获取一个Workbook
     * @param path
     * @return
     */
    private static Workbook workBookFromFile(String path) {
        FileInputStream is = null;
        try {
            is = new FileInputStream(new File(path));
        } catch (FileNotFoundException e1) {
            throw new RuntimeException("文件路径异常");
        }
        return createWorkbook(is, path);
    }
    private static Workbook createWorkbook(InputStream is, String path) {
        Workbook wookbook = null;
        // 根据excel文件版本获取工作簿
        if (path.endsWith(".xls")) {
            wookbook = xls(is);
        } else if (path.endsWith(".xlsx")) {
            wookbook = xlsx(is);
        } else {
            throw new RuntimeException("文件出错，非excel文件");
        }
        return wookbook;
    }
    /**
     * 对excel 2003处理
     */
    private static Workbook xls(InputStream is) {
        try {
            // 得到工作簿
            return new HSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 对excel 2007处理
     */
    private static Workbook xlsx(InputStream is) {
        try {
            // 得到工作簿
            return new XSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 创建单元格
     *
     * @param row
     * @param c
     * @param cellValue
     * @param style
     */
    private static void createCell(Row row, int c, Object cellValue, CellStyle style) {
        Cell cell = row.createCell(c);
        if (cellValue == null) {
            String nullVal = null;
            cell.setCellValue(nullVal);
        } else if (cellValue instanceof String) {
            cell.setCellValue((String)cellValue);
        } else if (cellValue instanceof Double) {
            cell.setCellValue((Double)cellValue);
        } else if (cellValue instanceof Integer) {
            cell.setCellValue((Integer)cellValue);
        } else {
            cell.setCellValue(String.valueOf(cellValue));
        }
        cell.setCellStyle(style);
    }

}

 四、在mian中调用ExcelUtils类函数

List<EmployeeProvidentRatio> employeeProvidentRatios = 、
ExcelUtils.readExcelFile("./data/员工五险一金申报表.xlsx", EmployeeProvidentRatio.class);
String target = "./data/企业人员月度工资成本支付表.xlsx";
//mylist为存储写入表格内容对象的队列
ExcelUtils.writeExcelFile(mylist, target, 2);