导入导出笔记

import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 功能描述  解析excel  导出excel 工具类
 */
public final class ExcelUtils {


    /**
     * 解析 excel
     * @param users
     * @param <T>
     * @return
     * @throws IllegalAccessException
     */
    public static <T> SXSSFWorkbook createUserXlsFa(List<T> users) throws IllegalAccessException {
        //创建一个新的XSSFWorkbook对象
        XSSFWorkbook xssfWorkbook=new XSSFWorkbook();

        SXSSFWorkbook excel = new SXSSFWorkbook(xssfWorkbook , 1000);
        //创建一个excel工作表
        Sheet sheetAt=excel.createSheet("用户信息表");
        //创建表头
        Row row;
        Cell cell;
        int rowNum=0;
        row=sheetAt.createRow(rowNum);
        rowNum++;
        //取出其中一个进行反射，设置表头属性
        T object=users.get(0);
        Class tempClass=object.getClass();
        List<Field>  filesList= Arrays.asList(tempClass.getDeclaredFields());
        int m=0;
        for (Field field: filesList){
            //判断属性上有无此Excel注解
            if(field.isAnnotationPresent(Excel.class)){
                //获取注解对象
                Excel annotation = field.getAnnotation(Excel.class);
                if(annotation.isExcel()){
                    continue;
                }else {
                    cell=row.createCell(m);
                    cell.setCellValue(annotation.name().equals("fieldName")?field.getName():annotation.name());
                }
            }else {
                cell=row.createCell(m);
                cell.setCellValue(field.getName());
            }
            m++;
        }
        //根据反射获取属性值填入excel中
        for (T obj : users){
            m=0;
            row=sheetAt.createRow(rowNum);
            for (Field field:filesList) {
                String type = field.getGenericType().toString();
                if(field.isAnnotationPresent(Excel.class)){
                    Excel annotation = field.getAnnotation(Excel.class);
                    if(annotation.isExcel()){
                        continue;
                    }else {
                        cell=row.createCell(m);
                        field.setAccessible(true);
                        setValue(type,field.get(obj),cell);
                    }
                }else {
                    cell=row.createCell(m);
                    field.setAccessible(true);
                    setValue(type,field.get(obj),cell);
                }
                m++;
            }
            rowNum++;
        }
        return excel;
    }

    /**
     * 导出excel
     * @param users
     * @param <T>
     * @throws IOException
     */
    public static<T> void exportExcel(List<T> users) throws IOException {
        SXSSFWorkbook wb;
        try {
            wb=createUserXlsFa(users);
            ByteArrayOutputStream byteArrayOutputStream=new ByteArrayOutputStream();
            wb.write(byteArrayOutputStream);
            byte[] xlsxBytes = byteArrayOutputStream.toByteArray();
            OutputStream out = new FileOutputStream("D://excel.xlsx");
            out.write(xlsxBytes);
            out.close();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
    }

    /**
     * 测试 启动
     * @param args
     * @throws IOException
     */
    public static void main(String[] args) throws  IOException {
        List<Dog> objects = new ArrayList<>();
        for (int i = 0; i < 3; i++) {
            Dog dog = new Dog();
            dog.setSex(i%2==0?0:1);
            dog.setName("狗狗"+i+"号");
            dog.setAge(5);
            dog.setPrice(1.0);
            dog.setPrice1(2.0);
            dog.setPrice2(BigDecimal.valueOf(3.0));
            dog.setFlag(true);
            dog.setFlag1(false);
            dog.setBigNum(2089146174099386620L);
            objects.add(dog);
        }
        exportExcel(objects);
        //解析excel 成集合
        List<String[]> list = readExcel("");
        //转化成对象
        List<Dog> dogs = changeToObject(list,Dog.class);
    }

    /**
     * 根据类型 把值写到excel中
     * @param type
     * @param obj
     * @param cell
     */
    private static void setValue(String type ,Object obj,Cell cell){
        switch (type) {
            case "class java.lang.String":
                cell.setCellValue(ObjectUtils.isEmpty(obj)?null:String.valueOf(obj));
                break;
            case "int":
            case "class java.lang.Integer":
                cell.setCellValue(ObjectUtils.isEmpty(obj)?0:Integer.parseInt(String.valueOf(obj)));
                break;
            case "double":
            case "class java.lang.Double":
                cell.setCellValue(ObjectUtils.isEmpty(obj)?0.0:Double.parseDouble(String.valueOf(obj)));
                break;
            case "long":
            case "class java.lang.Long":
                cell.setCellValue(ObjectUtils.isEmpty(obj)?0L:Long.parseLong(String.valueOf(obj)));
                break;
            case "short":
            case "class java.lang.Short":
                cell.setCellValue(ObjectUtils.isEmpty(obj)?0:Short.parseShort(String.valueOf(obj)));
                break;
            case "char":
            case "class java.lang.Character":
                cell.setCellValue(ObjectUtils.isEmpty(obj)?null:(Character)obj);
                break;
            case "float":
            case "class java.lang.Float":
                cell.setCellValue(ObjectUtils.isEmpty(obj)?0F:Float.parseFloat(String.valueOf(obj)));
                break;
            case "class java.math.BigDecimal":
                cell.setCellValue(ObjectUtils.isEmpty(obj)?0:Double.parseDouble(String.valueOf(obj)));
                break;
            case "boolean":
            case "class java.lang.Boolean":
                cell.setCellValue(ObjectUtils.isEmpty(obj)?null:(Boolean) obj);
                break;
            default:
                break;
        }

    }

    /**
     * 导入excel 读取文件
     * @param path 文件路径
     * @return
     */
    public static List<String[]> readExcel(String path) {
        path = "D://excel.xlsx";
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        List<String[]> list = null;
        try {
            //同时支持Excel 2003、2007
            //创建文件对象
            File excelFile = new File(path);
            //文件流
            FileInputStream is = new FileInputStream(excelFile);
            //这种方式 Excel 2003/2007/2010 都是可以处理的
            Workbook workbook = WorkbookFactory.create(is);
            //Sheet的数量
            int sheetCount = workbook.getNumberOfSheets();
            //存储数据容器
            list = new ArrayList<String[]>();
            //遍历每个Sheet
            for (int s = 0; s < sheetCount; s++) {
                Sheet sheet = workbook.getSheetAt(s);
                //获取总行数
                int rowCount = sheet.getPhysicalNumberOfRows();
                //遍历每一行
                for (int r = 0; r < rowCount; r++) {
                    Row row = sheet.getRow(r);
                    //获取总列数
                    int cellCount = row.getPhysicalNumberOfCells();
                    //用来存储每行数据的容器
                    String[] model = new String[cellCount];
                    //遍历每一列
                    for (int c = 0; c < cellCount; c++) {
                        Cell cell = row.getCell(c);
                        CellType cellType = cell.getCellType();

                        String cellValue = null;
                        switch (cellType.getCode()) {
                            //文本
                            case 1:
                                cellValue = cell.getStringCellValue();
                                break;
                            //数字、日期
                            case 0:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    cellValue = fmt.format(cell.getDateCellValue());
                                } else {
                                    //解析excel 数字格式 默认会带小数点 比如 1 解析 就会成1.0 格式化数字
                                    DecimalFormat df = new DecimalFormat("0");
                                    cellValue = df.format(cell.getNumericCellValue());
                                }
                                break;
                            //布尔型
                            case 4:
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            //空白
                            case 3:
                                cellValue = cell.getStringCellValue();
                                break;
                            //错误
                            case 5:
                                cellValue = "错误";
                                break;
                            //公式
                            case 2:
                                cellValue = "错误";
                                break;
                            default:
                                cellValue = "错误";

                        }
                        model[c] = cellValue;
                    }
                    //model放入list容器中
                    list.add(model);
                }
            }
            is.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }


    private static <T> List<T> changeToObject(List<String[]> list, Class<T> d){
        String[] strings = list.get(0);
        List<T> objects = new ArrayList<>();
        for(int i = 1 ;i <list.size();i++){
            objects.add(setValue1(strings,list.get(i),d));
        }
        return objects;
    }

    /**
     * 反射 给对象赋值
     * @param strings
     * @param strs
     * @param d
     * @param <T>
     * @return
     */
    private static <T> T setValue1(String[] strings, String[] strs, Class<T> d) {
        Field[] fields = d.getDeclaredFields();
        T t = null;
        try {
            // 通过类的详情信息，创建目标对象 这一步等同于UserTwo target = new UserTwo()；
            t = d.newInstance();
            for (Field f : fields) {
                f.setAccessible(true);
                for (int i = 0; i < strings.length; i++) {
                    if (f.getName().equals(strings[i])) {
                        // 获取目标对象的属性名，将属性名首字母大写，拼接如：setUsername、setId的字符串
                        String tName = f.getName();
                        char[] tChars = tName.toCharArray();
                        tChars[0] -= 32;
                        String tMethodName = "set" + String.valueOf(tChars);
                        /* 获得属性的set方法 */
                        Method tMethod = d.getMethod(tMethodName, f.getType());
                        //根据属性的类型转化需要赋值的类型
                        String type = f.getGenericType().toString();
                        changeValue(tMethod,t, strs[i],type);
                        break;

                    }

                }

            }
        } catch (Exception e) {
            System.out.println(e);

        }
        return t;
    }

    /**
     * 根据类型赋值
     * @param tMethod
     * @param t
     * @param str
     * @param type
     * @param <T>
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    private static <T> void changeValue(Method tMethod,T t, String str, String type ) throws InvocationTargetException, IllegalAccessException {
        switch (type) {
            case "class java.lang.String":
            case "char":
            case "class java.lang.Character":
                // 调用方法，并将源对象get方法返回值作为参数传入
                tMethod.invoke(t, str);
                break;
            case "int":
            case "class java.lang.Integer":
                tMethod.invoke(t, Integer.parseInt(str));
                break;
            case "double":
            case "class java.lang.Double":
                tMethod.invoke(t, Double.parseDouble(str));
                break;
            case "long":
            case "class java.lang.Long":
                tMethod.invoke(t, Long.parseLong(str));
                break;
            case "short":
            case "class java.lang.Short":
                tMethod.invoke(t,Short.parseShort(str));
                break;
            case "float":
            case "class java.lang.Float":
                tMethod.invoke(t, Float.parseFloat(str));
                break;
            case "class java.math.BigDecimal":
                tMethod.invoke(t,new BigDecimal(str));
                break;
            case "boolean":
            case "class java.lang.Boolean":
                if(StringUtils.isNotEmpty(str)){
                    if("true".equals(str)){
                        tMethod.invoke(t,true);
                    }else {
                        tMethod.invoke(t,false);
                    }
                }else {
                    tMethod.invoke(t,false);
                }
                break;
            default:
                break;
        }
    }

}

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;


/**
 * 功能描述 自定义注解
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Excel {
    String name() default "fieldName";
    
    //字段是否需要导出，true不需要 默认false
    boolean isExcel() default false;
}

import lombok.Data;

import java.math.BigDecimal;

/**
 * 功能描述 模拟导出，导入的对象
 */
@Data
public class Dog {

    @Excel(name = "name")
    private String name;

    @Excel(name = "sex")
    private int sex;

    @Excel(name = "age")
    private Integer age;

    @Excel(name = "price")
    private double price;

    @Excel(name = "price1")
    private Double price1;

    @Excel(name = "price2")
    private BigDecimal price2;

    @Excel(name = "flag")
    private boolean flag;

    @Excel(name = "flag1")
    private Boolean flag1;

    @Excel(name = "bigNum")
    private long bigNum;
}