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(); } }
上一篇:
IDEA上Java项目控制台中文乱码