自定义注解实现Excel 的导入和导出

自定义注解实现Excel 的导入和导出

需要的jar

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

注解使用

@ExcelTableName(tableName = “人员数据”)//设置一个工作表名称,也可以不设置
@Excel注解有三个属性值name:表格当前列的列名,width:当前列的宽度;skip:是否忽略当前字段转换

@ExcelTableName(tableName = "人员数据")
public class User {

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

    @Excel(name = "年龄", width = 3)
    private String age;

    @Excel(skip = true)
    private String password;

    @Excel(name = "xx", width = 6)
    private Double xx;

    @Excel(name = "yy", width = 18)
    private Date yy;

    @Excel(name = "锁定", width = 5)
    private Boolean locked;

    @Excel(name = "金额")
    private BigDecimal db;

    //省略get和set...


    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Double getXx() {
        return xx;
    }

    public void setXx(Double xx) {
        this.xx = xx;
    }

    public Date getYy() {
        return yy;
    }

    public void setYy(Date yy) {
        this.yy = yy;
    }

    public Boolean getLocked() {
        return locked;
    }

    public void setLocked(Boolean locked) {
        this.locked = locked;
    }

    public BigDecimal getDb() {
        return db;
    }

    public void setDb(BigDecimal db) {
        this.db = db;
    }
}

测试代码

public class Test {
    public static void main(String[] args) throws Exception {

        List<User> list = new ArrayList<User>();
        User u = new User();
        u.setAge("3");
        u.setName("fdsafdsa");
        u.setXx(123.23D);
        u.setYy(new Date());
        u.setLocked(false);
        u.setDb(new BigDecimal(123));
        list.add(u);

        u = new User();
        u.setAge("23");
        u.setName("张珊");
        u.setXx(123.23D);
        u.setYy(new Date());
        u.setLocked(true);
        u.setDb(new BigDecimal(234));
        list.add(u);

        u = new User();
        u.setAge("123");
        u.setName("fdsafdsa");
        u.setXx(123.23D);
        u.setYy(new Date());
        u.setLocked(false);
        u.setDb(new BigDecimal(2344));
        list.add(u);

        u = new User();
        u.setAge("22");
        u.setName("fdsafdsa");
        u.setXx(123.23D);
        u.setYy(new Date());
        u.setLocked(true);
        u.setDb(new BigDecimal(908));
        list.add(u);
		//设置数据替换:例如Bean里面  sex字段表示性别:  值: 0:表示男;     1:表示女
		//在这里我们可以设置
		//map.put("0","男")
		//map.put("1","女")
		//def.set("sex",map)
		//这样导出的表格值性别这一列将会以男或者女展示
        ExcelDataFormatter edf = new ExcelDataFormatter();
        Map<String, String> map = new HashMap<String, String>();
        map.put("true", "未锁");
        map.put("false", "锁定");
        edf.set("locked", map);
        //导出表格
        ExcelUtils excelUtils = new ExcelUtils();
        excelUtils.setExcelDataFormatter(edf);
        excelUtils.writeToFile("D:\\x.xlsx", list,list1,list2....);//可以转入多个list对象,将会在一个文件里面创建多张表单
		//将表格转出bean对象
        List<User> xx = new ExcelUtils(new User()).readFromFile(new File("D:\\x.xlsx"));
        System.out.println(JSON.toJSONString(xx));

    }
}

注解类

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface Excel {
    //列名
    String name() default "";
    //宽度
    int width() default 4;
    //列名的层级,设置的数必须大于0,相同的数值为同一个层级,从小到大排序
    //忽略该字段
    boolean skip() default false;

}

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface ExcelTableName {
    String tableName() default "";
}

处理代码

public class ExcelDataFormatter {
private Map<String,Map<String,String>> formatter=new HashMap<String, Map<String,String>>();

public void set(String key,Map<String,String> map){
    formatter.put(key, map);
}

public Map<String,String> get(String key){
    return formatter.get(key);
}
}

public class ExcelUtils<E> {
private E e;
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
private int etimes = 0;
private static ExcelDataFormatter edf;
public void setExcelDataFormatter(ExcelDataFormatter edf) {
    this.edf = edf;
};

public ExcelUtils(E e) {
    this.e = e;
}
public ExcelUtils() {
}

public E get() throws InstantiationException, IllegalAccessException {
    return (E) e.getClass().newInstance();
}

/**
 * 将数据写入到EXCEL文档
 *
 * @param lists
 *            可以传递多个数据集合
 *
 *            数据格式化,比如有些数字代表的状态,像是0:女,1:男,或者0:正常,1:锁定,变成可读的文字
 *            该字段仅仅针对Boolean,Integer两种类型作处理
 * @param filePath
 *            文件路径
 * @throws Exception
 */
public static  void writeToFile ( String filePath,List<?>... lists) throws Exception {
    // 创建并获取工作簿对象
    Workbook wb = new SXSSFWorkbook();
   // FileOutputStream out = null;
    for (List<?> beans:lists) {
         wb = getWorkBook(beans,edf,wb);
        // 写入到文件
    }
    FileOutputStream out = new FileOutputStream(filePath);
    wb.write(out);
    out.close();
}

/**
 * 获得Workbook对象
 *
 * @param list
 *            数据集合
 * @param edf
 * @return Workbook
 * @throws Exception
 */
private static <T> Workbook getWorkBook(List<T> list, ExcelDataFormatter edf, Workbook wb) throws Exception {

    if (list == null || list.size() == 0){
        return wb;
    }
    Class clsaa = list.get(0).getClass();
    // 创建一个工作表sheet
    ExcelTableName excelTableName = (ExcelTableName) clsaa.getAnnotation(ExcelTableName.class);
    String tableName = excelTableName.tableName();
    if ("".equals(tableName)){
        tableName = null;
    }
    Sheet sheet = wb.createSheet(tableName);
    // 申明行
    Row row = sheet.createRow(0);
    // 申明单元格
    Cell cell = null;

    CreationHelper createHelper = wb.getCreationHelper();

    Field[] fields =clsaa.getDeclaredFields();

    XSSFCellStyle titleStyle = (XSSFCellStyle) wb.createCellStyle();
    titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    // 设置前景色
    titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(159, 213, 183)));
    titleStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font font = wb.createFont();
    font.setColor(HSSFColor.BROWN.index);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    // 设置字体
    titleStyle.setFont(font);

    int columnIndex = 0;
    Excel excel = null;
    for (Field field : fields) {
        field.setAccessible(true);
        excel = field.getAnnotation(Excel.class);
        if (excel == null || excel.skip() == true) {
            continue;
        }
        // 列宽注意乘256
        sheet.setColumnWidth(columnIndex, excel.width() * 400);
        // 写入标题
        cell = row.createCell(columnIndex);
        cell.setCellStyle(titleStyle);
        cell.setCellValue(excel.name());

        columnIndex++;
    }

    int rowIndex = 1;

    CellStyle cs = wb.createCellStyle();

    for (T t : list) {
        row = sheet.createRow(rowIndex);
        columnIndex = 0;
        Object o = null;
        for (Field field : fields) {

            field.setAccessible(true);

            // 忽略标记skip的字段
            excel = field.getAnnotation(Excel.class);
            if (excel == null || excel.skip() == true) {
                continue;
            }
            // 数据
            cell = row.createCell(columnIndex);

            o = field.get(t);
            // 如果数据为空,填充""
            if (o == null){
                cell.setCellValue("");
            }else if (o instanceof Date) {// 处理日期类型
                cs.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
                cell.setCellStyle(cs);
                cell.setCellValue((Date) field.get(t));
            } else if (o instanceof Double || o instanceof Float) {
                cell.setCellValue((Double) field.get(t));
            } else if (o instanceof Boolean) {
                Boolean bool = (Boolean) field.get(t);
                if (ExcelUtils.edf == null) {
                    cell.setCellValue(bool);
                } else {
                    String str = field.getName();
                    Map<String, String> map = ExcelUtils.edf.get(field.getName());
                    if (map == null) {
                        cell.setCellValue(bool);
                    } else {
                        cell.setCellValue(map.get(bool.toString().toLowerCase()));
                    }
                }

            } else if (o instanceof Integer) {

                Integer intValue = (Integer) field.get(t);

                if (ExcelUtils.edf == null) {
                    cell.setCellValue(intValue);
                } else {
                    Map<String, String> map = ExcelUtils.edf.get(field.getName());
                    if (map == null) {
                        cell.setCellValue(intValue);
                    } else {
                        cell.setCellValue(map.get(intValue.toString()));
                    }
                }
            } else {
                cell.setCellValue(field.get(t).toString());
            }

            columnIndex++;
        }

        rowIndex++;
    }

    return wb;
}

/**
 * 从文件读取数据,最好是所有的单元格都是文本格式,日期格式要求yyyy-MM-dd HH:mm:ss,布尔类型0:真,1:假
 *
 * @param file
 *            Excel文件,支持xlsx后缀,xls的没写,基本一样
 * @return
 * @throws Exception
 */
public List<E> readFromFile(File file) throws Exception {
    Field[] fields = e.getClass().getDeclaredFields();

    Map<String, String> textToKey = new HashMap<String, String>();

    Excel _excel = null;
    for (Field field : fields) {
        _excel = field.getAnnotation(Excel.class);
        if (_excel == null || _excel.skip() == true) {
            continue;
        }
        textToKey.put(_excel.name(), field.getName());
    }

    InputStream is = new FileInputStream(file);

    Workbook wb = new XSSFWorkbook(is);

    Sheet sheet = wb.getSheetAt(0);
    Row title = sheet.getRow(0);
    // 标题数组,后面用到,根据索引去标题名称,通过标题名称去字段名称用到 textToKey
    String[] titles = new String[title.getPhysicalNumberOfCells()];
    for (int i = 0; i < title.getPhysicalNumberOfCells(); i++) {
        titles[i] = title.getCell(i).getStringCellValue();
    }

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

    E e = null;

    int rowIndex = 0;
    int columnCount = titles.length;
    Cell cell = null;
    Row row = null;

    for (Iterator<Row> it = sheet.rowIterator(); it.hasNext();) {

        row = it.next();
        if (rowIndex++ == 0) {
            continue;
        }

        if (row == null) {
            break;
        }

        e = get();

        for (int i = 0; i < columnCount; i++) {
            cell = row.getCell(i);
            etimes = 0;
            readCellContent(textToKey.get(titles[i]), fields, cell, e, edf);
        }
        list.add(e);
    }
    return list;
}

/**
 *
 * @param key
 *            当前单元格对应的Bean字段
 * @param fields
 *            Bean所有的字段数组
 * @param cell
 *            单元格对象
 * @param e
 * @throws Exception
 */
private void readCellContent(String key, Field[] fields, Cell cell, E e, ExcelDataFormatter edf) throws Exception {

    Object o = null;
    try {
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_BOOLEAN:
                o = cell.getBooleanCellValue();
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                o = cell.getNumericCellValue();
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    o = DateUtil.getJavaDate(cell.getNumericCellValue());
                }
                break;
            case XSSFCell.CELL_TYPE_STRING:
                o = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_ERROR:
                o = cell.getErrorCellValue();
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                o = null;
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                o = cell.getCellFormula();
                break;
            default:
                o = null;
                break;
        }

        if (o == null || "".equals(o))
            return;

        for (Field field : fields) {
            field.setAccessible(true);
            if (field.getName().equals(key)) {
                Boolean bool = true;
                Map<String, String> map = null;
                if (edf == null) {
                    bool = false;
                } else {
                    map = edf.get(field.getName());
                    if (map == null) {
                        bool = false;
                    }
                }

                if (field.getType().equals(Date.class)) {
                    if (o.getClass().equals(Date.class)) {
                        field.set(e, o);
                    } else {
                        field.set(e, sdf.parse(o.toString()));
                    }
                } else if (field.getType().equals(String.class)) {
                    if (o.getClass().equals(String.class)) {
                        field.set(e, o);
                    } else {
                        field.set(e, o.toString());
                    }
                } else if (field.getType().equals(Long.class)) {
                    if (o.getClass().equals(Long.class)) {
                        field.set(e, o);
                    } else {
                        field.set(e, Long.parseLong(o.toString()));
                    }
                } else if (field.getType().equals(Integer.class)) {
                    if (o.getClass().equals(Integer.class)) {
                        field.set(e, o);
                    } else {
                        // 检查是否需要转换
                        if (bool) {
                            field.set(e, map.get(o.toString()) != null ? Integer.parseInt(map.get(o.toString())) : Integer.parseInt(o.toString()));
                        } else {
                            field.set(e, Integer.parseInt(o.toString()));
                        }

                    }
                } else if (field.getType().equals(BigDecimal.class)) {
                    if (o.getClass().equals(BigDecimal.class)) {
                        field.set(e, o);
                    } else {
                        field.set(e, BigDecimal.valueOf(Double.parseDouble(o.toString())));
                    }
                } else if (field.getType().equals(Boolean.class)) {
                    if (o.getClass().equals(Boolean.class)) {
                        field.set(e, o);
                    } else {
                        // 检查是否需要转换
                        if (bool) {
                            field.set(e, map.get(o.toString()) != null ? Boolean.parseBoolean(map.get(o.toString())) : Boolean.parseBoolean(o.toString()));
                        } else {
                            field.set(e, Boolean.parseBoolean(o.toString()));
                        }
                    }
                } else if (field.getType().equals(Float.class)) {
                    if (o.getClass().equals(Float.class)) {
                        field.set(e, o);
                    } else {
                        field.set(e, Float.parseFloat(o.toString()));
                    }
                } else if (field.getType().equals(Double.class)) {
                    if (o.getClass().equals(Double.class)) {
                        field.set(e, o);
                    } else {
                        field.set(e, Double.parseDouble(o.toString()));
                    }

                }

            }
        }

    } catch (Exception ex) {
        ex.printStackTrace();
        // 如果还是读到的数据格式还是不对,只能放弃了
        if (etimes > 7) {
            throw ex;
        }
        etimes++;
        if (o == null) {
            readCellContent(key, fields, cell, e, edf);
        }
    }
}

}
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值