Java对Excel表格的上传和下载处理方法
更新时间：2017年08月06日 10:37:45 作者：blue_wz 
我要评论
width="700" height="60" scrolling="no" src="https://pos.baidu.com/s?hei=60&wid=700&di=u3431600<u=https%3A%2F%2Fwww.jb51.net%2Farticle%2F120443.htm&cja=false<r=https%3A%2F%2Fwww.baidu.com%2Flink%3Furl%3Dn7BEKrqatRBJdaukfLUFwouGOlwbA_YV60MVWNDyeukVi9Pv6Zzv0X7N6eA5cqTVhxns2fjBsbg4jjZQJO5T5q%26wd%3D%26eqid%3Daa53c87800000944000000035be25842&psr=1280x720&prot=2&ari=2&par=1280x680&tcn=1541560840&col=zh-CN&dis=0&cec=GBK&cpl=3&drs=1&dai=2&tpr=1541560839690&cce=true&cfv=0&ps=760x106&pis=-1x-1&dri=0&ti=Java%E5%AF%B9Excel%E8%A1%A8%E6%A0%BC%E7%9A%84%E4%B8%8A%E4%BC%A0%E5%92%8C%E4%B8%8B%E8%BD%BD%E5%A4%84%E7%90%86%E6%96%B9%E6%B3%95_java_%E8%84%9A%E6%9C%AC%E4%B9%8B%E5%AE%B6&chi=1&cdo=-1&tlm=1541560839&dtm=HTML_POST&ccd=24&pss=1263x16051&ant=0&dc=3&cmi=4&exps=111000&pcs=1263x610">
这篇文章主要介绍了Java对Excel表格的上传和下载处理方法,需要的朋友可以参考下
Excel表格文件的上传和下载，java中涉及到文件肯定会有io流的知识。
而excel文件就要涉及到poi技术，而excel的版本包括：2003-2007和2010两个版本， 即excel的后缀名为：xls和xlsx。
这里我是按照正规的项目流程做的案例，所以可能会比网上的一些Demo复杂一些。不过文件的上传和下载基本都是一套固定的流程，只是每个人的实现方式不太相同。
数据库我用的是MySql。
下面是我的项目目录：
按照正常的项目做了分层处理，文件上传的业务我放到了service处理，而文件下载业务还在controller层。
对前端请求处理，我分成了两个方法都放在HandleExcelController里面，这个类继承了BaseExcelController,基本的文件操作处理在BaseExcelController里面。
BaseExcelController继承了BaseController，BaseController类是所有controller的父类，这里用到的不太多，这个类封装了response返回值等的处理等一些方法。
项目中除了springMVC和mybatis的jar包之外还引入了：
上传和下载excel文件：
1、创建需要上传的excel文件，为了简化，我这里只写了四列，即四个字段
2、创建jsp页面
|
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
|
<%@ page language=
"java"
import
=
"java.util.*"
pageEncoding=
"UTF-8"
%>
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.01 Transitional//EN"
>
<html>
<head>
<title>Excel文件处理</title>
<script type=
"text/javascript"
src=
"<c:url value='/res/js/jquery.js'/>"
></script>
<script>
$(function(){
var $wrap = $(
".wrap"
);
var find = function(str){
return
$wrap.find(str);
}
var getJname = function(name){
return
find(
"input[name='"
+name+
"']"
);
}
getJname(
"Upload"
).click(function(){
var form =
new
FormData(document.getElementById(
"tf"
));
$.ajax({
url:
"<c:url value='/File/UploadExcel'/>"
,
type:
"post"
,
data:form,
dataType:
"json"
,
processData:
false
,
contentType:
false
,
success:function(data){
//window.clearInterval(timer);
if
(data.success ==
"success"
){
alert(
"提交文件成功,已将数据存入数据库"
);
}
},
error:function(e){
alert(
"错误！"
);
//window.clearInterval(timer);
}
});
})
getJname(
"Download"
).click(function(){
$.post(
"<c:url value='/File/DownLoadExcel'/>"
,{
"id"
:
"3"
},function(data){
//alert("下载文件成功");
},
"json"
)
})
})
</script>
</head>
<body>
<div
class
=
"wrap"
>
<form id=
"tf"
>
<p>
<input type=
"file"
name=
"file"
value=
"选择文件"
/>
Excel文件上传：<input type=
"button"
name=
"Upload"
value=
"upload"
/>
</p>
<p>
Excel文件下载：<input type=
"button"
name=
"Download"
value=
"updown"
/>
</p>
</form>
</div>
</body>
</html>
|
3、依次创建controller、service、domain、mapper层，注意它们的依赖关系
1）、controller层的处理，在HandleExcelController里面注入BaseExcelService。因为只是做个示范，所欲我这里将泛型固定为Students类
BaseExcelController代码：
|
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
|
package
cn.wangze.controller;
import
java.io.File;
import
java.io.FileInputStream;
import
java.io.FileNotFoundException;
import
java.io.FileOutputStream;
import
java.io.IOException;
import
java.io.InputStream;
import
java.lang.reflect.Method;
import
java.text.SimpleDateFormat;
import
java.util.Collection;
import
java.util.Date;
import
java.util.Iterator;
import
java.util.List;
import
javax.servlet.http.HttpServletResponse;
import
org.apache.commons.lang.StringUtils;
import
org.apache.poi.hssf.usermodel.HSSFCell;
import
org.apache.poi.hssf.usermodel.HSSFCellStyle;
import
org.apache.poi.hssf.usermodel.HSSFFont;
import
org.apache.poi.hssf.usermodel.HSSFPalette;
import
org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.CellRangeAddress;
import
org.apache.poi.hssf.util.HSSFColor;
import
org.apache.poi.ss.usermodel.Sheet;
import
org.apache.poi.xssf.usermodel.XSSFWorkbook;
import
org.springframework.web.multipart.MultipartFile;
import
cn.wangze.domain.Students;
public
class
BaseExcelController
extends
BaseController{
//获取文件的路径
String separator = System.getProperty(
"file.separator"
);
//验证元素是否为空
@SuppressWarnings
(
"all"
)
public
boolean
isEmpty(Object obj){
if
(obj
instanceof
Object[]){
if
(((Object[]) obj).length==
0
){
return
true
;
}
if
(obj ==
null
)
return
true
;
if
((String.valueOf(obj).trim()).length() ==
0
){
return
true
;
}
if
(obj
instanceof
List){
if
(((List) obj) ==
null
|| ((List)obj).size() ==
0
){
return
true
;
}
}
}
return
false
;
}
/**
* 文件上传部分
* */
//验证文件
protected
boolean
checkPathName(String fileName,HttpServletResponse response){
//验证文件是否存在
if
(isEmpty(fileName)){
sendError(
"上传文件不存在"
,response);
return
false
;
}
//验证文件是否是以xls或者xlsx做后缀的文件，如果不是就返回错误信息
if
(!(StringUtils.endsWithIgnoreCase(fileName,
".xls"
)||StringUtils.endsWithIgnoreCase(fileName,
".xlsx"
))){
sendError(
"上传文件类型错误，请核对后重新上传?"
,response);
}
return
true
;
}
//获取文件的sheet
protected
Sheet getSheet(MultipartFile file,String path,String fileName)
throws
IllegalStateException, IOException{
//找到要存放到项目里面的路径，新建文件
File targetFile =
new
File(path, fileName);
targetFile.mkdirs();
if
(targetFile.exists()) {
targetFile.delete();
file.transferTo(targetFile);
}
else
{
file.transferTo(targetFile);
}
//封装输入流,封装sheet里面的内容
InputStream is =
null
;
try
{
is =
new
FileInputStream(path+separator+fileName);
//判断版本是否为Excel加强版
if
(StringUtils.endsWithIgnoreCase(fileName,
".xls"
)){
return
new
HSSFWorkbook(is).getSheetAt(
0
);
}
else
if
(StringUtils.endsWithIgnoreCase(fileName,
".xlsx"
)){
return
new
XSSFWorkbook(is).getSheetAt(
0
);
}
return
null
;
}
finally
{
if
(is !=
null
){
is.close();
}
}
}
/**
* 文件下载部分
* */
//根据传入的Sting值，判断生成在excel表的位置
private
HSSFCellStyle getPublicStyle(HSSFWorkbook workbook,String key){
HSSFFont font = workbook.createFont();
HSSFCellStyle style = workbook.createCellStyle();
HSSFPalette customPalette = workbook.getCustomPalette();
customPalette.setColorAtIndex(HSSFColor.TEAL.index, (
byte
)
64
, (
byte
)
148
, (
byte
)
160
);
customPalette.setColorAtIndex(HSSFColor.ORANGE.index, (
byte
)
170
, (
byte
)
204
, (
byte
)
204
);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
if
(key==
"head"
){
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
font.setFontHeightInPoints((
short
)
12
);
font.setColor(HSSFColor.TEAL.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
}
if
(key==
"title"
){
font.setColor(HSSFColor.WHITE.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.WHITE.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.WHITE.index);
style.setFont(font);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.ORANGE.index);
style.setFillBackgroundColor(HSSFColor.ORANGE.index);
}
return
style;
}
//创建head头信息
private
void
createHead(HSSFSheet sheet,HSSFCellStyle style,String[] title){
HSSFRow row1 = sheet.createRow(
0
);
HSSFCell cellTitle = row1.createCell(
0
);
cellTitle.setCellValue(
new
HSSFRichTextString(title[
0
]));
sheet.addMergedRegion(
new
CellRangeAddress(
0
,
0
,
0
,title.length-
2
));
cellTitle.setCellStyle(style);
}
//创建title信息
private
void
createTitle(HSSFSheet sheet,HSSFCellStyle style,String[] label,
int
columnNum){
HSSFRow row2 = sheet.createRow(
1
);
HSSFCell cell1 =
null
;
for
(
int
n=
0
;n<columnNum;n++){
cell1 = row2.createCell(n);
cell1.setCellValue(label[n+
1
]);
cell1.setCellStyle(style);
}
}
//创建content数据信息
private
void
createContent(HSSFSheet sheet,HSSFCellStyle style,Collection<Students> list,
int
columnNum,String[] parameters){
int
index=
0
;
Iterator<Students> it = list.iterator();
while
(it.hasNext()){
index++;
Students cash = it.next();
int
num2 = parameters.length;
HSSFRow row = sheet.createRow(index+
1
);
initCells(style, num2,cash, parameters,row);
}
}
//验证是否为中文
public
boolean
checkChinese(String s){
int
n=
0
;
boolean
flag =
false
;
for
(
int
i=
0
; i<s.length(); i++) {
n = (
int
)s.charAt(i);
flag=(
19968
<= n && n <
40623
)?
true
:
false
;
}
return
flag;
}
//将数据设置到excel表格内
public
void
initCells(HSSFCellStyle style,
int
columnNum, Students t,
String[] endContent, HSSFRow row3) {
for
(
int
j=
0
;j<columnNum;j++){
HSSFCell cell = row3.createCell(j);
String fieldName = endContent[j];
try
{
if
(fieldName!=
""
&& !checkChinese(fieldName)){
String getMethodName =
"get"
+fieldName.substring(
0
,
1
).toUpperCase()+fieldName.substring(
1
);
Class clazz = t.getClass();
Method getMethod = clazz.getMethod(getMethodName,
new
Class[]{});
String value = (String)getMethod.invoke(t,
new
Object[]{});
cell.setCellValue(value);
}
else
{
cell.setCellValue(fieldName);
}
cell.setCellStyle(style);
}
catch
(Exception e){
e.printStackTrace();
}
}
}
public
void
createEnd(HSSFSheet sheet,HSSFCellStyle style,
int
numText,
int
columnNum,Students t,String[] endContent){
HSSFRow row3 = sheet.createRow(numText+
2
);
initCells(style, columnNum, t, endContent, row3);
}
//根据service查询到的数据，创建excel表并插入查询的数据信息
protected
String getOutputName(List<Students> list, String path, String[] title, String[] parameters, Students t, String[] endContent)
throws
IOException{
//根据传入的title数组的第一个值，设置文件名称
String filename = title[
0
]+
"_"
+
new
SimpleDateFormat(
"yyyyMMdd"
).format(
new
Date())+
".xls"
;
//输出流放到文件的本地位置
FileOutputStream fos =
new
FileOutputStream(path+separator+filename);
//列数，根据title的个数，除去第一个就是每列title的信息
int
columnNum = title.length-
1
;
int
numText = list.size();
HSSFWorkbook workbook =
new
HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
sheet.setDefaultColumnWidth (
20
);
sheet.setDefaultRowHeight((
short
)
400
);
HSSFCellStyle contentStyle =
this
.getPublicStyle(workbook,
""
);
HSSFCellStyle titleStyle =
this
.getPublicStyle(workbook,
"title"
);
HSSFCellStyle headerStyle =
this
.getPublicStyle(workbook,
"head"
);
createHead(sheet,headerStyle,title);
createTitle(sheet,titleStyle,title,columnNum);
createContent(sheet,contentStyle,list,columnNum,parameters);
//createEnd(sheet,contentStyle,numText,columnNum,t,endContent);
workbook.write(fos);
fos.flush();
fos.close();
return
filename;
}
}
|
HandleExcelController用来处理前端请求，代码如下：
|
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
|
package
cn.wangze.controller;
import
java.io.File;
import
java.util.List;
import
javax.servlet.ServletOutputStream;
import
javax.servlet.http.HttpServletResponse;
import
javax.servlet.http.HttpSession;
import
org.apache.commons.io.FileUtils;
import
org.springframework.beans.factory.annotation.Autowired;
import
org.springframework.stereotype.Controller;
import
org.springframework.web.bind.annotation.RequestMapping;
import
org.springframework.web.multipart.MultipartFile;
import
cn.wangze.domain.Students;
import
cn.wangze.service.BaseExcelService;
@Controller
@RequestMapping
(
"/File"
)
public
class
HandleExcelController
extends
BaseExcelController{
@Autowired
private
BaseExcelService baseExcelService;
@RequestMapping
(
"/UploadExcel"
)
public
void
UploadExcel(MultipartFile file,HttpSession session,HttpServletResponse response)
throws
Exception{
//如果上传的文件不存在，抛出异常
if
(file ==
null
){
throw
new
Exception(
"文件不存在"
);
}
//获取文件名
String fileName = file.getOriginalFilename();
//选择上传的文件存放到项目的路径
String path = session.getServletContext().getRealPath(separator+
"res"
+separator+
"upload"
);
if
(!checkPathName(fileName,response))
return
;
String msg = baseExcelService.loadExcel(getSheet(file, path, fileName));
sendMsg(
true
,msg,response);
}
@RequestMapping
(
"/DownLoadExcel"
)
public
void
UpdownExcel(Students student,HttpServletResponse res,HttpSession session,HttpServletResponse response)
throws
Exception{
List<Students> stus = baseExcelService.queryList(student);
if
(stus.size()==
0
){
res.sendRedirect(
"/index.jsp"
);
return
;
}
//下载的excel文件存放的本地路径
String path = session.getServletContext().getRealPath(separator+
"res"
+separator+
"exportExcel"
+separator);
ServletOutputStream os = res.getOutputStream();
Students t = baseExcelService.queryTotal(student);
//标题文字，数值中的第一个值+当前日期为文件名称，以后的每个元素为每列的标题
String[] title={
"studets04"
,
"id"
,
"名字"
,
"年龄"
,
"性别"
};
//标题文字
//对应实体类的属性值
String[] parameters ={
"id"
,
"name"
,
"age"
,
"sex"
};
String[] endContent = {
""
,
""
,
""
,
""
};
//调用父类的处理方法，生成excel文件
String filename = getOutputName(stus,path,title,parameters,t,endContent);
try
{
res.reset();
res.setCharacterEncoding(
"utf8"
);
res.setContentType(
"application/vnd.ms-excel;charset=utf8"
);
res.setHeader(
"Content-Disposition"
,
"attachment;fileName="
+
new
String(filename.getBytes(
"utf-8"
),
"iso-8859-1"
));
os.write(FileUtils.readFileToByteArray(
new
File(path+separator+filename)));
sendResult(
true
,response);
os.flush();
}
finally
{
if
(os !=
null
) {
os.close();
}
}
}
}
|
2）、service层的处理，把StudentsMapper注入到BaseExcelService
BaseExcelService代码：
|
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
|
package
cn.wangze.service;
import
java.util.ArrayList;
import
java.util.HashMap;
import
java.util.List;
import
java.util.Map;
import
javax.servlet.ServletOutputStream;
import
javax.servlet.http.HttpSession;
import
org.apache.poi.ss.usermodel.Cell;
import
org.apache.poi.ss.usermodel.Row;
import
org.apache.poi.ss.usermodel.Sheet;
import
org.springframework.beans.factory.annotation.Autowired;
import
org.springframework.stereotype.Service;
import
cn.wangze.domain.Students;
import
cn.wangze.mapper.StudentsMapper;
@Service
public
class
BaseExcelService {
@Autowired
private
StudentsMapper<Students> studentsMapper;
//判断字符串是否为空
public
boolean
isEmpty(String str) {
return
str ==
null
|| str.length() ==
0
;
}
//获取单个表格(字段)存放的信息
private
String getValue(Cell cell,String cellLable,Map<String,String> errMap){
cell.setCellType(Cell.CELL_TYPE_STRING);
String value = cell.getStringCellValue().trim();
return
value;
}
//通过这个方法将excel表的每行的数据放到info对象里面
private
String addInfo(Row row,Students info){
Map<String,String> errMap =
new
HashMap<String,String>();
String id = getValue(row.getCell(
0
),
"ID"
,errMap);
String username = getValue(row.getCell(
1
),
"姓名"
,errMap);
String age = getValue(row.getCell(
2
),
"年龄"
,errMap);
String sex = getValue(row.getCell(
3
),
"性别"
,errMap);
String errMsg = errMap.get(
"errMsg"
);
if
(!isEmpty(errMsg)){
return
errMsg;
}
info.setId(id);
info.setName(username);
info.setAge(age);
info.setSex(sex);
return
null
;
}
public
String loadExcel(Sheet sheet)
throws
Exception{
//新建一个List集合，用来存放所有行信息，即每行为单条实体信息
List<Students> infos =
new
ArrayList<Students>();
//获取到数据行数，第一行是title，不需要存入数据库，所以rowNum从1开始
for
(
int
rowNum =
1
; rowNum <= sheet.getLastRowNum(); rowNum++) {
Students info =
new
Students();
String errMsg2 = addInfo(sheet.getRow(rowNum),info);
if
(errMsg2 !=
null
)
return
errMsg2;
infos.add(info);
}
if
(infos.isEmpty()){
return
"没有解析到学生数据，请查验EXCEL文件"
;
}
//通过studentsMapper的insertSheetData方法，将实体类存放的数据插入到数据库
int
result = studentsMapper.insertSheetData(infos);
//若插入成功会返回大于1的整数，返回success
if
(result >=
1
){
return
"success"
;
}
return
"error"
;
}
//查询所有数据库存放的学生信息
public
List<Students> queryList(Students students){
return
studentsMapper.queryList(students);
}
//获取到的学生实体信息
public
Students queryTotal(Students students){
return
studentsMapper.queryTotal(students);
}
public
void
downExcel(HttpSession session,String separator){
}
}
|
3）、实体层的处理，字段要对应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
|
package
cn.wangze.domain;
public
class
Students {
String id;
String name;
String age;
String sex;
public
String getId() {
return
id;
}
public
void
setId(String id) {
this
.id = id;
}
public
String getName() {
return
name;
}
public
void
setName(String name) {
this
.name = name;
}
public
String getAge() {
return
age;
}
public
void
setAge(String age) {
this
.age = age;
}
public
String getSex() {
return
sex;
}
public
void
setSex(String sex) {
this
.sex = sex;
}
}
|
4）、dao层处理：StudentsMapper.java是一个接口，业务到数据库需要执行的方法在这里声明，StudentsMapper.xml相当于接口的实现类，用来连接java和数据库的操作。
StudentsMapper.java代码：
|
1
2
3
4
5
6
7
|
package
cn.wangze.mapper;
import
java.util.List;
public
interface
StudentsMapper<T> {
public
int
insertSheetData(List<T> list);
public
List<T> queryList(T t);
public
T queryTotal(T t);
}
|
StudentsMapper.xml代码：
|
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
|
<?xml version=
"1.0"
encoding=
"UTF-8"
?>
<!DOCTYPE mapper PUBLIC
"-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd"
>
<mapper namespace=
"cn.wangze.mapper.StudentsMapper"
>
<sql id=
"ColumnList"
>
id,name,age,sex
</sql>
<sql id=
"ColumnList_t"
>
t.id,t.name,t.age,t.sex
</sql>
<sql id=
"ValueList"
>
#{id},#{name},#{age},#{sex}
</sql>
<sql id=
"WhereClause"
>
where
1
=
1
<
if
test=
"id!=null and id!=''"
>and id=#{id}</
if
>
<
if
test=
"name!=null and name!=''"
>and name=#{name}</
if
>
<
if
test=
"age!=null and age!=''"
>and age=#{age}</
if
>
<
if
test=
"sex!=null and sex!=''"
>and sex=#{sex}</
if
>
</sql>
<sql id=
"WhereClause_pager"
>
where
1
=
1
<
if
test=
"t.id!=null and t.id!=''"
>and id=#{t.id}</
if
>
<
if
test=
"t.name!=null and t.name!=''"
>and name=#{t.name}</
if
>
<
if
test=
"t.age!=null"
>and age=#{t.age}</
if
>
<
if
test=
"t.sex!=null and t.sex!=''"
>and sex=#{t.sex}</
if
>
</sql>
<sql id=
"SetClause"
>
set
<trim suffixOverrides=
","
>
<
if
test=
"id!=null"
>id=#{id},</
if
>
<
if
test=
"name!=null"
>name=#{name},</
if
>
<
if
test=
"pid!=null"
>age=#{age},</
if
>
<
if
test=
"url!=null"
>sex=#{sex},</
if
>
</trim>
</sql>
<select id=
"queryList"
resultType=
"Students"
>
select <include refid=
"ColumnList"
/> from students
</select>
<select id=
"queryTotal"
parameterType=
"Students"
resultType=
"Students"
>
select <include refid=
"ColumnList"
/> from students <include refid=
"WhereClause"
/>
<!-- (select <include refid=
"ColumnList"
/> from t_account_cash t
<include refid=
"WhereClauseQuery"
/> group by to_char(t.add_time,
'yyyy-mm-dd'
),t.account_id) a -->
</select>
<insert id=
"insertSheetData"
useGeneratedKeys=
"true"
parameterType=
"java.util.List"
>
<!-- <selectKey resultType=
"long"
keyProperty=
"id"
order=
"AFTER"
>
SELECT
LAST_INSERT_ID()
</selectKey> -->
insert into students (id,name,age,sex)
values
<foreach collection=
"list"
item=
"item"
index=
"index"
separator=
","
>
(#{item.id},#{item.name},#{item.age},#{item.sex})
</foreach>
</insert>
</mapper>
|
所有的代码就是这些了，操作的时候需要注意的多是路径的问题。最复杂的就是BaseExcelController的操作，它做的事情就是解析上传和创建下载excel文件。
执行完之后的结果图是这样：
在数据库查看上传的excel表：
下载到D:\tomcat\tomcat6.0.32\webapps\ExcelHandleDemo\res\exportExcel文件夹下的excel表：
这里有一点不足的地方，我相信你已经发现了，就是下载完excel表格之后，前端还没有和业务对接上，没有相应的提示来告诉操作人执行结果，只能通过代码设置好的路径去查看文件夹下是否有下载的excel文件，
不过这都是细节问题，相信难不倒聪明的各位。
总结
以上所述是小编给大家介绍的Java对Excel表格的上传和下载处理方法，希望对大家有所帮助，如果大家有任何疑问欢迎给我留言，小编会及时回复大家的！
<div class="art_xg">
3518
到【灌水乐园】发言
最新评论