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.awt.Color; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.text.SimpleDateFormat; import java.util.*; 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.CellStyle; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import utilities.GlobalVar; import utilities.MyDate; /** Verify drots with xlsx signed leave roster. So far it assumes the transactions on leave rosters are for * report only */ public class CompareDrotVSRoster { private Map<String, Map<String, String>> proclibrary; // Map< SSN, Map<ctrlNum, signOutdate>> private Map<String, Map<String, String>> rejlibrary; private Map<String, Map<String, String>> recylibrary; // private static final SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); // private final Workbook wb = new XSSFWorkbook(); public CompareDrotVSRoster(String encodedDrotFileName) throws IOException { proclibrary = new TreeMap<>(); rejlibrary = new TreeMap<>(); recylibrary = new TreeMap<>(); // decode the drot file String outputFileName = GlobalVar.SECURE_FOLDER_NAME + "/temp.txt"; // decoded file GlobalVar.decode(encodedDrotFileName, outputFileName); File outputFile = new File(outputFileName); Scanner lines = new Scanner(outputFile); while (lines.hasNextLine()) { String line = lines.nextLine(); String[] data = line.split(GlobalVar.PARSE); int processStatusIndex = data.length - 1; // the last element in the array is the process status String processStatus = data[processStatusIndex]; switch (processStatus) { case GlobalVar.LEAVE_PROCESSED: addLeaveInLib(data, proclibrary); break; case GlobalVar.LEAVE_REJECT: case GlobalVar.LEAVE_REJPART: addLeaveInLib(data, rejlibrary); break; case GlobalVar.LEAVE_RECYCLED: addLeaveInLib(data, recylibrary); break; } } lines.close(); outputFile.delete(); } // put the drot file in a good data structure //Map< SSN, Map<ctrlNum, Map<date, processStatus>>> // processStatus P will overwrite other status // open xlsx file // Map< SSN, Map<ctrlNum, signOutdate>> private void addLeaveInLib(String[] data, Map<String, Map<String, String>> lib) { String ctrlNum = data[GlobalVar.DROT_CTRL_NUM_INDEX]; String SSN = data[GlobalVar.DROT_SSN_INDEX]; //String signIn = data[GlobalVar.DROT_SIGN_IN_DATE_INDEX]; String signOut = data[GlobalVar.DROT_SIGN_OUT_DATE_INDEX]; String leave = signOut; // List<String> leave = new ArrayList<>(); // leave.add(signOut); //leave.add(signIn); if (!lib.containsKey(SSN)) { Map<String, String> leaves = new TreeMap<>(); leaves.put(ctrlNum, leave); lib.put(SSN, leaves); } else { Map<String, String> leaves = lib.get(SSN); if (!leaves.containsKey(ctrlNum)) { leaves.put(ctrlNum, leave); lib.put(SSN, leaves); } } } // open an existing signed leave roster and set the background of leave transactions according // to their process status: red - reject, yellow - recycle, and green - processed. public void colorXLSXFile(String leaveXlsxRoster) throws FileNotFoundException, IOException { File xlsxFile = new File(leaveXlsxRoster); try { 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.SIGNED_LEAVE_TITLES.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); foregroundColorSetUp(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 { 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!"); } } //given the color and process status: P, J, R, return cell style private void foregroundColorSetUp(Row row, XSSFWorkbook myWorkBook) { Cell ssnCell = row.getCell(GlobalVar.LAST4_CELL_INDEX_V1); DataFormatter df = new DataFormatter(); String ssnString = df.formatCellValue(ssnCell); //return ***-**-**** ssnString = ssnString.replace("-", ""); Cell soCell = row.getCell(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1); String signOutDateString = df.formatCellValue(soCell); //return ***-**-**** Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V1); String ctrlNumString = ctrlNumCell.getStringCellValue(); if (proclibrary.containsKey(ssnString)) { // Map< SSN, Map<ctrlNum, signOutdate>> Map<String, String> leaves = proclibrary.get(ssnString); if (leaves.containsKey(ctrlNumString)) { String date = leaves.get(ctrlNumString); if (date.equals(signOutDateString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); } } } else if (rejlibrary.containsKey(ssnString)) { Map<String, String> leaves = rejlibrary.get(ssnString); if (leaves.containsKey(ctrlNumString)) { String date = leaves.get(ctrlNumString); if (date.equals(signOutDateString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.RED.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); } } } else if (recylibrary.containsKey(ssnString)) { Map<String, String> leaves = recylibrary.get(ssnString); if (leaves.containsKey(ctrlNumString)) { String date = leaves.get(ctrlNumString); if (date.equals(signOutDateString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); } } } } // create a standard style for given workbook private CellStyle createStandardStyle(XSSFWorkbook myWorkBook) { CellStyle style = myWorkBook.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setWrapText(true); return style; } }