Java之POI操作,封装ExcelUtil实现Excel导入导出

最近老师布置了一个任务,实现 Excel 和数据库的导入导出。

这个问题起初看起来是不复杂,实现简陋的导入导出比较容易。

但是,后来老师要求写一个包装类,让其他同学能够直接使用。

这就涉及到了泛型和反射的内容,其实也不复杂。

最终,在网上找到一段代码,写得挺好的,然后一顿调试和修改,就能直接拿来用了。

现在也分享给大家。

 

基本功能

* 1.实体属性配置了注解就能导出到excel中,每个属性都对应一列.

* 2.列名称可以通过注解配置.

* 3.导出到哪一列可以通过注解配置.

* 4.鼠标移动到该列时提示信息可以通过注解配置.

* 5.用注解设置只能下拉选择不能随意填写功能.

* 6.用注解设置是否只导出标题而不导出内容,这在导出内容作为模板以供用户填写时比较实用.

 

效果图

数据库

Java之POI操作,封装ExcelUtil实现Excel导入导出

 

Excel

Java之POI操作,封装ExcelUtil实现Excel导入导出

 

调用方法

下面是通过 SpringMVC 操作,导入导出

UserVO.java

  1. package com.change.vo.excel;
  2. import com.change.units.ExcelVOAttribute;
  3. /**
  4.  * @author 言曌
  5.  * @date 2017/12/25 上午10:39
  6.  */
  7. public class UserVO {
  8.     @ExcelVOAttribute(name = "账号名", column = "A", isExport = true)
  9.     private String userName;
  10. //    @ExcelVOAttribute(name = "密码", column = "B", prompt = "密码要保密哦!", isExport = false)
  11. //    private String userPassword;
  12.     @ExcelVOAttribute(name = "角色名称", column = "B",isExport = true)
  13.     private String userRoleName;
  14.     @ExcelVOAttribute(name = "学院名称", column = "C" ,isExport = true)
  15.     private String userSchoolName;
  16.     @ExcelVOAttribute(name = "状态", column = "D", combo = {"正常","禁用"},isExport = true)
  17.     private String status;
  18.     @ExcelVOAttribute(name = "备注", column = "E",isExport = true)
  19.     private String memo;
  20.     public String getUserName() {
  21.         return userName;
  22.     }
  23.     public void setUserName(String userName) {
  24.         this.userName = userName;
  25.     }
  26. //    public String getUserPassword() {
  27. //        return userPassword;
  28. //    }
  29. //
  30. //    public void setUserPassword(String userPassword) {
  31. //        this.userPassword = userPassword;
  32. //    }
  33.     public String getUserRoleName() {
  34.         return userRoleName;
  35.     }
  36.     public void setUserRoleName(String userRoleName) {
  37.         this.userRoleName = userRoleName;
  38.     }
  39.     public String getUserSchoolName() {
  40.         return userSchoolName;
  41.     }
  42.     public void setUserSchoolName(String userSchoolName) {
  43.         this.userSchoolName = userSchoolName;
  44.     }
  45.     public String getStatus() {
  46.         return status;
  47.     }
  48.     public void setStatus(String status) {
  49.         this.status = status;
  50.     }
  51.     public String getMemo() {
  52.         return memo;
  53.     }
  54.     public void setMemo(String memo) {
  55.         this.memo = memo;
  56.     }
  57. }

 

1、导出

  1. /**
  2.     * 将user表导出到Excel表
  3.     * @return
  4.     */
  5.    @RequestMapping(value = "/exportExcel")
  6.    @ResponseBody
  7.    public String importExcel() {
  8.        // 初始化数据
  9.        List<User> userList = userService.listUser();
  10.        List<UserVO> userVOList = new ArrayList<UserVO>();
  11.        //将 userList 拷贝到 userVOList 中
  12.        for(int i=0; i<userList.size(); i++) {
  13.            UserVO userVO = new UserVO();
  14.            User user = userList.get(i);
  15.            BeanUtils.copyProperties(user, userVO);
  16.            userVOList.add(userVO);
  17.        }
  18.        //将 userVoList 写入到数据库中
  19.        FileOutputStream out = null;
  20.        try {
  21.            out = new FileOutputStream("/Users/liuyanzhao/Desktop/Test/demo.xls");
  22.        } catch (FileNotFoundException e) {
  23.            e.printStackTrace();
  24.        }
  25.        ExcelUtil<UserVO> util = new ExcelUtil<UserVO>(UserVO.class);// 创建工具类.
  26.        util.exportExcel(userVOList, "用户信息"65536, out);// 导出
  27.        System.out.println("----执行完毕----------");
  28.        return "success";
  29.    }

 

2、导入

  1. @RequestMapping(value = "/importExcel")
  2.     @ResponseBody
  3.     public String importExcel() {
  4.                 FileInputStream fis = null;
  5.         try {
  6.             fis = new FileInputStream("/Users/liuyanzhao/Desktop/Test/demo2.xls");
  7.             ExcelUtil<UserVO> util = new ExcelUtil<UserVO>(
  8.                     UserVO.class);
  9.             // 创建excel工具类,返回Excel中的数据
  10.             List<UserVO> userVOList = util.importExcel("用户信息", fis);// 导入
  11.             System.out.println(userVOList);
  12.             //将userVOList 转成 userList
  13.             List<User> userList = new ArrayList<User>();
  14.             for(int i = 0; i < userVOList.size(); i++) {
  15.                 User user = new User();
  16.                 UserVO userVO = userVOList.get(i);
  17.                 BeanUtils.copyProperties(userVO,user);
  18.                 userList.add(user);
  19.             }
  20.             //插入数据库中
  21.             userService.insertUserBatch(userList);
  22.         } catch (FileNotFoundException e) {
  23.             e.printStackTrace();
  24.         }
  25.         return "success";
  26.     }

 

 

封装实现

1、ExcelVOAttribute.java  自定义注解

  1. package com.change.units;
  2. import java.lang.annotation.Retention;
  3. import java.lang.annotation.RetentionPolicy;
  4. import java.lang.annotation.Target;
  5. /**
  6.  * @author 言曌
  7.  * @date 2017/12/25 上午10:19
  8.  */
  9. @Retention(RetentionPolicy.RUNTIME)
  10. @Target( { java.lang.annotation.ElementType.FIELD })
  11. public @interface ExcelVOAttribute {
  12.     /**
  13.      * 导出到Excel中的名字.
  14.      */
  15.     public abstract String name();
  16.     /**
  17.      * 配置列的名称,对应A,B,C,D....
  18.      */
  19.     public abstract String column();
  20.     /**
  21.      * 提示信息
  22.      */
  23.     public abstract String prompt() default "";
  24.     /**
  25.      * 设置只能选择不能输入的列内容.
  26.      */
  27.     public abstract String[] combo() default {};
  28.     /**
  29.      * 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.
  30.      */
  31.     public abstract boolean isExport() default true;
  32. }

 

2、ExcelUtil.java   Excel操作封装类

  1. package com.change.units;
  2. import java.io.IOException;
  3. import java.io.InputStream;
  4. import java.io.OutputStream;
  5. import java.lang.reflect.Field;
  6. import java.text.DecimalFormat;
  7. import java.text.SimpleDateFormat;
  8. import java.util.ArrayList;
  9. import java.util.HashMap;
  10. import java.util.List;
  11. import java.util.Map;
  12. import org.apache.poi.hssf.usermodel.*;
  13. import org.apache.poi.ss.util.CellRangeAddressList;
  14. /**
  15.  * @author 言曌
  16.  * @date 2017/12/24 下午9:08
  17.  */
  18. /*
  19.  * ExcelUtil工具类实现功能:
  20.  * 导出时传入list<T>,即可实现导出为一个excel,其中每个对象T为Excel中的一条记录.
  21.  * 导入时读取excel,得到的结果是一个list<T>.T是自己定义的对象.
  22.  * 需要导出的实体对象只需简单配置注解就能实现灵活导出,通过注解您可以方便实现下面功能:
  23.  * 1.实体属性配置了注解就能导出到excel中,每个属性都对应一列.
  24.  * 2.列名称可以通过注解配置.
  25.  * 3.导出到哪一列可以通过注解配置.
  26.  * 4.鼠标移动到该列时提示信息可以通过注解配置.
  27.  * 5.用注解设置只能下拉选择不能随意填写功能.
  28.  * 6.用注解设置是否只导出标题而不导出内容,这在导出内容作为模板以供用户填写时比较实用.
  29.  * 本工具类以后可能还会加功能,请关注我的博客: http://blog.csdn.net/lk_blog
  30.  */
  31. public class ExcelUtil<T> {
  32.     Class<T> clazz;
  33.     public ExcelUtil(Class<T> clazz) {
  34.         this.clazz = clazz;
  35.     }
  36.     public List<T> importExcel(String sheetName, InputStream input) {
  37.         List<T> list = new ArrayList<T>();
  38.         try {
  39.             HSSFWorkbook workbook = new HSSFWorkbook(input);
  40.             HSSFSheet sheet = workbook.getSheet(sheetName);
  41.             if (!sheetName.trim().equals("")) {
  42.                 sheet = workbook.getSheet(sheetName);// 如果指定sheet名,则取指定sheet中的内容.
  43.             }
  44.             if (sheet == null) {
  45.                 sheet = workbook.getSheetAt(0); // 如果传入的sheet名不存在则默认指向第1个sheet.
  46.             }
  47.             int rows = sheet.getPhysicalNumberOfRows();
  48.             if (rows > 0) {// 有数据时才处理
  49.                 Field[] allFields = clazz.getDeclaredFields();// 得到类的所有field.
  50.                 Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();// 定义一个map用于存放列的序号和field.
  51.                 for (Field field : allFields) {
  52.                     // 将有注解的field存放到map中.
  53.                     if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
  54.                         ExcelVOAttribute attr = field
  55.                                 .getAnnotation(ExcelVOAttribute.class);
  56.                         int col = getExcelCol(attr.column());// 获得列号
  57.                         // System.out.println(col + "====" + field.getName());
  58.                         field.setAccessible(true);// 设置类的私有字段属性可访问.
  59.                         fieldsMap.put(col, field);
  60.                     }
  61.                 }
  62.                 for (int i = 1; i < rows; i++) {// 从第2行开始取数据,默认第一行是表头.
  63.                     HSSFRow row = sheet.getRow(i);
  64.                     int cellNum = row.getPhysicalNumberOfCells();
  65.                     T entity = null;
  66.                     for (int j = 0; j < cellNum; j++) {
  67.                         HSSFCell cell = row.getCell(j);
  68.                         if (cell == null) {
  69.                             continue;
  70.                         }
  71.                         String value = "";
  72.                         switch (cell.getCellType()) {
  73.                             case HSSFCell.CELL_TYPE_NUMERIC: // 数字
  74.                                 //如果为时间格式的内容
  75.                                 if (HSSFDateUtil.isCellDateFormatted(cell)) {
  76.                                     //注:format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制,若要24小时制,则把小h变为H即可,yyyy-MM-dd HH:mm:ss
  77.                                     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
  78.                                     value = sdf.format(HSSFDateUtil.getJavaDate(cell.
  79.                                             getNumericCellValue())).toString();
  80.                                     break;
  81.                                 } else {
  82.                                     value = new DecimalFormat("0").format(cell.getNumericCellValue());
  83.                                 }
  84.                                 break;
  85.                             case HSSFCell.CELL_TYPE_STRING: // 字符串
  86.                                 value = cell.getStringCellValue();
  87.                                 break;
  88.                             case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
  89.                                 value = cell.getBooleanCellValue() + "";
  90.                                 break;
  91.                             case HSSFCell.CELL_TYPE_FORMULA: // 公式
  92.                                 value = cell.getCellFormula() + "";
  93.                                 break;
  94.                             case HSSFCell.CELL_TYPE_BLANK: // 空值
  95.                                 value = "";
  96.                                 break;
  97.                             case HSSFCell.CELL_TYPE_ERROR: // 故障
  98.                                 value = "非法字符";
  99.                                 break;
  100.                             default:
  101.                                 value = "未知类型";
  102.                                 break;
  103.                         }
  104.                         System.out.println(value);
  105.                         if (value.equals("")) {
  106.                             continue;
  107.                         }
  108.                         entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建.
  109.                         // System.out.println(cells[j].getContents());
  110.                         Field field = fieldsMap.get(j);// 从map中得到对应列的field.
  111.                         // 取得类型,并根据对象类型设置值.
  112.                         Class<?> fieldType = field.getType();
  113.                         if (String.class == fieldType) {
  114.                             field.set(entity, String.valueOf(value));
  115.                         } else if ((Integer.TYPE == fieldType)
  116.                                 || (Integer.class == fieldType)) {
  117.                             field.set(entity, Integer.parseInt(value));
  118.                         } else if ((Long.TYPE == fieldType)
  119.                                 || (Long.class == fieldType)) {
  120.                             field.set(entity, Long.valueOf(value));
  121.                         } else if ((Float.TYPE == fieldType)
  122.                                 || (Float.class == fieldType)) {
  123.                             field.set(entity, Float.valueOf(value));
  124.                         } else if ((Short.TYPE == fieldType)
  125.                                 || (Short.class == fieldType)) {
  126.                             field.set(entity, Short.valueOf(value));
  127.                         } else if ((Double.TYPE == fieldType)
  128.                                 || (Double.class == fieldType)) {
  129.                             field.set(entity, Double.valueOf(value));
  130.                         } else if (Character.TYPE == fieldType) {
  131.                             if ((value != null) && (value.length() > 0)) {
  132.                                 field.set(entity, Character
  133.                                         .valueOf(value.charAt(0)));
  134.                             }
  135.                         }
  136.                     }
  137.                     if (entity != null) {
  138.                         list.add(entity);
  139.                     }
  140.                 }
  141.             }
  142.         } catch (IOException e) {
  143.             e.printStackTrace();
  144.         } catch (InstantiationException e) {
  145.             e.printStackTrace();
  146.         } catch (IllegalAccessException e) {
  147.             e.printStackTrace();
  148.         } catch (IllegalArgumentException e) {
  149.             e.printStackTrace();
  150.         }
  151.         return list;
  152.     }
  153.     /**
  154.      * 对list数据源将其里面的数据导入到excel表单
  155.      *
  156.      * @param sheetName
  157.      *            工作表的名称
  158.      * @param sheetSize
  159.      *            每个sheet中数据的行数,此数值必须小于65536
  160.      * @param output
  161.      *            java输出流
  162.      */
  163.     public boolean exportExcel(List<T> list, String sheetName, int sheetSize,
  164.                                OutputStream output) {
  165.         Field[] allFields = clazz.getDeclaredFields();// 得到所有定义字段
  166.         List<Field> fields = new ArrayList<Field>();
  167.         // 得到所有field并存放到一个list中.
  168.         for (Field field : allFields) {
  169.             if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
  170.                 fields.add(field);
  171.             }
  172.         }
  173.         HSSFWorkbook workbook = new HSSFWorkbook();// 产生工作薄对象
  174.         // excel2003中每个sheet中最多有65536行,为避免产生错误所以加这个逻辑.
  175.         if (sheetSize > 65536 || sheetSize < 1) {
  176.             sheetSize = 65536;
  177.         }
  178.         double sheetNo = Math.ceil(list.size() / sheetSize);// 取出一共有多少个sheet.
  179.         for (int index = 0; index <= sheetNo; index++) {
  180.             HSSFSheet sheet = workbook.createSheet();// 产生工作表对象
  181.             if (sheetNo == 0) {
  182.                 workbook.setSheetName(index, sheetName);
  183.             } else {
  184.                 workbook.setSheetName(index, sheetName + index);// 设置工作表的名称.
  185.             }
  186.             HSSFRow row;
  187.             HSSFCell cell;// 产生单元格
  188.             row = sheet.createRow(0);// 产生一行
  189.             // 写入各个字段的列头名称
  190.             for (int i = 0; i < fields.size(); i++) {
  191.                 Field field = fields.get(i);
  192.                 ExcelVOAttribute attr = field
  193.                         .getAnnotation(ExcelVOAttribute.class);
  194.                 int col = getExcelCol(attr.column());// 获得列号
  195.                 cell = row.createCell(col);// 创建列
  196.                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 设置列中写入内容为String类型
  197.                 cell.setCellValue(attr.name());// 写入列名
  198.                 // 如果设置了提示信息则鼠标放上去提示.
  199.                 if (!attr.prompt().trim().equals("")) {
  200.                     setHSSFPrompt(sheet, "", attr.prompt(), 1100, col, col);// 这里默认设了2-101列提示.
  201.                 }
  202.                 // 如果设置了combo属性则本列只能选择不能输入
  203.                 if (attr.combo().length > 0) {
  204.                     setHSSFValidation(sheet, attr.combo(), 1100, col, col);// 这里默认设了2-101列只能选择不能输入.
  205.                 }
  206.             }
  207.             int startNo = index * sheetSize;
  208.             int endNo = Math.min(startNo + sheetSize, list.size());
  209.             // 写入各条记录,每条记录对应excel表中的一行
  210.             for (int i = startNo; i < endNo; i++) {
  211.                 row = sheet.createRow(i + 1 - startNo);
  212.                 T vo = (T) list.get(i); // 得到导出对象.
  213.                 for (int j = 0; j < fields.size(); j++) {
  214.                     Field field = fields.get(j);// 获得field.
  215.                     field.setAccessible(true);// 设置实体类私有属性可访问
  216.                     ExcelVOAttribute attr = field
  217.                             .getAnnotation(ExcelVOAttribute.class);
  218.                     try {
  219.                         // 根据ExcelVOAttribute中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
  220.                         if (attr.isExport()) {
  221.                             cell = row.createCell(getExcelCol(attr.column()));// 创建cell
  222.                             cell.setCellType(HSSFCell.CELL_TYPE_STRING);
  223.                             cell.setCellValue(field.get(vo) == null ? ""
  224.                                     : String.valueOf(field.get(vo)));// 如果数据存在就填入,不存在填入空格.
  225.                         }
  226.                     } catch (IllegalArgumentException e) {
  227.                         e.printStackTrace();
  228.                     } catch (IllegalAccessException e) {
  229.                         e.printStackTrace();
  230.                     }
  231.                 }
  232.             }
  233.         }
  234.         try {
  235.             output.flush();
  236.             workbook.write(output);
  237.             output.close();
  238.             return true;
  239.         } catch (IOException e) {
  240.             e.printStackTrace();
  241.             System.out.println("Output is closed ");
  242.             return false;
  243.         }
  244.     }
  245.     /**
  246.      * 将EXCEL中A,B,C,D,E列映射成0,1,2,3
  247.      *
  248.      * @param col
  249.      */
  250.     public static int getExcelCol(String col) {
  251.         col = col.toUpperCase();
  252.         // 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。
  253.         int count = -1;
  254.         char[] cs = col.toCharArray();
  255.         for (int i = 0; i < cs.length; i++) {
  256.             count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
  257.         }
  258.         return count;
  259.     }
  260.     /**
  261.      * 设置单元格上提示
  262.      *
  263.      * @param sheet
  264.      *            要设置的sheet.
  265.      * @param promptTitle
  266.      *            标题
  267.      * @param promptContent
  268.      *            内容
  269.      * @param firstRow
  270.      *            开始行
  271.      * @param endRow
  272.      *            结束行
  273.      * @param firstCol
  274.      *            开始列
  275.      * @param endCol
  276.      *            结束列
  277.      * @return 设置好的sheet.
  278.      */
  279.     public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle,
  280.                                           String promptContent, int firstRow, int endRow, int firstCol,
  281.                                           int endCol) {
  282.         // 构造constraint对象
  283.         DVConstraint constraint = DVConstraint
  284.                 .createCustomFormulaConstraint("DD1");
  285.         // 四个参数分别是:起始行、终止行、起始列、终止列
  286.         CellRangeAddressList regions = new CellRangeAddressList(firstRow,
  287.                 endRow, firstCol, endCol);
  288.         // 数据有效性对象
  289.         HSSFDataValidation data_validation_view = new HSSFDataValidation(
  290.                 regions, constraint);
  291.         data_validation_view.createPromptBox(promptTitle, promptContent);
  292.         sheet.addValidationData(data_validation_view);
  293.         return sheet;
  294.     }
  295.     /**
  296.      * 设置某些列的值只能输入预制的数据,显示下拉框.
  297.      *
  298.      * @param sheet
  299.      *            要设置的sheet.
  300.      * @param textlist
  301.      *            下拉框显示的内容
  302.      * @param firstRow
  303.      *            开始行
  304.      * @param endRow
  305.      *            结束行
  306.      * @param firstCol
  307.      *            开始列
  308.      * @param endCol
  309.      *            结束列
  310.      * @return 设置好的sheet.
  311.      */
  312.     public static HSSFSheet setHSSFValidation(HSSFSheet sheet,
  313.                                               String[] textlist, int firstRow, int endRow, int firstCol,
  314.                                               int endCol) {
  315.         // 加载下拉列表内容
  316.         DVConstraint constraint = DVConstraint
  317.                 .createExplicitListConstraint(textlist);
  318.         // 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
  319.         CellRangeAddressList regions = new CellRangeAddressList(firstRow,
  320.                 endRow, firstCol, endCol);
  321.         // 数据有效性对象
  322.         HSSFDataValidation data_validation_list = new HSSFDataValidation(
  323.                 regions, constraint);
  324.         sheet.addValidationData(data_validation_list);
  325.         return sheet;
  326.     }
  327. }

 

 

原文地址:http://blog.csdn.net/lk_blog/article/details/8007777

 

 

  • 微信
  • 交流学习,有偿服务
  • weinxin
  • 博客/Java交流群
  • 资源分享,问题解决,技术交流。群号:590480292
  • weinxin
言曌

发表评论

:?::razz::sad::evil::!::smile::oops::grin::eek::shock::???::cool::lol::mad::twisted::roll::wink::idea::arrow::neutral::cry::mrgreen:

目前评论:1   其中:访客  1   博主  0

    • avatar 生如夏花

      老哥你是真的6,我就跟着你学就可以了 :mrgreen: :mrgreen: :mrgreen: 圈粉了