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

avatar 2017年12月25日13:21:33 7 3552 views
博主分享免费Java教学视频，B站账号：Java刘哥 ，长期提供技术问题解决、项目定制：本站商品点此
最近老师布置了一个任务，实现 Excel 和数据库的导入导出。

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

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

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

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

现在也分享给大家。


基本功能


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

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

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

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

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

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


效果图


数据库





Excel




调用方法


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

UserVO.java
  1. package com.change.vo.excel;
  3. import com.change.units.ExcelVOAttribute;
  5. /**
  6.  * @author 言曌
  7.  * @date 2017/12/25 上午10:39
  8.  */
  10. public class UserVO {
  12.     @ExcelVOAttribute(name = "账号名", column = "A", isExport = true)
  13.     private String userName;
  15. //    @ExcelVOAttribute(name = "密码", column = "B", prompt = "密码要保密哦！", isExport = false)
  16. //    private String userPassword;
  18.     @ExcelVOAttribute(name = "角色名称", column = "B",isExport = true)
  19.     private String userRoleName;
  21.     @ExcelVOAttribute(name = "学院名称", column = "C" ,isExport = true)
  22.     private String userSchoolName;
  24.     @ExcelVOAttribute(name = "状态", column = "D", combo = {"正常","禁用"},isExport = true)
  25.     private String status;
  27.     @ExcelVOAttribute(name = "备注", column = "E",isExport = true)
  28.     private String memo;
  30.     public String getUserName() {
  31.         return userName;
  32.     }
  34.     public void setUserName(String userName) {
  35.         this.userName = userName;
  36.     }
  38. //    public String getUserPassword() {
  39. //        return userPassword;
  40. //    }
  41. //
  42. //    public void setUserPassword(String userPassword) {
  43. //        this.userPassword = userPassword;
  44. //    }
  46.     public String getUserRoleName() {
  47.         return userRoleName;
  48.     }
  50.     public void setUserRoleName(String userRoleName) {
  51.         this.userRoleName = userRoleName;
  52.     }
  54.     public String getUserSchoolName() {
  55.         return userSchoolName;
  56.     }
  58.     public void setUserSchoolName(String userSchoolName) {
  59.         this.userSchoolName = userSchoolName;
  60.     }
  62.     public String getStatus() {
  63.         return status;
  64.     }
  66.     public void setStatus(String status) {
  67.         this.status = status;
  68.     }
  70.     public String getMemo() {
  71.         return memo;
  72.     }
  74.     public void setMemo(String memo) {
  75.         this.memo = memo;
  76.     }
  77. }



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();
  11.        List<UserVO> userVOList = new ArrayList<UserVO>();
  13.        //将 userList 拷贝到 userVOList 中
  14.        for(int i=0; i<userList.size(); i++) {
  15.            UserVO userVO = new UserVO();
  16.            User user = userList.get(i);
  17.            BeanUtils.copyProperties(user, userVO);
  18.            userVOList.add(userVO);
  19.        }
  21.        //将 userVoList 写入到数据库中
  22.        FileOutputStream out = null;
  23.        try {
  24.            out = new FileOutputStream("/Users/liuyanzhao/Desktop/Test/demo.xls");
  25.        } catch (FileNotFoundException e) {
  26.            e.printStackTrace();
  27.        }
  28.        ExcelUtil<UserVO> util = new ExcelUtil<UserVO>(UserVO.class);// 创建工具类.
  29.        util.exportExcel(userVOList, "用户信息", 65536, out);// 导出
  30.        System.out.println("----执行完毕----------");
  31.        return "success";
  32.    }



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



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





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







历史上的今天
十二月
25日
  • 微信
  • 交流学习，资料分享
  • weinxin
  • 个人淘宝
  • 店铺名：言曌博客咨询部

  • (部分商品未及时上架淘宝)
avatar

发表评论

avatar 登录者：匿名
匿名评论，评论回复后会有邮件通知

  

已通过评论：1   待审核评论数：0
  1. avatar 生如夏花
    回复 2017年12月25日 13:41:24

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