纯代码，慎入
读取excel表格
通过poi读取excel表格
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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
/**
* 操作Excel表格的功能类
* @author：
* @version 1.0
*/
public class ExcelReader {
private POIFSFileSystem fs;
private HSSFWorkbook wb;
private HSSFSheet sheet;
private HSSFRow row;
/**
* 读取Excel表格表头的内容
* @param InputStream
* @return String 表头内容的数组
*
*/
public String[] readExcelTitle(InputStream is) {
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
row = sheet.getRow(0);
//标题总列数
int colNum = row.getPhysicalNumberOfCells();
String[] title = new String[colNum];
for (int i=0; i<colNum; i++) {
title[i] = getStringCellValue(row.getCell((short) i));
}
return title;
}
/**
* 读取Excel数据内容
* @param InputStream
* @return Map 包含单元格数据内容的Map对象
*/
public Map<Integer,String> readExcelContent(InputStream is) {
Map<Integer,String> content = new HashMap<Integer,String>();
String str = "";
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
//得到总行数
int rowNum = sheet.getLastRowNum();
row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
//正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
while (j<colNum) {
//每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据
//也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean
str += getStringCellValue(row.getCell((short) j)).trim() + "-";
j ++;
}
content.put(i, str);
str = "";
}
return content;
}
/**
* 获取单元格数据内容为字符串类型的数据
* @param cell Excel单元格
* @return String 单元格数据内容
*/
private String getStringCellValue(HSSFCell cell) {
String strCell = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
if (cell == null) {
return "";
}
return strCell;
}
/**
* 获取单元格数据内容为日期类型的数据
* @param cell Excel单元格
* @return String 单元格数据内容
*/
private String getDateCellValue(HSSFCell cell) {
String result = "";
try {
int cellType = cell.getCellType();
if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
Date date = cell.getDateCellValue();
result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
+ "-" + date.getDate();
} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
String date = getStringCellValue(cell);
result = date.replaceAll("[年月]", "-").replace("日", "").trim();
} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
result = "";
}
} catch (Exception e) {
System.out.println("日期格式不正确!");
e.printStackTrace();
}
return result;
}
public static void main(String[] args) {
try {
//对读取Excel表格标题测试
InputStream is = new FileInputStream("C://Excel表格测试.xls");
ExcelReader excelReader = new ExcelReader();
String[] title = excelReader.readExcelTitle(is);
System.out.println("获得Excel表格的标题:");
for (String s : title) {
System.out.print(s + " ");
}
System.out.println();
//对读取Excel表格内容测试
InputStream is2 = new FileInputStream("C://Excel表格测试.xls");
Map<Integer,String> map = excelReader.readExcelContent(is2);
System.out.println("获得Excel表格的内容:");
for (int i=1; i<=map.size(); i++) {
System.out.println(map.get(i));
}
} catch (FileNotFoundException e) {
System.out.println("未找到指定路径的文件!");
e.printStackTrace();
}
}
}
欢迎我的公众号！
如果你觉得文章对你有帮助，可点击下面的打赏按钮，支持一下！
