将数据字典转为Excel表格

最新推荐文章于 2024-06-12 17:30:00 发布

diiiw

最新推荐文章于 2024-06-12 17:30:00 发布

阅读量1.1k

点赞数 1

文章标签： java servlet 数据库

本文链接：https://blog.csdn.net/diiiw/article/details/126505182

版权

导入依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.10-FINAL</version>
</dependency>

转Excel的工具类

public class ExportExcel {
   /**
     * @param sheetName 工作表的名字
     * @param column  列名
     * @param data  需要导出的数据    ( map的键定义为列的名字 一定要和column中的列明保持一致  )
     * @param response
     */
    public static void exportExcel(String sheetName, List<String> column, List<Map<String,Object>> data, HttpServletRequest request, HttpServletResponse response){
       //创建工作薄
      HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
      //创建sheet
      HSSFSheet sheet = hssfWorkbook.createSheet(sheetName);
      // 表头
      HSSFRow headRow = sheet.createRow(0);
      for (int i = 0; i < column.size(); i++){
         headRow.createCell(i).setCellValue(column.get(i));
      }
      
      for (int i = 0; i < data.size(); i++) {
         HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
         for (int x = 0; x < column.size(); x++) {
            dataRow.createCell(x).setCellValue(data.get(i).get(column.get(x))==null?"":data.get(i).get(column.get(x)).toString());
         }
      }

      response.setContentType("application/vnd.ms-excel");
      
      try {
            //获取浏览器名称
            String agent=request.getHeader("user-agent");
            String filename=sheetName+".xls";
            //不同浏览器需要对文件名做特殊处理
            if (agent.contains("Firefox")) { // 火狐浏览器
             filename = "=?UTF-8?B?"
               + new BASE64Encoder().encode(filename.getBytes("utf-8"))
               + "?=";
             filename = filename.replaceAll("\r\n", "");
            } else { // IE及其他浏览器
             filename = URLEncoder.encode(filename, "utf-8");
             filename = filename.replace("+"," ");
            }
            //推送浏览器
            response.setHeader("Content-Disposition","attachment;filename="+filename);
            hssfWorkbook.write(response.getOutputStream());
           } catch (Exception e) {
            e.printStackTrace();
           }
    }

}

具体代码实现

 /**
     * 导出数据到excel表格
     * @param request
     * @param response
     */
    @RequestMapping("exportData")
    @ResponseBody
    public void export(HttpServletRequest request, HttpServletResponse response){
        //创建excel表头
        List<String> column = new ArrayList<>();
        column.add("id");
        column.add("parent_id");
        column.add("name");
        column.add("dict_code");
        column.add("create_id");
        column.add("create_name");
        column.add("update_name");
        column.add("create_time");
        column.add("update_time");
        column.add("is_deleted");
        List<Dict> dictList = dictService.findAll();
        //表头对应的数据
        List<Map<String,Object>> data = new ArrayList<>();
        //遍历获取到的需要导出的数据，k要和表头一样
        dictList.stream().forEach(dict ->{
            Map<String,Object> dataMap = new HashMap<>();
            dataMap.put("id",dict.getId() );
            dataMap.put("parent_id", dict.getParentId());
            dataMap.put("name", dict.getName());
            dataMap.put("dict_code", dict.getDictCode());
            dataMap.put("create_id", dict.getCreateId());
            dataMap.put("create_name", dict.getCreateName());
            dataMap.put("update_name", dict.getUpdateName());
            dataMap.put("create_time", dict.getCreateTime());
            dataMap.put("update_time", dict.getUpdateTime());
            dataMap.put("is_deleted", dict.getIsDeleted());
            data.add(dataMap);
        });
        //调用导出工具类
        ExportExcel.exportExcel("数据字典",column,data,request,response);

    }