Java realizes the transfer of data queried from the database into an excel table

After reading a lot of messy articles, I wrote a simple and violent one that I can understand at a glance. There are not so many bells and whistles. The table style can be defined through code. I find it troublesome

Note that if the date format is saved to the database in String type, it needs to be converted once. The direct export format is incorrect.

Because the excel table is exported using the get method to pass parameters, so if you need to export the data Use Chinese fuzzy query. At this time, when using get to pass parameters, Chinese garbled characters will appear

Solution:

The front end encodes the Chinese parameters that need to be passed URLEncoder.encode( Passing parameters, "utf-8");

The background needs to be decoded again: URLDecoder.decode(received parameters, "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 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();
		}

	}

The above is the detailed content of Java realizes the transfer of data queried from the database into an excel table. For more information, please follow other related articles on the PHP Chinese website!