List of usage examples for org.apache.poi.ss.util AreaReference getLastCell
public CellReference getLastCell()
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 . ja va 2s . c om * 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 av a 2 s . c o m*/ * 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 *//* w ww .ja v a2s .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.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public int[] getReferenceCoordinatesForName(String name) { Name cname = getName(name);/*from w ww. j ava 2 s . 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 DataFrame readNamedRegion(String name, boolean header, ReadStrategy readStrategy, DataType[] colTypes, boolean forceConversion, String dateTimeFormat, boolean takeCached, int[] subset) { Name cname = getName(name);/*from w w w. j a v a 2s .co 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);/* w w w .ja v a 2s .c o 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 clearRangeFromReference(String reference) { AreaReference ref = new AreaReference(reference); CellReference firstCell = ref.getFirstCell(); CellReference lastCell = ref.getLastCell(); String sheetName = firstCell.getSheetName(); int[] coords = { firstCell.getRow(), firstCell.getCol(), lastCell.getRow(), lastCell.getCol() }; clearRange(sheetName, coords);//from w w w . j a v a2 s . co m }
From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java
License:Apache License
private boolean extendTable(XSSFTable table, int firstRow, int firstCol, int lastRow) throws Exception { try {// w ww . j a v a 2 s .c o m AreaReference currentRef = null; if (currentType == SpreadsheetTyp.XLS) { currentRef = new AreaReference(table.getCTTable().getRef(), SpreadsheetVersion.EXCEL97); } else { currentRef = new AreaReference(table.getCTTable().getRef(), SpreadsheetVersion.EXCEL2007); } CellReference topLeft = currentRef.getFirstCell(); CellReference buttomRight = currentRef.getLastCell(); if (topLeft.getRow() <= firstRow && buttomRight.getRow() >= firstRow && topLeft.getCol() <= firstCol && buttomRight.getCol() >= firstCol) { // this table is within our write area, we have to expand it AreaReference newRef = new AreaReference(topLeft, // left top including the header line new CellReference(lastRow, buttomRight.getCol())); // bottom right table.getCTTable().setRef(newRef.formatAsString()); return true; } else { return false; } } catch (Exception t) { if (workbook instanceof SXSSFWorkbook) { throw new Exception( "Extending table ranges cannot work in a workbook which is not fully loaded because of the memory saving mode. Uncheck Memory saving mode in tFileExcelWorkbookOpen!", t); } else { throw t; } } }
From source file:nu.mine.kino.projects.utils.POI2Test.java
License:Open Source License
public void test3() throws InvalidFormatException, IOException { // String range = "A1:C5"; // CellRangeAddress address = CellRangeAddress.valueOf(range); // int firstRow = address.getFirstRow(); // int lastRow = address.getLastRow(); // int firstColumn = address.getFirstColumn(); // int lastColumn = address.getLastColumn(); // System.out.println(firstRow); // System.out.println(lastRow); // System.out.println(lastColumn); // System.out.println(firstColumn); // OwZ/*ww w . j a v a 2 s. c o m*/ Sheet sheet = workbook.getSheetAt(0); Name name = workbook.getName("??"); CellReference cellRef = new CellReference(name.getRefersToFormula()); Row row = sheet.getRow(cellRef.getRow()); Cell baseDateCell = row.getCell(cellRef.getCol()); System.out.println("cellt:" + PoiUtil.isCellDateFormatted(baseDateCell)); Date baseDate = baseDateCell.getDateCellValue(); System.out.println(baseDate); System.out.println(baseDateCell.getNumericCellValue()); // OwZ?B final Name DATA_AREA = workbook.getName("DATA_AREA"); final AreaReference areaReference = new AreaReference(DATA_AREA.getRefersToFormula()); final CellReference firstCell = areaReference.getFirstCell(); final CellReference lastCell = areaReference.getLastCell(); Row fRow = sheet.getRow(firstCell.getRow()); Row lRow = sheet.getRow(lastCell.getRow()); System.out.printf("??sIndex %s\n", firstCell.getRow()); System.out.printf("??I?sIndex %s\n", lastCell.getRow()); Cell fCell = fRow.getCell(firstCell.getCol()); Cell lCell = lRow.getCell(lastCell.getCol()); System.out.println(fCell); System.out.println(lCell); for (int index = firstCell.getRow(); index <= lastCell.getRow(); index++) { Row tmpRow = sheet.getRow(index); Cell tmpCell = tmpRow.getCell(firstCell.getCol()); // RRwColumnNumber?B System.out.println(tmpCell); } }