List of usage examples for org.apache.poi.ss.usermodel Sheet getSheetName
String getSheetName();
From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java
License:Apache License
/** * parse excel file data to java object//from w ww . j a v a2 s . c om * * @param workbookInputStream * @param sheetProcessors */ @SuppressWarnings({ "unchecked", "rawtypes" }) public static void read(InputStream workbookInputStream, ExcelReadSheetProcessor<?>... sheetProcessors) { Assert.isTrue(workbookInputStream != null, "workbookInputStream can't be null"); Assert.isTrue(sheetProcessors != null && sheetProcessors.length != 0, "sheetProcessor can't be null"); try { Workbook workbook = WorkbookFactory.create(workbookInputStream); for (ExcelReadSheetProcessor<?> sheetProcessor : sheetProcessors) { ExcelReadContext context = new ExcelReadContext(); try { Class clazz = sheetProcessor.getTargetClass(); Integer sheetIndex = sheetProcessor.getSheetIndex(); String sheetName = sheetProcessor.getSheetName(); context.setCurSheetIndex(sheetIndex); context.setCurSheetName(sheetName); Sheet sheet = null; if (sheetName != null) { try { sheet = workbook.getSheet(sheetName); } catch (IllegalArgumentException e) { // ignore } if (sheet != null && sheetIndex != null && !sheetIndex.equals(workbook.getSheetIndex(sheet))) { throw new IllegalArgumentException( "sheetName[" + sheetName + "] and sheetIndex[" + sheetIndex + "] not match."); } } else if (sheetIndex != null) { try { sheet = workbook.getSheetAt(sheetIndex); } catch (IllegalArgumentException e) { // ignore } } else { throw new IllegalArgumentException("sheetName or sheetIndex can't be null"); } if (sheet == null) { ExcelReadException e = new ExcelReadException( "Sheet Not Found Exception. for sheet name:" + sheetName); e.setCode(ExcelReadException.CODE_OF_SHEET_NOT_EXSIT); throw e; } if (sheetIndex == null) { sheetIndex = workbook.getSheetIndex(sheet); } if (sheetName == null) { sheetName = sheet.getSheetName(); } // do check Map<Integer, Map<String, ExcelReadFieldMappingAttribute>> fieldMapping = new HashMap<Integer, Map<String, ExcelReadFieldMappingAttribute>>(); Map<String, Map<String, ExcelReadFieldMappingAttribute>> src = null; if (sheetProcessor.getFieldMapping() != null) { src = sheetProcessor.getFieldMapping().export(); } convertFieldMapping(sheet, sheetProcessor, src, fieldMapping); if (sheetProcessor.getTargetClass() != null && sheetProcessor.getFieldMapping() != null && !Map.class.isAssignableFrom(sheetProcessor.getTargetClass())) { readConfigParamVerify(sheetProcessor, fieldMapping); } // proc sheet context.setCurSheet(sheet); context.setCurSheetIndex(sheetIndex); context.setCurSheetName(sheet.getSheetName()); context.setCurRow(null); context.setCurRowData(null); context.setCurRowIndex(null); context.setCurColIndex(null); context.setCurColIndex(null); // beforeProcess sheetProcessor.beforeProcess(context); if (sheetProcessor.getPageSize() != null) { context.setDataList(new ArrayList(sheetProcessor.getPageSize())); } else { context.setDataList(new ArrayList()); } Integer pageSize = sheetProcessor.getPageSize(); int startRow = sheetProcessor.getStartRowIndex(); Integer rowEndIndex = sheetProcessor.getEndRowIndex(); int actLastRow = sheet.getLastRowNum(); if (rowEndIndex != null) { if (rowEndIndex > actLastRow) { rowEndIndex = actLastRow; } } else { rowEndIndex = actLastRow; } ExcelProcessControllerImpl controller = new ExcelProcessControllerImpl(); if (pageSize != null) { int total = rowEndIndex - startRow + 1; int pageCount = (total + pageSize - 1) / pageSize; for (int i = 0; i < pageCount; i++) { int start = startRow + pageSize * i; int size = pageSize; if (i == pageCount - 1) { size = rowEndIndex - start + 1; } read(controller, context, sheet, start, size, fieldMapping, clazz, sheetProcessor.getRowProcessor(), sheetProcessor.isTrimSpace()); sheetProcessor.process(context, context.getDataList()); context.getDataList().clear(); if (controller.isDoBreak()) { controller.reset(); break; } } } else { read(controller, context, sheet, startRow, rowEndIndex - startRow + 1, fieldMapping, clazz, sheetProcessor.getRowProcessor(), sheetProcessor.isTrimSpace()); sheetProcessor.process(context, context.getDataList()); context.getDataList().clear(); } } catch (RuntimeException e) { sheetProcessor.onException(context, e); } finally { sheetProcessor.afterProcess(context); } } } catch (Exception e) { if (e instanceof RuntimeException) { throw (RuntimeException) e; } else { throw new RuntimeException(e); } } }
From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java
License:Apache License
@SuppressWarnings("unchecked") private static void write(boolean useTemplate, Workbook workbook, OutputStream outputStream, ExcelWriteSheetProcessor<?>... sheetProcessors) { for (@SuppressWarnings("rawtypes") ExcelWriteSheetProcessor sheetProcessor : sheetProcessors) { @SuppressWarnings("rawtypes") ExcelWriteContext context = new ExcelWriteContext(); try {// ww w . j a v a 2 s. co m if (sheetProcessor == null) { continue; } String sheetName = sheetProcessor.getSheetName(); Integer sheetIndex = sheetProcessor.getSheetIndex(); Sheet sheet = null; if (sheetProcessor.getTemplateStartRowIndex() == null && sheetProcessor.getTemplateEndRowIndex() == null) { sheetProcessor.setTemplateRows(sheetProcessor.getStartRowIndex(), sheetProcessor.getStartRowIndex()); } // sheetName priority, if (useTemplate) { if (sheetName != null) { try { sheet = workbook.getSheet(sheetName); } catch (IllegalArgumentException e) { // ignore } if (sheet != null && sheetIndex != null && !sheetIndex.equals(workbook.getSheetIndex(sheet))) { throw new IllegalArgumentException( "sheetName[" + sheetName + "] and sheetIndex[" + sheetIndex + "] not match."); } } else if (sheetIndex != null) { try { sheet = workbook.getSheetAt(sheetIndex); } catch (IllegalArgumentException e) { // ignore } } else { throw new IllegalArgumentException("sheetName or sheetIndex can't be null"); } if (sheet == null) { ExcelWriteException e = new ExcelWriteException( "Sheet Not Found Exception. for sheet name:" + sheetName); e.setCode(ExcelWriteException.CODE_OF_SHEET_NOT_EXSIT); throw e; } } else { if (sheetName != null) { sheet = workbook.getSheet(sheetName); if (sheet != null) { if (sheetIndex != null && !sheetIndex.equals(workbook.getSheetIndex(sheet))) { throw new IllegalArgumentException("sheetName[" + sheetName + "] and sheetIndex[" + sheetIndex + "] not match."); } } else { sheet = workbook.createSheet(sheetName); if (sheetIndex != null) { workbook.setSheetOrder(sheetName, sheetIndex); } } } else if (sheetIndex != null) { sheet = workbook.createSheet(); workbook.setSheetOrder(sheet.getSheetName(), sheetIndex); } else { throw new IllegalArgumentException("sheetName or sheetIndex can't be null"); } } if (sheetIndex == null) { sheetIndex = workbook.getSheetIndex(sheet); } if (sheetName == null) { sheetName = sheet.getSheetName(); } // proc sheet context.setCurSheet(sheet); context.setCurSheetIndex(sheetIndex); context.setCurSheetName(sheet.getSheetName()); context.setCurRow(null); context.setCurRowIndex(null); context.setCurCell(null); context.setCurColIndex(null); // beforeProcess sheetProcessor.beforeProcess(context); // write head writeHead(useTemplate, sheet, sheetProcessor); // sheet ExcelProcessControllerImpl controller = new ExcelProcessControllerImpl(); int writeRowIndex = sheetProcessor.getStartRowIndex(); boolean isBreak = false; Map<Integer, InnerRow> cacheForTemplateRow = new HashMap<Integer, InnerRow>(); List<?> dataList = sheetProcessor.getDataList(); // if (dataList != null && !dataList.isEmpty()) { for (Object rowData : dataList) { // proc row Row row = sheet.getRow(writeRowIndex); if (row == null) { row = sheet.createRow(writeRowIndex); } InnerRow templateRow = getTemplateRow(cacheForTemplateRow, sheet, sheetProcessor, writeRowIndex); if (templateRow != null) { row.setHeight(templateRow.getHeight()); row.setHeightInPoints(templateRow.getHeightInPoints()); row.setRowStyle(templateRow.getRowStyle()); row.setZeroHeight(templateRow.isZeroHeight()); } context.setCurRow(row); context.setCurRowIndex(writeRowIndex); context.setCurColIndex(null); context.setCurCell(null); // try { controller.reset(); if (sheetProcessor.getRowProcessor() != null) { sheetProcessor.getRowProcessor().process(controller, context, rowData, row); } if (!controller.isDoSkip()) { writeRow(context, templateRow, row, rowData, sheetProcessor); writeRowIndex++; } if (controller.isDoBreak()) { isBreak = true; break; } } catch (RuntimeException e) { if (e instanceof ExcelWriteException) { ExcelWriteException ewe = (ExcelWriteException) e; // ef.setColIndex(null); user may want to set this value, ewe.setRowIndex(writeRowIndex); throw ewe; } else { ExcelWriteException ewe = new ExcelWriteException(e); ewe.setColIndex(null); ewe.setCode(ExcelWriteException.CODE_OF_PROCESS_EXCEPTION); ewe.setRowIndex(writeRowIndex); throw ewe; } } } if (isBreak) { break; } } if (sheetProcessor.getTemplateStartRowIndex() != null && sheetProcessor.getTemplateEndRowIndex() != null) { writeDataValidations(sheet, sheetProcessor); writeStyleAfterFinish(useTemplate, sheet, sheetProcessor); } } catch (RuntimeException e) { sheetProcessor.onException(context, e); } finally { sheetProcessor.afterProcess(context); } } try { workbook.write(outputStream); } catch (IOException e) { throw new RuntimeException(e); } }
From source file:org.interpss.util.reader.ExcelFileReader.java
License:Open Source License
/** * Load the spreadsheet and process the row, one row at a time, * using the File processor // w w w . j av a2s. c o m * * @param procer * @return sheet name */ public String processFile(IExcelFileProcessor<Row> procer) throws InterpssException { final File file = new File(this.filepath); Workbook wb; try { final InputStream stream = new FileInputStream(file); wb = WorkbookFactory.create(stream); } catch (Exception e) { ipssLogger.severe(e.toString()); throw new InterpssException(e.toString()); } if (wb.getNumberOfSheets() <= this.indexSheet) // index starts from 0 throw new InterpssException(this.sheetname + " does not exit in " + this.filepath); Sheet sheet = wb.getSheetAt(this.indexSheet); Iterator<Row> rowIter = sheet.rowIterator(); while (rowIter.hasNext()) { procer.processRow(rowIter.next()); } return sheet.getSheetName(); }
From source file:org.is.jxlpoi.JXLPOIWorkbook.java
License:Apache License
public String[] getSheetNames() { int numSheets = workbook.getNumberOfSheets(); String[] names = new String[numSheets]; for (int i = 0; i < numSheets; i++) { Sheet sheet = workbook.getSheetAt(i); names[i] = sheet.getSheetName(); }//from www. j a v a 2 s . c o m return names; }
From source file:org.laukvik.excel.Viewer.java
License:Apache License
public void openFile(File file) throws FileNotFoundException { r = new Reader(); r.open(file);/*from ww w . jav a 2s .c o m*/ setTitle(file.getAbsolutePath()); jTabbedPane1.removeAll(); viewers.clear(); int max = r.getWorkbook().getNumberOfSheets(); for (int x = 0; x < max; x++) { Sheet sheet = r.getWorkbook().getSheetAt(x); SheetViewer viewer = new SheetViewer(sheet); viewers.add(viewer); jTabbedPane1.add(new JScrollPane(viewer)); jTabbedPane1.setTitleAt(0, sheet.getSheetName()); } }
From source file:org.lisapark.octopus.util.json.JsonUtils.java
License:Open Source License
/** * /*from w w w . ja v a 2s. co m*/ * @param sheet * @param ontology * @return * @throws JSONException */ private String jsonFromSS(Sheet sheet, int increment) throws JSONException { // Return null, if sheet is null if (sheet == null) return null; String sheetName = sheet.getSheetName(); if (sheetName.isEmpty()) { sheetName = SPREAD_SHEET_ROWS; } // Iterate through the rows. StringBuilder stringBuilderRows = new StringBuilder(); List<String> stack = new ArrayList<String>(); Boolean first = Boolean.TRUE; int splitRowNumber = 0; if (sheet.getPaneInformation() != null && sheet.getPaneInformation().isFreezePane()) { splitRowNumber = sheet.getPaneInformation().getHorizontalSplitPosition(); } for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) { Row row = rowsIT.next(); if (row.getPhysicalNumberOfCells() > 0 && row.getRowNum() >= splitRowNumber) { continue; } String stringCells = jsonFromRowAsString(row); if (stringCells.isEmpty()) { continue; } String stringRow = jsonFromRowAsTreeNode(row, stringCells, stack, increment); if (first) { stringBuilderRows.append("[").append(stringRow); first = Boolean.FALSE; } else { stringBuilderRows.append(",").append(stringRow); } } // Get the JSON text. stringBuilderRows = stringBuilderRows.append("]" // + "}" ); return //"{" + "\"" + sheetName + "\"" + " : " + stringBuilderRows.toString(); }
From source file:org.mallon.tools.xls.injector.XLSInjector.java
License:Open Source License
private Worksheet inject(Sheet xlsSheet) { Worksheet sheet = XlsFactory.eINSTANCE.createWorksheet(); sheet.setName(xlsSheet.getSheetName()); Table table = XlsFactory.eINSTANCE.createTable(); sheet.setWs_table(table);//from w ww .ja v a 2 s. c om for (org.apache.poi.ss.usermodel.Row xlsRow : xlsSheet) { //Management of the rows just inserted by user (and so with empty cells) if (!StringUtils.isBlank(xlsRow.getCell(0).getStringCellValue())) { table.getT_rows().add(inject(xlsRow)); } } return sheet; }
From source file:org.mifos.dmt.excel.cleanup.PurgeEmptyRows.java
License:Open Source License
public Workbook processEmptyRows() throws DMTException { for (int j = 1; j <= (workbook.getNumberOfSheets() - 1); j++) { Sheet sheet = workbook.getSheetAt(j); int targetRow = -1; for (int i = 0; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i);//from w w w.jav a 2 s. co m if (row == null) continue; Cell cell = row.getCell(0); if (cell == null) continue; String val = cell.toString(); if (val.equals("EOF")) { targetRow = i; } } if (targetRow == -1) { logger.error("EOF value missing @ " + sheet.getSheetName()); throw new DMTException("EOF value missing @ " + sheet.getSheetName()); } cleanUpSheet(sheet, targetRow, sheet.getLastRowNum()); } return workbook; }
From source file:org.mifos.dmt.excel.cleanup.PurgeEmptyRows.java
License:Open Source License
private void cleanUpSheet(Sheet sheet, int targetRow, int lastRowNum) { logger.info(("sheet " + sheet.getSheetName() + " processed with " + targetRow + " rows.")); for (int i = targetRow; i <= lastRowNum; i++) { Row row = sheet.getRow(i);// w ww .j a va 2 s .c o m if (row == null) continue; sheet.removeRow(row); } }
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 {/*w w w .ja v a2 s. c o 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; }