读取excel数据，并进行统计输出

package cn.cnnic.ops;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.Map.Entry;
import java.util.Set;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;public class DutyRosterVerification { public static void main(String[] args) {
try {
// getDataFromExcel("D:\\soft\\eclipse\\workspace\\DutyRoster\\src\\20160726_2016年8月份值班表.xlsx");
getDataFromExcel("D:\\Data\\xxx.xlsx");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
} public static void 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("\n")[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("\n")[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("\n")[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);
}
}
}
}
} outputSort("白班", dayShift, dutyMap);
outputSort("夜班", nightShift, dutyMap);
} /**
*
* @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());
}
}
}