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 eventHandlers; 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 final int DUPLICATE_CTRL_NUM = -1; // private static final int OVERLAP_LEAVE = -2; // private static final int TRASH = -9; // private static final int GOOD_LEAVE = 1; private static SSNDataBase DB; //private Map<String, Map<String, List<String>>> leaves; //key = SSN, value = {key = ctrl, si, so} public XLSX2BatchHandler(String xlsxFileName, String batchFileName, SSNDataBase database) throws IOException { //leaves = new HashMap<String, Map<String, List<String>>>(); DB = database; File file = new File(batchFileName); 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); 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()); numCell++; } if (numCell == GlobalVar.LEAVE_TITLES_V1.length) { // check if the excel is the leave roster int globalCount = 1; MyDate soDate = new MyDate(); MyDate siDate = new MyDate(); while (rowIterator.hasNext()) { //int cellCount = 0; lineCount++; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Map<String, String> container = new HashMap<String, String>(); int keys = 0; //index of the cell // reset date every row Boolean soDateAssigned = false; Boolean siDateAssigned = false; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); int type = cell.getCellType(); // distinguish the cell content, in the xlsx file, // SSN and dates are CELL_TYPE_NUMERIC // number of leave days is CELL_TYPE_FORMULA // the rest is CELL_TYPE_STRING if (type == HSSFCell.CELL_TYPE_NUMERIC && keys != GlobalVar.LAST4_CELL_INDEX_V1) { // dates DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); String dateString = df.formatCellValue(cell); if (!soDateAssigned && !siDateAssigned) { //System.out.println(dateString); //150201 soDate = new MyDate(dateString); soDateAssigned = true; } else if (soDateAssigned && !siDateAssigned) { siDate = new MyDate(dateString); siDateAssigned = true; } else { // do nothing //System.out.println("We should never come here!"); } //String value = (int) cell.getNumericCellValue() + ""; // number of days container.put(keyList.get(keys), dateString); // System.out.println(value); // } else if (type == HSSFCell.CELL_TYPE_STRING // && keys == GlobalVar.SSN_CELL_INDEX){ } else if (keys == GlobalVar.LAST4_CELL_INDEX_V1) { DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); String lastFour = df.formatCellValue(cell); //return ***-**-**** // String lastFour = cell.getStringCellValue(); //ssnString = ssnString.replace("-",""); lastFour = GlobalVar.last4Generator(lastFour); // System.out.println("SSN: " + lastFour); container.put(keyList.get(keys), lastFour); } else if (keys == GlobalVar.LAST_NAME_CELL_INDEX_V1) { String value = cell.getStringCellValue(); container.put(keyList.get(keys), value); // } else if (type == HSSFCell.CELL_TYPE_FORMULA) { // String days = soDate.getDaysDifftoString(siDate); // container.put(keyList.get(keys),days); // //reset the dates } else if (type == HSSFCell.CELL_TYPE_STRING) { String value; if (keys == GlobalVar.LEAVE_AREA_CELL_INDEX_V1) { String str2 = cell.getStringCellValue(); //System.out.println(str2); value = GlobalVar.getDMOLeaveArea(str2); //System.out.println(value); } else if (keys == GlobalVar.LEAVE_TYPE_CELL_INDEX_V1) { value = GlobalVar.getDMOLeaveType(cell.getStringCellValue()); //System.out.println(value); } else { value = 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 = container.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V1)); 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); String leaveDays = GlobalVar.computeNumOfDays(new MyDate(signOutDate), new MyDate(signInDate)); int leaveMsg = GlobalVar.checkLeaves(ctrlNum, SSN, signOutDate, signInDate, leaves); if (leaveMsg == GlobalVar.GOOD_LEAVE) { String lc = GlobalVar.LC; String inputSource = ctrlNum.substring(0, 2); String cycle = GlobalVar.CYCLE; String data = "0SB03" + SSN + first5 + signOutDate + signInDate + leaveType + leaveDays + leaveArea + ctrlNum + "000" + GlobalVar.whiteSpace() + lc + inputSource + cycle + "@" + GlobalVar.globalCountGenerator(globalCount); output.println(data); globalCount++; } else if (leaveMsg == GlobalVar.OVERLAP_LEAVE) { //String lastName = container.get(keyList.get(GlobalVar.LAST_NAME_INDEX)); String msg = "Line" + lineCount + " : " + ctrlNum + " " + lastName + " " + signOutDate + " - " + signInDate; JOptionPane.showMessageDialog(null, msg + "\n is overlapping with a leave posted before."); } else if (leaveMsg == GlobalVar.DUPLICATE_CTRL_NUM) { //String lastName = container.get(keyList.get(GlobalVar.LAST_NAME_INDEX)); JOptionPane.showMessageDialog(null, "Line" + lineCount + " : Duplicate control number " + ctrlNum + " for " + lastName + "."); } } fis.close(); output.close(); } 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!"); } // finds the work book in stance for XLSX file } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "Xlsx file not found!"); Logger.getLogger(XLSX2BatchHandler.class.getName()).log(Level.SEVERE, null, ex); } } // Given the cycle, return true if the cycle is in the right format: two character long, // the first one is a letter, the second one is a number // return false otherwise. // private Boolean checkCycle(String str) { // if (str.length() == GlobalVar.CYCLE.length()) { // str = str.toUpperCase(); // if (str.charAt(0) >= 'A' && str.charAt(0) <= 'Z') { // return (str.charAt(1) >= '0' && str.charAt(1) <= '9'); // } else { // return false; // } // } else { // return false; // } // } }