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

女爷i 2021-11-08 22:22 198阅读 0赞

最近有需求，需要做一个可以从数据库中导出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方法。

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

@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;
        }

自定义标题等内容，传入工具类生成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传值。

大功告成。JS中代码的作用就是在前台选择后，传入选择的ids到后台后，传入相关的fileurl到下载方法。![1106052-20170817095803584-1470611790.png][]

转载于:https://www.cnblogs.com/zl0717/p/7380064.html

[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211108/1ec52961591e4ee4b9867907ac26b905.png
[1106052-20170817095803584-1470611790.png]: /images/20211108/13b2f30ea705468899c617e2db5bdbf2.png