List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex
int getColumnIndex();
From source file:com.jeefuse.system.param.web.imports.excel.GsysParameterExcelImport.java
License:GNU General Public License
/** * populate model./*from ww w. j a v a 2 s. c om*/ * * @generated */ @Override protected GsysParameter readExcelToModel(Row row, String[] columnNames) { if (row == null) return null; GsysParameter model = new GsysParameter(); int cellLenght = columnNames.length; Cell cell = null; for (int i = 0; i < cellLenght; i++) { cell = row.getCell(i); String columnName = columnNames[i]; GsysParameterField gsysParameterField = GsysParameterField.valueOfFieldLabel(columnName); if (null == gsysParameterField) { gsysParameterField = GsysParameterField.valueOfFieldName(columnName); } if (null == gsysParameterField) throw new DataNoExistException("??:" + columnName); setModelData(model, cell, gsysParameterField); InvalidValue[] invalidValues = GsysParameterValidate.validateProperty(model, gsysParameterField); if (invalidValues.length > 0) { List<String> errors = new ArrayList<String>(); for (InvalidValue invalidValue : invalidValues) { errors.add(gsysParameterField.getFieldLabel() + ": " + invalidValue.getMessage()); } throw new ValidateViolationException("" + (cell.getRowIndex() + 1) + "," + (cell.getColumnIndex() + 1) + "!", errors); } } return model; }
From source file:com.jeefuse.system.security.web.imports.excel.GsysFunctionExcelImport.java
License:GNU General Public License
/** * populate model.//ww w . j a va 2 s .com * * @generated */ @Override protected GsysFunction readExcelToModel(Row row, String[] columnNames) { if (row == null) return null; GsysFunction model = new GsysFunction(); int cellLenght = columnNames.length; Cell cell = null; for (int i = 0; i < cellLenght; i++) { cell = row.getCell(i); String columnName = columnNames[i]; GsysFunctionField gsysFunctionField = GsysFunctionField.valueOfFieldLabel(columnName); if (null == gsysFunctionField) { gsysFunctionField = GsysFunctionField.valueOfFieldName(columnName); } if (null == gsysFunctionField) throw new DataNoExistException("??:" + columnName); setModelData(model, cell, gsysFunctionField); InvalidValue[] invalidValues = GsysFunctionValidate.validateProperty(model, gsysFunctionField); if (invalidValues.length > 0) { List<String> errors = new ArrayList<String>(); for (InvalidValue invalidValue : invalidValues) { errors.add(gsysFunctionField.getFieldLabel() + ": " + invalidValue.getMessage()); } throw new ValidateViolationException("" + (cell.getRowIndex() + 1) + "," + (cell.getColumnIndex() + 1) + "!", errors); } } return model; }
From source file:com.jmc.jfxxlsdiff.task.GetWorkSheetContent.java
private List<List> findRows() { List<List> rows = new ArrayList<>(); Iterator<Row> ri = sheet.rowIterator(); while (ri.hasNext()) { Row r = ri.next();/* w w w. ja v a2 s . com*/ Iterator<Cell> ci = r.cellIterator(); if (ci.hasNext()) { List colVals = new ArrayList(); //colVals.add( r.getRowNum() + 1 ); for (int i = 0; ci.hasNext(); i++) { Cell cell = ci.next(); while (cell.getColumnIndex() > i) { i++; colVals.add(null); } colVals.add(POIXlsUtil.getCellValue(cell)); } rows.add(colVals); } else { rows.add(null); } //rows.add( ri.next() ); } return rows; }
From source file:com.jogo.dao.RepositorioDao.java
@Override public List importarPerguntas(int index) { //CRIO UM ARRAY DE PERGUNTAS PARA ARMAZENAR AS PERGUNTAS BUSCAS DO EXCEL. List<Perguntas> perguntas = new ArrayList<>(); try {/*from www . j av a 2 s . co m*/ //CAPTURANDO O EXCEL PARA MEU WB. wb = WorkbookFactory.create(new FileInputStream(patch)); //CAPTURO A FOLHA DO EXCEL PASSANDO O INDEX Sheet folha = wb.getSheetAt(index); //CRIO UM ITERATOR PARA INTERAGIR COM AS LINHAS. Iterator filaIterator = folha.rowIterator(); //ENQUANTO HOUVER INTERAO PEGA UMA LINHA. while (filaIterator.hasNext()) { //CAPTURO A LINHA DO EXCEL Row linha = (Row) filaIterator.next(); //CRIO UM ITERATOR PARA PEGAR AS COLUNAS Iterator colunaIterator = linha.cellIterator(); //AQUI DIGO QUE MINHAS COLUNAS NO PODE PASSAR DE 6, COMO TA A MINHA ESTRUTURA PARA O EXCEL //1 - PERGUNTA, 2- ALTERNATIVA, 3- ALTERNATIVA, 4- ATLTERNATIVA, 5- ALTERNATIVA, 6- RESPOSTA //CHEGOU MAIOR QUE 6 SAIU DO LOOP DE COLUNAS. if (linha.getLastCellNum() > 6) { break; } //CRIOU A CLASSE DE PERGUNTAS E ADD DENTRO DO MEU ARRAY Perguntas per = new Perguntas(); perguntas.add(per); //INTERAGIR COM AS COLUNAS, PEGAR AS COLUNAS DO EXCEL while (colunaIterator.hasNext()) { //TENDO A LINHA E COLUNA JA POSSO TER UMA CELULA. Cell celula = (Cell) colunaIterator.next(); //APOS CAPTURAR O VALOR NA CELULA, SETO PARA MINHA CLASSE PERGUNTAS QUE CRIEI LOGO ACIMA. if (celula != null) { //CAPTURAR O TIPO DA CELULA, NO CASO TODAS AS PERGUNTAS E ALTERNATIVAS SO STRINGS. //OBS: OLHE QUE NESSE CASO S POSSO TRAZER STRING'S CASO CONTRARIO NO IR? FUNCIONAR.:/ switch (celula.getCellType()) { case Cell.CELL_TYPE_STRING: switch (celula.getColumnIndex()) { case 0: per.setPergunta(celula.getStringCellValue()); break; case 1: per.setAlt1(celula.getStringCellValue()); break; case 2: per.setAlt2(celula.getStringCellValue()); break; case 3: per.setAlt3(celula.getStringCellValue()); break; case 4: per.setAlt4(celula.getStringCellValue()); break; case 5: per.setResposta(celula.getStringCellValue()); break; default: break; } } } } } } catch (IOException | InvalidFormatException | EncryptedDocumentException e) { } return perguntas; }
From source file:com.lapis.jsfexporter.excel.ExcelExportType.java
License:Apache License
@Override public Row exportRow(IExportRow row) { Row xlsRow = sheet.createRow(rowCount++); int cellIndex = 0; for (IExportCell cell : row.getCells()) { boolean cellIsUsed; do {//from w ww. java 2 s . com cellIsUsed = false; for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress region = sheet.getMergedRegion(i); if (region.isInRange(xlsRow.getRowNum(), cellIndex)) { cellIsUsed = true; cellIndex += region.getLastColumn() - region.getFirstColumn() + 1; } } } while (cellIsUsed); Cell xlsCell = xlsRow.createCell(cellIndex++); xlsCell.setCellValue(cell.getValue()); if (cell.getColumnSpanCount() > 1 || cell.getRowSpanCount() > 1) { sheet.addMergedRegion(new CellRangeAddress(xlsCell.getRowIndex(), xlsCell.getRowIndex() + cell.getRowSpanCount() - 1, xlsCell.getColumnIndex(), xlsCell.getColumnIndex() + cell.getColumnSpanCount() - 1)); cellIndex += cell.getColumnSpanCount() - 1; } } return xlsRow; }
From source file:com.movielabs.availslib.AvailSS.java
License:Open Source License
/** * Add a sheet from an Excel spreadsheet to a spreadsheet object * @param wb an Apache POI workbook object * @param sheet an Apache POI sheet object * @return created sheet object/*from w w w .j a v a 2s . c om*/ */ private AvailsSheet addSheetHelper(Workbook wb, Sheet sheet) throws Exception { AvailsSheet as = new AvailsSheet(this, sheet.getSheetName()); // int qq = 0; for (Row row : sheet) { // qq++; int len = row.getLastCellNum(); if (len < 0) continue; String[] fields = new String[len]; for (int i = 0; i < len; i++) // XXX: don't want nulls fields[i] = ""; for (Cell cell : row) { int idx = cell.getColumnIndex(); int type = cell.getCellType(); switch (type) { case 0: // Numeric double v = cell.getNumericCellValue(); if (v < 0.5) { // XXX hack: assume TotalRunTime java.util.Date d = cell.getDateCellValue(); fields[idx] = String.format("%02d:%02d:%02d", d.getHours(), d.getMinutes(), d.getSeconds()); //System.out.println("run=" + tmp); } else { fields[idx] = cell.toString(); } break; case 1: // String case 3: // Blank fields[idx] = cell.getStringCellValue().trim(); break; default: //logger.warn("Cell[" + i + "," + idx + "]: invalid type (" + type + ")"); fields[idx] = cell.toString(); break; } } /* cell */ if (as.isAvail(fields)) as.addRow(fields, row.getRowNum() + 1); } /* row */ sheets.add(as); return as; }
From source file:com.mycompany.bandaru_exam.ReadfromExcel.java
public List getAccountListFromExcel() { List accountList = new ArrayList(); FileInputStream fis = null;/* w ww. j ava 2 s . c o m*/ try { fis = new FileInputStream(FILE_PATH); /* Use XSSF for xlsx format, for xls use HSSF */ Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); /* looping over each workbook sheet */ for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.iterator(); /* iterating over each row */ while (rowIterator.hasNext()) { Account account = new Account(); Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); /* checking if the cell is having a String value . */ if (Cell.CELL_TYPE_STRING == cell.getCellType()) { /* Cell with index 0 contains first name */ if (cell.getColumnIndex() == 0) { account.setFirstName(cell.getStringCellValue()); } /* Cell with index 1 contains last name */ if (cell.getColumnIndex() == 1) { account.setLastName(cell.getStringCellValue()); } /* Cell with index 2 contains account number */ if (cell.getColumnIndex() == 2) { account.setAccNumber(cell.getStringCellValue()); } } /* checking if the cell is having a numeric value */ else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { /* Cell with index 3 contains account number */ if (cell.getColumnIndex() == 3) { account.setBalance(cell.getNumericCellValue()); } } } /* end iterating a row, add all the elements of a row in list */ accountList.add(account); } } fis.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } //Remove Headers accountList.remove(0); return accountList; }
From source file:com.mycompany.chartproject.ExcelReader.java
public List<ChartSeries> getSeriesChartData(String repo) { List<ChartSeries> cs = new ArrayList<>(); try {// w ww .j a v a 2 s . c o m String fileName = "src/main/resources/Series.xlsx"; String test = fileName; //String fileName2 = "src/main/resources/Series.xlsx"; //String test2 = fileName2; FileInputStream file = new FileInputStream(new File(test)); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheet(repo); //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); ChartSeries chartSeries = null; while (rowIterator.hasNext()) { chartSeries = new ChartSeries(); Row row = rowIterator.next(); if (row.getRowNum() == 0) { row = rowIterator.next(); } //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //System.out.println("numeric"); switch (cell.getColumnIndex()) { case 1: chartSeries.setTotal((int) cell.getNumericCellValue()); break; case 2: chartSeries.setPassed((int) cell.getNumericCellValue()); break; case 3: chartSeries.setFailed((int) cell.getNumericCellValue()); break; case 4: chartSeries.setSkipped((int) cell.getNumericCellValue()); break; } System.out.println(cell.getDateCellValue() + "\t\t"); System.out.print(cell.getNumericCellValue() + "\t\t"); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "\t\t"); break; case Cell.CELL_TYPE_STRING: chartSeries.setDate(cell.getStringCellValue()); System.out.print(cell.getStringCellValue() + "\t\t"); break; } } System.out.println(""); cs.add(chartSeries); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return cs; }
From source file:com.mycompany.gannaraputakehomeexam.ReadingFromExcel.java
public List getSongsListFromExcel() { List songList = new ArrayList(); FileInputStream fis = null;/* w ww . j a va2 s . c o m*/ try { fis = new FileInputStream(FILE_PATH); /* Use XSSF for xlsx format, for xls use HSSF */ Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); /* looping over each workbook sheet */ for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.iterator(); /* iterating over each row */ while (rowIterator.hasNext()) { SongsList song = new SongsList(); Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); /* checking if the cell is having a String value . */ if (Cell.CELL_TYPE_STRING == cell.getCellType()) { /* Cell with index 1 contains Album name */ if (cell.getColumnIndex() == 1) { song.setAlbumname(cell.getStringCellValue()); } /* Cell with index 2 contains Genre */ if (cell.getColumnIndex() == 2) { song.setGenre(cell.getStringCellValue()); } /* Cell with index 3 contains Artist name */ if (cell.getColumnIndex() == 3) { song.setArtist(cell.getStringCellValue()); } } /* checking if the cell is having a numeric value */ else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { /* Cell with index 0 contains Sno */ if (cell.getColumnIndex() == 0) { song.setSno((int) cell.getNumericCellValue()); } /* Cell with index 5 contains Critic score. */ else if (cell.getColumnIndex() == 5) { song.setCriticscore((int) cell.getNumericCellValue()); } /* Cell with index 4 contains Release date */ else if (cell.getColumnIndex() == 4) { Date dateValue = null; if (DateUtil.isCellDateFormatted(cell)) { dateValue = cell.getDateCellValue(); } song.setReleasedate(dateValue); } } } /* end iterating a row, add all the elements of a row in list */ songList.add(song); } } fis.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return songList; }
From source file:com.mycompany.gayamtakehomeexam.ReadfromExcel.java
public List getSongsListFromExcel() { List songList = new ArrayList(); FileInputStream fis = null;//from ww w .j a v a 2 s.c om try { fis = new FileInputStream(FILE_PATH); /* Use XSSF for xlsx format, for xls use HSSF */ Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); /* looping over each workbook sheet */ for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.iterator(); /* iterating over each row */ while (rowIterator.hasNext()) { Song song = new Song(); Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); /* checking if the cell is having a String value . */ if (Cell.CELL_TYPE_STRING == cell.getCellType()) { /* Cell with index 1 contains Album name */ if (cell.getColumnIndex() == 1) { song.setAlbumname(cell.getStringCellValue()); } /* Cell with index 2 contains Genre */ if (cell.getColumnIndex() == 2) { song.setGenre(cell.getStringCellValue()); } /* Cell with index 3 contains Artist name */ if (cell.getColumnIndex() == 3) { song.setArtist(cell.getStringCellValue()); } } /* checking if the cell is having a numeric value */ else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { /* Cell with index 0 contains Sno */ if (cell.getColumnIndex() == 0) { song.setSno((int) cell.getNumericCellValue()); } /* Cell with index 5 contains Critic score. */ else if (cell.getColumnIndex() == 5) { song.setCriticscore((int) cell.getNumericCellValue()); } /* Cell with index 4 contains Release date */ else if (cell.getColumnIndex() == 4) { Date dateValue = null; if (DateUtil.isCellDateFormatted(cell)) { dateValue = cell.getDateCellValue(); } song.setReleasedate(dateValue); } } } /* end iterating a row, add all the elements of a row in list */ songList.add(song); } } fis.close(); } catch (FileNotFoundException e) { } catch (IOException e) { } return songList; }