页面数据导入到Excel

首先需要添加依赖,这里4.1.0版本方便下面设置下载的文件名

<!--导出到Excel-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-excelant</artifactId>
      <version>4.1.0</version>
    </dependency>

controller层的代码

//导出Excel
    @RequestMapping("/project-export")
    public String projectExporrt(String filename,String ids,String mark, int currentPage, Condition condition, HttpServletResponse response)throws Exception{
        String conValue = condition.getConValue();
        condition.setConValue("%"+conValue+"%");
        List<Project> pros = null;
        if("curr".equals(mark)){
            String[] split = ids.split(",");
            int[] idS = new int[split.length];
            for (int i = 0; i < split.length; i++) {
                idS[i] = Integer.parseInt(split[i]);
            }
            pros = projectService.queryProjectToExport(idS,null);
        }else{
            pros = projectService.queryProjectToExport(null,condition);
        }
        //导出操作---导出的时候需要从集合中获取数据然后向Excel表中写入
        //先创建Excel文件
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
        workbook.setCompressTempFiles(true);
        //在文件中创建一张表
        SXSSFSheet sheet = workbook.createSheet("customer");
        //创建标题行
        SXSSFRow headerRow = sheet.createRow(0);
        //在第一行中添加单元格
        SXSSFCell headerCell = null;
        //标题名称
        String[] titles = { "序号", "项目名称","项目简介","创建时间","花费" };
        for (int c = 0; c < titles.length; c++) {
            headerCell = headerRow.createCell(c);
            headerCell.setCellValue(titles[c]);
            sheet.setColumnWidth(c, (30 * 160));
        }

        //循环遍历集合，在循环遍历集合的同时，创建表格的其他行，以及其他单元格，在每个单元格中插入数据
        for(int i = 0;i < pros.size();i++){
            Project project = pros.get(i);
            SXSSFRow row = sheet.createRow(i + 1);
            SXSSFCell cell = null;
            int c = 0;
            cell = row.createCell(c++);
            cell.setCellValue(project.getPid());
            cell = row.createCell(c++);
            cell.setCellValue(project.getPname());
            cell = row.createCell(c++);
            cell.setCellValue(project.getRemark());
            cell = row.createCell(c++);
            Date d = project.getStarttime();
            System.out.println(d);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String format = sdf.format(d);
            cell.setCellValue(format);
            cell = row.createCell(c++);
            cell.setCellValue(project.getCost());
        }
        //把表格送出去--保存到服务器端机器中，送出到客户端
        //导出服务器端
//        FileOutputStream fileOut = new FileOutputStream("C:\\Users\\Thinkpad\\Desktop\\项目\\customer.xlsx");
        //下载到客户端
        filename = filename + ".xls";
        filename = new String(filename.getBytes("utf-8"),"iso8859-1");
        //设置头
//        HttpHeaders header = new HttpHeaders();
//        header.setContentDispositionFormData("attachment", filename);
        response.setHeader("content-Disposition","attachment;fileName="+filename);
        OutputStream fileOut  = response.getOutputStream();
        workbook.write(fileOut);
        fileOut.close();
        return "redirect:/project-show";
    }
    //项目需求分析管理的点击进入时的展示
    @RequestMapping("/project-need-show")
    public String projectNeedShow(Model model,@RequestParam(defaultValue = "1") int currentPage)throws Exception{
        PageInfo<AnalysisExtends> pageInfo = analysisService.queryAnalysisList(currentPage);
        model.addAttribute("pageInfo",pageInfo);
        return "/project-need.jsp";
    }

前端页面的代码

//导出Excel函数
    function exportCus(name) {
        //alert(11);
        var filename = prompt("请输入下载的文件名");
        var  mt = $("#mt").val();
        var info = $("#info").val();
        if ("curr" == name){
            var str = "";
            $(".np").each(function () {
                if (this.checked){
                    str += ",";
                    str += $(this).val();
                }
            });
            if (str.length > 1){
                str = str.substring(1);
                //alert(str);
                location.href= "pro/project-export?ids="+str+"&mark="+name+"&conCaption="+mt+"&conValue="+info+"&currentPage="+$("#currentPage").val()+"&filename="+filename;
            } else {
                alert("您还未选中需要导出的选项!");
                return;
            }
        }else {
            location.href= "pro/project-export?ids=&mark="+name+"&conCaption="+mt+"&conValue="+info+"&currentPage="+$("#currentPage").val()+"&filename="+filename;
        }
    }

xml文件中的配置

<!--导出到Excel-->
	<select id="queryProjectToExport" parameterType="map" resultType="Project">
		SELECT * FROM project
		<where>
			<if test="ids != null">
				<foreach collection="ids" item="id" open="pid in (" close=")" separator=",">
					#{id}
				</foreach>
			</if>
			<if test="con != null and con.conCaption.length > 0">
				${con.conCaption} like #{con.conValue}
			</if>
		</where>
	</select>