查询分页excele

tangkuo

浏览: 103144 次
性别:
来自: 深圳

最近访客更多访客>>

sky3063

JiefzzLon

qgk

835937470

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

/**
     * 下载商户前一日的交易对账单
     * @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);
            }
        }
    }
}

分享到：