List of usage examples for org.apache.poi.ss.usermodel Row cellIterator
Iterator<Cell> cellIterator();
From source file:readers.discount.XmlDiscountReader.java
public Discounts getNext() { if (!hasNext()) { return null; }// w w w .j a va 2s . c o m Row row = rowIterator.next(); currentRow++; String name = null; try { Iterator<Cell> cellIterator = row.cellIterator(); Cell cell = cellIterator.next(); if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { return getNext(); } name = cell.getStringCellValue(); double percentage = getPercentage(cellIterator.next()); return new Discounts(name, percentage); } catch (Exception ex) { String message = "SKOROSZYT: " + (currentSheet + 1); if (name != null) { message += " NAZWA PRODUKTU: " + name; } else { message += " LINIA: " + currentRow; } throw new RuntimeException(message); } }
From source file:regression.home.java
public void openData() { JFileChooser fileChooser = new JFileChooser(); int returnValue = fileChooser.showOpenDialog(null); if (returnValue == JFileChooser.APPROVE_OPTION) { File selectedFile = fileChooser.getSelectedFile(); try {/*www .j av a 2s . c o m*/ FileInputStream file = new FileInputStream(selectedFile.getPath()); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIteratorCount = sheet.iterator(); Iterator<Row> rowIteratorValue = sheet.iterator(); int rowCount = 0, colCount = 0, rowValue = 0, colValue = 0; while (rowIteratorCount.hasNext()) { Row row = rowIteratorCount.next(); Iterator<Cell> cellIteratorCount = row.cellIterator(); colCount = 0; while (cellIteratorCount.hasNext()) { Cell cell = cellIteratorCount.next(); colCount++; } rowCount++; } di.header = new String[colCount]; di.dataValue = new String[rowCount - 1][colCount]; while (rowIteratorValue.hasNext()) { Row row = rowIteratorValue.next(); Iterator<Cell> cellIteratorValue = row.cellIterator(); colValue = 0; while (cellIteratorValue.hasNext()) { Cell cell = cellIteratorValue.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: if (rowValue == 0) { di.header[colValue] = String.valueOf(cell.getBooleanCellValue()); } else { di.dataValue[rowValue - 1][colValue] = String.valueOf(cell.getBooleanCellValue()); } break; case Cell.CELL_TYPE_NUMERIC: if (rowValue == 0) { di.header[colValue] = String.valueOf(cell.getNumericCellValue()); } else { double cellVal = cell.getNumericCellValue(); String dt = null; if (cellVal % 1 != 0) { dt = new DecimalFormat("#0.00##").format(cell.getNumericCellValue()); di.dataValue[rowValue - 1][colValue] = dt; } else { dt = new DecimalFormat("#0").format(cell.getNumericCellValue()); di.dataValue[rowValue - 1][colValue] = dt; } } break; case Cell.CELL_TYPE_STRING: if (rowValue == 0) { di.header[colValue] = String.valueOf(cell.getStringCellValue()); } else { di.dataValue[rowValue - 1][colValue] = String.valueOf(cell.getStringCellValue()); } break; } colValue++; } rowValue++; } DefaultTableModel model = new DefaultTableModel(di.dataValue, di.header); di.jTable1.setModel(model); di.jTable1.getTableHeader().setFont(new Font("Tahoma", Font.PLAIN, 16)); for (int c = 0; c < di.header.length; c++) { di.jTable1.getColumnModel().getColumn(c).setCellRenderer(new FTable()); } file.close(); di.setVisible(true); dispose(); } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(fileChooser, "File Not Found"); } catch (IOException ex) { JOptionPane.showMessageDialog(fileChooser, "Ekstensi File yang Anda masukkan salah"); } } }
From source file:regression._dataImport.java
public void openData() { JFileChooser fileChooser = new JFileChooser(); int returnValue = fileChooser.showOpenDialog(null); if (returnValue == JFileChooser.APPROVE_OPTION) { File selectedFile = fileChooser.getSelectedFile(); try {/* www .j av a2 s.c o m*/ FileInputStream file = new FileInputStream(selectedFile.getPath()); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIteratorCount = sheet.iterator(); Iterator<Row> rowIteratorValue = sheet.iterator(); int rowCount = 0, colCount = 0, rowValue = 0, colValue = 0; while (rowIteratorCount.hasNext()) { Row row = rowIteratorCount.next(); Iterator<Cell> cellIteratorCount = row.cellIterator(); colCount = 0; while (cellIteratorCount.hasNext()) { Cell cell = cellIteratorCount.next(); colCount++; } rowCount++; } header = new String[colCount]; dataValue = new String[rowCount - 1][colCount]; while (rowIteratorValue.hasNext()) { Row row = rowIteratorValue.next(); Iterator<Cell> cellIteratorValue = row.cellIterator(); colValue = 0; while (cellIteratorValue.hasNext()) { Cell cell = cellIteratorValue.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: if (rowValue == 0) { header[colValue] = String.valueOf(cell.getBooleanCellValue()); } else { dataValue[rowValue - 1][colValue] = String.valueOf(cell.getBooleanCellValue()); } break; case Cell.CELL_TYPE_NUMERIC: if (rowValue == 0) { header[colValue] = String.valueOf(cell.getNumericCellValue()); } else { double cellVal = cell.getNumericCellValue(); String dt = null; if (cellVal % 1 != 0) { dt = new DecimalFormat("#0.00##").format(cell.getNumericCellValue()); dataValue[rowValue - 1][colValue] = dt; } else { dt = new DecimalFormat("#0").format(cell.getNumericCellValue()); dataValue[rowValue - 1][colValue] = dt; } } break; case Cell.CELL_TYPE_STRING: if (rowValue == 0) { header[colValue] = String.valueOf(cell.getStringCellValue()); } else { dataValue[rowValue - 1][colValue] = String.valueOf(cell.getStringCellValue()); } break; } colValue++; } rowValue++; } DefaultTableModel model = new DefaultTableModel(dataValue, header); jTable1.setModel(model); for (int c = 0; c < header.length; c++) { jTable1.getColumnModel().getColumn(c).setCellRenderer(new FTable()); } file.close(); } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(fileChooser, "File Not Found"); } catch (IOException ex) { JOptionPane.showMessageDialog(fileChooser, "Wrong File Type Selected"); } } }
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 }//from w ww . j av a 2s . com //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;/*from www . java 2 s.c om*/ if (selectedFile.getPath().endsWith("xlsx")) { workbook = new XSSFWorkbook(inputStream); } else { 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:send.sms.az.util.ReadExcelFileToList.java
public static List<Map<String, Entry>> readExcelData(UploadedFile fileName) { List<Map<String, Entry>> mapList = new ArrayList<>(); // logger.info("--------------- starting workbook"); try {//from ww w . ja v a2 s. c o m //Create the input stream from the xlsx/xls file InputStream fis = fileName.getInputstream(); // InputStream fis = new FileInputStream("D:/test.xls"); //Create Workbook instance for xlsx/xls file input stream Workbook workbook = null; // logger.info("--------------- getting workbook"); if (fileName.getFileName().toLowerCase().endsWith("xlsx")) { // logger.info("--------------- " + fileName.getFileName().toLowerCase()); workbook = new XSSFWorkbook(fis); } else if (fileName.getFileName().toLowerCase().endsWith("xls")) { workbook = new HSSFWorkbook(fis); } //Get the number of sheets in the xlsx file int numberOfSheets = workbook.getNumberOfSheets(); //loop through each of the sheets for (int i = 0; i < numberOfSheets; i++) { //Get the nth sheet from the workbook Sheet sheet = workbook.getSheetAt(i); //every sheet has rows, iterate over them Iterator<Row> rowIterator = sheet.iterator(); int rowPos = 0; while (rowIterator.hasNext()) { ++rowPos; Map<String, Entry> map = new HashMap<>();//map for full row //Get the row object Row row = rowIterator.next(); //Every row has columns, get the column iterator and iterate over them Iterator<Cell> cellIterator = row.cellIterator(); int cellPos = 0;//setirsayi while (cellIterator.hasNext()) { //Get the Cell object ++cellPos; Cell cell = cellIterator.next(); //check the cell type and process accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: String value = cell.getStringCellValue().trim(); // System.out.println("string " + value + " " + cellPos + " " + rowPos); map.put("" + (cellPos - 1), new Entry("" + rowPos, value)); break; case Cell.CELL_TYPE_NUMERIC: double numValue = cell.getNumericCellValue(); // System.out.println("numb " + numValue + " " + cellPos + " " + rowPos); map.put("" + (cellPos - 1), new Entry("" + rowPos, "" + numValue)); break; } } //end of cell iterator // System.out.println("added to list"); mapList.add(map); } //end of rows iterator } //end of sheets for loop //close file input stream fis.close(); } catch (Exception e) { e.printStackTrace(); } return mapList; }
From source file:send.sms.az.util.UploadBlackList.java
public static List<String> readExcelData(UploadedFile fileName) { List<Map<String, Entry>> mapList = new ArrayList<>(); List<String> blList = new ArrayList(); try {// w w w. ja v a 2s .com InputStream fis = fileName.getInputstream(); Workbook workbook = null; if (fileName.getFileName().toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(fis); } else if (fileName.getFileName().toLowerCase().endsWith("xls")) { workbook = new HSSFWorkbook(fis); } //Get the number of sheets in the xlsx file int numberOfSheets = workbook.getNumberOfSheets(); //loop through each of the sheets for (int i = 0; i < numberOfSheets; i++) { //Get the nth sheet from the workbook Sheet sheet = workbook.getSheetAt(i); //every sheet has rows, iterate over them Iterator<Row> rowIterator = sheet.iterator(); int rowPos = 0; while (rowIterator.hasNext()) { ++rowPos; Map<String, Entry> map = new HashMap<>();//map for full row //Get the row object Row row = rowIterator.next(); //Every row has columns, get the column iterator and iterate over them Iterator<Cell> cellIterator = row.cellIterator(); int cellPos = 0;//setirsayi while (cellIterator.hasNext()) { //Get the Cell object ++cellPos; Cell cell = cellIterator.next(); //check the cell type and process accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: String value = cell.getStringCellValue().trim(); // System.out.println("string " + value + " " + cellPos + " " + rowPos); // map.put("" + (cellPos - 1), new Entry("" + rowPos, value)); blList.add(value); break; case Cell.CELL_TYPE_NUMERIC: double numValue = cell.getNumericCellValue(); // System.out.println("numb " + numValue + " " + cellPos + " " + rowPos); // map.put("" + (cellPos - 1), new Entry("" + rowPos, "" + numValue)); blList.add("" + numValue); break; } break; } //end of cell iterator mapList.add(map); } //end of rows iterator break; } //end of sheets for loop //close file input stream fis.close(); } catch (Exception e) { blList = new ArrayList<>(); e.printStackTrace(); } return blList; }
From source file:simbad.reporteUnificado.java
public void MostrarReporte() throws IOException { Workbook workbook = new XSSFWorkbook(new FileInputStream( "C:\\Users\\Miguel\\My Documents\\NetBeansprojects\\SIMBAD\\reporteUnificado.xlsx")); Sheet sheet = workbook.getSheet("Sheet1"); for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) { Row row = rit.next(); for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) { Cell cell = cit.next();/* w w w . j a va 2 s . c o m*/ cell.setCellType(Cell.CELL_TYPE_STRING); System.out.print(cell.getStringCellValue() + "\t"); } System.out.println(); } }
From source file:soc.scar.service.excel.ProjectExcelService.java
public List<Module> getModule(Row rowRef) { List<Module> modulesList = new ArrayList<>(); Iterator<Cell> cellIteratorRef = rowRef.cellIterator(); Cell cell = cellIteratorRef.next();/*from w w w . j a v a 2s. co m*/ cell = cellIteratorRef.next(); while (cellIteratorRef.hasNext()) { Module module = new Module(); cell = cellIteratorRef.next(); module.setReference(cell.getStringCellValue()); if (!checkModuleExist(module) && !Strings.isNullOrEmpty(module.getReference())) { insertModule(module); } if (checkModuleExist(module)) modulesList.add(module); } return modulesList; }
From source file:species.formatReader.SpreadsheetWriter.java
public static void writeDataInSheet(Workbook wb, JSONArray gridData, int sheetNo, String writeContributor, String contEmail, JSONArray orderedArray) { //System.out.println("================================" + writeContributor +"===" + contEmail ); /*if(writeContributor.equals("true")){ JSONObject r = gridData.getJSONObject(0); if(!r.has("contributor")){//from w w w . j a v a 2s . c o m for(int k = 0; k < gridData.length();k++){ JSONObject r1 = gridData.getJSONObject(k); r1.put("contributor", contEmail); } } }*/ Sheet sheet = wb.getSheetAt(sheetNo); Iterator<Row> rowIterator = sheet.iterator(); int index = 0; int i = 0; boolean headerRow = true; //System.out.println("===JSON ARRAY LENGTH=============="); //System.out.println(gridData.length()); int gDataSize = gridData.length(); JSONObject rowData = gridData.getJSONObject(index); Iterator<String> keys = rowData.keys(); int numKeys = 0; while (keys.hasNext()) { String kk = keys.next(); numKeys++; } String[] keysArray = new String[numKeys]; //String[] keysArray = orderedArray; for (int k = 0; k < numKeys; k++) { keysArray[k] = orderedArray.getString(k); } Row row = rowIterator.next(); for (int a = 0; a < numKeys; a++) { Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK); //System.out.println("======PRINTING THIS TO HEADER CELL===== " + keysArray[a]); cell.setCellValue(keysArray[a]); i++; } int lastHeaderCellNum = row.getLastCellNum(); for (int j = i; j <= lastHeaderCellNum; j++) { Cell cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK); cell.setCellValue(""); } for (int k = 0; k < gDataSize; k++) { //System.out.println("REACHED FOR LOOP"); rowData = gridData.getJSONObject(index); //mapRow.put(gridData.get(count)); //rowIterator.hasNext(); // if (rowIterator.hasNext()) { row = rowIterator.next(); } else { row = sheet.createRow(k + 1); for (int a = 0; a < numKeys; a++) { Cell cell = row.createCell(a); } } i = 0; //System.out.println("============ "); //System.out.println(gridData); //for ( Map.Entry<String, String> entry : mapRow.entrySet()) { //while( keys.hasNext() ){ for (int a = 0; a < numKeys; a++) { //String key = (String)keys.next(); Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK); //System.out.println ("=====PRINTING THIS TO NORMAL CELL====== " + rowData.getString(keysArray[a])); cell.setCellValue(rowData.getString(keysArray[a])); i++; } index++; headerRow = false; // rest cells in that row overwritten with empty string int lastCellNum = row.getLastCellNum(); for (int j = i; j <= lastCellNum; j++) { Cell cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK); cell.setCellValue(""); } } //overwrite rest row data in sheet while (rowIterator.hasNext()) { row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); cell.setCellValue(""); } } return; }