List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt
Sheet getSheetAt(int index);
From source file:annualleave.PersonelTara.java
static public void Detection(int ilk, int son, String URL) throws FileNotFoundException, IOException { char gun[] = new char[100]; int gunler[] = new int[32]; for (int j = ilk; j <= son; j++) { gunler[j] = j;//from ww w . j av a 2 s . c om } String oncekiAd; // C:\\Users\\talha\\Documents\\NetBeansProjects\\AnnualLeave\\src\\annualleave\\Mays 23.xlsx String excelFilePath = URL; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); Workbook workbook = new XSSFWorkbook(inputStream); Sheet firstSheet = workbook.getSheetAt(0); int sonuncuIndex = firstSheet.getLastRowNum(); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); if (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getRowIndex() >= 6) { oncekiAd = ad; cell = cellIterator.next(); cell = cellIterator.next(); cell = cellIterator.next(); ad = cell.getStringCellValue(); cell = cellIterator.next(); cell = cellIterator.next(); tarih = cell.getStringCellValue(); if (ad != oncekiAd && i != 0 && !(oncekiAd.equals("Personel Ad Soyad")) && !(oncekiAd.isEmpty()) && !(ad.isEmpty()) && !(ad.equals("Personel Ad Soyad")) || cell.getRowIndex() == sonuncuIndex) { for (int j = ilk; j <= son; j++) { if (gunler[j] != 0) { GETIR[z] = oncekiAd + " " + gunler[j]; z++; test = 1; } if (isBuilt) { int left = tarih.indexOf("."); int right = tarih.lastIndexOf("."); String sub = tarih.substring(left + 1, right); ay = Integer.parseInt(sub); int left2 = tarih.lastIndexOf("."); int right2 = tarih.lastIndexOf(""); String sub2 = tarih.substring(left2 + 1, right2); yil = Integer.parseInt(sub2); } Build(); } if (test == 1) { yeniAd[c] = oncekiAd; for (int j = ilk; j <= son; j++) { if (gunler[j] != 0) { Calendar date = Calendar.getInstance(); date.set(yil, ay - 1, gunler[j]); if (date.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY || date.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY) { } else { yeniAd[c] += " " + gunler[j]; } } } c++; test = 0; } for (int j = ilk; j <= son; j++) { gunler[j] = j; } } if (!(cell.getStringCellValue().isEmpty()) && !(ad.equals("Personel Ad Soyad"))) { int left = tarih.indexOf(0); int right = tarih.indexOf("."); String sub = tarih.substring(left + 1, right); gunler[Integer.parseInt(sub)] = 0; i++; } } } } }
From source file:ar.edu.unrc.gametictactoe.performanceandtraining.configurations.StatisticExperiment.java
License:Open Source License
/** * * @param filePath/*from w w w.j a v a 2 s . c o m*/ * @param backupFiles * @param resultsPerFile * @param resultsRandom * @param randomPerceptronFile <p> * @throws IOException * @throws InvalidFormatException */ public void exportToExcel(String filePath, List<File> backupFiles, Map<File, StatisticForCalc> resultsPerFile, Map<File, StatisticForCalc> resultsRandom, File randomPerceptronFile) throws IOException, InvalidFormatException { InputStream inputXLSX = this.getClass() .getResourceAsStream("/ar/edu/unrc/gametictactoe/resources/EstadisticasTicTacToe.xlsx"); Workbook wb = WorkbookFactory.create(inputXLSX); try (FileOutputStream outputXLSX = new FileOutputStream( filePath + "_" + dateFormater.format(dateForFileName) + "_STATISTICS" + ".xlsx")) { //============= imptimimos en la hoja de % Of Games Won =================== Sheet sheet = wb.getSheetAt(0); //Estilo par los titulos de las tablas int rowStartTitle = 0; int colStartTitle = 2; int rowStart = 1; int colStart = 3; Row rowPlayer1; Row rowPlayer2; Row rowDraw; // Luego creamos el objeto que se encargar de aplicar el estilo a la celda Font fontCellTitle = wb.createFont(); fontCellTitle.setFontHeightInPoints((short) 10); fontCellTitle.setFontName("Arial"); fontCellTitle.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle CellStyleTitle = wb.createCellStyle(); CellStyleTitle.setWrapText(true); CellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER); CellStyleTitle.setVerticalAlignment(CellStyle.VERTICAL_TOP); CellStyleTitle.setFont(fontCellTitle); // Establecemos el tipo de sombreado de nuestra celda CellStyleTitle.setFillBackgroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); CellStyleTitle.setFillPattern(CellStyle.SOLID_FOREGROUND); loadTitle(rowStartTitle, colStartTitle, sheet, backupFiles.size(), CellStyleTitle); //estilo titulo finalizado //Estilo de celdas con los valores de las estadisticas CellStyle cellStyle = wb.createCellStyle(); cellStyle.setWrapText(true); /* We are now ready to set borders for this style */ /* Draw a thin left border */ cellStyle.setBorderLeft(CellStyle.BORDER_THIN); /* Add medium right border */ cellStyle.setBorderRight(CellStyle.BORDER_THIN); /* Add dashed top border */ cellStyle.setBorderTop(CellStyle.BORDER_THIN); /* Add dotted bottom border */ cellStyle.setBorderBottom(CellStyle.BORDER_THIN); //estilo celdas finalizado //loadTitle(rowStartTitle, colStartTitle, sheet, backupFiles.size(), CellStyleTitle); rowPlayer1 = sheet.getRow(rowStart); rowPlayer2 = sheet.getRow(rowStart + 1); rowDraw = sheet.getRow(rowStart + 2); for (int file = 0; file < backupFiles.size(); file++) { Cell cellPlayer1 = rowPlayer1.createCell(file + colStart, Cell.CELL_TYPE_NUMERIC); Cell cellPlayer2 = rowPlayer2.createCell(file + colStart, Cell.CELL_TYPE_NUMERIC); Cell cellDraw = rowDraw.createCell(file + colStart, Cell.CELL_TYPE_NUMERIC); cellPlayer1.setCellStyle(cellStyle); cellPlayer2.setCellStyle(cellStyle); cellDraw.setCellStyle(cellStyle); Double cellValuePlayer1 = resultsPerFile.get(backupFiles.get(file)).getWinRatePlayer1(); Double cellValuePlayer2 = resultsPerFile.get(backupFiles.get(file)).getWinRatePlayer2(); Double cellValueDraw = resultsPerFile.get(backupFiles.get(file)).getDrawRate(); assert cellValuePlayer1 <= 100 && cellValuePlayer1 >= 0; assert cellValuePlayer2 <= 100 && cellValuePlayer2 >= 0; assert cellValueDraw <= 100 && cellValueDraw >= 0; //assert cellValueDraw + cellValuePlayer1 + cellValuePlayer2 == 100; cellDraw.setCellValue(cellValueDraw); cellPlayer1.setCellValue(cellValuePlayer1); cellPlayer2.setCellValue(cellValuePlayer2); } if (!resultsRandom.isEmpty()) { int file = 0;//hay que ir a buscar el randomfile Cell cellDraw = rowDraw.createCell(file + colStart - 1, Cell.CELL_TYPE_NUMERIC); Cell cellPlayer1 = rowPlayer1.createCell(file + colStart - 1, Cell.CELL_TYPE_NUMERIC); Cell cellPlayer2 = rowPlayer2.createCell(file + colStart - 1, Cell.CELL_TYPE_NUMERIC); cellDraw.setCellStyle(cellStyle); cellPlayer1.setCellStyle(cellStyle); cellPlayer2.setCellStyle(cellStyle); // StatisticForCalc get = resultsRandom.get(randomPerceptronFile); // Double cellValuePlayer1 = get.getWinRatePlayer1(); Double cellValuePlayer1 = resultsRandom.get(randomPerceptronFile).getWinRatePlayer1(); Double cellValuePlayer2 = resultsRandom.get(randomPerceptronFile).getWinRatePlayer2(); Double cellValueDraw = resultsRandom.get(randomPerceptronFile).getDrawRate(); //assert cellValueDraw + cellValuePlayer1 + cellValuePlayer2 == 100; cellPlayer1.setCellValue(cellValuePlayer1); cellPlayer2.setCellValue(cellValuePlayer2); cellDraw.setCellValue(cellValueDraw); } wb.write(outputXLSX); } }
From source file:au.com.onegeek.lambda.parser.Excel2SeleniumParser.java
License:Apache License
private void parse(InputStream stream) throws CannotCompileException, NotFoundException, CannotCreateTestClassException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException { logger.debug("Parsing..."); if (this.dataMap != null && this.tests != null) { return;/*from w ww .j av a2 s. c om*/ } this.dataMap = new ArrayList<Map<String, Object>>(); this.tests = new ArrayList<Class<Test>>(); Workbook workbook = null; try { workbook = new XSSFWorkbook(stream); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } logger.debug("workbook" + workbook.toString()); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); if (sheet.getSheetName().startsWith("data")) { // parse key\value pairs HashMap<String, Object> map = new HashMap<String, Object>(); dataMap.add(map); boolean done = false; Row row = sheet.getRow(sheet.getFirstRowNum()); while (!done && row != null && row.getPhysicalNumberOfCells() > 0) { // TODO: parse numerics correctly (i.e. don't add decimal points if not needed) String key = (String) XslxUtil.objectFrom(workbook, row.getCell(0)); String value = null; try { value = (String) XslxUtil.objectFrom(workbook, row.getCell(1)); logger.debug("Adding variable to map: " + key + ":" + value); map.put(key, value); row = sheet.getRow(row.getRowNum() + 1); if (row == null || (row.getRowNum() == sheet.getLastRowNum() + 1)) { done = true; } } catch (NullPointerException e) { //throw new CannotCreateVariableException("No value found for variable '" + key + "' in dataset: " + sheet.getSheetName()); done = true; } } } } JavassistTestBuilderImpl builder = JavassistTestBuilderImpl.getInstance(); // Parse Test sheets into Test objects for (int s = 0; s < workbook.getNumberOfSheets(); s++) { Sheet sheet = workbook.getSheetAt(s); int i = 0; // Ignore data sheets if (sheet.getSheetName().startsWith("suite")) { int maxRows = sheet.getPhysicalNumberOfRows(); int currentRow = sheet.getFirstRowNum(); logger.debug("Nr rows in sheet: " + maxRows); // Create Test Class String testCaseName = "Test" + Excel2SeleniumParser.toCamelCase(sheet.getSheetName()); logger.debug("Creating Test class with name: " + testCaseName); builder.makeTestClass(testCaseName, this.dataMap); boolean testCaseInProgress = false; boolean dataProviderAdded = false; // Get First row, containing the test name and the data to be injected while (i < maxRows) { logger.debug("i: " + i); logger.debug("currentRow: " + currentRow); Row row = sheet.getRow(currentRow); TestCommand command = null; // Check for empty row if (row != null && row.getPhysicalNumberOfCells() != 0) { i++; // Get Cells Iterator<Cell> iterator = row.cellIterator(); while (iterator.hasNext()) { Cell cell = iterator.next(); String cellValue = (cell == null || cell.toString() == "") ? "" : XslxUtil.objectFrom(workbook, cell).toString(); logger.debug("Cell: " + cellValue); if (cellValue.startsWith("test")) { logger.debug("Test case found: " + cellValue + ". Creating Test Case"); // Create new Test CASE try { builder.addTest(cellValue); testCaseInProgress = true; dataProviderAdded = false; } catch (CannotModifyTestMethodException e) { e.printStackTrace(); throw new CannotCreateTestClassException( "Could not create Test Class as there was a variable not found in test assertion. Embedded exception: " + e.getMessage()); } catch (VariableNotFoundException e) { e.printStackTrace(); throw new CannotCreateTestClassException( "Could not create Test Class as there was a variable not found in test assertion. Embedded exception: " + e.getMessage()); } break; } else { if (command == null & !cellValue.equals("")) { logger.debug("Command found: " + cellValue + ". Creating new TestCommand"); command = new TestCommand(cellValue); } else if (!cellValue.equals("")) { logger.debug("Command argument found: " + cellValue); command.addParameter(cellValue); } } } } else { // Blank row could mean a test case has just been completed // Complete last test case by adding a data provider if (testCaseInProgress && !dataProviderAdded) { try { logger.debug("In Progress Test Case now being closed off and added to class..."); builder.addDataProvider(); dataProviderAdded = true; logger.debug("In Progress Test Case now closed off!"); } catch (CannotCreateDataProviderException e) { throw new CannotCreateTestClassException( "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: " + e.getMessage()); } } } try { if (command != null) { logger.debug("Adding command to method"); builder.appendTestToLastMethod(command); } } catch (CannotModifyTestMethodException e) { throw new CannotCreateTestClassException("Unable to add Test Case '" + command.toString() + "' to Test Class. Embedded exception: " + e.getMessage()); } catch (VariableNotFoundException e) { throw new CannotCreateTestClassException("Unable to add Test Case '" + command.toString() + "' to Test Class as a variable was not found. Embedded exception: " + e.getMessage()); } currentRow++; } // Blank row could mean a test case has just been completed // Complete last test case by adding a data provider logger.debug( "End of rows...Checking if In Progress Test Case now being closed off and added to class..."); if (testCaseInProgress && !dataProviderAdded) { logger.debug(" In Progress Test Case now being closed off and added to class..."); try { builder.addDataProvider(); dataProviderAdded = true; logger.debug("In Progress Test Case now closed off!"); } catch (CannotCreateDataProviderException e) { throw new CannotCreateTestClassException( "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: " + e.getMessage()); } } if (testCaseInProgress) { logger.debug("Generating class file"); try { this.tests.add(builder.getCreatedClass()); } catch (CannotModifyTestMethodException e) { e.printStackTrace(); throw new CannotCreateTestClassException( "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: " + e.getMessage()); } testCaseInProgress = false; } } } try { stream.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } logger.info("Looking at our classes..."); // Look at the Test Objects for (Class<Test> clazz : tests) { logger.info("Class: " + clazz.getName()); for (Method m : clazz.getMethods()) { logger.info("Method: " + m); if (m.getName().equalsIgnoreCase("testRetailDataProvider")) { logger.info("invoking data provider"); Test test = clazz.newInstance(); Object[][] data = (Object[][]) m.invoke(test); for (Object[] obs : data) { for (Object o : obs) { logger.info("data value: " + o); } } } } } }
From source file:au.gov.ansto.bragg.quokka.experiment.util.ExperimentModelUtils.java
License:Open Source License
public static void refineExperimentFromExcel(Experiment experiment, InputStream input) throws IOException { // Sample environment is not supported at this stage if (experiment.isControlledEnvironment()) { return;/* w ww . j a va2s .c o m*/ } // Clear existing acquisition entries experiment.getNormalAcquisition().getEntries().clear(); // Read from a Excel file Workbook workbook = new HSSFWorkbook(input); Sheet sheet = workbook.getSheetAt(0); // Start from row 3 for (int i = 2; i < sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); int samplePosition = Integer.parseInt(row.getCell(1).getStringCellValue()); Sample sample = experiment.getSamples().get(samplePosition); AcquisitionEntry entry = new AcquisitionEntry(sample); experiment.getNormalAcquisition().getEntries().add(entry); } }
From source file:bad.robot.excel.row.Row.java
License:Apache License
public void insertAt(Workbook workbook, SheetIndex sheetIndex, RowIndex rowIndex) { Sheet sheet = workbook.getSheetAt(sheetIndex.value()); sheet.shiftRows(rowIndex.value(), sheet.getLastRowNum(), shiftDownAmount); org.apache.poi.ss.usermodel.Row row = sheet.createRow(rowIndex.value()); copyCellsTo(row, workbook);/*from w ww . j a va 2 s .c om*/ }
From source file:bad.robot.excel.row.Row.java
License:Apache License
public void appendTo(Workbook workbook, SheetIndex index) { Sheet sheet = workbook.getSheetAt(index.value()); org.apache.poi.ss.usermodel.Row row = createRow(sheet); copyCellsTo(row, workbook);//from w w w.j a v a 2s .c o m }
From source file:bad.robot.excel.WorkbookResource.java
License:Apache License
public static org.apache.poi.ss.usermodel.Row getRowForCoordinate(Coordinate coordinate, Workbook workbook) throws IOException { Sheet sheet = workbook.getSheetAt(coordinate.getSheet().value()); org.apache.poi.ss.usermodel.Row row = sheet.getRow(coordinate.getRow().value()); if (row == null) throw new IllegalStateException("expected to find a row"); return row;/* w w w .j ava2 s . c o m*/ }
From source file:bandaru_excelreadwrite.ReadfromExcel.java
public List getSongsListFromExcel() { List songList = new ArrayList(); FileInputStream fis = null;//from ww w . j a va2s . 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) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return songList; }
From source file:blanco.commons.calc.parser.AbstractBlancoCalcParser.java
License:Open Source License
/** * ???/* w w w.ja v a 2 s. c om*/ * * @param workbook * * @throws SAXException * SAX???? */ private void parseWorkbook(final Workbook workbook) throws SAXException { getContentHandler().startDocument(); getContentHandler().startElement("", (String) getProperty(URI_PROPERTY_NAME_WORKBOOK), (String) getProperty(URI_PROPERTY_NAME_WORKBOOK), new AttributesImpl()); for (int indexSheet = 0; indexSheet < workbook.getNumberOfSheets(); indexSheet++) { Sheet sheet = workbook.getSheetAt(indexSheet); parseSheet(sheet); } getContentHandler().endElement("", (String) getProperty(URI_PROPERTY_NAME_WORKBOOK), (String) getProperty(URI_PROPERTY_NAME_WORKBOOK)); getContentHandler().endDocument(); }
From source file:bo.com.offercruzmail.imp.InterpretadorMensajeGenerico.java
protected Multipart enviarPlantilla(boolean plantillaNueva, String idCargar) throws MessagingException, IOException { String nombreArchivoOrigen;/* w w w . j a v a 2s. c o m*/ String nombreAdjunto; List<T> lista = null; mensajesError = null; T entidad = null; getObjetoNegocio().setIdUsuario(idUsuario); getObjetoNegocio().setComandoPermiso(nombreEntidad); try { if (!plantillaNueva) { if ("todos".equals(idCargar)) { lista = getObjetoNegocio().obtenerTodos(); nombreArchivoOrigen = nombreEntidad + "-" + "lista"; nombreAdjunto = "lista_" + nombreEntidad + ".xlsx"; } else { ID id; try { id = convertirId(idCargar); } catch (Exception ex) { return FormadorMensajes.enviarIdCargarNoValido(); } entidad = getObjetoNegocio().recuperarPorId(id); if (entidad == null) { return FormadorMensajes.enviarEntidadNoExiste(idCargar); } nombreArchivoOrigen = nombreEntidad; nombreAdjunto = nombreEntidad + "_" + idCargar + ".xlsx"; } } else { nombreArchivoOrigen = nombreEntidad; nombreAdjunto = "plantilla_" + nombreEntidad + ".xlsx"; // if (this instanceof IInterpretadorFormularioDasometrico) { // if (cargarPlantillaFormularios) { // nombreArchivoOrigen = "plantillafrm"; // } // } } String nombreArchivoOriginal = "plantillas/" + nombreArchivoOrigen + ".xlsx"; File archivoCopia = UtilitariosMensajes.reservarNombre(nombreEntidad); UtilitariosMensajes.copiarArchivo(new File(nombreArchivoOriginal), archivoCopia); archivosTemporales.add(archivoCopia); FileInputStream fis = null; OutputStream os = null; try { Workbook libro; fis = new FileInputStream(archivoCopia); libro = WorkbookFactory.create(fis); hojaActual = new HojaExcelHelper(libro.getSheetAt(0)); if (plantillaNueva) { preparPlantillaAntesDeEnviar(libro); } else { if (lista != null) { mostrarLista(lista); } else { mostrarEntidad(entidad, libro); } } if (mensajesError != null) { return FormadorMensajes.enviarErroresNegocio(mensajesError); } //Guardamos cambio os = new FileOutputStream(archivoCopia); libro.write(os); } catch (InvalidFormatException ex) { } finally { if (fis != null) { fis.close(); } if (os != null) { os.close(); } } String textoMensaje; if (plantillaNueva) { textoMensaje = escapeHtml4("La plantilla est adjunta a este mensaje."); } else if (lista != null) { textoMensaje = "La consulta ha devuelto " + lista.size() + " registro(s)."; } else { textoMensaje = escapeHtml4("El registro solicitado est adjunto a este mensaje"); } Multipart cuerpo = new MimeMultipart(); BodyPart adjunto = new MimeBodyPart(); DataSource origen = new FileDataSource(archivoCopia); adjunto.setDataHandler(new DataHandler(origen)); adjunto.setFileName(nombreAdjunto); cuerpo.addBodyPart(FormadorMensajes.getBodyPartEnvuelto(textoMensaje)); cuerpo.addBodyPart(adjunto); return cuerpo; } catch (PermisosInsuficientesException ex) { appendException(new BusinessExceptionMessage(ex.getMessage(), "Autentificacion")); } if (mensajesError != null) { return FormadorMensajes.enviarErroresNegocio(mensajesError); } return null; }