List of usage examples for org.apache.poi.ss.usermodel Sheet setColumnWidth
void setColumnWidth(int columnIndex, int width);
The maximum column width for an individual cell is 255 characters.
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 w w w. j a v a2 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.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 ww. j av a2s. c o m 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.bonitasoft.connectors.excel.AddDimensionedImage.java
License:Apache License
/** * Determines whether the sheets columns should be re-sized to accomodate * the image, adjusts the columns width if necessary and creates then * returns a ClientAnchorDetail object that facilitates construction of * an ClientAnchor that will fix the image on the sheet and establish * it's size./*from ww w . ja v a 2 s.c o m*/ * * @param sheet A reference to the sheet that will 'contain' the image. * @param colNumber A primtive int that contains the index number of a * column on the sheet. * @param reqImageWidthMM A primtive double that contains the required * width of the image in millimetres * @param resizeBehaviour A primitve int whose value will indicate how the * width of the column should be adjusted if the * required width of the image is greater than the * width of the column. * @return An instance of the ClientAnchorDetail class that will contain * the index number of the column containing the cell whose top * left hand corner also defines the top left hand corner of the * image, the index number column containing the cell whose top * left hand corner also defines the bottom right hand corner of * the image and an inset that determines how far the right hand * edge of the image can protrude into the next column - expressed * as a specific number of co-ordinate positions. */ private ClientAnchorDetail fitImageToColumns(Sheet sheet, int colNumber, double reqImageWidthMM, int resizeBehaviour) { double colWidthMM = 0.0D; double colCoordinatesPerMM = 0.0D; int pictureWidthCoordinates = 0; ClientAnchorDetail colClientAnchorDetail = null; // Get the colum's width in millimetres colWidthMM = ConvertImageUnits.widthUnits2Millimetres((short) sheet.getColumnWidth(colNumber)); // Check that the column's width will accomodate the image at the // required dimension. If the width of the column is LESS than the // required width of the image, decide how the application should // respond - resize the column or overlay the image across one or more // columns. if (colWidthMM < reqImageWidthMM) { // Should the column's width simply be expanded? if ((resizeBehaviour == AddDimensionedImage.EXPAND_COLUMN) || (resizeBehaviour == AddDimensionedImage.EXPAND_ROW_AND_COLUMN)) { // Set the width of the column by converting the required image // width from millimetres into Excel's column width units. sheet.setColumnWidth(colNumber, ConvertImageUnits.millimetres2WidthUnits(reqImageWidthMM)); // To make the image occupy the full width of the column, convert // the required width of the image into co-ordinates. This value // will become the inset for the ClientAnchorDetail class that // is then instantiated. colWidthMM = reqImageWidthMM; colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM; pictureWidthCoordinates = (int) (reqImageWidthMM * colCoordinatesPerMM); colClientAnchorDetail = new ClientAnchorDetail(colNumber, colNumber, pictureWidthCoordinates); } // If the user has chosen to overlay both rows and columns or just // to expand ONLY the size of the rows, then calculate how to lay // the image out across one or more columns. else if ((resizeBehaviour == AddDimensionedImage.OVERLAY_ROW_AND_COLUMN) || (resizeBehaviour == AddDimensionedImage.EXPAND_ROW)) { colClientAnchorDetail = this.calculateColumnLocation(sheet, colNumber, reqImageWidthMM); } } // If the column is wider than the image. else { // Mow many co-ordinate positions are there per millimetre? colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM; // Given the width of the image, what should be it's co-ordinate? pictureWidthCoordinates = (int) (reqImageWidthMM * colCoordinatesPerMM); colClientAnchorDetail = new ClientAnchorDetail(colNumber, colNumber, pictureWidthCoordinates); } return (colClientAnchorDetail); }
From source file:org.cerberus.service.export.ExportServiceFactory.java
License:Open Source License
private void createReportByTagExport(Workbook workbook) { //handles the export of the execution by tag data HashMap<String, SummaryStatisticsDTO> summaryMap = new HashMap<String, SummaryStatisticsDTO>(); HashMap<String, HashMap<String, List<TestCaseExecution>>> mapList = new HashMap<String, HashMap<String, List<TestCaseExecution>>>(); List<String> mapCountries = new ArrayList<String>(); List<CellStyle> stylesList = new LinkedList<CellStyle>(); if (exportOptions.contains("chart") || exportOptions.contains("list")) { //then we need to create the default colors for each cell HSSFWorkbook hwb = new HSSFWorkbook(); HSSFPalette palette = hwb.getCustomPalette(); CellStyle okStyle = workbook.createCellStyle(); // get the color which most closely matches the color you want to use // code to get the style for the cell goes here okStyle.setFillForegroundColor(palette.findSimilarColor(92, 184, 0).getIndex()); okStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); //okStyle.setFont(); stylesList.add(okStyle);/*from w ww . j a v a 2 s .c om*/ } for (TestCaseExecution execution : (List<TestCaseExecution>) list) { //check if the country and application shows if (exportOptions.contains("chart") || exportOptions.contains("summary")) { String keySummaryTable = execution.getApplication() + " " + execution.getCountry() + " " + execution.getEnvironment(); SummaryStatisticsDTO stats; String status = execution.getControlStatus(); if (summaryMap.containsKey(keySummaryTable)) { stats = summaryMap.get(keySummaryTable); } else { stats = new SummaryStatisticsDTO(); stats.setApplication(execution.getApplication()); stats.setCountry(execution.getCountry()); stats.setEnvironment(execution.getEnvironment()); } stats.updateStatisticByStatus(status); summaryMap.put(keySummaryTable, stats); //updates the map } if (exportOptions.contains("list")) { if (exportOptions.contains("filter")) { //filter active } else { //all data is saved } HashMap<String, List<TestCaseExecution>> listExecution; List<TestCaseExecution> testCaseList; String testKey = execution.getTest(); String testCaseKey = execution.getTestCase(); if (mapList.containsKey(testKey)) { listExecution = mapList.get(testKey); } else { listExecution = new HashMap<String, List<TestCaseExecution>>(); } if (listExecution.containsKey(testCaseKey)) { testCaseList = listExecution.get(testCaseKey); } else { testCaseList = new ArrayList<TestCaseExecution>(); } testCaseList.add(execution); listExecution.put(testCaseKey, testCaseList); mapList.put(testKey, listExecution); if (mapCountries.indexOf(execution.getCountry()) == -1) { mapCountries.add(execution.getCountry()); } } } int rowCount = -1; //Create a blank sheet Sheet sheet = workbook.createSheet("Report by Tag"); sheet.getPrintSetup().setLandscape(true); PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); //ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); sheet.setFitToPage(true); sheet.setColumnWidth(0, 9000); if (exportOptions.contains("chart")) { SummaryStatisticsDTO sumsTotal = calculateTotalValues(summaryMap); Row row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Report By Status"); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Status"); row.createCell(1).setCellValue("Total"); row.createCell(2).setCellValue("Percentage"); row = sheet.createRow(++rowCount); CellStyle okStyle = stylesList.get(0); Cell cellOk = row.createCell(0); cellOk.setCellValue("OK"); cellOk.setCellStyle(okStyle); row.createCell(1).setCellValue(sumsTotal.getOk()); row.createCell(2).setCellValue(sumsTotal.getPercOk()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("KO"); row.createCell(1).setCellValue(sumsTotal.getKo()); row.createCell(2).setCellValue(sumsTotal.getPercKo()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("FA"); row.createCell(1).setCellValue(sumsTotal.getFa()); row.createCell(2).setCellValue(sumsTotal.getPercFa()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("NA"); row.createCell(1).setCellValue(sumsTotal.getNa()); row.createCell(2).setCellValue(sumsTotal.getPercNa()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("NE"); row.createCell(1).setCellValue(sumsTotal.getNe()); row.createCell(2).setCellValue(sumsTotal.getPercNe()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("PE"); row.createCell(1).setCellValue(sumsTotal.getPe()); row.createCell(2).setCellValue(sumsTotal.getPercPe()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("CA"); row.createCell(1).setCellValue(sumsTotal.getCa()); row.createCell(2).setCellValue(sumsTotal.getPercCa()); row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Total"); row.createCell(1).setCellValue(sumsTotal.getTotal()); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); } if (exportOptions.contains("summary")) { //draw the table with data Row row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Summary Table"); //start creating data row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Application"); row.createCell(1).setCellValue("Country"); row.createCell(2).setCellValue("Environment"); row.createCell(3).setCellValue("OK"); row.createCell(4).setCellValue("KO"); row.createCell(5).setCellValue("FA"); row.createCell(6).setCellValue("NA"); row.createCell(7).setCellValue("NE"); row.createCell(8).setCellValue("PE"); row.createCell(9).setCellValue("CA"); row.createCell(10).setCellValue("NOT OK"); row.createCell(11).setCellValue("Total"); /*temporary styles*/ CellStyle styleBlue = workbook.createCellStyle(); CellStyle styleGreen = workbook.createCellStyle(); HSSFWorkbook hwb = new HSSFWorkbook(); HSSFPalette palette = hwb.getCustomPalette(); // get the color which most closely matches the color you want to use HSSFColor myColor = palette.findSimilarColor(66, 139, 202); // get the palette index of that color short palIndex = myColor.getIndex(); // code to get the style for the cell goes here styleBlue.setFillForegroundColor(palIndex); styleBlue.setFillPattern(CellStyle.SPARSE_DOTS); HSSFColor myColorGreen = palette.findSimilarColor(92, 184, 0); styleGreen.setFillForegroundColor(myColorGreen.getIndex()); styleGreen.setFillPattern(CellStyle.SPARSE_DOTS); int startRow = (rowCount + 2); TreeMap<String, SummaryStatisticsDTO> sortedSummaryMap = new TreeMap<String, SummaryStatisticsDTO>( summaryMap); for (String key : sortedSummaryMap.keySet()) { row = sheet.createRow(++rowCount); SummaryStatisticsDTO sumStats = summaryMap.get(key); //application row.createCell(0).setCellValue((String) sumStats.getApplication()); //country row.createCell(1).setCellValue((String) sumStats.getCountry()); //environment row.createCell(2).setCellValue((String) sumStats.getEnvironment()); //OK row.createCell(3).setCellValue(sumStats.getOk()); //KO row.createCell(4).setCellValue(sumStats.getKo()); //FA row.createCell(5).setCellValue(sumStats.getFa()); //NA row.createCell(6).setCellValue(sumStats.getNa()); //NE row.createCell(7).setCellValue(sumStats.getNe()); //PE row.createCell(8).setCellValue(sumStats.getPe()); //CA row.createCell(9).setCellValue(sumStats.getCa()); int rowNumber = row.getRowNum() + 1; //NOT OK //row.createCell(11).setCellValue(sumStats.getNotOkTotal()); row.createCell(10).setCellFormula("SUM(E" + rowNumber + ":J" + rowNumber + ")"); //Total row.createCell(11).setCellFormula("SUM(D" + rowNumber + ",K" + rowNumber + ")"); //row.createCell(12).setCellValue(sumStats.getTotal()); if (sumStats.getOk() == sumStats.getTotal()) { for (int i = 0; i < 12; i++) { row.getCell(i).setCellStyle(styleGreen); } } } //TODO:FN percentages missing //Total row row = sheet.createRow(++rowCount); row.createCell(0).setCellValue("Total"); row.createCell(1).setCellValue(""); row.createCell(2).setCellValue(""); //OK row.createCell(3).setCellFormula("SUM(D" + startRow + ":D" + rowCount + ")"); //KO row.createCell(4).setCellFormula("SUM(E" + startRow + ":E" + rowCount + ")"); //FA row.createCell(5).setCellFormula("SUM(F" + startRow + ":F" + rowCount + ")"); //NA row.createCell(6).setCellFormula("SUM(G" + startRow + ":G" + rowCount + ")"); //NE row.createCell(7).setCellFormula("SUM(H" + startRow + ":H" + rowCount + ")"); //PE row.createCell(8).setCellFormula("SUM(I" + startRow + ":I" + rowCount + ")"); //CA row.createCell(9).setCellFormula("SUM(J" + startRow + ":J" + rowCount + ")"); int rowNumberTotal = row.getRowNum() + 1; //NOT OK row.createCell(10).setCellFormula("SUM(E" + rowNumberTotal + ":J" + rowNumberTotal + ")"); //Total row.createCell(11).setCellFormula("SUM(D" + rowNumberTotal + ",K" + rowNumberTotal + ")"); for (int i = 0; i < 12; i++) { row.getCell(i).setCellStyle(styleBlue); } //add some empty rows sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); sheet.createRow(++rowCount).createCell(0).setCellValue(""); } if (exportOptions.contains("list")) { //exports the data from test cases' executions Row r = sheet.createRow(++rowCount); r.createCell(0).setCellValue("Test"); r.createCell(1).setCellValue("Test Case"); r.createCell(2).setCellValue("Description"); r.createCell(3).setCellValue("Application"); r.createCell(4).setCellValue("Environment"); r.createCell(5).setCellValue("Browser"); //creates the country list Collections.sort(mapCountries);//sorts the list of countries int startIndexForCountries = 6; for (String country : mapCountries) { r.createCell(startIndexForCountries).setCellValue(country); startIndexForCountries++; } TreeMap<String, HashMap<String, List<TestCaseExecution>>> sortedKeys = new TreeMap<String, HashMap<String, List<TestCaseExecution>>>( mapList); rowCount++; for (String keyMapList : sortedKeys.keySet()) { rowCount = createRow(keyMapList, mapList.get(keyMapList), sheet, rowCount, mapCountries); } } }
From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.BaseXLS.java
License:Open Source License
/** * This method writes string value with hyperlink url into a specific cell. * /* ww w .j av a2 s .c om*/ * @param sheet is the sheet where you want to add information into. * @param value is the specific information to be written. * @param link is the specific link with the to reference */ public void writeHyperlink(Sheet sheet, String value, XSSFHyperlink link) { // Set description sheet.setColumnWidth(columnCounter, 5000); this.prepareCell(sheet); cell.setCellValue(value); cell.setHyperlink(link); }
From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.BaseXLS.java
License:Open Source License
/** * This method writes string founded value into a specific cell * /* w ww. jav a2 s. c o m*/ * @param sheet is the sheet where you want to add information into. * @param value is the specific information to be written. * @param terms terms to compare the string. */ public void writeSearchString(Sheet sheet, String text, String[] terms) { this.prepareCell(sheet); StringTokenizer tokens; String token; int begin = 0; XSSFRichTextString richText = new XSSFRichTextString(); if (text == null) { cell.setCellValue(""); } else { // tokens = new StringTokenizer(text); while (tokens.hasMoreTokens()) { token = tokens.nextToken().toLowerCase(); richText.append(token); begin = richText.length() - token.length(); for (Point point : this.auxiliarRichText(token, terms)) { richText.applyFont(begin + point.x, begin + point.y + 1, this.richTextFont); } richText.append(" "); } if (text.toString().length() > 30) { sheet.setColumnWidth(columnCounter, 12000); } cell.setCellValue(richText); } }
From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.BaseXLS.java
License:Open Source License
/** * This method writes string value into a specific cell. * /*from w ww . ja v a2 s .com*/ * @param sheet is the sheet where you want to add information into. * @param value is the specific information to be written. */ public void writeString(Sheet sheet, String value) { this.prepareCell(sheet); if (value == null) { cell.setCellValue(""); } else { if (value.toString().length() > 30) { sheet.setColumnWidth(columnCounter, 12000); } else { sheet.setColumnWidth(columnCounter, 8000); } cell.setCellValue(value); } }
From source file:org.efaps.esjp.common.file.FileUtil_Base.java
License:Apache License
/** * Copy sheets.//from www. j a va2 s. c o m * * @param _newSheet the new sheet * @param _sheet the sheet * @param _copyStyle the copy style */ protected void copySheets(final Sheet _newSheet, final Sheet _sheet, final boolean _copyStyle) { int maxColumnNum = 0; final Map<Integer, CellStyle> styleMap = _copyStyle ? new HashMap<>() : null; for (int i = _sheet.getFirstRowNum(); i <= _sheet.getLastRowNum(); i++) { final Row srcRow = _sheet.getRow(i); final Row destRow = _newSheet.createRow(i); if (srcRow != null) { copyRow(_sheet, _newSheet, srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); } } } for (int i = 0; i <= maxColumnNum; i++) { _newSheet.setColumnWidth(i, _sheet.getColumnWidth(i)); } }
From source file:org.forzaframework.util.ExcelUtils.java
License:Apache License
public static void setColumnsWidth(Sheet sheet, Map<Integer, Integer> columnWidthMap, Integer numberOfColumns) { //Colocamos las columnas con el ancho correcto for (Integer i = 0; i < numberOfColumns; i++) { //Obtenemos el maximo numero de caracteres de la columna Integer columnWidth = columnWidthMap.get(i) + 1; columnWidth = columnWidth > 100 ? 100 : columnWidth; //multiplicamos por 256 porque es lo que representa un caracter en excel sheet.setColumnWidth(i, columnWidth * 256); //TODO: Esta es otra forma dar el ancho de la columna correctamente, probar si es mas optimo // sheet.autoSizeColumn(i); // sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 256); }//from w w w . j a va2 s . c om }
From source file:org.h819.commons.file.excel.poi.examples.AddDimensionedImage.java
License:Apache License
/** * Determines whether the sheets columns should be re-sized to accomodate * the image, adjusts the columns width if necessary and creates then * returns a ClientAnchorDetail object that facilitates construction of an * ClientAnchor that will fix the image on the sheet and establish it's * size./*from w ww .ja v a 2 s . c o m*/ * * @param sheet * A reference to the sheet that will 'contain' the image. * @param colNumber * A primtive int that contains the index number of a column on * the sheet. * @param reqImageWidthMM * A primtive double that contains the required width of the * image in millimetres * @param resizeBehaviour * A primitve int whose value will indicate how the width of the * column should be adjusted if the required width of the image * is greater than the width of the column. * @return An instance of the ClientAnchorDetail class that will contain the * index number of the column containing the cell whose top left * hand corner also defines the top left hand corner of the image, * the index number column containing the cell whose top left hand * corner also defines the bottom right hand corner of the image and * an inset that determines how far the right hand edge of the image * can protrude into the next column - expressed as a specific * number of co-ordinate positions. */ private ClientAnchorDetail fitImageToColumns(Sheet sheet, int colNumber, double reqImageWidthMM, int resizeBehaviour) { double colWidthMM = 0.0D; double colCoordinatesPerMM = 0.0D; int pictureWidthCoordinates = 0; ClientAnchorDetail colClientAnchorDetail = null; // Get the colum's width in millimetres colWidthMM = ConvertImageUnits.widthUnits2Millimetres((short) sheet.getColumnWidth(colNumber)); // Check that the column's width will accomodate the image at the // required dimension. If the width of the column is LESS than the // required width of the image, decide how the application should // respond - resize the column or overlay the image across one or more // columns. if (colWidthMM < reqImageWidthMM) { // Should the column's width simply be expanded? if ((resizeBehaviour == AddDimensionedImage.EXPAND_COLUMN) || (resizeBehaviour == AddDimensionedImage.EXPAND_ROW_AND_COLUMN)) { // Set the width of the column by converting the required image // width from millimetres into Excel's column width units. sheet.setColumnWidth(colNumber, ConvertImageUnits.millimetres2WidthUnits(reqImageWidthMM)); // To make the image occupy the full width of the column, // convert // the required width of the image into co-ordinates. This value // will become the inset for the ClientAnchorDetail class that // is then instantiated. colWidthMM = reqImageWidthMM; colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM; pictureWidthCoordinates = (int) (reqImageWidthMM * colCoordinatesPerMM); colClientAnchorDetail = new ClientAnchorDetail(colNumber, colNumber, pictureWidthCoordinates); } // If the user has chosen to overlay both rows and columns or just // to expand ONLY the size of the rows, then calculate how to lay // the image out across one or more columns. else if ((resizeBehaviour == AddDimensionedImage.OVERLAY_ROW_AND_COLUMN) || (resizeBehaviour == AddDimensionedImage.EXPAND_ROW)) { colClientAnchorDetail = this.calculateColumnLocation(sheet, colNumber, reqImageWidthMM); } } // If the column is wider than the image. else { // Mow many co-ordinate positions are there per millimetre? colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM; // Given the width of the image, what should be it's co-ordinate? pictureWidthCoordinates = (int) (reqImageWidthMM * colCoordinatesPerMM); colClientAnchorDetail = new ClientAnchorDetail(colNumber, colNumber, pictureWidthCoordinates); } return (colClientAnchorDetail); }