查询分页excele

/**
* 下载商户前一日的交易对账单
* @param dateTime
* @return
* @throws TtyException
*/
@Override
public void downloadMerchantBillsTask(Date beginDate,Date endDate) throws TtyException {
List<MemberMerchant> list = memberService.queryAllMerchants();
FileOutputStream fOut = null;
InputStream fin = null;
for(int i=0;i<list.size();i++){
MemberMerchant memberMerchant = list.get(i);
log.info("循环当前商户的登录账号为{}",memberMerchant.getLoginId());
String newBeginDate = "";
if(Utils.isNull(beginDate)){
newBeginDate = DateUtil.format(new Date(), DateUtil.YYYY_MM_DD, -1);//前一日的交易对账单
}else{
newBeginDate = DateUtil.format(beginDate,DateUtil.YYYY_MM_DD);
}
log.info("开始时间="+newBeginDate);
String fileName = memberMerchant.getLoginId();
//对账单结束时间
String newEndDate = "";
if(Utils.isNull(endDate)){
newEndDate = DateUtil.format(new Date(), DateUtil.YYYY_MM_DD, -1);//前一日的交易对账单
}else{
newEndDate = DateUtil.format(endDate,DateUtil.YYYY_MM_DD);
}
if (!StringUtils.isEmpty(newBeginDate) && !StringUtils.isEmpty(newEndDate)) {
fileName += "(" + newBeginDate + "-" + newEndDate + ")"+"_";
}
//模板路径
String templateUrl = Property.getProperty("url.downloadMerchantBills.template", "");
//导出到指定路径
String newfilePath = Property.getProperty("url.merchantBills.path");
//根据商户登陆帐户生成目录结构
String memberId = memberMerchant.getMemberId()+"/";
newfilePath +=memberId;
File dirFile = new File(newfilePath);
log.info("----newfilePath----:" + newfilePath);
if (!dirFile.exists()) {
log.info("创建文件夹:" + newfilePath);
dirFile.mkdirs();
}

//存放对帐单数据的文件路径
newfilePath +=fileName;
//查询当前商户前一交易日的对账单数据参数
Map<String, Object> data = new HashMap<String, Object>();
data.put("beginDate", newBeginDate);
data.put("endDate", newEndDate);
data.put("mchLoginId", memberMerchant.getLoginId());

//处理大数据量的导出
String tempFilePath="";
//查询当前商户的总对帐数量
int totalRecord = this.queryMerchantBillsCountByLoginId(data);
//根据传入的分页开始与结束值查询数据
int startPageNo=0,pageSize=0,pagesTotal = 0,size=2,endPageNo=0;
if(totalRecord >0){
//此商户存在对帐单数据
startPageNo = 1;
pagesTotal=(totalRecord-1)/size+1;
}
for(int j=0;j<pagesTotal;j++){
/*startPageNo = j*size+1;
endPageNo = (j+1)*size;*/
//导出多个excele文件
IPage<SettlementOrder> pList = orderQueryService.querySettlementListByMchLoginId(data, j+1, size);
if (null != pList && null != pList.getRows()) {
List<SettlementOrder> list2 = (List<SettlementOrder>) pList.getRows();
data.put("items", list2);
}
try {
tempFilePath = newfilePath +j+".xls";
//模板文件从服务器下载方式
XLSTransformer transformer = new XLSTransformer();
URL inUrl = new URL(templateUrl);
URLConnection connection = inUrl.openConnection();
fin = connection.getInputStream();
fOut = new FileOutputStream(tempFilePath);
transformer = new XLSTransformer();
Workbook workbook = transformer.transformXLS(fin, data);
workbook.write(fOut);
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (fOut != null) {
fOut.flush();
fOut.close();
}
if (fin != null) {
fin.close();
}
} catch (IOException ie) {
log.error("定时任务报表导出异常", ie);
}
}
}

}

}


/**
* 下载商户前一日的交易对账单
* @param dateTime
* @return
* @throws TtyException
*/
@Autowired
@Override
public void downloadMerchantBillsTask(Date beginDate,Date endDate) throws TtyException {
List<MemberMerchant> list = memberService.queryAllMerchants();
FileOutputStream fOut = null;
InputStream fin = null;
for(int i=0;i<list.size();i++){
MemberMerchant memberMerchant = list.get(i);
log.info("循环当前商户的登录账号为{}",memberMerchant.getLoginId());
String newBeginDate = "";
if(Utils.isNull(beginDate)){
newBeginDate = DateUtil.format(new Date(), DateUtil.YYYY_MM_DD, -1);//前一日的交易对账单
}else{
newBeginDate = DateUtil.format(beginDate,DateUtil.YYYY_MM_DD);
}
log.info("开始时间="+newBeginDate);
String fileName = memberMerchant.getLoginId();
//对账单结束时间
String newEndDate = "";
if(Utils.isNull(endDate)){
newEndDate = DateUtil.format(new Date(), DateUtil.YYYY_MM_DD, -1);//前一日的交易对账单
}else{
newEndDate = DateUtil.format(endDate,DateUtil.YYYY_MM_DD);
}
if (!StringUtils.isEmpty(newBeginDate) && !StringUtils.isEmpty(newEndDate)) {
fileName += "(" + newBeginDate + "-" + newEndDate + ")";
}
//模板路径
String templateUrl = Property.getProperty("url.downloadMerchantBills.template", "");
//导出到指定路径
String newfilePath = Property.getProperty("url.merchantBills.path");
//根据商户登陆帐户生成目录结构
String memberId = memberMerchant.getMemberId()+"/";
newfilePath +=memberId;
File dirFile = new File(newfilePath);
log.info("----newfilePath----:" + newfilePath);
if (!dirFile.exists()) {
log.info("创建文件夹:" + newfilePath);
dirFile.mkdirs();
}

//存放对帐单数据的文件路径
newfilePath +=fileName;
//查询当前商户前一交易日的对账单数据参数
Map<String, Object> data = new HashMap<String, Object>();
data.put("beginDate", newBeginDate);
data.put("endDate", newEndDate);
data.put("mchLoginId", memberMerchant.getLoginId());

String tempFilePath="";
int totalRecord = this.queryMerchantBillsCountByLoginId(data);
int pagesTotal = 0,size=2;
if(totalRecord >0){
//此商户存在对帐单数据
pagesTotal=(totalRecord-1)/size+1;
for(int j=0;j<pagesTotal;j++){
//循环导出多个excele文件
log.info("正在导出商户编号为:"+memberMerchant.getMemberId()+"第"+j+"个excel文件");
IPage<SettlementOrder> pList = orderQueryService.querySettlementListByMchLoginId(data, j+1, size);
if (null != pList && null != pList.getRows()) {
List<SettlementOrder> list2 = (List<SettlementOrder>) pList.getRows();
data.put("items", list2);
}
tempFilePath = newfilePath +"_" +j+".xls";
ExportController.reportExp(tempFilePath,templateUrl,data);

/*try {
tempFilePath = newfilePath +"_" +j+".xls";
//模板文件从服务器下载方式
XLSTransformer transformer = new XLSTransformer();
URL inUrl = new URL(templateUrl);
URLConnection connection = inUrl.openConnection();
fin = connection.getInputStream();
fOut = new FileOutputStream(tempFilePath);
transformer = new XLSTransformer();
Workbook workbook = transformer.transformXLS(fin, data);
workbook.write(fOut);
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (fOut != null) {
fOut.flush();
fOut.close();
}
if (fin != null) {
fin.close();
}
} catch (IOException ie) {
log.error("定时任务报表导出异常", ie);
}
}*/
}
}else{
//没有对帐单数据信息.
List<SettlementOrder> list3 = new ArrayList<SettlementOrder>();
data.put("items", list3);
tempFilePath = newfilePath +".xls";
ExportController.reportExp(tempFilePath,templateUrl,data);
/*try {
List<SettlementOrder> list3 = new ArrayList<SettlementOrder>();
data.put("items", list3);
tempFilePath = newfilePath +".xls";
//模板文件从服务器下载方式
XLSTransformer transformer = new XLSTransformer();
URL inUrl = new URL(templateUrl);
URLConnection connection = inUrl.openConnection();
fin = connection.getInputStream();
fOut = new FileOutputStream(tempFilePath);
transformer = new XLSTransformer();
Workbook workbook = transformer.transformXLS(fin, data);
workbook.write(fOut);
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (fOut != null) {
fOut.flush();
fOut.close();
}
if (fin != null) {
fin.close();
}
} catch (IOException ie) {
log.error("定时任务报表导出异常", ie);
}
}*/
}


}

}


/**
* Copyright (c) 2011-2015 All Rights Reserved.
*/
package com.kame.micropay.order.service.utils;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;
import java.net.URLConnection;
import java.util.Map;

import net.sf.jxls.transformer.XLSTransformer;

import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;

/**
*
* @author Administrator
*
*/
@Controller
public abstract class ExportController {

private static Logger log = LoggerFactory.getLogger(ExportController.class);

/**
* 输出报表
* @param tempFilePath
* @param templateUrl
* @param data
*/
public static void reportExp(String tempFilePath, String templateUrl, Map<String, Object> data) {
OutputStream fOut = null;
InputStream fin = null;
try {
URL url = new URL(templateUrl);
URLConnection connection = url.openConnection();
fin = connection.getInputStream();
XLSTransformer transformer = new XLSTransformer();
fin = connection.getInputStream();
fOut = new FileOutputStream(tempFilePath);
transformer = new XLSTransformer();
Workbook workbook = transformer.transformXLS(fin, data);
workbook.write(fOut);

} catch (Exception e) {
log.error("报表导出异常", e);
} finally {
try {
if (fOut != null) {
fOut.flush();
fOut.close();
}
if (fin != null) {
fin.close();
}
} catch (IOException ie) {
log.error("报表导出异常", ie);
}
}
}
}