List of usage examples for org.apache.poi.ss.usermodel Workbook sheetIterator
Iterator<Sheet> sheetIterator();
From source file:org.wso2.security.tool.adapter.ExcelInputAdapter.java
License:Open Source License
/** * Converts the data in the files with .xlsx extension to the JSON format. * A workbook is created from the the excel file (.xlsx) and while iterating through the sheets in the workbook; * the data is read and set in to a JSONObject. The JSONObject returned by the method contains an array of * row objects corresponding to each row in the workbook. A row object contains values of each cell in a given row, * with key values starting from letter 'A'. * * @param dataFilePath The path where the data file uploaded is saved. * @return returns the JSON object that contains all the data in the .xlsx file. * @throws FeedbackToolException If the .xlsx file is not found in the given path or due to an error in * parsing the data in the data file. *///from w w w . ja v a 2 s . c om @Override public JSONObject convert(String dataFilePath) throws FeedbackToolException { // JSONObject to hold the array of row objects JSONObject dataJSONObject = new JSONObject(); try { Workbook workbook = WorkbookFactory.create(new File(dataFilePath)); logInfo = "Workbook has " + workbook.getNumberOfSheets() + " sheets"; log.info(logInfo); Iterator<Sheet> sheetIterator = workbook.sheetIterator(); // JSONArray to hold all the row objects JSONArray rowsJSONArray = new JSONArray(); while (sheetIterator.hasNext()) { Sheet sheet = sheetIterator.next(); logInfo = "Sheet: " + sheet.getSheetName() + " has " + sheet.getNumMergedRegions() + " merged regions"; log.info(logInfo); DataFormatter dataFormatter = new DataFormatter(); logInfo = "Iterating over Rows and Columns using for-each loop"; log.info(logInfo); for (Row row : sheet) { // JSONObject to hold the data in the cells of a given row JSONObject rowJSONObject = new JSONObject(); char keyLetter = 'A'; for (Cell cell : row) { String cellValue = dataFormatter.formatCellValue(cell); rowJSONObject.put(keyLetter, cellValue); ++keyLetter; } rowsJSONArray.add(rowJSONObject); } } dataJSONObject.put(Constants.JSON_DATA_OBJECT, rowsJSONArray); } catch (InvalidFormatException e) { throw new FeedbackToolException("Error in parsing the data file uploaded", e); } catch (IOException e) { throw new FeedbackToolException("Data file was not found in the specified location", e); } return dataJSONObject; }
From source file:rpt.GUI.ProgramManager.Variants.VariantsController.java
public void importButtonClicked(ActionEvent event) throws IOException { //List with all variants read from the imported Excel file List<Variant> variants = new ArrayList(); //Create File Chooser window FileChooser fileChooser = new FileChooser(); fileChooser.setTitle("Import Excel File"); //Set filter to allow only Excel files ExtensionFilter filter = new ExtensionFilter("Excel Files", "*.xls", "*.xlsx"); fileChooser.getExtensionFilters().addAll(filter); //Show File Selector File selectedFile = fileChooser.showOpenDialog(null); //import Excel file if a file has been selected, if not, do nothing //based on good example on: //http://www.codejava.net/coding/how-to-read-excel-files-in-java-using-apache-poi if (selectedFile != null) { System.out.println("File selected: " + selectedFile.getPath()); FileInputStream inputStream = new FileInputStream(new File(selectedFile.getPath())); Workbook workbook = new HSSFWorkbook(inputStream); //Use Sheet iterator to extract all sheet names Iterator<Sheet> sheetIterator = workbook.sheetIterator(); //Iterate over all sheets and populate a checkboxfield and let user select on of the sheets while (sheetIterator.hasNext()) { Sheet nextSheet = sheetIterator.next(); //TODO //Create dialogbox with a radio button selection showing all the available sheets. //Clicking on cancel has to break the main if or protect the rest with an if that Cancel wasnt clicked }/* w w w . ja v a2 s . co m*/ //set sheet to the sheet selected Sheet firstSheet = workbook.getSheet("Raw data CP2016A"); //Change to variable name at later date Iterator<Row> rowIterator = firstSheet.iterator(); //find first row //TODO //set first row keyword into application settings Boolean firstRowFound = false; Cell nextCell; Iterator<Cell> cellIterator; do { Row nextRow = rowIterator.next(); cellIterator = nextRow.cellIterator(); nextCell = cellIterator.next(); if (getCellValue(nextCell) != null) {//blank cells return null if (getCellValue(nextCell).equals("Plant")) { firstRowFound = true; } } } while (!firstRowFound && rowIterator.hasNext()); //First row is now found, loop through entire row and build a //dictionary using HashMaps Map<Integer, String> dictionary = new HashMap<Integer, String>(); while (cellIterator.hasNext()) { if (getCellValue(nextCell) != null) { //blank cells return null dictionary.put(nextCell.getColumnIndex(), (String) getCellValue(nextCell)); } nextCell = cellIterator.next(); } //loop through all rows in the file while (rowIterator.hasNext()) { Row nextRow = rowIterator.next(); cellIterator = nextRow.cellIterator(); Variant aVariant = new Variant(); //loop through all columns in the row while (cellIterator.hasNext()) { nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); if (getCellValue(nextCell) != null) { aVariant.setValue(dictionary.get(nextCell.getColumnIndex()), getCellValue(nextCell)); // switch (columnIndex) { // case 0: // aVariant.setPlant((String) getCellValue(nextCell)); // break; // case 1: // Double value = ((Double) getCellValue(nextCell)); // aVariant.setPlantCode(value.intValue()); // break; // case 2: // aVariant.setPlantName((String) getCellValue(nextCell)); // break; // default: // break; // } } else { } } variants.add(aVariant); } inputStream.close(); //now that we have a list of Variants we put them into the tableView data.clear(); int index = 1; for (Variant variant : variants) { TableVariants entry = new TableVariants(index, variant.getEngineName(), variant.getDenomination(), variant.getGearbox(), variant.getEmiss()); add(entry); index++; } } //TODO remove the else, we don't do anything if the user presses Cancel else { System.out.println("File selection cancelled."); } }
From source file:rpt.GUI.ProgramStrategist.CyclePlans.CyclePlansController.java
public void importButtonClicked(ActionEvent event) throws IOException { //List with all variants read from the imported Excel file List<TableVariant> variants = new ArrayList(); //Create File Chooser window FileChooser fileChooser = new FileChooser(); fileChooser.setTitle("Import Excel File"); //Set filter to allow only Excel files ExtensionFilter filter = new ExtensionFilter("Excel Files", "*.xls", "*.xlsx"); fileChooser.getExtensionFilters().addAll(filter); //Show File Selector File selectedFile = fileChooser.showOpenDialog(null); //import Excel file if a file has been selected, if not, do nothing //based on good example on: //http://www.codejava.net/coding/how-to-read-excel-files-in-java-using-apache-poi if (selectedFile != null) { //open dialog box and show available sheets in the file // the dialog box will then process the file and add data into the table FileInputStream inputStream = new FileInputStream(new File(selectedFile.getPath())); Workbook workbook; if (selectedFile.getPath().endsWith("xlsx")) { workbook = new XSSFWorkbook(inputStream); } else {/* ww w . j a v a 2 s . com*/ workbook = new HSSFWorkbook(inputStream); } //Use Sheet iterator to extract all sheet names Iterator<Sheet> sheetIterator = workbook.sheetIterator(); //Iterate over all sheets and populate a checkboxfield and let user select on of the sheets sheetsInFile = new ArrayList(); //reset just in case while (sheetIterator.hasNext()) { String nextSheet = sheetIterator.next().getSheetName(); sheetsInFile.add(nextSheet); //add found sheet into list of available sheets. } selectedSheet = null; //Show dialog box presenting all the available sheets for the user to select from Stage stage; Parent root; stage = new Stage(); root = FXMLLoader .load(getClass().getResource("/rpt/GUI/ProgramStrategist/CyclePlans/dialogSelectSheet.fxml")); stage.setScene(new Scene(root)); stage.setTitle("Select Sheet"); stage.initModality(Modality.APPLICATION_MODAL); stage.showAndWait(); // pause until the user has selected a sheet // If user has selected sheet to read, show next dialog box allowing // user to set the name of the imported cycle plan if (selectedSheet != null) { //preset the file name to the file name part before the . sign importedCyclePlanName = selectedFile.getName().split("\\.")[0]; //Create dialog stage = new Stage(); root = FXMLLoader .load(getClass().getResource("/rpt/GUI/ProgramStrategist/CyclePlans/dialogSetName.fxml")); stage.setScene(new Scene(root)); stage.setTitle("Set Cycle Plan Name"); stage.initModality(Modality.APPLICATION_MODAL); stage.showAndWait(); // pause until the user has selected a sheet } // only continue if a sheet was selected (=abort if used presses cancel) // AND if the cycle plan name is unique, i.e. not already imported if (selectedSheet != null && importedCyclePlanName != null) { // Add new cycleplan into Database try { Statement statement = RPT.conn.createStatement(); statement.setQueryTimeout(30); query = "INSERT INTO CYCLEPLANS (Name, Version) VALUES (\'" + importedCyclePlanName + "\', 1)"; statement.executeUpdate(query); //set sheet to the sheet selected Sheet firstSheet = workbook.getSheet(selectedSheet); Iterator<Row> rowIterator = firstSheet.iterator(); //find first row //TODO //set first row keyword into application settings Boolean firstRowFound = false; Cell nextCell; Iterator<Cell> cellIterator; //dictionary using HashMaps Map<Integer, String> dictionary = new HashMap<Integer, String>(); do { Row nextRow = rowIterator.next(); cellIterator = nextRow.cellIterator(); nextCell = cellIterator.next(); if (getCellValue(nextCell) != null) {//blank cells return null if (getCellValue(nextCell).equals("Plant")) { //dictionary.put(nextCell.getColumnIndex(), (String) getCellValue(nextCell)); firstRowFound = true; } } } while (!firstRowFound && rowIterator.hasNext()); //First row is now found, loop through entire row and build a while (cellIterator.hasNext()) { if (getCellValue(nextCell) != null) { //blank cells return null dictionary.put(nextCell.getColumnIndex(), getCellValue(nextCell).toString()); } nextCell = cellIterator.next(); } // Since Excel stores numbers as floats and as an example 1 will turn into 1.0 this will be a problem // Mainly because the cycle plan tends to mix text and numbers in Excel. // Create a Dataformatter which will be used to solve this DataFormatter fmt = new DataFormatter(); //loop through all rows in the file while (rowIterator.hasNext()) { Row nextRow = rowIterator.next(); cellIterator = nextRow.cellIterator(); TableVariant aVariant = new TableVariant(); //loop through all columns in the row while (cellIterator.hasNext()) { nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); if (getCellValue(nextCell) != null) { aVariant.setValue(dictionary.get(nextCell.getColumnIndex()), fmt.formatCellValue(nextCell)); //aVariant.setValue(dictionary.get(nextCell.getColumnIndex()), getCellValue(nextCell).toString()); } else { } } variants.add(aVariant); } } catch (Exception e) { System.err.println("CyclePlansController line 343: " + e.getMessage()); } //remove current selection and add the new variants data.clear(); int index = 1; for (TableVariant variant : variants) { TableVariant entry = new TableVariant(variant.getPlant(), variant.getPlatform(), variant.getVehicle(), variant.getPropulsion(), variant.getDenomination(), variant.getFuel(), variant.getEngineFamily(), variant.getGeneration(), variant.getEngineName(), variant.getEngineCode(), variant.getDisplacement(), variant.getEnginePower(), variant.getElMotorPower(), variant.getTorque(), variant.getTorqueOverBoost(), variant.getGearboxType(), variant.getGears(), variant.getGearbox(), variant.getDriveline(), variant.getTransmissionCode(), variant.getCertGroup(), variant.getEmissionClass(), variant.getStartOfProd(), variant.getEndOfProd()); add(entry); index++; try { Statement statement = RPT.conn.createStatement(); statement.setQueryTimeout(30); String variantID = variant.getVehicle() + variant.getEngineCode() + variant.getTransmissionCode() + variant.getEmissionClass() + variant.getStartOfProd(); query = "SELECT COUNT(VariantID) FROM VARIANTS WHERE VariantID = '" + variantID + "'"; ResultSet rs = statement.executeQuery(query); //check count of previous query, 0 = new variant, 1 = it already exists Integer count = rs.getInt(1); // add variant if it does not exist if (count == 0) { // entry did not existbefore query = "INSERT INTO VARIANTS (" + "Plant, Platform, Vehicle, Propulsion, Denomination, Fuel, EngineFamily, Generation, EngineCode, Displacement, " + "EnginePower, ElMotorPower, Torque, TorqueOverBoost, GearboxType, Gears, Gearbox, Driveline, TransmissionCode, " + "CertGroup, EmissionClass, StartOfProd, EndOfProd, VariantID" + ")" + "" + " VALUES (\'" + variant.getPlant() + "\', \'" + variant.getPlatform() + "\', \'" + variant.getVehicle() + "\', \'" + variant.getPropulsion() + "\', \'" + variant.getDenomination() + "\', \'" + variant.getFuel() + "\', \'" + variant.getEngineFamily() + "\', \'" + variant.getGeneration() + "\', \'" + variant.getEngineCode() + "\', \'" + variant.getDisplacement() + "\', \'" + variant.getEnginePower() + "\', \'" + variant.getElMotorPower() + "\', \'" + variant.getTorque() + "\', \'" + variant.getTorqueOverBoost() + "\', \'" + variant.getGearboxType() + "\', \'" + variant.getGears() + "', '" + variant.getGearbox() + "\', \'" + variant.getDriveline() + "\', \'" + variant.getTransmissionCode() + "\', \'" + variant.getCertGroup() + "\', \'" + variant.getEmissionClass() + "\', \'" + variant.getStartOfProd() + "\', \'" + variant.getEndOfProd() + "\', \'" + variantID + "\')"; statement.executeUpdate(query); } // Add relation between cycle plan and variant query = "INSERT INTO VariantBelongsToCyclePlan (VariantID, CyclePlanID) VALUES (\'" + variantID + "\', \'" + importedCyclePlanName + "\')"; statement.executeUpdate(query); } catch (Exception e) { System.out.println("Query: " + query); System.err.println("CyclePlansController line 394: " + e.getMessage()); } } cyclePlanList.add(importedCyclePlanName); cyclePlanSelector.getSelectionModel().select(importedCyclePlanName); } // end of reading file after user has selected sheet and name of cycle plan inputStream.close(); } }