Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package Files; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.PrintStream; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.logging.Level; import java.util.logging.Logger; import javax.swing.JOptionPane; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import Utilities.GlobalVar; import Utilities.MultiKeyMap; import Utilities.MyDate; import Utilities.SSNDataBase; /** * Export XLSX to a batch file. * @author SPC CUI */ public final class XLSX2BatchHandler { private static SSNDataBase DB; private boolean batchFileExists; // private String ADSN; public XLSX2BatchHandler(String xlsxFileName, String batchFileName, SSNDataBase database, String adsn) throws IOException { DB = database; File file = new File(batchFileName); batchFileExists = false; ADSN = adsn; if (file.exists()) { int response = JOptionPane.showConfirmDialog(null, "The batch file " + "already exsits. Do you want to overwrite it?", "Confirm", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE); if (response == JOptionPane.YES_OPTION) { exportBatchFile(xlsxFileName, batchFileName); } } else { exportBatchFile(xlsxFileName, batchFileName); } } public void exportBatchFile(String xlsxFileName, String batchFileName) throws IOException { File myFile = new File(xlsxFileName); FileOutputStream outputFile = new FileOutputStream(batchFileName); //allow to append PrintStream output = new PrintStream(outputFile); try { FileInputStream fis = new FileInputStream(myFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); //Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); List<String> errorList = new ArrayList<>(); Map<String, Map<String, List<String>>> leaves = new HashMap<String, Map<String, List<String>>>(); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); int lineCount = 1; // 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<String>(); //keep track info of each column while (it.hasNext()) { keyList.add(it.next().getStringCellValue()); // add the title in xlsx to keyList numCell++; } System.out.println("XLSX2BatchHandler.java UCFR loaded = " + DB.isUCFRLoaded()); if (numCell == GlobalVar.LEAVE_TITLES_V1.length && DB.isUCFRLoaded()) { // check if the excel is the leave roster System.out.println("XLSX2BatchHandler.java: V1, UCFR is loaded."); int globalCount = 1; while (rowIterator.hasNext()) { lineCount++; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Map<String, String> container = new HashMap<>(); int keys = 0; //index of the cell // reset date every row while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (keys == GlobalVar.CTRL_NUM_CELL_INDEX_V1 || keys == GlobalVar.LAST_NAME_CELL_INDEX_V1) { String value = cell.getStringCellValue(); container.put(keyList.get(keys), value); } else if (keys == GlobalVar.LAST4_CELL_INDEX_V1) { DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); String lastFour = df.formatCellValue(cell); //return ***-**-**** lastFour = GlobalVar.last4Generator(lastFour); container.put(keyList.get(keys), lastFour); } else if (keys == GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1 || keys == GlobalVar.SIGN_IN_DATE_CELL_INDEX_V1) { DataFormatter df = new DataFormatter(); String dateString = df.formatCellValue(cell); container.put(keyList.get(keys), dateString); } else if (keys == GlobalVar.LEAVE_AREA_CELL_INDEX_V1) { int type = cell.getCellType(); String value = null; if (type == HSSFCell.CELL_TYPE_STRING) { value = GlobalVar.getDMOLeaveArea(cell.getStringCellValue()); } else if (type == HSSFCell.CELL_TYPE_NUMERIC) { value = GlobalVar.getDMOLeaveArea(cell.getNumericCellValue()); //read a double and return string } container.put(keyList.get(keys), value); } else if (keys == GlobalVar.LEAVE_TYPE_CELL_INDEX_V1) { String value = GlobalVar.getDMOLeaveType(cell.getStringCellValue()); container.put(keyList.get(keys), value); } keys++; } // public static final String[] SIGNED_LEAVE_TITLES = {"Ctrl Number", // "SSN", "Last Name", "Sign-in Date", "Sign-out Date", "Leave Area", // "Leave Type", "Num of Days", "First Five","Projected Sign-in Date", "Projected Sign-out Date"}; // make sure the key mataches the name in the header String ctrlNum = GlobalVar .readCtrlNum(container.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V1))); if (ctrlNum == null) { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid ctrl number received."); } String lastName = container.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V1)); String lastFour = container.get(keyList.get(GlobalVar.LAST4_CELL_INDEX_V1)); // last four // lastName = DB.getSSN(lastName, lastFour); String signOutDate = container.get(keyList.get(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1)); String signInDate = container.get(keyList.get(GlobalVar.SIGN_IN_DATE_CELL_INDEX_V1)); String leaveArea = container.get(keyList.get(GlobalVar.LEAVE_AREA_CELL_INDEX_V1)); String leaveType = container.get(keyList.get(GlobalVar.LEAVE_TYPE_CELL_INDEX_V1)); //Map<String, String> thisMap = DB.get(thislastName, lastFour); String SSN = DB.getSSN(lastName, lastFour); //System.out.println("xlsx2batchHandler.java" + SSN + "+" + lastName); String first5 = DB.getFirst5(lastName, lastFour); MyDate soDate = new MyDate(signOutDate); MyDate siDate = new MyDate(signInDate); String leaveDays = "---"; if (siDate != null && siDate.afterOrEqual(soDate)) { leaveDays = GlobalVar.computeNumOfDays(soDate, siDate); } else { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": How to sign in before sign out?"); } if (leaveDays != null) { int leaveMsg = GlobalVar.checkLeaves(ctrlNum, SSN, signOutDate, signInDate, first5, leaves); //add leave into leaves globalCount = GlobalVar.batchGenerator(SSN, first5, lastName, signOutDate, signInDate, leaveType, leaveDays, leaveArea, ctrlNum, leaveMsg, lineCount, globalCount, output, errorList, ADSN); } else { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid dates are entered!"); } } String msg = "BatchGenerator.java: The batch file is generated successfully! \n"; if (!errorList.isEmpty()) { for (String errorMsg : errorList) { msg += errorMsg + "\n"; } } batchFileExists = true; JOptionPane.showMessageDialog(null, msg); fis.close(); // output.close(); // outputFile.close(); } else if (numCell == GlobalVar.LEAVE_TITLES_V2.length && DB.isUCFRLoaded()) { // full SSN xlsx System.out.println("XLSX2BatchHandler.java: V2, UCFR is loaded."); int globalCount = 1; while (rowIterator.hasNext()) { //int cellCount = 0; lineCount++; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Map<String, String> rowContainer = new HashMap<>(); //store info of each row rowContainerBuilder(rowContainer, keyList, cellIterator); // update rowContainer // public static final String[] SIGNED_LEAVE_TITLES = {"Ctrl Number", // "SSN", "Last Name", "Sign-in Date", "Sign-out Date", "Leave Area", // "Leave Type", "Num of Days", "First Five","Projected Sign-in Date", "Projected Sign-out Date"}; // make sure the key mataches the name in the header String ctrlNum = GlobalVar .readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2))); if (ctrlNum == null) { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid ctrl number received."); } // String lastName = rowContainer.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V2)); String fullSSN = rowContainer.get(keyList.get(GlobalVar.FULL_SSN_CELL_INDEX_V2)); // full ssn String lastName = DB.getLastNamefromSSN(fullSSN); String lastFour = GlobalVar.last4Generator(fullSSN); String signOutDate = rowContainer.get(keyList.get(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V2)); String signInDate = rowContainer.get(keyList.get(GlobalVar.SIGN_IN_DATE_CELL_INDEX_V2)); String leaveArea = rowContainer.get(keyList.get(GlobalVar.LEAVE_AREA_CELL_INDEX_V2)); String leaveType = rowContainer.get(keyList.get(GlobalVar.LEAVE_TYPE_CELL_INDEX_V2)); String first5 = DB.getFirst5(lastName, lastFour); MyDate soDate = new MyDate(signOutDate); MyDate siDate = new MyDate(signInDate); String leaveDays = "---"; if (siDate != null && siDate.afterOrEqual(soDate)) { leaveDays = GlobalVar.computeNumOfDays(soDate, siDate); } else { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": How to sign in before sign out?"); } int leaveMsg = GlobalVar.checkLeaves(ctrlNum, fullSSN, signOutDate, signInDate, first5, leaves); globalCount = GlobalVar.batchGenerator(fullSSN, first5, lastName, signOutDate, signInDate, leaveType, leaveDays, leaveArea, ctrlNum, leaveMsg, lineCount, globalCount, output, errorList, ADSN); } String msg = "BatchGenerator.java: The batch file is generated successfully! \n"; if (!errorList.isEmpty()) { for (String errorMsg : errorList) { msg += errorMsg + "\n"; } } batchFileExists = true; JOptionPane.showMessageDialog(null, msg); fis.close(); // output.close(); // outputFile.close(); } else if (numCell == GlobalVar.LEAVE_TITLES_V2.length && !DB.isUCFRLoaded()) { System.out.println("XLSX2BatchHandler.java: V2, UCFR is not loaded."); int globalCount = 1; while (rowIterator.hasNext()) { //int cellCount = 0; lineCount++; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Map<String, String> rowContainer = new HashMap<>(); //store info of each row rowContainerBuilder(rowContainer, keyList, cellIterator); // update rowContainer // public static final String[] SIGNED_LEAVE_TITLES = {"Ctrl Number", // "SSN", "Last Name", "Sign-in Date", "Sign-out Date", "Leave Area", // "Leave Type", "Num of Days", "First Five","Projected Sign-in Date", "Projected Sign-out Date"}; // make sure the key mataches the name in the header String ctrlNum = GlobalVar .readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2))); //String ctrlNum = GlobalVar.readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2))); if (ctrlNum == null) { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid ctrl number received."); } // String lastName = rowContainer.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V2)); String fullSSN = rowContainer.get(keyList.get(GlobalVar.FULL_SSN_CELL_INDEX_V2)); // full ssn String lastName = GlobalVar.TEMP_DMO_NAME; String signOutDate = rowContainer.get(keyList.get(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V2)); String signInDate = rowContainer.get(keyList.get(GlobalVar.SIGN_IN_DATE_CELL_INDEX_V2)); String leaveArea = rowContainer.get(keyList.get(GlobalVar.LEAVE_AREA_CELL_INDEX_V2)); String leaveType = rowContainer.get(keyList.get(GlobalVar.LEAVE_TYPE_CELL_INDEX_V2)); String first5 = GlobalVar.TEMP_DMO_NAME; MyDate soDate = new MyDate(signOutDate); MyDate siDate = new MyDate(signInDate); String leaveDays = "---"; if (siDate != null && siDate.getDay() != null && soDate != null && soDate.getDay() != null) { if (siDate.afterOrEqual(soDate)) { leaveDays = GlobalVar.computeNumOfDays(soDate, siDate); } else { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": How to sign in before sign out?"); } } else { JOptionPane.showMessageDialog(null, "Line " + lineCount + ": Invalid date entered"); } int leaveMsg = GlobalVar.checkLeaves(ctrlNum, fullSSN, signOutDate, signInDate, first5, leaves); // write the leave on the batch file (PrintStream output) globalCount = GlobalVar.batchGenerator(fullSSN, first5, lastName, signOutDate, signInDate, leaveType, leaveDays, leaveArea, ctrlNum, leaveMsg, lineCount, globalCount, output, errorList, ADSN); } String msg = "BatchGenerator.java: The batch file is generated successfully! \n"; if (!errorList.isEmpty()) { for (String errorMsg : errorList) { msg += errorMsg + "\n"; } } batchFileExists = true; JOptionPane.showMessageDialog(null, msg); fis.close(); } else if (!DB.isUCFRLoaded()) { JOptionPane.showMessageDialog(null, "XLSX2BatchHandler.java: UCFR needs to be loaded to process this type of xlsx."); } else { JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct format!"); } } else { JOptionPane.showMessageDialog(null, "XLSX file is empty!"); System.out.println("The xlsx file is empty!"); } // finds the work book in stance for XLSX file output.close(); outputFile.close(); } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "XLSX2BatchHandler.java: Xlsx file not found!"); Logger.getLogger(XLSX2BatchHandler.class.getName()).log(Level.SEVERE, null, ex); } } public boolean doesBatchFileExist() { return batchFileExists; } public void rowContainerBuilder(Map<String, String> rowContainer, List<String> keyList, Iterator<Cell> cellIterator) { int keys = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (keys == GlobalVar.CTRL_NUM_CELL_INDEX_V2) { String value = cell.getStringCellValue(); rowContainer.put(keyList.get(keys), value); } else if (keys == GlobalVar.FULL_SSN_CELL_INDEX_V2) { int type = cell.getCellType(); String fullSSN = null; if (type == HSSFCell.CELL_TYPE_STRING) { fullSSN = cell.getStringCellValue(); //string type ssn fullSSN = fullSSN.replaceAll("-", ""); } else { DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); fullSSN = df.formatCellValue(cell); //return ***-**-**** } fullSSN = GlobalVar.fullSSNgenerator(fullSSN); rowContainer.put(keyList.get(keys), fullSSN); } else if (keys == GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V2 || keys == GlobalVar.SIGN_IN_DATE_CELL_INDEX_V2) { DataFormatter df = new DataFormatter(); String dateString = df.formatCellValue(cell); rowContainer.put(keyList.get(keys), dateString); } else if (keys == GlobalVar.LEAVE_AREA_CELL_INDEX_V2) { int type = cell.getCellType(); String value = null; if (type == HSSFCell.CELL_TYPE_STRING) { value = GlobalVar.getDMOLeaveArea(cell.getStringCellValue()); } else if (type == HSSFCell.CELL_TYPE_NUMERIC) { value = GlobalVar.getDMOLeaveArea(cell.getNumericCellValue()); //read a double and return string } rowContainer.put(keyList.get(keys), value); } else if (keys == GlobalVar.LEAVE_TYPE_CELL_INDEX_V2) { String value = GlobalVar.getDMOLeaveType(cell.getStringCellValue()); rowContainer.put(keyList.get(keys), value); } keys++; } } }