• POI动态字段导出Excel-导入Excel，解析加密数据再导出

    一、POI动态字段导出Excel-导入Excel

    1.直接导出Excel

    1.  public static void main(String[] args) throws IOException {
    2. //        String filePath = "E:\\测试POI生成文件\\测试POI生成文件01.xls";
    3. //        HSSFWorkbook workbook = new HSSFWorkbook();
    4. //        HSSFSheet sheet = workbook.createSheet();
    5. //        sheet = workbook.createSheet("测试POI生成文件01");
    6. //        FileOutputStream out = new FileOutputStream(filePath);
    7. //        workbook.write(out);
    8. //        out.close();
    9.  
    10.         // 升级到 POI 3.5
    11. //        TestController.test01();
    12. //        TestController.test02();o
    13.         String jsonString = "{\n" +
    14.                 "        \"title\":\"查询表格\",\n" +
    15.                 "        \"columns\":[\n" +
    16.                 "            [\n" +
    17.                 "                {\n" +
    18.                 "                    \"title\":\"序号\",\n" +
    19.                 "                    \"field\":\"SEQ\"\n" +
    20.                 "                },\n" +
    21.                 "                {\n" +
    22.                 "                    \"title\":\"当前状态\",\n" +
    23.                 "                    \"field\":\"CURRENT_STATUS\"\n" +
    24.                 "                }\n" +
    25.                 "            ]\n" +
    26.                 "        ]\n" +
    27.                 "    }";
    28.         Map headerMap = JSON.parseObject(jsonString);
    29.         List> list = new ArrayList<>();
    30.         for (int i = 1; i < 65534; i++) {
    31.             Map map = new HashMap<>();
    32.             map.put("SEQ", "seq" + i);
    33.             map.put("CURRENT_STATUS", "CURRENT_STATUS" + i);
    34.             list.add(map);
    35.         }
    36.         HSSFWorkbook workbook = TestController.test03(headerMap, list);
    37.         FileOutputStream os = new FileOutputStream("E:\\\\测试POI生成文件\\测试poi生成文件03.xls");
    38.         workbook.write(os);
    39.     }

    2.响应式导出Excel

    1. /**
    2.  *
    3.  * @param head 列表标题行，例：{"head": {"title": "接口异常统计数据","columns": [[{"title": "接口名称","field": "requestUrl"}, {"title": "接口类型","field": "interfaceType"}]]}}
    4.  * @param param  查询列表数据参数 {"code":"c_test_select","initSql":"AA==","init":[],"query":[{"name":"XM","value":"","type":"2"}]}
    5.  * @param request
    6.  * @param response
    7.  * @return
    8.  * @throws Exception
    9.  */
    10. @ApiOperation("2.导出列表数据")
    11. @GetMapping(value = "exportQueryData")
    12. public void exportQueryExcel(@RequestParam(value = "head") String head,
    13.                                    @RequestParam(value = "param" ) String param,
    14.                                    @RequestParam(value = "pager.pageNo" , defaultValue = "1") String pageNo,
    15.                                    @RequestParam(value = "pager.pageSize" , defaultValue = "0") String pageSize,
    16.                                    @RequestParam(value = "sort" , defaultValue = "") String sort,
    17.                                    @RequestParam(value = "direction" , defaultValue = "") String direction,
    18.                                    HttpServletRequest request,
    19.                                    HttpServletResponse response) throws Exception{
    20.     queryService.exportQueryExcel(head, param, pageNo, pageSize, sort, direction, request, response);
    21.     
    22.     
    23. /**
    24.  *
    25.  * @param head 列表标题行，例：{"head": {"title": "接口异常统计数据","columns": [[{"title": "接口名称","field": "requestUrl"}, {"title": "接口类型","field": "interfaceType"}]]}}
    26.  * @param param  查询列表数据参数 {"code":"c_test_select","initSql":"AA==","init":[],"query":[{"name":"XM","value":"","type":"2"}]}
    27.  * @param request
    28.  * @param response
    29.  * @return
    30.  * @throws Exception
    31.  */
    32. void exportQueryExcel(String head, String param, String pageNo, String pageSize, String sort, String direction, HttpServletRequest request, HttpServletResponse response) throws Exception;
    33.  
    34. @Override
    35.     public void exportQueryExcel(String head, String param, String pageNo, String pageSize, String sort, String direction, HttpServletRequest request, HttpServletResponse response) throws Exception {
    36.         log.info("-------------------------------开始导出数据------------------------------------------------------");
    37.         String deStringParam = ""; // 解密后 param
    38.         String deStringHead = ""; // 解密后 head
    39.         try {
    40.             // 解密（前端加密2次，）
    41.             deStringParam = URLDecoder.decode(param, "UTF-8");
    42.             log.info("解密后param：" + deStringParam);
    43.             deStringHead = URLDecoder.decode(head, "UTF-8");
    44.             log.info("解密后head：" + deStringHead);
    45.         }catch (Exception e){
    46.             log.error("解密数据异常");
    47.             e.printStackTrace();
    48.         }
    49.         Map paramMap = JSON.parseObject(deStringParam, Map.class);
    50.         Map headMap = new HashMap<>();
    51.         List outTitleAndColumnsList = new ArrayList<>();
    52.         List> inTitleAndColumnsList = new ArrayList<>();
    53.         String fileTitle = "";
    54.         if (paramMap!= null){
    55.             // 获取列表的查询标识
    56.             String queryCode = (String)paramMap.get("code");
    57.             Query query = queryDao.get(queryCode);
    58.             // 导出文件的标题
    59.             fileTitle = query.getQueryName();
    60.             String[] codes = new String[]{queryCode};
    61.             List fList = queryDao.getField(codes);
    62.             // 获取到导出文件的表头
    63.             for (Field field : fList) {
    64. //                List fieldList = new ArrayList();
    65.                 String[] inSort = deStringHead.split(",");
    66.                 for (String inS : inSort) {
    67.                     String fieSort = String.valueOf(field.getSort().toString());
    68.                     if (fieSort.equals(inS)){
    69.                         Map fieldMap = new HashMap<>();
    70.                         String tittle = field.getTittle();
    71.                         String fieldName = field.getFieldName();
    72.                         fieldMap.put("title", tittle);
    73.                         fieldMap.put("field", fieldName);
    74.                         inTitleAndColumnsList.add(fieldMap);
    75.                     }
    76.                 }
    77.             }
    78.         }
    79. //        List nullList = new ArrayList<>();
    80. //        nullList.add(inTitleAndColumnsList);
    81. //        outTitleAndColumnsList.add(nullList);
    82.         outTitleAndColumnsList.add(inTitleAndColumnsList);
    83.         Map map = new HashMap();
    84.         map.put("title", fileTitle);
    85.         map.put("columns", outTitleAndColumnsList);
    86.         headMap.put("head",map);
    87.         deStringHead = JSON.toJSONString(headMap);
    88.         log.info("-----------------------------------" + deStringHead);
    89.         //  获取需要导出的数据
    90.         Map inVo = new HashMap<>();
    91.         inVo.put("param", deStringParam);
    92.         inVo.put("pager.pageNo", pageNo);
    93.         // 暂时限制最多导出 1w 条数据
    94.         inVo.put("pager.pageSize", (StringUtils.isBlank(pageSize) || Integer.valueOf(pageSize) > 10000) ? "10000" : pageSize);
    95.         inVo.put("sort", sort);
    96.         inVo.put("direction", direction);
    97.         log.info("导出：查询数据，请求地址为:{},请求参数为：{}", commonQueryLoadDataUrl, JSON.toJSONString(inVo));
    98.         String result = HttpUtils.postString(queryDataUrl, inVo);
    99.         log.info("请求返回结果为：{}", result);
    100.         Map resultMap = JSON.parseObject(result, Map.class);
    101.         if (null == resultMap || !resultMap.containsKey("rows") || CollectionUtils.isEmpty((List)resultMap.get("rows"))){
    102.             throw new Exception("未查询到数据,请稍后再试！");
    103.         }
    104.         // 列表数据 集合(需要导出的数据)
    105.         List rows = (List)resultMap.get("rows");
    106.         List> dataList = ExportUtils.formatList(rows);
    107.         log.info("导出数据量为" + dataList.size());
    108.         Map parameterMap = new HashMap();
    109.         parameterMap.put("map",deStringParam);
    110.         Map headerMap = ExportUtils.buildMap(deStringHead,"head");// 列表标题行
    111.         // Excel的head 和 查询到的数据字段名会不一致，将导出的Excel的head名转换为数据字段名
    112.         if (!CollectionUtils.isEmpty(dataList)){
    113.             Map dataMap = dataList.get(0); // 数据的字段名 值
    114.             List> columns = (List>)((List) headerMap.get("columns")).get(0); // 传入的需要导出的标题 字段名
    115.             for (String key : dataMap.keySet()) {
    116.                 for (Map columnsMap : columns){
    117.                     if (key.equalsIgnoreCase(columnsMap.get("field"))){
    118.                         columnsMap.put("field", key);
    119.                     }
    120.                 }
    121.             }
    122.         }
    123.         //生成数据
    124.         HSSFWorkbook workbook = ExportUtils.createExcel(headerMap, dataList);
    125.         //输出
    126.         Writer.exportExcel(response, workbook, ExportUtils.getTitle(headerMap));
    127.         log.info("-------------------------------结束导出数据------------------------------------------------------");
    128.     }

    3.ExportUtils工具类

    1.  
    2.  
    3. import com.alibaba.fastjson.JSON;
    4. import net.sf.json.JSONObject;
    5. import org.apache.commons.lang.StringUtils;
    6. import org.apache.poi.hssf.usermodel.*;
    7. import org.apache.poi.hssf.util.Region;
    8.  
    9. import javax.servlet.http.HttpServletResponse;
    10. import java.util.ArrayList;
    11. import java.util.HashMap;
    12. import java.util.List;
    13. import java.util.Map;
    14. import java.util.regex.Pattern;
    15.  
    16. public class ExportUtils {
    17.    
    18.    // 通用Excel导出
    19.       public static HSSFWorkbook createExcel(Map headerMap,
    20.                                                List> dataList) {
    21.          // 第三行开始写表头
    22.          int headNum = 2;
    23.          // 表头行数
    24.          int headSize = 0;
    25.          // 数据列数
    26.          int columnLength = 0;
    27.  
    28.          int[][] place = null;
    29.          
    30.          String[] field = null;
    31.  
    32.          List dataField = new ArrayList();
    33.  
    34.          // 创建excel工作簿
    35.          HSSFWorkbook workbook = new HSSFWorkbook();
    36.          // 创建sheet
    37.          HSSFSheet sheet = workbook.createSheet();
    38.  
    39.          // 单元格样式
    40.          HSSFCellStyle style = workbook.createCellStyle(); // 样式对象
    41.          style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
    42.          style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
    43.  
    44.          // 字体样式
    45.          HSSFFont font = workbook.createFont();
    46.          font.setFontHeightInPoints((short) 20);// 设置字体大小
    47.          style.setFont(font);
    48.  
    49.          // 单元格样式
    50.          HSSFCellStyle cellStyle = workbook.createCellStyle(); // 样式对象
    51.          cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
    52.          cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
    53.  
    54.          HSSFRow row1 = sheet.createRow(0);
    55.          row1.setHeight((short) 500);
    56.          HSSFCell cell1 = row1.createCell((short) 0);
    57.  
    58.          //sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) 10));
    59.          cell1.setCellStyle(style);
    60.          //-------------cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
    61.          // 写Excel文件名称
    62.          cell1.setCellValue(headerMap.get("title").toString());
    63.  
    64.          // 获取表头数据
    65.          List> headerList = (List>) headerMap
    66.                .get("columns");
    67.          headSize = headerList.size();
    68.  
    69.          // 通过第一列表头，获取导出Excel列数
    70.          List> first = (List>) headerList
    71.                .get(0);
    72.          for (int i = 0; i < first.size(); i++) {
    73.             Map m = first.get(i);
    74.             if (null != m.get("colspan")) {
    75.                Object value = m.get("colspan");
    76.                if (value instanceof Integer) {
    77.                   columnLength = columnLength + (Integer) value;
    78.                }
    79.             } else {
    80.                columnLength = columnLength + 1;
    81.             }
    82.          }
    83.          // 初始化表头位置标志，用于判断单元格是否合并
    84.          place = new int[headSize][columnLength];
    85.          field = new String[columnLength];
    86.          System.out.println("  columnLength  " + columnLength);
    87.          System.out.println("   headsize   " + headSize);
    88.          // 在第三行开始创建表头
    89.          HSSFRow row = sheet.createRow(headNum);
    90.          // 设置每一列的宽度
    91.          sheet.setDefaultColumnWidth((short) 15);
    92.          sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) (columnLength-1)));
    93.  
    94.          for (int i = 0; i < headerList.size(); i++) {
    95.             if (headerList.get(i) instanceof List) {
    96.  
    97.                int k = 0, l = 0;
    98.                // 获取当前行的表头设置
    99.                List> header = (List>) headerList
    100.                      .get(i);
    101.                for (; k < columnLength; k++) {
    102.                   HSSFCell cellCheck = null;
    103.                   HSSFCell cell = null;
    104.                   int rowspan = 0;
    105.                   int colspan = 0;
    106.                   // 合并单元格直接跳过，进入下一列
    107.                   if (place[headNum - 2][k] == 1) {
    108.                      continue;
    109.                   } else { // 否则创建单元格
    110.                      cell = row.createCell((short) k);
    111.                   }
    112.                   // 获取当前单元格定义
    113.                   Map h = header.get(l);
    114.                   // 获取跨行
    115.                   if (null != h.get("rowspan")) {
    116.                      Object value = h.get("rowspan");
    117.                      if (value instanceof Integer) {
    118.                         rowspan = (Integer) value;
    119.                      }
    120.                   }
    121.                   // 获取跨列
    122.                   if (null != h.get("colspan")) {
    123.                      Object value = h.get("colspan");
    124.                      if (value instanceof Integer) {
    125.                         colspan = (Integer) value;
    126.                      }
    127.                   }
    128.  
    129.                   if (rowspan != 0 && colspan != 0) { // 跨行跨列
    130.                      // 合并单元格
    131.                      sheet.addMergedRegion(new Region(headNum, (short) k,
    132.                            headNum + rowspan - 1,
    133.                            (short) (k + colspan - 1)));
    134.                      // 标记合并单元格
    135.                      for (int m = headNum; m < headNum + rowspan; m++) {
    136.                         for (int n = k; n < k + colspan; n++) {
    137.                            place[m - 2][n] = 1;
    138.                         }
    139.                      }
    140.                      k = k + colspan - 1;
    141.                   } else if (rowspan != 0) { // 只跨行
    142.                      // 合并单元格
    143.                      sheet.addMergedRegion(new Region(headNum, (short) k,
    144.                            headNum + rowspan - 1, (short) k));
    145.                      // 标记合并单元格
    146.                      for (int m = headNum; m < headNum + rowspan; m++) {
    147.                         place[m - 2][k] = 1;
    148.                      }
    149.                   } else if (colspan != 0) { // 只跨列
    150.                      // 合并单元格
    151.                      sheet.addMergedRegion(new Region(headNum, (short) k,
    152.                            headNum, (short) (k + colspan - 1)));
    153.                      // 标记合并单元格
    154.                      for (int n = k; n < k + colspan; n++) {
    155.                         place[headNum - 2][n] = 1;
    156.                      }
    157.                      // 标记下一个未写列
    158.                      k = k + colspan - 1;
    159.                   }
    160.                   // 下一个表头设置
    161.                   l++;
    162.                   // 标记字段值
    163.                   if (null != h.get("field")
    164.                         && StringUtils.isNotBlank(h.get("field"))) {
    165.                      field[k] = h.get("field");
    166.                      dataField.add(h.get("field"));
    167.                   }
    168.                   // 设置单元格的样式
    169.                   cell.setCellStyle(cellStyle);
    170.                   cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    171.                   //-------------cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    172.                   // 在单元格写内容
    173.                   cell.setCellValue(h.get("title").toString());
    174.                   cell.getStringCellValue();
    175.                }
    176.                // 下一行
    177.                headNum = headNum + 1;
    178.                row = sheet.createRow(headNum);
    179.             }
    180.          }
    181.  
    182.          for (int i = 0; i < dataList.size(); i++) {
    183.             // 表头的下一行开始填充数据
    184.             HSSFRow row_value = sheet.createRow(headNum);
    185.             Map dataMap = dataList.get(i);
    186. //          System.out.println("------------------start数据"+ i + 1 +"------------------");
    187. //          String string = JSON.toJSONString(dataMap);
    188. //          System.out.println(string);
    189. //          System.out.println("------------------end数据"+ i + 1 +"------------------");
    190.             for (int n = 0; n < field.length; n++) {
    191. //             if (dataMap.get("CUST_CER_ID").equals("2134の1") && n == 9){
    192. //                System.out.println(1111);
    193. //                Object cust_cer_id = dataMap.get("CUST_CER_ID");
    194. //                System.out.println(cust_cer_id);
    195. //             }
    196.                HSSFCell cell = row_value.createCell((short) n);
    197.                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    198.                //-------------cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    199. //             cell.setCellValue(dataMap.get(field[n]) == null ? ""
    200. //                   : dataMap.get(field[n]).toString());
    201.                if(dataMap.get(field[n]) == null || dataMap.get(field[n]).toString() == ""){
    202.                   cell.setCellValue("");
    203.                }else{
    204.                   Boolean strResult = dataMap.get(field[n]).toString().matches("-[0-9]+(.[0-9]+)?|[0-9]+(.[0-9]+)?");
    205.                   Pattern p = Pattern.compile("[a-zA-z]");
    206.                   /**判断是否含有英文单词*/
    207.                    Boolean flag = p.matcher(dataMap.get(field[n]).toString()).find();
    208.                   /**dataMap.get(field[n]).toString().indexOf("-")>0判断主要是区分字符串2017-10和数字-10*/
    209. //                if (!strResult||dataMap.get(field[n]).toString().indexOf("-")>0||flag){
    210. //                   cell.setCellValue(dataMap.get(field[n]).toString());
    211. //                }else{
    212. //                   if(dataMap.get(field[n]).toString().length()>11){
    213. //                      //韶关现场 事项编码数字过长,导出时转换为String类型
    214. //                      cell.setCellValue(dataMap.get(field[n]).toString());
    215. //                   }else{
    216. //                      // 存在特殊符号的时候转换成 Double类型会出错
    217. //                      cell.setCellValue(Double.parseDouble(dataMap.get(field[n]).toString()));
    218. //                   }
    219. //                }
    220.                   cell.setCellValue(dataMap.get(field[n]).toString());
    221.                }
    222.             }
    223.             headNum = headNum + 1;
    224.          }
    225.          return workbook;
    226.       }
    227.       
    228.       /**
    229.        * 根据前台参数 整理出表头数据
    230.        * @param parameterMap
    231.        * @return
    232.        */
    233.       public static Map buildMap(Map parameterMap, String key){
    234.          JSONObject json = JSONObject.fromObject(parameterMap.get("map"));
    235.          Map param = json;
    236.          if (key!=null) {
    237.             Map keyMap = (Map) param.get(key);
    238.             return keyMap;
    239.          }
    240.          // 获取表头数据
    241.          return param;
    242.       }
    243.  
    244.    /**
    245.     * 根据前台参数 整理出表头数据
    246.     * @param parameterMap json串
    247.     * @param key head
    248.     * @return
    249.     */
    250.    public static Map buildMap(String parameterMap, String key){
    251.       Map param = JSON.parseObject(parameterMap, Map.class);
    252. //    Map param = json;
    253.       if (key!=null) {
    254.          Map keyMap = (Map) param.get(key);
    255.          return keyMap;
    256.       }
    257.       // 获取表头数据
    258.       return param;
    259.    }
    260.       
    261.       public static Map buildHeadMap(Map parameterMap){
    262.             Map keyMap = (Map) parameterMap.get("head");
    263.             return keyMap;
    264.       }
    265.       
    266.       /**
    267.        * 获取一个title
    268.        * @param headerMap
    269.        * @return
    270.        */
    271.       public static String getTitle(Map headerMap){
    272.          return headerMap.get("title") == null ? "未命名" : headerMap.get("title").toString();
    273.       }
    274.       
    275.       /**
    276.        * 根据 list 生成  List> 格式的数据
    277.        * @param 
    278.        * @param list
    279.        * @return
    280.        */
    281.       @SuppressWarnings("unchecked")
    282.       public static  List> formatList(List list){
    283.          List> dataList = new ArrayList>();
    284.          for(Object temp:list){
    285.             dataList.add(JSONObject.fromObject(temp));
    286.          }
    287.          return dataList;
    288.       }
    289.       
    290.       /**
    291.        * 传进表头数据和列表数据 直接导出excel
    292.        * @param header
    293.        * @param list
    294.        * @param response
    295.        */
    296.       public static void exportWrite(String header, List list, HttpServletResponse response){
    297.          Map parameterMap = new HashMap();
    298.          parameterMap.put("map", header);
    299.          
    300.          List> dataList = formatList(list);
    301.          
    302.          //根据head生成表头数据
    303.          Map headerMap = buildMap(parameterMap,"head");
    304.          //生成数据
    305.          HSSFWorkbook workbook = createExcel(headerMap,dataList);
    306.          //输出
    307.          Writer.exportExcel(response, workbook, getTitle(headerMap));
    308.       }
    309. }
      310.  

      4.Writer工具类
       

      1.  
      2.  
      3. import net.sf.json.JSONArray;
      4. import net.sf.json.JSONObject;
      5. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
      6.  
      7. import javax.servlet.http.HttpServletResponse;
      8. import java.io.IOException;
      9. import java.io.OutputStream;
      10. import java.io.PrintWriter;
      11. import java.net.URLEncoder;
      12. import java.util.List;
      13. import java.util.Map;
      14.  
      15. /**
      16.  * 文件名称：Writer.java
      17.  *
      18.  *
      19.  */
      20. public class Writer {
      21.    /**
      22.     * 输出不分页列表到前台
      23.     * @param response
      24.     * @param list
      25.     */
      26.    public static void outPutList(HttpServletResponse response, List list){
      27.       try {
      28.          response.setContentType("text/html;charset=GBK");
      29.          PrintWriter out = response.getWriter();
      30.          out.print(JSONArray.fromObject(list));
      31.          out.flush();
      32.          out.close();
      33.       } catch (IOException e) {
      34.          e.printStackTrace();
      35.       }
      36.    }
      37.    /**
      38.     * 输出普通对象到前台
      39.     * @param response
      40.     * @param list
      41.     */
      42.    public static void outPutObject(HttpServletResponse response, Object object){
      43.       try {
      44.          response.setContentType("text/html;charset=GBK");
      45.          PrintWriter out = response.getWriter();
      46.          out.print(JSONObject.fromObject(object));
      47.          out.flush();
      48.          out.close();
      49.       } catch (IOException e) {
      50.          e.printStackTrace();
      51.       }
      52.    }
      53.    /**
      54.     * 输出字符串到前台
      55.     * @param response
      56.     * @param list
      57.     */
      58.    public static void outPutStr(HttpServletResponse response, String jsonStr){
      59.       try {
      60.          response.setContentType("text/html;charset=GBK");
      61.          PrintWriter out = response.getWriter();
      62.          out.print(jsonStr);
      63.          out.flush();
      64.          out.close();
      65.       } catch (IOException e) {
      66.          e.printStackTrace();
      67.       }
      68.    }
      69.    
      70.    /**
      71.     * 导出到excel
      72.     * @param response
      73.     * @param workbook
      74.     */
      75.    public static void exportExcel(HttpServletResponse response, HSSFWorkbook workbook ){
      76.       try{
      77.          OutputStream out = response.getOutputStream();;
      78.          response.setContentType("application/vnd.ms-excel;charset=utf-8");
      79.          response.setCharacterEncoding("utf-8");
      80.          response.setHeader("Content-disposition", "attachment; filename="  
      81. //               + new String("所有办件统计".getBytes("utf-8"), "ISO8859-1")+".xls");
      82.                + String.valueOf(URLEncoder.encode("所有办件统计", "UTF-8"))+".xls");
      83.          workbook.write(out);
      84.          out.flush();
      85.          out.close();
      86.       }catch(Exception e){
      87.          e.printStackTrace();
      88.       }
      89.    }
      90.    
      91.    /**
      92.     * 导出到excel，文件名可自定义
      93.     * @param response
      94.     * @param workbook
      95.     */
      96.    public static void exportExcel(HttpServletResponse response, HSSFWorkbook workbook, String fileName ){
      97.       try{
      98.          OutputStream out = response.getOutputStream();;
      99.          response.setContentType("application/vnd.ms-excel;charset=utf-8");
      100.          response.setCharacterEncoding("utf-8");
      101.          /*response.setHeader("Content-disposition", "attachment; filename="  
      102.                  + String.valueOf(URLEncoder.encode(fileName, "UTF-8"))+".xls");*/
      103.          /**ISO8859-1是页面上数据传输的格式*/
      104.          response.addHeader("Content-Disposition", "attachment;filename="+ new String(fileName.getBytes("GB2312"),"ISO-8859-1")+".xls");
      105.          workbook.write(out);
      106.          out.flush();
      107.          out.close();
      108.       }catch(Exception e){
      109.          e.printStackTrace();
      110.       }
      111.    }
      112.    
      113.    /**
      114.     * 输出分页列表到前台供datagrid使用
      115.     * @param response
      116.     * @param list
      117.     */
      118.    public static void outPutToDataGrid(HttpServletResponse response, List> list){
      119.       try {
      120.          response.setContentType("text/html;charset=GBK");
      121.          PrintWriter out = response.getWriter();
      122.          DataGridObject dg = new DataGridObject();
      123.          dg.setRows(list);
      124.          dg.setTotal(PagerThreadLocal.getValue().getTotal()==0?list.size():PagerThreadLocal.getValue().getTotal());
      125.          JSONObject jsonObject = JSONObject.fromObject(dg);
      126.          out.print(jsonObject.toString());
      127.          out.flush();
      128.          out.close();
      129.       } catch (IOException e) {
      130.          e.printStackTrace();
      131.       }
      132.    }
      133. }
      134.  
       

      二、解析加密数据再导出
       

      2.1 ExcelUtil工具类：
       

      1.  
      2.  
      3. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
      4. import org.apache.poi.ss.usermodel.*;
      5.  
      6. import java.io.FileInputStream;
      7. import java.io.FileOutputStream;
      8. import java.io.IOException;
      9. import java.lang.reflect.Field;
      10. import java.util.ArrayList;
      11. import java.util.List;
      12.  
      13. /**
      14.  *  借鉴地址：https://blog.csdn.net/qq_37960007/article/details/84451534?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-14.control&dist_request_id=1328593.13383.16147799212719547&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-14.control
      15.  *  java如何读取、写入Excel文件(将内容转成任意的bean对象)
      16.  */
      17. public class ExcelUtil {
      18.  
      19.     public static  List parseFromExcel(String path, Class aimClass) {
      20.         return parseFromExcel(path, 0, aimClass);
      21.     }
      22.  
      23.     /**
      24.      *  导入Excel文件，将其数据装换为对应实体bean
      25.      * @param path 文件路径
      26.      * @param firstIndex 从firstIndex行开始
      27.      * @param aimClass 对应的实体类
      28.      * @param 
      29.      * @return
      30.      */
      31.     @SuppressWarnings("deprecation")
      32.     public static  List parseFromExcel(String path, int firstIndex, Class aimClass) {
      33.         List result = new ArrayList();
      34.         try {
      35.             FileInputStream fis = new FileInputStream(path);
      36.             Workbook workbook = WorkbookFactory.create(fis);
      37.             //对excel文档的第一页,即sheet1进行操作
      38.             Sheet sheet = workbook.getSheetAt(0);
      39.             int lastRaw = sheet.getLastRowNum();
      40.             for (int i = firstIndex; i <= lastRaw; i++) {
      41.                 //第i行
      42.                 Row row = sheet.getRow(i);
      43.                 T parseObject = aimClass.newInstance();
      44.                 Field[] fields = aimClass.getDeclaredFields();
      45.                 for (int j = 0; j < fields.length; j++) {
      46.                     Field field = fields[j];
      47.                     field.setAccessible(true);
      48.                     Class type = field.getType();
      49.                     //第j列
      50.                     Cell cell = row.getCell(j);
      51.                     if (cell == null)
      52.                         continue;
      53.                     //很重要的一行代码,如果不加,像12345这样的数字是不会给你转成String的,只会给你转成double,而且会导致cell.getStringCellValue()报错
      54.                     cell.setCellType(Cell.CELL_TYPE_STRING);
      55.                     String cellContent = cell.getStringCellValue();
      56.                     cellContent = "".equals(cellContent) ? "0" : cellContent;
      57.                     if (type.equals(String.class)) {
      58.                         field.set(parseObject, cellContent);
      59.                     } else if (type.equals(char.class) || type.equals(Character.class)) {
      60.                         field.set(parseObject, cellContent.charAt(0));
      61.                     } else if (type.equals(int.class) || type.equals(Integer.class)) {
      62.                         field.set(parseObject, Integer.parseInt(cellContent));
      63.                     } else if (type.equals(long.class) || type.equals(Long.class)) {
      64.                         field.set(parseObject, Long.parseLong(cellContent));
      65.                     } else if (type.equals(float.class) || type.equals(Float.class)) {
      66.                         field.set(parseObject, Float.parseFloat(cellContent));
      67.                     } else if (type.equals(double.class) || type.equals(Double.class)) {
      68.                         field.set(parseObject, Double.parseDouble(cellContent));
      69.                     } else if (type.equals(short.class) || type.equals(Short.class)) {
      70.                         field.set(parseObject, Short.parseShort(cellContent));
      71.                     } else if (type.equals(byte.class) || type.equals(Byte.class)) {
      72.                         field.set(parseObject, Byte.parseByte(cellContent));
      73.                     } else if (type.equals(boolean.class) || type.equals(Boolean.class)) {
      74.                         field.set(parseObject, Boolean.parseBoolean(cellContent));
      75.                     }
      76.                 }
      77.                 result.add(parseObject);
      78.             }
      79.             fis.close();
      80.         } catch (Exception e) {
      81.             e.printStackTrace();
      82.             System.err.println("An error occured when parsing object from Excel. at " );
      83.         }
      84.         return result;
      85.     }
      86.  
      87.  
      88.  
      89.     // 带标题写入Excel
      90.     public static  void writeExcelWithTitle(List beans, String path) {
      91.         writeExcel(beans,path,true);
      92.     }
      93.  
      94.     // 仅把数据写入Excel
      95.     public static  void writeExcel(List beans, String path) {
      96.         writeExcel(beans,path,false);
      97.     }
      98.  
      99.     /**
      100.      *
      101.      * @param beans 需要写成Excel文件的集合数据
      102.      * @param path  写到的指定路径
      103.      * @param writeTitle 文件标题
      104.      * @param 
      105.      */
      106.     private static  void writeExcel(List beans, String path, boolean writeTitle) {
      107.         if(beans == null || beans.size() == 0){
      108.             return;
      109.         }
      110.         Workbook workbook = new HSSFWorkbook();
      111.         FileOutputStream fos = null;
      112.         int offset = writeTitle ? 1 : 0;
      113.         try {
      114.             Sheet sheet = workbook.createSheet();
      115.             for (int i = 0; i < beans.size() + offset; ++i) {
      116.                 if(writeTitle && i == 0) {
      117.                     createTitle(beans, sheet);
      118.                     continue;
      119.                 }
      120.                 Row row = sheet.createRow(i);
      121.                 T bean = beans.get(i - offset);
      122.                 Field[] fields = bean.getClass().getDeclaredFields();
      123.                 for (int j = 0; j < fields.length; j++) {
      124.                     Field field = fields[j];
      125.                     field.setAccessible(true);
      126.                     Cell cell = row.createCell(j);
      127.                     //Date,Calender都可以 使用  +"" 操作转成字符串
      128.                     cell.setCellValue(field.get(bean)+"");
      129.                 }
      130.             }
      131.             fos = new FileOutputStream(path);
      132.             workbook.write(fos);
      133.         } catch (Exception e) {
      134.             e.printStackTrace();
      135.         }finally {
      136.             try {
      137.                 fos.close();
      138.                 workbook.close();
      139.             } catch (IOException e) {
      140.                 e.printStackTrace();
      141.             }
      142.         }
      143.     }
      144.  
      145.     private static  void createTitle(List beans,Sheet sheet){
      146.         Row row = sheet.createRow(0);
      147.         T bean = beans.get(0);
      148.         Field[] fields = bean.getClass().getDeclaredFields();
      149.         for (int i = 0; i < fields.length; i++) {
      150.             Field field = fields[i];
      151.             field.setAccessible(true);
      152.             Cell cell = row.createCell(i);
      153.             cell.setCellValue(field.getName());
      154.         }
      155.     }
      156.  
      157.  
      158.  
      159.     public static void main(String[] args) {
      160.  
      161.         /**
      162.          * 1.将Excel文件读取到程序中
      163.          */
      164.         //参数里的1表示有效行数从第1行开始
      165.         List publicUserList = ExcelUtil.parseFromExcel("C:\\Users\\tission_01\\Desktop\\（解密前）.xlsx", 1,AESPublicUser.class);
      166.         System.out.println("数据量：-----------" + publicUserList.size());
      167.         for (AESPublicUser aesPublicUser : publicUserList) {
      168.             System.out.println(aesPublicUser.toString());
      169.         }
      170.  
      171.         // 解密手机号码或IDcard号码
      172.         EncryptDesUtils des2 = new EncryptDesUtils("TESTTESTWIJDINVA");
      173.         for(AESPublicUser user : publicUserList){
      174.             try {
      175.                 user.setCertificate_no(des2.decrypt(user.getCertificate_no()));
      176.                 user.setContact_phone(des2.decrypt(user.getContact_phone()));
      177.             } catch (Exception e) {
      178.                 e.printStackTrace();
      179.             }
      180.         }
      181.  
      182.         /**
      183.          * 2.将数据写成Excel文件
      184.          */
      185. //        // 带标题写入excel
      186. //        List scenics = Lists.newArrayList();
      187. //        ExcelUtil.writeExcelWithTitle(scenics,"C:\\Users\\unive\\Documents\\景点信息\\scenics.xlsx");
      188. //
      189. //        // 不带标题写入excel
      190.         ExcelUtil.writeExcel(publicUserList,"C:\\Users\\tission_01\\Desktop\\(解密后).xlsx");
      191.  
      192.     }
      193. }
       

      2.2 Excel 对应的 实体类:
       

      1.  
      2.  
      3. import lombok.AllArgsConstructor;
      4. import lombok.Builder;
      5. import lombok.Data;
      6. import lombok.NoArgsConstructor;
      7.  
      8. @Data
      9. @Builder
      10. @NoArgsConstructor
      11. @AllArgsConstructor
      12. public class AESPublicUser {
      13.  
      14.     /**
      15.      * 用户ID
      16.      */
      17.     private String user_id;
      18.  
      19.     /**
      20.      * 用户名
      21.      */
      22. //    private String user_name;
      23.  
      24.     /**
      25.      * 手机号码
      26.      */
      27.     private String contact_phone;
      28.  
      29.     /**
      30.      * 证件号码
      31.      */
      32.     private String certificate_no;
      33. }
       

      
                
                    
                

                
    310. 

                    相关阅读:
      
                    
【RHCE】作业：DNS主从同步&防火墙iptables服务使用                            
      
如何有效利用chatgpt?                            
      
二、训练fashion_mnist数据集                            
      
java-Spring-入门学习-第二天(单例模式和多例模式)                            
      
K8s控制器                            
      
语法基础(函数)                            
      
python毕业设计作品基于django框架个人博客系统毕设成品（8）毕业设计论文模板                            
      
react151618刷新几次的问题                            
      
java,python遍历文件夹与子文件夹                            
      
java随手记                            
      
                          
                
      311. 
                
    311. 
                    原文地址：https://blog.csdn.net/liumangtutu2919/article/details/126450813
                
      312.