excel导入导出工具类

Excel导入功能

导入功能是将excel中的数据转换成对应的javaBean。

实现思路

1. 使用poi读取excel表格
2. 获取每行表格中的单元格信息，将单元格中的数据存入到对应的javaBean中。
3. 写一个注解类来标注javaBean中的那个属性要与Excel中的数据进行对应。
4. 运用java反射来获取javaBean中的注解属性。

代码实现

 public class ImportExcelUtil {
    private final Logger logger = LoggerFactory.getLogger(ImportExcelUtil.class);
    private static final int DEFAULT_COUNT=20000;
    private static final int DEFAULT_START_LINE=0;
    public static <T> List<T> convertSheetToList(InputStream in,Class<T> clazz, boolean isExcel2003,
                                                 int startLine,int maxCount) throws Exception {
       List<T> list = new ArrayList<>();
        /** 根据版本选择创建Workbook的方式 */
        Workbook wb;
        if (isExcel2003) {
            wb = new HSSFWorkbook(in);
        } else {
            wb = new XSSFWorkbook(in);
        }
        if(null != wb){
            //获取第0个工作表格
            Sheet sheet = wb.getSheetAt(0);
            int count = sheet.getLastRowNum();
            if(maxCount == 0){
                maxCount = DEFAULT_COUNT;
            }
            if(count > maxCount){
                throw new Exception("导入失败，excel数据控制在"+maxCount+"条之内！");
            }
            //遍历excel表格并将每一行中的数据转换成对象
            if(startLine < 0){
                startLine = DEFAULT_START_LINE;
            }
            for(int i=startLine;i<=count;i++){
                Row row = sheet.getRow(i);
                if(row==null){
                    continue;
                }
                T obj = convertLineToObj(clazz,row);
                if(obj==null){
                    continue;
                }
                list.add(obj);
            }
        }
        return list;
    }
    //行遍历
    private static <T> T convertLineToObj(Class<T> clazz, Row row) throws Exception {
        T obj = clazz.newInstance();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields){
            ExcelImport annotation = field.getAnnotation(ExcelImport.class);
            if(annotation!=null && row.getLastCellNum() >= annotation.columnIndex()){
                //每行对应的单元格遍历
                Cell cell = row.getCell(annotation.columnIndex());
                if(cell==null)
                    throw new Exception("请使用正确的excel模板");
                field.setAccessible(true);
                field.set(obj,getCellValue(cell));
            }
        }
        return obj;
    }
    private static Object getCellValue(Cell cell) {
        if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
            return String.valueOf(cell.getBooleanCellValue());
        }else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
            DecimalFormat df = new DecimalFormat("0.00");
            String str = df.format(cell.getNumericCellValue());
            return str;
        }
        return String.valueOf(cell.getStringCellValue());
    }

    public static void main(String[] args) {
        try {
            FileInputStream in = new FileInputStream("/Users/vobile_lzl/Downloads/tvids(2).xlsx");
//            List<TvIdPresent> list = convertSheetToList(in, TvIdPresent.class,false,1);
//            System.out.println("list = " + list);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

注解类（）

该类主要用来识别excel字段中对应那个javaBean的属性。

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.METHOD, ElementType.ANNOTATION_TYPE})
public @interface ExcelImport {
    int columnIndex() default 0;
}

• 例子

public class ShopResourceExcelBo {
    @ExcelImport(columnIndex = 0)
    private String resourceId;
    @ExcelImport(columnIndex = 1)
    private String resourceName;
    @ExcelImport(columnIndex = 2)
    private String priceStr;
    @ExcelImport(columnIndex = 3)
    private String inventoryNumStr;
   }
   ...省去set和get方法

   调用方法
    List<ShopResourceExcelBo> excelList = ImportExcelUtil.convertSheetToList(inputStream, 
    ShopResourceExcelBo.class,isExcel2003,1,500);

导出Excel

将List集合中的对象转换过成excel单元格数据

• 同样是利用poi的导出功能来实现。
• 利用注解来说明javaBean的属性对应excel的单元格信息。
• 利用java反射将注解类的属性描述写入到excel的头信息上。
• 利用java反射将注解类中的属性值写入到excel对应的单元格。

代码实现

public class ExportExcelUtil {
    private final Logger logger = LoggerFactory.getLogger(ExportExcelUtil.class);
    private OutputStream outputStream;
    private Workbook workbook;
    private HSSFSheet sheet;
    private int index;

    public ExportExcelUtil(String fileName){
        try {
            this.init(new FileOutputStream(fileName));
        } catch (FileNotFoundException e) {
            logger.error("导出excel出错");
            e.printStackTrace();
        }
    }
    public ExportExcelUtil(OutputStream outputStream){
        try {
            this.init(outputStream);
        } catch (Exception e) {
            logger.error("导出excel出错");
            e.printStackTrace();
        }
    }
    private void init(OutputStream fileOutputStream) {
        this.outputStream = fileOutputStream;
        this.workbook = new HSSFWorkbook();
        this.index = 0;
    }

    public ExportExcelUtil writeHead(Class<?> tClass){
        if (tClass==null){
            return this;
        }
        List<String> list = new ArrayList<>();
        for (Field filed : getFields(tClass)) {
           ExcelExport excelExport = filed.getAnnotation(ExcelExport.class);
           list.add(excelExport.columnName());
        }
        createData(list);
        return this;
    }

    /**
     * 获取带有ExcelExport注解的属性
     * @param tClass
     * @return
     */
    private List<Field> getFields(Class<?> tClass){
        Field[] fields = tClass.getDeclaredFields();
        List<Field> list = new ArrayList<>(fields.length);
        for (Field f: fields) { //遍历tClass中的属性
            if(f.isAnnotationPresent(ExcelExport.class)){ //有ExcelExport注解的信息属性保留
                list.add(f);
            }
        }
        return list;
    }
    /**
     * 行列填充数据
     * @param list
     */
    private void createData(List<String> list) {
        if(sheet == null){
            sheet = (HSSFSheet) workbook.createSheet();
        }
        HSSFRow row = sheet.createRow(index++);
        HSSFCell[] cells = new HSSFCell[list.size()];
        for(int i=0;i<cells.length;i++) {
            cells[i] = row.createCell(i);
            cells[i].setCellValue(list.get(i));
        }
    }
    public <T> ExportExcelUtil writeList(List<T> list){
        if(list!=null && list.size()>0){
            for ( Object obj: list) {
                writeObject(obj);
            }
        }
        return this;
    }

    private void writeObject(Object obj) {
        Class clazz = obj.getClass();
        List<String> list = new ArrayList<>();
        for (Field f: getFields(clazz)) {
            f.setAccessible(true);
            try {
                Object o = f.get(obj);
                if(o instanceof Date){
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    list.add(sdf.format(o));
                }else if(o instanceof BigDecimal){
                    list.add(String.valueOf(((BigDecimal) o).setScale(2,BigDecimal.ROUND_CEILING)));
                }else{
                    list.add(String.valueOf(o));
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
                logger.error("格式化obj失败");
            }
        }
        if(!list.isEmpty()){
            createData(list);
        }
    }
    public void exportData(){
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
            logger.error("创建excel失败");
        }
    }
}

注解类

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.METHOD, ElementType.ANNOTATION_TYPE})
public @interface ExcelExport {
    String columnName() default "";
}

• 例子

public class OperationRecordVO{

    @ExcelExport(columnName = "用户名")
    private String username;

    @ExcelExport(columnName = "角色")
    private String roleName;

    @ExcelExport(columnName = "IP地址")
    private String ip;

    @ExcelExport(columnName = "操作内容")
    private String context;

    private Integer type;

    @ExcelExport(columnName = "操作类型")
    private String typeStr;

    private String objectType;

    private String objectId;

    @ExcelExport(columnName = "操作时间")
    @JsonSerialize(converter = JacksonDateConverter.BacklashDateTimeConverter.class)
    private Date createTime;
    }

    ...省去get和set方法

    //调用实例
 public void exportRecords(String startTime,String endTime,HttpServletResponse response){
        List<OperationRecordVO> list = operationRecordService.exportRecordsByDate(startTime,endTime);
        //导出excel
        String fileName = "操作日志.xls";
        try {
            response.setContentType("application/octet-stream;");
            response.setHeader("Content-disposition",
                    "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "iso8859-1"));
            ExcelExportUtil excelExportUtil = new ExcelExportUtil(response.getOutputStream());
            //excel第一列表头信息
            excelExportUtil.writeHeader(OperationRecordVO.class);
            //写入每一行信息
            excelExportUtil.writeObjects(list);
            excelExportUtil.exportData();
        } catch (IOException e) {
            logger.error("导出excel失败...");
            e.printStackTrace();
        }
    }