dbutils的导出下载功能（excel）

来源：互联网发布：mac软件可以移动吗? 编辑：程序博客网时间：2024/06/05 11:51

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

1.

<input type="button" name="button"  onclick="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和LINUXMap<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