EXCEL导入导出

一、使用Apache POI方式

1.1  基本实现方式

        HSSFWorkbook sheets = new HSSFWorkbook();
        HSSFSheet sheet = sheets.createSheet(aClass.getName());
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell=null;
        for (int i = 0; i <declaredFields.length ; i++) {
            cell= row.createCell(i);
            cell.setCellValue(declaredFields[i].getName());
        }
        sheets.write(new FileOutputStream("C:\Users\yaohuiqin\Desktop\excel\excelname.xls"));    

1.2 列合并

        int mergeBeginRow = 3;
        int mergeEndRow = 4;
        int columnIndex = 0;
        sheet.addMergedRegion(new CellRangeAddress(
                mergeBeginRow , //first row (0-based)
                mergeEndRow, //last row  (0-based)
                columnIndex, //first column (0-based)
                columnIndex  //last column  (0-based)
        ));    

1.3 直接读EXCEL

package com.amazing.poi.excel.test;

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

/**
 * @author yaohuiqin
 * @description
 * @date 2020-04-20
 */
public class ReadTest {
    public static void main(String[] args) throws IOException {
        ReadTest readTest = new ReadTest();
        readTest.readexcel();
    }

    public void readexcel() throws IOException {
        System.out.println("开始读。。。。。。");
        String path = "C:\Users\yaohuiqin\Desktop\excel\m-cell-3.xls";
        FileInputStream fis = new FileInputStream(path);
        Workbook wb = WorkbookFactory.create(fis);
        Sheet sheetAt = wb.getSheetAt(0);
        Iterator<Row> rowIterator = sheetAt.rowIterator();
        while (rowIterator.hasNext()) {
            Row next = rowIterator.next();
            rowhandler(next);
            System.out.println();
        }
    }

    public void rowhandler(Row row0) {
        Iterator<Cell> cellIterator = row0.cellIterator();
        while (cellIterator.hasNext()) {
            Cell next = cellIterator.next();
            Object getvalue = getvalue(next);
            System.out.print(getvalue + "    ");
        }
    }

    public Object getvalue(Cell tmp) {
        Object value = null;
        switch (tmp.getCellType()) {
            case _NONE:
                break;
            case BLANK:
                break;
            case STRING:
                value = tmp.getStringCellValue();
                break;
            case BOOLEAN:
                value = tmp.getBooleanCellValue();
                break;
            case FORMULA:
                value = tmp.getCellFormula();
                break;
            case NUMERIC:
                value = tmp.getNumericCellValue();
                break;
            case ERROR:
                value = tmp.getErrorCellValue();
                break;
            default:
                break;
        }
        return value;
    }
}

1.4 读取excel，将excel转为对象列表

@Documented
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface excelvalue {
    String name() default "";
}

public class Person {
    @excelvalue(name = "姓名")
    String name;
    @excelvalue(name = "年龄")
    int age;
    @excelvalue(name = "性别")
    String gender;
//get and set
}

package com.amazing.poi.excel.test;

import com.amazing.poi.excel.commons.ObjectCastUtils;
import org.apache.poi.ss.usermodel.*;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

/**
 * @author yaohuiqin
 * @description
 * @date 2020-04-20
 */
public class ReadToObjectTest {
    public static void main(String[] args) throws Exception {
        ReadToObjectTest readToObjectTest = new ReadToObjectTest();
        String path = "C:\Users\yaohuiqin\Desktop\excel\m-cell-3.xls";
        FileInputStream fis = new FileInputStream(path);
        Person person = new Person();
        List<Person> peoples = readToObjectTest.readExcel(person, fis);
        for(Person people : peoples){
            System.out.println(people);
        }
    }
    public <T> List<T> readExcel(T t, FileInputStream fis) throws Exception {
        Workbook wb = WorkbookFactory.create(fis);
        List<T> list = new LinkedList<T>();
        Sheet sheetAt = wb.getSheetAt(0);
        Iterator<Row> rowIterator = sheetAt.rowIterator();
        Map<Integer, Method> headerhandler = null;
        //处理表头
        if(rowIterator.hasNext()){
            Row headerRow = rowIterator.next();
            headerhandler = headerhandler(t, headerRow);
        }
        while (rowIterator.hasNext()) {
            Row next = rowIterator.next();
            T t1 = rowhandler(t,next,headerhandler);
            list.add(t1);
        }
        return list;
    }

    public <T> Map<Integer,Method> headerhandler(T t, Row headerRow) throws Exception {
        Iterator<Cell> cellIterator = headerRow.cellIterator();
        int i = 1;
        Map<Integer,Method> map = new HashMap<Integer,Method>();
        while (cellIterator.hasNext()) {
            Cell next = cellIterator.next();
            Object getvalue = getvalue(next);
            Method writeMethod = getcellHandler(t, getvalue);
            if(writeMethod == null){
                throw new Exception("excel头部和对象不匹配");
            }
            map.put(i,writeMethod);
            i++;
        }
        return map;
    }

    public <T> T  rowhandler(T t, Row row0, Map<Integer, Method> headerhandler) throws IllegalAccessException, InstantiationException, InvocationTargetException {
        Class<?> aClass = t.getClass();
        T object = (T) aClass.newInstance();
        Iterator<Cell> cellIterator = row0.cellIterator();
        int i = 1;
        while (cellIterator.hasNext()) {
            Cell next = cellIterator.next();
            Object getvalue = getvalue(next);
            Method writeMethod = headerhandler.get(i);
            Class<?>[] parameterTypes = writeMethod.getParameterTypes();
            Class<?> parameterType = parameterTypes[0];
            Object castValue = ObjectCastUtils.objectCast(parameterType,getvalue);
            i++;
            writeMethod.invoke(object,castValue);
        }
        return object;
    }

    public <T> Method getcellHandler(T t,Object getvalue) throws IntrospectionException {
        System.out.println(getvalue.getClass());
        Field[] declaredFields = t.getClass().getDeclaredFields();
        List<String > list = new ArrayList<>();
        for(Field field :declaredFields){
            excelvalue annotation = field.getAnnotation(excelvalue.class);
            String name = annotation.name();
            if(name.equals(getvalue)){
               PropertyDescriptor pd = new PropertyDescriptor(field.getName(), t.getClass());
               Method writeMethod = pd.getWriteMethod();
               return writeMethod;
            }
        }
        return null;
    }

    public Object getvalue(Cell tmp) {
        Object value = null;
        switch (tmp.getCellType()) {
            case _NONE:
                break;
            case BLANK:
                break;
            case STRING:
                value = tmp.getStringCellValue();
                break;
            case BOOLEAN:
                value = tmp.getBooleanCellValue();
                break;
            case FORMULA:
                value = tmp.getCellFormula();
                break;
            case NUMERIC:
                value = tmp.getNumericCellValue();
                break;
            case ERROR:
                value = tmp.getErrorCellValue();
                break;
            default:
                break;
        }
        return value;
    }
}