依赖项
1
2
3
4
5
| dependencies {
compile group: 'org.apache.poi', name: 'poi', version: '4.1.2'
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.2'
compile group: 'org.apache.poi', name: 'poi-ooxml-schemas', version: '4.1.2'
}
|
常用操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
var workbook = new XSSFWorkbook(Main.class.getResourceAsStream(String.format("/%s.xlsx", fileName)));
var sheet = workbook.getSheet("sheet1");
var row = sheet.getRow(0);
var cell = row.getCell(0);
for (var row : sheet) {
for (var cell : row) {
}
}
double val = cell.getNumericCellValue());
String str = cell.getStringCellValue();
var formatter = new DecimalFormat("#");
String str = formatter.format(cell.getNumericCellValue());
sheet.removeRow(row);
sheet.shiftRows(start, end, -1);
sheet.removeRow(row);
sheet.shiftRows(start, end, -1);
try (var os = new FileOutputStream(new File("**********"))) {
workbook.write(os);
}
|
实战演练
任务一
接触Apache POI不是心血来潮，不过是主人的任务罢了。
导员给我的任务是：筛选出成绩表中由导员负责的并且挂科超过3门的同学。
已有的表格：计科全体学生各科成绩表、软工全体学生各科成绩表、物联网全体学生各科成绩表、导员负责的同学的名单
表格具体内容涉及隐私就不放了，核心代码如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
| public class Main {
public static void main(String[] args) throws IOException {
output("计科");
output("软工");
output("物联网");
}
private static void output(String fileName) throws IOException {
System.out.println("---------------------------------- 优雅的分隔符 ----------------------------------");
System.out.println(fileName + ":");
try (var workbook = new XSSFWorkbook(Main.class.getResourceAsStream(String.format("/%s.xlsx", fileName)))) {
var sheet = workbook.getSheet("sheet1");
var courses = sheet.getRow(0);
List<List<String>> students = new ArrayList<>();
var studentsOfYue = studentOfYue();
for (var row : sheet) {
if (row.getRowNum() == 0 || row.getRowNum() == 1) {
continue;
}
String id = row.getCell(1).toString();
if (!studentsOfYue.contains(id)) {
continue;
}
int cnt = 0;
List<String> student = new ArrayList<>();
for (var cell : row) {
switch (cell.getCellType()) {
case STRING -> student.add(cell.getStringCellValue());
case NUMERIC -> {
if (cell.getNumericCellValue() < 60) {
cnt++;
student.add(getCourseName(cell.getColumnIndex(), courses) + ":" + cell.getNumericCellValue());
}
}
}
}
if (cnt >= 3) {
students.add(student);
}
}
for (var student : students) {
System.out.println(student);
}
}
}
private static Set<String> studentOfYue() throws IOException {
try (var workbook = new XSSFWorkbook(Main.class.getResourceAsStream("/月.xlsx"))) {
var sheet = workbook.getSheet("总表");
Set<String> set = new HashSet<>();
for (var row : sheet) {
if (row.getRowNum() == 0) {
continue;
}
var cell = row.getCell(2);
var formatter = new DecimalFormat("#");
switch (cell.getCellType()) {
case STRING -> set.add(cell.getStringCellValue());
case NUMERIC -> set.add(formatter.format(cell.getNumericCellValue()));
}
}
return set;
}
}
private static String getCourseName(int index, XSSFRow courses) {
var name = courses.getCell(index).getStringCellValue();
if (name.isBlank()) {
return getCourseName(index - 1, courses);
} else {
return name;
}
}
}
|
运行结果如下：
效果差强人意。
任务二
导员的任务是：筛选出表中导员负责的同学的得分情况（移除其它同学的得分情况）
已有的表格：各个学院和书院同学的得分情况、导员负责的同学的名单
核心代码如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
| package eternal.fire;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
public class Main {
public static void main(String[] args) throws IOException {
try (var workbook = new XSSFWorkbook(Main.class.getResourceAsStream("/学宪法.xlsx"))) {
var sheet = workbook.getSheet("***");
var studentsOfYue = studentsNameOfYue();
List<Row> rowsToDel = new ArrayList<>();
for (var row : sheet) {
if (row.getRowNum() == 0 || row.getRowNum() == 1) {
continue;
}
var name = row.getCell(1).getStringCellValue();
if (!studentsOfYue.contains(name)) {
rowsToDel.add(row);
}
}
for (var row : rowsToDel) {
sheet.removeRow(row);
}
removeEmptyRow(sheet);
try (var os = new FileOutputStream(new File("***********"))) {
workbook.write(os);
}
}
}
private static Set<String> studentsNameOfYue() throws IOException {
try (var workbook = new XSSFWorkbook(Main.class.getResourceAsStream("/月.xlsx"))) {
var sheet = workbook.getSheet("总表");
Set<String> set = new HashSet<>();
for (var row : sheet) {
if (row.getRowNum() == 0) {
continue;
}
var cell = row.getCell(1);
set.add(cell.getStringCellValue());
}
return set;
}
}
private static Set<String> studentsIdOfYue() throws IOException {
try (var workbook = new XSSFWorkbook(Main.class.getResourceAsStream("/月.xlsx"))) {
var sheet = workbook.getSheet("总表");
Set<String> set = new HashSet<>();
for (var row : sheet) {
if (row.getRowNum() == 0) {
continue;
}
var cell = row.getCell(2);
var formatter = new DecimalFormat("#");
set.add(formatter.format(cell.getNumericCellValue()));
}
return set;
}
}
private static void removeEmptyRow(XSSFSheet sheet) {
int lastRowNum = sheet.getLastRowNum();
for (int i = lastRowNum - 1; i >= 0; i--) {
var row = sheet.getRow(i);
if (isEmptyRow(row)) {
sheet.shiftRows(i + 1, lastRowNum, -1);
}
}
}
private static boolean isEmptyRow(XSSFRow row) {
if (row == null) {
return true;
}
for (var cell : row) {
if (cell != null && cell.getCellType() != CellType.BLANK) {
return false;
}
}
return true;
}
}
|
运行结果：
不贴了
To be continued…