尊重原创 传送门先行: http://www.tangjinyi.com/index.php/archives/81
apache poi 读取excel 2003.2007
jar:
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
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
|
import
java
.
io
.
File
;
import
java
.
io
.
FileInputStream
;
import
java
.
io
.
FileNotFoundException
;
import
java
.
io
.
FileOutputStream
;
import
java
.
io
.
IOException
;
import
java
.
text
.
DecimalFormat
;
import
java
.
text
.
SimpleDateFormat
;
import
java
.
util
.
Date
;
import
java
.
util
.
LinkedList
;
import
java
.
util
.
List
;
import
org
.
apache
.
poi
.
hssf
.
usermodel
.
HSSFCell
;
import
org
.
apache
.
poi
.
hssf
.
usermodel
.
HSSFCellStyle
;
import
org
.
apache
.
poi
.
hssf
.
usermodel
.
HSSFDataFormat
;
import
org
.
apache
.
poi
.
hssf
.
usermodel
.
HSSFDateUtil
;
import
org
.
apache
.
poi
.
hssf
.
usermodel
.
HSSFFont
;
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
.
hssf
.
util
.
HSSFColor
;
import
org
.
apache
.
poi
.
xssf
.
usermodel
.
XSSFCell
;
import
org
.
apache
.
poi
.
xssf
.
usermodel
.
XSSFCellStyle
;
import
org
.
apache
.
poi
.
xssf
.
usermodel
.
XSSFFont
;
import
org
.
apache
.
poi
.
xssf
.
usermodel
.
XSSFRow
;
import
org
.
apache
.
poi
.
xssf
.
usermodel
.
XSSFSheet
;
import
org
.
apache
.
poi
.
xssf
.
usermodel
.
XSSFWorkbook
;
public
class
CreatAndReadExcel
{
public
static
void
main
(
String
[
]
args
)
throws
Exception
{
//读取2003Excel文件
String
path2003
=
System
.
getProperty
(
"user.dir"
)
+
System
.
getProperty
(
"file.separator"
)
+
"style_2003.xls"
;
// 获取项目文件路径+2003版文件名
System
.
out
.
println
(
"路径:"
+
path2003
)
;
File
f2003
=
new
File
(
path2003
)
;
try
{
readExcel
(
f2003
)
;
}
catch
(
IOException
e
)
{
// TODO Auto-generated catch block
e
.
printStackTrace
(
)
;
}
//读取2007Excel文件
String
path2007
=
System
.
getProperty
(
"user.dir"
)
+
System
.
getProperty
(
"file.separator"
)
+
"style_2007.xlsx"
;
// 获取项目文件路径+2007版文件名
System
.
out
.
println
(
"路径:"
+
path2007
)
;
File
f2007
=
new
File
(
path2007
)
;
try
{
readExcel
(
f2007
)
;
}
catch
(
IOException
e
)
{
e
.
printStackTrace
(
)
;
}
}
/**
* 创建2007版Excel文件
*
* @throws FileNotFoundException
* @throws IOException
*/
private
static
void
creat2007Excel
(
)
throws
FileNotFoundException
,
IOException
{
// HSSFWorkbook workBook = new HSSFWorkbook();// 创建 一个excel文档对象
XSSFWorkbook
workBook
=
new
XSSFWorkbook
(
)
;
XSSFSheet
sheet
=
workBook
.
createSheet
(
)
;
// 创建一个工作薄对象
sheet
.
setColumnWidth
(
1
,
10000
)
;
// 设置第二列的宽度为
XSSFRow
row
=
sheet
.
createRow
(
1
)
;
// 创建一个行对象
row
.
setHeightInPoints
(
23
)
;
// 设置行高23像素
XSSFCellStyle
style
=
workBook
.
createCellStyle
(
)
;
// 创建样式对象
// 设置字体
XSSFFont
font
=
workBook
.
createFont
(
)
;
// 创建字体对象
font
.
setFontHeightInPoints
(
(
short
)
15
)
;
// 设置字体大小
font
.
setBoldweight
(
HSSFFont
.
BOLDWEIGHT_BOLD
)
;
// 设置粗体
font
.
setFontName
(
"黑体"
)
;
// 设置为黑体字
style
.
setFont
(
font
)
;
// 将字体加入到样式对象
// 设置对齐方式
style
.
setAlignment
(
HSSFCellStyle
.
ALIGN_CENTER_SELECTION
)
;
// 水平居中
style
.
setVerticalAlignment
(
HSSFCellStyle
.
VERTICAL_CENTER
)
;
// 垂直居中
// 设置边框
style
.
setBorderTop
(
HSSFCellStyle
.
BORDER_THICK
)
;
// 顶部边框粗线
style
.
setTopBorderColor
(
HSSFColor
.
RED
.
index
)
;
// 设置为红色
style
.
setBorderBottom
(
HSSFCellStyle
.
BORDER_DOUBLE
)
;
// 底部边框双线
style
.
setBorderLeft
(
HSSFCellStyle
.
BORDER_MEDIUM
)
;
// 左边边框
style
.
setBorderRight
(
HSSFCellStyle
.
BORDER_MEDIUM
)
;
// 右边边框
// 格式化日期
style
.
setDataFormat
(
HSSFDataFormat
.
getBuiltinFormat
(
"m/d/yy h:mm"
)
)
;
XSSFCell
cell
=
row
.
createCell
(
1
)
;
// 创建单元格
cell
.
setCellValue
(
new
Date
(
)
)
;
// 写入当前日期
cell
.
setCellStyle
(
style
)
;
// 应用样式对象
// 文件输出流
FileOutputStream
os
=
new
FileOutputStream
(
"style_2007.xlsx"
)
;
workBook
.
write
(
os
)
;
// 将文档对象写入文件输出流
os
.
close
(
)
;
// 关闭文件输出流
System
.
out
.
println
(
"创建成功 office 2007 excel"
)
;
}
/**
* 创建2003版本的Excel文件
*/
private
static
void
creat2003Excel
(
)
throws
FileNotFoundException
,
IOException
{
HSSFWorkbook
workBook
=
new
HSSFWorkbook
(
)
;
// 创建 一个excel文档对象
HSSFSheet
sheet
=
workBook
.
createSheet
(
)
;
// 创建一个工作薄对象
sheet
.
setColumnWidth
(
1
,
10000
)
;
// 设置第二列的宽度为
HSSFRow
row
=
sheet
.
createRow
(
1
)
;
// 创建一个行对象
row
.
setHeightInPoints
(
23
)
;
// 设置行高23像素
HSSFCellStyle
style
=
workBook
.
createCellStyle
(
)
;
// 创建样式对象
// 设置字体
HSSFFont
font
=
workBook
.
createFont
(
)
;
// 创建字体对象
font
.
setFontHeightInPoints
(
(
short
)
15
)
;
// 设置字体大小
font
.
setBoldweight
(
HSSFFont
.
BOLDWEIGHT_BOLD
)
;
// 设置粗体
font
.
setFontName
(
"黑体"
)
;
// 设置为黑体字
style
.
setFont
(
font
)
;
// 将字体加入到样式对象
// 设置对齐方式
style
.
setAlignment
(
HSSFCellStyle
.
ALIGN_CENTER_SELECTION
)
;
// 水平居中
style
.
setVerticalAlignment
(
HSSFCellStyle
.
VERTICAL_CENTER
)
;
// 垂直居中
// 设置边框
style
.
setBorderTop
(
HSSFCellStyle
.
BORDER_THICK
)
;
// 顶部边框粗线
style
.
setTopBorderColor
(
HSSFColor
.
RED
.
index
)
;
// 设置为红色
style
.
setBorderBottom
(
HSSFCellStyle
.
BORDER_DOUBLE
)
;
// 底部边框双线
style
.
setBorderLeft
(
HSSFCellStyle
.
BORDER_MEDIUM
)
;
// 左边边框
style
.
setBorderRight
(
HSSFCellStyle
.
BORDER_MEDIUM
)
;
// 右边边框
// 格式化日期
style
.
setDataFormat
(
HSSFDataFormat
.
getBuiltinFormat
(
"m/d/yy h:mm"
)
)
;
HSSFCell
cell
=
row
.
createCell
(
1
)
;
// 创建单元格
cell
.
setCellValue
(
new
Date
(
)
)
;
// 写入当前日期
cell
.
setCellStyle
(
style
)
;
// 应用样式对象
// 文件输出流
FileOutputStream
os
=
new
FileOutputStream
(
"style_2003.xls"
)
;
workBook
.
write
(
os
)
;
// 将文档对象写入文件输出流
os
.
close
(
)
;
// 关闭文件输出流
System
.
out
.
println
(
"创建成功 office 2003 excel"
)
;
}
/**
* 对外提供读取excel 的方法
*/
public
static
List
<
List
<
Object
>>
readExcel
(
File
file
)
throws
IOException
{
String
fileName
=
file
.
getName
(
)
;
String
extension
=
fileName
.
lastIndexOf
(
"."
)
==
-
1
?
""
:
fileName
.
substring
(
fileName
.
lastIndexOf
(
"."
)
+
1
)
;
if
(
"xls"
.
equals
(
extension
)
)
{
return
read2003Excel
(
file
)
;
}
else
if
(
"xlsx"
.
equals
(
extension
)
)
{
return
read2007Excel
(
file
)
;
}
else
{
throw
new
IOException
(
"不支持的文件类型"
)
;
}
}
/**
* 读取 office 2003 excel
*
* @throws IOException
* @throws FileNotFoundException
*/
private
static
List
<
List
<
Object
>>
read2003Excel
(
File
file
)
throws
IOException
{
List
<
List
<
Object
>>
list
=
new
LinkedList
<
List
<
Object
>>
(
)
;
HSSFWorkbook
hwb
=
new
HSSFWorkbook
(
new
FileInputStream
(
file
)
)
;
HSSFSheet
sheet
=
hwb
.
getSheetAt
(
0
)
;
Object
value
=
null
;
HSSFRow
row
=
null
;
HSSFCell
cell
=
null
;
System
.
out
.
println
(
"读取office 2003 excel内容如下:"
)
;
for
(
int
i
=
sheet
.
getFirstRowNum
(
)
;
i
<=
sheet
.
getPhysicalNumberOfRows
(
)
;
i
++
)
{
row
=
sheet
.
getRow
(
i
)
;
if
(
row
==
null
)
{
continue
;
}
List
<
Object
>
linked
=
new
LinkedList
<
Object
>
(
)
;
for
(
int
j
=
row
.
getFirstCellNum
(
)
;
j
<=
row
.
getLastCellNum
(
)
;
j
++
)
{
cell
=
row
.
getCell
(
j
)
;
if
(
cell
==
null
)
{
continue
;
}
DecimalFormat
df
=
new
DecimalFormat
(
"0"
)
;
// 格式化 number String
// 字符
SimpleDateFormat
sdf
=
new
SimpleDateFormat
(
"yyyy-MM-dd HH:mm:ss"
)
;
// 格式化日期字符串
DecimalFormat
nf
=
new
DecimalFormat
(
"0.00"
)
;
// 格式化数字
switch
(
cell
.
getCellType
(
)
)
{
case
XSSFCell
.
CELL_TYPE_STRING
:
// System.out.println(i + "行" + j + " 列 is String type");
value
=
cell
.
getStringCellValue
(
)
;
System
.
out
.
print
(
" "
+
value
+
" "
)
;
break
;
case
XSSFCell
.
CELL_TYPE_NUMERIC
:
// System.out.println(i + "行" + j
// + " 列 is Number type ; DateFormt:"
// + cell.getCellStyle().getDataFormatString());
if
(
"@"
.
equals
(
cell
.
getCellStyle
(
)
.
getDataFormatString
(
)
)
)
{
value
=
df
.
format
(
cell
.
getNumericCellValue
(
)
)
;
}
else
if
(
"General"
.
equals
(
cell
.
getCellStyle
(
)
.
getDataFormatString
(
)
)
)
{
value
=
nf
.
format
(
cell
.
getNumericCellValue
(
)
)
;
}
else
{
value
=
sdf
.
format
(
HSSFDateUtil
.
getJavaDate
(
cell
.
getNumericCellValue
(
)
)
)
;
}
System
.
out
.
print
(
" "
+
value
+
" "
)
;
break
;
case
XSSFCell
.
CELL_TYPE_BOOLEAN
:
// System.out.println(i + "行" + j + " 列 is Boolean type");
value
=
cell
.
getBooleanCellValue
(
)
;
System
.
out
.
print
(
" "
+
value
+
" "
)
;
break
;
case
XSSFCell
.
CELL_TYPE_BLANK
:
// System.out.println(i + "行" + j + " 列 is Blank type");
value
=
""
;
System
.
out
.
print
(
" "
+
value
+
" "
)
;
break
;
default
:
// System.out.println(i + "行" + j + " 列 is default type");
value
=
cell
.
toString
(
)
;
System
.
out
.
print
(
" "
+
value
+
" "
)
;
}
if
(
value
==
null
||
""
.
equals
(
value
)
)
{
continue
;
}
linked
.
add
(
value
)
;
}
System
.
out
.
println
(
""
)
;
list
.
add
(
linked
)
;
}
return
list
;
}
/**
* 读取Office 2007 excel
*/
private
static
List
<
List
<
Object
>>
read2007Excel
(
File
file
)
throws
IOException
{
List
<
List
<
Object
>>
list
=
new
LinkedList
<
List
<
Object
>>
(
)
;
// String path = System.getProperty("user.dir") +
// System.getProperty("file.separator")+"dd.xlsx";
// System.out.println("路径:"+path);
// 构造 XSSFWorkbook 对象,strPath 传入文件路径
XSSFWorkbook
xwb
=
new
XSSFWorkbook
(
new
FileInputStream
(
file
)
)
;
// 读取第一章表格内容
XSSFSheet
sheet
=
xwb
.
getSheetAt
(
0
)
;
Object
value
=
null
;
XSSFRow
row
=
null
;
XSSFCell
cell
=
null
;
System
.
out
.
println
(
"读取office 2007 excel内容如下:"
)
;
for
(
int
i
=
sheet
.
getFirstRowNum
(
)
;
i
<=
sheet
.
getPhysicalNumberOfRows
(
)
;
i
++
)
{
row
=
sheet
.
getRow
(
i
)
;
if
(
row
==
null
)
{
continue
;
}
List
<
Object
>
linked
=
new
LinkedList
<
Object
>
(
)
;
for
(
int
j
=
row
.
getFirstCellNum
(
)
;
j
<=
row
.
getLastCellNum
(
)
;
j
++
)
{
cell
=
row
.
getCell
(
j
)
;
if
(
cell
==
null
)
{
continue
;
}
DecimalFormat
df
=
new
DecimalFormat
(
"0"
)
;
// 格式化 number String
// 字符
SimpleDateFormat
sdf
=
new
SimpleDateFormat
(
"yyyy-MM-dd HH:mm:ss"
)
;
// 格式化日期字符串
DecimalFormat
nf
=
new
DecimalFormat
(
"0.00"
)
;
// 格式化数字
switch
(
cell
.
getCellType
(
)
)
{
case
XSSFCell
.
CELL_TYPE_STRING
:
// System.out.println(i + "行" + j + " 列 is String type");
value
=
cell
.
getStringCellValue
(
)
;
System
.
out
.
print
(
" "
+
value
+
" "
)
;
break
;
case
XSSFCell
.
CELL_TYPE_NUMERIC
:
// System.out.println(i + "行" + j
// + " 列 is Number type ; DateFormt:"
// + cell.getCellStyle().getDataFormatString());
if
(
"@"
.
equals
(
cell
.
getCellStyle
(
)
.
getDataFormatString
(
)
)
)
{
value
=
df
.
format
(
cell
.
getNumericCellValue
(
)
)
;
}
else
if
(
"General"
.
equals
(
cell
.
getCellStyle
(
)
.
getDataFormatString
(
)
)
)
{
value
=
nf
.
format
(
cell
.
getNumericCellValue
(
)
)
;
}
else
{
value
=
sdf
.
format
(
HSSFDateUtil
.
getJavaDate
(
cell
.
getNumericCellValue
(
)
)
)
;
}
System
.
out
.
print
(
" "
+
value
+
" "
)
;
break
;
case
XSSFCell
.
CELL_TYPE_BOOLEAN
:
// System.out.println(i + "行" + j + " 列 is Boolean type");
value
=
cell
.
getBooleanCellValue
(
)
;
System
.
out
.
print
(
" "
+
value
+
" "
)
;
break
;
case
XSSFCell
.
CELL_TYPE_BLANK
:
// System.out.println(i + "行" + j + " 列 is Blank type");
value
=
""
;
// System.out.println(value);
break
;
default
:
// System.out.println(i + "行" + j + " 列 is default type");
value
=
cell
.
toString
(
)
;
System
.
out
.
print
(
" "
+
value
+
" "
)
;
}
if
(
value
==
null
||
""
.
equals
(
value
)
)
{
continue
;
}
linked
.
add
(
value
)
;
}
System
.
out
.
println
(
""
)
;
list
.
add
(
linked
)
;
}
return
list
;
}
}
|