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

注意日期格式如果是以String类型的方式存到数据库的导出时要转换一次，直接导出格式不对

因为导出excel表格用的是get方式传参，所以如果需要对导出的数据用中文模糊查询，此时 用get传参会出现中文乱码

解决办法：

前端对需要传的中文参数进行一次编码 URLEncoder.encode(传参,“utf-8”);

后台需要再次解码：URLDecoder.decode(接收的参数,“utf-8”);

@RequestMapping(value = "outPutExcel", method = RequestMethod.GET) @ResponseBody public void outPutExcel( HttpServletResponse response,String officeid, String sonid,String nameorphone,String beginTime, String endTime,String option) { 		String nString = ""; 		try { 			if (nameorphone != null && nameorphone != "") { 			//对前端传的参数解码 				 nString = URLDecoder.decode(nameorphone,"UTF-8"); 			} 		} catch (UnsupportedEncodingException e2) { 			// TODO Auto-generated catch block 			e2.printStackTrace(); 		} 		response.reset(); 		//设置浏览器下载的格式，并以当前时间的毫秒数命名 		response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls"); 		response.setContentType("application/msexcel"); 		List<PurchaseSum> list = purchaseService.selectPCSum(officeid, sonid, nString, beginTime, endTime, option); 		if (list == null && list.isEmpty()) { 			throw new NullPointerException("导出数据源为空"); 		} 		HSSFWorkbook wb = new HSSFWorkbook(); 		HSSFSheet sheet = wb.createSheet("sheet0"); 		HSSFRow rows; 		HSSFCell cells; 		//设置表格第一行的列名 		// 获得表格第一行 		rows = sheet.createRow(0); 		// 根据需要给第一行每一列设置标题 		cells = rows.createCell(0); 		cells.setCellValue("客户姓名");  		cells = rows.createCell(1); 		cells.setCellValue("客户电话");  		cells = rows.createCell(2); 		cells.setCellValue("下单日期");  		cells = rows.createCell(3); 		cells.setCellValue("订单号");  		cells = rows.createCell(4); 		cells.setCellValue("所属分公司");  		cells = rows.createCell(5); 		cells.setCellValue("签单人");  		cells = rows.createCell(6); 		cells.setCellValue("品名");  		cells = rows.createCell(7); 		cells.setCellValue("型号");  		cells = rows.createCell(8); 		cells.setCellValue("颜色");  		cells = rows.createCell(9); 		cells.setCellValue("尺寸");  		cells = rows.createCell(10); 		cells.setCellValue("材质");  		cells = rows.createCell(11); 		cells.setCellValue("已采购数量(件)"); 		 		cells = rows.createCell(12); 		cells.setCellValue("采购单价"); 		 		cells = rows.createCell(13); 		cells.setCellValue("采购总价"); 		 		cells = rows.createCell(14); 		cells.setCellValue("已出库(件)"); 		//循环数据库查出来的数据集，对应每一列赋值 		//此处list.size()本不应该-1，因为同事在list集合里追加了另一条数据，导致报错故将其去除 		for (int i = 0; i < list.size()-1; i++) { 			rows = sheet.createRow(i + 1); 			 			cells = rows.createCell(0); 			cells.setCellValue(list.get(i).getCustomerName());  			cells = rows.createCell(1); 			cells.setCellValue(list.get(i).getPhone()); 			//对日期格式进行转换 			cells = rows.createCell(2); 			String dateString  = list.get(i).getPlaceOrderTime().toString(); 			Date date = null; 			try { 				date = new SimpleDateFormat("EEE MMM dd HH:mm:ss Z yyyy", Locale.UK).parse(dateString); 			} catch (ParseException e1) { 				// TODO Auto-generated catch block 				e1.printStackTrace(); 			} 			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); 			cells.setCellValue(sdf.format(date));  			cells = rows.createCell(3); 			cells.setCellValue(list.get(i).getOrderNumber());  			cells = rows.createCell(4); 			cells.setCellValue(list.get(i).getOfficeName());  			cells = rows.createCell(5); 			cells.setCellValue(list.get(i).getUsername());  			cells = rows.createCell(6); 			cells.setCellValue(list.get(i).getProductName());  			cells = rows.createCell(7); 			cells.setCellValue(list.get(i).getType());  			cells = rows.createCell(8); 			cells.setCellValue(list.get(i).getColor());  			cells = rows.createCell(9); 			cells.setCellValue(list.get(i).getSize());  			cells = rows.createCell(10); 			cells.setCellValue(list.get(i).getTexture());  			cells = rows.createCell(11); 			cells.setCellValue(list.get(i).getPurchasedNumber());  			cells = rows.createCell(12); 			cells.setCellValue(list.get(i).getPurchaseprice()); 			 			cells = rows.createCell(13); 			cells.setCellValue(list.get(i).getPurchasePriceSun()); 			 			cells = rows.createCell(14); 			cells.setCellValue(list.get(i).getOutlibraryNumber()); 			 		} 		try { 			OutputStream oStream = response.getOutputStream(); 			wb.write(oStream); 			oStream.flush(); 		} catch (FileNotFoundException e1) { 			// TODO Auto-generated catch block 			e1.printStackTrace(); 		} catch (IOException e) { 			// TODO Auto-generated catch block 			e.printStackTrace(); 		}  	}

以上就是Java实现数据库中查询出的数据转存成excel表的详细内容，更多内容请关注技术你好其它相关文章！