dbutils的导出下载功能（excel）

最新推荐文章于 2023-12-03 20:27:50 发布

一点寒芒先至

最新推荐文章于 2023-12-03 20:27:50 发布

阅读量1.5k

点赞数 1

分类专栏： Java

本文链接：https://blog.csdn.net/q975583865/article/details/70229070

版权

Java 专栏收录该内容

19 篇文章 2 订阅

订阅专栏

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

1.

<input type="button" name="button"  οnclick="cashDetailExport();"class="btn btn82 btn_export" value="导出">


==============================================================================================


function cashDetailExport() {
	$("#cashDetailQuery").attr("action",ctx()+'/cash/cashDetailExport');
    $("#cashDetailQuery").submit();
    $("#cashDetailQuery").attr("action", ctx()+'/cash/cashDetailQuery');
}


===============================================================================================

@RequestMapping("/cashDetailExport")
	public void cashDetailExport(@RequestParam Map<String, String> params, HttpServletResponse response, HttpServletRequest request) throws Exception {
		List<Map<String, Object>> list=cashRequestService.cashExportQuery(params);
		if(list.size()>0){
			exportCashExcel(response, request,list);
		} else {
			// response.sendRedirect("/cash/cashDetailQuery");
			render(HTML_TYPE, "<html><script>alert('没有查询到符合条件的数据，不予导出！');"
					+ "window.location='cashDetailQuery';</script></html>", response);
		}
	}		

===============================================================================================
/**
	 * 导出结算信息（这个Query_Param和cashDetailQuery里的查询一起封装，见文章最下的图）
	 * @param params
	 * @return
	 */
	public List<Map<String, Object>> cashExportQuery(Map<String, String> params) throws Exception{
		List<Object> list = new ArrayList<Object>();
		String sql = Query_Param(params, list);		
		return dao.find(sql, list.toArray());
	}



===============================================================================================


private String Query_Param(Map<String, String> params,List<Object> list){
		StringBuffer sb = new StringBuffer();
		
		if (StringUtils.isNotEmpty(params.get("request_time_begin"))) {
			String request_time_begin = params.get("request_time_begin");
			sb.append(" and apd.settle_time >=to_date(?,'yyyy-mm-dd HH24:MI:SS') ");
			list.add(request_time_begin);
		}
		if(StringUtils.isNotEmpty(params.get("request_time_end"))){
			String request_time_end=params.get("request_time_end");
			sb.append(" and apd.settle_time <=todate(?,'yyyy-mm-dd HH24：MI:SS') ");
			list.add(request_time_end);
		}
		if(StringUtils.isNotEmpty(params.get("cash_status")) && !params.get("cash_status").equals("-1")){
			String cash_status=params.get("cash_status");
			sb.append("and apd.status=?");
			list.add(cash_status);
		}
		
		
		String sql ="select apd.*, dic.dic_item_name statusName from agent_pay_detail apd "+
  " left join (select sd.dic_item_code, sd.dic_item_name from sys_dic_item sd where sd.sys_dic_code = 'ACSETTLE_STATUS') dic "+
    " on apd.status = dic.dic_item_code"+sb.toString();
				return sql;
	}



===============================================================================================
	
private String merExcelPath = "merExcel";
private void exportCashExcel(HttpServletResponse response,
			HttpServletRequest request, List<Map<String, Object>> list
			) throws Exception {
		SimpleDateFormat sm = new SimpleDateFormat("yyyyMMddHHmmss");
		String date = sm.format(new Date());
		String parentpath = request.getSession().getServletContext()
				.getRealPath("/")
				+ File.separator + "uploads" + File.separator + cashExcelPath;
		File file = new File(parentpath);
		if (!file.exists() || file.isFile()) {
			file.mkdirs();
		}
		String fiename = "结算信息_" + date + ".xls";
		File result = ExcelUtils.getCashExcel(parentpath, fiename, list);
		FileUtils.Download(result, response);                         //下载
	}


===============================================================================================


	/**
	 * 将结算信息写入Excel（如果有合并项，可以在sql中处理）
	 * @param parentPath
	 * @param filename
	 * @param list
	 * @return
	 * @throws Exception
	 */
	public static File getCashExcel(String parentPath, String filename,
			List<Map<String, Object>> list) throws Exception {
		 File file = new File(parentPath + File.separator + filename);//创建一个文件，兼容WINDOWS和LINUX
			Map<String, String> metadata = new LinkedHashMap<>();
			metadata.put("代理商编号,5000", "AGENT_NO");
			metadata.put("状态,5000", "statusName");
			metadata.put("结算金额,3500", "SETTLE_AMOUNT");
			metadata.put("结算费率,3000", "SETTLE_RATE");
			metadata.put("结算手续费,4000", "SETTLE_FEE");
			metadata.put("结算时间,5000", "SETTLE_TIME");
			metadata.put("创建时间,5000", "CREATE_TIME");
			metadata.put("开户卡号,7000", "ACCOUNT_NO");
			metadata.put("提现金额,5000", "CASH_AMOUNT");
			FileOutputStream os = new FileOutputStream(file);
			generateExcel(metadata, list).write(os);
			os.close();
			return file;
	}

2.

/**
	 * 生成excel
	 * @param metadata 所需要的元数据的LinkedHashMap，格式如下：{"col1Title.col1Width": "field1", "col2Title.col2Width": "field2",...}
	 * @param data 需要用excel展示的数据
	 * @return
	 */
	private static HSSFWorkbook generateExcel(Map<String, String> metadata, List<Map<String, Object>> data) {
		int rowIndex = 0, colIndex = 0;
		HSSFWorkbook wb = new HSSFWorkbook();// 创建一个Excel文件 
		HSSFSheet sheet = wb.createSheet();//创建一个Excel的Sheet（表格）
		// 普通单元格样式
		HSSFCellStyle commCellStyle = wb.createCellStyle();// 生成一个样式
		commCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中 
		// 金额单元格样式
		HSSFCellStyle amountCellStyle = wb.createCellStyle();
		amountCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
		amountCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		// 设置第一行列头
		HSSFRow row = sheet.createRow(rowIndex ++);//创建第一行  
		for (String aCol : metadata.keySet()) {
			String[] titleCfg = aCol.split(",");
			HSSFCell cell = row.createCell(colIndex);// 创建第一列 
			cell.setCellStyle(commCellStyle);
			cell.setCellValue(titleCfg[0]);
			sheet.setColumnWidth(colIndex++, Integer.valueOf(titleCfg[1]));//设置列宽
		}
		// 填充数据
		for (Map<String, Object> aRow : data) {
			row = sheet.createRow(rowIndex ++);
			colIndex = 0;
			for (String field : metadata.values()) {
				HSSFCellStyle cellStyle = commCellStyle;
				HSSFCell cell = row.createCell(colIndex++);
				Object val = aRow.get(field);
				if (null == val) {
					cell.setCellValue("--");
				} else if (val instanceof Date) {
					cell.setCellValue(DateFormatUtils.format((Date) val, "yyyy-MM-dd HH:mm:ss"));
				} else if (val instanceof Number) {
					cell.setCellValue(Double.valueOf(val.toString()));
					cellStyle = amountCellStyle;
				} else {
					cell.setCellValue(val.toString());
				}
				cell.setCellStyle(cellStyle);
			}
		}
		return wb;
	}

3.FileUtils.Download

/**
	 * 下载指定文件
	 * 
	 * @param result
	 * @param response
	 * @throws IOException
	 */
	public static void Download(File result, HttpServletResponse response)
			throws IOException {
		InputStream fis = new BufferedInputStream(new FileInputStream(result));
		byte[] buffer = new byte[fis.available()];
		fis.read(buffer);
		fis.close();
		response.reset(); // 清空输出流
		response.setHeader("Content-disposition", "attachment;filename="
				+ new String(result.getName().getBytes("GBK"), "ISO8859-1"));
		response.setContentType("application/msexcel;charset=UTF-8");// 定义输出类型
		response.addHeader("Content-Length", "" + result.length());
		OutputStream toClient = new BufferedOutputStream(
				response.getOutputStream());
		response.setContentType("application/octet-stream");
		toClient.write(buffer);
		toClient.flush();
		toClient.close();
	}

一点寒芒先至

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
dbutils的导出下载功能（excel）

1.==============================================================================================function cashDetailExport() { $("#cashDetailQuery").attr("action",ctx()+'/cash/cashDetailExpo
复制链接

扫一扫