Excel读取
- 多个excel,根据某一行,读取到后,然后(列)向后 ,偏移移动读取
- 最后排序打印出来
@Data
public class TestExpOut {
@PropertiesName(name = "消息1")
private String msg1;
@PropertiesName(name = "消息2")
private String msg2;
}
public static void main(String[] args) throws Exception {
TreeMap<String, TestExpOut> treeMap = new TreeMap<>((o1, o2) -> {
String s1 = o1.substring(0, 4);
String s2 = o1.substring(5).replace("月", "");
Integer tmp1 = new Integer(s1) * 100 + new Integer(s2);
String s3 = o2.substring(0, 4);
String s4 = o2.substring(5).replace("月", "");
Integer tmp2 = new Integer(s3) * 100 + new Integer(s4);
return tmp2 - tmp1;
});
File file = new File("D:\\test");
File[] files = file.listFiles();
for (int x = 0; x < files.length; x++) {
if (files[x].isDirectory()) {
continue;
}
String fileName = files[x].getName();
String endFileName = fileName.substring(2, fileName.indexOf("份"));
System.out.println("本次读取的文件是" + endFileName);
setMyPutData(treeMap, endFileName, files[x]);
}
Set<String> treeMaps = treeMap.keySet();
for (String key : treeMaps) {
System.out.println(key + "==" + treeMap.get(key));
}
}
private static void setMyPutData(TreeMap treeMap, String myName, File x) throws Exception {
Workbook workbook = WorkbookFactory.create(x);
Sheet sheet = workbook.getSheetAt(1);
int rowNum = sheet.getLastRowNum();
int j = 2;
Row titleRow = sheet.getRow(3);
for (int i = 5; i <= rowNum; i++) {
try {
String littleDate = sheet.getRow(i).getCell(j).toString().trim();
System.out.println(littleDate);
if (StringUtils.isBlank(littleDate)) {
continue;
}
if (littleDate.equalsIgnoreCase("test")) {
TestExpOut out = new TestExpOut();
String msg1 = sheet.getRow(i).getCell(j + 3).toString();
out.setMsg1(titleRow.getCell(j + 3) + ":" + msg1);
String cellValue = getCellValue(sheet.getRow(i).getCell(j + 19));
out.setMsg2(titleRow.getCell(j + 19) + ":" + cellValue);
treeMap.put(myName, out);
return;
}
} catch (Exception e) {
TestExpOut out = new TestExpOut();
out.setMsg1("查无此人");
out.setMsg2("查无此人");
treeMap.put(myName, out);
return;
}
}
}
获取函数中的值
public static String getCellValue(Cell cell) {
String value = null;
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
try {
value = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException e) {
value = String.valueOf(cell.getRichStringCellValue());
}
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
value = String.valueOf(cell.getRichStringCellValue());
break;
}
}
return value;
}
public static String getCellValue(Cell cell) {
String cellValue;
switch (cell.getCellTypeEnum()) {
case NUMERIC:
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
} else {
DataFormatter dataFormatter = new DataFormatter();
cellValue = dataFormatter.formatCellValue(cell);
}
break;
case STRING:
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
cellValue = cell.getBooleanCellValue() + "";
break;
case FORMULA:
cellValue = cell.getCellFormula() + "";
break;
case BLANK:
cellValue = "";
break;
case ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
ArrayList和map自定义排序
@Data
public class TestExpOut{
private String date;
private String msg1;
private String msg2;
}
List<TestExpOut> list = new ArrayList();
Collections.sort(list, (a, b) -> {
String o1 = a.getDate();
String o2 = b.getDate();
String s1 = o1.substring(0, 4);
String s2 = o1.substring(5).replace("月", "");
Integer tmp1 = new Integer(s1) * 100 + new Integer(s2);
String s3 = o2.substring(0, 4);
String s4 = o2.substring(5).replace("月", "");
Integer tmp2 = new Integer(s3) * 100 + new Integer(s4);
return tmp1-tmp2;
});
public class Student implements Comparable{
private int id;
private int age;
private int height;
private String name;
@Override
public int compareTo(Object o) {
Student s = (Student) o;
if (this.age > s.age) {
return 1;
}
else if (this.age < s.age) {
return -1;
}
else {
if (this.height >= s.height) {
return 1;
}
else {
return -1;
}
}
}
}