package cn.cnnic.ops; import java.awt.Button; import java.awt.FileDialog; import java.awt.FlowLayout; import java.awt.Panel; import java.awt.TextArea; import java.awt.TextField; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.Collections; import java.util.Comparator; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Set; import java.util.Map.Entry; import javax.swing.JFrame; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; /** * * @author zhzhang * 实现根据排班表，计算每个人监控室值班的个数（分白班和夜班） */ public class DutyRosterVerificationFrame { public static void main(String[] args) { /** * 初始化JFrame */ final JFrame jframe = new JFrame("值班表数据汇总"); /** * 设置布局方式 */ Panel flowLayoutPanel = new Panel(); flowLayoutPanel.setLayout(new FlowLayout()); /** * 需要计算的文件位置和名称 */ final TextField tfFileName = new TextField(); tfFileName.setColumns(50); tfFileName.setText("此处显示选定的排班表"); tfFileName.setEditable(true); /** * 计算结果：每个人监控室值班的个数（分白班和夜班） */ final TextArea taResult = new TextArea(); taResult.setLocation(50, 50); taResult.setSize(500, 500); taResult.setText("此处显示白班和夜班的个数"); Button btnOpen = new Button("Open"); /** * 按钮打开事件 */ btnOpen.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { FileDialog fd = new FileDialog(jframe,"打开文件",FileDialog.LOAD); fd.setVisible(true); String fileName = fd.getDirectory()+fd.getFile(); tfFileName.setText(fileName); } }); /** * 打开按钮的大小 */ btnOpen.setSize(100, 50); /** * 打开按钮的位置 */ btnOpen.setLocation(100, 100); /** * 初始化计算按钮 */ Button btnCal = new Button("Calculate"); btnCal.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { try { String strResult = getDataFromExcel(tfFileName.getText().toString()); taResult.setText(strResult); } catch (InvalidFormatException | IOException e1) { e1.printStackTrace(); } } }); flowLayoutPanel.add(btnOpen); flowLayoutPanel.add(tfFileName); flowLayoutPanel.add(btnCal); jframe.add(taResult); jframe.add(flowLayoutPanel); jframe.setSize(600, 600); jframe.setLocation(100, 100); jframe.setVisible(true); } /** * * @param file * @return 计算结果 * @throws FileNotFoundException * @throws IOException * @throws InvalidFormatException */ public static String getDataFromExcel(String file) throws FileNotFoundException, IOException, InvalidFormatException { InputStream ins = null; Workbook wb = null; ins = new FileInputStream(new File(file)); wb = WorkbookFactory.create(ins); ins.close(); Sheet sheet = wb.getSheetAt(0); int rowNum = sheet.getLastRowNum(); Map<String, String> dutyMap = new HashMap<String, String>(); dutyMap.put("1", "***"); dutyMap.put("2", "***"); dutyMap.put("3", "***"); dutyMap.put("4", "***"); dutyMap.put("5", "***"); dutyMap.put("6", "***"); dutyMap.put("7", "***"); Map<String, Integer> dayShift = new HashMap<String, Integer>(); Map<String, Integer> nightShift = new HashMap<String, Integer>(); // System.out.println(sheet.getRow(3).getCell(2).toString().split(" ")[0]); for (int rowIndex = 0; rowIndex <= rowNum; rowIndex++) { Row rowCurrent = sheet.getRow(rowIndex); if (rowIndex >= 3 && (rowIndex - 3) % 4 == 0) { for (int colIndex = 2; colIndex <= 8; colIndex++) { Cell cellCurrent = rowCurrent.getCell(colIndex); if (cellCurrent != null) { cellCurrent.setCellType(Cell.CELL_TYPE_STRING); } String team = cellCurrent.toString().trim().split(" ")[0]; String[] teamPerson = team.split(","); for (int teamIndex = 0; teamIndex < teamPerson.length; teamIndex++) { if (dayShift.get(teamPerson[teamIndex]) == null) { dayShift.put(teamPerson[teamIndex], 1); } else { dayShift.put(teamPerson[teamIndex], dayShift.get(teamPerson[teamIndex]) + 1); } } } } else if (rowIndex >= 4 && (rowIndex - 4) % 4 == 0) { for (int colIndex = 2; colIndex <= 8; colIndex++) { Cell cellCurrent = rowCurrent.getCell(colIndex); if (cellCurrent != null) { cellCurrent.setCellType(Cell.CELL_TYPE_STRING); } String team = cellCurrent.toString().trim().split(" ")[0]; String[] teamPerson = team.split(","); for (int teamIndex = 0; teamIndex < teamPerson.length; teamIndex++) { if (nightShift.get(teamPerson[teamIndex]) == null) { nightShift.put(teamPerson[teamIndex], 1); } else { nightShift.put(teamPerson[teamIndex], nightShift.get(teamPerson[teamIndex]) + 1); } } } } } return outputSortReturn("白班", dayShift, dutyMap)+outputSortReturn("夜班", nightShift, dutyMap);// } /** * * @param str * 说明白班还是夜班 * @param map * 员工及值班个数HashMap * @param mapDim * 值班维表 * @return * 计算结果 */ public static String outputSortReturn(String str, Map<String, Integer> map, Map<String, String> mapDim) { List<Map.Entry<String, Integer>> list = new ArrayList<Map.Entry<String, Integer>>(map.entrySet()); Collections.sort(list, new Comparator<Map.Entry<String, Integer>>() { @Override public int compare(Entry<String, Integer> o1, Entry<String, Integer> o2) { return -(o1.getValue() - o2.getValue()); } }); String strResult = "===================" + str + "======================"+" "; strResult += "代号 姓名 数量 "; for (int index = 0; index < list.size(); index++) { strResult += list.get(index).getKey() + " " + mapDim.get(list.get(index).getKey()) + " " + list.get(index).getValue()+" "; } return strResult; } /** * * @param str * 说明白班还是夜班 * @param map * 员工及值班个数HashMap * @param mapDim * 值班维表 */ public static void outputSort(String str, Map<String, Integer> map, Map<String, String> mapDim) { List<Map.Entry<String, Integer>> list = new ArrayList<Map.Entry<String, Integer>>(map.entrySet()); Collections.sort(list, new Comparator<Map.Entry<String, Integer>>() { @Override public int compare(Entry<String, Integer> o1, Entry<String, Integer> o2) { return -(o1.getValue() - o2.getValue()); } }); System.out.println("===================" + str + "======================"); for (int index = 0; index < list.size(); index++) { System.out.println(list.get(index).getKey() + "==" + mapDim.get(list.get(index).getKey()) + "==" + list.get(index).getValue()); } } /** * * @param str * 说明白班还是夜班 * @param map * 员工及值班个数HashMap * @param mapDim * 值班维表 */ public static void output(String str, Map<String, Integer> map, Map<String, String> mapDim) { System.out.println("===================" + str + "======================"); Set<Entry<String, Integer>> dayEntities = map.entrySet(); for (Entry<String, Integer> en : dayEntities) { System.out.println(en.getKey() + "---" + mapDim.get(en.getKey()) + "---" + en.getValue()); } } }