@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"); } }
相关推荐
Java模板化导出Excel是一种高效且灵活的方式,用于在Java应用程序中生成Excel文件。这种方式借助了jxls库,它允许开发者使用Apache POI库提供的功能,结合模板来批量生成复杂结构的Excel数据。jxls扩展了POI的功能,...
poi导入、导出,支持百万级数据模板导出、合并excel。项目为spring-boot-2上开发。resource里面有模板,在junit测试类中修改为本地存在路径即可导出文件,在junit测试类中修改for循环的i可以模拟百万级数据导出。注意...
这个Java demo提供了一种实用的方法来处理Excel大数据量导出的问题,它适用于那些需要高效、稳定地处理大量数据并生成Excel报告的业务场景。通过深入理解并实践这个示例,开发者可以更好地掌握Apache POI的流式API,...
本示例关注的是如何使用Java根据模板格式来创建适用于打印的专业Excel报表。这通常涉及到对Apache POI库的使用,这是一个强大的API,允许程序员在Java中处理Microsoft Office格式的文档,包括Excel(.xlsx)文件。 ...
本文将深入探讨如何使用Java实现这一功能,包括选用的库、步骤、以及处理复杂模板的关键技巧。 首先,Java中最常用的库是Apache POI,这是一个用于读写Microsoft Office格式档案的开源库,特别适合处理Excel文件(....
poi实现导入数据到excel模板,本来想上传poi的jar包,谁知限制我只能上传15M的文件,汗。。。。。 不知道啥时候开始要分了... 代码见: https://github.com/thisisnohi/nohi-doc poi实现导入数据到excel模板。...
Java Excel 读写与导入数据库是Java开发中常见的任务,特别是在数据处理和分析场景下。以下将详细讲解这个主题,并提供一些关键知识点。 首先,Java处理Excel文件主要依赖于两个库:Apache POI 和 JExcelAPI。...
在IT行业中,Excel作为一种强大的电子表格工具,常用于数据分析、报告制作和数据交换。"Excel导出数据(根据Excel模板定义)...使用Apache POI库,我们可以实现根据预设模板动态生成和导出Excel文件,满足各种业务需求。
(3) 实现业务处理类:创建一个业务处理类(例如:ExcelPoi),在这个类中,实现将查询结果写入模板的方法。通过`getExcelSheeet`方法,使用SQL查询结果更新模板文件中的数据。最后,将更新后的模板文件导出为新的...
在Java编程领域,Apache POI库是一个非常流行的API,它允许开发者读取、写入以及修改Microsoft Office格式的...通过学习这些资源,你可以更好地理解和实践上述知识,从而在项目中实现高效且灵活的Excel模板导出功能。
在Java编程中,导出Excel是一项常见的任务,特别是在数据分析、报表生成或数据交换等领域。而带图片的Excel导出则增加了复杂性,因为涉及到二进制数据的处理和Excel特定格式的支持。以下是一些关于如何在Java中实现...
总结来说,这个Java web项目通过Apache POI库实现了数据到Excel的动态导出,涉及到了文件操作、数据结构映射、模板应用和性能优化等多个方面,是Java Web开发中一个实用的功能实现。在实际开发中,可以参考这个案例...
本文将深入探讨如何使用Java实现这一功能,主要关注3.0版本的相关特性。 首先,Java中处理Excel文件最常用的库是Apache POI。Apache POI是一个开源项目,提供了对Microsoft Office格式文件(如Excel)的读写支持。...
在Java开发中,处理Excel文件是一项常见...综上所述,Java处理Excel文件上传和模板操作涉及到Web开发、文件读写、数据处理等多个方面,通过熟练掌握Apache POI库和相关的编程技巧,可以高效地实现各种复杂的Excel功能。
这里,我们将详细探讨如何实现这个功能,主要依赖于`Apache POI`库来操作Excel,并结合`JFreeChart`库来创建图表。 首先,我们需要引入Apache POI库,这是一个用于读取和写入Microsoft Office格式文件(包括Excel)...
这里我们将深入探讨如何使用Java实现从数据库中查询数据并将其以多级表头的形式导入到Excel文件中。 首先,我们需要理解多级表头的概念。在Excel中,多级表头允许我们在工作表的列上设置多个层次的标题,以更清晰地...
本文将详细探讨如何使用Java编程语言来实现Excel模板的读写操作,以便于理解和应用。 首先,我们要知道,Java中读写Excel文件通常依赖于第三方库,如Apache POI。Apache POI是一个强大的API,它允许开发者创建、...
本文主要介绍Java如何读取Excel简单模板,主要讲解了使用Java读取Excel模板的方法和步骤,涵盖了Java读取Excel模板的基本概念和实现细节。 首先,Java读取Excel模板需要使用org.apache.poi库,该库提供了读取Excel...
本篇文章将深入探讨如何使用Java来创建Excel文件,并基于提供的标签“源码”和“工具”分享一些实用的方法。 首先,Java生成Excel文件通常会用到Apache POI库,这是一个流行的API,用于读写Microsoft Office格式的...
Apache POI是一个强大的Java库,专门用于处理Microsoft Office格式的文件,尤其是Excel。在这个特定的例子中,我们将讨论如何使用POI库基于一个Excel模板文件循环输出数据行,并将结果导出为新的Excel文件。 首先,...