List of usage examples for org.apache.poi.ss.usermodel Row cellIterator
Iterator<Cell> cellIterator();
From source file:StockForecast.Stock.java
private void converttoraw() throws Exception { File fileTxt = new File("RawData.txt"); FileInputStream file = new FileInputStream(new File("RawData.xls")); PrintWriter writer = new PrintWriter(fileTxt); writer.print(""); writer.close();/*from w ww. ja va 2s . c om*/ FileWriter fw = new FileWriter(fileTxt.getAbsoluteFile()); BufferedWriter bw = new BufferedWriter(fw); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: // System.out.print(cell.getNumericCellValue() + ","); bw.write(cell.getNumericCellValue() + ","); break; case Cell.CELL_TYPE_STRING: // System.out.print(cell.getStringCellValue() + ","); bw.write(cell.getStringCellValue() + ","); break; } } bw.write("\n"); } bw.close(); file.close(); fw.close(); }
From source file:Test.LeerExcel.java
public static void main(String args[]) throws IOException { FileInputStream file = new FileInputStream(new File("C:\\Users\\f2 a55m-s1\\Documents\\baseSqlLite\\")); // Crear el objeto que tendra el libro de Excel HSSFWorkbook workbook = new HSSFWorkbook(file); /*//from w w w. ja v a 2s. c o m * Obtenemos la primera pestaa a la que se quiera procesar indicando el indice. * Una vez obtenida la hoja excel con las filas que se quieren leer obtenemos el iterator * que nos permite recorrer cada una de las filas que contiene. */ HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Row row; // Recorremos todas las filas para mostrar el contenido de cada celda while (rowIterator.hasNext()) { row = rowIterator.next(); // Obtenemos el iterator que permite recorres todas las celdas de una fila Iterator<Cell> cellIterator = row.cellIterator(); Cell celda; while (cellIterator.hasNext()) { celda = cellIterator.next(); // Dependiendo del formato de la celda el valor se debe mostrar como String, Fecha, boolean, entero... switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(celda)) { System.out.println(celda.getDateCellValue()); } else { System.out.println(celda.getNumericCellValue()); } System.out.println(celda.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.println(celda.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: System.out.println(celda.getBooleanCellValue()); break; } } } // cerramos el libro excel workbook.close(); }
From source file:Test.LeerExcelXlsx.java
public static void main(String args[]) throws IOException { FileInputStream file = new FileInputStream( new File("C:\\Users\\f2 a55m-s1\\Documents\\baseSqlLite\\Libro1.xlsx")); // Crear el objeto que tendra el libro de Excel XSSFWorkbook workbook = new XSSFWorkbook(file); /*/*from ww w . j av a2 s. c om*/ * Obtenemos la primera pestaa a la que se quiera procesar indicando el indice. * Una vez obtenida la hoja excel con las filas que se quieren leer obtenemos el iterator * que nos permite recorrer cada una de las filas que contiene. */ XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Row row; // Recorremos todas las filas para mostrar el contenido de cada celda while (rowIterator.hasNext()) { row = rowIterator.next(); // Obtenemos el iterator que permite recorres todas las celdas de una fila Iterator<Cell> cellIterator = row.cellIterator(); Cell celda; while (cellIterator.hasNext()) { celda = cellIterator.next(); // Dependiendo del formato de la celda el valor se debe mostrar como String, Fecha, boolean, entero... switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(celda)) { System.out.print(celda.getDateCellValue()); } else { System.out.print(celda.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: System.out.print(celda.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(celda.getBooleanCellValue()); break; } } System.out.println(""); } // cerramos el libro excel workbook.close(); }
From source file:testpoi.GenerateDailyExcel.java
License:Open Source License
private static void makeEntry(Department deptt) { //create new row in xlsx to be generated Row newRow = sheetNew.createRow(rowCnt++); //Create a new cell in current row Cell newCell = newRow.createCell(0); //Set value to the department's name newCell.setCellValue(deptt.name);//w w w . j a v a 2 s . c o m double random = Math.random(); Row row = null; if (deptt.name.equals("Gynaecology")) { //Pick a row from female sheet randomly (Female sheet should have all reproducible ages) int rowNum = (int) (random * sheetFemale.getPhysicalNumberOfRows()); row = sheetFemale.getRow(rowNum); } else if (deptt.name.equals("Paediatrics")) { //Pick a row from children sheet randomly (Children sheet should have all ages under 13) int rowNum = (int) (random * sheetChildren.getPhysicalNumberOfRows()); row = sheetChildren.getRow(rowNum); } else { //Pick a row from all sheet randomly int rowNum = (int) (random * sheetAll.getPhysicalNumberOfRows()); row = sheetAll.getRow(rowNum); } assert (row != null); //read and write fetched row Iterator<Cell> cellIterator = row.cellIterator(); int newCellCnt = 1; while (cellIterator.hasNext()) { //May we write all cells as strings? Cell cell = cellIterator.next(); String cellValue = null; try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = cell.getNumericCellValue() + ""; else cellValue = cell.getStringCellValue(); newCell = newRow.createCell(newCellCnt++); newCell.setCellValue(cellValue); // System.out.print (cellValue+"("+cell.getColumnIndex()+")\t"); } catch (Exception e) { System.out.println("Could not write from cell (value:" + cellValue + // ", column:"+cell.getSheet().getWorkbook().+ ", sheet:" + cell.getSheet().getSheetName() + ", row:" + cell.getRowIndex() + ", column:" + cell.getColumnIndex() + ")"); e.printStackTrace(); } } System.out.println(); //delete row read if (row.getSheet() == sheetFemale) sheetFemale.removeRow(row); else if (row.getSheet() == sheetChildren) sheetChildren.removeRow(row); else sheetAll.removeRow(row); }
From source file:testpoi.GenerateDailyExcelPickingRowsSequentially.java
License:Open Source License
private static void makeEntry(Department deptt) { //create new row in xlsx to be generated Row newRow = sheetNew.createRow(rowCnt++); //Create a new cell in current row Cell newCell = newRow.createCell(0); //Set value to the department's name newCell.setCellValue(deptt.name);//from w w w . ja va2 s . c om Row row = null; if (deptt.name.equals("Obs & Gynae") && femaleRowNum < sheetFemale.getPhysicalNumberOfRows()) { // //Pick a row from female sheet randomly (Female sheet should have all reproducible ages) // int rowNum = (int)(random*sheetFemale.getPhysicalNumberOfRows()); row = sheetFemale.getRow(femaleRowNum++); System.out.println("Sheet:Female, row: " + row.getRowNum()); } else if (deptt.name.equals("Paediatrics") && childRowNum < sheetChildren.getPhysicalNumberOfRows()) { row = sheetChildren.getRow(childRowNum++); System.out.println("Sheet:Children, row: " + row.getRowNum()); } else //if (allRowNum<sheetAll.getPhysicalNumberOfRows()) { row = sheetAll.getRow(allRowNum++); System.out.println("Sheet:All, row: " + row.getRowNum()); } assert row != null; //read and write fetched row Iterator<Cell> cellIterator = row.cellIterator(); int newCellCnt = 1; while (cellIterator.hasNext()) { //May we write all cells as strings? Cell cell = cellIterator.next(); String cellValue = null; try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = (int) (cell.getNumericCellValue()) + ""; else cellValue = cell.getStringCellValue(); newCell = newRow.createCell(newCellCnt++); newCell.setCellValue(cellValue); } catch (Exception e) { System.out.println("Could not write from cell (value:" + cellValue + // ", column:"+cell.getSheet().getWorkbook().+ ", sheet:" + cell.getSheet().getSheetName() + ", row:" + cell.getRowIndex() + ", column:" + cell.getColumnIndex() + ")"); e.printStackTrace(); } } System.out.println(); // //delete row read // if (row.getSheet()==sheetFemale) // sheetFemale.removeRow(row); // else if (row.getSheet()==sheetChildren) // sheetChildren.removeRow(row); // else // sheetAll.removeRow(row); }
From source file:testpoi.Department.java
License:Open Source License
private static void makeEntry(Department deptt) { //create new row in xlsx to be generated Row newRow = sheetNew.createRow(rowCnt++); //Create a new cell in current row Cell newCell = newRow.createCell(0); //Set value to the department's name newCell.setCellValue(deptt.name);/*from ww w.ja va 2s. com*/ newCell = newRow.createCell(1); newCell.setCellValue(deptt.isNew ? "New" : "Old"); newCell = newRow.createCell(2); newCell.setCellValue(crNo++); Row row = null; if (deptt.name.equals("Obs & Gynae") && femaleRowNum < sheetFemale.getPhysicalNumberOfRows()) { // //Pick a row from female sheet randomly (Female sheet should have all reproducible ages) // int rowNum = (int)(random*sheetFemale.getPhysicalNumberOfRows()); row = sheetFemale.getRow(femaleRowNum++); System.out.println("Sheet:Female, row: " + row.getRowNum()); } else if (deptt.name.equals("Paediatrics") && childRowNum < sheetChildren.getPhysicalNumberOfRows()) { row = sheetChildren.getRow(childRowNum++); System.out.println("Sheet:Children, row: " + row.getRowNum()); } else //if (allRowNum<sheetAll.getPhysicalNumberOfRows()) { row = sheetAll.getRow(allRowNum++); System.out.println("Sheet:All, row: " + row.getRowNum()); } assert row != null; //read and write fetched row Iterator<Cell> cellIterator = row.cellIterator(); int newCellCnt = 3; while (cellIterator.hasNext()) { //May we write all cells as strings? Cell cell = cellIterator.next(); String cellValue = null; try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = (int) (cell.getNumericCellValue()) + ""; else cellValue = cell.getStringCellValue(); newCell = newRow.createCell(newCellCnt++); newCell.setCellValue(cellValue); } catch (Exception e) { System.out.println("Could not write from cell (value:" + cellValue + // ", column:"+cell.getSheet().getWorkbook().+ ", sheet:" + cell.getSheet().getSheetName() + ", row:" + cell.getRowIndex() + ", column:" + cell.getColumnIndex() + ")"); e.printStackTrace(); } } System.out.println(); // //delete row read // if (row.getSheet()==sheetFemale) // sheetFemale.removeRow(row); // else if (row.getSheet()==sheetChildren) // sheetChildren.removeRow(row); // else // sheetAll.removeRow(row); }
From source file:testpoi.OldDepttSheet.java
License:Open Source License
private static void makeEntry(Department deptt) { //create new row in xlsx to be generated Row newRow = sheetNew.createRow(rowCnt++); //Create a new cell in current row Cell newCell = newRow.createCell(0); //Set value to the department's name newCell.setCellValue(deptt.name);/*from w w w .j a v a 2s . c om*/ newCell = newRow.createCell(1); newCell.setCellValue(deptt.isNew ? "New" : "Old"); if (deptt.isNew) { newCell = newRow.createCell(2); newCell.setCellValue(crNo++); Row row = null; if (deptt.name.equals("Obs & Gynae")) { // //Pick a row from female sheet randomly (Female sheet should have all reproducible ages) // int rowNum = (int)(random*sheetFemale.getPhysicalNumberOfRows()); if (femaleRowNum < sheetFemale.getPhysicalNumberOfRows()) { row = sheetFemale.getRow(femaleRowNum++); System.out.println("Sheet:Female, row: " + row.getRowNum()); } else { System.err.println("Female entries exhausted!"); } } else if (deptt.name.equals("Paediatrics")) { if (childRowNum < sheetChildren.getPhysicalNumberOfRows()) { row = sheetChildren.getRow(childRowNum++); System.out.println("Sheet:Children, row: " + row.getRowNum()); } else { System.err.println("Child entries exhausted!"); } } else { if (allRowNum < sheetAll.getPhysicalNumberOfRows()) { row = sheetAll.getRow(allRowNum++); System.out.println("Sheet:All, row: " + row.getRowNum()); } else { System.err.println("All(General New) entries exhausted!"); } } if (row == null) { throw new IllegalArgumentException("New input Rows Exhausted"); } assert row != null; //read and write fetched row Iterator<Cell> cellIterator = row.cellIterator(); int newCellCnt = 3; while (cellIterator.hasNext()) { //May we write all cells as strings? Cell cell = cellIterator.next(); String cellValue = null; try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = (int) (cell.getNumericCellValue()) + ""; else cellValue = cell.getStringCellValue(); newCell = newRow.createCell(newCellCnt++); newCell.setCellValue(cellValue); } catch (Exception e) { System.out.println("Could not write from cell (value:" + cellValue + // ", column:"+cell.getSheet().getWorkbook().+ ", sheet:" + cell.getSheet().getSheetName() + ", row:" + cell.getRowIndex() + ", column:" + cell.getColumnIndex() + ")"); e.printStackTrace(); } } } else //deptt is old { OldDepttSheet oldDepttSheetToUse = depttToOldSheetsMap.get(deptt.name); Row row = oldDepttSheetToUse.sheet.getRow(oldDepttSheetToUse.rowCnt++); if (row == null) { throw new IllegalArgumentException("Old Input Rows Exhausted in department " + deptt.name); } System.out.println("Sheet:" + deptt.name + ", row: " + row.getRowNum()); //Copy row from old sheet to newRow int newCellCnt = 2; Iterator<Cell> cellIterator = row.cellIterator(); //Skip columns Department and Patient Type cellIterator.next(); cellIterator.next(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellValue = null; try { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cellValue = (int) (cell.getNumericCellValue()) + ""; else cellValue = cell.getStringCellValue(); newCell = newRow.createCell(newCellCnt++); newCell.setCellValue(cellValue); } catch (Exception e) { System.out.println("Could not write from old sheet cell (value:" + cellValue + ", sheet:" + cell.getSheet().getSheetName() + ", row:" + cell.getRowIndex() + ", column:" + cell.getColumnIndex() + ")"); e.printStackTrace(); } } } }
From source file:testpoi.OPDDataTransfer.java
License:Open Source License
public static void main(String args[]) { try {//from www . ja v a 2 s . c om String dateFolder = "2.3.13"; FileInputStream file = new FileInputStream( new File("C:\\Documents and Settings\\Admin\\My Documents\\NetBeansProjects\\TestPOI\\Docs\\" + dateFolder + "\\" + dateFolder + ".xlsx")); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); Connection conn = connectToDatabaseHMS(); assert (conn != null); Connection conn2 = connectToDatabaseHMSSecurity(); assert (conn2 != null); try { conn.setAutoCommit(false); conn2.setAutoCommit(false); } catch (SQLException sqle) { System.err.println("Could not set autocommit to false"); sqle.printStackTrace(); } //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); //Skip the 1st row rowIterator.next(); //set time time = Time.valueOf("09:00:00"); //set entry number default to 1. entryNumber = 1; // //set entry crNo // entryCrNo = 1; Timestamp timestamp = new Timestamp(date.getTime() + time.getTime() + 19800000/*for IST*/); System.out.println(timestamp.toString()); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, get values of each column Iterator<Cell> cellIterator = row.cellIterator(); // Cell cell = cellIterator.next(); // int crNo; // if(cell.getCellType()==0) // crNo = (int)(cell.getNumericCellValue()); // else // { // System.out.println ("crNo cell value: "+cell.getStringCellValue()); // crNo = (int)Integer.parseInt(cell.getStringCellValue().trim()); // } Cell cell = cellIterator.next(); String dept; if (cell.getCellType() == 1) dept = cell.getStringCellValue(); else { int no = (int) cell.getNumericCellValue(); dept = no + ""; System.out.println(dept); } cell = cellIterator.next(); String type = cell.getStringCellValue(); cell = cellIterator.next(); int crNo; if (cell.getCellType() == 0) crNo = (int) (cell.getNumericCellValue()); else { System.out.println("crNo cell value: " + cell.getStringCellValue()); crNo = (int) Integer.parseInt(cell.getStringCellValue().trim()); } cell = cellIterator.next(); String name = cell.getStringCellValue(); cell = cellIterator.next(); String guardian = cell.getStringCellValue(); cell = cellIterator.next(); String rel = cell.getStringCellValue(); cell = cellIterator.next(); System.out.println("\n cell.getCellType() :" + cell.getCellType()); int ageYrs = 0; if (cell.getCellType() == 0) ageYrs = (int) (cell.getNumericCellValue()); else { System.out.println("age cell value: " + cell.getStringCellValue()); ageYrs = (int) Integer.parseInt(cell.getStringCellValue().trim()); } cell = cellIterator.next(); String gender = cell.getStringCellValue(); cell = cellIterator.next(); String add = cell.getStringCellValue(); cell = cellIterator.next(); String city = cell.getStringCellValue(); cell = cellIterator.next(); String state = cell.getStringCellValue(); int deptID = getDeptID(dept); assert (deptID != 0); int stateID = getStateID(state); assert (stateID != 0); int drID = getDrID(deptID); assert (drID != 0); // if drID = 0 that means a dept. has been entered which doesn't have a doctor String loginUserName = getLoginUserName(); String userID = getLoginUserID(loginUserName); long OPDNo = Long.parseLong(OPDDATE + entryNumber); boolean queryExecuted = true; if (type.equals("New")) // As only New patients must be registered and have a CR generated { try { String insertSql = "INSERT INTO Reg " + "(Regno, Name, Fname, Relation, AgeY, Sex, Address1, City, State, Department, Date)" + "VALUES(" + crNo + ",'" + name + "','" + guardian + "','" + rel + "'," + ageYrs + ",'" + gender + "','" + add + "','" + city + "'," + stateID + "," + deptID + ",'" + date + "')"; System.out.println(insertSql); Statement st = conn.createStatement(); int val = st.executeUpdate(insertSql); System.out.println("One row in Reg gets affected..."); } catch (SQLException ex) { queryExecuted = false; System.out.println("Cannot insert row into Reg...!!"); ex.printStackTrace(); } } try { String insertSql = "INSERT INTO OPD " + "(OPDNo, CrNo, PatientType, DepartmentId, DrId, Date, Time, LoginUserName, IsActive)" + "VALUES(" + OPDNo + "," + crNo + ",'" + type + "'," + deptID + "," + drID + ",'" + date + "','" + time + "','" + loginUserName + "','" + true + "')"; System.out.println(insertSql); Statement st = conn.createStatement(); int val = st.executeUpdate(insertSql); System.out.println("One row in OPD gets affected..."); } catch (SQLException ex) { queryExecuted = false; System.out.println("Cannot insert row into OPD...!!"); ex.printStackTrace(); } try { String updateSql = "UPDATE aspnet_Users " + "SET LastActivityDate='" + timestamp + "'" + "WHERE UserId='" + userID + "'"; System.out.println(updateSql); Statement st = conn2.createStatement(); int val = st.executeUpdate(updateSql); System.out.println("One row in aspnet_Users gets affected..."); } catch (SQLException ex) { queryExecuted = false; System.out.println("Cannot update timestamp in aspnet_Users...!!"); ex.printStackTrace(); } if (!queryExecuted) //if insertion to any table fails, rollback. try { conn.rollback(); conn2.rollback(); break; // and run program again at any error } catch (SQLException ex) { ex.printStackTrace(); } else try { conn.commit(); conn2.commit(); entryNumber++; // entryCrNo++; time = new Time(time.getTime() + 50000);//add 50 seconds timestamp = new Timestamp(timestamp.getTime() + 50000); } catch (SQLException ex) { ex.printStackTrace(); } } file.close(); if (conn != null) { try { // close() releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released. conn.close(); System.out.println("HMS Database connection terminated...!!!"); } catch (SQLException ex) { ex.printStackTrace(); } } if (conn2 != null) { try { // close() releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released. conn2.close(); System.out.println("HMS_Security Database connection terminated...!!!"); } catch (SQLException ex) { ex.printStackTrace(); } } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:testpoi.POIFeed2Tables.java
License:Open Source License
public static void main(String args[]) { try {/*from ww w.ja v a 2s . c o m*/ FileInputStream file = new FileInputStream(new File( "C:\\Documents and Settings\\Admin\\My Documents\\NetBeansProjects\\TestPOI\\Docs\\1.xlsx")); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); Connection conn = connectToDatabase(); assert (conn != null); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); //Skip the 1st row rowIterator.next(); //set time time = Time.valueOf("09:00:00"); //set entry number default to 1. entryNumber = 1; //set entry crNo entryCrNo = 1; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, get values of each column Iterator<Cell> cellIterator = row.cellIterator(); Cell cell = cellIterator.next(); int crNo; if (cell.getCellType() == 0) crNo = (int) (cell.getNumericCellValue()); else { System.out.println("crNo cell value: " + cell.getStringCellValue()); crNo = (int) Integer.parseInt(cell.getStringCellValue().trim()); } cell = cellIterator.next(); String dept; if (cell.getCellType() == 1) dept = cell.getStringCellValue(); else { int no = (int) cell.getNumericCellValue(); dept = no + ""; System.out.println(dept); } cell = cellIterator.next(); String name = cell.getStringCellValue(); cell = cellIterator.next(); String guardian = cell.getStringCellValue(); cell = cellIterator.next(); String rel = cell.getStringCellValue(); cell = cellIterator.next(); System.out.println("\n cell.getCellType() :" + cell.getCellType()); int ageYrs = 0; if (cell.getCellType() == 0) ageYrs = (int) (cell.getNumericCellValue()); else { System.out.println("age cell value: " + cell.getStringCellValue()); ageYrs = (int) Integer.parseInt(cell.getStringCellValue().trim()); } cell = cellIterator.next(); String gender = cell.getStringCellValue(); cell = cellIterator.next(); String add = cell.getStringCellValue(); cell = cellIterator.next(); String city = cell.getStringCellValue(); cell = cellIterator.next(); String state = cell.getStringCellValue(); int deptID = getDeptID(dept); assert (deptID != 0); int stateID = getStateID(state); assert (stateID != 0); int drID = getDrID(deptID); assert (drID != 0); // if drID = 0 that means a dept. has been entered which doesn't have a doctor String loginUserName = getLoginUserName(); long OPDNo = Long.parseLong(OPDDATE + entryNumber); boolean queryExecuted = true; try { conn.setAutoCommit(false); String insertSql = "INSERT INTO Reg " + "(Regno, Name, Fname, Relation, AgeY, Sex, Address1, City, State, Department, Date)" + "VALUES(" + crNo + ",'" + name + "','" + guardian + "','" + rel + "'," + ageYrs + ",'" + gender + "','" + add + "','" + city + "'," + stateID + "," + deptID + ",'" + date + "')"; System.out.println(insertSql); Statement st = conn.createStatement(); int val = st.executeUpdate(insertSql); System.out.println("One row in Reg gets affected..."); } catch (SQLException ex) { queryExecuted = false; System.out.println("Cannot insert row into Reg...!!"); ex.printStackTrace(); } try { String insertSql = "INSERT INTO OPD " + "(OPDNo, CrNo, PatientType, DepartmentId, DrId, Date, Time, LoginUserName, IsActive)" + "VALUES(" + OPDNo + "," + entryCrNo + ",'NEW'," + deptID + "," + drID + ",'" + date + "','" + time + "','" + loginUserName + "','" + true + "')"; System.out.println(insertSql); Statement st = conn.createStatement(); int val = st.executeUpdate(insertSql); System.out.println("One row in OPD gets affected..."); } catch (SQLException ex) { queryExecuted = false; System.out.println("Cannot insert row into OPD...!!"); ex.printStackTrace(); } if (!queryExecuted) //if insertion to any table fails, rollback. try { conn.rollback(); break; // and run program again at any error } catch (SQLException ex) { ex.printStackTrace(); } else try { conn.commit(); entryNumber++; entryCrNo++; time = new Time(time.getTime() + 90000);//add 90 seconds } catch (SQLException ex) { ex.printStackTrace(); } } file.close(); if (conn != null) { try { // close() releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released. conn.close(); System.out.println("Database connection terminated...!!!"); } catch (SQLException ex) { ex.printStackTrace(); } } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:testpoi.POI_MySQL_Test.java
License:Open Source License
public static void main(String args[]) { try {//from w w w. j ava2 s .co m FileInputStream file = new FileInputStream(new File( "C:\\Documents and Settings\\Admin\\My Documents\\NetBeansProjects\\TestPOI\\Docs\\OPD_NEW_2.xlsx")); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); Connection conn = connectToDatabase(); assert (conn != null); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); //Skip the 1st row rowIterator.next(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, get values of each column Iterator<Cell> cellIterator = row.cellIterator(); Cell cell = cellIterator.next(); int crNo; if (cell.getCellType() == 0) crNo = (int) (cell.getNumericCellValue()); else { System.out.println("crNo cell value: " + cell.getStringCellValue()); crNo = (int) Integer.parseInt(cell.getStringCellValue().trim()); } cell = cellIterator.next(); String dept; if (cell.getCellType() == 1) dept = cell.getStringCellValue(); else { int no = (int) cell.getNumericCellValue(); dept = no + ""; System.out.println(dept); } cell = cellIterator.next(); String name = cell.getStringCellValue(); cell = cellIterator.next(); String guardian = cell.getStringCellValue(); cell = cellIterator.next(); String rel = cell.getStringCellValue(); cell = cellIterator.next(); System.out.println("\n cell.getCellType() :" + cell.getCellType()); int ageYrs = 0; if (cell.getCellType() == 0) ageYrs = (int) (cell.getNumericCellValue()); else { System.out.println("age cell value: " + cell.getStringCellValue()); ageYrs = (int) Integer.parseInt(cell.getStringCellValue().trim()); } cell = cellIterator.next(); String gender = cell.getStringCellValue(); cell = cellIterator.next(); String add = cell.getStringCellValue(); cell = cellIterator.next(); String city = cell.getStringCellValue(); cell = cellIterator.next(); String state = cell.getStringCellValue(); try { Statement st = conn.createStatement(); String insertSql = "INSERT INTO OPDData VALUES(" + crNo + ",'" + dept + "','" + name + "','" + guardian + "','" + rel + "'," + ageYrs + ",'" + gender + "','" + add + "','" + city + "','" + state + "')"; System.out.println(insertSql); int val = st.executeUpdate(insertSql); System.out.println("One row get affected..."); } catch (SQLException ex) { System.out.println("Cannot connect to database server...!!"); ex.printStackTrace(); } } file.close(); if (conn != null) { try { // close() releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released. conn.close(); System.out.println("Database connection terminated...!!!"); } catch (SQLException ex) { ex.printStackTrace(); } } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }