List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt
@Override public XSSFSheet getSheetAt(int index)
From source file:testpoi.POIFeed2Tables.java
License:Open Source License
public static void main(String args[]) { try {//from w w w. ja v a 2 s.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 ww w .j a va 2s. c om 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(); } }
From source file:testpoi.POI_POC.java
/** * @param args the command line arguments *///from w ww . j a v a2 s . c om public static void main(String[] args) { try { FileInputStream file = new FileInputStream( new File("/home/chandni/Documents/HMS Docs/01.01.2014.xlsx")); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "\t\t"); break; case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "\t\t"); break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + "\t\t"); break; } } System.out.println(""); } file.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:testpoi.ReadExcelRowsRandomly.java
License:Open Source License
public static void main(String args[]) { try {//from w w w.j a v a 2s.co 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); double random = Math.random(); int rowNum = (int) (random * sheet.getPhysicalNumberOfRows()); Row row = sheet.getRow(rowNum); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { double cellValue = cell.getNumericCellValue(); System.out.print(cellValue + "\t"); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String cellValue = cell.getStringCellValue(); System.out.print(cellValue + "\t"); } } System.out.println(); } catch (Exception e) { e.printStackTrace(); } }
From source file:testpoi_.Department.java
License:Open Source License
public static void main(String args[]) { //For Reading FileInputStream file1 = null, file2 = null, fileOldIn = null; try {/*from w w w . j a va 2 s. co m*/ file1 = new FileInputStream(new File(path + "new.xlsx")); XSSFWorkbook workbook1 = new XSSFWorkbook(file1); //Get first sheet from the workbook1 sheetAll = workbook1.getSheetAt(0); //Get second sheet from the workbook1 sheetFemale = workbook1.getSheetAt(1); file2 = new FileInputStream(new File(path + "children.xlsx")); XSSFWorkbook workbook2 = new XSSFWorkbook(file2); //Get first sheet from the workbook2 sheetChildren = workbook2.getSheetAt(0); fileOldIn = new FileInputStream(new File(path + "old.xlsx")); workbookOld = new XSSFWorkbook(fileOldIn); } catch (Exception e) { System.err.println("Error opening files for reading."); e.printStackTrace(); } //For writing XSSFWorkbook workbook = new XSSFWorkbook(); sheetNew = workbook.createSheet("Generated File - Do not edit"); //Create a new row in current sheet for heading. Row row = sheetNew.createRow(0); //Create a new cell in current row Cell cell = row.createCell(0); //Set value to new value cell.setCellValue("Department"); cell = row.createCell(1); cell.setCellValue("Patient Type"); cell = row.createCell(2); cell.setCellValue("CR No."); cell = row.createCell(3); cell.setCellValue("Name"); cell = row.createCell(4); cell.setCellValue("Guardian's Name"); cell = row.createCell(5); cell.setCellValue("Relation"); cell = row.createCell(6); cell.setCellValue("AgeYrs"); cell = row.createCell(7); cell.setCellValue("Gender"); cell = row.createCell(8); cell.setCellValue("Address"); cell = row.createCell(9); cell.setCellValue("City"); cell = row.createCell(10); cell.setCellValue("State"); rowCnt = 1; femaleRowNum = 1; childRowNum = 1; allRowNum = 1; /************************ TO SET AT EVERY RUN **************************/ crNo = 1050; deptts = new ArrayList<>(); /* New */ deptts.add(new Department("Medicine", 118, true)); deptts.add(new Department("Surgery", 89, true)); deptts.add(new Department("Obs & Gynae", 67, true)); deptts.add(new Department("Paediatrics", 20, true)); deptts.add(new Department("Orthopaedics", 54, true)); deptts.add(new Department("Ophthalmology", 33, true)); deptts.add(new Department("ENT", 28, true)); deptts.add(new Department("Dental", 27, true)); deptts.add(new Department("Casualty", 42, true)); /* Old */ deptts.add(new Department("Medicine", 15, false)); deptts.add(new Department("Surgery", 13, false)); deptts.add(new Department("Obs & Gynae", 12, false)); deptts.add(new Department("Paediatrics", 9, false)); deptts.add(new Department("Orthopaedics", 11, false)); deptts.add(new Department("Ophthalmology", 16, false)); deptts.add(new Department("ENT", 6, false)); deptts.add(new Department("Dental", 8, false)); // Casualty is only new /***********************************************************************/ //Fill depttToOldSheetsMap Iterator<XSSFSheet> oldSheetsIter = workbookOld.iterator(); //Skip 1st sheet which contains all old patients oldSheetsIter.next(); depttToOldSheetsMap = new HashMap<>(); while (oldSheetsIter.hasNext()) { XSSFSheet oldSheet = oldSheetsIter.next(); depttToOldSheetsMap.put(oldSheet.getSheetName(), new OldDepttSheet(oldSheet)); } try { generateRows(); } catch (IllegalArgumentException e) { System.err.println(e.getMessage()); e.printStackTrace(); } try { FileOutputStream out = new FileOutputStream(new File(path + date + ".xlsx")); workbook.write(out); out.close(); if (file1 != null) file1.close(); if (file2 != null) file2.close(); System.out.println("Excel written successfully.."); } catch (Exception e) { e.printStackTrace(); } }
From source file:testpoi_.OPDDataTransfer.java
License:Open Source License
public static void main(String args[]) { try {/*from ww w. j a v a 2 s. c o m*/ 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(); /*************************** TO UPDATE ON ERROR ******************************/ //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(); 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:timetable.translate.CourseInfoTranslator.java
public boolean convertToCourseStruct(XSSFWorkbook workbook, List<CourseStruct> coursesInfo) { XSSFSheet courseInfoSheet = workbook.getSheetAt(0); XSSFCell cell;// w w w .ja v a2 s . c o m for (Row row : courseInfoSheet) { CourseStruct tempCourseStruct = new CourseStruct(); tempCourseStruct.courseCode = row.getCell(0).getStringCellValue(); tempCourseStruct.courseTitle = row.getCell(1).getStringCellValue(); tempCourseStruct.batch = row.getCell(2).getStringCellValue(); if (row.getCell(3) != null) { tempCourseStruct.teacher = row.getCell(3).getStringCellValue(); } else { tempCourseStruct.teacher = ""; } if (!tempCourseStruct.courseCode.equals("Course Code")) { coursesInfo.add(tempCourseStruct); } } return true; }
From source file:timetable.translate.ScheduleTranslator.java
public boolean convertToTableStruct(XSSFWorkbook workbook, TableStruct[] semesterTables) { XSSFSheet[] semesterSheets = new XSSFSheet[workbook.getNumberOfSheets()]; XSSFRow[] row = new XSSFRow[workbook.getNumberOfSheets()]; XSSFCell[] cell = new XSSFCell[workbook.getNumberOfSheets()]; int firstCell = 1; int lastCell = 8; int firstRow = 6; int lastRow = 10; for (int iSheet = 0; iSheet < workbook.getNumberOfSheets(); iSheet++) { semesterSheets[iSheet] = workbook.getSheetAt(iSheet); if (semesterSheets[iSheet] != null) { //Setting the headers semesterTables[iSheet].university = semesterSheets[iSheet].getRow(0).getCell(1) .getStringCellValue(); semesterTables[iSheet].department = semesterSheets[iSheet].getRow(1).getCell(1) .getStringCellValue(); semesterTables[iSheet].semester = semesterSheets[iSheet].getRow(2).getCell(1).getStringCellValue(); semesterTables[iSheet].section = semesterSheets[iSheet].getRow(3).getCell(1).getStringCellValue(); semesterTables[iSheet].classRoom = semesterSheets[iSheet].getRow(3).getCell(1).getStringCellValue(); ArrayList<CellRangeAddress> allMergedRegions = (ArrayList<CellRangeAddress>) semesterSheets[iSheet] .getMergedRegions(); ArrayList<CellStruct> mergedRegions = new ArrayList<>(); Iterator<CellRangeAddress> iter = allMergedRegions.iterator(); while (iter.hasNext()) { CellRangeAddress region = iter.next(); if ((region.getFirstRow() >= firstRow) && (region.getLastRow() <= lastRow) && (region.getFirstColumn() >= firstCell - 1) && (region.getLastColumn() <= lastCell)) { mergedRegions.add(new CellStruct(region.getFirstRow(), region.getFirstColumn())); }/*from w w w .j a v a2 s . c o m*/ } int iRow = firstRow; for (int physRow = 0; physRow < 5; physRow++) { row[iSheet] = semesterSheets[iSheet].getRow(iRow); CellStruct currentDayCell = new CellStruct(iRow, firstCell - 1); if (mergedRegions.contains(currentDayCell)) { //This is a merged row. int iCell = firstCell; for (int physCol = 0; physCol < 8; physCol++) { CellStruct currentCell = new CellStruct(iRow, iCell); if (mergedRegions.contains(currentCell)) { //This is a merged cell. cell[iSheet] = row[iSheet].getCell(iCell); String currentStr = cell[iSheet].getStringCellValue(); semesterTables[iSheet].table[physRow][physCol] = currentStr; physCol++; semesterTables[iSheet].table[physRow][physCol] = currentStr; iCell++; } else { cell[iSheet] = row[iSheet].getCell(iCell); String currentStr = cell[iSheet].getStringCellValue(); semesterTables[iSheet].table[physRow][physCol] = currentStr; } iCell++; } iRow++; iCell = firstCell; row[iSheet] = semesterSheets[iSheet].getRow(iRow); for (int physCol = 0; physCol < 8; physCol++) { CellStruct currentCell = new CellStruct(iRow, iCell); if (mergedRegions.contains(currentCell)) { //This is a merged cell. cell[iSheet] = row[iSheet].getCell(iCell); String currentStr = cell[iSheet].getStringCellValue(); semesterTables[iSheet].altTable[physRow][physCol] = currentStr; physCol++; semesterTables[iSheet].altTable[physRow][physCol] = currentStr; iCell++; } else { cell[iSheet] = row[iSheet].getCell(iCell); String currentStr = cell[iSheet].getStringCellValue(); semesterTables[iSheet].altTable[physRow][physCol] = currentStr; } iCell++; } } else { //Not a merged row. int iCell = firstCell; for (int physCol = 0; physCol < 8; physCol++) { CellStruct currentCell = new CellStruct(iRow, iCell); if (mergedRegions.contains(currentCell)) { //This is a merged cell. cell[iSheet] = row[iSheet].getCell(iCell); String currentStr = cell[iSheet].getStringCellValue(); semesterTables[iSheet].table[physRow][physCol] = currentStr; physCol++; semesterTables[iSheet].table[physRow][physCol] = currentStr; iCell++; } else { cell[iSheet] = row[iSheet].getCell(iCell); String currentStr = cell[iSheet].getStringCellValue(); semesterTables[iSheet].table[physRow][physCol] = currentStr; } iCell++; } } iRow++; } } } return true; }
From source file:timetable.translate.StudentInfoTranslator.java
public boolean convertToStudentMap(XSSFWorkbook workbook, HashMap<String, String> studentMap, List<CourseStruct> coursesInfo, List<String> courseList) { XSSFSheet[] enrolmentSheets = new XSSFSheet[workbook.getNumberOfSheets()]; // XSSFRow[] row = new XSSFRow[workbook.getNumberOfSheets()]; // XSSFCell[] cell = new XSSFCell[workbook.getNumberOfSheets()]; for (int iSheet = 0; iSheet < workbook.getNumberOfSheets(); iSheet++) { enrolmentSheets[iSheet] = workbook.getSheetAt(iSheet); if (enrolmentSheets[iSheet] != null) { for (Row row : enrolmentSheets[iSheet]) { StudentStruct tempStudent = new StudentStruct(); tempStudent.studentID = row.getCell(0).getStringCellValue(); tempStudent.studentName = row.getCell(1).getStringCellValue(); if ((!tempStudent.studentID.equals("Registration Number")) && (!tempStudent.studentID.equals("Name"))) { coursesInfo.get(courseList.indexOf(workbook.getSheetName(iSheet))).enrolledStudents .add(tempStudent); studentMap.put(tempStudent.studentID, tempStudent.studentName); }//from w ww .j a v a 2 s .c o m // studentMap.remove("Registration Number", "Name"); } } } return true; }
From source file:Tools.CompareDrotVSRoster.java
public void colorXLSXFile(String leaveXlsxRoster) throws FileNotFoundException, IOException { File xlsxFile = new File(leaveXlsxRoster); try {//from w w w. j av a 2 s. c o m FileInputStream fis = new FileInputStream(xlsxFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); //Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); // Traversing over each row of XLSX file if (rowIterator.hasNext()) { Row headerRow = rowIterator.next(); //skip the header row Iterator<Cell> it = headerRow.cellIterator(); int numCell = 0; // List<String> keyList = new ArrayList<>(); //keep track info of each column while (it.hasNext()) { //keyList.add(it.next().getStringCellValue()); it.next(); numCell++; } if (numCell == GlobalVar.LEAVE_TITLES_V1.length) { // correct xlsx file int rowNum = 1; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //row.getRowStyle(); rowNum++; } for (int i = 1; i < rowNum; i++) { Row row = mySheet.getRow(i); foregroundColorSetUpV1(row, myWorkBook); //check each row and update foreground color } fis.close(); FileOutputStream output; String targetFile = null; String[] names = leaveXlsxRoster.split("\\."); if (!names[0].equals(leaveXlsxRoster)) { //doesn't contain surfix targetFile = names[0] + "COLORED.xlsx"; } else { targetFile = leaveXlsxRoster + "COLORED.xlsx"; } output = new FileOutputStream(targetFile); myWorkBook.write(output); output.close(); //myWorkBook.write(output); } else if (numCell == GlobalVar.LEAVE_TITLES_V2.length) { // full ssn roster System.out.println("CompareDrotVsRoster.java: Unsupported."); JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info! " + "CompareDrotVsRoster.java: Title V2 is not supported"); } else { JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info!"); } } else { JOptionPane.showMessageDialog(null, "XLSX file is empty!"); System.out.println("The xlsx file is empty!"); } JOptionPane.showMessageDialog(null, "The leave roster is colored successfully. Please check *COLORED.xlsx."); // finds the work book in stance for XLSX file } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "Xlsx file not found!"); } }