JAVA POI导出excel-白红宇

JAVA POI导出excel

阅读量：6246 次

发布时间：2019-06-22

本文共 4902 字，大约阅读时间需要 16 分钟。

1.引入maven 依赖


                 
      
       org.apache.poi
                  
      
       poi
                  
      
       ${poi.version}
              
             
                 
      
       org.apache.poi
                  
      
       poi-ooxml
                  
      
       ${poi.version}
              
             
                 
      
       org.apache.poi
                  
      
       poi-ooxml-schemas
                  
      
       ${poi.version}

2.工具类

public class ExcelUtils {    /**     * @param response     * @param fileName excel文件名     * @param headMap  表头map     * @param dataList 表格数据     */    public static void exportXlsx(HttpServletResponse response, String fileName,                                  Map
     
       headMap, List
      
       
        > dataList) {        Workbook workbook = exportXlsx(fileName, headMap, dataList);        response.setContentType("application/binary;charset=ISO8859_1");        OutputStream outputStream = null;        try {            outputStream = response.getOutputStream();            String fn = new String(fileName.getBytes(), "ISO8859_1");            response.setHeader("Content-disposition", "attachment; filename=" + fn + ".xlsx");            workbook.write(outputStream);        } catch (Exception e) {            e.printStackTrace();        } finally {            if (outputStream != null) {                try {                    outputStream.close();                } catch (IOException e) {                    e.printStackTrace();                }            }        }    }    /**     * 导出数据     *     * @param headMap     * @param dataList     */    public static Workbook exportXlsx(String sheetName, Map
        
          headMap, List
         
          
           > dataList) { Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(sheetName); int rowIndex = 0, columnIndex = 0; Set
           
             keys = headMap.keySet(); //表头 Row row = sheet.createRow(rowIndex++); for (String key : keys) { Cell cell = row.createCell(columnIndex++); cell.setCellValue(headMap.get(key)); } //内容 if (dataList != null && !dataList.isEmpty()) { for (Map
            
              map : dataList) { row = sheet.createRow(rowIndex++); columnIndex = 0; for (String key : keys) { Cell cell = row.createCell(columnIndex++); setCellValue(cell, map.get(key)); } } } return workbook; } private static void setCellValue(Cell cell, Object obj) { if (obj == null) { return; } if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Date) { Date date = (Date) obj; if (date != null) { cell.setCellValue(DateUtils.dfDateTime.format(date)); } } else if (obj instanceof Calendar) { Calendar calendar = (Calendar) obj; if (calendar != null) { cell.setCellValue(DateUtils.dfDateTime.format(calendar.getTime())); } } else if (obj instanceof Timestamp) { Timestamp timestamp = (Timestamp) obj; if (timestamp != null) { cell.setCellValue(DateUtils.dfDateTime.format(new Date(timestamp.getTime()))); } } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } else { cell.setCellValue(obj.toString()); } }}

　　3.controller导出用户数据

@RequestMapping("/export")    public String export(String username, HttpServletRequest request, HttpServletResponse repsonse) {        //查询用户数据        UserQueryDTO queryDTO = new UserQueryDTO() {
    {            setUsername(username);        }};        List
     
       userList = userService.findUser(queryDTO);        //表头        Map
      
        headNameMap = new LinkedHashMap
       
        ();        headNameMap.put("userId", "ID");        headNameMap.put("roleName", "角色");        headNameMap.put("userName", "账号");        headNameMap.put("realName", "姓名");        headNameMap.put("mobile", "电话号码");        headNameMap.put("createDate", "创建时间");        headNameMap.put("status", "状态");        //表格数据        List
        
         
          > list = new ArrayList
          
           
            >(); if (userList != null && userList.size() > 0) { for (CmsUser user : userList) { String statusName = "正常"; if (StringUtils.isNotBlank(user.getDeleteFlag()) && user.getDeleteFlag().equals(ConstantHelper.DELETE_FLAG_DELETED)) { statusName = "删除"; } String createDate = ""; if (user.getCreateDate() != null) { createDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getCreateDate()); } String roleName = ""; Set
            
              roleSet = roleService.findByUserId(user.getUserId()); if (roleSet != null && roleSet.size() > 0) { for (CmsRole r : roleSet) { roleName += r.getName() + " "; } } Map
             
               map = new HashMap
              
               (); map.put("userId", user.getUserId()); map.put("roleName", roleName); map.put("userName", user.getUsername()); map.put("realName", user.getRealName()); map.put("mobile", user.getMobile()); map.put("createDate", createDate); map.put("status", statusName); list.add(map); } } ExcelUtils.exportXlsx(repsonse, "用户", headNameMap, list); return null; }