一个基于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:[e-mail]
  * @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模版

　　

　　填写数据上传

　　

　　后台控制台打印输出结果

　　

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