Java,数据库中的数据导入到Excel

时间:2022-10-20 15:06:23

private static void executeMethod(JobExecutionContext arg0) throws Exception{
try {
TContrastService tContrastService =SpringContextHelper.getBean("tContrastService", TContrastService.class);

String data="2016";
Integer shopId=135;

List<TContrast> list=tContrastService.queryByShopId("2016", 135, 0);

WritableWorkbook wwb = null;

// 创建可写入的Excel工作簿
String fileName = "D://"+data+"_"+shopId+".xls";
File file=new File(fileName);
if (!file.exists()) {
file.createNewFile();
}
//以fileName为文件名来创建一个Workbook
wwb = Workbook.createWorkbook(file);

// 创建工作表
WritableSheet ws = wwb.createSheet("Test Shee 1", 0);

//要插入到的Excel表格的行号,默认从0开始
Label ID= new Label(0, 0, "编号(id)");//表示第
Label PAY_TYPE= new Label(1, 0, "支付类型");
Label PASS_ID= new Label(2, 0, "通道id");
Label PASS_NO= new Label(3, 0, "通道订单号");
Label PASS_AMOUNT= new Label(4, 0, "通道金额");
Label SYS_ID= new Label(5, 0, "系统订单id");
Label SYS_NO= new Label(6, 0, "系统订单号");
Label SYS_AMOUNT= new Label(7, 0, "系统金额");
Label ORDER_TYPE= new Label(8, 0, "订单类型");
Label ORDER_STATE= new Label(9, 0, "订单状态");
Label ORDER_TIME= new Label(10, 0, "交易时间");
Label DIF_AMOUNT= new Label(11, 0, "差异金额");
Label RATE= new Label(12, 0, "手续费");
Label RATE_AMOUNT= new Label(13, 0, "手续费率金额");
Label RECE_AMOUNT= new Label(14, 0, "实收金额");

ws.addCell(ID);
ws.addCell(PAY_TYPE);
ws.addCell(PASS_ID);
ws.addCell(PASS_NO);
ws.addCell(PASS_AMOUNT);
ws.addCell(SYS_ID);
ws.addCell(SYS_NO);
ws.addCell(SYS_AMOUNT);
ws.addCell(ORDER_TYPE);
ws.addCell(ORDER_STATE);
ws.addCell(ORDER_TIME);
ws.addCell(DIF_AMOUNT);
ws.addCell(RATE);
ws.addCell(RATE_AMOUNT);
ws.addCell(RECE_AMOUNT);
for (int i = 0; i < list.size(); i++) {
TContrast t =list.get(i);
Label labelID= new Label(0, i+1, t.getID()+"");//表示第
ws.addCell(labelID);
Label labelPAY_TYPE=null;
if(t.getPayType().intValue()==6){
labelPAY_TYPE= new Label(1, i+1, "支付宝");
}else{
labelPAY_TYPE= new Label(1, i+1, "微信");
}
ws.addCell(labelPAY_TYPE);
Label labelPASS_ID= new Label(2, i+1, t.getPassId());
ws.addCell(labelPASS_ID);
Label labelPASS_NO= new Label(3, i+1, t.getPassNo());
ws.addCell(labelPASS_NO);
Label labelPASS_AMOUNT= new Label(4, i+1, t.getPassAmount()+"");
ws.addCell(labelPASS_AMOUNT);
Label labelSYS_ID= new Label(5, i+1, t.getSysId());
ws.addCell(labelSYS_ID);
Label labelSYS_NO= new Label(6, i+1, t.getSysNo());
ws.addCell(labelSYS_NO);
Label labelSYS_AMOUNT= null;
if(t.getSysAmount()!=null){
labelSYS_AMOUNT= new Label(7, i+1, t.getSysAmount()+"");
}else{
labelSYS_AMOUNT= new Label(7, i+1,"0");
}

ws.addCell(labelSYS_AMOUNT);
Label labelORDER_STATE=null;
switch (t.getOrderState().intValue()) {//0正常1未对账2已对帐3已调账
case 0:
labelORDER_STATE= new Label(8, i+1, "正常");
break;
case 1:
labelORDER_STATE= new Label(8, i+1, "未对账");
break;
case 2:
labelORDER_STATE= new Label(8, i+1, "已对帐");
break;
default:
labelORDER_STATE= new Label(8, i+1, "已调账");
break;
}
ws.addCell(labelORDER_STATE);
Label labelORDER_TYPE=null;
switch (t.getOrderType().intValue()) {//0正常1订单缺失2通道缺失3金额不正确
case 0:
labelORDER_TYPE= new Label(9, i+1, "正常");
break;
case 1:
labelORDER_TYPE= new Label(9, i+1, "订单缺失");
break;
case 2:
labelORDER_TYPE= new Label(9, i+1, "通道缺失");
break;
default:
labelORDER_TYPE= new Label(9, i+1, "金额不正确");
break;
}
ws.addCell(labelORDER_TYPE);
SimpleDateFormat dd = new SimpleDateFormat("yyyy-MM-dd HH:mm");
Label labelORDER_TIME= new Label(10, i+1, dd.format(t.getOrderTime()));
ws.addCell(labelORDER_TIME);
Label labelDIF_AMOUNT= new Label(11, i+1, t.getDifAmount()+"");
ws.addCell(labelDIF_AMOUNT);
Label labelRATE= new Label(12, i+1, t.getRATE());
ws.addCell(labelRATE);
Label labelRATE_AMOUNT= new Label(13, i+1, t.getRateAmount()+"");
ws.addCell(labelRATE_AMOUNT);
Label labelRECE_AMOUNT= new Label(14, i+1, t.getReceAmount()+"");
ws.addCell(labelRECE_AMOUNT);
}

//写进文档
wwb.write();
// 关闭Excel工作簿对象
wwb.close();


} catch (Exception e) {
e.printStackTrace();
}
}

 

 

jsp访问的时候只能用location.herf="www.baidu.com"

 

来自:http://www.cnblogs.com/zyw-205520/p/3762954.html