ExcelUtils.java
5.07 KB
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
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
package com.sincere.student.utils;
import com.aliyun.oss.OSSClient;
import com.aliyun.oss.model.OSSObject;
import com.sincere.student.model.Point;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtils {
public static OSSClient ossClient = null;
private static final String key = "QiuM3PwHTnVotcGy";
private static final String secret = "Yqs7RlaC1MioZu2YYJ6u0TdeO13VFC";
private static final String endPoint = "http://oss-cn-hangzhou.aliyuncs.com";
private static final String bucketName = "szyundisk";
private static final String domain = "https://szyundisk.oss-cn-hangzhou.aliyuncs.com/";
public static synchronized OSSClient getInstance() {
if (ossClient == null) {
synchronized (ExcelUtils.class) {
if (ossClient == null) {
ossClient = new OSSClient(endPoint, key, secret);
}
}
}
return ossClient;
}
public static List<Point> analysisExcel(String url) throws ResultException {
InputStream inputStream = null;
OSSClient ossClient = getInstance();
OSSObject object = ossClient.getObject(bucketName, url.replace(domain, ""));
try {
File excelFile = new File(url);
// 获得工作簿
String file = excelFile.getName();
Workbook workbook = null;
inputStream = object.getObjectContent();
if (inputStream == null) {
throw new ResultException(901, "路径错误");
}
if (file.endsWith("xls")) {
workbook = new HSSFWorkbook(inputStream);
} else {
workbook = new XSSFWorkbook(inputStream);
}
// 获得工作表
Sheet sheet = workbook.getSheetAt(0);
int rows = sheet.getPhysicalNumberOfRows();
List<Point> list = new ArrayList<>();
for (int i = 1; i <= rows; i++) {
// 获取第i行数据
Row sheetRow = sheet.getRow(i);
if (sheetRow != null) {
Point point = new Point();
Cell cell1 = sheetRow.getCell(0);
if (cell1 != null) {
cell1.setCellType(CellType.STRING);
point.setUniversityName(cell1.getStringCellValue().trim());
}
Cell cell2 = sheetRow.getCell(1);
if (cell2 != null) {
cell2.setCellType(CellType.STRING);
point.setMajor(cell2.getStringCellValue().trim());
}
Cell cell3 = sheetRow.getCell(2);
if (cell3 != null) {
cell3.setCellType(CellType.STRING);
if(StringUtils.isNotBlank(cell3.getStringCellValue())){
point.setEnrollNumber(Integer.valueOf(cell3.getStringCellValue().trim()));
}else{
point.setEnrollNumber(null);
}
}
Cell cell4 = sheetRow.getCell(3);
if (cell4 != null) {
cell4.setCellType(CellType.STRING);
if(StringUtils.isNotBlank(cell4.getStringCellValue())){
point.setGrade(Integer.valueOf(cell4.getStringCellValue().trim()));
}else{
point.setGrade(null);
}
}
Cell cell5 = sheetRow.getCell(4);
if (cell5 != null) {
cell5.setCellType(CellType.STRING);
if(StringUtils.isNotBlank(cell5.getStringCellValue())){
point.setRank(Integer.valueOf(cell5.getStringCellValue().trim()));
}else{
point.setRank(null);
}
}
Cell cell6 = sheetRow.getCell(5);
if (cell6 != null) {
cell6.setCellType(CellType.STRING);
point.setProvince(cell6.getStringCellValue().trim());
}
Cell cell7 = sheetRow.getCell(6);
if (cell7 != null) {
cell7.setCellType(CellType.STRING);
point.setCity(cell7.getStringCellValue());
}
list.add(point);
}
}
return list;
} catch (Exception e) {
e.printStackTrace();
throw new ResultException(999, "系统错误");
} finally {
try {
inputStream.close();
object.close();
} catch (Exception e) {
}
}
}
}