使用excle导出数据的方法

最新推荐文章于 2022-05-15 18:28:33 发布

原创最新推荐文章于 2022-05-15 18:28:33 发布 · 515 阅读

0 ·

CC 4.0 BY-SA版权

java 专栏收录该内容

59 篇文章

订阅专栏

本文详细介绍了一种从系统导出数据至Excel的方法，包括数据处理、格式转换及响应设置等关键步骤。通过实例展示了如何使用Java处理大量交易记录，并将其格式化为Excel文件供下载。适用于希望了解数据导出流程的开发者。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、导出数据到excel中的实现方法

@RequestMapping(value = "/ordersExcel", method = RequestMethod.GET)
public void queryOrdersExcel(HttpServletRequest request, HttpServletResponse response,@AuthForHeader AuthContext context,
							 @RequestParam(value = "brokerId", required = false) Integer brokerId,
							 @RequestParam(value = "symbol", required = false) String symbol,
							 @RequestParam(value = "beginTime", required = false)  @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") String beginTime,
							 @RequestParam(value = "endTime", required = false)  @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") String endTime,
							 @RequestParam(value = "email", required = false) String email,
							 @RequestParam(value = "buyUid",required = false)Integer buyUid,
							 @RequestParam(value = "sellUid",required = false)Integer sellUid,
							 @RequestParam(value = "tid", required = false) String tid, @RequestParam(value = "pageNo", required = false) Integer pageNo,
							 @RequestParam(value = "pageSize", required = false) Integer pageSize) {
	try {

		// 汇总计算
		TradeCountResult tcr = tradeRecordService.countTradResult1(brokerId, symbol, beginTime, endTime, buyUid,sellUid, tid);
		List<TradeMatchResult> pageInfo = tradeService.queryResult1(brokerId, symbol, beginTime, endTime,buyUid,sellUid,tid);
		for(TradeMatchResult page :pageInfo){
			BigDecimal number=page.getNumber();
			BigDecimal price=page.getPrice();
			BigDecimal amount=number.multiply(price);
			//设置要获取到什么样的时间
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			//获取String类型的时间
			String createdate = sdf.format(page.getCreateTime());
		/*	List<TradeMatchResult> taxDTOList = bbcTaxService.queryForExportExcelByAdmin(queryCondition);*/
		List<Map<String, Object>> objects = new ArrayList<Map<String, Object>>();
		Map<String, Object> sheetData = new HashMap<String, Object>();
		Map<String,Object> map = new HashMap<String,Object>();
		map.put("pageList", pageInfo);
		map.put("tcr", tcr);
        map.put("total", pageInfo.size());
		sheetData.put("data",map);
		objects.add(sheetData);



	String com_path =this.getClass().getClassLoader().getResource("TradeResult.xlsx").getPath();// 获取该文件所在路径

		log.info("监听到失效的d:{}", file);
		FileInputStream is = new FileInputStream(file);
		String fileName = "明细.xlsx";

		Configuration config = new Configuration();
		config.setMetaInfoToken("\\\\");
		XLSTransformer transformer = new XLSTransformer(config);
		Workbook workbook =  transformer.transformXLS(is, sheetData);

		response.setContentType("application/ms-excel");
		response.setCharacterEncoding("UTF-8");
		String encodedFileName = null;
		// 如果是IE,通过URLEncoder对filename进行UTF8编码。而其他的浏览器（firefox、chrome、safari、opera），则要通过字节转换成ISO8859-1。
		if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) {
			encodedFileName = URLEncoder.encode(fileName, "UTF-8");
		} else {
			encodedFileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
		}
		response.setHeader("Content-Disposition", "attachment; filename=" + encodedFileName);//设置文件头编码方式和文件名
		OutputStream out=response.getOutputStream();
		workbook.write(out);
	} catch (Exception e) {
		response.setCharacterEncoding("utf8");
		response.setContentType("text/html");
/*		PrintWriter writer = response.getWriter();
		writer.println("出现异常");*/
	}

}

二、excel表格中写人需要的数据字段
在这里插入图片描述
这样一个导出功能就实现了，有什么问题都可以和我沟通