List of usage examples for org.apache.poi.ss.usermodel Sheet getSheetName
String getSheetName();
From source file:org.cgiar.ccafs.marlo.action.center.capdev.ParticipantsAction.java
License:Open Source License
public void createDataValidator(Workbook wb, Sheet sheet, String[] data, String dataValidationName) { String reference = null;/*from w ww . j a v a 2 s . co m*/ /* * se escriben las listas con los datos en cada sheet deseada */ for (int i = 0; i < data.length; i++) { final Row fila = sheet.createRow(i); final Cell celda = fila.createCell(0); celda.setCellValue(data[i]); } // se protege el sheet para evitar que el usuario lo modifique sheet.protectSheet("marlo-ciat"); /* * se crea referencia la lista de datos con el que se creara el data validator */ final Name namedCountry = wb.createName(); namedCountry.setNameName(dataValidationName); reference = sheet.getSheetName() + "!$A$1:$A$" + data.length; // area de referencia namedCountry.setRefersToFormula(reference); }
From source file:org.codelabor.example.crud.emp.web.controller.EmpController.java
License:Apache License
private List<EmpDto> fileToDtoList(MultipartFile file, List<String> failureMessages) throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD // by/*from w ww. j a va 2s .co m*/ // "SHIN Sang-jae" Workbook wb = WorkbookFactory.create(file.getInputStream()); int numberOfSheets = wb.getNumberOfSheets(); logger.debug("numberOfSheets: {}", numberOfSheets); // prepare model List<EmpDto> empDtoList = new ArrayList<EmpDto>(); // set effective position int effectiveFirstSheetIndex = 0; int effectiveLastSheetIndex = numberOfSheets - 1; // traverse sheet StringBuilder sb = new StringBuilder(); for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) { Sheet sheet = wb.getSheetAt(i); String sheetName = sheet.getSheetName(); logger.debug("sheetName: {}", sheetName); int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); logger.debug("firstRowNum: {}, lastRowNum: {}", firstRowNum, lastRowNum); // set effective position int effectiveFirstRowIndex = 1; // header row: 0 int effectiveLastRowIndex = lastRowNum; // traverse row for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) { // prepare model EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae" Row row = sheet.getRow(j); int rowNum = row.getRowNum(); int firstCellNum = row.getFirstCellNum(); int lastCellNum = row.getLastCellNum(); logger.debug("rowNum: {}, firstCellNum: {}, lastCellNum: {}", rowNum, firstCellNum, lastCellNum); // set effective position int effectiveFirstCellIndex = firstCellNum; int effectiveLastCellIndex = lastCellNum - 1; // traverse cell for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) { Cell cell = row.getCell(k); if (cell != null) { int rowIndex = cell.getRowIndex(); int columnIndex = cell.getColumnIndex(); CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae" logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex); // populate dto switch (k) { case 0: // EMPNO empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue()); break; case 1: // ENAME empDto.setEname(cell.getRichStringCellValue().toString()); break; case 2: // JOB empDto.setJob(cell.getRichStringCellValue().toString()); break; case 3: // MGR empDto.setMgr(((Double) cell.getNumericCellValue()).intValue()); break; case 4: // HIREDATE empDto.setHireDate(cell.getDateCellValue()); break; case 5: // SAL // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 6: // COMM // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 7: // DEPTNO empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue()); break; default: break; } } } logger.debug("empDto: {}", empDto); // validate Validator validator = Validation.buildDefaultValidatorFactory().getValidator(); Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto); if (violations.isEmpty()) { // do all or nothing empDtoList.add(empDto); } else { // add failure message sb.setLength(0); // init StringBuilder for reuse for (ConstraintViolation<EmpDto> violation : violations) { String propertyPath = violation.getPropertyPath().toString(); String message = violation.getMessage(); sb.append(message); sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')'); failureMessages.add(sb.toString()); logger.error(sb.toString()); sb.setLength(0); } } } } return empDtoList; }
From source file:org.codelabor.example.crud.emp.web.controller.EmpController.java
License:Apache License
private List<EmpDto> fileToDtoList(Part file, List<String> failureMessages) throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD // by/*from w w w .j a va 2 s. c o m*/ // "SHIN Sang-jae" Workbook wb = WorkbookFactory.create(file.getInputStream()); int numberOfSheets = wb.getNumberOfSheets(); logger.debug("numberOfSheets: {}", numberOfSheets); // prepare model List<EmpDto> empDtoList = new ArrayList<EmpDto>(); // set effective position int effectiveFirstSheetIndex = 0; int effectiveLastSheetIndex = numberOfSheets - 1; // traverse sheet StringBuilder sb = new StringBuilder(); for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) { Sheet sheet = wb.getSheetAt(i); String sheetName = sheet.getSheetName(); logger.debug("sheetName: {}", sheetName); int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); logger.debug("firstRowNum: {}, lastRowNum: {}", firstRowNum, lastRowNum); // set effective position int effectiveFirstRowIndex = 1; // header row: 0 int effectiveLastRowIndex = lastRowNum; // traverse row for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) { // prepare model EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae" Row row = sheet.getRow(j); int rowNum = row.getRowNum(); int firstCellNum = row.getFirstCellNum(); int lastCellNum = row.getLastCellNum(); logger.debug("rowNum: {}, firstCellNum: {}, lastCellNum: {}", rowNum, firstCellNum, lastCellNum); // set effective position int effectiveFirstCellIndex = firstCellNum; int effectiveLastCellIndex = lastCellNum - 1; // traverse cell for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) { Cell cell = row.getCell(k); if (cell != null) { int rowIndex = cell.getRowIndex(); int columnIndex = cell.getColumnIndex(); CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae" logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex); // populate dto switch (k) { case 0: // EMPNO empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue()); break; case 1: // ENAME empDto.setEname(cell.getRichStringCellValue().toString()); break; case 2: // JOB empDto.setJob(cell.getRichStringCellValue().toString()); break; case 3: // MGR empDto.setMgr(((Double) cell.getNumericCellValue()).intValue()); break; case 4: // HIREDATE empDto.setHireDate(cell.getDateCellValue()); break; case 5: // SAL // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 6: // COMM // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 7: // DEPTNO empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue()); break; default: break; } } } logger.debug("empDto: {}", empDto); // validate Validator validator = Validation.buildDefaultValidatorFactory().getValidator(); Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto); if (violations.isEmpty()) { // do all or nothing empDtoList.add(empDto); } else { // add failure message sb.setLength(0); // init StringBuilder for reuse for (ConstraintViolation<EmpDto> violation : violations) { String propertyPath = violation.getPropertyPath().toString(); String message = violation.getMessage(); sb.append(message); sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')'); failureMessages.add(sb.toString()); logger.error(sb.toString()); sb.setLength(0); } } } } return empDtoList; }
From source file:org.codelabor.example.emp.web.controller.EmpController.java
License:Apache License
private List<EmpDto> fileToDtoList(MultipartFile file, List<String> failureMessages) throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD by "SHIN Sang-jae" Workbook wb = WorkbookFactory.create(file.getInputStream()); int numberOfSheets = wb.getNumberOfSheets(); logger.debug("numberOfSheets: {}", numberOfSheets); // prepare model List<EmpDto> empDtoList = new ArrayList<EmpDto>(); // set effective position int effectiveFirstSheetIndex = 0; int effectiveLastSheetIndex = numberOfSheets - 1; // traverse sheet StringBuilder sb = new StringBuilder(); for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) { Sheet sheet = wb.getSheetAt(i); String sheetName = sheet.getSheetName(); logger.debug("sheetName: {}", sheetName); int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); logger.debug("firstRowNum: {}, lastRowNum: {}", firstRowNum, lastRowNum); // set effective position int effectiveFirstRowIndex = 1; // header row: 0 int effectiveLastRowIndex = lastRowNum; // traverse row for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) { // prepare model EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae" Row row = sheet.getRow(j);/* w w w .j a v a 2 s.co m*/ int rowNum = row.getRowNum(); int firstCellNum = row.getFirstCellNum(); int lastCellNum = row.getLastCellNum(); logger.debug("rowNum: {}, firstCellNum: {}, lastCellNum: {}", rowNum, firstCellNum, lastCellNum); // set effective position int effectiveFirstCellIndex = firstCellNum; int effectiveLastCellIndex = lastCellNum - 1; // traverse cell for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) { Cell cell = row.getCell(k); if (cell != null) { int rowIndex = cell.getRowIndex(); int columnIndex = cell.getColumnIndex(); CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae" logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex); // populate dto switch (k) { case 0: // EMPNO empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue()); break; case 1: // ENAME empDto.setEname(cell.getRichStringCellValue().toString()); break; case 2: // JOB empDto.setJob(cell.getRichStringCellValue().toString()); break; case 3: // MGR empDto.setMgr(((Double) cell.getNumericCellValue()).intValue()); break; case 4: // HIREDATE empDto.setHireDate(cell.getDateCellValue()); break; case 5: // SAL // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 6: // COMM // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 7: // DEPTNO empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue()); break; default: break; } } } logger.debug("empDto: {}", empDto); // validate Validator validator = Validation.buildDefaultValidatorFactory().getValidator(); Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto); if (violations.isEmpty()) { // do all or nothing empDtoList.add(empDto); } else { // add failure message sb.setLength(0); // init StringBuilder for reuse for (ConstraintViolation<EmpDto> violation : violations) { String propertyPath = violation.getPropertyPath().toString(); String message = violation.getMessage(); sb.append(message); sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')'); failureMessages.add(sb.toString()); logger.error(sb.toString()); sb.setLength(0); } } } } return empDtoList; }
From source file:org.codelabor.example.emp.web.controller.EmpController.java
License:Apache License
private List<EmpDto> fileToDtoList(Part file, List<String> failureMessages) throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD by "SHIN Sang-jae" Workbook wb = WorkbookFactory.create(file.getInputStream()); int numberOfSheets = wb.getNumberOfSheets(); logger.debug("numberOfSheets: {}", numberOfSheets); // prepare model List<EmpDto> empDtoList = new ArrayList<EmpDto>(); // set effective position int effectiveFirstSheetIndex = 0; int effectiveLastSheetIndex = numberOfSheets - 1; // traverse sheet StringBuilder sb = new StringBuilder(); for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) { Sheet sheet = wb.getSheetAt(i); String sheetName = sheet.getSheetName(); logger.debug("sheetName: {}", sheetName); int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); logger.debug("firstRowNum: {}, lastRowNum: {}", firstRowNum, lastRowNum); // set effective position int effectiveFirstRowIndex = 1; // header row: 0 int effectiveLastRowIndex = lastRowNum; // traverse row for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) { // prepare model EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae" Row row = sheet.getRow(j);/*www. j a v a 2s .c o m*/ int rowNum = row.getRowNum(); int firstCellNum = row.getFirstCellNum(); int lastCellNum = row.getLastCellNum(); logger.debug("rowNum: {}, firstCellNum: {}, lastCellNum: {}", rowNum, firstCellNum, lastCellNum); // set effective position int effectiveFirstCellIndex = firstCellNum; int effectiveLastCellIndex = lastCellNum - 1; // traverse cell for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) { Cell cell = row.getCell(k); if (cell != null) { int rowIndex = cell.getRowIndex(); int columnIndex = cell.getColumnIndex(); CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae" logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex); // populate dto switch (k) { case 0: // EMPNO empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue()); break; case 1: // ENAME empDto.setEname(cell.getRichStringCellValue().toString()); break; case 2: // JOB empDto.setJob(cell.getRichStringCellValue().toString()); break; case 3: // MGR empDto.setMgr(((Double) cell.getNumericCellValue()).intValue()); break; case 4: // HIREDATE empDto.setHireDate(cell.getDateCellValue()); break; case 5: // SAL // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 6: // COMM // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue())); break; case 7: // DEPTNO empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue()); break; default: break; } } } logger.debug("empDto: {}", empDto); // validate Validator validator = Validation.buildDefaultValidatorFactory().getValidator(); Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto); if (violations.isEmpty()) { // do all or nothing empDtoList.add(empDto); } else { // add failure message sb.setLength(0); // init StringBuilder for reuse for (ConstraintViolation<EmpDto> violation : violations) { String propertyPath = violation.getPropertyPath().toString(); String message = violation.getMessage(); sb.append(message); sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')'); failureMessages.add(sb.toString()); logger.error(sb.toString()); sb.setLength(0); } } } } return empDtoList; }
From source file:org.cytoscape.tableimport.internal.ui.PreviewTablePanel.java
License:Open Source License
private void updatePreviewTable(final Sheet sheet) throws IOException { final PreviewTableModel newModel = parseExcel(sheet, startLine); if (newModel.getRowCount() > 0) { final String sheetName = sheet.getSheetName(); dataTypes = TypeUtil.guessDataTypes(newModel); types = TypeUtil.guessTypes(importType, newModel, dataTypes, null); listDelimiters = new String[newModel.getColumnCount()]; updatePreviewTable(newModel, sheetName); }//from w w w.j a va 2s .c om if (getPreviewTable() == null) throw new IllegalStateException("No data found in the Excel sheets."); }
From source file:org.cytoscape.tableimport.internal.ui.PreviewTablePanel.java
License:Open Source License
private JComboBox<Sheet> getSheetComboBox() { if (sheetComboBox == null) { sheetComboBox = new JComboBox<>(); sheetComboBox.setVisible(false); sheetComboBox.setRenderer(new DefaultListCellRenderer() { @Override//from ww w . ja v a2s .co m public Component getListCellRendererComponent(JList<?> list, Object value, int index, boolean isSelected, boolean cellHasFocus) { super.getListCellRendererComponent(list, value, index, isSelected, cellHasFocus); setText(((Sheet) value).getSheetName()); return this; } }); sheetComboBox.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent evt) { if (!updating) { disposeEditDialog(); final Sheet sheet = (Sheet) sheetComboBox.getSelectedItem(); try { if (sheet != null) updatePreviewTable(sheet); } catch (IOException e) { logger.error("Cannot preview Excel sheet '" + sheet.getSheetName() + "'.", e); } } } }); } return sheetComboBox; }
From source file:org.databene.platform.xls.SingleSheetXLSEntityIterator.java
License:Open Source License
public SingleSheetXLSEntityIterator(Sheet sheet, Converter<String, ?> preprocessor, ComplexTypeDescriptor entityDescriptor, BeneratorContext context, boolean rowBased, boolean formatted, String emptyMarker) {/*w w w . ja v a 2 s . com*/ this.workbook = sheet.getWorkbook(); this.preprocessor = preprocessor; this.context = context; this.rowBased = rowBased; this.formatted = formatted; this.emptyMarker = emptyMarker; this.source = createRawIterator(sheet, rowBased, preprocessor); // if not specified explicitly, determine entity type by sheet name this.entityDescriptor = entityDescriptor; if (this.entityDescriptor == null) { String entityTypeName = sheet.getSheetName(); if (context != null) { DataModel dataModel = context.getDataModel(); this.entityDescriptor = (ComplexTypeDescriptor) dataModel.getTypeDescriptor(entityTypeName); if (this.entityDescriptor != null) this.entityDescriptor = new ComplexTypeDescriptor(entityTypeName + "_", context.getLocalDescriptorProvider()); else this.entityDescriptor = createDescriptor(entityTypeName); } else this.entityDescriptor = createDescriptor(entityTypeName); } // parse headers parseHeaders(); if (headers == null) { this.source = null; // empty sheet return; } // parse first data row DataContainer<Object[]> tmp = this.source.next(sourceContainer.get()); if (tmp == null) { this.source = null; // no data in sheet return; } this.buffer = tmp.getData(); converter = new Array2EntityConverter(this.entityDescriptor, headers, false); }
From source file:org.databene.platform.xls.SingleSheetXLSEntityIterator.java
License:Open Source License
private Sheet getSheet(String tabName) { for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet candidate = workbook.getSheetAt(i); if (candidate.getSheetName().trim().equalsIgnoreCase(tabName.trim())) return candidate; }/* w ww . j av a 2s . c o m*/ // tab not found throw new ConfigurationError("Tab '" + tabName + "' not found" + (uri != null ? " in " + uri : "")); }
From source file:org.diffkit.diff.sns.DKPoiSheet.java
License:Apache License
protected String getNameFromSheet() throws IOException { Sheet sheet = this.getSheet(); _log.debug("sheet->{}", sheet); if (sheet == null) throw new IOException(String.format("no sheet!")); return sheet.getSheetName(); }