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 punchcardrecords.ui; import java.awt.Desktop; import java.awt.Image; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.math.BigDecimal; import java.text.DecimalFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.Map; import java.util.Set; import java.util.logging.Level; import java.util.logging.Logger; import javax.imageio.ImageIO; import javax.swing.JFileChooser; import javax.swing.JOptionPane; import javax.swing.JProgressBar; import javax.swing.filechooser.FileNameExtensionFilter; import org.apache.commons.io.FileUtils; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFPalette; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.OfficeXmlFileException; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import punchcardrecords.common.CommonPrintStream; import punchcardrecords.common.PropertyUtils; /** * * @author Administrator */ public class PunchCardRecordsMainFrame extends javax.swing.JFrame { /** * Creates new form PunchCardRecordsMainFrame */ public PunchCardRecordsMainFrame() { initComponents(); redirectSyetemPrint(); initProcessBar(); } /** * ?? */ private void initProcessBar() { progressBar.setOrientation(JProgressBar.HORIZONTAL); progressBar.setStringPainted(true); progressBar.setMinimum(0); progressBar.setMaximum(100); progressBar.setValue(0); progressBar.setString("?"); } /** * ??? */ private void redirectSyetemPrint() { printStream = new CommonPrintStream(System.out, statusView); System.setOut(printStream); //System.setErr(printStream); } /** * ????? */ public void init() { // this.setTitle(PropertyUtils.instanceVersion().getName()); abort = new Abort(this); try { // Image logo = ImageIO.read(this.getClass().getResource("/resources/image/logo.png")); if (null != logo) { this.setIconImage(logo); } } catch (IOException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); } // ? this.setLocationRelativeTo(null); this.setVisible(true); } /** * This method is called from within the constructor to initialize the form. * WARNING: Do NOT modify this code. The content of this method is always * regenerated by the Form Editor. */ @SuppressWarnings("unchecked") // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents private void initComponents() { infoBottom = new javax.swing.JLabel(); doWithLabel = new javax.swing.JLabel(); fileName = new javax.swing.JLabel(); parseInfoPanel = new javax.swing.JScrollPane(); statusView = new javax.swing.JTextArea(); copyRight = new javax.swing.JLabel(); progressBar = new javax.swing.JProgressBar(); menuBar = new javax.swing.JMenuBar(); menuFile = new javax.swing.JMenu(); singleMonth = new javax.swing.JMenuItem(); wholeYear = new javax.swing.JMenuItem(); menuAbort = new javax.swing.JMenu(); menuItemInfo = new javax.swing.JMenuItem(); setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE); setName("mainFrame"); // NOI18N setResizable(false); infoBottom.setHorizontalAlignment(javax.swing.SwingConstants.CENTER); infoBottom.setText("?@?18?HR"); doWithLabel.setText("?:"); statusView.setEditable(false); statusView.setColumns(20); statusView.setRows(5); parseInfoPanel.setViewportView(statusView); copyRight.setHorizontalAlignment(javax.swing.SwingConstants.CENTER); copyRight.setText("Copyright 2016 Dev QiuYu. All rights reserved."); menuFile.setText(""); singleMonth.setAccelerator(javax.swing.KeyStroke.getKeyStroke(java.awt.event.KeyEvent.VK_F, java.awt.event.InputEvent.ALT_MASK)); singleMonth.setIcon(copyRight.getIcon()); singleMonth.setText("?"); singleMonth.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { singleMonthActionPerformed(evt); } }); menuFile.add(singleMonth); wholeYear.setAccelerator(javax.swing.KeyStroke.getKeyStroke(java.awt.event.KeyEvent.VK_A, java.awt.event.InputEvent.ALT_MASK)); wholeYear.setText(""); wholeYear.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { wholeYearActionPerformed(evt); } }); menuFile.add(wholeYear); menuBar.add(menuFile); menuAbort.setText(""); menuItemInfo.setAccelerator(javax.swing.KeyStroke.getKeyStroke(java.awt.event.KeyEvent.VK_I, java.awt.event.InputEvent.ALT_MASK)); menuItemInfo.setText("?"); menuItemInfo.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { menuItemInfoActionPerformed(evt); } }); menuAbort.add(menuItemInfo); menuBar.add(menuAbort); setJMenuBar(menuBar); javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane()); getContentPane().setLayout(layout); layout.setHorizontalGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addComponent(copyRight, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addComponent(infoBottom, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addGroup(layout.createSequentialGroup().addGap(31, 31, 31).addGroup(layout .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false) .addComponent(parseInfoPanel, javax.swing.GroupLayout.DEFAULT_SIZE, 441, Short.MAX_VALUE) .addGroup(layout.createSequentialGroup().addComponent(doWithLabel) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addComponent(fileName, javax.swing.GroupLayout.PREFERRED_SIZE, 343, javax.swing.GroupLayout.PREFERRED_SIZE)) .addComponent(progressBar, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)) .addGap(0, 31, Short.MAX_VALUE))); layout.setVerticalGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING).addGroup( javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup().addContainerGap() .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false) .addComponent(doWithLabel, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addComponent(fileName, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addComponent(progressBar, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addComponent(parseInfoPanel, javax.swing.GroupLayout.PREFERRED_SIZE, 217, javax.swing.GroupLayout.PREFERRED_SIZE) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addComponent(copyRight, javax.swing.GroupLayout.PREFERRED_SIZE, 15, javax.swing.GroupLayout.PREFERRED_SIZE) .addGap(4, 4, 4).addComponent(infoBottom).addContainerGap())); pack(); }// </editor-fold>//GEN-END:initComponents /** * ?? * @param process ? */ private void addBar(final int process) { new Thread(new Runnable() { @Override public void run() { int value = progressBar.getValue(); // ????? progressBar.setValue(value + process); DecimalFormat df = new DecimalFormat("#0.00"); progressBar.setString("??:" + df.format(progressBar.getPercentComplete() * 100) + "%"); } }).start(); } /** * ???? * @param evt */ private void singleMonthActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_singleMonthActionPerformed new Thread(new Runnable() { @Override public void run() { // ?? JFileChooser fileChooser = new JFileChooser(); fileChooser.setAcceptAllFileFilterUsed(false); String[] saveType = { "xls", "xlsx" }; fileChooser.setFileFilter(new FileNameExtensionFilter("*.xls,*.xlsx", saveType)); int selectResult = fileChooser.showDialog(null, ""); if (selectResult == JFileChooser.APPROVE_OPTION) { File file = fileChooser.getSelectedFile(); if (null != file && file.exists()) { String selectedFileName = file.getName(); String regex = selectedFileName.split("\\.")[1]; if ("xlsx".equals(regex) || "xls".equals(regex)) { fileName.setText(selectedFileName); clearMessage(); addMessage("?:" + selectedFileName); addMessage("?:" + selectedFileName); if ("xls".equals(regex)) { parseExcel42003(file); } else if ("xlsx".equals(regex)) { parseExcel42007(file); } } else { alert("???,Excel!"); } } } } }).start(); }//GEN-LAST:event_singleMonthActionPerformed /** * ??? * @param evt */ private void wholeYearActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_wholeYearActionPerformed new Thread(new Runnable() { @Override public void run() { // ?? JFileChooser fileChooser = new JFileChooser(); fileChooser.setAcceptAllFileFilterUsed(false); fileChooser.setMultiSelectionEnabled(true); String[] saveType = { "xls", "xlsx" }; fileChooser.setFileFilter(new FileNameExtensionFilter("*.xls,*.xlsx", saveType)); int selectResult = fileChooser.showDialog(null, ""); if (selectResult == JFileChooser.APPROVE_OPTION) { File[] files = fileChooser.getSelectedFiles(); if (files != null) { clearMessage();// ? progressBar.setMaximum(files.length); Map<String, double[]> result = new HashMap<String, double[]>(); // ?? for (File file : files) { if (null != file && file.exists()) { String selectedFileName = file.getName(); String regex = selectedFileName.split("\\.")[1]; if ("xlsx".equals(regex) || "xls".equals(regex)) { fileName.setText(selectedFileName); addMessage("?:" + selectedFileName); addMessage("?"); if ("xls".equals(regex)) { result = conactPunchRecords(result, parseExcelYear42003(file)); } else if ("xlsx".equals(regex)) { result = conactPunchRecords(result, parseExcelYear42007(file)); } } else { alert("???,Excel!"); } } // ?? addBar(1); } // ?,?? File file = files[0]; String path = file.getAbsolutePath().substring(0, file.getAbsolutePath().lastIndexOf("\\")); // writeYearCountResult(result, path); } } } }).start(); }//GEN-LAST:event_wholeYearActionPerformed private void menuItemInfoActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_menuItemInfoActionPerformed abort.setVisible(true); }//GEN-LAST:event_menuItemInfoActionPerformed /** * Excel * @param year */ private void writeYearCountResult(Map<String, double[]> yearRecords, String path) { XSSFWorkbook resultWorkBook = new XSSFWorkbook(); XSSFSheet sheet = resultWorkBook.createSheet(); Set<String> userNumNames = yearRecords.keySet(); // String[] titles = { "?", "??", "()", "(?)", "?(?)" }; int col = 0;// XSSFRow titleRow = sheet.createRow(0); for (String title : titles) { titleRow.createCell(col).setCellValue(title); sheet.autoSizeColumn((short) (col)); col++; } int row = 1;// // for (String userStr : userNumNames) { XSSFRow recordRow = sheet.createRow(row); int innerCol = 0;// ? // ? String[] userInfos = userStr.split(":"); for (String userInfo : userInfos) { recordRow.createCell(innerCol).setCellValue(userInfo); innerCol++; } // double[] records = yearRecords.get(userStr); for (double record : records) { recordRow.createCell(innerCol).setCellValue(record); innerCol++; } // ? recordRow.createCell(innerCol) .setCellValue(new BigDecimal(records[0] == 0 ? 0 : records[1] / records[0]) .setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue()); row++; } // ???,?? JFileChooser fileSaveChooser = new JFileChooser(); fileSaveChooser.setDialogTitle("?"); fileSaveChooser.setSelectedFile(new File(path + "/.xlsx")); String[] saveType = { "xlsx" }; fileSaveChooser.setAcceptAllFileFilterUsed(false); fileSaveChooser.setFileFilter(new FileNameExtensionFilter("*.xlsx", saveType)); int saveResult = fileSaveChooser.showSaveDialog(this); if (saveResult == JFileChooser.APPROVE_OPTION) { File saveFile = fileSaveChooser.getSelectedFile(); // ??? String saveFilePath = saveFile.getAbsolutePath(); addMessage("?,??->" + saveFilePath); FileOutputStream out = null; try { out = new FileOutputStream(saveFile); resultWorkBook.write(out); out.close(); } catch (FileNotFoundException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (out != null) { out.close(); } } catch (IOException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); } } Object[] options = { "", "", ",?" }; int response = JOptionPane.showOptionDialog(this, "??,???", "?", JOptionPane.YES_OPTION, JOptionPane.QUESTION_MESSAGE, null, options, options[0]); if (0 == response) {// // ?? addMessage(",??"); try { Desktop.getDesktop().open(saveFile); } catch (IOException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); } } else if (1 == response) {// addMessage(",??"); String[] cmd = new String[5]; cmd[0] = "cmd"; cmd[1] = "/c"; cmd[2] = "start"; cmd[3] = " "; cmd[4] = saveFile.getAbsolutePath().substring(0, saveFile.getAbsolutePath().lastIndexOf("\\")); try { Runtime.getRuntime().exec(cmd); } catch (IOException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); } } else { alert("??,?()"); } } else { // ??,? clearMessage(); fileName.setText(""); // ??? addMessage("??"); } } /** * ?? * @param year * @param singleMonth ? * @return ??? */ private Map<String, double[]> conactPunchRecords(Map<String, double[]> year, Map<String, double[]> singleMonth) { Set<String> keys = singleMonth.keySet(); for (String key : keys) { if (year.containsKey(key)) { double[] yearRecords = year.get(key); double[] monthRecords = singleMonth.get(key); for (int i = 0; i < yearRecords.length; i++) { yearRecords[i] = yearRecords[i] + monthRecords[i]; } year.put(key, yearRecords); } else { double[] monthRecords = singleMonth.get(key); double[] yearRecords = { monthRecords[0], monthRecords[1] }; year.put(key, yearRecords); } } return year; } /** * ?? * @param message ???? */ private void alert(String message) { JOptionPane.showMessageDialog(this, message); } /** * ,? * @param excelFile ??,2003-2007excel * @return ? */ private Map<String, double[]> parseExcelYear42003(File excelFile) { return parseExcel42003(excelFile, false); } /** * ,? * @param excelFile ??,2007+excel * @return ? */ private Map<String, double[]> parseExcelYear42007(File excelFile) { return parseExcel42007(excelFile, false); } /** * ??? * @param month * @param day * @return */ private boolean isWeekEnd(int year, int month, int day) { Calendar calendar = Calendar.getInstance(); calendar.set(year, month - 1, day); return calendar.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY || calendar.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY; } /** * ?excel(2007+),?? * @param excelFile ??Excel * @param single ?? */ private Map<String, double[]> parseExcel42007(File excelFile) { return parseExcel42007(excelFile, true); } /** * ?excel(2007+) * @param excelFile ??Excel * @param single ?? */ private Map<String, double[]> parseExcel42007(File excelFile, boolean single) { Map<String, double[]> result = new HashMap<String, double[]>(); try { // ?,?, File copyExcelFile = null; XSSFWorkbook copyWorkBook = null; if (single) {// ?? addMessage(""); copyExcelFile = new File( excelFile.getAbsolutePath().substring(0, excelFile.getAbsolutePath().lastIndexOf("\\")) + "/.xlsx"); FileUtils.copyFile(excelFile, copyExcelFile); // copyWorkBook = new XSSFWorkbook(new FileInputStream(copyExcelFile)); } // ? XSSFWorkbook workBook = new XSSFWorkbook(new FileInputStream(excelFile)); XSSFSheet sheet = workBook.getSheetAt(0); int rows = sheet.getLastRowNum(); if (rows >= 6) { // 6,??? int month = -1; // ? int year = -1;// ? if (single) {// ?? // ?3,? String dateStr = sheet.getRow(2).getCell(2).getStringCellValue(); if (StringUtils.isNotBlank(dateStr)) { addMessage("??:" + dateStr); String[] dates = dateStr.split("~"); month = Integer.parseInt(dates[0].split("\\/")[1]);// ?? year = Integer.parseInt(dates[0].split("\\/")[0]);// ?? } else { addMessage( "??,??,?"); } // ?,?? // ,?? int maxValue = (rows - 6) / 2; progressBar.setMaximum(maxValue); } int days = sheet.getRow(3).getLastCellNum(); // ? SimpleDateFormat punchFormat = new SimpleDateFormat("HH:mm"); if (single) {// ?? // ?,,,? String[] title = { "", "", "?" }; if (null != copyWorkBook) { for (int i = 0; i < title.length; i++) { copyWorkBook.getSheetAt(0).getRow(4).createCell(days + i).setCellValue(title[i]); XSSFCellStyle cellStyle = (XSSFCellStyle) copyWorkBook.getSheetAt(0).getRow(4) .getCell(0).getCellStyle().clone(); cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); copyWorkBook.getSheetAt(0).getRow(4).getCell(days + i).setCellStyle(cellStyle); copyWorkBook.getSheetAt(0).autoSizeColumn((short) (days + i)); } } } for (int i = 4; i < rows; i = i + 2) { // //,?,?+2 String userName = sheet.getRow(i).getCell(10).getStringCellValue();// ?? String userNum = sheet.getRow(i).getCell(2).getStringCellValue();// ? if (single) {// ?? addMessage("?:" + userName + "<?:" + userNum + ">"); // ?? addBar(1); } // ??,i+1 XSSFRow recordRow = sheet.getRow(i + 1); // double punchDays = 0; // (?),? double punchHours = 0, avgHours = 0; // ??? for (int j = 0; j < days; j++) {// ??? if (single) {// ?? // ?, // ?,??,?? if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { // , if (null != copyWorkBook) { XSSFCellStyle weekend = (XSSFCellStyle) copyWorkBook.getSheetAt(0) .getRow(i + 1).getCell(j).getCellStyle().clone(); weekend.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); weekend.setFillForegroundColor( new XSSFColor(new java.awt.Color(21, 225, 216))); //weekend.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j).setCellStyle(weekend); } } } } // ??? String record = recordRow.getCell(j).getStringCellValue();// ? if (StringUtils.isNotBlank(record)) {// ??,?? String[] records = record.split("\n"); // ???,,? if (records.length >= 2) { try { // ?start,?end,?ls,??le Date end = punchFormat.parse(records[records.length - 1]), start = punchFormat.parse(records[0]); Date ls = punchFormat.parse("11:40"), le = punchFormat.parse("13:00"); if (start.after(ls) && end.before(le)) { // ??,?? if (single) {// ?? // ?,??,?? if (null != copyWorkBook) { XSSFCellStyle excepitonStyle = (XSSFCellStyle) copyWorkBook .getSheetAt(0).getRow(i + 1).getCell(j).getCellStyle() .clone(); excepitonStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { // , excepitonStyle.setFillForegroundColor( IndexedColors.PINK.getIndex()); } else { excepitonStyle.setFillForegroundColor( IndexedColors.RED.getIndex()); } } copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j) .setCellStyle(excepitonStyle); } } } else {//??? punchDays = punchDays + 1; // ? long ms = end.getTime() - start.getTime();//???? // ??,???,? long mins = 75 * 60 * 1000;//?75 // ??,??? if (start.before(ls) && end.before(le)) { // ???? mins = end.getTime() - ls.getTime(); } // ??,??? if (start.after(ls) && end.after(le)) { // ???,?:??-? if (start.before(le)) { mins = le.getTime() - start.getTime(); } else if (start.after(ls)) { // ???,?0 mins = 0; } } ms = ms - mins;// ?? punchHours = punchHours + (double) ms / (3600 * 1000); // (?) } } catch (ParseException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); } } else {// ?, if (single) {// ?? // ?,??,?? if (null != copyWorkBook) { XSSFCellStyle excepitonStyle = (XSSFCellStyle) copyWorkBook.getSheetAt(0) .getRow(i + 1).getCell(j).getCellStyle().clone(); excepitonStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { // , excepitonStyle .setFillForegroundColor(IndexedColors.PINK.getIndex()); } else { excepitonStyle.setFillForegroundColor(IndexedColors.RED.getIndex()); } } copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j) .setCellStyle(excepitonStyle); } } } } } // ? if (punchDays > 0) { // ???? punchHours = new BigDecimal(punchHours).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue(); avgHours = new BigDecimal(punchHours / punchDays).setScale(1, BigDecimal.ROUND_HALF_UP) .doubleValue(); } double[] values = { punchDays, punchHours, avgHours }; result.put(userNum + ":" + userName, values); if (single) {// ?? addMessage(":" + userName + "<?:" + userNum + ">??,:" + "D:" + punchDays + ",H:" + punchHours + ",AH:" + avgHours); if (null != copyWorkBook) { for (int v = 0; v < values.length; v++) { copyWorkBook.getSheetAt(0).getRow(i + 1).createCell(days + v) .setCellValue(values[v]); XSSFCellStyle cellStyle = (XSSFCellStyle) copyWorkBook.getSheetAt(0).getRow(i + 1) .getCell(0).getCellStyle().clone(); cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(days + v).setCellStyle(cellStyle); } } } } if (single) {// ?? // ?? // ,? addMessage("?,??"); if (null != copyWorkBook) { FileOutputStream out = new FileOutputStream(copyExcelFile); copyWorkBook.write(out); out.close(); } // ???,?? JFileChooser fileSaveChooser = new JFileChooser(); fileSaveChooser.setDialogTitle("?"); fileSaveChooser.setSelectedFile(new File( excelFile.getAbsolutePath().substring(0, excelFile.getAbsolutePath().lastIndexOf(".")) + "-.xlsx")); String[] saveType = { "xlsx" }; fileSaveChooser.setAcceptAllFileFilterUsed(false); fileSaveChooser.setFileFilter(new FileNameExtensionFilter("*.xlsx", saveType)); int saveResult = fileSaveChooser.showSaveDialog(this); if (saveResult == JFileChooser.APPROVE_OPTION) { File saveFile = fileSaveChooser.getSelectedFile(); // ??? String saveFilePath = saveFile.getAbsolutePath(); addMessage("?,??->" + saveFilePath); FileUtils.copyFile(copyExcelFile, saveFile); Object[] options = { "", "", ",?" }; int response = JOptionPane.showOptionDialog(this, "??,???", "?", JOptionPane.YES_OPTION, JOptionPane.QUESTION_MESSAGE, null, options, options[0]); if (0 == response) {// // ?? addMessage(",??"); Desktop.getDesktop().open(saveFile); } else if (1 == response) {// addMessage(",??"); String[] cmd = new String[5]; cmd[0] = "cmd"; cmd[1] = "/c"; cmd[2] = "start"; cmd[3] = " "; cmd[4] = saveFile.getAbsolutePath().substring(0, saveFile.getAbsolutePath().lastIndexOf("\\")); Runtime.getRuntime().exec(cmd); } else { alert("??,?()"); } } else { // ??,? clearMessage(); fileName.setText(""); // ??? addMessage("??"); } // ??? if (null != copyExcelFile) { copyExcelFile.delete(); } } } else { // excel???,??????? alert("????!"); } } catch (FileNotFoundException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); alert(",??"); } catch (IOException | OfficeXmlFileException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); alert(":" + ex.getMessage()); } return result; } /** * ?excel(2003) * @param excelFile ??Excel * @param single ?? */ private Map<String, double[]> parseExcel42003(File excelFile) { return parseExcel42003(excelFile, true); } /** * ?excel(2003) * @param excelFile ??Excel * @param single ?? */ private Map<String, double[]> parseExcel42003(File excelFile, boolean single) { Map<String, double[]> result = new HashMap<String, double[]>(); try { File copyExcelFile = null; HSSFWorkbook copyWorkBook = null; if (single) {// ?? // ?,?, addMessage(""); copyExcelFile = new File( excelFile.getAbsolutePath().substring(0, excelFile.getAbsolutePath().lastIndexOf("\\")) + "/.xlsx"); FileUtils.copyFile(excelFile, copyExcelFile); // copyWorkBook = new HSSFWorkbook(new FileInputStream(copyExcelFile)); } // ? HSSFWorkbook workBook = new HSSFWorkbook(new FileInputStream(excelFile)); HSSFSheet sheet = workBook.getSheetAt(0); int rows = sheet.getLastRowNum(); if (rows >= 6) { // 6,??? // ?3,? String dateStr = sheet.getRow(2).getCell(2).getStringCellValue(); int month = -1; // ? int year = -1;// ? if (single) {// ??. if (StringUtils.isNotBlank(dateStr)) { addMessage("??:" + dateStr); String[] dates = dateStr.split("~"); month = Integer.parseInt(dates[0].split("\\/")[1]);// ?? year = Integer.parseInt(dates[0].split("\\/")[0]);// ?? } else { addMessage( "??,??,?"); } // ?,?? // ,?? int maxValue = (rows - 6) / 2; progressBar.setMaximum(maxValue); } int days = sheet.getRow(3).getLastCellNum(); // ? SimpleDateFormat punchFormat = new SimpleDateFormat("HH:mm"); // ?,,,? String[] title = { "", "", "?" }; if (single) {// ?? if (copyWorkBook != null) { for (int i = 0; i < title.length; i++) { copyWorkBook.getSheetAt(0).getRow(4).createCell(days + i).setCellValue(title[i]); HSSFCellStyle cellStyle = copyWorkBook.createCellStyle(); cellStyle .cloneStyleFrom(copyWorkBook.getSheetAt(0).getRow(4).getCell(0).getCellStyle()); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); copyWorkBook.getSheetAt(0).getRow(4).getCell(days + i).setCellStyle(cellStyle); copyWorkBook.getSheetAt(0).autoSizeColumn((short) (days + i)); } } } for (int i = 4; i < rows; i = i + 2) { // //,?,?+2 String userName = sheet.getRow(i).getCell(10).getStringCellValue();// ?? String userNum = sheet.getRow(i).getCell(2).getStringCellValue();// ? if (single) {// ?? addMessage("?:" + userName + "<?:" + userNum + ">"); // ?? addBar(1); } // ??,i+1 HSSFRow recordRow = sheet.getRow(i + 1); // double punchDays = 0; // (?),? double punchHours = 0, avgHours = 0; // ??? for (int j = 0; j < days; j++) {// ??? if (single) {// ?? // ?, // ?,??,?? if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { if (copyWorkBook != null) { // , HSSFCellStyle weekend = copyWorkBook.createCellStyle(); weekend.cloneStyleFrom( copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j).getCellStyle()); weekend.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); HSSFPalette paltette = copyWorkBook.getCustomPalette(); byte[] color = { (byte) (0xff & 21), (byte) (0xff & 225), (byte) (0xff & 216) }; paltette.setColorAtIndex((short) 9, color[0], color[1], color[2]); weekend.setFillForegroundColor((short) 9); copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j).setCellStyle(weekend); } } } } // ??? String record = recordRow.getCell(j).getStringCellValue();// ? if (StringUtils.isNotBlank(record)) {// ??,?? String[] records = record.split("\n"); // ???,,? if (records.length >= 2) { try { // ?start,?end,?ls,??le Date end = punchFormat.parse(records[records.length - 1]), start = punchFormat.parse(records[0]); Date ls = punchFormat.parse("11:40"), le = punchFormat.parse("13:00"); // ?:??? if (start.after(ls) && end.before(le)) { // ? if (single) {// ?? if (null != copyWorkBook) { // ?,??,?? HSSFCellStyle excepitonStyle = copyWorkBook.createCellStyle(); excepitonStyle.cloneStyleFrom(copyWorkBook.getSheetAt(0) .getRow(i + 1).getCell(j).getCellStyle()); excepitonStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { // , excepitonStyle.setFillForegroundColor( IndexedColors.PINK.getIndex()); } else { excepitonStyle.setFillForegroundColor( IndexedColors.RED.getIndex()); } } copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j) .setCellStyle(excepitonStyle); } } } else { //??? punchDays = punchDays + 1; // ? long ms = end.getTime() - start.getTime();//???? // ??,???,? long mins = 75 * 60 * 1000;//?75 // ??,??? if (start.before(ls) && end.before(le)) { // ???? mins = end.getTime() - ls.getTime(); } // ??,??? if (start.after(ls) && end.after(le)) { // ???,?:??-? if (start.before(le)) { mins = le.getTime() - start.getTime(); } else if (start.after(ls)) { // ???,?0 mins = 0; } } ms = ms - mins;// ?? punchHours = punchHours + (double) ms / (3600 * 1000); // (?) } } catch (ParseException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); } } else {// ?, if (single) {// ?? if (null != copyWorkBook) { // ?,??,?? HSSFCellStyle excepitonStyle = copyWorkBook.createCellStyle(); excepitonStyle.cloneStyleFrom( copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j).getCellStyle()); excepitonStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { // , excepitonStyle .setFillForegroundColor(IndexedColors.PINK.getIndex()); } else { excepitonStyle.setFillForegroundColor(IndexedColors.RED.getIndex()); } } copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j) .setCellStyle(excepitonStyle); } } } } } // ? if (punchDays > 0) { // ???? punchHours = new BigDecimal(punchHours).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue(); avgHours = new BigDecimal(punchHours / punchDays).setScale(1, BigDecimal.ROUND_HALF_UP) .doubleValue(); } double[] values = { punchDays, punchHours, avgHours }; result.put(userNum + ":" + userName, values); if (single) {// ?? addMessage(":" + userName + "<?:" + userNum + ">??,:" + "D:" + punchDays + ",H:" + punchHours + ",AH:" + avgHours); if (copyWorkBook != null) { for (int v = 0; v < values.length; v++) { copyWorkBook.getSheetAt(0).getRow(i + 1).createCell(days + v) .setCellValue(values[v]); HSSFCellStyle cellStyle = copyWorkBook.createCellStyle(); cellStyle.cloneStyleFrom( copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(0).getCellStyle()); cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(days + v).setCellStyle(cellStyle); } } } } if (single) {// ?? // ?? // ,? addMessage("?,??"); if (copyWorkBook != null) { FileOutputStream out = new FileOutputStream(copyExcelFile); copyWorkBook.write(out); out.close(); } // ???,?? JFileChooser fileSaveChooser = new JFileChooser(); fileSaveChooser.setDialogTitle("?"); fileSaveChooser.setSelectedFile(new File( excelFile.getAbsolutePath().substring(0, excelFile.getAbsolutePath().lastIndexOf(".")) + "-.xls")); String[] saveType = { "xls" }; fileSaveChooser.setAcceptAllFileFilterUsed(false); fileSaveChooser.setFileFilter(new FileNameExtensionFilter("*.xls", saveType)); int saveResult = fileSaveChooser.showSaveDialog(this); if (saveResult == JFileChooser.APPROVE_OPTION) { File saveFile = fileSaveChooser.getSelectedFile(); // ??? String saveFilePath = saveFile.getAbsolutePath(); addMessage("?,??->" + saveFilePath); FileUtils.copyFile(copyExcelFile, saveFile); Object[] options = { "", "", ",?" }; int response = JOptionPane.showOptionDialog(this, "??,???", "?", JOptionPane.YES_OPTION, JOptionPane.QUESTION_MESSAGE, null, options, options[0]); if (0 == response) {// // ?? addMessage(",??"); Desktop.getDesktop().open(saveFile); } else if (1 == response) {// addMessage(",??"); String[] cmd = new String[5]; cmd[0] = "cmd"; cmd[1] = "/c"; cmd[2] = "start"; cmd[3] = " "; cmd[4] = saveFile.getAbsolutePath().substring(0, saveFile.getAbsolutePath().lastIndexOf("\\")); Runtime.getRuntime().exec(cmd); } else { alert("??,?()"); } } else { // ??,? clearMessage(); fileName.setText(""); // ??? addMessage("??"); } // ??? if (copyExcelFile != null) { copyExcelFile.delete(); } } } else { // excel???,??????? alert("????!"); } } catch (FileNotFoundException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); alert(",??"); } catch (IOException | OfficeXmlFileException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); alert(":" + ex.getMessage()); } return result; } /** * ?? * @param message ? */ private void addMessage(String message) { System.out.print(message + "..."); //String originalText = statusView.getText(); //statusView.setText(originalText+message+"...\r\n"); } /** * ?? */ private void clearMessage() { printStream.clear(); statusView.setText(""); progressBar.setValue(0); progressBar.setString("?"); } /** * ?, */ public static void instance() { /* Set the Nimbus look and feel */ //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) "> /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel. * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html */ try { for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) { if ("Nimbus".equals(info.getName())) { javax.swing.UIManager.setLookAndFeel(info.getClassName()); break; } } } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | javax.swing.UnsupportedLookAndFeelException ex) { java.util.logging.Logger.getLogger(PunchCardRecordsMainFrame.class.getName()) .log(java.util.logging.Level.SEVERE, null, ex); } //</editor-fold> /* Create and display the form */ java.awt.EventQueue.invokeLater(new Runnable() { @Override public void run() { new PunchCardRecordsMainFrame().init(); } }); } // Variables declaration - do not modify//GEN-BEGIN:variables private javax.swing.JLabel copyRight; private javax.swing.JLabel doWithLabel; private javax.swing.JLabel fileName; private javax.swing.JLabel infoBottom; private javax.swing.JMenu menuAbort; private javax.swing.JMenuBar menuBar; private javax.swing.JMenu menuFile; private javax.swing.JMenuItem menuItemInfo; private javax.swing.JScrollPane parseInfoPanel; private javax.swing.JProgressBar progressBar; private javax.swing.JMenuItem singleMonth; private javax.swing.JTextArea statusView; private javax.swing.JMenuItem wholeYear; // End of variables declaration//GEN-END:variables private CommonPrintStream printStream; private Abort abort; }