ExcelUtils.java
3.47 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
package com.sincere.student.utils;
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.FileInputStream;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtils {
public static InputStream GetFileInputStream(String fileUrl){
try{
URL url = new URL(fileUrl);
HttpURLConnection conn = (HttpURLConnection)url.openConnection();
//设置超时间为3秒
conn.setConnectTimeout(8*1000);
//得到输入流
InputStream inputStream = conn.getInputStream();
return inputStream ;
}catch (Exception e){
}
return null ;
}
public static List<Point> analysisExcel(String url) throws ResultException{
try{
File excelFile = new File(url);
// 获得工作簿
String file = excelFile.getName();
Workbook workbook = null;
//InputStream inputStream = GetFileInputStream(url);
InputStream inputStream = new FileInputStream(url);
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){
e.printStackTrace();
throw new ResultException(999,"系统错误");
}
}
}