List of usage examples for org.apache.poi.ss.util AreaReference getFirstCell
public CellReference getFirstCell()
From source file:com.haulmont.yarg.formatters.impl.xls.Area.java
License:Apache License
public Area(AreaReference areaReference) { topLeft = new Cell(areaReference.getFirstCell()); bottomRight = new Cell(areaReference.getLastCell()); }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
/** * Method writes horizontal band/*from w w w. java2s .com*/ * Note: Only one band for row is supported. Now we think that many bands for row aren't usable. * * @param band - band to write * @param templateSheet - template sheet * @param resultSheet - result sheet */ protected void writeHorizontalBand(BandData band, HSSFSheet templateSheet, HSSFSheet resultSheet) { String rangeName = band.getName(); AreaReference templateRange = getAreaForRange(templateWorkbook, rangeName); if (templateRange == null) { throw wrapWithReportingException(String.format("No such named range in xls file: %s", rangeName)); } CellReference[] crefs = templateRange.getAllReferencedCells(); CellReference topLeft, bottomRight; AreaReference resultRange; int rowsAddedByHorizontalBandBackup = rowsAddedByHorizontalBand; int rownumBackup = rownum; if (crefs != null) { addRangeBounds(band, crefs); ArrayList<HSSFRow> resultRows = new ArrayList<HSSFRow>(); int currentRowNum = -1; int currentRowCount = -1; int currentColumnCount = 0; int offset = 0; topLeft = new CellReference(rownum + rowsAddedByHorizontalBand, 0); // no child bands - merge regions now if (band.getChildrenList().isEmpty()) { copyMergeRegions(resultSheet, rangeName, rownum + rowsAddedByHorizontalBand, getCellFromReference(crefs[0], templateSheet).getColumnIndex()); } for (CellReference cellRef : crefs) { HSSFCell templateCell = getCellFromReference(cellRef, templateSheet); HSSFRow resultRow; if (templateCell.getRowIndex() != currentRowNum) { //create new row resultRow = resultSheet.createRow(rownum + rowsAddedByHorizontalBand); copyPageBreaks(templateSheet, resultSheet, templateCell.getRowIndex(), resultRow.getRowNum()); rowsAddedByHorizontalBand += 1; //todo move to options if (templateCell.getCellStyle().getParentStyle() != null && templateCell.getCellStyle().getParentStyle().getUserStyleName() != null && templateCell.getCellStyle().getParentStyle().getUserStyleName() .equals(DYNAMIC_HEIGHT_STYLE)) { //resultRow.setHeight(templateCell.getRow().getHeight()); } else { resultRow.setHeight(templateCell.getRow().getHeight()); } resultRows.add(resultRow); currentRowNum = templateCell.getRowIndex(); currentRowCount++; currentColumnCount = 0; offset = templateCell.getColumnIndex(); } else { // or write cell to current row resultRow = resultRows.get(currentRowCount); currentColumnCount++; } copyCellFromTemplate(templateCell, resultRow, offset + currentColumnCount, band); } bottomRight = new CellReference(rownum + rowsAddedByHorizontalBand - 1, offset + currentColumnCount); resultRange = new AreaReference(topLeft, bottomRight); areaDependencyManager.addDependency(new Area(band.getName(), Area.AreaAlign.HORIZONTAL, templateRange), new Area(band.getName(), Area.AreaAlign.HORIZONTAL, resultRange)); bandsToResultRanges.put(band, new Range(resultSheet.getSheetName(), resultRange.getFirstCell().getCol() + 1, resultRange.getFirstCell().getRow() + 1, resultRange.getLastCell().getCol() + 1, resultRange.getLastCell().getRow() + 1)); } for (BandData child : band.getChildrenList()) { writeBand(child); } // scheduled merge regions if (!band.getChildrenList().isEmpty() && crefs != null) { copyMergeRegions(resultSheet, rangeName, rownumBackup + rowsAddedByHorizontalBandBackup, getCellFromReference(crefs[0], templateSheet).getColumnIndex()); } rownum += rowsAddedByHorizontalBand; rowsAddedByHorizontalBand = 0; rownum += rowsAddedByVerticalBand; rowsAddedByVerticalBand = 0; }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
/** * Method writes vertical band/*w w w . j a va 2 s . c om*/ * Note: no child support for vertical band ;) * * @param band - band to write * @param templateSheet - template sheet * @param resultSheet - result sheet */ protected void writeVerticalBand(BandData band, HSSFSheet templateSheet, HSSFSheet resultSheet) { String rangeName = band.getName(); CellReference[] crefs = getRangeContent(templateWorkbook, rangeName); Set<Integer> addedRowNumbers = new HashSet<Integer>(); if (crefs != null) { addRangeBounds(band, crefs); Bounds thisBounds = templateBounds.get(band.getName()); Bounds parentBounds = templateBounds.get(band.getParentBand().getName()); Range parentRange = bandsToResultRanges.get(band.getParentBand()); int localRowNum = parentBounds != null && parentRange != null ? parentRange.getFirstRow() - 1 + thisBounds.row0 - parentBounds.row0 : rownum; colnum = colnum == 0 ? getCellFromReference(crefs[0], templateSheet).getColumnIndex() : colnum; copyMergeRegions(resultSheet, rangeName, localRowNum, colnum); int firstRow = crefs[0].getRow(); int firstColumn = crefs[0].getCol(); for (CellReference cref : crefs) {//create necessary rows int currentRow = cref.getRow(); final int rowOffset = currentRow - firstRow; if (!rowExists(resultSheet, localRowNum + rowOffset)) { HSSFRow resultRow = resultSheet.createRow(localRowNum + rowOffset); copyPageBreaks(templateSheet, resultSheet, cref.getRow(), resultRow.getRowNum()); } addedRowNumbers.add(cref.getRow()); } CellReference topLeft = null; CellReference bottomRight = null; for (CellReference cref : crefs) { int currentRow = cref.getRow(); int currentColumn = cref.getCol(); final int rowOffset = currentRow - firstRow; final int columnOffset = currentColumn - firstColumn; HSSFCell templateCell = getCellFromReference(cref, templateSheet); resultSheet.setColumnWidth(colnum + columnOffset, templateSheet.getColumnWidth(templateCell.getColumnIndex())); HSSFCell resultCell = copyCellFromTemplate(templateCell, resultSheet.getRow(localRowNum + rowOffset), colnum + columnOffset, band); if (topLeft == null) { topLeft = new CellReference(resultCell); } bottomRight = new CellReference(resultCell); } colnum += crefs[crefs.length - 1].getCol() - firstColumn + 1; AreaReference templateRange = getAreaForRange(templateWorkbook, rangeName); AreaReference resultRange = new AreaReference(topLeft, bottomRight); areaDependencyManager.addDependency(new Area(band.getName(), Area.AreaAlign.VERTICAL, templateRange), new Area(band.getName(), Area.AreaAlign.VERTICAL, resultRange)); bandsToResultRanges.put(band, new Range(resultSheet.getSheetName(), resultRange.getFirstCell().getCol() + 1, resultRange.getFirstCell().getRow() + 1, resultRange.getLastCell().getCol() + 1, resultRange.getLastCell().getRow() + 1)); } //for first level vertical bands we should increase rownum by number of rows added by vertical band //nested vertical bands do not add rows, they use parent space if (BandData.ROOT_BAND_NAME.equals(band.getParentBand().getName())) { List<BandData> sameBands = band.getParentBand().getChildrenByName(band.getName()); if (sameBands.size() > 0 && sameBands.get(sameBands.size() - 1) == band) {//check if this vertical band is last vertical band with same name rownum += addedRowNumbers.size(); // rowsAddedByVerticalBand = 0; } } }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
/** * Method creates mapping [rangeName : List<CellRangeAddress>]. * List contains all merge regions for this named range. * Attention: if merged regions writes wrong - look on methods isMergeRegionInsideNamedRange or isNamedRangeInsideMergeRegion * todo: how to recognize if merge region must be copied with named range * * @param currentSheet Sheet which contains merge regions *//*from w w w . j a v a2 s . com*/ protected void initMergeRegions(HSSFSheet currentSheet) { int rangeNumber = templateWorkbook.getNumberOfNames(); for (int i = 0; i < rangeNumber; i++) { HSSFName aNamedRange = templateWorkbook.getNameAt(i); String refersToFormula = aNamedRange.getRefersToFormula(); if (!AreaReference.isContiguous(refersToFormula)) { continue; } AreaReference aref = new AreaReference(refersToFormula); Integer rangeFirstRow = aref.getFirstCell().getRow(); Integer rangeFirstColumn = (int) aref.getFirstCell().getCol(); Integer rangeLastRow = aref.getLastCell().getRow(); Integer rangeLastColumn = (int) aref.getLastCell().getCol(); for (int j = 0; j < currentSheet.getNumMergedRegions(); j++) { CellRangeAddress mergedRegion = currentSheet.getMergedRegion(j); if (mergedRegion != null) { Integer regionFirstRow = mergedRegion.getFirstRow(); Integer regionFirstColumn = mergedRegion.getFirstColumn(); Integer regionLastRow = mergedRegion.getLastRow(); Integer regionLastColumn = mergedRegion.getLastColumn(); boolean mergedInsideNamed = isMergeRegionInsideNamedRange(rangeFirstRow, rangeFirstColumn, rangeLastRow, rangeLastColumn, regionFirstRow, regionFirstColumn, regionLastRow, regionLastColumn); boolean namedInsideMerged = isNamedRangeInsideMergeRegion(rangeFirstRow, rangeFirstColumn, rangeLastRow, rangeLastColumn, regionFirstRow, regionFirstColumn, regionLastRow, regionLastColumn); if (mergedInsideNamed || namedInsideMerged) { String name = aNamedRange.getNameName(); SheetRange sheetRange = new SheetRange(mergedRegion, currentSheet.getSheetName()); if (mergeRegionsForRangeNames.get(name) == null) { ArrayList<SheetRange> list = new ArrayList<SheetRange>(); list.add(sheetRange); mergeRegionsForRangeNames.put(name, list); } else { mergeRegionsForRangeNames.get(name).add(sheetRange); } } } } } }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
/** * Create new merge regions in result sheet identically to range's merge regions from template. * Not support copy of frames and rules// w w w . j av a 2 s . co m * * @param resultSheet - result sheet * @param rangeName - range name * @param firstTargetRangeRow - first column of target range * @param firstTargetRangeColumn - first column of target range */ protected void copyMergeRegions(HSSFSheet resultSheet, String rangeName, int firstTargetRangeRow, int firstTargetRangeColumn) { int rangeNameIdx = templateWorkbook.getNameIndex(rangeName); if (rangeNameIdx == -1) return; HSSFName aNamedRange = templateWorkbook.getNameAt(rangeNameIdx); AreaReference aref = new AreaReference(aNamedRange.getRefersToFormula()); int column = aref.getFirstCell().getCol(); int row = aref.getFirstCell().getRow(); List<SheetRange> regionsList = mergeRegionsForRangeNames.get(rangeName); if (regionsList != null) for (SheetRange sheetRange : regionsList) { if (resultSheet.getSheetName().equals(sheetRange.getSheetName())) { CellRangeAddress cra = sheetRange.getCellRangeAddress(); if (cra != null) { int regionHeight = cra.getLastRow() - cra.getFirstRow() + 1; int regionWidth = cra.getLastColumn() - cra.getFirstColumn() + 1; int regionVOffset = cra.getFirstRow() - row; int regionHOffset = cra.getFirstColumn() - column; CellRangeAddress newRegion = cra.copy(); newRegion.setFirstColumn(regionHOffset + firstTargetRangeColumn); newRegion.setLastColumn(regionHOffset + regionWidth - 1 + firstTargetRangeColumn); newRegion.setFirstRow(regionVOffset + firstTargetRangeRow); newRegion.setLastRow(regionVOffset + regionHeight - 1 + firstTargetRangeRow); boolean skipRegion = false; for (int mergedIndex = 0; mergedIndex < resultSheet.getNumMergedRegions(); mergedIndex++) { CellRangeAddress mergedRegion = resultSheet.getMergedRegion(mergedIndex); if (!intersects(newRegion, mergedRegion)) { continue; } skipRegion = true; } if (!skipRegion) { resultSheet.addMergedRegion(newRegion); } } } } }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public int[] getReferenceCoordinatesForName(String name) { Name cname = getName(name);//from ww w .ja v a2s. c o m AreaReference aref = new AreaReference(cname.getRefersToFormula()); // Get upper left corner CellReference first = aref.getFirstCell(); // Get lower right corner CellReference last = aref.getLastCell(); int top = first.getRow(); int bottom = last.getRow(); int left = first.getCol(); int right = last.getCol(); return new int[] { top, left, bottom, right }; }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public void writeNamedRegion(DataFrame data, String name, boolean header) { Name cname = getName(name);/* w w w. ja v a 2s .c om*/ checkName(cname); // Get sheet where name is defined in Sheet sheet = workbook.getSheet(cname.getSheetName()); AreaReference aref = new AreaReference(cname.getRefersToFormula()); // Get upper left corner CellReference topLeft = aref.getFirstCell(); // Compute bottom right cell coordinates int bottomRightRow = Math.max(topLeft.getRow() + data.rows() - 1, topLeft.getRow()); if (header) ++bottomRightRow; int bottomRightCol = Math.max(topLeft.getCol() + data.columns() - 1, topLeft.getCol()); // Create bottom right cell reference CellReference bottomRight = new CellReference(sheet.getSheetName(), bottomRightRow, bottomRightCol, true, true); // Define named range area aref = new AreaReference(topLeft, bottomRight); // Redefine named range cname.setRefersToFormula(aref.formatAsString()); writeData(data, sheet, topLeft.getRow(), topLeft.getCol(), header); }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public DataFrame readNamedRegion(String name, boolean header, ReadStrategy readStrategy, DataType[] colTypes, boolean forceConversion, String dateTimeFormat, boolean takeCached, int[] subset) { Name cname = getName(name);/* w w w .jav a2 s. c o m*/ checkName(cname); // Get sheet where name is defined in Sheet sheet = workbook.getSheet(cname.getSheetName()); AreaReference aref = new AreaReference(cname.getRefersToFormula()); // Get name corners (top left, bottom right) CellReference topLeft = aref.getFirstCell(); CellReference bottomRight = aref.getLastCell(); // Determine number of rows and columns int nrows = bottomRight.getRow() - topLeft.getRow() + 1; int ncols = bottomRight.getCol() - topLeft.getCol() + 1; return readData(sheet, topLeft.getRow(), topLeft.getCol(), nrows, ncols, header, readStrategy, colTypes, forceConversion, dateTimeFormat, takeCached, subset); }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public void addImage(File imageFile, String name, boolean originalSize) throws FileNotFoundException, IOException { Name cname = getName(name);/*from w w w . ja v a2s . co m*/ // Get sheet where name is defined in Sheet sheet = workbook.getSheet(cname.getSheetName()); AreaReference aref = new AreaReference(cname.getRefersToFormula()); // Get name corners (top left, bottom right) CellReference topLeft = aref.getFirstCell(); CellReference bottomRight = aref.getLastCell(); // Determine image type int imageType; String filename = imageFile.getName().toLowerCase(); if (filename.endsWith("jpg") || filename.endsWith("jpeg")) { imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_JPEG; } else if (filename.endsWith("png")) { imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_PNG; } else if (filename.endsWith("wmf")) { imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_WMF; } else if (filename.endsWith("emf")) { imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_EMF; } else if (filename.endsWith("bmp") || filename.endsWith("dib")) { imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_DIB; } else if (filename.endsWith("pict") || filename.endsWith("pct") || filename.endsWith("pic")) { imageType = org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_PICT; } else throw new IllegalArgumentException( "Image type \"" + filename.substring(filename.lastIndexOf('.') + 1) + "\" not supported!"); InputStream is = new FileInputStream(imageFile); byte[] bytes = IOUtils.toByteArray(is); int imageIndex = workbook.addPicture(bytes, imageType); is.close(); Drawing drawing; if (isHSSF()) { drawing = ((HSSFSheet) sheet).getDrawingPatriarch(); if (drawing == null) { drawing = sheet.createDrawingPatriarch(); } } else if (isXSSF()) { drawing = ((XSSFSheet) sheet).createDrawingPatriarch(); } else { drawing = sheet.createDrawingPatriarch(); } CreationHelper helper = workbook.getCreationHelper(); ClientAnchor anchor = helper.createClientAnchor(); anchor.setRow1(topLeft.getRow()); anchor.setCol1(topLeft.getCol()); // +1 since we want to include the anchor.setRow2(bottomRight.getRow() + 1); anchor.setCol2(bottomRight.getCol() + 1); anchor.setAnchorType(ClientAnchor.DONT_MOVE_AND_RESIZE); Picture picture = drawing.createPicture(anchor, imageIndex); if (originalSize) picture.resize(); }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public void setCellStyle(String formula, CellStyle cs) { AreaReference aref = new AreaReference(formula); String sheetName = aref.getFirstCell().getSheetName(); if (sheetName == null) { throw new IllegalArgumentException("Invalid formula reference - should be of the form Sheet!A1:B10"); }/*from ww w . j a v a 2 s. c o m*/ Sheet sheet = getSheet(sheetName); CellReference[] crefs = aref.getAllReferencedCells(); for (CellReference cref : crefs) { Cell c = getCell(sheet, cref.getRow(), cref.getCol()); setCellStyle(c, cs); } }