List of usage examples for org.apache.poi.ss.usermodel Sheet addMergedRegion
int addMergedRegion(CellRangeAddress region);
From source file:org.aio.handy.poi.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/*from w ww. j a v a2 s.c om*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); // title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Weekly Timesheet"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); // header row Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if (j == 9) { // the 10th cell contains sum over week days, e.g. // SUM(C3:I3) String ref = "C" + rownum + ":I" + rownum; cell.setCellFormula("SUM(" + ref + ")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11) { cell.setCellFormula("J" + rownum + "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } // row with totals below Row sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(35); Cell cell; cell = sumRow.createCell(0); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellValue("Total Hrs:"); cell.setCellStyle(styles.get("formula")); for (int j = 2; j < 12; j++) { cell = sumRow.createCell(j); String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; cell.setCellFormula("SUM(" + ref + ")"); if (j >= 9) cell.setCellStyle(styles.get("formula_2")); else cell.setCellStyle(styles.get("formula")); } rownum++; sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Regular Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("L13"); cell.setCellStyle(styles.get("formula_2")); sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Overtime Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("K13"); cell.setCellStyle(styles.get("formula_2")); // set sample data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; if (sample_data[i][j] instanceof String) { row.getCell(j).setCellValue((String) sample_data[i][j]); } else { row.getCell(j).setCellValue((Double) sample_data[i][j]); } } } // finally set column widths, the width is measured in units of 1/256th // of a character width sheet.setColumnWidth(0, 30 * 256); // 30 characters wide for (int i = 2; i < 9; i++) { sheet.setColumnWidth(i, 6 * 256); // 6 characters wide } sheet.setColumnWidth(10, 10 * 256); // 10 characters wide // Write the output to a file String file = "e:/timesheet.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * ?/*from w w w . ja v a 2s.c o m*/ * * @param fromSheet * @param rangeAddress * @param toSheet * @param toRowNum * @param toColumnNum * @param clearFromRange */ public static void copyRange(Sheet fromSheet, CellRangeAddress rangeAddress, Sheet toSheet, int toRowNum, int toColumnNum, boolean clearFromRange) { if (fromSheet == null || rangeAddress == null || toSheet == null) { return; } int fromRowIndex = rangeAddress.getFirstRow(); int fromColumnIndex = rangeAddress.getFirstColumn(); int rowNumOffset = toRowNum - fromRowIndex; int columnNumOffset = toColumnNum - fromColumnIndex; // CellRangeAddress toAddress = new CellRangeAddress(rangeAddress.getFirstRow() + rowNumOffset, rangeAddress.getLastRow() + rowNumOffset, rangeAddress.getFirstColumn() + columnNumOffset, rangeAddress.getLastColumn() + columnNumOffset); Workbook fromWorkbook = fromSheet.getWorkbook(); Sheet baseSheet = fromSheet; Sheet tmpSheet = null; // ????? if (fromSheet.equals(toSheet) && crossRangeAddress(rangeAddress, toAddress)) { // ? tmpSheet = fromWorkbook.getSheet(TMP_SHEET_NAME); if (tmpSheet == null) { tmpSheet = fromWorkbook.createSheet(TMP_SHEET_NAME); } baseSheet = tmpSheet; int lastColNum = getLastColNum(fromSheet); for (int i = 0; i <= lastColNum; i++) { tmpSheet.setColumnWidth(i, fromSheet.getColumnWidth(i)); } copyRange(fromSheet, rangeAddress, tmpSheet, rangeAddress.getFirstRow(), rangeAddress.getFirstColumn(), false); // ? if (clearFromRange) { clearRange(fromSheet, rangeAddress); } } // ???? Set<CellRangeAddress> targetCellSet = getMergedAddress(baseSheet, rangeAddress); // ??? clearRange(toSheet, toAddress); // ??? for (CellRangeAddress mergeAddress : targetCellSet) { toSheet.addMergedRegion(new CellRangeAddress(mergeAddress.getFirstRow() + rowNumOffset, mergeAddress.getLastRow() + rowNumOffset, mergeAddress.getFirstColumn() + columnNumOffset, mergeAddress.getLastColumn() + columnNumOffset)); } for (int i = rangeAddress.getFirstRow(); i <= rangeAddress.getLastRow(); i++) { // Row fromRow = baseSheet.getRow(i); if (fromRow == null) { continue; } Row row = toSheet.getRow(i + rowNumOffset); if (row == null) { row = toSheet.createRow(i + rowNumOffset); row.setHeight((short) 0); } // ?????? int fromRowHeight = fromRow.getHeight(); int toRowHeight = row.getHeight(); if (toRowHeight < fromRowHeight) { row.setHeight(fromRow.getHeight()); } ColumnHelper columnHelper = null; if (toSheet instanceof XSSFSheet) { XSSFSheet xssfSheet = (XSSFSheet) toSheet.getWorkbook() .getSheetAt(toSheet.getWorkbook().getSheetIndex(toSheet)); CTWorksheet ctWorksheet = xssfSheet.getCTWorksheet(); columnHelper = new ColumnHelper(ctWorksheet); } for (int j = rangeAddress.getFirstColumn(); j <= rangeAddress.getLastColumn(); j++) { Cell fromCell = fromRow.getCell(j); if (fromCell == null) { continue; } int maxColumn = SpreadsheetVersion.EXCEL97.getMaxColumns(); if (toSheet instanceof XSSFSheet) { maxColumn = SpreadsheetVersion.EXCEL2007.getMaxColumns(); } if (j + columnNumOffset >= maxColumn) { break; } Cell cell = row.getCell(j + columnNumOffset); if (cell == null) { cell = row.createCell(j + columnNumOffset); if (toSheet instanceof XSSFSheet) { // XSSF?????????? CTCol col = columnHelper.getColumn(cell.getColumnIndex(), false); if (col == null || !col.isSetWidth()) { toSheet.setColumnWidth(cell.getColumnIndex(), baseSheet.getColumnWidth(j)); } } } // ? copyCell(fromCell, cell); // ?????? int fromColumnWidth = baseSheet.getColumnWidth(j); int toColumnWidth = toSheet.getColumnWidth(j + columnNumOffset); if (toColumnWidth < fromColumnWidth) { toSheet.setColumnWidth(j + columnNumOffset, baseSheet.getColumnWidth(j)); } } } if (tmpSheet != null) { // fromWorkbook.removeSheetAt(fromWorkbook.getSheetIndex(tmpSheet)); } else if (clearFromRange) { // ???? clearRange(fromSheet, rangeAddress); } }
From source file:org.bbreak.excella.reports.tag.BlockColRepeatParamParser.java
License:Open Source License
@Override public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { try {/*from www.j av a 2 s .c o m*/ // ?? Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ? checkParam(paramDef, tagCell); ReportsParserInfo info = (ReportsParserInfo) data; ParamInfo paramInfo = info.getParamInfo(); // ParsedReportInfo parsedReportInfo = new ParsedReportInfo(); List<Object> resultList = new ArrayList<Object>(); // BC????? String bcTagname = paramDef.get(PARAM_VALUE); if (log.isDebugEnabled()) { log.debug("BC?? : " + bcTagname); } // ????? Object[] paramInfos = getParamData(paramInfo, bcTagname); if (paramInfos == null) { return parsedReportInfo; } // ???? List<SingleParamParser> singleParsers = getSingleReplaceParsers(info); // POJOParamInfo??? List<ParamInfo> paramInfoList = new ArrayList<ParamInfo>(); for (Object obj : paramInfos) { if (obj instanceof ParamInfo) { paramInfoList.add((ParamInfo) obj); continue; } ParamInfo childParamInfo = new ParamInfo(); Map<String, Object> map = PropertyUtils.describe(obj); for (Map.Entry<String, Object> entry : map.entrySet()) { for (ReportsTagParser<?> parser : singleParsers) { childParamInfo.addParam(parser.getTag(), entry.getKey(), entry.getValue()); } } paramInfoList.add(childParamInfo); } // ?? if (paramDef.containsKey(PARAM_MIN_REPEAT_NUM)) { Integer minRepeatNum = Integer.valueOf(paramDef.get(PARAM_MIN_REPEAT_NUM)); if (minRepeatNum > paramInfoList.size()) { int addEmptyRowNum = minRepeatNum - paramInfoList.size(); for (int num = 0; num < addEmptyRowNum; num++) { ParamInfo childParamInfo = new ParamInfo(); paramInfoList.add(childParamInfo); } } } paramInfos = paramInfoList.toArray(new ParamInfo[paramInfoList.size()]); // ? Integer repeatNum = paramInfos.length; if (paramDef.containsKey(PARAM_REPEAT)) { if (Integer.valueOf(paramDef.get(PARAM_REPEAT)) < repeatNum) { repeatNum = Integer.valueOf(paramDef.get(PARAM_REPEAT)); } } // ? // TODO ? /* * Integer turnNum = null; if (paramDef.containsKey( PARAM_TURN)) { turnNum = Integer.valueOf( paramDef.get( PARAM_TURN)); } */ // ?????????? Map<String, Object> beforeBlockSingleDataMap = new HashMap<String, Object>(); // ??? if (paramDef.containsKey(PARAM_DUPLICATE)) { String[] tmp = paramDef.get(PARAM_DUPLICATE).split(";"); // single??? for (String str : tmp) { for (ReportsTagParser<?> parser : singleParsers) { str = parser.getTag() + TAG_PARAM_PREFIX + str + TAG_PARAM_SUFFIX; // ??key??? beforeBlockSingleDataMap.put(str, ""); } } } // removeTag boolean removeTag = false; if (paramDef.containsKey(PARAM_REMOVE_TAG)) { removeTag = Boolean.valueOf(paramDef.get(PARAM_REMOVE_TAG)); } // fromCell int[] fromCellPosition = ReportsUtil.getCellIndex(paramDef.get(PARAM_FROM), PARAM_FROM); int defaultFromCellRowIndex = tagCell.getRow().getRowNum() + fromCellPosition[0]; int defaultFromCellColIndex = tagCell.getColumnIndex() + fromCellPosition[1]; // toCell int[] toCellIndex = ReportsUtil.getCellIndex(paramDef.get(PARAM_TO), PARAM_TO); int defaultToCellRowIndex = tagCell.getRow().getRowNum() + toCellIndex[0]; int defaultToCellColIndex = tagCell.getColumnIndex() + toCellIndex[1]; // ?(??) int blockEndRowIndex = defaultToCellRowIndex; int blockEndColIndex = defaultToCellColIndex; int blockStartRowIndex = defaultFromCellRowIndex; int blockStartColIndex = defaultFromCellColIndex; // BC??Cell[row][col] Object[][] blockCellsValue = ReportsUtil.getBlockCellValue(sheet, defaultFromCellRowIndex, defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex); CellStyle[][] blockCellsStyle = ReportsUtil.getBlockCellStyle(sheet, defaultFromCellRowIndex, defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex); CellType[][] blockCellTypes = ReportsUtil.getBlockCellType(sheet, defaultFromCellRowIndex, defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex); // ????? int[] columnWidths = ReportsUtil.getColumnWidth(sheet, defaultFromCellColIndex, defaultToCellColIndex); // ? int rowlen = defaultToCellRowIndex - defaultFromCellRowIndex + 1; int collen = defaultToCellColIndex - defaultFromCellColIndex + 1; // ??? CellRangeAddress[] margedCells = ReportsUtil.getMargedCells(sheet, defaultFromCellRowIndex, defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex); // ???? int turnCount = 0; int maxblockEndRowIndex = blockEndRowIndex; TagParser<?> parser = null; ParsedReportInfo result = null; // for (int repeatCount = 0; repeatCount < repeatNum; repeatCount++) { collen = defaultToCellColIndex - defaultFromCellColIndex + 1; // ??????blockStartRowIndex??blockEndColIndex? // TODO ? /* * if (turnNum != null && turnNum <= turnCount) { blockStartRowIndex = blockEndRowIndex + 1; blockEndColIndex = 0; turnCount = 0; } */ // if (turnCount > 0) { blockStartColIndex = blockEndColIndex + 1; blockEndColIndex = blockStartColIndex + collen - 1; } else { // ??????? 2009/11/16 // blockStartColIndex = tagCell.getColumnIndex(); } turnCount++; // ???BC if (repeatCount > 0) { CellRangeAddress rangeAddress = new CellRangeAddress(blockStartRowIndex, sheet.getLastRowNum(), blockStartColIndex, blockStartColIndex + collen - 1); PoiUtil.insertRangeRight(sheet, rangeAddress); if (log.isDebugEnabled()) { log.debug(""); log.debug(blockStartRowIndex + ":" + sheet.getLastRowNum() + ":" + blockStartColIndex + ":" + (blockStartColIndex + collen - 1)); } // ??? // ??????????? // ?????? int targetColNum = blockEndColIndex - defaultToCellColIndex; for (CellRangeAddress address : margedCells) { // ???(BC????????) // ???(BC????????) // ??? + BC?? - BC? // ??? + BC?? - BC? int firstRowNum = address.getFirstRow(); int lastRowNum = address.getLastRow(); int firstColumnNum = address.getFirstColumn() + targetColNum; int lastColumnNum = address.getLastColumn() + targetColNum; CellRangeAddress copyAddress = new CellRangeAddress(firstRowNum, lastRowNum, firstColumnNum, lastColumnNum); sheet.addMergedRegion(copyAddress); } } // ? if (log.isDebugEnabled()) { log.debug("?????? =" + repeatCount); } for (int rowIdx = 0; rowIdx < blockCellsValue.length; rowIdx++) { // ? // int copyToRowIndex = blockStartRowIndex + rowIdx; Row row = sheet.getRow(copyToRowIndex); // ??null??????????????? if (row == null && !ReportsUtil.isEmptyRow(blockCellTypes[rowIdx], blockCellsValue[rowIdx], blockCellsStyle[rowIdx])) { // ???? // ? // (??)?????????? // ??null???(RowCreate?)??????????? row = sheet.createRow(copyToRowIndex); } if (row != null) { // ? for (int colIdx = 0; colIdx < blockCellsValue[rowIdx].length; colIdx++) { // int copyToColIndex = blockStartColIndex + colIdx; // ? sheet.setColumnWidth(copyToColIndex, columnWidths[colIdx]); // ? Cell cell = row.getCell(copyToColIndex); // ?? CellType cellType = blockCellTypes[rowIdx][colIdx]; // ?? Object cellValue = blockCellsValue[rowIdx][colIdx]; // ?? CellStyle cellStyle = blockCellsStyle[rowIdx][colIdx]; // ????????????????? if (cell == null && !ReportsUtil.isEmptyCell(cellType, cellValue, cellStyle)) { cell = row.createCell(copyToColIndex); } // if (cell != null) { // ? cell.setCellType(cellType); // ?? PoiUtil.setCellValue(cell, cellValue); // ?? if (cellStyle != null) { cell.setCellStyle(cellStyle); } if (log.isDebugEnabled()) { log.debug("row=" + (copyToRowIndex) + " col" + (copyToColIndex) + ">>>>>>" + blockCellsValue[rowIdx][colIdx]); } } } } } // ????????? int plusRowNum = 0; // ????????? int plusColNum = 0; collen = defaultToCellColIndex - defaultFromCellColIndex + 1; // for (int targetRow = blockStartRowIndex; targetRow < blockStartRowIndex + rowlen + plusRowNum; targetRow++) { if (sheet.getRow(targetRow) == null) { if (log.isDebugEnabled()) { log.debug("row=" + targetRow + " : row is not available. continued..."); } continue; } // ? Cell chgTargetCell = null; // for (int targetCol = blockStartColIndex; targetCol <= blockStartColIndex + collen + plusColNum - 1; targetCol++) { // ???? chgTargetCell = sheet.getRow(targetRow).getCell(targetCol); if (chgTargetCell == null) { if (log.isDebugEnabled()) { log.debug("row=" + targetRow + " col=" + targetCol + " : cell is not available. continued..."); } continue; } parser = info.getMatchTagParser(sheet, chgTargetCell); if (parser == null) { if (log.isDebugEnabled()) { log.debug("row=" + targetRow + " col=" + targetCol + " parser is not available. continued..."); } continue; } String chgTargetCellString = chgTargetCell.getStringCellValue(); if (log.isDebugEnabled()) { log.debug("########## row=" + targetRow + " col=" + targetCol + " =" + chgTargetCellString + " ##########"); } // ? result = (ParsedReportInfo) parser.parse(sheet, chgTargetCell, info.createChildParserInfo((ParamInfo) paramInfos[repeatCount])); // ??? plusRowNum += result.getRowIndex() - result.getDefaultRowIndex(); // ??? plusColNum += result.getColumnIndex() - result.getDefaultColumnIndex(); // ?single??????????? // ??????????? if (parser instanceof SingleParamParser && beforeBlockSingleDataMap.containsKey(chgTargetCellString)) { if (beforeBlockSingleDataMap.get(chgTargetCellString) .equals(result.getParsedObject())) { // PoiUtil.setCellValue(chgTargetCell, ""); } else { // ???? beforeBlockSingleDataMap.put(chgTargetCellString, result.getParsedObject()); } } // ?????????????? if (blockStartColIndex != result.getDefaultColumnIndex() && maxblockEndRowIndex <= blockEndRowIndex && result.getRowIndex() > result.getDefaultRowIndex()) { CellRangeAddress preRangeAddress = new CellRangeAddress(blockEndRowIndex + 1, blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex()), blockStartColIndex, targetCol - 1); PoiUtil.insertRangeDown(sheet, preRangeAddress); if (log.isDebugEnabled()) { log.debug("******"); log.debug("1 : " + (blockEndRowIndex + 1) + ":" + (blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex())) + ":" + blockStartColIndex + ":" + (targetCol - 1)); } } // R?????? if (parser instanceof RowRepeatParamParser && maxblockEndRowIndex <= blockEndRowIndex && result.getRowIndex() > result.getDefaultRowIndex()) { CellRangeAddress rearRangeAddress = new CellRangeAddress(blockEndRowIndex + 1, blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex()), result.getDefaultColumnIndex() + 1, blockEndColIndex); PoiUtil.insertRangeDown(sheet, rearRangeAddress); if (log.isDebugEnabled()) { log.debug("******"); log.debug("2 : " + (blockEndRowIndex + 1) + ":" + (blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex())) + ":" + (result.getDefaultColumnIndex() + 1) + ":" + blockEndColIndex); } } blockEndRowIndex = defaultToCellRowIndex + plusRowNum; resultList.add(result.getParsedObject()); if (parser instanceof BlockColRepeatParamParser || parser instanceof BlockRowRepeatParamParser) { collen += result.getColumnIndex() - result.getDefaultColumnIndex(); plusColNum -= result.getColumnIndex() - result.getDefaultColumnIndex(); } // ???????? if (blockStartColIndex + collen + plusColNum - 1 > blockEndColIndex) { int beforeLastColIndex = blockEndColIndex; blockEndColIndex = blockStartColIndex + collen + plusColNum - 1; // ??????????? CellRangeAddress preRangeAddress = new CellRangeAddress(tagCell.getRowIndex(), targetRow - 1, beforeLastColIndex + 1, blockEndColIndex); PoiUtil.insertRangeRight(sheet, preRangeAddress); if (log.isDebugEnabled()) { log.debug("******"); log.debug("1 : " + tagCell.getRowIndex() + ":" + (targetRow - 1) + ":" + (beforeLastColIndex + 1) + ":" + blockEndColIndex); } // ?????? int lastRowNum = sheet.getLastRowNum(); if ((blockEndRowIndex + 1) <= lastRowNum && (beforeLastColIndex + 1) <= blockEndColIndex) { CellRangeAddress rangeAddress = new CellRangeAddress(blockEndRowIndex + 1, lastRowNum, beforeLastColIndex + 1, blockEndColIndex); PoiUtil.insertRangeRight(sheet, rangeAddress); if (log.isDebugEnabled()) { log.debug("******"); log.debug("3 : " + (blockEndRowIndex + 1) + ":" + lastRowNum + ":" + (beforeLastColIndex + 1) + ":" + blockEndColIndex); } } } // ? } // ?????? if (blockStartColIndex + collen + plusColNum - 1 < blockEndColIndex) { CellRangeAddress rearRangeAddress = new CellRangeAddress(targetRow, targetRow, blockStartColIndex + collen + plusColNum, blockEndColIndex); PoiUtil.insertRangeRight(sheet, rearRangeAddress); if (log.isDebugEnabled()) { log.debug("******"); log.debug("2 : " + targetRow + ":" + targetRow + ":" + (blockStartColIndex + collen + plusColNum) + ":" + blockEndColIndex); } } plusColNum = 0; // ? } // ??????? if (maxblockEndRowIndex < blockEndRowIndex) { if (log.isDebugEnabled()) { log.debug("******"); } if (repeatCount != 0) { CellRangeAddress preRangeAddress = new CellRangeAddress(maxblockEndRowIndex + 1, blockEndRowIndex, defaultFromCellColIndex, blockStartColIndex - 1); PoiUtil.insertRangeDown(sheet, preRangeAddress); if (log.isDebugEnabled()) { log.debug("1 : " + (maxblockEndRowIndex + 1) + ":" + blockEndRowIndex + ":" + defaultFromCellColIndex + ":" + (blockStartColIndex - 1)); } } // ??????? CellRangeAddress rearRangeAddress = new CellRangeAddress(maxblockEndRowIndex + 1, blockEndRowIndex, blockEndColIndex + 1, PoiUtil.getLastColNum(sheet)); PoiUtil.insertRangeDown(sheet, rearRangeAddress); if (log.isDebugEnabled()) { log.debug("2 : " + (maxblockEndRowIndex + 1) + ":" + blockEndRowIndex + ":" + (blockEndColIndex + 1) + ":" + PoiUtil.getLastColNum(sheet)); } maxblockEndRowIndex = blockEndRowIndex; } } // if (removeTag) { tagCell.setCellType(CellType.BLANK); } // parsedReportInfo.setDefaultRowIndex(defaultToCellRowIndex); parsedReportInfo.setDefaultColumnIndex(defaultToCellColIndex); parsedReportInfo.setColumnIndex(blockEndColIndex); parsedReportInfo.setParsedObject(resultList); parsedReportInfo.setRowIndex(maxblockEndRowIndex); if (log.isDebugEnabled()) { log.debug("finalBlockRowIndex= " + maxblockEndRowIndex); log.debug("finalBlockColIndex=" + blockEndColIndex); } return parsedReportInfo; } catch (Exception e) { throw new ParseException(tagCell, e); } }
From source file:org.bbreak.excella.reports.tag.BlockRowRepeatParamParser.java
License:Open Source License
@Override public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { try {// www . ja v a 2 s. com // ?? Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ? checkParam(sheet, paramDef, tagCell); ReportsParserInfo reportsParserInfo = (ReportsParserInfo) data; ParamInfo paramInfo = reportsParserInfo.getParamInfo(); // parse? ParsedReportInfo parsedReportInfo = new ParsedReportInfo(); List<Object> resultList = new ArrayList<Object>(); // ? int finalBlockRowIndex = 0; int finalBlockColIndex = 0; String brTagName = paramDef.get(PARAM_VALUE); if (log.isDebugEnabled()) { log.debug("BR??: " + brTagName); } // ????? Object[] paramInfos = getParamData(paramInfo, brTagName); if (paramInfos == null) { return parsedReportInfo; } // ???? List<SingleParamParser> singleParsers = getSingleReplaceParsers(reportsParserInfo); // POJOParamInfo??? List<ParamInfo> paramInfoList = new ArrayList<ParamInfo>(); for (Object obj : paramInfos) { if (obj instanceof ParamInfo) { paramInfoList.add((ParamInfo) obj); continue; } ParamInfo childParamInfo = new ParamInfo(); Map<String, Object> map = PropertyUtils.describe(obj); for (Map.Entry<String, Object> entry : map.entrySet()) { for (ReportsTagParser<?> parser : singleParsers) { childParamInfo.addParam(parser.getTag(), entry.getKey(), entry.getValue()); } } paramInfoList.add(childParamInfo); } // ?? if (paramDef.containsKey(PARAM_MIN_REPEAT_NUM)) { Integer minRepeatNum = Integer.valueOf(paramDef.get(PARAM_MIN_REPEAT_NUM)); if (minRepeatNum > paramInfoList.size()) { int addEmptyRowNum = minRepeatNum - paramInfoList.size(); for (int num = 0; num < addEmptyRowNum; num++) { ParamInfo childParamInfo = new ParamInfo(); paramInfoList.add(childParamInfo); } } } paramInfos = paramInfoList.toArray(new ParamInfo[paramInfoList.size()]); // repeatNum Integer repeatNum = paramInfos.length; if (paramDef.containsKey(PARAM_REPEAT_NUM)) { if (Integer.valueOf(paramDef.get(PARAM_REPEAT_NUM)) < repeatNum) { repeatNum = Integer.valueOf(paramDef.get(PARAM_REPEAT_NUM)); } } // duplicateParams ???????? Map<String, Object> unduplicableParamMap = new HashMap<String, Object>(); if (paramDef.containsKey(PARAM_DUPLICATE)) { String[] params = paramDef.get(PARAM_DUPLICATE).split(";"); for (String param : params) { for (ReportsTagParser<?> parser : singleParsers) { param = parser.getTag() + TAG_PARAM_PREFIX + param + TAG_PARAM_SUFFIX; unduplicableParamMap.put(param, ""); } } } // removeTag boolean removeTag = false; if (paramDef.containsKey(PARAM_REMOVE_TAG)) { removeTag = Boolean.valueOf(paramDef.get(PARAM_REMOVE_TAG)); } // int tagCellRowIndex = tagCell.getRowIndex(); int tagCellColIndex = tagCell.getColumnIndex(); // fromCell String fromCellParamDef = paramDef.get(PARAM_FROM_CELL); int[] fromCellPosition = ReportsUtil.getCellIndex(fromCellParamDef, PARAM_FROM_CELL); int defaultFromCellRowIndex = tagCellRowIndex + fromCellPosition[0]; int defaultFromCellColIndex = tagCellColIndex + fromCellPosition[1]; // toCell String toCellParamDef = paramDef.get(PARAM_TO_CELL); int[] toCellPosition = ReportsUtil.getCellIndex(toCellParamDef, PARAM_TO_CELL); int defaultToCellRowIndex = tagCellRowIndex + toCellPosition[0]; int defaultToCellColIndex = tagCellColIndex + toCellPosition[1]; // Object[][] blockCellValues = ReportsUtil.getBlockCellValue(sheet, defaultFromCellRowIndex, defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex); CellStyle[][] blockCellStyles = ReportsUtil.getBlockCellStyle(sheet, defaultFromCellRowIndex, defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex); CellType[][] blockCellTypes = ReportsUtil.getBlockCellType(sheet, defaultFromCellRowIndex, defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex); float[] rowHeight = ReportsUtil.getRowHeight(sheet, defaultFromCellRowIndex, defaultToCellRowIndex); // ??? CellRangeAddress[] margedCells = ReportsUtil.getMargedCells(sheet, defaultFromCellRowIndex, defaultToCellRowIndex, defaultFromCellColIndex, defaultToCellColIndex); // ? final int defaultBlockHeight = defaultToCellRowIndex - defaultFromCellRowIndex + 1; final int defaultBlockWidth = defaultToCellColIndex - defaultFromCellColIndex + 1; int rowlen = defaultBlockHeight; int collen = defaultBlockWidth; // ??(fromCell??, 1?????) int blockStartRowIndex = defaultFromCellRowIndex; int blockStartColIndex = defaultFromCellColIndex; int blockEndRowIndex = defaultToCellRowIndex; int blockEndColIndex = defaultToCellColIndex; int maxblockEndRowIndex = blockEndRowIndex; parsedReportInfo.setDefaultRowIndex(defaultToCellRowIndex); parsedReportInfo.setDefaultColumnIndex(defaultToCellColIndex); for (int repeatCount = 0; repeatCount < repeatNum; repeatCount++) { // ???? if (repeatCount > 0) { blockStartRowIndex = blockEndRowIndex + 1; blockEndRowIndex = blockStartRowIndex + rowlen - 1; CellRangeAddress rangeAddress = new CellRangeAddress(blockStartRowIndex, blockEndRowIndex, blockStartColIndex, PoiUtil.getLastColNum(sheet)); PoiUtil.insertRangeDown(sheet, rangeAddress); if (log.isDebugEnabled()) { log.debug(""); log.debug(" : " + blockStartRowIndex + ":" + (blockStartRowIndex + rowlen - 1) + ":" + blockStartColIndex + ":" + PoiUtil.getLastColNum(sheet)); } // ??? // ??????????? // ?????? int targetRowNum = maxblockEndRowIndex - (defaultFromCellRowIndex - 1); for (CellRangeAddress address : margedCells) { // ??? + BR?? - BR? // ??? + BR?? - BR? // ???(BR????????) // ???(BR????????) int firstRowNum = address.getFirstRow() + targetRowNum; int lastRowNum = address.getLastRow() + targetRowNum; int firstColumnNum = address.getFirstColumn(); int lastColumnNum = address.getLastColumn(); CellRangeAddress copyAddress = new CellRangeAddress(firstRowNum, lastRowNum, firstColumnNum, lastColumnNum); sheet.addMergedRegion(copyAddress); } } if (log.isDebugEnabled()) { log.debug("repeatCount = " + repeatCount); log.debug("blockStartRowIndex = " + blockStartRowIndex); log.debug("blockStartColIndex = " + blockStartColIndex); } // ?? if (log.isDebugEnabled()) { log.debug("?????? =" + repeatCount); } for (int rowIdx = 0; rowIdx < defaultBlockHeight; rowIdx++) { // ? Row row = sheet.getRow(blockStartRowIndex + rowIdx); // ??null??????????????? if (row == null && !ReportsUtil.isEmptyRow(blockCellTypes[rowIdx], blockCellValues[rowIdx], blockCellStyles[rowIdx])) { row = sheet.createRow(blockStartRowIndex + rowIdx); } if (row != null) { // ??? row.setHeightInPoints(rowHeight[rowIdx]); // ? for (int colIdx = 0; colIdx < defaultBlockWidth; colIdx++) { // ? Cell cell = row.getCell(blockStartColIndex + colIdx); // ?? CellType cellType = blockCellTypes[rowIdx][colIdx]; // ?? Object cellValue = blockCellValues[rowIdx][colIdx]; // ?? CellStyle cellStyle = blockCellStyles[rowIdx][colIdx]; // ????????????????? if (cell == null && !ReportsUtil.isEmptyCell(cellType, cellValue, cellStyle)) { cell = row.createCell(blockStartColIndex + colIdx); } // if (cell != null) { // ? cell.setCellType(cellType); // ?? PoiUtil.setCellValue(cell, cellValue); // ?? if (cellStyle == null) { log.info("Cell Style at [" + rowIdx + "," + colIdx + "] is not available. Skipping setCellValue()"); } else { cell.setCellStyle(cellStyle); } log.debug("row=" + (blockStartRowIndex + rowIdx) + " col" + (blockStartColIndex + colIdx) + ">>>>>>" + blockCellValues[rowIdx][colIdx]); } } } } int currentBlockHeight = rowlen; int currentBlockWidth = collen; // ????????? int plusRowNum = 0; // ????????? int plusColNum = 0; collen = defaultBlockWidth; // // ??? for (int targetRow = blockStartRowIndex; targetRow < blockStartRowIndex + rowlen + plusRowNum; targetRow++) { if (finalBlockRowIndex < targetRow) { finalBlockRowIndex = targetRow; } if (sheet.getRow(targetRow) == null) { if (log.isDebugEnabled()) { log.debug("row=" + targetRow + " : row is not available. continued..."); } continue; } for (int targetCol = blockStartColIndex; targetCol <= blockStartColIndex + collen + plusColNum - 1; targetCol++) { if (finalBlockColIndex < targetCol) { finalBlockColIndex = targetCol; } Cell targetCell = sheet.getRow(targetRow).getCell(targetCol); if (targetCell == null) { if (log.isDebugEnabled()) { log.debug("row=" + targetRow + " col=" + targetCol + " : cell is not available. continued..."); } continue; } // ?? TagParser<?> parser = reportsParserInfo.getMatchTagParser(sheet, targetCell); if (parser == null) { if (log.isDebugEnabled()) { log.debug("row=" + targetRow + " col=" + targetCol + " parser is not available. continued..."); } continue; } String targetCellTag = targetCell.getStringCellValue(); if (log.isDebugEnabled()) { log.debug("########## row=" + targetRow + " col=" + targetCol + " =" + targetCellTag + " ##########"); } // ParsedReportInfo result = (ParsedReportInfo) parser.parse(sheet, targetCell, reportsParserInfo.createChildParserInfo((ParamInfo) paramInfos[repeatCount])); resultList.add(result.getParsedObject()); // ??? plusRowNum += result.getRowIndex() - result.getDefaultRowIndex(); // ??? plusColNum += result.getColumnIndex() - result.getDefaultColumnIndex(); int additionalHeight = result.getRowIndex() - result.getDefaultRowIndex(); int additionalWidth = result.getColumnIndex() - result.getDefaultColumnIndex(); // ?????????? currentBlockHeight = currentBlockHeight + additionalHeight; currentBlockWidth = currentBlockWidth + additionalWidth; // ??? if (parser instanceof SingleParamParser) { if (unduplicableParamMap.containsKey(targetCellTag)) { if (unduplicableParamMap.get(targetCellTag).equals(result.getParsedObject())) { PoiUtil.setCellValue(targetCell, ""); } else { unduplicableParamMap.put(targetCellTag, result.getParsedObject()); } } } // ?????????????? if (defaultFromCellColIndex != result.getDefaultColumnIndex() && result.getRowIndex() > result.getDefaultRowIndex() && blockStartColIndex < targetCol) { CellRangeAddress preRangeAddress = new CellRangeAddress(blockEndRowIndex + 1, blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex()), blockStartColIndex, targetCol - 1); PoiUtil.insertRangeDown(sheet, preRangeAddress); if (log.isDebugEnabled()) { log.debug("******"); log.debug("1 : " + (blockEndRowIndex + 1) + ":" + (blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex())) + ":" + blockStartColIndex + ":" + (targetCol - 1)); } } // R?????? if (parser instanceof RowRepeatParamParser && result.getRowIndex() > result.getDefaultRowIndex()) { CellRangeAddress rearRangeAddress = new CellRangeAddress(blockEndRowIndex + 1, blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex()), result.getDefaultColumnIndex() + 1, PoiUtil.getLastColNum(sheet)); PoiUtil.insertRangeDown(sheet, rearRangeAddress); if (log.isDebugEnabled()) { log.debug("******"); log.debug("2 : " + (blockEndRowIndex + 1) + ":" + (blockEndRowIndex + (result.getRowIndex() - result.getDefaultRowIndex())) + ":" + (result.getDefaultColumnIndex() + 1) + ":" + PoiUtil.getLastColNum(sheet)); } } blockEndRowIndex += result.getRowIndex() - result.getDefaultRowIndex(); if (parser instanceof BlockColRepeatParamParser || parser instanceof BlockRowRepeatParamParser) { collen += result.getColumnIndex() - result.getDefaultColumnIndex(); plusColNum -= result.getColumnIndex() - result.getDefaultColumnIndex(); } // ???????? if (blockStartColIndex + collen + plusColNum - 1 > blockEndColIndex) { int beforeLastColIndex = blockEndColIndex; blockEndColIndex = blockStartColIndex + collen + plusColNum - 1; // ??????????? CellRangeAddress preRangeAddress = new CellRangeAddress(tagCell.getRowIndex(), targetRow - 1, beforeLastColIndex + 1, blockEndColIndex); PoiUtil.insertRangeRight(sheet, preRangeAddress); if (log.isDebugEnabled()) { log.debug("******"); log.debug("1 : " + tagCell.getRowIndex() + ":" + (targetRow - 1) + ":" + (beforeLastColIndex + 1) + ":" + blockEndColIndex); } } // ? } // ?????? if (blockStartColIndex + collen + plusColNum - 1 < blockEndColIndex) { CellRangeAddress rearRangeAddress = new CellRangeAddress(targetRow, targetRow, blockStartColIndex + collen + plusColNum, blockEndColIndex); PoiUtil.insertRangeRight(sheet, rearRangeAddress); if (log.isDebugEnabled()) { log.debug("******"); log.debug("2 : " + targetRow + ":" + targetRow + ":" + (blockStartColIndex + collen + plusColNum) + ":" + blockEndColIndex); } } plusColNum = 0; // ? } // ??????? int lastColNum = PoiUtil.getLastColNum(sheet); if ((maxblockEndRowIndex + 1) <= blockEndRowIndex && (blockEndColIndex + 1) <= lastColNum) { CellRangeAddress rearRangeAddress = new CellRangeAddress(maxblockEndRowIndex + 1, blockEndRowIndex, blockEndColIndex + 1, lastColNum); PoiUtil.insertRangeDown(sheet, rearRangeAddress); if (log.isDebugEnabled()) { log.debug("2 : " + (maxblockEndRowIndex + 1) + ":" + blockEndRowIndex + ":" + (blockEndColIndex + 1) + ":" + lastColNum); } } maxblockEndRowIndex = blockEndRowIndex; } // if (removeTag) { tagCell.setCellType(CellType.BLANK); } // ?? parsedReportInfo.setColumnIndex(finalBlockColIndex); parsedReportInfo.setRowIndex(finalBlockRowIndex); parsedReportInfo.setParsedObject(resultList); parsedReportInfo.setDefaultRowIndex(defaultToCellRowIndex); parsedReportInfo.setDefaultColumnIndex(defaultToCellColIndex); if (log.isDebugEnabled()) { log.debug("finalBlockRowIndex= " + finalBlockRowIndex); log.debug("finalBlockColIndex=" + finalBlockColIndex); } return parsedReportInfo; } catch (Exception e) { throw new ParseException(tagCell, e); } }
From source file:org.bbreak.excella.reports.tag.ColRepeatParamParser.java
License:Open Source License
@Override public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ?//from w w w.j av a 2s . com checkParam(paramDef, tagCell); String tag = tagCell.getStringCellValue(); ReportsParserInfo info = (ReportsParserInfo) data; ParamInfo paramInfo = info.getParamInfo(); ParsedReportInfo parsedReportInfo = new ParsedReportInfo(); // ?? Object[] paramValues = null; try { // ??? String replaceParam = paramDef.get(PARAM_VALUE); // ? Integer repeatNum = null; if (paramDef.containsKey(PARAM_REPEAT_NUM)) { repeatNum = Integer.valueOf(paramDef.get(PARAM_REPEAT_NUM)); } // ?? Integer minRepeatNum = null; if (paramDef.containsKey(PARAM_MIN_REPEAT_NUM)) { minRepeatNum = Integer.valueOf(paramDef.get(PARAM_MIN_REPEAT_NUM)); } // ? boolean sheetLink = false; if (paramDef.containsKey(PARAM_SHEET_LINK)) { sheetLink = Boolean.valueOf(paramDef.get(PARAM_SHEET_LINK)); } // String propertyName = null; if (paramDef.containsKey(PARAM_PROPERTY)) { propertyName = paramDef.get(PARAM_PROPERTY); } // ??? boolean hideDuplicate = false; if (paramDef.containsKey(PARAM_DUPLICATE)) { hideDuplicate = Boolean.valueOf(paramDef.get(PARAM_DUPLICATE)); } // if (ReportsUtil.VALUE_SHEET_NAMES.equals(replaceParam)) { // ?? paramValues = ReportsUtil.getSheetNames(info.getReportBook()).toArray(); } else if (ReportsUtil.VALUE_SHEET_VALUES.equals(replaceParam)) { // paramValues = ReportsUtil .getSheetValues(info.getReportBook(), propertyName, info.getReportParsers()).toArray(); } else { // ??? if (paramInfo != null) { paramValues = getParamData(paramInfo, replaceParam); } } if (paramValues == null || paramValues.length == 0) { // ? paramValues = new Object[] { null }; } // ? if (hideDuplicate && paramValues.length > 1) { List<Object> paramValuesList = new ArrayList<Object>(); for (int i = 0; i <= paramValues.length - 1; i++) { // ????? if (!paramValuesList.contains(paramValues[i])) { paramValuesList.add(paramValues[i]); } else { paramValuesList.add(null); } } paramValues = paramValuesList.toArray(); } // ? int shiftNum = paramValues.length; // ? int paramLength = paramValues.length; // ??????? if (minRepeatNum != null && shiftNum < minRepeatNum) { Object[] tmpValues = new Object[minRepeatNum]; System.arraycopy(paramValues, 0, tmpValues, 0, paramValues.length); paramValues = tmpValues; shiftNum = paramValues.length; paramLength = paramValues.length; } // ??? int defaultFromCellRowIndex = tagCell.getRowIndex(); // ??? int defaultFromCellColIndex = tagCell.getColumnIndex(); // ?? int unitColSize = 1; // ??? List<CellRangeAddress> maegedAddresses = new ArrayList<CellRangeAddress>(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress targetAddress = sheet.getMergedRegion(i); maegedAddresses.add(targetAddress); } // ??? if (maegedAddresses.size() > 0) { // ????????????? for (CellRangeAddress curMergedAdress : maegedAddresses) { if (defaultFromCellColIndex == curMergedAdress.getFirstColumn() && defaultFromCellRowIndex == curMergedAdress.getFirstRow()) { // ???????????? // ?????? unitColSize = curMergedAdress.getLastColumn() - curMergedAdress.getFirstColumn() + 1; // ?????? shiftNum = shiftNum * unitColSize; } } } // ? if (repeatNum != null && repeatNum < shiftNum) { // ?????? // ???????????????? // ?(repeatNum)??(unitColSize)?? shiftNum = repeatNum * unitColSize; // ?????? paramLength = repeatNum; } // ??? // ????? tagCell = new CellClone(tagCell); List<Cell> cellList = new ArrayList<Cell>(); int defaultToOverCellColIndex = tagCell.getColumnIndex() + unitColSize; for (int i = defaultFromCellColIndex; i < defaultToOverCellColIndex; i++) { Row targetCellRow = sheet.getRow(tagCell.getRowIndex()); cellList.add(new CellClone(targetCellRow.getCell(i))); } // ? if (shiftNum > 1) { // ?(????????) int shiftColSize = tagCell.getColumnIndex() + shiftNum - unitColSize - 1; // ??? CellRangeAddress rangeAddress = new CellRangeAddress(tagCell.getRowIndex(), tagCell.getRowIndex(), tagCell.getColumnIndex(), shiftColSize); PoiUtil.insertRangeRight(sheet, rangeAddress); // int tagCellWidth = sheet.getColumnWidth(tagCell.getColumnIndex()); for (int i = tagCell.getColumnIndex() + 1; i <= shiftColSize; i++) { int colWidth = sheet.getColumnWidth(i); if (colWidth < tagCellWidth) { // ?? ??????? // ?????? sheet.setColumnWidth(i, tagCellWidth); } } } // ??? Workbook workbook = sheet.getWorkbook(); String sheetName = workbook.getSheetName(workbook.getSheetIndex(sheet)); // ?? List<String> sheetNames = ReportsUtil.getSheetNames(info.getReportBook()); // ? List<Object> resultValues = new ArrayList<Object>(); // ??(beforeValue) Object beforeValue = null; // ? int valueIndex = -1; // ????? for (int colIndex = 0; colIndex < shiftNum; colIndex++) { // ?? Row row = sheet.getRow(tagCell.getRowIndex()); if (row == null) { // ???? // ? // (??)?????????? // ??null???(RowCreate?)??????????? row = sheet.createRow(tagCell.getRowIndex()); } // ?? Cell cell = row.getCell(tagCell.getColumnIndex() + colIndex); if (cell == null) { cell = row.createCell(tagCell.getColumnIndex() + colIndex); } // ????(null) Object value = null; // ?????? // ??0???(?????????)??????? int cellIndex = colIndex % unitColSize; // ????????? boolean skipCol = false; if (cellIndex != 0) { skipCol = true; } else { valueIndex++; } // // ? PoiUtil.copyCell(cellList.get(cellIndex), cell); // ? Object currentValue = paramValues[valueIndex]; // ??=true??????????? boolean duplicateValue = false; if (beforeValue != null && currentValue != null && beforeValue.equals(currentValue)) { // ??? duplicateValue = true; } if (!skipCol && !(hideDuplicate && duplicateValue)) { // ??=true // ?????????????? value = currentValue; } if (log.isDebugEnabled()) { log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")] " + tag + " " + value); } PoiUtil.setCellValue(cell, value); resultValues.add(value); // ? if (sheetLink) { if (!skipCol && valueIndex < sheetNames.size()) { PoiUtil.setHyperlink(cell, HyperlinkType.DOCUMENT, "'" + sheetNames.get(valueIndex) + "'!A1"); if (log.isDebugEnabled()) { log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")] Hyperlink " + "'" + sheetNames.get(valueIndex) + "'!A1"); } } } // ?? // ?????????????????? if (!skipCol && unitColSize > 1 && paramLength > valueIndex + 1) { CellRangeAddress rangeAddress = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex() + unitColSize - 1); sheet.addMergedRegion(rangeAddress); // ???????????? beforeValue = value; } // ??????? if (unitColSize == 1) { beforeValue = value; } } parsedReportInfo.setDefaultRowIndex(tagCell.getRowIndex()); // ?? parsedReportInfo.setDefaultColumnIndex(tagCell.getColumnIndex() + unitColSize - 1); parsedReportInfo.setRowIndex(tagCell.getRowIndex()); parsedReportInfo.setColumnIndex(tagCell.getColumnIndex() + shiftNum - 1); parsedReportInfo.setParsedObject(resultValues); if (log.isDebugEnabled()) { log.debug(parsedReportInfo); } return parsedReportInfo; } catch (Exception e) { throw new ParseException(tagCell, e); } }
From source file:org.bbreak.excella.reports.tag.RowRepeatParamParser.java
License:Open Source License
@Override public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ?//from w ww. j a v a2s .c o m checkParam(paramDef, tagCell); String tag = tagCell.getStringCellValue(); ReportsParserInfo reportsParserInfo = (ReportsParserInfo) data; // ? Object[] paramValues = null; try { // ? boolean rowShift = false; if (paramDef.containsKey(PARAM_ROW_SHIFT)) { rowShift = Boolean.valueOf(paramDef.get(PARAM_ROW_SHIFT)); } // ?? boolean hideDuplicate = false; if (paramDef.containsKey(PARAM_DUPLICATE)) { hideDuplicate = Boolean.valueOf(paramDef.get(PARAM_DUPLICATE)); } // ? Integer breakNum = null; if (paramDef.containsKey(PARAM_BREAK_NUM)) { breakNum = Integer.valueOf(paramDef.get(PARAM_BREAK_NUM)); } // boolean changeBreak = false; if (paramDef.containsKey(PARAM_CHANGE_BREAK)) { changeBreak = Boolean.valueOf(paramDef.get(PARAM_CHANGE_BREAK)); } // ? Integer repeatNum = null; if (paramDef.containsKey(PARAM_REPEAT_NUM)) { repeatNum = Integer.valueOf(paramDef.get(PARAM_REPEAT_NUM)); } // ?? Integer minRepeatNum = null; if (paramDef.containsKey(PARAM_MIN_REPEAT_NUM)) { minRepeatNum = Integer.valueOf(paramDef.get(PARAM_MIN_REPEAT_NUM)); } // ? boolean sheetLink = false; if (paramDef.containsKey(PARAM_SHEET_LINK)) { sheetLink = Boolean.valueOf(paramDef.get(PARAM_SHEET_LINK)); } // String propertyName = null; if (paramDef.containsKey(PARAM_PROPERTY)) { propertyName = paramDef.get(PARAM_PROPERTY); } // ??? String replaceParam = paramDef.get(PARAM_VALUE); // if (ReportsUtil.VALUE_SHEET_NAMES.equals(replaceParam)) { // ?? paramValues = ReportsUtil.getSheetNames(reportsParserInfo.getReportBook()).toArray(); } else if (ReportsUtil.VALUE_SHEET_VALUES.equals(replaceParam)) { // paramValues = ReportsUtil.getSheetValues(reportsParserInfo.getReportBook(), propertyName, reportsParserInfo.getReportParsers()).toArray(); } else { // ??? ParamInfo paramInfo = reportsParserInfo.getParamInfo(); if (paramInfo != null) { paramValues = getParamData(paramInfo, replaceParam); } } if (paramValues == null || paramValues.length == 0) { // ? paramValues = new Object[] { null }; } // ? int shiftNum = paramValues.length; // ? int paramLength = paramValues.length; // ??????? if (minRepeatNum != null && shiftNum < minRepeatNum) { Object[] tmpValues = new Object[minRepeatNum]; System.arraycopy(paramValues, 0, tmpValues, 0, paramValues.length); paramValues = tmpValues; shiftNum = paramValues.length; paramLength = paramValues.length; } // ??? int defaultFromCellRowIndex = tagCell.getRowIndex(); // ??? int defaultFromCellColIndex = tagCell.getColumnIndex(); // ??? int unitRowSize = 1; // ??? List<CellRangeAddress> maegedAddresses = new ArrayList<CellRangeAddress>(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress targetAddress = sheet.getMergedRegion(i); maegedAddresses.add(targetAddress); } // ??? if (maegedAddresses.size() > 0) { // ????????????? for (CellRangeAddress curMergedAdress : maegedAddresses) { if (defaultFromCellColIndex == curMergedAdress.getFirstColumn() && defaultFromCellRowIndex == curMergedAdress.getFirstRow()) { // ???????????? // ?????? unitRowSize = curMergedAdress.getLastRow() - curMergedAdress.getFirstRow() + 1; // ?????? shiftNum = shiftNum * unitRowSize; } } } // ??? // ????? tagCell = new CellClone(tagCell); List<Cell> cellList = new ArrayList<Cell>(); int defaultToOverCellRowIndex = tagCell.getRowIndex() + unitRowSize; for (int i = defaultFromCellRowIndex; i < defaultToOverCellRowIndex; i++) { Row targetCellRow = sheet.getRow(i); cellList.add(new CellClone(targetCellRow.getCell(tagCell.getColumnIndex()))); } // ? if (repeatNum != null && repeatNum < shiftNum) { // ?????? // ???????????????? // ????? shiftNum = repeatNum * unitRowSize; // ?????? paramLength = repeatNum; } // ? if (shiftNum > 1) { // ?(????????) int shiftRowSize = tagCell.getRowIndex() + shiftNum - unitRowSize - 1; if (!rowShift) { // ????? CellRangeAddress rangeAddress = new CellRangeAddress(tagCell.getRowIndex(), shiftRowSize, tagCell.getColumnIndex(), tagCell.getColumnIndex()); PoiUtil.insertRangeDown(sheet, rangeAddress); } else { // ????? // #35 POI??????????????????? // ??????0???????? CellRangeAddress rangeAddress = new CellRangeAddress(tagCell.getRowIndex() + unitRowSize, tagCell.getRowIndex() + shiftNum - 1, 0, PoiUtil.getLastColNum(sheet)); PoiUtil.insertRangeDown(sheet, rangeAddress); // int shiftStartRow = tagCell.getRowIndex() + 1; // int shiftEndRow = sheet.getLastRowNum(); // if ( shiftEndRow < shiftStartRow) { // // ???????????????? // // ???????????? // shiftEndRow = shiftStartRow + 1; // } // sheet.shiftRows( shiftStartRow, shiftEndRow, shiftNum - unitRowSize); } } // ??? Workbook workbook = sheet.getWorkbook(); String sheetName = workbook.getSheetName(workbook.getSheetIndex(sheet)); // ?? List<String> sheetNames = ReportsUtil.getSheetNames(reportsParserInfo.getReportBook()); // ? List<Object> resultValues = new ArrayList<Object>(); // ??(beforeValue) Object beforeValue = null; // ? int valueIndex = -1; // ????? for (int rowIndex = 0; rowIndex < shiftNum; rowIndex++) { // ?? Row row = sheet.getRow(tagCell.getRowIndex() + rowIndex); if (row == null) { row = sheet.createRow(tagCell.getRowIndex() + rowIndex); } // ?? Cell cell = row.getCell(tagCell.getColumnIndex()); if (cell == null) { cell = row.createCell(tagCell.getColumnIndex()); } // ????(null) Object value = null; // ?????? // ??0???(?????????)??????? int cellIndex = rowIndex % unitRowSize; // ????????? boolean skipRow = false; if (cellIndex != 0) { skipRow = true; } else { valueIndex++; } // (?) PoiUtil.copyCell(cellList.get(cellIndex), cell); // ? Object currentValue = paramValues[valueIndex]; // ??=true??????????? boolean duplicateValue = false; if (beforeValue != null && currentValue != null && beforeValue.equals(currentValue)) { // ??? duplicateValue = true; } if (!skipRow && !(hideDuplicate && duplicateValue)) { // ??=true // ?????????????? value = currentValue; } if (log.isDebugEnabled()) { log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")] " + tag + " " + value); } PoiUtil.setCellValue(cell, value); resultValues.add(value); // ? if (sheetLink) { if (!skipRow && valueIndex < sheetNames.size()) { PoiUtil.setHyperlink(cell, HyperlinkType.DOCUMENT, "'" + sheetNames.get(valueIndex) + "'!A1"); if (log.isDebugEnabled()) { log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")] Hyperlink " + "'" + sheetNames.get(valueIndex) + "'!A1"); } } } // if (!skipRow) { if (breakNum != null && valueIndex != 0 && valueIndex % breakNum == 0) { // ??? sheet.setRowBreak(row.getRowNum() - 1); } if (changeBreak && valueIndex != 0 && !duplicateValue) { // ??? sheet.setRowBreak(row.getRowNum() - 1); } } // ?? // ?????????? // ???????????? if (!skipRow && unitRowSize > 1) { // ??? boolean mergedRegionFlag = false; if (rowShift && valueIndex != 0) { // ????????????? mergedRegionFlag = true; } else if (!rowShift && paramLength > valueIndex + 1) { // ???????????? mergedRegionFlag = true; } // ?? if (mergedRegionFlag) { CellRangeAddress rangeAddress = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex() + unitRowSize - 1, cell.getColumnIndex(), cell.getColumnIndex()); sheet.addMergedRegion(rangeAddress); // ???????????? beforeValue = currentValue; } } // ??????? if (unitRowSize == 1) { beforeValue = currentValue; } } // ???? ParsedReportInfo parsedReportInfo = new ParsedReportInfo(); parsedReportInfo.setParsedObject(resultValues); // ?? parsedReportInfo.setDefaultRowIndex(tagCell.getRowIndex() + unitRowSize - 1); parsedReportInfo.setDefaultColumnIndex(tagCell.getColumnIndex()); parsedReportInfo.setRowIndex(tagCell.getRowIndex() + shiftNum - 1); parsedReportInfo.setColumnIndex(tagCell.getColumnIndex()); if (log.isDebugEnabled()) { log.debug(parsedReportInfo); } return parsedReportInfo; } catch (Exception e) { throw new ParseException(tagCell, e); } }
From source file:org.bbreak.excella.reports.util.ReportsUtilTest.java
License:Open Source License
/** * {@link org.bbreak.excella.reports.util.ReportsUtil#getMergedAddress(Sheet, int, int)} ???? *///from w w w .ja va 2 s. c o m @Test public void testgetMergedAddress() { // ? Workbook hssfWb = new HSSFWorkbook(); // ? Sheet hssfSheet = hssfWb.createSheet("testsheet"); // ? Row hssfRow = hssfSheet.createRow(0); // ? hssfRow.createCell(0); hssfRow.createCell(1); hssfRow.createCell(2); // ?? CellRangeAddress address1 = new CellRangeAddress(0, 1, 1, 1); hssfSheet.addMergedRegion(address1); CellRangeAddress address2 = new CellRangeAddress(0, 0, 2, 3); hssfSheet.addMergedRegion(address2); // assertNull(ReportsUtil.getMergedAddress(hssfSheet, 0, 0)); assertEquals(address1.toString(), ReportsUtil.getMergedAddress(hssfSheet, 0, 1).toString()); assertEquals(address2.toString(), ReportsUtil.getMergedAddress(hssfSheet, 0, 2).toString()); try { hssfWb.close(); } catch (IOException e) { } // ? Workbook xssfWb = new XSSFWorkbook(); // ? Sheet xssfSheet = xssfWb.createSheet("testsheet"); // ? Row xssfRow = xssfSheet.createRow(0); // ? xssfRow.createCell(0); xssfRow.createCell(1); xssfRow.createCell(2); // ?? address1 = new CellRangeAddress(0, 1, 1, 1); xssfSheet.addMergedRegion(address1); address2 = new CellRangeAddress(0, 0, 2, 3); xssfSheet.addMergedRegion(address2); // assertNull(ReportsUtil.getMergedAddress(xssfSheet, 0, 0)); assertEquals(address1.toString(), ReportsUtil.getMergedAddress(xssfSheet, 0, 1).toString()); assertEquals(address2.toString(), ReportsUtil.getMergedAddress(xssfSheet, 0, 2).toString()); try { xssfWb.close(); } catch (IOException e) { } }
From source file:org.corpus_tools.peppermodules.spreadsheet.tests.Spreadsheet2SaltMapperTest.java
License:Apache License
private void createSecondXlsxSample() throws IOException { xlsxWb = new XSSFWorkbook(); Sheet xlsxSheet = xlsxWb.createSheet("secondXlsxSample"); Row xlsxRow1 = xlsxSheet.createRow(0); Cell xlsxCell1 = xlsxRow1.createCell(0); xlsxCell1.setCellValue("tok"); Cell xlsxCell2 = xlsxRow1.createCell(1); xlsxCell2.setCellValue("anno1[tok]"); Cell xlsxCell3 = xlsxRow1.createCell(2); xlsxCell3.setCellValue("tok2"); Cell xlsxCell4 = xlsxRow1.createCell(3); xlsxCell4.setCellValue("anno2[tok2]"); Row xlsxRow2 = xlsxSheet.createRow(1); Cell xlsxCell21 = xlsxRow2.createCell(0); xlsxCell21.setCellValue("This"); Cell xlsxCell22 = xlsxRow2.createCell(1); xlsxCell22.setCellValue("pron1"); Cell xlsxCell23 = xlsxRow2.createCell(2); xlsxCell23.setCellValue("This"); Cell xlsxCell24 = xlsxRow2.createCell(3); xlsxCell24.setCellValue("pron2"); Row xlsxRow3 = xlsxSheet.createRow(2); Cell xlsxCell31 = xlsxRow3.createCell(0); xlsxCell31.setCellValue("is"); Cell xlsxCell32 = xlsxRow3.createCell(1); xlsxCell32.setCellValue("verb1"); Cell xlsxCell33 = xlsxRow3.createCell(2); xlsxCell33.setCellValue("is"); Cell xlsxCell34 = xlsxRow3.createCell(3); xlsxCell34.setCellValue("verb2"); Row xlsxRow4 = xlsxSheet.createRow(3); Cell xlsxCell41 = xlsxRow4.createCell(0); xlsxCell41.setCellValue("an"); Cell xlsxCell42 = xlsxRow4.createCell(1); xlsxCell42.setCellValue("art1"); Cell xlsxCell43 = xlsxRow4.createCell(2); xlsxCell43.setCellValue("an"); Cell xlsxCell44 = xlsxRow4.createCell(3); xlsxCell44.setCellValue("art2"); Row xlsRow5 = xlsxSheet.createRow(4); Cell xlsCell51 = xlsRow5.createCell(0); xlsCell51.setCellValue("example"); Cell xlsCell52 = xlsRow5.createCell(1); xlsCell52.setCellValue("noun1"); Cell xlsCell53 = xlsRow5.createCell(2); xlsCell53.setCellValue("ex-"); Cell xlsCell54 = xlsRow5.createCell(3); xlsCell54.setCellValue("noun2"); Row xlsxRow6 = xlsxSheet.createRow(5); xlsxRow6.createCell(0);/*from w w w. j av a 2s . co m*/ xlsxRow6.createCell(1); Cell xlsxCell63 = xlsxRow6.createCell(2); xlsxCell63.setCellValue("ample"); xlsxRow6.createCell(3); CellRangeAddress mergedPrim = new CellRangeAddress(4, 5, 0, 0); xlsxSheet.addMergedRegion(mergedPrim); CellRangeAddress mergedAnno1 = new CellRangeAddress(4, 5, 1, 1); xlsxSheet.addMergedRegion(mergedAnno1); CellRangeAddress mergedAnno2 = new CellRangeAddress(4, 5, 3, 3); xlsxSheet.addMergedRegion(mergedAnno2); Row xlsxRow7 = xlsxSheet.createRow(6); Cell xlsxCell71 = xlsxRow7.createCell(0); xlsxCell71.setCellValue("."); Cell xlsxCell72 = xlsxRow7.createCell(1); xlsxCell72.setCellValue("punct1"); Cell xlsxCell73 = xlsxRow7.createCell(2); xlsxCell73.setCellValue("."); Cell xlsxCell74 = xlsxRow7.createCell(3); xlsxCell74.setCellValue("punct2"); xlsxWb.write(outStream); }
From source file:org.corpus_tools.peppermodules.spreadsheet.tests.Spreadsheet2SaltMapperTest.java
License:Apache License
private void createThirdXlsxSample() throws IOException { xlsxWb = new XSSFWorkbook(); Sheet xlsxSheet = xlsxWb.createSheet("ThirdXlsxSample"); Row xlsxRow1 = xlsxSheet.createRow(0); Cell xlsxCell1 = xlsxRow1.createCell(0); xlsxCell1.setCellValue("tok"); Cell xlsxCell2 = xlsxRow1.createCell(1); xlsxCell2.setCellValue("anno1"); Cell xlsxCell3 = xlsxRow1.createCell(2); xlsxCell3.setCellValue("tok2"); Cell xlsxCell4 = xlsxRow1.createCell(3); xlsxCell4.setCellValue("anno2"); Row xlsxRow2 = xlsxSheet.createRow(1); Cell xlsxCell21 = xlsxRow2.createCell(0); xlsxCell21.setCellValue("This"); Cell xlsxCell22 = xlsxRow2.createCell(1); xlsxCell22.setCellValue("pron1"); Cell xlsxCell23 = xlsxRow2.createCell(2); xlsxCell23.setCellValue("This"); Cell xlsxCell24 = xlsxRow2.createCell(3); xlsxCell24.setCellValue("pron2"); Row xlsxRow3 = xlsxSheet.createRow(2); Cell xlsxCell31 = xlsxRow3.createCell(0); xlsxCell31.setCellValue("is"); Cell xlsxCell32 = xlsxRow3.createCell(1); xlsxCell32.setCellValue("verb1"); Cell xlsxCell33 = xlsxRow3.createCell(2); xlsxCell33.setCellValue("is"); Cell xlsxCell34 = xlsxRow3.createCell(3); xlsxCell34.setCellValue("verb2"); Row xlsxRow4 = xlsxSheet.createRow(3); Cell xlsxCell41 = xlsxRow4.createCell(0); xlsxCell41.setCellValue("an"); Cell xlsxCell42 = xlsxRow4.createCell(1); xlsxCell42.setCellValue("art1"); Cell xlsxCell43 = xlsxRow4.createCell(2); xlsxCell43.setCellValue("an"); Cell xlsxCell44 = xlsxRow4.createCell(3); xlsxCell44.setCellValue("art2"); Row xlsRow5 = xlsxSheet.createRow(4); Cell xlsCell51 = xlsRow5.createCell(0); xlsCell51.setCellValue("example"); Cell xlsCell52 = xlsRow5.createCell(1); xlsCell52.setCellValue("noun1"); Cell xlsCell53 = xlsRow5.createCell(2); xlsCell53.setCellValue("ex-"); Cell xlsCell54 = xlsRow5.createCell(3); xlsCell54.setCellValue("noun2"); Row xlsxRow6 = xlsxSheet.createRow(5); xlsxRow6.createCell(0);/*w ww . jav a 2 s. c om*/ xlsxRow6.createCell(1); Cell xlsxCell63 = xlsxRow6.createCell(2); xlsxCell63.setCellValue("ample"); xlsxRow6.createCell(3); CellRangeAddress mergedPrim = new CellRangeAddress(4, 5, 0, 0); xlsxSheet.addMergedRegion(mergedPrim); CellRangeAddress mergedAnno1 = new CellRangeAddress(4, 5, 1, 1); xlsxSheet.addMergedRegion(mergedAnno1); CellRangeAddress mergedAnno2 = new CellRangeAddress(4, 5, 3, 3); xlsxSheet.addMergedRegion(mergedAnno2); Row xlsxRow7 = xlsxSheet.createRow(6); Cell xlsxCell71 = xlsxRow7.createCell(0); xlsxCell71.setCellValue("."); Cell xlsxCell72 = xlsxRow7.createCell(1); xlsxCell72.setCellValue("punct1"); Cell xlsxCell73 = xlsxRow7.createCell(2); xlsxCell73.setCellValue("."); Cell xlsxCell74 = xlsxRow7.createCell(3); xlsxCell74.setCellValue("punct2"); xlsxWb.write(outStream); }
From source file:org.corpus_tools.peppermodules.spreadsheet.tests.Spreadsheet2SaltMapperTest.java
License:Apache License
private FileOutputStream createFourthXlsxSample() throws IOException { xlsxWb = new XSSFWorkbook(); Sheet xlsxSheet = xlsxWb.createSheet("fourthXlsxSample"); Row xlsxRow1 = xlsxSheet.createRow(0); Cell xlsxCell1 = xlsxRow1.createCell(0); xlsxCell1.setCellValue("tok"); Cell xlsxCell2 = xlsxRow1.createCell(1); xlsxCell2.setCellValue("anno1"); Cell xlsxCell3 = xlsxRow1.createCell(2); xlsxCell3.setCellValue("tok2"); Cell xlsxCell4 = xlsxRow1.createCell(3); xlsxCell4.setCellValue("anno2"); Cell xlsxCell5 = xlsxRow1.createCell(4); xlsxCell5.setCellValue("lb"); Row xlsxRow2 = xlsxSheet.createRow(1); Cell xlsxCell21 = xlsxRow2.createCell(0); xlsxCell21.setCellValue("This"); Cell xlsxCell22 = xlsxRow2.createCell(1); xlsxCell22.setCellValue("pron1"); Cell xlsxCell23 = xlsxRow2.createCell(2); xlsxCell23.setCellValue("This"); Cell xlsxCell24 = xlsxRow2.createCell(3); xlsxCell24.setCellValue("pron2"); Cell xlsxCell25 = xlsxRow2.createCell(4); xlsxCell25.setCellValue("lb"); Row xlsxRow3 = xlsxSheet.createRow(2); Cell xlsxCell31 = xlsxRow3.createCell(0); xlsxCell31.setCellValue("is"); Cell xlsxCell32 = xlsxRow3.createCell(1); xlsxCell32.setCellValue("verb1"); Cell xlsxCell33 = xlsxRow3.createCell(2); xlsxCell33.setCellValue("is"); Cell xlsxCell34 = xlsxRow3.createCell(3); xlsxCell34.setCellValue("verb2"); xlsxRow3.createCell(4);//from www . jav a 2s.c om Row xlsxRow4 = xlsxSheet.createRow(3); Cell xlsxCell41 = xlsxRow4.createCell(0); xlsxCell41.setCellValue("an"); Cell xlsxCell42 = xlsxRow4.createCell(1); xlsxCell42.setCellValue("art1"); Cell xlsxCell43 = xlsxRow4.createCell(2); xlsxCell43.setCellValue("an"); Cell xlsxCell44 = xlsxRow4.createCell(3); xlsxCell44.setCellValue("art2"); xlsxRow4.createCell(4); Row xlsRow5 = xlsxSheet.createRow(4); Cell xlsCell51 = xlsRow5.createCell(0); xlsCell51.setCellValue("example"); Cell xlsCell52 = xlsRow5.createCell(1); xlsCell52.setCellValue("noun1"); Cell xlsCell53 = xlsRow5.createCell(2); xlsCell53.setCellValue("ex-"); Cell xlsCell54 = xlsRow5.createCell(3); xlsCell54.setCellValue("noun2"); xlsRow5.createCell(4); Row xlsxRow6 = xlsxSheet.createRow(5); xlsxRow6.createCell(0); xlsxRow6.createCell(1); Cell xlsxCell63 = xlsxRow6.createCell(2); xlsxCell63.setCellValue("ample"); xlsxRow6.createCell(3); CellRangeAddress mergedPrim = new CellRangeAddress(4, 5, 0, 0); xlsxSheet.addMergedRegion(mergedPrim); CellRangeAddress mergedAnno1 = new CellRangeAddress(4, 5, 1, 1); xlsxSheet.addMergedRegion(mergedAnno1); CellRangeAddress mergedAnno2 = new CellRangeAddress(4, 5, 3, 3); xlsxSheet.addMergedRegion(mergedAnno2); xlsxRow6.createCell(4); Row xlsxRow7 = xlsxSheet.createRow(6); Cell xlsxCell71 = xlsxRow7.createCell(0); xlsxCell71.setCellValue("."); Cell xlsxCell72 = xlsxRow7.createCell(1); xlsxCell72.setCellValue("punct1"); Cell xlsxCell73 = xlsxRow7.createCell(2); xlsxCell73.setCellValue("."); Cell xlsxCell74 = xlsxRow7.createCell(3); xlsxCell74.setCellValue("punct2"); xlsxRow7.createCell(4); CellRangeAddress mergedAnnoLb = new CellRangeAddress(1, 6, 4, 4); xlsxSheet.addMergedRegion(mergedAnnoLb); xlsxWb.write(outStream); return outStream; }