使用jdbc来连接具体的数据库,下面是具体的代码示例
private void startJob(String merchantNo, Date date) throws Exception { Connection con = null; Statement statement = null; Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String conUrl = "jdbc:sqlserver://188.40.1.189\MSSQLSERVER2;DatabaseName=TransactionBase;user=app;password=sf@tlgd20!7"; log.info("数据库连接URL: " + conUrl); con = DriverManager.getConnection(conUrl); statement = con.createStatement(); String tbName = "Trxrecord_T1-"+df.format(date); String sql = "select t.[交易流水号],t.[商户号],m.[商户名称],t.[终端号]," + "s.[门店名称],t.[交易时间],t.[清算日期],t.[交易类型]," + "t.[交易卡号],t.[卡类别],t.[发卡机构代码],t.[交易初始金额]," + "t.[交易金额],t.[商户手续费],t.[交易状态],t.[订单号]," + "t.[失败原因],t.[父交易流水号],t.[交易备注]" + " from ["+tbName+"] t" +" left join Merrecord m on m.[商户号] = t.[商户号]" +" left join Subbranch s on s.[商户号] = t.[商户号] and s.[门店编号] = t.[门店号]" +" where t.[交易类型] in ('银联扫码支付','银联扫码撤销','银联扫码退货')" +" and m.[父商户号] = '"+merchantNo+"'" +" and left(t.[交易时间],10)='"+format.format(date)+"'" +" and t.[产品名称] = '网上收银'" + " order by t.[交易时间]"; ResultSet resultSet = statement.executeQuery(sql); String path = PropertiesUtil.get("FILE_PATH")+merchantNo+"record"+format.format(date) +".xlsx"; log.info("数据存放路径为:"+path); log.info("开始导出为excel"); createExcel(resultSet,path,format.format(date)); log.info("导出为excel成功"); String subject =format.format(date)+ "商户号为"+merchantNo+"的交易明细"; log.info("开始发送邮件"); MailSender.send(subject, path); log.info("邮件发送成功"); }
将文件输出为具体的文件夹位置
private void createExcel(ResultSet resultSet,String path,String date) throws Exception{ List<Map<String,Object>> list = downToMap(resultSet); ExportExcel<Map<String, Object>> ex = new ExportExcel<Map<String, Object>>(); String[] headers = { "交易单号", "商户号", "商户名称", "门店名称", "终端号","交易时间", "清算日期", "交易类型","交易账号", "卡类型","所属银行","原始金额","交易金额","手续费","处理状态","订单号","原因","原交易单号","备注"}; String[] keys = {"tradeNo","merchantNo","merchantName","subbranchName","terminalNo", "tradeTime","settleTime","tradeType","accountNo","accountType", "bankCode","amount","tradeAmount","fee","tradeStatus", "orderNo","errMsg","fOrderNo","remark"}; File file = new File(path); OutputStream out = new FileOutputStream(file); ex.exportExcel(headers, keys,list, out,"yyyy-MM-dd"); out.close(); }
将result中的文件导出为excel
@SuppressWarnings("unchecked") public void exportExcel(String title, String[] headers, String[] keys, Collection<T> dataset, OutputStream out, String pattern) { // 声明一个工作薄 SXSSFWorkbook workbook = new SXSSFWorkbook(); // 生成一个表格 Sheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(20); //设置表格默认行高为15 sheet.setDefaultRowHeightInPoints(16); // 生成一个样式 CellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); style.setBorderTop(XSSFCellStyle.BORDER_THIN); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 生成一个字体 Font font = workbook.createFont(); font.setFontHeightInPoints((short) 14); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.BLUE.index); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 CellStyle style2 = workbook.createCellStyle(); style2.setBorderBottom(XSSFCellStyle.BORDER_THIN); style2.setBorderLeft(XSSFCellStyle.BORDER_THIN); style2.setBorderRight(XSSFCellStyle.BORDER_THIN); style2.setBorderTop(XSSFCellStyle.BORDER_THIN); style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 Font font2 = workbook.createFont(); font2.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 声明一个画图的顶级管理器 Drawing patriarch = sheet.createDrawingPatriarch(); // 定义注释的大小和位置,详见文档 Comment comment = patriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // 设置注释内容 comment.setString(new XSSFRichTextString("可以在POI中添加注释!")); // 产生表格标题行 Row row = sheet.createRow(0); for (short i = 0; i < headers.length; i++) { Cell cell = row.createCell(i); cell.setCellStyle(style); XSSFRichTextString text = new XSSFRichTextString(headers[i]); cell.setCellValue(text); } // 遍历集合数据,产生数据行 Iterator<T> it = dataset.iterator(); int index = 0; Font font3 = workbook.createFont(); font3.setColor(HSSFColor.BLACK.index); while (it.hasNext()) { index++; row = sheet.createRow(index); Map<String, Object> map = (Map<String, Object>) it.next(); for (short i = 0; i < keys.length; i++) { Cell cell = row.createCell(i); cell.setCellStyle(style2); try { Object value = map.get(keys[i]); // 判断值的类型后进行强制类型转换 String textValue = null; if (value instanceof Date) { Date date = (Date) value; SimpleDateFormat sdf = new SimpleDateFormat(pattern); textValue = sdf.format(date); XSSFRichTextString richString = new XSSFRichTextString( textValue); richString.applyFont(font3); cell.setCellValue(richString); } else if (value instanceof byte[]) { // 有图片时,设置行高为60px; row.setHeightInPoints(60); // 设置图片所在列宽度为80px,注意这里单位的一个换算 sheet.setColumnWidth(i, (short) (35.7 * 80)); // sheet.autoSizeColumn(i); byte[] bsValue = (byte[]) value; XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6, index); anchor.setAnchorType(2); patriarch.createPicture(anchor, workbook.addPicture( bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG)); } else { // 其它数据类型都当作字符串简单处理 if (null != value) textValue = value.toString(); else textValue = ""; // 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 if (textValue != null) { Pattern p = Pattern.compile("^\d+(\.\d+)?$"); Pattern p1 = Pattern.compile("^\d+\.\d+$"); Matcher matcher = p.matcher(textValue); Matcher matcher1 = p1.matcher(textValue); if (matcher.matches()||matcher1.matches()) { // 是数字当作double处理 String findValues = matcher.group(); if(findValues.length() < 10){ cell.getCellStyle().setFont(font3); cell.setCellValue(Double.parseDouble(textValue)); }else{ short text1 = 0x31; style2.setDataFormat(text1); XSSFRichTextString richString = new XSSFRichTextString( textValue); richString.applyFont(font3); cell.setCellValue(richString); } }else{ XSSFRichTextString richString = new XSSFRichTextString( textValue); richString.applyFont(font3); cell.setCellValue(richString); } } } } catch (SecurityException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } finally { // 清理资源 } } // T t = (T) it.next(); // // 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 // Field[] fields = t.getClass().getDeclaredFields(); // for (short i = 0; i < fields.length; i++) // {} } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); } }
关键语句list
List<Map<String,Object>> list = downToMap(resultSet);
使用downToMap将resultset转换成list
private List<Map<String, Object>> downToMap(ResultSet resultSet) throws Exception { List<Map<String,Object >> list = new ArrayList<Map<String, Object>>(); while(resultSet.next()){ Map<String,Object> map = new HashMap<String,Object>(); map.put("tradeNo",resultSet.getString(1)); map.put("merchantNo",resultSet.getString(2)); map.put("merchantName",resultSet.getString(3)); map.put("terminalNo",resultSet.getString(4)); map.put("subbranchName",resultSet.getString(5)); map.put("tradeTime",resultSet.getString(6)); map.put("settleTime",resultSet.getString(7)); map.put("tradeType",resultSet.getString(8)); map.put("accountNo",resultSet.getString(9).equals("0")? "":resultSet.getString(9).replaceAll("(\d{6})\d+(\w{4})","$1*****$2")); map.put("accountType",resultSet.getString(10)); map.put("bankCode",resultSet.getString(11)); map.put("amount",resultSet.getString(12)); map.put("tradeAmount",resultSet.getString(13)); map.put("fee",resultSet.getString(14)); map.put("tradeStatus",resultSet.getString(15)); map.put("orderNo",resultSet.getString(16)); map.put("errMsg",resultSet.getString(17)); map.put("fOrderNo",resultSet.getString(18).equals("0")? "":resultSet.getString(18)); map.put("remark",resultSet.getString(19)); list.add(map); } return list; }
用这个博客来记录下,下次使用起来就很快了。