JDBC连接数据库并将resultset中的文件输出为excel
使用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; }
用这个博客来记录下，下次使用起来就很快了。
浙公网安备 33010602011771号