ReadExcel.java
5.6 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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
package com.w1hd.zzhnc.util;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.impl.piccolo.io.FileFormatException;
import com.w1hd.zzhnc.vo.ExcelSales_Vo;
public class ReadExcel {
private static final String EXTENSION_XLS = "xls";
private static final String EXTENSION_XLSX = "xlsx";
/***
* <pre>
* 取得Workbook对象(xls和xlsx对象不同,不过都是Workbook的实现类)
* xls:HSSFWorkbook
* xlsx：XSSFWorkbook
* @return
* @throws IOException
* </pre>
*/
private Workbook getWorkbook(String suffix, InputStream is) throws IOException {
Workbook workbook = null;
try {
if (suffix.endsWith(EXTENSION_XLS)) {
workbook = new HSSFWorkbook(is);
} else if (suffix.endsWith(EXTENSION_XLSX)) {
workbook = new XSSFWorkbook(is);
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("read fail:"+e.getMessage());
}
return workbook;
}
/**
* 文件检查
*
* @param filePath
* @throws Exception
* @throws FileFormatException
*/
private boolean preReadCheck(String suffix) {
if (!(suffix.endsWith(EXTENSION_XLS) || suffix.endsWith(EXTENSION_XLSX))) {
return false;
}
return true;
}
public List<ExcelSales_Vo> readExcel(String suffix, InputStream is) {
Workbook workbook = null;
int row=0;
try {
if (!preReadCheck(suffix))
return null;
workbook = this.getWorkbook(suffix, is);
// 读文件 一个sheet一个sheet地读取
List<ExcelSales_Vo> list = new ArrayList<ExcelSales_Vo>();
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
Sheet sheet = workbook.getSheetAt(numSheet);
if (sheet == null || (sheet != null && sheet.getLastRowNum() == 0)) {
continue;
}
System.out.println("=======================" + sheet.getSheetName() + "=========================");
int firstRowIndex = sheet.getFirstRowNum();
int lastRowIndex = sheet.getLastRowNum();
// 读取首行 即,表头
Row firstRow = sheet.getRow(firstRowIndex+1);// 过滤掉表格第一行标题
for (int i = firstRow.getFirstCellNum(); i <firstRow.getLastCellNum(); i++) {
Cell cell = firstRow.getCell(i);
String cellValue = this.getCellValue(cell, true);
System.out.print("" + cellValue + "\t");
}
// 读取数据行
for (int rowIndex = firstRowIndex + 2; rowIndex <= lastRowIndex; rowIndex++) {
Row currentRow = sheet.getRow(rowIndex);// 当前行
int firstColumnIndex = currentRow.getFirstCellNum(); // 首列
int lastColumnIndex = currentRow.getLastCellNum();// 最后一列
ExcelSales_Vo sale = new ExcelSales_Vo();
row++;
for (int columnIndex = firstColumnIndex; columnIndex <= lastColumnIndex; columnIndex++) {
Cell currentCell = currentRow.getCell(columnIndex);// 当前单元格
String currentCellValue = this.getCellValue(currentCell, true);// 当前单元格的值
System.out.print(currentCellValue + "\t");
if (firstColumnIndex + 1 == columnIndex) {
sale.setProjectname(currentCellValue);
} else if (firstColumnIndex + 2 == columnIndex) {
sale.setName(currentCellValue);
} else if (firstColumnIndex + 3 == columnIndex) {
sale.setPhone(currentCellValue);
}
}
list.add(sale);
}
}
return list;
} catch (Exception e) {
e.printStackTrace();
System.out.println("excel导入失败：" + e.getMessage());
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return null;
}
/**
* 取单元格的值
*
* @param cell
* 单元格对象
* @param treatAsStr
* 为true时，当做文本来取值 (取到的是文本，不会把“1”取成“1.0”)
* @return
*/
@SuppressWarnings("deprecation")
private String getCellValue(Cell cell, boolean treatAsStr) {
if (cell == null) {
return "";
}
if (treatAsStr) {
cell.setCellType(Cell.CELL_TYPE_STRING);
}
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
} else {
return String.valueOf(cell.getStringCellValue());
}
}
}