您的位置: 首页  >  IT文章  >  Springmvc，使用poi导出选中内容，从数据库导出excel。可以自定义标题。

Springmvc，使用poi导出选中内容，从数据库导出excel。可以自定义标题。

分类: IT文章 • 2024-04-10 09:39:21

最近有需求，需要做一个可以从数据库中导出excel的功能，excel需要可以调整格式和sheet等，导出的内容可以在前台界面选择后，把选择的内容根据自定义的格式导出。 在网上查找了很多资料，耗时很久，终于实现 了此功能。下面贴上代码。

创建excel的工具类，调用这个方法从controller那边传入需要的值，titles标题，sheet名称，文件路径，导入的内容map。

public class ExcelUtil {


    /**
     * @info 写出Excel标题内容
     * @param fos
     * @return
     */
    public static void writeExcel(String filePath, String[] titles,
            List<Map<Integer, String>> lists ,String sheetname) throws IOException {
        //List<Map<Integer, String>> lists
        
        OutputStream fos = new FileOutputStream(filePath);
        HSSFWorkbook xls = new HSSFWorkbook();
        HSSFSheet sheet = xls.createSheet(sheetname);
        HSSFRow row = sheet.createRow(0);// 第一行

        HSSFCell cell = null;
        
        //设置单元格宽度，从第3列开始宽度为40.
        for(int i=0;i< titles.length; i++){
        sheet.setColumnWidth(i, 20 * 256);
        }

        CellStyle cs = setSimpleCellStyle(xls);//Excel单元格样式
        CellStyle cs2 = setSimpleCellStyle2(xls);//Excel单元格样式
          for (int i = 0; i < titles.length; i++) {
            cell = row.createCell(i);
            cell.setCellStyle(cs2);
            cell.setCellValue(titles[i]);
            
          }                


        // 内容
        
//        Map<Integer, String> map = new HashMap<Integer, String>();
          int rowNum = 1;
        for(Map<Integer, String> map:lists){
         HSSFRow rowTmp = sheet.createRow(rowNum);
         for(int i=0;i<map.size();i++){
             cell= rowTmp.createCell(i);
             cell.setCellValue(map.get(i));
             cell.setCellStyle(cs);
         }
  
        rowNum++;
    }
        xls.write(fos);
        fos.close();
    }
}

controller方法。

    @RequestMapping("ajaxExport.ajax")
    @ResponseBody
    public  String downloadPlan( HttpServletRequest request, HttpServletResponse response,String fileUrl,String ids, HttpSession seesion,PlanEntity pe) throws Exception {
        ResultObject result = new ResultObject();
        //        Date date = new Date(); Map<String, Object>
        String mypath=    this.getClass().getClassLoader().getResource("/").getPath();
        mypath=URLDecoder.decode(mypath,"utf-8");
        String s=mypath.substring(0, mypath.length()-32);    
        //fileUrl =this.uploadUrl;
        
        List<String> idlist = new ArrayList<String>();  
        Collections.addAll(idlist, ids.split(","));    
        List<PlanEntity> pes = planService.selectByCondition1(pe, idlist);
        String sheetname="预案";
        String fileName ="plan"+pes.size();
        String filePath = s + "/uploadPlan/" +fileName + ".xlsx";
        System.out.println(filePath);
        //设置表头
        String[] titles = {"预案名","预案类型","创建人","使用单位","创建时间","实施时间","截止时间","责任人","责任人电话"};
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy年MM月dd日 "); 
//        SimpleDateFormat dateFormater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
         List<Map<Integer, String>> lists = new ArrayList<Map<Integer,String>>();
        for (int i = 0; i < pes.size(); i++) {
            PlanEntity pe1 =  pes.get(i);
            Map<Integer, String> paramsLists = new HashMap<Integer, String>();
            
            paramsLists.put(0, pe1.getName());
            paramsLists.put(1, pe1.getType());    
            paramsLists.put(2, pe1.getWriter());
            paramsLists.put(3, pe1.getCompanyname());
            paramsLists.put(4, sdf.format(pe1.getAdddate()));
            paramsLists.put(5, sdf.format(pe1.getWorkdate()));
            paramsLists.put(6, sdf.format(pe1.getDeadline()));
            paramsLists.put(7, pe1.getResponser());
            paramsLists.put(8, pe1.getPhone());
            
            
            lists.add(paramsLists);
        }
        ExcelUtil.writeExcel(filePath, titles, lists,sheetname);
        String ip="http://192.168.0.140:8080";
        String path=ip+"/uploadPlan/" +fileName + ".xlsx";
        result.setSuccess(true);
        System.out.println(path);
        result.setData(path);
        //return result;
        return path;
    }
View Code

自定义标题等内容，传入工具类生成excel，生成excel后需要调用这个下载的方法。

    @RequestMapping("/downloafFile.html")
    public void downloafFile(String fileUrl, String fileName ,HttpServletRequest request, HttpServletResponse response, Model model) throws Exception {
        try {
            System.out.println(fileUrl);
            System.out.println(fileName);
            fileName+=".xlsx";
            System.out.println(fileName);
            //fileUrl =this.uploadUrl+fileUrl;
            response.setHeader("Content-type", "text/html;charset=UTF-8");
            URL url = new URL(fileUrl);
            URLConnection con = url.openConnection(); 
            System.out.println();
            if(((HttpURLConnection) con).getResponseCode()==404){
                response.getWriter().print("文件不存在！");
                return;
            }
            //设置请求超时为5s  
            con.setConnectTimeout(5*1000);  
            // 输入流  
            InputStream is = con.getInputStream(); 
           // response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8"));
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
            response.setCharacterEncoding("UTF-8");
            //response.addHeader("Content-Length", "" + file.length());
     //     response.setContentType("application/vnd.ms-excel");
            response.setContentType("application/octet-stream");
            BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream());
            //bos.write(FileUtils.readFileToByteArray(file));
            bos.write(readInputStream(is));
            bos.flush();
        } catch (Exception e) {
            throw e;
        }
    }

然后在JSP页面中通过ajax传值。

          function downloafFile(fileUrl,fileName){
              if(fileName==null){
                    var selectedRows = mmg.selectedRows();
                            fileName=selectedRows[0].name;
                }
             var DLurl =  basePath + "/plan/plan/downloafFile.html?fileUrl=" + fileUrl+"&fileName="+fileName;
            
            window.open(DLurl);
          }

             var mmg = null;
             
             //前台选择ids传入
             $(function(){
              $('#exportExcel').click(function(){

                        var ids = new Array();
                          var selectedRows = mmg.selectedRows();
                      for (var i = 0; i < selectedRows.length; i++) {
                              ids.push(selectedRows[i].id);
                              
                          }
                      if(ids.length == 0){
                          HSLayer.errorMsg("您还未选择要导出的预案！");
                      return;
                      }

                      var param={};
                      param["ids"]=ids.join(',');
                      $.ajax({
                          url:"${basePath}/plan/plan/ajaxExport.ajax",
                      
                          data:param,
                          type:"POST",
                          dateType:"json",
                          //返回excel地址
                          success:function(result){
                              console.log(result);
                              var fileUrl= result;
                              //alert(fileUrl);
                              downloafFile(fileUrl,null);
                          }
                          
                      });
                    
});

大功告成。JS中代码的作用就是在前台选择后，传入选择的ids到后台后，传入相关的fileurl到下载方法。Springmvc，使用poi导出选中内容，从数据库导出excel。可以自定义标题。

相关推荐