List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:Interface.StateBodyEmployee.StateEmployeeWorkAreaJPanel.java
public void readFromExcel() { try {/* w w w . j ava2 s .c o m*/ FileInputStream file = new FileInputStream(new File("sensorData.xlsx")); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<org.apache.poi.ss.usermodel.Row> rowIterator = sheet.iterator(); DataFormatter df = new DataFormatter(); while (rowIterator.hasNext()) { sensorCounter++; org.apache.poi.ss.usermodel.Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); for (a = 0; a < 6; a++) { i = (a % 6); //System.out.print(row.getCell(i)+"\t"); switch (i) { case 0: location = df.formatCellValue(row.getCell(i)); // System.out.println("location= "+location); break; case 1: waterUsage = Double.parseDouble(df.formatCellValue(row.getCell(i))); // System.out.println("waterUsage= "+waterUsage); break; case 2: waterStorageCapacity = Double.parseDouble(df.formatCellValue(row.getCell(i))); // System.out.println("waterStorageCapacity= "+waterStorageCapacity); break; case 3: triggerPercentage = Double.parseDouble(df.formatCellValue(row.getCell(i))); // System.out.println("triggerPercentage= "+triggerPercentage); break; case 4: criticalPercentage = Double.parseDouble(df.formatCellValue(row.getCell(i))); // System.out.println("criticalPercentage= "+criticalPercentage); break; case 5: soilType = df.formatCellValue(row.getCell(i)); // System.out.println("location= "+soilType); break; } } if (sensorCounter == 1) { s1 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage, criticalPercentage, soilType, enterprise); } if (sensorCounter == 2) { s2 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage, criticalPercentage, soilType, enterprise); } if (sensorCounter == 3) { s3 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage, criticalPercentage, soilType, enterprise); } if (sensorCounter == 4) { s4 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage, criticalPercentage, soilType, enterprise); } if (sensorCounter == 5) { s5 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage, criticalPercentage, soilType, enterprise); } } file.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:javaapplication1.BackTrack.java
private void btnBackTrackActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnBackTrackActionPerformed // TODO add your handling code here: // Excel ob = null; /* int len[][]=(int[][]) Excel.data.toArray(); //from w w w . ja va 2 s .c o m for(int i=0 ;i<len.length ;i++)*/ int i = 1, j = 1; float samplesec = Float.parseFloat(txtSamples.getText()); if (samplesec == 0) { samplesec = 1; } float th = Float.parseFloat(txtThreshold.getText()); try { DefaultTableModel defmodel = new DefaultTableModel(); tblTH.setModel(defmodel); defmodel.setColumnIdentifiers(new Object[] { "Seconds", "Value" }); String temp; temp = txtFilename.getText() + ".xlsx"; FileInputStream file = new FileInputStream(new File(temp)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); // int i=1,j=1; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //Check the cell type and format accordingly dataTemp.add(cell.getNumericCellValue()); // System.out.print(dataTemp.get(0).toString()); if (cell.getNumericCellValue() > th) { defmodel.addRow(new Object[] { (i / samplesec), cell.getNumericCellValue() }); } i++; } j++; data.add(dataTemp); } file.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:javaapplication1.Excel.java
private void btnImportActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnImportActionPerformed // TODO add your handling code here: try {/*from w ww. j a v a 2 s .co m*/ String temp; temp = txtfileName.getText() + ".xlsx"; FileInputStream file = new FileInputStream(new File(temp)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); int i = 1, j = 1; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //Check the cell type and format accordingly dataTemp.add(cell.getNumericCellValue()); // System.out.print(dataTemp.get(0).toString()); if (j == 1) { col.add("S" + i); i++; } } j++; data.add(dataTemp); } file.close(); } catch (Exception e) { e.printStackTrace(); } printData(); }
From source file:javafx.JavaFX.java
public void initialize() throws Exception { tree.setEditable(true);//w w w . j ava2 s . com tree.setShowRoot(false); TreeItem<TRow> root = new TreeItem<>(new TRow("", "", "", "", "")); root.setExpanded(true); File s = new File("C:\\Users\\Markus\\Desktop\\Prism.xlsx"); FileInputStream file = new FileInputStream(s); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIter = sheet.iterator(); TreeItem<TRow> level1 = new TreeItem<>(); TreeItem<TRow> level2 = new TreeItem<>(); while (rowIter.hasNext()) { Row r = rowIter.next(); String[] values = getValues(r); // 0=Hierarchie, 1=Pruefpunkt, 2=Typ, 3=Einheit, 4=Min, 5=Max if (values[0].matches("\\w")) { level1 = new TreeItem<>(new TRow(values[1], "", "", "", "")); root.getChildren().add(level1); } if (values[0].matches("\\w[0-9]")) { level2 = new TreeItem<>(new TRow(values[1], "", "", "", "")); level1.getChildren().add(level2); } if (values[0].matches("\\w[0-9]\\|[0-9]+")) { level2.getChildren().add(new TreeItem<>( new TRow(values[1], "", values[3], formatMinMax(values[4], values[5]), "asd"))); } } C1.setCellValueFactory((CellDataFeatures<TRow, String> param) -> new SimpleStringProperty( param.getValue().getValue().getPruefpunkt())); C2.setCellFactory(TextFieldTreeTableCell.forTreeTableColumn()); C2.setCellValueFactory((CellDataFeatures<TRow, String> param) -> new SimpleStringProperty( param.getValue().getValue().getMesswert())); C2.setOnEditCommit((TreeTableColumn.CellEditEvent<TRow, String> evt) -> { String nv = evt.getNewValue(); evt.getRowValue().getValue().setMesswert(nv); }); C3.setCellValueFactory((CellDataFeatures<TRow, String> param) -> new SimpleStringProperty( param.getValue().getValue().getEinheit())); C4.setCellValueFactory((CellDataFeatures<TRow, String> param) -> new SimpleStringProperty( param.getValue().getValue().getMinMax())); C4.setCellValueFactory((CellDataFeatures<TRow, String> param) -> new SimpleStringProperty( param.getValue().getValue().getMinMax())); C5.setCellValueFactory((CellDataFeatures<TRow, String> param) -> new SimpleStringProperty( param.getValue().getValue().getStatus())); C5.setCellFactory(ComboBoxTreeTableCell.forTreeTableColumn("Pass", "Fail")); C5.setOnEditCommit((TreeTableColumn.CellEditEvent<TRow, String> evt) -> { String nv = evt.getNewValue(); evt.getRowValue().getValue().setStatus(nv); }); but.addEventHandler(MouseEvent.MOUSE_CLICKED, (event) -> { System.out.println("asdasd231231"); }); C6.setCellValueFactory((CellDataFeatures<TRow, String> param) -> new SimpleStringProperty( param.getValue().getValue().getBemerkung())); Callback<TreeTableColumn<TRow, String>, TreeTableCell<TRow, String>> cellFactory = new Callback<TreeTableColumn<TRow, String>, TreeTableCell<TRow, String>>() { public TreeTableCell call(TreeTableColumn p) { return new EditTreeTableCell(); } }; C6.setCellFactory(cellFactory); tree.setRoot(root); }
From source file:jdbc.BS.java
String read_BS(String company, Integer rownumber, Integer data_year) { // linenumber > 5
Integer rowindex = 2;//from w w w .ja v a 2s . co m
Integer n = 0;
Integer y = 0;
Integer colindex = -1;//number of years in the file
Integer max_year = 2014;
String result = "N/A";
DataFormatter fmt = new DataFormatter();
try {
FileInputStream file = new FileInputStream(
new File("C:\\Users\\tri\\Desktop\\busi\\data\\All\\" + company + "_BS.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if (rowindex > 3 && rowindex < 30) {
if (rowindex == 4) {
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
colindex++;
}
}
if (rowindex == rownumber) {
Iterator<Cell> cellIterator = row.cellIterator();
if (data_year == 999) {
Cell cell = cellIterator.next();
result = fmt.formatCellValue(cell);
return result;
}
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
y = colindex + data_year - max_year; //get the number of cells we need to jump
if (y > 0) {
if (y == n) {//go through the cells till reach right one
return result = fmt.formatCellValue(cell);
}
n++;
}
}
}
}
rowindex++;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if ("".equals(result) || "-".equals(result) || "N/A".equals(result)) {
return "0.0";
} else {
return result;
}
}
}
From source file:jdbc.BS.java
String read_BS_Current(String company, Integer linenumber, Integer data_year) { // linenumber > 5
Integer b = 3;/*from w w w. ja va2 s . c o m*/
Integer c = 0;
Integer y = 0;
Integer n = 0;
String result = null;
DataFormatter fmt = new DataFormatter();
try {
FileInputStream file_BS = new FileInputStream(
new File("C:\\Users\\tri\\Desktop\\busi\\data\\Current\\" + company + "_BS.xlsx"));
XSSFWorkbook workbook_BS = new XSSFWorkbook(file_BS);
XSSFSheet sheet_BS = workbook_BS.getSheetAt(0);
Iterator<Row> rowIterator_BS = sheet_BS.iterator();
while (rowIterator_BS.hasNext()) {
Row row = rowIterator_BS.next();
if (b > 4 && b < 30) {
if (b == 5) {
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
c = c + 1;
}
}
if (b == linenumber) {
Iterator<Cell> cellIterator = row.cellIterator();
if (data_year == 999) {
Cell cell = cellIterator.next();
result = fmt.formatCellValue(cell);
} else {
y = c + data_year - 2014;
if (y > 0) {
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (n == y) {
result = fmt.formatCellValue(cell);
}
n = n + 1;
}
} else {
result = "N/A";
}
}
}
}
b = b + 1;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if ("".equals(result) || "-".equals(result) || "N/A".equals(result)) {
return "0.0";
} else {
return result;
}
}
}
From source file:jdbc.BS.java
String imort_BS(String company, Integer linenumber, Integer data_year) { // linenumber > 5
Integer b = 2;//from w w w . jav a 2 s. co m
Integer c = 0;
Integer y = 0;
Integer n = 0;
String result = null;
DataFormatter fmt = new DataFormatter();
try {
FileInputStream file_BS = new FileInputStream(
new File("C:\\Users\\tri\\Desktop\\busi\\data\\All\\" + company + "_BS.xlsx"));
XSSFWorkbook workbook_BS = new XSSFWorkbook(file_BS);
XSSFSheet sheet_BS = workbook_BS.getSheetAt(0);
Iterator<Row> rowIterator_BS = sheet_BS.iterator();
while (rowIterator_BS.hasNext()) {
Row row = rowIterator_BS.next();
if (b > 4 && b < 31) {
if (b == 5) {
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
c = c + 1;
}
}
if (b == linenumber) {
Iterator<Cell> cellIterator = row.cellIterator();
if (data_year == 999) {
Cell cell = cellIterator.next();
result = fmt.formatCellValue(cell);
} else {
y = c + data_year - 2014;
if (y > 0) {
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (n == y) {
result = fmt.formatCellValue(cell);
}
n = n + 1;
}
} else {
result = "N/A";
}
}
}
}
b = b + 1;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
return result;
}
}
From source file:jdbc.CFS.java
String read_CFS(String company, Integer rownumber, Integer data_year) { // linenumber > 5
Integer rowindex = 2;//from ww w .j a v a 2 s. com
Integer n = 0;
Integer y = 0;
Integer colindex = -1;//number of years in the file
Integer max_year = 2014;
String result = "N/A";
DataFormatter fmt = new DataFormatter();
try {
FileInputStream file = new FileInputStream(
new File("C:\\Users\\tri\\Desktop\\busi\\data\\All\\" + company + "_CFS.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if (rowindex > 3 && rowindex < 44) {
if (rowindex == 4) {
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
colindex++;
}
}
if (rowindex == rownumber) {
Iterator<Cell> cellIterator = row.cellIterator();
if (data_year == 999) {
Cell cell = cellIterator.next();
result = fmt.formatCellValue(cell);
return result;
}
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
y = colindex + data_year - max_year; //get the number of cells we need to jump
if (y > 0) {
if (y == n) {//go through the cells till reach right one
return result = fmt.formatCellValue(cell);
}
n++;
}
}
}
}
rowindex++;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if ("".equals(result) || "-".equals(result) || "N/A".equals(result)) {
return "0.0";
} else {
return result;
}
}
}
From source file:jdbc.CFS.java
String iport_CFS(String company, Integer linenumber, Integer data_year) { // linenumber > 5
Integer b = 3;//from w w w. j a v a 2 s. c o m
Integer c = 0;
Integer y = 0;
Integer n = 0;
String result = null;
DataFormatter fmt = new DataFormatter();
try {
FileInputStream file_CFS = new FileInputStream(
new File("C:\\Users\\tri\\Desktop\\busi\\data\\All\\" + company + "_CFS.xlsx"));
XSSFWorkbook workbook_CFS = new XSSFWorkbook(file_CFS);
XSSFSheet sheet_CFS = workbook_CFS.getSheetAt(0);
Iterator<Row> rowIterator_CFS = sheet_CFS.iterator();
while (rowIterator_CFS.hasNext()) {
Row row = rowIterator_CFS.next();
if (b > 4 && b < 44) {
if (b == 5) {
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
c = c + 1;
}
}
if (b == linenumber) {
Iterator<Cell> cellIterator = row.cellIterator();
if (data_year == 999) {
Cell cell = cellIterator.next();
result = fmt.formatCellValue(cell);
} else {
y = c + data_year - 2014;
if (y > 0) {
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (n == y) {
result = fmt.formatCellValue(cell);
}
n = n + 1;
}
} else {
result = "N/A";
}
}
}
}
b = b + 1;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
return result;
}
}
From source file:jdbc.FI.java
String read_FI(String company, Integer rownumber, Integer data_year) { // colnumber > 1
Integer rowindex = 2;// w w w. j a v a 2 s. c o m
Integer n = 0;
Integer y = 0;
Integer colindex = -1;//number of years in the file
Integer max_year = 2014;
String result = "N/A";
DataFormatter fmt = new DataFormatter();
try {
FileInputStream file = new FileInputStream(
new File("C:\\Users\\tri\\Desktop\\busi\\data\\All\\" + company + "_FI.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if (rowindex > 3 && rowindex < 25) {
if (rowindex == 4) {
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
colindex++;
}
}
if (rowindex == rownumber) {
Iterator<Cell> cellIterator = row.cellIterator();
if (data_year == 999) {
Cell cell = cellIterator.next();
result = fmt.formatCellValue(cell);
return result;
}
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
y = colindex + data_year - max_year; //get the number of cells we need to jump
if (y > 0) {
if (y == n) {//go through the cells till reach right one
return result = fmt.formatCellValue(cell);
}
n++;
}
}
}
}
rowindex++;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if ("".equals(result) || "-".equals(result) || "N/A".equals(result)) {
return "0.0";
} else {
return result;
}
}
}