List of usage examples for org.apache.poi.ss.usermodel Sheet getWorkbook
Workbook getWorkbook();
From source file:org.isisaddons.module.excel.dom.CellMarshaller.java
License:Apache License
private static void setCellComment(final Cell cell, final String commentText) { Sheet sheet = cell.getSheet(); Row row = cell.getRow();//w w w .jav a 2 s.c om Workbook workbook = sheet.getWorkbook(); CreationHelper creationHelper = workbook.getCreationHelper(); ClientAnchor anchor = creationHelper.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(row.getRowNum()); anchor.setRow2(row.getRowNum() + 3); Drawing drawing = sheet.createDrawingPatriarch(); Comment comment1 = drawing.createCellComment(anchor); RichTextString commentRtf = creationHelper.createRichTextString(commentText); comment1.setString(commentRtf); Comment comment = comment1; cell.setCellComment(comment); }
From source file:org.jplus.hyberbin.excel.service.BaseExcelService.java
License:Apache License
/** * sheet ?/*from www .j a v a 2s . co m*/ * @param sheet * @param row * @param length * @param data */ public static void addTitle(Sheet sheet, int row, int length, String data) { Row sheetRow = sheet.createRow(row); for (int i = 0; i < length; i++) { sheetRow.createCell(i); } CellStyle style = sheet.getWorkbook().createCellStyle(); // ? style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// CellRangeAddress cellRangeAddress = new CellRangeAddress(row, row, 0, length - 1); sheet.addMergedRegion(cellRangeAddress); Cell cell = sheetRow.getCell(0); cell.setCellStyle(style); cell.setCellValue(data); }
From source file:org.netxilia.impexp.impl.ExcelExportService.java
License:Open Source License
@Override public void exportSheetTo(INetxiliaSystem workbookProcessor, SheetFullName sheetName, OutputStream out, IProcessingConsole console)/*w ww . j a va 2s . co m*/ throws ExportException, NetxiliaResourceException, NetxiliaBusinessException { Workbook poiWorkbook = new HSSFWorkbook(); Sheet poiSheet = poiWorkbook.createSheet(sheetName.getSheetName()); ISheet nxSheet = null; try { nxSheet = workbookProcessor.getWorkbook(sheetName.getWorkbookId()).getSheet(sheetName.getSheetName()); SheetData nxSheetData = nxSheet.receiveSheet().getNonBlocking(); for (AreaReference area : nxSheetData.getSpans()) { poiSheet.addMergedRegion(new CellRangeAddress(area.getFirstRowIndex(), area.getLastRowIndex(), area.getFirstColumnIndex(), area.getLastColumnIndex())); } // cells Matrix<CellData> nxCells = nxSheet.receiveCells(AreaReference.ALL).getNonBlocking(); int rowIndex = 0; for (List<CellData> nxRow : nxCells.getRows()) { Row poiRow = poiSheet.createRow(rowIndex); for (CellData nxCell : nxRow) { if (nxCell != null) { Cell poiCell = poiRow.createCell(nxCell.getReference().getColumnIndex()); try { copyCellValue(nxCell, poiCell); } catch (Exception ex) { if (console != null) { console.println("Error " + nxCell.getReference() + ":" + ex); } } } } rowIndex++; } // columns List<ColumnData> nxColumns = nxSheet.receiveColumns(Range.ALL).getNonBlocking(); for (int c = 0; c < nxColumns.size(); ++c) { ColumnData col = nxColumns.get(c); if (col.getWidth() > 0) { poiSheet.setColumnWidth(c, PoiUtils.pixel2WidthUnits(col.getWidth())); } PoiUtils.netxiliaStyle2Poi(col.getStyles(), poiSheet.getWorkbook(), poiSheet.getColumnStyle(c)); } } catch (StorageException e) { throw new ExportException(e); } // close the workbook try { poiWorkbook.write(out); } catch (IOException e) { throw new ExportException(e); } }
From source file:org.netxilia.impexp.impl.ExcelImportService.java
License:Open Source License
@Override public List<SheetFullName> importSheets(INetxiliaSystem workbookProcessor, WorkbookId workbookName, InputStream is, IProcessingConsole console) throws ImportException { List<SheetFullName> sheetNames = new ArrayList<SheetFullName>(); try {/*from w w w. j av a 2 s. co m*/ log.info("Starting import:" + workbookName); Workbook poiWorkbook = new HSSFWorkbook(is); IWorkbook nxWorkbook = workbookProcessor.getWorkbook(workbookName); log.info("Read POI"); NetxiliaStyleResolver styleResolver = new NetxiliaStyleResolver( styleService.getStyleDefinitions(workbookName)); HSSFPalette palette = ((HSSFWorkbook) poiWorkbook).getCustomPalette(); for (int s = 0; s < poiWorkbook.getNumberOfSheets(); ++s) { Sheet poiSheet = poiWorkbook.getSheetAt(s); SheetFullName sheetName = new SheetFullName(workbookName, getNextFreeSheetName(nxWorkbook, poiSheet.getSheetName())); ISheet nxSheet = null; BlockCellCommandBuilder cellCommandBuilder = new BlockCellCommandBuilder(); try { List<CellReference> refreshCells = new ArrayList<CellReference>(); for (Row poiRow : poiSheet) { if (poiRow.getRowNum() % 100 == 0) { log.info("importing row #" + poiRow.getRowNum()); } for (Cell poiCell : poiRow) { if (nxSheet == null) { // lazy creation while (true) { try { nxSheet = nxWorkbook.addNewSheet(sheetName.getSheetName(), SheetType.normal); nxSheet.setRefreshEnabled(false); break; } catch (AlreadyExistsException e) { // may happen is simultaneous imports take place sheetName = new SheetFullName(workbookName, getNextFreeSheetName(nxWorkbook, poiSheet.getSheetName())); } } } CellReference ref = new CellReference(sheetName.getSheetName(), poiRow.getRowNum(), poiCell.getColumnIndex()); try { ICellCommand cmd = copyCell(poiCell, ref, palette, styleResolver); if (cmd != null) { cellCommandBuilder.command(cmd); } if (poiCell.getCellType() == Cell.CELL_TYPE_FORMULA) { refreshCells.add(ref); } } catch (Exception e) { if (console != null) { console.println("Could import cell " + ref + ":" + poiCell + ":" + e); } log.error("Could import cell " + ref + ":" + poiCell + ":" + e, e); } } if (poiRow.getRowNum() % 100 == 0 && !cellCommandBuilder.isEmpty()) { nxSheet.sendCommandNoUndo(cellCommandBuilder.build()); cellCommandBuilder = new BlockCellCommandBuilder(); } } if (nxSheet == null) { // empty sheet continue; } if (!cellCommandBuilder.isEmpty()) { nxSheet.sendCommandNoUndo(cellCommandBuilder.build()); } // add the columns after as is not very clear how to get the number of cols in poi for (int c = 0; c < nxSheet.getDimensions().getNonBlocking().getColumnCount(); ++c) { int width = 50; try { width = PoiUtils.widthUnits2Pixel(poiSheet.getColumnWidth(c)); nxSheet.sendCommand(ColumnCommands.width(Range.range(c), width)); } catch (NullPointerException ex) { // ignore it // NPE in at org.apache.poi.hssf.model.Sheet.getColumnWidth(Sheet.java:998) // defaultColumnWidth can be null !? } CellStyle poiStyle = poiSheet.getColumnStyle(c); if (poiStyle == null) { continue; } Styles styles = PoiUtils.poiStyle2Netxilia(poiStyle, poiSheet.getWorkbook().getFontAt(poiStyle.getFontIndex()), palette, styleResolver); if (styles != null) { nxSheet.sendCommand(ColumnCommands.styles(Range.range(c), styles)); } } // merge List<AreaReference> spans = new ArrayList<AreaReference>(poiSheet.getNumMergedRegions()); for (int i = 0; i < poiSheet.getNumMergedRegions(); ++i) { CellRangeAddress poiSpan = poiSheet.getMergedRegion(i); spans.add(new AreaReference(sheetName.getSheetName(), poiSpan.getFirstRow(), poiSpan.getFirstColumn(), poiSpan.getLastRow(), poiSpan.getLastColumn())); } nxSheet.sendCommand(SheetCommands.spans(spans)); // refresh all the cells now nxSheet.setRefreshEnabled(true); nxSheet.sendCommandNoUndo(moreCellCommands.refresh(refreshCells, false)); } finally { if (nxSheet != null) { sheetNames.add(sheetName); } } } } catch (IOException e) { throw new ImportException(null, "Cannot open workbook:" + e, e); } catch (StorageException e) { throw new ImportException(null, "Error storing sheet:" + e, e); } catch (NotFoundException e) { throw new ImportException(null, "Cannot find workbook:" + e, e); } catch (NetxiliaResourceException e) { throw new ImportException(null, e.getMessage(), e); } catch (NetxiliaBusinessException e) { throw new ImportException(null, e.getMessage(), e); } return sheetNames; }
From source file:org.projectforge.excel.ExportSheet.java
License:Open Source License
private static Row copyRow(Sheet worksheet, int rowNum) { Row sourceRow = worksheet.getRow(rowNum); //Save the text of any formula before they are altered by row shifting String[] formulasArray = new String[sourceRow.getLastCellNum()]; for (int i = 0; i < sourceRow.getLastCellNum(); i++) { if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA) formulasArray[i] = sourceRow.getCell(i).getCellFormula(); }//from w ww.j av a 2 s .c o m worksheet.shiftRows(rowNum, worksheet.getLastRowNum(), 1); Row newRow = sourceRow; //Now sourceRow is the empty line, so let's rename it sourceRow = worksheet.getRow(rowNum + 1); //Now the source row is at rowNum+1 // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell Cell oldCell = sourceRow.getCell(i); Cell newCell; // If the old cell is null jump to next cell if (oldCell == null) { continue; } else { newCell = newRow.createCell(i); } // Copy style from old cell and apply to new cell CellStyle newCellStyle = worksheet.getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(formulasArray[i]); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; default: break; } } // If there are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } return newRow; }
From source file:org.shareok.data.documentProcessor.ExcelHandler.java
/** * Reads out the data in an excel file and stores data in a hashmap<p> * The cell data has the ending of "--type" to label the data type * //from w w w . ja va 2 s. co m * @throws Exception */ @Override public void readData() { String name = fileName; Sheet sheet = null; try { if (null == name || "".equals(name)) { throw new FileNameException("File name is not specified!"); } FileInputStream file = new FileInputStream(new File(name)); String extension = DocumentProcessorUtil.getFileExtension(name); String[] excelTypes = router.loadOfficeFileType("excel"); if (null == excelTypes || excelTypes.length == 0) { throw new FileTypeException("The file types are empty!"); } HashMap<String, String> typeMap = new HashMap<>(); for (String s : excelTypes) { typeMap.put(s, s); } if (typeMap.containsKey(extension)) { if (extension.equals("xlsx")) { } } sheet = getWorkbookSheet(extension, file); int maxNumOfCells = sheet.getRow(0).getLastCellNum(); Iterator<Row> rowIterator = sheet.iterator(); DateFormat df = new SimpleDateFormat("MM/dd/yyyy"); int rowCount = 0; //int colCount = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); //while(cellIterator.hasNext()) { for (int colCount = 0; colCount < maxNumOfCells; colCount++) { //Cell cell = cellIterator.next(); Cell cell = row.getCell(colCount); if (null == cell) { cell = row.createCell(colCount); } String key = Integer.toString(rowCount) + "-" + Integer.toString(colCount); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: data.put(key, Boolean.toString(cell.getBooleanCellValue()) + "---bool"); break; case Cell.CELL_TYPE_NUMERIC: if (isCellDateFormatted(cell)) { data.put(key, df.format(cell.getDateCellValue()) + "---dat"); } else { data.put(key, Double.toString(cell.getNumericCellValue()) + "---num"); } break; case Cell.CELL_TYPE_STRING: data.put(key, cell.getStringCellValue() + "---str"); break; case Cell.CELL_TYPE_BLANK: data.put(key, ""); break; case Cell.CELL_TYPE_ERROR: data.put(key, "ERROR_VALUE"); break; case Cell.CELL_TYPE_FORMULA: FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper() .createFormulaEvaluator(); //handleCell(cell.getCachedFormulaResultType(), cell, evaluator); data.put(key, String.valueOf(cell.getCachedFormulaResultType())); break; default: data.put(key, cell.getRichStringCellValue() + "---def"); break; } // colCount++; } rowCount++; //colCount = 0; } file.close(); } catch (FileNotFoundException ex) { Logger.getLogger(ExcelHandler.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ExcelHandler.class.getName()).log(Level.SEVERE, null, ex); } catch (Exception ex) { Logger.getLogger(ExcelHandler.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:org.tiefaces.components.websheet.configuration.ConfigurationHandler.java
License:MIT License
/** * Copy the each command area to seperated sheet. As it will be used for * iteration./* w w w. j a va 2 s .co m*/ * * @param sheet * sheet. */ private void copyTemplateForTieCommands(final Sheet sheet) { // if skip configuration. then return. if (parent.isSkipConfiguration()) { return; } Workbook wb = sheet.getWorkbook(); String copyName = TieConstants.COPY_SHEET_PREFIX + sheet.getSheetName(); if (wb.getSheet(copyName) == null) { Sheet newSheet = wb.cloneSheet(wb.getSheetIndex(sheet)); int sheetIndex = wb.getSheetIndex(newSheet); wb.setSheetName(sheetIndex, copyName); wb.setSheetHidden(sheetIndex, Workbook.SHEET_STATE_VERY_HIDDEN); } }
From source file:org.tiefaces.components.websheet.configuration.ConfigurationHandler.java
License:MIT License
/** * build command list from comment./* w w w . j a v a 2 s . com*/ * * @param sheet * sheet. * @param sheetRightCol * sheet right column. * @param cell * the cell * @param cList * command list. * @param cellAttributesMap * the cell attributes map * @return command list. */ private List<ConfigCommand> buildCommandList(final Sheet sheet, final int sheetRightCol, final Cell cell, final List<ConfigCommand> cList, final CellAttributesMap cellAttributesMap) { Comment comment = cell.getCellComment(); String text = comment.getString().getString(); String[] commentLines = text.split("\\n"); StringBuilder newComment = new StringBuilder(); boolean changed = false; for (String commentLine : commentLines) { String line = commentLine.trim(); if (ParserUtility.isCommandString(line)) { processCommandLine(sheet, cell, line, cList, sheetRightCol); changed = true; } else if (ParserUtility.isEmptyMethodString(line) || ParserUtility.isMethodString(line)) { processMethodLine(cell, line, cellAttributesMap); changed = true; } else { if (newComment.length() > 0) { newComment.append("\\n" + commentLine); } else { newComment.append(commentLine); } } } if (!changed) { moveCommentToMap(cell, text, cellAttributesMap.getTemplateCommentMap(), true); } else { // reset comment string if changed if (newComment.length() > 0) { moveCommentToMap(cell, newComment.toString(), cellAttributesMap.getTemplateCommentMap(), true); CreationHelper factory = sheet.getWorkbook().getCreationHelper(); RichTextString str = factory.createRichTextString(newComment.toString()); comment.setString(str); } else { // remove cell comment if new comment become empty. cell.removeCellComment(); } } return cList; }
From source file:org.tiefaces.components.websheet.configuration.FormCommand.java
License:MIT License
/** * Watch list serve for formula changes. Basically all the rows appeared in * the formula in the current sheet will be watched. Note if the cell * reference is from other sheet or workbooks, it will be ignored. * //from ww w . j a v a 2 s. c o m * @param wbWrapper * XSSFEvaluationWorkbook used for formula parse. * @param sheet * current sheet. * @return List row number for monitoring. */ private List<Integer> buildFormWatchList(final XSSFEvaluationWorkbook wbWrapper, final Sheet sheet) { List<Integer> watchList = new ArrayList<>(); ConfigRange cRange = this.getConfigRange(); List<ConfigCommand> commandList = cRange.getCommandList(); if (commandList.isEmpty()) { // if no command then no dynamic changes. then no need formula // shifts. return watchList; } int lastStaticRow = commandList.get(0).getTopRow() - 1; if (lastStaticRow < 0) { lastStaticRow = this.getTopRow(); } int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet); for (int i = this.getTopRow(); i <= this.getLastRow(); i++) { Row row = sheet.getRow(i); for (Cell cell : row) { if (cell.getCellTypeEnum() == CellType.FORMULA) { buildWatchListForCell(wbWrapper, sheetIndex, cell, watchList, lastStaticRow); } } } return watchList; }
From source file:org.tiefaces.components.websheet.utility.CellUtility.java
License:MIT License
/** * create cell style from source cell./*ww w .j ava 2s .co m*/ * * @param destSheet * dest sheet. * @param sourceCell * source cell. * @return cell style. */ private static CellStyle getCellStyleFromSourceCell(final Sheet destSheet, final Cell sourceCell) { Workbook wb = destSheet.getWorkbook(); // Copy style from old cell and apply to new cell CellStyle newCellStyle = wb.createCellStyle(); newCellStyle.cloneStyleFrom(sourceCell.getCellStyle()); return newCellStyle; }