List of usage examples for org.apache.poi.ss.usermodel Row cellIterator
Iterator<Cell> cellIterator();
From source file:javaapp.ParseCreditorTransactionsData.java
public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "S9/GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheet(name); //String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; String filter = ""; while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0;//ww w.j av a 2 s . c o m cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 32 || cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } if (cell.getColumnIndex() == 32) { filter = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) { rpps = rec + "-" + pay + "-" + per + "-" + svc; if (tbl.equalsIgnoreCase("open") || tbl.equalsIgnoreCase("close")) { if (filter.equalsIgnoreCase("Missing Invoice") || filter.equalsIgnoreCase("Unreconciled") || filter.equalsIgnoreCase("") || filter.equalsIgnoreCase("-")) { System.out.print(rpps); System.out.println(filter); continue; } System.out.print(filter); System.out.println(rpps); } // if(name.equalsIgnoreCase("Settled Transactions-Funds-Paid")){ //System.out.println(rpps+"|"+dval+"|"+cval); //} //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }
From source file:javaapp.ParseCreditorTransactionsDataEE.java
public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "EE/GBRMECOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheet(name); //String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; String filter = ""; while (iterator.hasNext()) { rec = ""; pay = ""; Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0;//from w w w . j av a 2 s .co m cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 23 || cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } if (cell.getColumnIndex() == 23) { filter = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) { rpps = rec + "-" + pay + "-" + per + "-" + svc; //System.out.print(rpps); //System.out.println("--->"+tbl); if (tbl.equalsIgnoreCase("open") || tbl.equalsIgnoreCase("close")) { if (filter.equalsIgnoreCase("Missing Invoice") || filter.equalsIgnoreCase("Unreconciled") || filter.equalsIgnoreCase("")) { continue; } } // if(name.equalsIgnoreCase("Settled Transactions-Funds-Paid")){ //System.out.println(rpps+"|"+dval+"|"+cval); //} //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }
From source file:javaapp.ParseDebtorData.java
public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "S9/GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheet(name); //String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0;/*ww w . ja v a2 s. c o m*/ cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if (rec.length() == 5 || rec.length() == 8) { rpps = rec + "-" + pay + "-" + per + "-" + svc; //System.out.print(rpps+"|"+dval+"|"+cval); //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }
From source file:javaapp.ParseDebtorTransactionsData.java
public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "S9/GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheet(name); //String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; String filter = ""; while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0;// w w w . j av a 2s. com cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 23 || cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } if (cell.getColumnIndex() == 23) { filter = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if (rec.length() == 5 || rec.length() == 8) { rpps = rec + "-" + pay + "-" + per + "-" + svc; if (tbl.equalsIgnoreCase("open") || tbl.equalsIgnoreCase("close")) { if (filter.equalsIgnoreCase("Missing Invoice") || filter.equalsIgnoreCase("Unreconciled") || filter.equalsIgnoreCase("")) { continue; } } // if(name.equalsIgnoreCase("Settled Transactions-Funds-Paid")){ //System.out.println(rpps+"|"+dval+"|"+cval); //} //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }
From source file:javaapp.SimpleExcelReaderExample.java
public static ArrayList<String> parseReport(int sno, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheetAt(sno); String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Number ---> " + sno + "---> Name : " + sname + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0;/*from ww w. ja va2 s . c o m*/ cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if (rec.length() == 5 || rec.length() == 8) { rpps = rec + "-" + pay + "-" + per + "-" + svc; //System.out.print(rpps+"|"+dval+"|"+cval); //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }
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(); // w ww .jav a 2s . co 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 w w .j av a 2 s . c o 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:jdbc.BS.java
String read_BS(String company, Integer rownumber, Integer data_year) { // linenumber > 5
Integer rowindex = 2;/*from w w w.j av a2 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 + "_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 ww . j a v 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\\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;// ww w . j a v a 2 s .c om
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;
}
}