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

看了很多文章乱糟糟的,自己写了个简单暴力的一眼就懂,没有那么多花里胡哨,表格样式可以通过代码定义,我嫌麻烦

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

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

解决办法:

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

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

@RequestMapping(value = "outPutExcel", method = RequestMethod.GET)@ResponseBodypublic 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表的详细内容,更多请关注龙方网络其它相关文章!

郑重声明:本文版权包含图片归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们(delete@yzlfxy.com)修改或删除,多谢。

郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。

留言与评论(共有 0 条评论)
   
验证码: