JAVA实现EXCEL方法三-SXSSFWorkbook+模板

@Service
public class OfflineNonIssuerTransEventListener {
    private static final Logger logger = LoggerFactory.getLogger(OfflineNonIssuerTransEventListener.class);

    @Autowired
private ReportRecordsService reportRecordsService;
    @Autowired
private ReportExcutLogService reportExcutLogService;

    @Autowired
private JdbcTemplate jdbcTemplate;

   @Value("${report.file.baseDir}")
    private String baseFilePath;

    @Value("${report.file.baseDir.win}")
    private String baseFilePath_win;

    @Value("${report.details.pageSize}")
    private Integer pageSize;

    //listener实现
public void onApplicationEvent(OfflineNonIssuerTransEvent event) {

         Long issuerId=event.getIssuerId();
         Issuer issuer=event.getIssuer();
         String beginTime=event.getBeginTime();
         String endTime=event.getEndTime();
        String loginfo="";
        Integer reportRecordsId=event.getReportRecordsId();
        if (issuerId == null || beginTime == null || endTime == null) {
            return;
        }
        ReportRecords reportRecords =reportRecordsService.geReportRecordsById(reportRecordsId);
        reportRecordsService.markStartTime(reportRecordsId);
        SXSSFWorkbook wb = null;
        boolean hasData = false;
        StringBuffer sbf=new StringBuffer();
        if(System.getProperty("os.name").toLowerCase().startsWith("win")){
            baseFilePath=baseFilePath_win;
        }
        try {

            File template = ResourceUtils.getFile("classpath:templates/ibms-offlineNonIssuerTrans-details.xlsx");
            List<ExcelCellBean> excelCellList=SXSSFPOIUtils.getSheetFirstRowCell(template,1);
            List<Row> addRowList=SXSSFPOIUtils.getAddRows(template,1);
            wb = SXSSFPOIUtils.createWorkBook(template,1);
            String fileDirectory = baseFilePath + File.separator + reportRecords.getReportId();
//            String excelName = String.format("异地卡本地消费查询_%s（%s至%s）_%s.xlsx", issuer.getName(), beginTime,endTime,reportRecordsId);
//            String zipName = String.format("异地卡本地消费查询_%s（%s至%s）_%s.zip", issuer.getName(), beginTime,endTime,reportRecordsId);
String excelName = String.format("ydkbdxf_%s_%s-%s_%s.xlsx", issuer.getIssuerNumber(), beginTime,endTime,reportRecordsId);
            String zipName = String.format("ydkbdxf_%s_%s-%s_%s.zip", issuer.getIssuerNumber(), beginTime,endTime,reportRecordsId);
            String fileName = new String(excelName.getBytes(), "utf-8");
            zipName = new String(zipName.getBytes(), "utf-8");
            Sheet currentSheet = wb.getSheetAt(0);
            int rowNum =0;
            Row row ;
            //循环获取产生每页数据
List<DetailsTrans> resultList;
            boolean    hasOutLocalData=false;
            //循环获取产生每页数据
for (int pageNum=1 ; ; pageNum++) {
                Pagination page = new Pagination<>(DetailsSqlProvider.buildQueryIbmsOutAtLocalTrans(), pageNum,pageSize, jdbcTemplate, 0, DetailsTrans.class,
                        issuerId,issuer.getIssuerNumber(),beginTime+" 00:00:00" , endTime +" 23:59:59");
                resultList = page.getResultList();
                if(pageNum == 1) {
                    sbf.append("sql:").append(DetailsSqlProvider.buildQueryIbmsOutAtLocalTrans()).append("para:").append(issuerId).append(",")
                            .append(issuerId).append(",").append(issuer.getIssuerNumber()).append(",").append(beginTime+" 00:00::00").append(",")
                            .append(endTime +" 23:59:59");
                }
                if(pageNum == 1 && resultList.size()>0){
                    hasOutLocalData= true;
                }
                if(resultList.size()==0){
                    //合计行
if(hasOutLocalData){
                       // ExcelUtils.sumRow(currentSheet,wb,rowNum,"N");
}
                    break;
                }
                // 明细行
for (DetailsTrans summary : resultList ) {

                    SXSSFPOIUtils.creatRow(currentSheet,excelCellList, rowNum + 1);
                    row = currentSheet.getRow(rowNum + 1);
                    inputOutAtLocal(row, summary);
                    rowNum++;
                }

                resultList.clear();
            }
            SXSSFPOIUtils.addRow(currentSheet,addRowList.subList(1,addRowList.size()));
            SXSSFPOIUtils.sumRow(currentSheet,wb,rowNum,"N");
            // 输出Excel
String absolutePath = ExcelUtils.writeExcel(wb, fileDirectory,fileName);
            //转换zip
List<String> fileNames =new ArrayList<>();
            fileNames.add(absolutePath);
            boolean result = ZipCompressorUtil.compressFiles(fileNames, fileDirectory+ File.separator+zipName);
            if (!result) {
                loginfo = "打包文件失败";
                reportRecordsService.markEndTime(reportRecordsId,4);
                return;
            }
                // 添加或者更新报表记录
reportRecordsService.markFilePath(reportRecordsId,zipName,fileDirectory+ File.separator+zipName);
                logger.info("****** ibms 异常消费查询报表完成，交易时间: {}至{}", beginTime,endTime);
            loginfo="执行成功";
            reportRecordsService.markEndTime(reportRecordsId,3);
        } catch (Exception ex) {
            reportRecordsService.markEndTime(reportRecordsId,4);
            OutputStream os = new ByteArrayOutputStream();
            ex.printStackTrace(new PrintStream(os));
            loginfo ="执行失败："+ os.toString();
            throw new InternalException(ex);
        } finally {
            if (wb != null) {
                try {
                    wb.close();
                } catch (Exception ex) {
                }
            }
            reportExcutLogService.saveReportExcutLog(reportRecordsId,loginfo,sbf.toString());
        }
    }

    /**
     *  imbs 异地卡本地交易明细
     */
private void inputOutAtLocal(final Row row, final DetailsTrans summary) {
        Cell cell = row.getCell(0);
        cell.setCellValue(summary.getSn());

        cell = row.getCell(1);
        cell.setCellValue(summary.getAcqName());
        cell = row.getCell(2);
        cell.setCellValue(summary.getIssuerName());
        cell = row.getCell(3);
        cell.setCellValue(summary.getBusCom());
        cell = row.getCell(4);
        cell.setCellValue(summary.getBusTeam());
        cell = row.getCell(5);
        cell.setCellValue(summary.getBusLine());
        cell = row.getCell(6);
        cell.setCellValue(summary.getBusNo());
        cell = row.getCell(7);
        cell.setCellValue(summary.getTermNo());
        cell = row.getCell(8);
        cell.setCellValue(summary.getTermTransNo());
        cell = row.getCell(9);
        cell.setCellValue(summary.getCardType());
        cell = row.getCell(10);
        cell.setCellValue(summary.getCardNo());
        cell = row.getCell(11);
        cell.setCellValue(summary.getTransNo());
        cell = row.getCell(12);
        cell.setCellValue(summary.getBeforeAmount());
        cell = row.getCell(13);
        cell.setCellValue(summary.getAmount());
        cell = row.getCell(14);
        cell.setCellValue(summary.getAfterAmount());
        cell = row.getCell(15);
        cell.setCellValue(summary.getTransTime());
        cell = row.getCell(16);
        cell.setCellValue(summary.getClearTime());
        cell = row.getCell(17);
        cell.setCellValue(summary.getStatus());
        cell = row.getCell(18);
        cell.setCellValue(summary.getRecordType());
        cell = row.getCell(19);
        cell.setCellValue(summary.getUpDownFlag());
        cell = row.getCell(20);
        cell.setCellValue(summary.getStationNo());
        cell = row.getCell(21);
        cell.setCellValue(summary.getEscapeCityCode());
        cell = row.getCell(22);
        cell.setCellValue(summary.getEscapeIssuerFlag());
        cell = row.getCell(23);
        cell.setCellValue(summary.getEscapeStation());
        cell = row.getCell(24);
        cell.setCellValue(summary.getEscapeTerminalNo());
        cell = row.getCell(25);
        cell.setCellValue(summary.getEscapeDealTime());
        cell = row.getCell(26);
        cell.setCellValue(summary.getEscapeDirectionFlag());
        cell = row.getCell(27);
        cell.setCellValue(summary.getEscapeLineNo());
        cell = row.getCell(28);
        cell.setCellValue(summary.getEscapeBusNo());
        cell = row.getCell(29);
        cell.setCellValue(summary.getTicketTimes());
        cell = row.getCell(30);
        cell.setCellValue(summary.getTicketType());
        cell = row.getCell(31);
        cell.setCellValue(summary.getTicketDate());
        cell = row.getCell(32);
        cell.setCellValue(summary.getStationName());
        cell = row.getCell(33);
        cell.setCellValue(summary.getLongitude());
        cell = row.getCell(34);
        cell.setCellValue(summary.getLatitude());
    }


}

public class SXSSFPOIUtils {

    /**
     * 根据模板创建workbook
     * @param template 模板文件地址
     * @return
* @throws IOException
     */
public static SXSSFWorkbook createWorkBook(File template, final int baseRowNum) throws Exception{
        FileInputStream is = new FileInputStream(template);
        XSSFWorkbook wb = new XSSFWorkbook(is);
        //获取模板中最后一行，用于判断是否存在公式
int lastRowNum = wb.getSheetAt(0).getLastRowNum();
        if(lastRowNum<baseRowNum)
            throw  new Exception("模板格式不正确，至少需要两"+baseRowNum+"行！");
        Sheet sheet0 = wb.getSheetAt(0);
        for(int i=lastRowNum;i>=baseRowNum;i--){
            Row baseRow1 = sheet0.getRow(i);
            sheet0.removeRow(baseRow1);
        }
        SXSSFWorkbook xwb = new SXSSFWorkbook(wb,1000);
        return xwb;
    }
    public static List<Row> getAddRows(File template, final int baseRowNum) throws Exception{
        List<Row> rowList=new ArrayList<Row>();
        FileInputStream is = new FileInputStream(template);
        XSSFWorkbook wb = new XSSFWorkbook(is);
        //获取模板中最后一行，用于判断是否存在公式
int lastRowNum = wb.getSheetAt(0).getLastRowNum();
        if(lastRowNum<baseRowNum)
            throw  new Exception("模板格式不正确，至少需要两"+baseRowNum+"行！");
        Sheet sheet0 = wb.getSheetAt(0);
        for(int i=baseRowNum;i<=lastRowNum;i++){
            Row baseRow1 = sheet0.getRow(i);
            rowList.add(baseRow1);
        }
        return rowList;
    }


    /**
     * Excel的工作表里复制行
     *
     * @param sheet Excel的工作表
     * @param sourceRowNum 要复制的行号（0开始）
     * @param destRowNum 目标行号（0开始）
     */
public static final void copyRow(final Sheet sheet, final int sourceRowNum, final int destRowNum) {
        int lastRowNum = sheet.getLastRowNum();

        if (destRowNum <= lastRowNum) {
            sheet.shiftRows(destRowNum, sheet.getLastRowNum(), 1);
        }

        final Row sourceRow = sheet.getRow(sourceRowNum); // 要复制的行
final Row destRow = sheet.createRow(destRowNum); // 目标行
final int lastCellNum = sourceRow.getLastCellNum();

        for (int i = 0; i < lastCellNum; i++) {
            Cell sourceCell = sourceRow.getCell(i); // 要复制的单元格
Cell destCell = destRow.getCell(i); // 目标单元格
if (sourceCell != null) {
                if (destCell == null) {
                    destCell = destRow.createCell(i);
                }

                destCell.setCellType(sourceCell.getCellType());
                destCell.setCellStyle(sourceCell.getCellStyle());

                if (sourceCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    if (!StringUtils.isBlank(sourceCell.getCellFormula())) { // 有公式
destCell.setCellFormula(getResolvedFormula(sourceCell.getCellFormula(), sourceRowNum, destRowNum));
                    }
                }

                boolean merge = false; // 是否需要合并，已合并过的不再合并
for (int j = 0, k = sheet.getNumMergedRegions(); j < k; j++) {
                    if (sheet.getMergedRegion(j).isInRange(destRowNum, i)) {
                        merge = true;
                    }
                }

                if (!merge) { // 查看是否需要合并
for (int j = 0, k = sheet.getNumMergedRegions(); j < k; j++) {
                        final CellRangeAddress rangeAddress = sheet.getMergedRegion(j);

                        if (rangeAddress.isInRange(sourceRowNum, i)) { // 要复制的单元格是合并的，被复制的也要合并
sheet.addMergedRegion(new CellRangeAddress(destRowNum, destRowNum, rangeAddress.getFirstColumn(), rangeAddress.getLastColumn()));

                            break;
                        }
                    }
                }
            }
        }
    }

    /**
     * Excel的工作表里复制行
     *
     * @param sheet Excel的工作表
     * @param destRowNum 目标行号（0开始）
     */
public static final void creatRow(Sheet sheet,  List<ExcelCellBean> excelCellList,  int destRowNum) {
        final int lastRowNum = sheet.getLastRowNum();
        ExcelCellBean excelCellBean;
//        if (destRowNum <= lastRowNum) {
//            sheet.shiftRows(destRowNum, sheet.getLastRowNum(), 1);
//        }
final Row destRow = sheet.createRow(destRowNum); // 目标行
int lastCellNum=excelCellList.size();
        for (int i = 0; i < lastCellNum; i++) {
            Cell destCell = destRow.getCell(i); // 目标单元格
if (excelCellList.get(i) != null) {
                if (destCell == null) {
                    destCell = destRow.createCell(i);
                }
                excelCellBean=excelCellList.get(i);
                destCell.setCellType(excelCellBean.getCellType());
                destCell.setCellStyle(excelCellBean.getCellStyle());

            }
        }
    }

    /**
     * Excel的工作表里复制行
     *
     * @param sheet Excel的工作表
     */
public static final void addRow(final Sheet sheet,  List<Row> rowList) {
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i < rowList.size(); i++) {
            lastRowNum=lastRowNum+1;
            Row sourceRow=rowList.get(i);
            copyRow(sheet,sourceRow, lastRowNum);
        }
    }

    /**************************************************************************************
     * 私有方法
     **************************************************************************************/
    /**
     * Excel复制行时调整公式，假定公式只对该行的进行操作<br>     * 如第三行的一个公式ROUND((A3+G3), 2)复制到第五行后要更新成ROUND((A5+G5), 2)
     */
private static final String getResolvedFormula(final String formula, final int sourceRowNum, final int destRowNum) {
        final Pattern p = Pattern.compile("[a-zA-Z]" + (sourceRowNum + 1) + "(\\D|$)");

        final Matcher m = p.matcher(formula);

        String resolvedFormula = formula;

        while (m.find()) { // 如果查找到，就替换第一个
resolvedFormula = StringUtils.replaceOnce(resolvedFormula, "" + (sourceRowNum + 1), "" + (destRowNum + 1), m.start());
        }

        return resolvedFormula;
    }

    public static List<ExcelCellBean> getSheetFirstRowCell(File template,int baseRowNum)throws Exception {
        FileInputStream is = new FileInputStream(template);
        XSSFWorkbook wb = new XSSFWorkbook(is);
        //获取模板中最后一行，用于判断是否存在公式
int lastRowNum = wb.getSheetAt(0).getLastRowNum();
        if(lastRowNum<1)
            throw  new Exception("模板格式不正确，至少需要两行！");
        Sheet sheet0 = wb.getSheetAt(0);
        Row baseRow = sheet0.getRow(baseRowNum);
        List<ExcelCellBean> excelCellList = new ArrayList<ExcelCellBean>();
        int i = 0;
        ExcelCellBean cellBean;
        for (Iterator<Cell> it = baseRow.cellIterator(); it.hasNext(); ) {
            Cell baseCell = it.next();
            cellBean = new ExcelCellBean();
            cellBean.setCellType(baseCell.getCellType());
            cellBean.setCellStyle(baseCell.getCellStyle());
            if (baseCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                cellBean.setCellFormula(baseCell.getCellFormula());
            }
            excelCellList.add(i, cellBean);
        }
        return   excelCellList;
    }


    /**
     * Excel的工作表里复制行
     *
     * @param sheet Excel的工作表
     * @param destRowNum 目标行号（0开始）
     */
public static final void copyRow(final Sheet sheet,  Row sourceRow, final int destRowNum) {
        final int lastRowNum = sheet.getLastRowNum();

        if (destRowNum <= lastRowNum) {
            sheet.shiftRows(destRowNum, sheet.getLastRowNum(), 1);
        }


        final Row destRow = sheet.createRow(destRowNum); // 目标行
final int lastCellNum = sourceRow.getLastCellNum();

        for (int i = 0; i < lastCellNum; i++) {
            Cell sourceCell = sourceRow.getCell(i); // 要复制的单元格
Cell destCell = destRow.getCell(i); // 目标单元格
if (sourceCell != null) {
                if (destCell == null) {
                    destCell = destRow.createCell(i);
                }

                destCell.setCellType(sourceCell.getCellType());
                destCell.setCellStyle(sourceCell.getCellStyle());
                if(sourceCell.getCellType()==Cell.CELL_TYPE_NUMERIC){
                    destCell.setCellValue(sourceCell.getNumericCellValue());
                }else if(sourceCell.getCellType()==Cell.CELL_TYPE_BOOLEAN){
                    destCell.setCellValue(sourceCell.getBooleanCellValue());
                }else if(sourceCell.getCellType()==Cell.CELL_TYPE_STRING){
                    destCell.setCellValue(sourceCell.getStringCellValue());
                }

                if (sourceCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    if (!StringUtils.isBlank(sourceCell.getCellFormula())) { // 有公式
destCell.setCellFormula(getResolvedFormula(sourceCell.getCellFormula(), sourceRow.getRowNum(), destRowNum));
                    }
                }

                boolean merge = false; // 是否需要合并，已合并过的不再合并
for (int j = 0, k = sheet.getNumMergedRegions(); j < k; j++) {
                    if (sheet.getMergedRegion(j).isInRange(destRowNum, i)) {
                        merge = true;
                    }
                }

                if (!merge) { // 查看是否需要合并
for (int j = 0, k = sheet.getNumMergedRegions(); j < k; j++) {
                        final CellRangeAddress rangeAddress = sheet.getMergedRegion(j);

                        if (rangeAddress.isInRange(sourceRow.getRowNum(), i)) { // 要复制的单元格是合并的，被复制的也要合并
sheet.addMergedRegion(new CellRangeAddress(destRowNum, destRowNum, rangeAddress.getFirstColumn(), rangeAddress.getLastColumn()));

                            break;
                        }
                    }
                }
            }
        }
    }
    public static List<ExcelCellBean> getSheetFirstRow(File template,int baseRowNum)throws Exception {
        FileInputStream is = new FileInputStream(template);
        XSSFWorkbook wb = new XSSFWorkbook(is);
        //获取模板中最后一行，用于判断是否存在公式
int lastRowNum = wb.getSheetAt(0).getLastRowNum();
        if(lastRowNum<1)
            throw  new Exception("模板格式不正确，至少需要两行！");
        Sheet sheet0 = wb.getSheetAt(0);
        Row baseRow = sheet0.getRow(baseRowNum);

        List<ExcelCellBean> excelCellList = new ArrayList<ExcelCellBean>();
        int i = 0;
        ExcelCellBean cellBean;
        for (Iterator<Cell> it = baseRow.cellIterator(); it.hasNext(); ) {
            Cell baseCell = it.next();
            cellBean = new ExcelCellBean();
            cellBean.setCellType(baseCell.getCellType());
            cellBean.setCellStyle(baseCell.getCellStyle());
            if (baseCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                cellBean.setCellFormula(baseCell.getCellFormula());
            }
            excelCellList.add(i, cellBean);
        }
        return   excelCellList;
    }
    /**
     * 获取单元格样式
     * @param workbook 工作表
     * @param type 单元格值类型 0-数量  1-逗号金额  2-普通金额 3-大写人民币符号格式
     * @return
*/
public static CellStyle getCellStyle(final Workbook workbook, final Cell cell, final Integer type) {
        CellStyle style = cell.getCellStyle();
        switch (type) {
            case 0: style.setDataFormat(workbook.createDataFormat().getFormat("0")); break;
            case 1: style.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00")); break;
            case 2: style.setDataFormat(workbook.createDataFormat().getFormat("0.00")); break;
            case 3: style.setDataFormat(workbook.createDataFormat().getFormat("¥#,##0.00;¥-#,##0.00")); break;
            default: break;
        }

        return style;
    }
    /**
     * 合计行
     */
public static void sumRow(Sheet currentSheet, Workbook wb, int rowNum, String colume){
        int rowNum1=currentSheet.getLastRowNum();
        Row row = currentSheet.getRow(currentSheet.getLastRowNum());
        Cell cell = row.getCell(1);
        cell.setCellValue(currentSheet.getLastRowNum()-1);
        cell = row.getCell(4);
        cell.setCellType(Cell.CELL_TYPE_FORMULA);
        String formula = String.format("SUM(%s2:%s%d)",colume,colume, rowNum1);
        cell.setCellStyle(getCellStyle(wb, cell, 1));
        cell.setCellFormula(formula);
        currentSheet.setForceFormulaRecalculation(true);
    }


}

public class Pagination<T> {
    //一页显示的记录数
    private int numPerPage = 100;
    //记录总数
    private int totalRows;
    //总页数
    private int totalPages;
    //当前页码
    private int currentPage = 1;
    //起始行数
    private int startIndex = 0;
    //结束行数
    private int lastIndex;
    //结果集存放List
    private List<T> resultList = new ArrayList<T>();

    private static final Logger logger = LoggerFactory.getLogger(Pagination.class);

    /**分页构造函数
     * @param sql 根据传入的sql语句得到一些基本分页信息
     * @param currentPage 当前页
     * @param numPerPage 每页记录数
     * @param jdbcTemplate 实例
     * @param dataBaseType 数据库类型 0-mysql 1-oracle
     */
    public Pagination(String sql, int currentPage, int numPerPage, JdbcTemplate jdbcTemplate, Integer dataBaseType, Class<T> returnType, Object... args){
        if (jdbcTemplate == null) {
            throw new IllegalArgumentException("jdbcTemplate不能为空");
        } else if (StringUtils.isEmpty(sql)) {
            throw new IllegalArgumentException("分页查询SQL不能为空");
        }
        //设置每页显示记录数
        setNumPerPage(numPerPage);
        //设置要显示的页数
        setCurrentPage(currentPage);
        //计算总记录数
        StringBuffer totalSql = new StringBuffer(" SELECT COUNT(1) FROM ( \n ");
        totalSql.append(sql).append("\n");
        totalSql.append(" ) AS TOTAL_TABLE ");
        //总记录数
        if (args != null) {
            setTotalRows(jdbcTemplate.queryForObject(totalSql.toString(), Integer.class, args));
        } else {
            setTotalRows(jdbcTemplate.queryForObject(totalSql.toString(), Integer.class));
        }
        //计算总页数
        setTotalPages();
        //计算起始行数
        setStartIndex();
        //计算结束行数
        setLastIndex();
        String querySql = "";
        if (0 == dataBaseType) {
            querySql = getMySqlPageSql(sql, numPerPage);
        } else {
            querySql = getOraclePageSql(sql);
        }
        logger.info("querySql="+querySql);
        //装入结果集
        setResultList(jdbcTemplate.query(querySql, new BeanPropertyRowMapper<T>(returnType), args));

    }

    /**
     * 构造MySQL数据分页SQL
     * @param dataSql 查询数据SQL
     * @param pageSize
     * @return
     */
    private String getMySqlPageSql(String dataSql, Integer pageSize) {
        StringBuilder stringBuilder = new StringBuilder(dataSql)
                .append(" LIMIT ").append(startIndex);
        if (pageSize != null) {
            stringBuilder.append(", ").append(pageSize);
        }

        return stringBuilder.toString();
    }

    /**
     * 构造oracle分页查询语句
     * @param dataSql 原始查询sql
     * @return 分页sql
     */
    private String getOraclePageSql(final String dataSql) {
        //构造oracle数据库的分页语句
        StringBuffer sql = new StringBuffer(" SELECT * FROM ( ").append("\n")
                .append(" SELECT TEMP.* , ROWNUM NUM FROM ( ").append("\n")
                .append(dataSql).append("\n")
                .append(" ) TEMP WHERE ROWNUM <= ").append(lastIndex).append("\n")
                .append(") WHERE NUM > ").append(startIndex);

        return sql.toString();
    }



    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getNumPerPage() {
        return numPerPage;
    }

    public void setNumPerPage(int numPerPage) {
        this.numPerPage = numPerPage;
    }

    public List<T> getResultList() {
        return resultList;
    }

    public void setResultList(List<T> resultList) {
        this.resultList = resultList;
    }

    public int getTotalPages() {
        return totalPages;
    }
    //计算总页数
    public void setTotalPages() {
        if(totalRows % numPerPage == 0){
            this.totalPages = totalRows / numPerPage;
        }else{
            this.totalPages = (totalRows / numPerPage) + 1;
        }
    }

    public int getTotalRows() {
        return totalRows;
    }

    public void setTotalRows(int totalRows) {
        this.totalRows = totalRows;
    }

    public int getStartIndex() {
        return startIndex;
    }

    public void setStartIndex() {
        this.startIndex = (currentPage - 1) * numPerPage;
    }

    public int getLastIndex() {
        return lastIndex;
    }

    //计算结束时候的索引
    public void setLastIndex() {
        if ( totalRows < numPerPage){
            this.lastIndex = totalRows;
        } else if ((totalRows % numPerPage == 0) || (totalRows % numPerPage != 0 && currentPage < totalPages)){
            this.lastIndex = currentPage * numPerPage;
        } else if (totalRows % numPerPage != 0 && currentPage == totalPages) { //最后一页
            this.lastIndex = totalRows ;
        }
    }
}

public class ExcelUtils {

    public static String writeExcel(Workbook wb, String fileDirectory, String fileName) throws Exception {
        // 输出Excel
        //String fileDirectory = baseFilePath + File.separator + "period";
        File fileDir = new File(fileDirectory);
        if (!fileDir.exists()) {
            fileDir.mkdir();
        }
        fileDirectory = String.format("%s%s", fileDirectory, File.separator);
        File fileDelete = new File(fileDirectory + fileName);
        if (fileDelete.exists() || fileDelete.isFile()) {
            fileDelete.delete();
        }
        POIUtils.write(fileDirectory, wb, fileName);
        return fileDirectory + fileName;
    }


    /**
     * 合计行
     */
    public static void sumRow(Sheet currentSheet, Workbook wb, int rowNum, String colume){
        Row row = currentSheet.getRow(rowNum+2);
        Cell cell = row.getCell(1);
        cell.setCellValue(rowNum-1);
        cell = row.getCell(4);
        cell.setCellType(Cell.CELL_TYPE_FORMULA);
        String formula = String.format("SUM(%s2:%s%d)",colume,colume, rowNum);
        cell.setCellStyle(POIUtils.getCellStyle(wb, cell, 1));
        cell.setCellFormula(formula);
        currentSheet.setForceFormulaRecalculation(true);
    }
}

public class ZipCompressorUtil {

   private static final Logger logger = Logger
         .getLogger(ZipCompressorUtil.class.getName());

   /**
    * 文件压缩，压缩成功返回true，否则false，压缩后删除原文件
    *
    * @return
    * @author Liuxueliang/Jul 9, 2014/4:02:14 PM
    */
   public static boolean compress(String srcFileName, String zipFileName) {
      File srcFile = new File(srcFileName);
      if (!srcFile.exists()) {
         logger.error("要压缩饿文件不存在，filename：" + srcFile);
      }

      try {
         File zipFile = new File(zipFileName);
         // 压缩文件若存在就删除，重新生成
         if (zipFile.exists()) {
            zipFile.delete();
         }
         Project prj = new Project();
         Zip zip = new Zip();
         zip.setProject(prj);
         zip.setDestFile(zipFile);
         // 压缩哪些文件
         FileSet fileSet = new FileSet();
         fileSet.setProject(prj);
         fileSet.setFile(srcFile);
         zip.addFileset(fileSet);
         zip.execute();
         srcFile.delete();
         return true;
      } catch (Exception e) {
         logger.error("file zipCompressor filed,filename:" + srcFileName);
         return false;
      }
   }

   /**
    * 文件压缩，压缩成功返回true，否则false，压缩后删除原文件

    * @return
    * @author Liuxueliang/Jul 9, 2014/4:02:14 PM
    */
   public static boolean compressFiles(List<String> srcFileNameList,
         String zipFileName) {

      File zipFile = new File(zipFileName);
      // 压缩文件若存在就删除，重新生成
      if (zipFile.exists()) {
         zipFile.delete();
      }
      Project prj = new Project();
      Zip zip = new Zip();
      zip.setProject(prj);
      zip.setDestFile(zipFile);
      // 压缩哪些文件
      FileSet fileSet = new FileSet();
      for (String srcFileName : srcFileNameList) {
         File srcFile = new File(srcFileName);
         if (!srcFile.exists()) {
            logger.error("要压缩饿文件不存在，filename：" + srcFile);
            return false;
         }
         fileSet.setFile(srcFile);
      }
      zip.addFileset(fileSet);
      zip.execute();
      for (String srcFileName : srcFileNameList) {
         File srcFile = new File(srcFileName);
         if (!srcFile.exists()) {
            continue;
         }
         srcFile.delete();
      }
      return true;

   }

   public static void main(String[] args) {
      // ZipCompressorUtil.compress("d:\\data\\19e_2013082811.csv",
      // "d:\\data\\19e_20130828" + ".zip");
      List<String> list = new ArrayList<String>();
      list.add("D:/1.txt");
      list.add("D:/2.txt");
      list.add("D:/3.txt");
      list.add("D:/4.txt");
      list.add("D:/5.txt");
      list.add("D:/6.txt");
      list.add("D:/7.txt");
      ZipCompressorUtil.compressFiles(list, "D:/123.zip");
   }

}