List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getColumnIndex
@Override
public int getColumnIndex()
From source file:de.viaboxx.nlstools.formats.MBExcelPersistencer.java
License:Apache License
private Comment comment(HSSFCell cell, String text) { CreationHelper factory = wb.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = factory.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(cell.getRow().getRowNum()); anchor.setRow2(cell.getRow().getRowNum() + 3); Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(text); comment.setString(str);//from w w w . j ava 2 s . co m cell.setCellComment(comment); return comment; }
From source file:edu.ku.brc.specify.tasks.subpane.wb.XLSImport.java
License:Open Source License
/** * @param cell/*from ww w . j a v a 2 s .c o m*/ * @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:eionet.gdem.conversion.excel.writer.ExcelConversionHandler.java
License:Mozilla Public License
@Override public void addCell(String type, String str_value, String style_name) { HSSFSheet _sheet = wb.getSheetAt(currentSheet); HSSFRow _row = _sheet.getRow(currentRow); HSSFCell _cell = _row.createCell((currentCell)); Double number_value = null;//from w w w .jav a 2 s .c om Boolean boolean_value = null; boolean isNumber = false; boolean isBoolean = false; if (type == null) { type = (String) getDefaultParams("data_type"); } if (type != null) { if (type.equals("float") || type.equals("number")) { if (str_value != null) { try { number_value = new Double(str_value); isNumber = true; } catch (Exception e) { // the value is not number, it will be inserted as a string // System.out.println(e.toString()); } } else { isNumber = true; } } else if (type.equals("boolean")) { if (str_value != null) { try { boolean_value = new Boolean(str_value); isBoolean = true; } catch (Exception e) { // the value is not boolean, it will be inserted as a string // System.out.println(e.toString()); } } else { isBoolean = true; } } else if (type.equals("date")) { if (str_value != null) { try { // cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyymmdd")); /* * * The way how to handle user defined formats not supported right now HSSFDataFormat format = * wb.createDataFormat(); HSSFCellStyle style = wb.createCellStyle(); * style.setDataFormat(format.getFormat("yyyymmdd")); _cell.setCellStyle(style); */ // cellStyle.setDataFormat(new HSSFDataFormat("yyyymmdd")); /* * try{ l_value=Long.parseLong(value); System.out.println(String.valueOf(l_value)); isLong=true; } * catch(Exception e){ System.out.println(e.toString()); } */ /* * if (isLong){ Date d = new Date(); _cell.setCellStyle(cellStyle); //_cell.setCellValue(d); * _cell.setCellValue(value); //System.out.println(d.toString()); isDate=true; } else * _cell.setCellValue(value); */ // System.out.println("hh"); } catch (Exception e) { System.out.println(e.toString()); } } } } if (isNumber) { if (number_value != null) { _cell.setCellValue(number_value.doubleValue()); } _cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); } else if (isBoolean) { if (boolean_value != null) { _cell.setCellValue(boolean_value.booleanValue()); } _cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN); } else { _cell.setCellType(HSSFCell.CELL_TYPE_STRING); // _cell.setEncoding(HSSFCell.ENCODING_UTF_16 );// _cell.setCellValue(str_value); } short idx = -1; if (style_name != null) { idx = getStyleIdxByName(style_name, ExcelStyleIF.STYLE_FAMILY_TABLE_CELL); } if (idx < 0) { Short short_idx = (Short) getDefaultParams("style"); if (short_idx != null) { idx = short_idx.shortValue(); } } if (idx > -1) { _cell.setCellStyle(wb.getCellStyleAt(idx)); } // calculates the col with according to the first row if (currentRow == 0 && idx > -1) { short colStyleWidth = 0; HSSFCellStyle style = wb.getCellStyleAt(idx); int f_i = style.getFontIndex(); HSSFFont font = wb.getFontAt((short) f_i); // character size short size = font.getFontHeightInPoints(); if (columns.size() > currentCell) { RowColumnDefinition column = columns.get(currentCell); String column_style_name = column.getStyleName() == null ? "" : column.getStyleName(); ExcelStyleIF definedStyle = getStyleByName(column_style_name, ExcelStyleIF.STYLE_FAMILY_TABLE_CELL); if (definedStyle != null) { colStyleWidth = definedStyle.getColumnWidth(); } } short width = (short) (_sheet.getDefaultColumnWidth() * size * 25); if (colStyleWidth > 0) { width = colStyleWidth; } else if (str_value.length() > 0) { width = (short) (str_value.length() * size * 50); } _sheet.setColumnWidth(currentCell, width); } currentCell = _cell.getColumnIndex() + 1; // System.out.println("Cell" + currentCell+ "-" + value); }
From source file:fr.ens.transcriptome.aozan.io.CasavaDesignXLSReader.java
License:Open Source License
@Override public CasavaDesign read() throws IOException { // create a POIFSFileSystem object to read the data final POIFSFileSystem fs = new POIFSFileSystem(this.is); // Create a workbook out of the input stream final HSSFWorkbook wb = new HSSFWorkbook(fs); // Get a reference to the worksheet final HSSFSheet sheet = wb.getSheetAt(0); // When we have a sheet object in hand we can iterator on // each sheet's rows and on each row's cells. final Iterator<Row> rows = sheet.rowIterator(); final List<String> fields = new ArrayList<>(); while (rows.hasNext()) { final HSSFRow row = (HSSFRow) rows.next(); final Iterator<Cell> cells = row.cellIterator(); while (cells.hasNext()) { final HSSFCell cell = (HSSFCell) cells.next(); while (fields.size() != cell.getColumnIndex()) { fields.add(""); }//from w w w. j ava 2s . co m // Convert cell value to String fields.add(parseCell(cell)); } // Parse the fields if (!isFieldsEmpty(fields)) { parseLine(fields); } fields.clear(); } this.is.close(); return getDesign(); }
From source file:guineu.data.parser.impl.ParserXLS.java
License:Open Source License
public int v_type(HSSFWorkbook wb, HSSFRow row, HSSFCell cell) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: System.out.println(" Error - Row: " + row.getRowNum() + " Cell: " + cell.getColumnIndex() + " - Cell type Blank - " + cell.toString()); return 0; case HSSFCell.CELL_TYPE_BOOLEAN: System.out.println(" Error - Row: " + row.getRowNum() + " Cell: " + cell.getColumnIndex() + " - Cell type boolean - " + cell.toString()); return 0; case HSSFCell.CELL_TYPE_FORMULA: System.out.println(" Error - Row: " + row.getRowNum() + " Cell: " + cell.getColumnIndex() + " - Cell type formula - " + cell.toString()); return 1; case HSSFCell.CELL_TYPE_NUMERIC: HSSFCellStyle style = cell.getCellStyle(); HSSFFont font = wb.getFontAt(style.getFontIndex()); if (font.getColor() == (new HSSFColor.RED().getIndex())) { return 0; }//w w w . j a v a 2 s .com return 1; case HSSFCell.CELL_TYPE_STRING: style = cell.getCellStyle(); font = wb.getFontAt(style.getFontIndex()); if (font.getColor() == (new HSSFColor.RED().getIndex())) { return 0; } return 2; default: return 0; } }
From source file:Import.Utils.XSSFConvert.java
/** * @param srcSheet the sheet to copy./*from www.j av a 2 s . c o m*/ * @param destSheet the sheet to create. * @param srcRow the row to copy. * @param destRow the row to create. * @param styleMap - */ public static void copyRow(HSSFSheet srcSheet, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { // manage a list of merged zone in order to not insert two times a // merged zone Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>(); destRow.setHeight(srcRow.getHeight()); // pour chaque row for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { HSSFCell oldCell = srcRow.getCell(j); // ancienne cell XSSFCell newCell = destRow.getCell(j); // new cell if (oldCell != null) { if (newCell == null) { newCell = destRow.createCell(j); } // copy chaque cell copyCell(oldCell, newCell, styleMap); // copy les informations de fusion entre les cellules // System.out.println("row num: " + srcRow.getRowNum() + // " , col: " + (short)oldCell.getColumnIndex()); CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex()); if (mergedRegion != null) { // System.out.println("Selected merged region: " + // mergedRegion.toString()); CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn()); // System.out.println("New merged region: " + // newMergedRegion.toString()); CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion); if (isNewMergedRegion(wrapper, mergedRegions)) { mergedRegions.add(wrapper); destSheet.addMergedRegion(wrapper.range); } } } } }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * //w ww . java 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
/** * ?PageSize?//from w w w . j a v a 2 s.co m * @param sheet -- ? * @param startRow -- ???PageSize * @param rows -- ? * @return */ public static HSSFSheet insertSheetRow(HSSFSheet sheet, int startRow, int rows) { if (sheet == null) return null; sheet.shiftRows(startRow, sheet.getLastRowNum(), rows, true, false); HSSFCell sourcell = null, descell = null; HSSFRow sourow = sheet.getRow(startRow - 1); for (int i = 0; i < rows; i++) { HSSFRow descrow = sheet.createRow(startRow + i); 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); descell.setCellType(sourcell.getCellType()); descell.setCellStyle(sourcell.getCellStyle()); } } //?? insertSheetRegions(sheet, startRow, rows); return sheet; }
From source file:org.jxstar.report.util.ReportXlsUtil.java
/** * ??POI?ReportImageUtil/*from ww w.j av a2 s .c o m*/ * @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 w w w. j a v a 2s .c om * @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; }