Java实现数据库中查询出的数据转存成excel表

表格样式可以通过代码定义 注意日期格式如果是以String类型的方式存到数据库，以对象的方式导出时要转换一次，直接导出格式不对。以HashMap的方式查询的没有该问题 因为导出excel表格用的是get方式传参，所以如果需要对导出的数据用中文模糊查询，此时用get传参会出现中文乱码 解决办法： 前端对需要传的中文参数进行一次编码 URLEncoder.encode(传参,“utf-8”); 后台需要再次解码：URLDecoder.decode(接收的参数,“utf-8”);

@RequestMapping(value = "outPutExcel", method = RequestMethod.GET)
	public void outPutExcel( HttpServletResponse response,String fuzzyQuery ,String beginTime, String endTime) {
          
   
		 //模糊查询防止中文乱码
		 if (null != fuzzyQuery && !fuzzyQuery.equals("")){
          
   
            try {
          
   
	                fuzzyQuery = URLDecoder.decode(fuzzyQuery,"UTF-8");
	            } catch (UnsupportedEncodingException e) {
          
   
	                e.printStackTrace();
	            }
	        }
			response.reset();
			//设置浏览器下载的格式，并以当前时间的毫秒数命名
			response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls");
			response.setContentType("application/msexcel");
			 List<HashMap<String, Object>> mapList = queryStatement(beginTime,endTime,fuzzyQuery);
	        if (mapList.size()<1){
          
   
	            throw new NullPointerException("导出数据源为空");
	        }
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("sheet0");
			HSSFRow rows;
			HSSFCell cells;
			//设置表格第一行的列名
			// 获得表格第一行
			int i =0;
			rows = sheet.createRow(i);
		 	String [] strings = {
          
   "客户姓名","单号","下单时间","采购单号","店面","采购金额","已付款"};
	        for (int j = 0;j<strings.length;j++){
          
   
	            // 根据需要给第一行每一列设置标题
	            cells = rows.createCell(j);
	            cells.setCellValue(strings[j]);
	        }
	        for (HashMap<String,Object> hashMap : mapList) {
          
   
	            i++;
	            rows = sheet.createRow(i);
	            //客户姓名
	            cells = rows.createCell(0);
	            cells.setCellValue(hashMap.get("consigneeName").toString());
	            //单号
	            cells = rows.createCell(1);
	            cells.setCellValue(hashMap.get("onlineNumber").toString());
	            //下单时间
	            cells = rows.createCell(2);
	            cells.setCellValue(hashMap.get("buyingTime").toString());
	            //采购单号
	            cells = rows.createCell(3);
	            cells.setCellValue(hashMap.get("buyNumber").toString());
	            //店面
	            cells = rows.createCell(4);
	            cells.setCellValue(hashMap.get("companyName").toString());
	            //采购金额
	            cells = rows.createCell(5);
	            cells.setCellValue(hashMap.get("purchaseAmount").toString());
	            //已付款
	            cells = rows.createCell(6);
	            cells.setCellValue(hashMap.get("paymentAmount").toString());
	        }
	        try {
          
   
	            OutputStream outputStream = response.getOutputStream();
	            wb.write(outputStream);
	            outputStream.flush();
	        } catch (IOException e) {
          
   
	            e.printStackTrace();
	        }
	}