ExcelUtils.java
3.92 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
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.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);
point.setEnrollNumber(Integer.valueOf(cell3.getStringCellValue().trim()));
}
Cell cell4 = sheetRow.getCell(3);
if(cell4 != null){
cell4.setCellType(CellType.STRING);
point.setGrade(Integer.valueOf(cell4.getStringCellValue().trim()));
}
Cell cell5 = sheetRow.getCell(4);
if(cell5 != null){
cell5.setCellType(CellType.STRING);
point.setRank(Integer.valueOf(cell5.getStringCellValue().trim()));
}
list.add(point);
}
}
return list ;
}catch (Exception e){
throw new ResultException(999,"系统错误");
}finally {
try{
inputStream.close();
object.close();
}catch (Exception e){
}
}
}
}