List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getRowIndex
@Override public int getRowIndex()
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
private void fixDownBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, int rowIndex, HSSFCell resultCell) { // fix simple down border fixDownCell(sheet, rowIndex + 1, columnIndex, cellStyle); // fix merged down border for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) { int firstColumn = mergedRegion.getFirstColumn(); int lastColumn = mergedRegion.getLastColumn(); int regionHeight = mergedRegion.getLastRow() - mergedRegion.getFirstRow() + 1; for (int downIndex = firstColumn; downIndex <= lastColumn; downIndex++) { fixDownCell(sheet, rowIndex + regionHeight, downIndex, cellStyle); }//from w ww .j av a 2 s . co m break; } } }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
/** * Method writes horizontal band/*from w w w . j a v a2 s.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:edu.ku.brc.specify.tasks.subpane.wb.XLSImport.java
License:Open Source License
/** * @param cell// w w w. j a va 2s . c om * @param activeHyperlinks * @return the Hyperlink applicable for the cell. * * NOTE: This code assumes that hyperlinks' row and column ranges do not overlap. */ protected HSSFHyperlink checkHyperlinks(final HSSFCell cell, final Vector<HSSFHyperlink> activeHyperlinks) { if (cell.getHyperlink() != null) { HSSFHyperlink l = cell.getHyperlink(); if (l.getLastRow() > cell.getRowIndex() || l.getLastColumn() > cell.getColumnIndex()) { activeHyperlinks.add(l); } return l; } for (HSSFHyperlink hl : activeHyperlinks) { if (cell.getRowIndex() >= hl.getFirstRow() && cell.getRowIndex() <= hl.getLastRow() && cell.getColumnIndex() >= hl.getFirstColumn() && cell.getColumnIndex() <= hl.getLastColumn()) { if (cell.getRowIndex() == hl.getLastRow()) { activeHyperlinks.remove(hl); } return hl; } } return null; }
From source file:org.jtotus.database.FileSystemFromHex.java
License:Open Source License
public BigDecimal omxNordicFile(String fileName, DateTime calendar, int row) { BigDecimal result = null;/*from w w w .j av a 2s .c o m*/ try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(pathToDataBaseDir + fileName)); HSSFWorkbook workbook = new HSSFWorkbook(fs); HSSFSheet worksheet = workbook.getSheetAt(0); //HSSFRow row1 = worksheet.getRow(0); String correctTime = dateFormatter.print(calendar); Iterator rowIter = worksheet.rowIterator(); while (rowIter.hasNext()) { HSSFRow rows = (HSSFRow) rowIter.next(); HSSFCell cell = rows.getCell(0); String dateString = null; if (cell.getCellType() == Cell.CELL_TYPE_STRING) { dateString = cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { Date date = cell.getDateCellValue(); SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); dateString = format.format(date); // System.err.printf("File (%s) is corrucped ? type:%s\n", fileName, dateString); } else { System.err.printf("File (%s) is corrucped ? type:%d formula:%d\n", fileName, cell.getCellType(), Cell.CELL_TYPE_FORMULA); return null; } // help.debug("FileSystemFromHex","Searching:%s from:%s\n", correctTime, temp); if (correctTime.compareTo(dateString) == 0) { HSSFCell closingPrice = rows.getCell(row); if (closingPrice == null) return null; float floatTemp = (float) closingPrice.getNumericCellValue(); System.out.printf("FileSystemFromHex", "Closing price at:%d f:%.4f Time:%s\n", cell.getRowIndex(), floatTemp, correctTime); return new BigDecimal(floatTemp); } } } catch (IOException ex) { Logger.getLogger(FileSystemFromHex.class.getName()).log(Level.SEVERE, null, ex); } return result; }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * // w ww . j ava 2 s . c o m * @param mainSheet -- ? * @param subSheet -- ? * @param tempRow -- ????? * @return */ public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet, int tempRow) { if (mainSheet == null || subSheet == null) return null; //?? if (!isAllowOut(mainSheet)) return mainSheet; //? int endRowNum = mainSheet.getPhysicalNumberOfRows(); HSSFRow sourow = null, descrow = null; HSSFCell sourcell = null, descell = null, orgcell = null; int i = 0, offsetcnt = 0; //? copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook()); //?? CellRangeAddress range = null; int mergedNum = subSheet.getNumMergedRegions(); for (i = 0; i < mergedNum; i++) { range = subSheet.getMergedRegion(i); range.setFirstRow(range.getFirstRow() + endRowNum); range.setLastRow(range.getLastRow() + endRowNum); mainSheet.addMergedRegion(range); } range = null; //int k = 0; //? mainSheet.setAlternativeExpression(subSheet.getAlternateExpression()); mainSheet.setAlternativeFormula(subSheet.getAlternateFormula()); mainSheet.setAutobreaks(subSheet.getAutobreaks()); mainSheet.setDialog(subSheet.getDialog()); mainSheet.setDisplayGuts(subSheet.getDisplayGuts()); mainSheet.setFitToPage(subSheet.getFitToPage()); for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext();) { sourow = (HSSFRow) iterow.next(); offsetcnt = sourow.getRowNum() + endRowNum; descrow = mainSheet.createRow(offsetcnt); descrow.setHeight(sourow.getHeight()); descrow.setHeightInPoints(sourow.getHeightInPoints()); java.util.Iterator<Cell> iter = sourow.cellIterator(); while (iter.hasNext()) { sourcell = (HSSFCell) iter.next(); int column = sourcell.getColumnIndex(); descell = descrow.createCell(column); /** * ??????orgcell = mainSheet.getRow(row).getCell(column); * ?? * ??orgcell.getCellStyle()????sheet?? * This Style does not belong to the supplied Workbook. * ?descell.getCellStyle().cloneStyleFrom(sourcell.getCellStyle());???excel * HSSFCellStyle cs = mainSheet.getWorkbook().createCellStyle(); * cs.cloneStyleFrom(sourcell.getCellStyle()); * descell.setCellStyle(cs);//excel? * tempRow???? */ //???????? int row = sourcell.getRowIndex(); if (tempRow > 0 && row > tempRow) { row = tempRow; } orgcell = mainSheet.getRow(row).getCell(column); if (orgcell != null) { //orgcell.getCellType()???0 descell.setCellType(HSSFCell.CELL_TYPE_STRING); //??? descell.setCellStyle(orgcell.getCellStyle()); } else { _log.showWarn("module xls [{0}, {1}] cell is null!", row, column); } if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING) descell.setCellValue(sourcell.getStringCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) descell.setCellValue(sourcell.getNumericCellValue()); } sourow = null; sourcell = null; descrow = null; orgcell = null; } return mainSheet; }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * ??POI?ReportImageUtil//from w ww. j a va 2s . c om * @param cell -- ? * @param bytes -- */ public static void addImageToSheet(HSSFCell cell, byte[] bytes) { if (cell == null) { _log.showError("-----insertImageToSheet: cell is null!"); return; } if (bytes == null || bytes.length == 0) { _log.showError("-----insertImageToSheet: bytes is null!"); return; } //?? HSSFSheet sheet = cell.getSheet(); //? int firstRow = cell.getRowIndex(); int lastRow = cell.getRowIndex(); int firstCol = cell.getColumnIndex(); int lastCol = cell.getColumnIndex(); //?? CellRangeAddress range = getMergedRegion(cell); if (range != null) { firstRow = range.getFirstRow(); lastRow = range.getLastRow(); firstCol = range.getFirstColumn(); lastCol = range.getLastColumn(); } _log.showDebug("---------image cells=[" + firstRow + "," + firstCol + "," + lastRow + "," + lastCol + "]"); //????5??1023255 HSSFClientAnchor anchor = new HSSFClientAnchor(10, 5, 1013, 250, (short) firstCol, firstRow, (short) lastCol, lastRow); anchor.setAnchorType(HSSFClientAnchor.MOVE_AND_RESIZE); //?? HSSFPatriarch draw = sheet.getDrawingPatriarch(); if (draw == null) { draw = sheet.createDrawingPatriarch(); } //??? sheet.getWorkbook().addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG); //???????+1?? List<HSSFPicture> lsPicture = getAllPicture(sheet); int index = lsPicture.size() + 1; _log.showDebug("---------new image index=" + index); draw.createPicture(anchor, index); }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * ????/*from ww w. j a va2 s .com*/ * @param cell -- ? * @return */ private static CellRangeAddress getMergedRegion(HSSFCell cell) { HSSFSheet sheet = cell.getSheet(); CellRangeAddress range = null; int mergedNum = sheet.getNumMergedRegions(); for (int i = 0; i < mergedNum; i++) { range = sheet.getMergedRegion(i); if (range.getFirstColumn() == cell.getColumnIndex() && range.getFirstRow() == cell.getRowIndex()) { return range; } } return null; }
From source file:org.openmicroscopy.shoola.util.file.SheetInfo.java
License:Open Source License
/** Auto-sizes all columns to fit contents. */ void sizeAllColumnsToFit() { Map<Integer, Integer> colMap = new HashMap<Integer, Integer>(); Iterator rowIterator = sheet.iterator();//sheet.rowIterator(); HSSFCell cell; HSSFRow row;// w w w . j av a 2 s .c o m Iterator k; while (rowIterator.hasNext()) { row = (HSSFRow) rowIterator.next(); k = row.cellIterator(); while (k.hasNext()) { cell = (HSSFCell) k.next(); colMap.put(cell.getColumnIndex(), cell.getRowIndex()); } } Iterator<Integer> colIterator = colMap.keySet().iterator(); int col; while (colIterator.hasNext()) { col = colIterator.next(); sizeColumnToFit(col); } }
From source file:ypcnv.views.impl.FileXLS.java
License:Open Source License
/** * With respect to cell type get cell's content. * /*from www . java 2s .c o m*/ * @param cell * - cell to be processed. * @return Cell's content. */ private String getCellContent(HSSFCell cell) { int foundCellType = cell.getCellType(); String cellContent = null; switch (foundCellType) { case Cell.CELL_TYPE_STRING: cellContent = cell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: cellContent = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: cellContent = cell.getBooleanCellValue() ? "TRUE" : "FALSE"; break; case Cell.CELL_TYPE_ERROR: byte xlsErrorCode = cell.getErrorCellValue(); if (xlsErrorCode == 0) { cellContent = ""; } else { cellContent = "XLS error code '" + xlsErrorCode + "'."; String message = "XLS cell type is 'ERROR', the XLS error code is '" + xlsErrorCode + "'." + " The cell row=" + cell.getRowIndex() + ", col=" + cell.getColumnIndex() + "."; LOG.info(message); } break; case Cell.CELL_TYPE_NUMERIC: HSSFDataFormatter numericFormat = new HSSFDataFormatter(); cellContent = numericFormat.formatCellValue(cell); // cellContent = cell.getNumericCellValue(); break; default: cellContent = ""; break; } return cellContent; }