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 Tools; 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; import Utilities.SSNDataBase; /** 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, List<String>> proclibrary; // Map< SSN, Map<ctrlNum, signOutdate>> private Map<String, List<String>> rejlibrary; private Map<String, List<String>> recylibrary; public static SSNDataBase DB; // private static final SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); // private final Workbook wb = new XSSFWorkbook(); public CompareDrotVSRoster(String encodedDrotFileName, SSNDataBase db) throws IOException { proclibrary = new TreeMap<>(); rejlibrary = new TreeMap<>(); recylibrary = new TreeMap<>(); DB = db; // 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[GlobalVar.DROT_PROCESS_STATUS_INDEX]; 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, List<ctrlNum>> private void addLeaveInLib(String[] data, Map<String, List<String>> lib) { String ctrlNum = data[GlobalVar.DROT_CTRL_NUM_INDEX]; String SSN = data[GlobalVar.DROT_SSN_INDEX]; if (!lib.containsKey(SSN)) { List<String> leaves = new ArrayList<String>(); leaves.add(ctrlNum); lib.put(SSN, leaves); } else { List<String> leaves = lib.get(SSN); if (!leaves.contains(ctrlNum)) { leaves.add(ctrlNum); 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.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!"); } } //given the color and process status: P, J, R, return cell style private void foregroundColorSetUpV1(Row row, XSSFWorkbook myWorkBook) { if (row != null) { Cell ssnCell = row.getCell(GlobalVar.LAST4_CELL_INDEX_V1); //DataFormatter df = new DataFormatter(); // String lastFour = df.formatCellValue(ssnCell); //return ***-**-**** DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); // String lastFour = df.formatCellValue(ssnCell); String lastFour = GlobalVar.last4Generator(df.formatCellValue(ssnCell)); Cell lastNameCell = row.getCell(GlobalVar.LAST_NAME_CELL_INDEX_V1); String lastName = null; if (lastNameCell != null) { lastName = lastNameCell.getStringCellValue(); } //System.out.println(lastName); String ssn = DB.getSSN(lastName, lastFour); if (ssn != null) { Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V1); String ctrlNumString = ctrlNumCell.getStringCellValue(); if (proclibrary.containsKey(ssn)) { // Map< SSN, Map<ctrlNum, signOutdate>> List<String> leaves = proclibrary.get(ssn); if (leaves.contains(ctrlNumString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); } } else if (rejlibrary.containsKey(ssn)) { List<String> leaves = rejlibrary.get(ssn); if (leaves.contains(ctrlNumString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.RED.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); } } else if (recylibrary.containsKey(ssn)) { List<String> leaves = recylibrary.get(ssn); if (leaves.contains(ctrlNumString)) { 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; } }