List of usage examples for org.apache.poi.ss.usermodel Workbook getSheet
Sheet getSheet(String name);
From source file:org.wso2.samples.RegistryResourceImporter.java
License:Open Source License
private static void addAssets(Registry registry, Workbook[] workbooks, String artifactType, Properties artifactMappings) throws Exception { for (Workbook workbook : workbooks) { Sheet sheet = workbook.getSheet(workbook.getSheetName(0)); if (sheet == null || sheet.getLastRowNum() == -1) { throw new RuntimeException("The first sheet is empty"); } else {/*from w w w . j a v a 2 s . co m*/ System.out.println("Adding data in Sheet : " + sheet.getSheetName()); } int limit = sheet.getLastRowNum(); if (limit < 1) { throw new RuntimeException("Column headers were not specified in Asset Data Spreadsheet"); } else { System.out.println("Total number of rows in the sheet : " + limit); } Row row = sheet.getRow(0); // We use a linked list to keep the order List<String> headersAttributeNames = new LinkedList<String>(); String value; int count = 0; Set artifactAttributes = artifactMappings.keySet(); while ((value = getCellValue(row.getCell(count++), null)) != null) { headersAttributeNames.add(getMappingName(artifactMappings, value)); } Registry governanceRegistry = GovernanceUtils.getGovernanceUserRegistry(registry, USERNAME); addAssetValues(governanceRegistry, sheet, limit, artifactType, headersAttributeNames, artifactAttributes); } }
From source file:playground.michalm.poznan.demand.kbr.PoznanChainsProcessor.java
License:Open Source License
private void processFile(String xlsFile) { try (InputStream inp = new FileInputStream(xlsFile)) { Workbook wb = new HSSFWorkbook(inp); currentSheet = wb.getSheet("Arkusz1"); int lastSurvey = 0; int lastRespondent = 0; int lastTrip = 0; int lastGoal = 0; int lastHour = 0; StringBuilder currentChain = new StringBuilder(); for (int i = 1; i < 45393; i++) { int survey = getInt(i, 0, true); int respondent = getInt(i, 1, true); int trip = getInt(i, 2, false); int goal = getInt(i, 3, false); int hour = getInt(i, 4, false); if (lastSurvey < survey) { lastSurvey = survey;//from www . j a va 2s . co m lastRespondent = -1; } else if (lastSurvey > survey) { error(i, 0, "last survey > current survey"); } if (lastRespondent < respondent) { registerChain(currentChain); lastRespondent = respondent; lastTrip = 0; lastGoal = 0; lastHour = 0; currentChain = new StringBuilder(); } else if (lastRespondent > respondent) { error(i, 1, "last respondent > current respondent"); } if (trip == -1 && lastTrip > 0) { trip = lastTrip;// transfer } if (trip >= lastTrip) { if (trip > lastTrip + 1) { warning(i, 2, "last trip and current trip do not fit"); } if (trip == lastTrip) {// possibly a transfer??? if (goal != -1 && goal != lastGoal) { // treat this as a new trip!!! warning(i, 3, "motivation different than before transfer"); } else { continue;// just a transfer } } lastTrip = trip; lastGoal = goal; lastHour = hour; currentChain.append(convertGoalNumberToChar(goal)); if (lastHour != -1 && hour != -1 && lastHour > hour) { warning(i, 4, "last hour > current hour"); } } else { error(i, 2, "last trip > current trip"); } } if (currentChain.length() > 0) { registerChain(currentChain); } } catch (IOException e) { throw new RuntimeException(e); } }
From source file:projetrdf.ExcelManager.java
public Datas extractData() throws FileNotFoundException, IOException, InvalidFormatException { FileInputStream fip = new FileInputStream(new File(path)); Workbook workbook = WorkbookFactory.create(fip); Sheet firstSheet = workbook.getSheet(sheet); Iterator<Row> iterator = firstSheet.iterator(); List<Individu> li = new ArrayList<>(); while (iterator.hasNext()) { Row nextRow = iterator.next();/* w w w . j a v a 2s .c o m*/ Iterator<Cell> cellIterator = nextRow.cellIterator(); double[] tabIndividu = new double[nextRow.getLastCellNum()]; String nomIndividu = ""; int cpt = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cpt == 0) { nomIndividu = cell.getStringCellValue(); } else { tabIndividu[cpt - 1] = Double.parseDouble(cell.getStringCellValue()); } cpt++; if (cell.getStringCellValue().equals("")) { break; } } int classe = 0; String[] nom = nomIndividu.split("\\."); classe = Integer.parseInt(nom[0]) / 100; li.add(new Individu(nomIndividu, tabIndividu, classe)); } return new Datas(li); }
From source file:qmul.align.AlignmentTester.java
License:Open Source License
/** * Print a summary sheet on the (gulp) excel spreadsheet * /*from w w w . ja v a 2 s .com*/ * @param wb * @param sheetName * @param speakerScores * @param originalSpks * @param speakerN */ private void printSummarySheet(Workbook wb, String sheetName, HashMap<String, ArrayList<Double>> speakerScores, HashMap<String, String> originalSpks, HashMap<String, ArrayList<Double>> speakerN, MetricsMap spkMetrics, MetricsMap totMetrics, boolean pairedCorpus) { CreationHelper creationHelper = wb.getCreationHelper(); sheetName = (sheetName == null ? "Summary" : shorten(sheetName)); System.out.println("Checking workbook " + wb + " for sheet " + sheetName); Sheet sheet = wb.getSheet(sheetName); if (sheet != null) { System.out.println("Exists, removing sheet " + sheetName); wb.removeSheetAt(wb.getSheetIndex(sheet)); } sheet = wb.createSheet(sheetName); wb.setSheetOrder(sheetName, 0); int iRow = 0; // first general identifying stuff Row row = sheet.createRow(iRow++); row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Corpus")); row.createCell(1, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(getCorpus().getId())); row = sheet.createRow(iRow++); row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Windower")); row.createCell(1, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(getWin().toString())); row = sheet.createRow(iRow++); row.createCell(0, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Similarity Measure")); row.createCell(1, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(getSim().toString())); // now header row = sheet.createRow(iRow++); row = sheet.createRow(iRow++); int iCol = 0; row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Speaker")); row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Genre")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Orig Speaker")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Orig Genre")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Speaker #units")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Dialogue #units")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Speaker #words")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Dialogue #words")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Speaker #tokens")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Dialogue #tokens")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Speaker avg offset")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Dialogue avg offset")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Speaker avg wordrate")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Dialogue avg wordrate")); iCol++; for (int i = 0; i < getWin().getLeftWindowSize(); i++) { row.createCell(i + iCol, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Mean i-" + (i + 1))); } // now means per speaker List<String> spks = new ArrayList<String>(speakerScores.keySet()); Collections.sort(spks); List<Double> means = new ArrayList<Double>(); List<Double> nums = new ArrayList<Double>(); for (int i = 0; i < getWin().getLeftWindowSize(); i++) { means.add(0.0); nums.add(0.0); } int nAll = 0; int nMatch = 0; for (String spk : spks) { // System.out.println("org chk [" + originalSpks.get(spk) + "][" + spk + "]"); boolean matching = false; if ((originalSpks.get(spk) != null) && originalSpks.get(spk).contains(":")) { int li = originalSpks.get(spk).lastIndexOf(":"); String pre = originalSpks.get(spk).substring(0, li); String suf = originalSpks.get(spk).substring(li); matching = spk.startsWith(pre) && spk.endsWith(suf); } nAll++; if (!pairedCorpus || matching) { nMatch++; // System.out.println("match " + pre + " " + suf); row = sheet.createRow(iRow++); iCol = 0; String dId = spk.replaceFirst("(.*)_.*", "$1"); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(spk)); row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue( creationHelper.createRichTextString(corpus.getGenreMap().get(spk.split(":")[0]))); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(originalSpks.get(spk))); row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper .createRichTextString(corpus.getGenreMap().get(originalSpks.get(spk).split(":")[0]))); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(spkMetrics.getNumUnits(spk)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(totMetrics.getNumUnits(dId)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(spkMetrics.getNumWords(spk)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(totMetrics.getNumWords(dId)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(spkMetrics.getNumTokens(spk)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(totMetrics.getNumTokens(dId)); if (Double.isNaN(spkMetrics.getTurnOffset(spk)) || spkMetrics.getNumTurnOffsets(spk) == 0) { iCol++; } else { row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue( spkMetrics.getTurnOffset(spk) / (double) spkMetrics.getNumTurnOffsets(spk)); } if (Double.isNaN(totMetrics.getTurnOffset(dId)) || totMetrics.getNumTurnOffsets(dId) == 0) { iCol++; } else { row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue( totMetrics.getTurnOffset(dId) / (double) totMetrics.getNumTurnOffsets(dId)); } if (Double.isNaN(spkMetrics.getWordRate(spk)) || spkMetrics.getNumWordRates(spk) == 0) { iCol++; } else { row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC) .setCellValue(spkMetrics.getWordRate(spk) / (double) spkMetrics.getNumWordRates(spk)); } if (Double.isNaN(totMetrics.getWordRate(dId)) || totMetrics.getNumWordRates(dId) == 0) { iCol++; } else { row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC) .setCellValue(totMetrics.getWordRate(dId) / (double) totMetrics.getNumWordRates(dId)); } iCol++; for (int i = 0; i < speakerScores.get(spk).size(); i++) { if (speakerN.get(spk).get(i) > 0.0) { double mean = speakerScores.get(spk).get(i) / speakerN.get(spk).get(i); row.createCell(i + iCol, Cell.CELL_TYPE_NUMERIC).setCellValue(mean); means.set(i, means.get(i) + mean); nums.set(i, nums.get(i) + 1); } } } } System.out.println("Matched " + nMatch + " of " + nAll); // and a final row for overall means row = sheet.createRow(iRow++); iCol = 14; row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Overall")); for (int i = 0; i < getWin().getLeftWindowSize(); i++) { means.set(i, means.get(i) / nums.get(i)); row.createCell(i + iCol, Cell.CELL_TYPE_NUMERIC).setCellValue(means.get(i)); } }
From source file:qmul.align.AlignmentTester.java
License:Open Source License
/** * Print a summary sheet on the (gulp) excel spreadsheet *//* w w w . ja va2s . com*/ private void printSummaryCountSheet(Workbook wb, String sheetName, HashMap<String, HashMap<Object, Integer>> allCounts, HashMap<String, HashMap<Object, Integer>> commonCounts) { CreationHelper creationHelper = wb.getCreationHelper(); sheetName = (sheetName == null ? "Summary" : shorten(sheetName)); System.out.println("Checking workbook " + wb + " for sheet " + sheetName); Sheet sheet = wb.getSheet(sheetName); if (sheet != null) { System.out.println("Exists, removing sheet " + sheetName); wb.removeSheetAt(wb.getSheetIndex(sheet)); } sheet = wb.createSheet(sheetName); wb.setSheetOrder(sheetName, 0); int iRow = 0; // first general identifying stuff Row row = sheet.createRow(iRow++); row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Corpus")); row.createCell(1, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(getCorpus().getId())); row = sheet.createRow(iRow++); row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Windower")); row.createCell(1, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(getWin().toString())); row = sheet.createRow(iRow++); row.createCell(0, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Similarity Measure")); row.createCell(1, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(getSim().toString())); // now header row = sheet.createRow(iRow++); row = sheet.createRow(iRow++); int iCol = 0; row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Type")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Overall count")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Common count")); for (String genre : allCounts.keySet()) { if (genre.isEmpty()) continue; row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(genre + " overall count")); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(genre + " common count")); } ArrayList<Object> keys = new ArrayList<Object>(allCounts.get("").keySet()); Collections.sort(keys, new DescendingComparator<Object>(allCounts.get(""))); for (Object key : keys) { row = sheet.createRow(iRow++); iCol = 0; row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(key.toString())); Cell cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC); if (allCounts.get("").get(key) != null) { cell.setCellValue(allCounts.get("").get(key)); } cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC); if (commonCounts.get("").get(key) != null) { cell.setCellValue(commonCounts.get("").get(key)); } for (String genre : allCounts.keySet()) { if (genre.isEmpty()) continue; cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC); if (allCounts.get(genre).get(key) != null) { cell.setCellValue(allCounts.get(genre).get(key)); } cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC); if (commonCounts.get(genre).get(key) != null) { cell.setCellValue(commonCounts.get(genre).get(key)); } } } }
From source file:raphdine.comptes.utils.ImportExcel.java
private void extract(FileInputStream fis) { try {//from ww w . j a va 2 s. c o m Workbook wb = WorkbookFactory.create(fis); Sheet sheet = wb.getSheet("Compte 2016"); for (int i = 0; i < sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); save(extractEntity(row)); } } catch (IOException | InvalidFormatException | EncryptedDocumentException e) { LOGGER.error(e, "Pb lors de l'import", path); } }
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 }// ww w. j ava 2 s . c o 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 {/* w w w . ja v a 2 s.c o m*/ 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(); } }
From source file:simbad.Excel.java
public double mayorLatitud(Workbook workbook) throws FileNotFoundException, IOException { Sheet sheet = workbook.getSheet("Sheet1"); double mayorLat = 0; Row row = sheet.getRow(0);// w w w. j ava 2 s .c o m for (int j = 0; j < row.getLastCellNum(); j++) { if (row.getCell(j).getRichStringCellValue().toString().equals("lat_ciu")) for (int i = 1; i < sheet.getLastRowNum(); i++) { Row row2 = sheet.getRow(i); if (i == 1 || Double.parseDouble((row2.getCell(j).getRichStringCellValue().toString())) > mayorLat) mayorLat = Double.parseDouble(row2.getCell(j).getRichStringCellValue().toString()); } } return mayorLat; }
From source file:simbad.Excel.java
public double menorLatitud(Workbook workbook) throws FileNotFoundException, IOException { Sheet sheet = workbook.getSheet("Sheet1"); double menorLat = 0; Row row = sheet.getRow(0);// w ww.j ava 2 s.c o m for (int j = 0; j < row.getLastCellNum(); j++) { if (row.getCell(j).getRichStringCellValue().toString().equals("lat_ciu")) for (int i = 1; i < sheet.getLastRowNum(); i++) { Row row2 = sheet.getRow(i); if (i == 1 || Double.parseDouble((row2.getCell(j).getRichStringCellValue().toString())) < menorLat) menorLat = Double.parseDouble(row2.getCell(j).getRichStringCellValue().toString()); } } return menorLat; }