一个基于POI的通用excel导入导出工具类的简单实现及使用方法

前言：

最近PM来了一个需求，简单来说就是在录入数据时一条一条插入到系统显得非常麻烦，让我实现一个直接通过excel导入的方法一次性录入所有数据。网上关于excel导入导出的例子很多，但大多相互借鉴。经过思考，认为一百个客户在录入excel的时候，就会有一百个格式版本，所以在实现这个功能之前，所以要统一excel的格式。于是提供了一个通用excel模版的下载功能。当所有客户用模版录入好数据再上传到系统，后端对excel进行解析，然后再持久化到数据库。

概述：

　　　此工具类的几大特点

　　　1、基本导入导出

　　　2、提供excel模版自动生成及下载功能

　　　3、创建模版过程简单通用，只需要在实体类上进行注解

　　　4、springMVC框架

　　　5、模版可以生成下拉框选择列

废话不多说，上代码......

一、引入poi相关依赖（及spring上传文件相关配置，不再解释）

<!-- apache poi start -->
        <poi.version>3.14</poi.version>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>${poi.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>
<!-- apache poi end -->

二、excel导入导出工具类的实现

/**
 * @Description
 * @author zhaomin E-mail:min.zhao@mljr.com
 * @date 创建时间：2017年2月14日 下午2:13:30
 * @version 1.0
 */
public class ImportExcelUtil {
    final static String notnullerror = "请填入第{0}行的{1},{2}不能为空";
    final static String errormsg = "第{0}行的{1}数据导入错误";

    /**
     * 导入Excel
     * 
     * @param clazz
     * @param xls
     * @return
     * @throws Exception
     */
    @SuppressWarnings("rawtypes")
    public static List importExcel(Class<?> clazz, InputStream xls) throws Exception {
        try {
            // 取得Excel
            HSSFWorkbook wb = new HSSFWorkbook(xls);
            HSSFSheet sheet = wb.getSheetAt(0);
            Field[] fields = clazz.getDeclaredFields();
            List<Field> fieldList = new ArrayList<Field>(fields.length);
            for (Field field : fields) {
                if (field.isAnnotationPresent(ModelProp.class)) {
                    ModelProp modelProp = field.getAnnotation(ModelProp.class);
                    if (modelProp.colIndex() != -1) {
                        fieldList.add(field);
                    }
                }
            }
            EmployeeDTO employee = new EmployeeDTO();
            // 行循环
            List<ImportModel> modelList = new ArrayList<ImportModel>(sheet.getPhysicalNumberOfRows() * 2);
            for (int i = 2; i < sheet.getPhysicalNumberOfRows(); i++) {
                // 数据模型
                ImportModel model = (ImportModel) clazz.newInstance();
                int nullCount = 0;
                Exception nullError = null;
                for (Field field : fieldList) {
                    ModelProp modelProp = field.getAnnotation(ModelProp.class);
                    HSSFCell cell = sheet.getRow(i).getCell(modelProp.colIndex());
                    try {
                        if (cell == null || cell.toString().length() == 0) {
                            nullCount++;
                            if (!modelProp.nullable()) {
                                nullError = new Exception(StringUtil.format(notnullerror,
                                        new String[] { "" + (1 + i), modelProp.name(), modelProp.name() }));

                            }
                        } else if (field.getType().equals(Date.class)) {
                            if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(), new Date(parseDate(parseString(cell))));
                            } else {
                                BeanUtils.setProperty(model, field.getName(),
                                        new Date(cell.getDateCellValue().getTime()));

                            }
                        } else if (field.getType().equals(Timestamp.class)) {
                            if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(),
                                        new Timestamp(parseDate(parseString(cell))));
                            } else {
                                BeanUtils.setProperty(model, field.getName(),
                                        new Timestamp(cell.getDateCellValue().getTime()));
                            }

                        } else if (field.getType().equals(java.sql.Date.class)) {
                            if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(),
                                        new java.sql.Date(parseDate(parseString(cell))));
                            } else {
                                BeanUtils.setProperty(model, field.getName(),
                                        new java.sql.Date(cell.getDateCellValue().getTime()));
                            }
                        } else if (field.getType().equals(java.lang.Integer.class)) {
                            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(), (int) cell.getNumericCellValue());
                            } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(), Integer.parseInt(parseString(cell)));
                            }
                        } else if (field.getType().equals(java.math.BigDecimal.class)) {
                            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(),
                                        new BigDecimal(cell.getNumericCellValue()));
                            } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(), new BigDecimal(parseString(cell)));
                            }
                        } else {
                            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(),
                                        new BigDecimal(cell.getNumericCellValue()));
                            } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                                BeanUtils.setProperty(model, field.getName(), parseString(cell));
                            }
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        throw new Exception(StringUtil.format(errormsg, new String[] { "" + (1 + i), modelProp.name() })
                                + "," + e.getMessage());
                    }
                }
                if (nullCount == fieldList.size()) {
                    break;
                }
                if (nullError != null) {
                    throw nullError;
                }
                modelList.add(model);
            }
            return modelList;

        } finally {
            xls.close();
        }
    }

    private final static int colsizeN = 630;
    private final static int colsizeM = 1000;

    /**
     * 下载Excel模版
     * 
     * @param clazz
     * @param map
     * @param rowSize
     * @return
     */
    public static InputStream excelModelbyClass(Class<?> clazz, Map<Integer, String[]> map, Integer rowSize) {
        try {
            if (!clazz.isAnnotationPresent(ModelTitle.class)) {
                throw new Exception("请在此类型中加上ModelTitle注解");
            }
            if (rowSize == null) {
                rowSize = 1000;
            }
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet();
            /**
             * 设置标题样式
             */
            HSSFCellStyle titleStyle = wb.createCellStyle();
            titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont font = wb.createFont();
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            font.setFontHeight((short) 400);
            titleStyle.setFont(font);
            HSSFCell titleCell = sheet.createRow(0).createCell(0); // 创建第一行，并在该行创建单元格，设置内容，做为标题行
            /**
             * 获取标题
             */
            ModelTitle modelTitle = clazz.getAnnotation(ModelTitle.class);
            titleCell.setCellValue(new HSSFRichTextString(modelTitle.name()));
            titleCell.setCellStyle(titleStyle);

            Field[] fields = clazz.getDeclaredFields();
            HSSFRow headRow = sheet.createRow(1);
            int colSzie = 0;
            /**
             * 设置表头样式
             */
            HSSFCellStyle headStyle = wb.createCellStyle();
            headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont headFont = wb.createFont();
            headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            headFont.setFontHeight((short) 240);
            headStyle.setFont(headFont);
            List<Integer> cells = new ArrayList<Integer>();

            for (Field field : fields) {
                if (field.isAnnotationPresent(ModelProp.class)) {
                    ModelProp modelProp = field.getAnnotation(ModelProp.class);
                    if (modelProp.colIndex() == -1)
                        continue;
                    cells.add(modelProp.colIndex());
                    HSSFCell cell = headRow.createCell(modelProp.colIndex());
                    cell.setCellValue(new HSSFRichTextString(modelProp.name()));
                    cell.setCellStyle(headStyle);
                    colSzie++;
                    sheet.autoSizeColumn((short) modelProp.colIndex());
                    sheet.setColumnWidth(modelProp.colIndex(), modelProp.name().length() * colsizeN + colsizeM);

                    // 设置列为下拉框格式
                    if (map != null && map.get(new Integer(modelProp.colIndex())) != null) {
                        DVConstraint constraint = DVConstraint
                                .createExplicitListConstraint(map.get(modelProp.colIndex()));
                        CellRangeAddressList regions = new CellRangeAddressList(2, rowSize, modelProp.colIndex(),
                                modelProp.colIndex());
                        HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
                        sheet.addValidationData(dataValidation);
                    }
                }
            }
            HSSFCellStyle cellStyle = wb.createCellStyle();
            HSSFDataFormat format = wb.createDataFormat();
            cellStyle.setDataFormat(format.getFormat("@"));
            for (int i = 2; i < rowSize; i++) {
                HSSFRow row = sheet.createRow(i);
                for (Integer integer : cells) {
                    HSSFCell cell = row.createCell(integer);
                    cell.setCellStyle(cellStyle);
                }
            }
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colSzie - 1));
            if (map != null) {
                for (Integer colIndex : map.keySet()) {
                    DVConstraint constraint = DVConstraint.createExplicitListConstraint(map.get(colIndex));
                    CellRangeAddressList regions = new CellRangeAddressList(2, 1000, colIndex, colIndex);
                    HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
                    sheet.addValidationData(dataValidation);
                }
            }

            ByteArrayOutputStream os = new ByteArrayOutputStream();
            try {
                wb.write(os);
            } catch (IOException e) {
                e.printStackTrace();
            }

            byte[] b = os.toByteArray();

            ByteArrayInputStream in = new ByteArrayInputStream(b);
            return in;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    private static String parseString(HSSFCell cell) {
        return String.valueOf(cell).trim();
    }

    private static long parseDate(String dateString) throws ParseException {
        if (dateString.indexOf("/") == 4) {
            return new SimpleDateFormat("yyyy/MM/dd").parse(dateString).getTime();
        } else if (dateString.indexOf("-") == 4) {
            return new SimpleDateFormat("yyyy-MM-dd").parse(dateString).getTime();
        } else if (dateString.indexOf("年") == 4) {
            return new SimpleDateFormat("yyyy年MM月dd").parse(dateString).getTime();
        } else if (dateString.length() == 8) {
            return new SimpleDateFormat("yyyyMMdd").parse(dateString).getTime();
        } else {
            return new Date().getTime();
        }
    }

}

 三、自定义spring注解

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ModelProp{
    public String name();
    public int colIndex() default -1;
    public boolean nullable() default true;
    public String interfaceXmlName() default "";
}

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface ModelTitle{
    public String name();
}

四、定义实体类父类

public class ImportModel {

}

五、定义实体类

@ModelTitle(name="人员列表")
public class EmployeeDTO extends ImportModel implements Serializable {

    private static final long serialVersionUID = -3434719712955859295L;

    private Long id;
    @ModelProp(name = "电话", colIndex = 1, nullable = false)
    private String telephone;

    @ModelProp(name = "名称", colIndex = 0, nullable = false)
    private String name;

    @ModelProp(name = "性别", colIndex = 2, nullable = false)
    private Integer sex;
}

六、定义controller

@RestController
@RequestMapping("/api/excelOpera")
public class ImportEmployeeController extends BaseController {
    
    private static Logger logger = LoggerFactory.getLogger(ImportEmployeeController.class);
    /**
     * 导入excel表
     * @version 1.0
     * @since 1.0
     */
    @RequestMapping(path = "/importEmployee", method = RequestMethod.POST)
    public RespMsg uploadExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
        try{
        　　 
            List<EmployeeDTO> employeeDTOList  = ImportExcelUtil.importExcel(EmployeeDTO.class, file.getInputStream());
            //可做持久化操作，现只打印观察
            for(EmployeeDTO employeeDTO : employeeDTOList){
                logger.info("name=" + employeeDTO.getName() + ",telephone=" + employeeDTO.getTelephone()+",sex=" + employeeDTO.getSex());
            }
        }catch(Exception e){
            logger.error(e.getMessage());
        }
        return null;
    }
    /**
     * 导出excel模版
     * @version 1.0
     * @since 1.0
     */
    @RequestMapping(path = "/downloadEmployeeModel", method = RequestMethod.GET)
    public RespMsg downloadEmployeeModel(HttpServletResponse response) {
        try{
            response.setContentType("application/xls");
            response.addHeader("Content-Disposition", "attachment;filename="+new String(("eeelist").getBytes("UTF-8"),"iso-8859-1")+".xls");
            Map<Integer,String[]> paramMap = new HashMap<Integer,String[]>();
            //excel第三行为下拉选择框
            paramMap.put(2, new String[]{"man","women"});
            BufferedInputStream input = new BufferedInputStream(ImportExcelUtil.excelModelbyClass(EmployeeDTO.class, paramMap, null));
            byte buffBytes[] = new byte[1024];
            OutputStream os = response.getOutputStream();
            int read = 0;
            while ((read = input.read(buffBytes)) != -1) {
                os.write(buffBytes, 0, read);
            }
            os.flush();
            os.close();
            input.close();
            return success("下载成功！");
        }catch(Exception e){
            logger.error("downloadEmployeeModel() catch Exception ",e);
            return fail("下载失败！");
        }
    }
    
}
        

至此全部工具类的实现已经完成，可以请求访问检验一下结果。

　　下载下来的excel模版

　　

　　填写数据上传

　　

　　后台控制台打印输出结果

　　

　　检验结果能够达到预期效果且能通用，是不是很简单呢，欢迎大神们提出意见，小女子感谢了。