java-导出excel并解决乱码问题

浅浅的花香味﹌ 2022-08-05 01:28 329阅读 0赞

技术 struts2+easyui（参考，主要解决编码和excel导出问题)

**表单的设置**

**js代码**

//弹框式导出到Excel表中
    	 function exportExcel(){
    		var gs = $('#gs').combobox('getValue');
    		var ssjd = $('#jd').combobox('getValue');
    		var number = $("#number").val();
    		var name = $("#name").val();

<%--方法一--%>
    <%--		//alert("查询条件：归属："+gs+",街道:"+ssjd+",编号："+number+",姓名："+name);--%>
    <%--		form1.action='/mzj/zjgl/exportExcel.action?rygs='+gs+'&ssjd='+ssjd+'&rybh='+number+'&name='+name,--%>
    <%--		form1.submit();--%>
    <%--方二--%>
    <%--		--%>
    <%--		 $.ajax({  --%>
    <%--			contentType : "application/json",--%>
    <%--			url:'/mzj/zjgl/exportExcel.action?rygs='+gs+'&ssjd='+ssjd+'&rybh='+number+'&name='+name,--%>
    <%--			type:"post",--%>
    <%--			dataType:"json",--%>
    <%--			async:false,--%>
    <%--			success:function(data) --%>
    <%--			{--%>
    <%--			},--%>
    <%--		 	error : function() {  --%>
    <%--                alert("导出失败");  --%>
    <%--			}--%>
    <%--		});--%>
    <%--方三--%>		 
    <%--		 jQuery("form")//选择form    --%>
    <%--		 .first()//选择第一个 第二个用eq(1) 最后一个 last()    --%>
    <%--		 .attr("action",'/mzj/zjgl/exportExcel.action?rygs='+gs+'&ssjd='+ssjd+'&rybh='+number+'&name='+name)//更改属性    --%>
    <%--		 .submit();//提交--%>
    <%--同方三--%>		 
    		    var url='/mzj/zjgl/exportExcel.action?rygs='+gs+'&ssjd='+ssjd+'&rybh='+number+'&name='+name; 
    			$("#form1").attr("action",url); 
    			$("#form1").submit(); 
    		 
    	 }

**后台代码**

// 弹出式导出Excel文件
    	public void exportExcel() {
    		HttpServletResponse response = this.getResponse();
    //		response.setContentType("application/vnd.ms-excel; charset=UTF-8");
    		response.setContentType("application/vnd.ms-excel;");
    		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss");
    		Date date = new Date();
    		String fileName = sdf.format(date);
    		response.setHeader("Content-Disposition", "attachment;filename="
    				+ fileName + ".xls");
    		
    		// ******************************************集合
    		String sort = this.getRequest().getParameter("sort");
    		String order = this.getRequest().getParameter("order");
    		String rygs = this.getRequest().getParameter("rygs");
    		String ssjd = this.getRequest().getParameter("ssjd");
    		String rybh = this.getRequest().getParameter("rybh");
    		String name = this.getRequest().getParameter("name");
    		// 查询条件
    		StringBuffer contions = new StringBuffer();
    		if (null != rygs && !"".equals(rygs)) {
    			contions.append(" and ").append(" CZ_RYGS='").append(rygs)
    					.append("' ");
    		}
    		if (null != ssjd && !"".equals(ssjd)) {
    			contions.append(" and ").append(" CZ_SSJD='").append(ssjd)
    					.append("' ");
    		}
    		if (null != rybh && !"".equals(rybh)) {
    			contions.append(" and ").append(" CZ_NUMBER = '").append(rybh)
    					.append("' ");
    		}
    		if (null != name && !"".equals(name)) {
    			contions.append(" and ").append(" CZ_NAME like '%")
    					.append(name).append("%' ");
    		}
    		Map map = new HashMap();
    		if (sort == null || order == null) {
    			map.put("sort", "rygs");
    			map.put("order", "asc");
    		} else {
    			map.put("sort", sort);
    			map.put("order", order);
    		}
    		map.put("contions", contions.toString());
    
    		CaptialService capitalService = new CaptialService();
    
    		long time1 = new Date().getTime();
    		System.out.println("开始时间："+new Date());
    		//写入Excel的数据list
    		List<CzzjInfo> list = capitalService.findAll(map);
    		//List<CzzjInfo> list=new ArrayList<CzzjInfo>();
    		System.out.println("结束时间："+new Date());
    		//数据列名
    		String[] headers = { "序号", "人员归属", "所属街道", "编号", "姓名", "商保（元）",
    				"商保补差（元）", "区补（元）", "生补（元）", "开户银行", "卡号" };
    		//写入Excel
    		try {
    			HSSFWorkbook wb = new HSSFWorkbook();
    			HSSFSheet sheet = wb.createSheet("sheet_1");
    			sheet.setDefaultColumnWidth((short) 15);
    			//设置格式
    			int iFontSize = 12;
    			String sFontname = "宋体";
    
    			HSSFFont Headfont = wb.createFont();
    			Headfont.setFontHeightInPoints((short) iFontSize); // 表头字体大小
    			Headfont.setFontName(sFontname); // 表头字体名称
    			Headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 表头字体加粗
    
    			HSSFCellStyle HeadStyle = wb.createCellStyle(); // 表头格式
    			HeadStyle.setFont(Headfont); // 表头字体
    			HeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 表头位置
    			
    			HSSFFont bodyfont = wb.createFont();
    			bodyfont.setFontHeightInPoints((short) 10); // 字体大小
    			bodyfont.setFontName(sFontname); // 字体名称
    			bodyfont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 字体加粗
    			HSSFCellStyle bodyStyle = wb.createCellStyle(); // 格式
    			bodyStyle.setFont(bodyfont); // 字体
    			bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 位置
    			
    			
    			int i = 0;
    			while (i <= list.size()) {
    				HSSFRow row = sheet.createRow(i);
    				if (i == 0) {
    					for (int j = 0; j < headers.length; j++) {
    						HSSFCell cell = row.createCell((short) j);
    						cell.setCellStyle(HeadStyle);
    						cell.setCellValue(headers[j]);
    					}
    				} else {
    					CzzjInfo cz = list.get(i-1);
    					HSSFCell cell = row.createCell((short) 0);
    					cell.setCellStyle(bodyStyle);
    					cell.setCellValue(i);
    					cell = row.createCell((short) 1);
    					cell.setCellValue(cz.getRygs());
    					cell = row.createCell((short) 2);
    					cell.setCellValue(cz.getSsjd());
    					cell = row.createCell((short) 3);
    					cell.setCellValue(cz.getBh());
    					cell = row.createCell((short) 4);
    					cell.setCellValue(cz.getXm());
    					cell = row.createCell((short) 5);
    					cell.setCellValue(cz.getSbs());
    					cell = row.createCell((short) 6);
    					cell.setCellValue(cz.getSbbcs());
    					cell = row.createCell((short) 7);
    					cell.setCellValue(cz.getQbs());
    					cell = row.createCell((short) 8);
    					cell.setCellValue(cz.getSbs());
    					cell = row.createCell((short) 9);
    					cell.setCellValue(cz.getKhyh());
    					cell = row.createCell((short) 10);
    					cell.setCellValue(cz.getKh());
    					
    	
    				}
    				i++;
    			}
    
    			java.io.OutputStream out = response.getOutputStream();
    			wb.write(out);
    			out.close();
    		} catch (FileNotFoundException e1) {
    			e1.printStackTrace();
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    
    	}