Java之POI操作，封裝ExcelUtil實現Excel匯入匯出

言曌發表於2018-04-02
Java封裝Excel

最近老師佈置了一個任務，實現 Excel 和資料庫的匯入匯出。

這個問題起初看起來是不復雜，實現簡陋的匯入匯出比較容易。

但是，後來老師要求寫一個包裝類，讓其他同學能夠直接使用。

這就涉及到了泛型和反射的內容，其實也不復雜。

最終，在網上找到一段程式碼，寫得挺好的，然後一頓除錯和修改，就能直接拿來用了。

現在也分享給大家。

 

基本功能

* 1.實體屬性配置了註解就能匯出到excel中,每個屬性都對應一列.

* 2.列名稱可以通過註解配置.

* 3.匯出到哪一列可以通過註解配置.

* 4.滑鼠移動到該列時提示資訊可以通過註解配置.

* 5.用註解設定只能下拉選擇不能隨意填寫功能.

* 6.用註解設定是否只匯出標題而不匯出內容,這在匯出內容作為模板以供使用者填寫時比較實用.

 

效果圖

資料庫

Java之POI操作，封裝ExcelUtil實現Excel匯入匯出

 

Excel

Java之POI操作，封裝ExcelUtil實現Excel匯入匯出

 

呼叫方法

下面是通過 SpringMVC 操作，匯入匯出

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

 

相關文章