spring-pio导入

前期准备依赖

   <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.9</version>
        </dependency>

自定义注解定义接口

package com.annota;

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

@Retention(RetentionPolicy.RUNTIME)
@Target({java.lang.annotation.ElementType.FIELD})
public @interface ExcelAttribute {
    /**
     * Excel中的列名
     */
    String name();
    /**
     * 列名对应的A,B,C,D...,不指定按照默认顺序排序
     */
    String column() default "";

    /**
     * 提示信息
     */
    public abstract String prompt() default "";

    /**
     * 设置只能选择不能输入的列内容
     */
     String[] combo() default {};
    /**
     * 是否导出数据
     */
      boolean isExport() default true;
    /**
     * 是否为重要字段（整列标红,着重显示）
     */
    boolean isMark() default false;
    /**
     * 是否合计当前列
     */
     boolean isSum() default false;
}

工具类

package com.excel;

import com.annota.ExcelAttribute;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;

import java.io.InputStream;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * excel导入工具类
 */
public class ExcelUtil<T> implements Serializable {
    private static final long serialVersionUID = 551970754610248636L;

    private Class<T> clazz;

    public ExcelUtil(Class<T> clazz) {
        this.clazz = clazz;
    }

    /**
     * 将excel表单数据源的数据导入到list
     *
     * @param sheetName 工作表的名称
     * @param
     */
    public List<T> getExcelToList(String sheetName, InputStream input) {
        List<T> list = new ArrayList<T>();
        try {
            HSSFWorkbook book = new HSSFWorkbook(input);
            HSSFSheet sheet = null;
            // 如果指定sheet名,则取指定sheet中的内容.
            if (StringUtils.isNotBlank(sheetName)) {
                sheet = book.getSheet(sheetName);
            }
            // 如果传入的sheet名不存在则默认指向第1个sheet.
            if (sheet == null) {
                sheet = book.getSheetAt(0);
            }
            // 得到数据的行数
            int rows = sheet.getLastRowNum();
            // 有数据时才处理
            if (rows > 0) {
                // 得到类的所有field
                Field[] allFields = clazz.getDeclaredFields();
                // 定义一个map用于存放列的序号和field
                Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();
                for (int i = 0, index = 0; i < allFields.length; i++) {
                    Field field = allFields[i];
                    // 将有注解的field存放到map中
                    if (field.isAnnotationPresent(ExcelAttribute.class)) {
                        // 设置类的私有字段属性可访问
                        field.setAccessible(true);
                        fieldsMap.put(index, field);
                        index++;
                    }
                }
                // 从第2行开始取数据,默认第一行是表头
                for (int i = 1, len = rows; i <= len; i++) {
                    // 得到一行中的所有单元格对象.
                    System.out.println("第**********" + i + "*************行");
                    HSSFRow row = sheet.getRow(i);

                    Iterator<Cell> cells = row.cellIterator();
                    T entity = null;
                    int index = 0;
                    int mm = 0;
                    while (cells.hasNext()) {
                        System.out.println(">>>>" + (++mm));
                        String c = getCellValue(cells.next());
                        System.out.println("**" + c);
                        if (c.indexOf("合计：") != -1) {
                            continue;
                        }
                        // 如果不存在实例则新建
                        entity = (entity == null ? clazz.newInstance() : entity);
                        // 从map中得到对应列的field
                        Field field = fieldsMap.get(index);
                        if (field == null) {
                            continue;
                        }
                        // 取得类型,并根据对象类型设置值.
                        Class<?> fieldType = field.getType();
                        if (fieldType == null) {
                            continue;
                        }
                        if (String.class == fieldType) {
                            field.set(entity, String.valueOf(c));
                        } else if (BigDecimal.class == fieldType) {
                            c = c.indexOf("%") != -1 ? c.replace("%", "") : c;
                            field.set(entity, BigDecimal.valueOf(Double.valueOf(c)));
                        } else if (Date.class == fieldType) {
                            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                            field.set(entity, StringUtils.isEmpty(c) ? null : simpleDateFormat.parse(c));
                        } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
                            field.set(entity, StringUtils.isEmpty(c) ? null : Integer.parseInt(c));
                        } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
                            field.set(entity, StringUtils.isEmpty(c) ? null : Long.valueOf(c));
                        } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
                            field.set(entity, StringUtils.isEmpty(c) ? null : Float.valueOf(c));
                        } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
                            field.set(entity, StringUtils.isEmpty(c) ? null : Short.valueOf(c));
                        } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
                            field.set(entity, StringUtils.isEmpty(c) ? null : Double.valueOf(c));
                        } else if (Character.TYPE == fieldType) {
                            if ((c != null) && (c.length() > 0)) {
                                field.set(entity, Character.valueOf(c.charAt(0)));
                            } else {
                                field.set(entity, "");
                            }
                        }
                        index++;

                    }

                    if (entity != null) {
                        list.add(entity);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            new Exception("将excel表单数据源的数据导入到list异常!", e);
        }
        return list;
    }

    public String getCellValue(Cell cell) {
        String value = "";
        // 以下是判断数据的类型
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                value = cell.getNumericCellValue() + "";
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    if (date != null) {
                        value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                    } else {
                        value = "";
                    }
                } else {
                    value = new DecimalFormat("0").format(cell.getNumericCellValue());
                }
                break;
            // 字符串
            case HSSFCell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            // Boolean
            case HSSFCell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue() + "";
                break;
            // 公式
            case HSSFCell.CELL_TYPE_FORMULA:
                value = cell.getCellFormula() + "";
                break;
            // 空值
            case HSSFCell.CELL_TYPE_BLANK:
                value = "";
                break;
            // 故障
            case HSSFCell.CELL_TYPE_ERROR:
                value = "";
                break;
            default:
                value = "";
                break;
        }
        return value;
    }

}

测试

   FileInputStream fis = new FileInputStream("F:\\aa.xls");
        ExcelUtil<TestModel> util1 = new ExcelUtil<TestModel>(TestModel.class);
        List<TestModel> testModels = util1.getExcelToList("aa", fis);
        for (TestModel testModel : testModels) {
            System.out.println(testModel.toString());
        }

阿里的easyExcel方式

  <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.2-beat1</version>
        </dependency>

/**
 * 有时需要表头，如果需要表头，我们就可以在相应的实体类中加入 @ExcelProperty(value = "id", index = 0) 注解，
 * 并且继承 BaseRowModel。其中 value 代表在导出 Excel 时
 * 该字段对应的表头名称；index 代表该字段对应的表头位置
 */
@Data
public class Catagory extends BaseRowModel {

    @ExcelProperty(value = "ID",index = 0)
    private Integer id;

    @ExcelProperty(value = "姓名",index = 1)
    private String name;

    @ExcelProperty(value = "年龄",index = 2)
    private Integer age;

    @ExcelProperty(value = "生日",index = 3)
    private String birth;

    @Override
    public String toString() {
        return "Catagory{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", birth=" +birth +
                '}';
    }
}

public class ExcelListener extends AnalysisEventListener {

    private List<Object> list = new ArrayList<>();

    public ExcelListener() {
        super();
    }

    public List<Object> getList() {
        return list;
    }

    public void setList(List<Object> list) {
        this.list = list;
    }

    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        /**  数据处理并加载到list集合**/
        list.add(o);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

    @RequestMapping("/expor")
    @ResponseBody
    public String exporExcel(HttpServletResponse response) throws IOException {
        ExcelWriter writer = null;
        OutputStream outputStream = response.getOutputStream();
        try {
            //添加响应头信息
            response.setHeader("Content-disposition", "attachment; filename=" + "catagory.xls");
            response.setContentType("application/msexcel;charset=UTF-8");//设置类型
            response.setHeader("Pragma", "No-cache");//设置头
            response.setHeader("Cache-Control", "no-cache");//设置头
            response.setDateHeader("Expires", 0);//设置日期头
            //实例化 ExcelWriter
            writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLS, true);
            //实例化表单
            Sheet sheet = new Sheet(1, 0, Catagory.class);
            sheet.setSheetName("目录");
            //获取数据
            List<Catagory> catagoryList = new ArrayList<>();
            for (int i = 0; i < 10; i++) {
                Catagory catagory = new Catagory();
                catagory.setAge(i);
                catagory.setId(i);
                catagory.setName("名称"+i);
                catagory.setBirth("2009-09-09");
                catagoryList.add(catagory);
            }
            //输出
            writer.write(catagoryList, sheet);
            writer.finish();
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                response.getOutputStream().close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return "index";
    }

    @RequestMapping("/import")
    @ResponseBody
    public String importExcel(@RequestParam("file") MultipartFile file) throws IOException {
        InputStream inputStream = file.getInputStream();

        //实例化实现了AnalysisEventListener接口的类
        ExcelListener listener = new ExcelListener();
        //传入参数
        ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLS, null, listener);
        //读取信息
        excelReader.read(new Sheet(1, 1, Catagory.class));

        //获取数据
        List<Object> list = listener.getList();

        List<Catagory> catagoryList = new ArrayList<Catagory>();
        Catagory catagory = new Catagory();

        //转换数据类型,并插入到数据库
        for (int i = 0; i < list.size(); i++) {
            catagory = (Catagory) list.get(i);
            System.out.println(catagory.toString());
        }
        return "index";
    }