POI 实体类对象集合 的读取与写入 源码

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

package com.example.demo.common;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.junit.jupiter.api.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Author Mr.Man
 * @Date 2020-11-25 23:59
 * Excel表格的读取与创建
 *
 * 第一步 传入数据  使用方法 setWork
 *
 * 第二步（导表）
 **/
public class ExcelUtil<E> {
    public static List<List<String>> cells = new ArrayList<>();

    /**
     * 工作簿对象
     */
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

    /**
     * 工作表对象
     */
    HSSFSheet hssfSheet = hssfWorkbook.createSheet();


    /**
     * 设置第i行的内容
     * 例如 i=0 list={"姓名“，”年龄“，”日期“，”性别“}
     *
     * 第一行的长度  后面都不能超过 否者报空
     *
     * 在表格中显示为：
     * 第一行 姓名 年龄  日期    性别
     * 例如 i=1 list={“man”,“12”,“2020-11”,“男”}
     * 第二行 man  12  2020-11  男
     *
     *
     * @param
     * @param list 行的值
     * @return 返回值为1时设置成功 为null时测试失败
     */
    public Integer setWork(List list){
        try {
            cells.add(list);
            if(cells.get(0).size()>=list.size()){
                return 1;
            }else {
                boolean remove = cells.remove(list);
                return null;
            }

        }catch (Exception e){
            return null;
        }
    }


    /**
     * 开始创建
     * @param path
     * @return
     */
    public String createWork(String path){
        try {
            String xlsPath =path +"test.xls";
            File file = new File(path);
            file.mkdirs();
            File file1 = new File(file, "test.xls");
            boolean newFile = file1.createNewFile();
            hssfWorkbook.setSheetName(0,"sheet01");
            for (int i = 0; i < cells.size(); i++) {
                HSSFRow row = hssfSheet.createRow((short)i);
                //一行的数据
                for (int j = 0; j < cells.get(0).size(); j++) {
                    //设置单元格内容
                    HSSFCell cell = row.createCell((short)j);
                    cell.setCellValue(cells.get(i).get(j));
                }
            }
            FileOutputStream fout = new FileOutputStream(file1);
            hssfWorkbook.write(fout);
            fout.flush();
            fout.close();
            System.out.println("文件生成");
            return xlsPath;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            for (int i = 0; i < cells.size(); i++) {
                cells.remove(i);
            }
        }
    }


    /**
     * 只读取第一个工作表，  hssfWorkbook.getSheetAt(0); 这行代码规定了获取的工作表
     * 读取excel表格
     * @param file 表格的对象  只能为xsl格式
     * @return
     */
    public List<List<String>> readWork(File file){
        try {
            FileInputStream fileInputStream = new FileInputStream(file);
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);
            HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);

            int lastRowNum = sheetAt.getLastRowNum();

            List<List<String>> list = new ArrayList<>();

            for (int i = 0; i <= lastRowNum; i++) {
                HSSFRow row = sheetAt.getRow(i);

                List<String> li = new ArrayList<>();

                short lastCellNum = row.getLastCellNum();
                for (int j = 0; j < lastCellNum; j++) {
                    row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
                    li.add(row.getCell(j).getStringCellValue());
                }
                list.add(li);
            }
            System.out.println("读取成功");
            return list;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            for (int i = 0; i < cells.size(); i++) {
                cells.remove(i);
            }
        }
    }


    /**
     * 读取示例： "E:\\1.xls"为excel表格的位置 全类名
     */
    @Test
    public void test01(){
        File file = new File("E:\\1.xls");
        List<List<String>> lists = readWork(file);


        cells=lists;
        createWork("");
    }

    /**
     * 通过实体类对象 写入表格
     * @param list
     * @param path
     */
    public void WritExcel(List<E> list,String path){
        if (list!=null && list.size()>0){
            E e = list.get(0);
            Field[] declaredFields = e.getClass().getDeclaredFields();
            List<String> strings = new ArrayList<>();
            for (Field declaredField : declaredFields) {
                    strings.add(declaredField.getName());
            }
            cells.add(strings);

            for (E e1 : list) {
                List<String> tempString = new ArrayList<>();
                for (Field declaredField : declaredFields) {
                    String function = functionSet(declaredField,e1);
                    tempString.add(function);
                }
                cells.add(tempString);
            }
            createWork(path);
        }else {
            System.out.println("集合内请存入数值");
        }
    }

    /**
     * 读取excel表格，返回指定E 类型对象的 集合
     * @param file
     * @param c
     * @return
     */
    public List<E> ReadExcel(File file,Class<E> c){
        List<List<String>> lists = readWork(file);
        List<E> es = new ArrayList<>();
//        E e = null;
        Field[] declaredFields = c.getDeclaredFields();
        for (int j =1 ;j <lists.size() ; j++) {
            E e2 = null;
            try {
                e2 = c.newInstance();
                for (int i = 0; i < lists.get(j).size(); i++) {
                    Field declaredField = declaredFields[i];
                    functionGet(declaredField, e2, lists.get(j).get(i));
                }
                es.add(e2);
            } catch (Exception Exception) {
                Exception.printStackTrace();
            }
        }
        return es;
    }
    /**
     * 读取表格
     * @param field
     * @param object
     * @return
     */
    public String functionSet(Field field, E object){
        try {
            if (field.getGenericType().toString().equals("class java.lang.String")) { // 如果type是类类型，则前面包含"class "，后面跟类名
                Method m = (Method) object.getClass().getMethod("get" + getMethodName(field.getName()));
                String val = null;// 调用getter方法获取属性值
                val = (String) m.invoke(object);

                if (val != null) {
                    return val;
                }
            }
            if (field.getGenericType().toString().equals("class java.lang.Integer")) {
                Method m = (Method) object.getClass().getMethod(
                        "get" + getMethodName(field.getName()));
                Integer val = (Integer) m.invoke(object);
                if (val != null) {
                    return val+"";
                }
            }
            if (field.getGenericType().toString().equals("class java.lang.Double")) {
                Method m = (Method) object.getClass().getMethod(
                        "get" + getMethodName(field.getName()));
                Double val = (Double) m.invoke(object);
                if (val != null) {
                    return val+"";
                }
            }
            if (field.getGenericType().toString().equals("class java.lang.Boolean")) {
                Method m = (Method) object.getClass().getMethod(
                        field.getName());
                Boolean val = (Boolean) m.invoke(object);
                if (val != null) {
                    return val +"";
                }
            }
            if (field.getGenericType().toString().equals("boolean")) {
                Method m = (Method) object.getClass().getMethod(
                        field.getName());
                Boolean val = (Boolean) m.invoke(object);
                if (val != null) {
                    return val+"";

                }
            }
            if (field.getGenericType().toString().equals("class java.util.Date")) {
                Method m = (Method) object.getClass().getMethod(
                        "get" + getMethodName(field.getName()));
                Date val = (Date) m.invoke(object);
                if (val != null) {
                    return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(val);
                }
            }
            if (field.getGenericType().toString().equals("class java.lang.Short")) {
                Method m = (Method) object.getClass().getMethod(
                        "get" + getMethodName(field.getName()));
                Short val = (Short) m.invoke(object);
                if (val != null) {
                    return val+"";
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    }

    public E functionGet(Field field,E object,String val){
        String countTemp="";
        try {
            if (field.getGenericType().toString().equals("class java.lang.String")) { // 如果type是类类型，则前面包含"class "，后面跟类名
                countTemp ="String type:" + val;
                Method m = (Method) object.getClass().getMethod("set" + getMethodName(field.getName())
                ,String.class
                );
                m.invoke(object,val);
            }
            if (field.getGenericType().toString().equals("class java.lang.Integer")) {
                countTemp ="Integer type:" + val;
                Method m = (Method) object.getClass().getMethod(
                        "set" + getMethodName(field.getName()),Integer.class);
                int i = Integer.parseInt(val);
                m.invoke(object,i);
            }
            if (field.getGenericType().toString().equals("class java.lang.Double")) {
                countTemp ="Double type:" + val;
                Method m = (Method) object.getClass().getMethod(
                        "set" + getMethodName(field.getName()),Double.class);
                double v = Double.parseDouble(val);
                m.invoke(object,v);
            }
            if (field.getGenericType().toString().equals(countTemp ="class java.lang.Boolean")) {
                countTemp ="Boolean type:" + val;
                Method m = (Method) object.getClass().getMethod(
                        field.getName(),Boolean.class);
                boolean b = Boolean.parseBoolean(val);
                m.invoke(object,b);
            }
            if (field.getGenericType().toString().equals("boolean")) {
                countTemp ="boolean type:" + val;
                Method m = (Method) object.getClass().getMethod(
                        field.getName(),Boolean.class);
                boolean b = Boolean.parseBoolean(val);
                m.invoke(object,b);
            }
            if (field.getGenericType().toString().equals("class java.util.Date")) {
                countTemp ="Date type:" + val;
                Method m = (Method) object.getClass().getMethod(
                        "set" + getMethodName(field.getName()),Date.class);
                Date parse = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(val);
                m.invoke(object,parse);
            }
            if (field.getGenericType().toString().equals(countTemp ="class java.lang.Short")) {
                countTemp ="Short type:"  + val;
                Method m = (Method) object.getClass().getMethod(
                        "set" + getMethodName(field.getName()));
                short i = Short.parseShort(val);
                m.invoke(object,i);
            }
        }catch (Exception e){
            System.out.println(countTemp+"  ；属性名为 ："+field.getName()+"该属性未赋值；");
            e.printStackTrace();
        }
        return object;
    }
    private String getMethodName(String name) {
        String s = name.substring(0, 1).toUpperCase() + name.substring(1);
        return s;
    }
}