body{
margin: 10px;
min-height: 500px;
}
form{
margin: 0;
}
select{
width: 130px;
}
fieldset{
margin: 0 0 5px;
}
.mainBody{
margin: 5px;
}
.header{
font-weight: bold;
font-size: 18px;
}
.dataTable{
width: 60%;
border-collapse:collapse;
margin-bottom: 5px;
color: #000000;
}
.dataTable .title{
background-color: #99CCCC;
text-align: center;
}
.dataTable .foot{
background-color: #F2F2F2;
}
.dataTable input[type=text]{
text-align: center;
}
.file-box{ position:relative;width:380px}
.txt{ height:22px; border:1px solid #cdcdcd; width:180px;}
.btn{ background-color:#FFF; border:1px solid #CDCDCD;height:24px; width:70px;}
.file{ position:absolute; top:0; right:80px; height:24px; filter:alpha(opacity:0);opacity: 0;width:260px }
以上是页面样式,其中上传文件那个浏览框的样式需要注意,还有表的列头样式以及单元格的边线样式
页面:
<%@ page language="java" pageEncoding="UTF-8"%>
<%@ taglib prefix="pg" uri="http://jsptags.com/tags/navigation/pager" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html>
<html>
<head>
<jsp:include page="../a/b/ui.jsp"></jsp:include>
<link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath}/a/b/c.css" />
<script type="text/javascript" charset="UTF-8">
function upLoadData(){
$("#upLoad").attr("disabled",true);
document.searchForm.submit();
}
function query(){
var url = '${pageContext.request.contextPath}/a/b/query.mmx';
window.location.href = url;
}
function downloadTemplate(){
var url = '${pageContext.request.contextPath}/a/b/downloadTemplate.mmx';
window.location.href = url;
}
function reLoadData(page,pageUrl){
window.location.href=pageUrl;
}
$(function(){
var tip = '${tip}';
if(tip != null&&tip !=""){
alert(tip);
}
$("#upLoad").attr("disabled",false);
});
</script>
</head>
<body>
<div>
<fieldset>
<legend></legend>
<form id="searchForm" name="searchForm" action="upLoad.mmx" method="post" enctype="multipart/form-data">
<div class="file-box">文件
<input type='text' id='textfield' name='path' class='txt' />
<input type='button' class='btn' value='选择文件' />
<input type="file" id="fileField" name="attendance" class="file" size="28" οnchange="document.getElementById('textfield').value=this.value" />
</div>
<div align="right">
<input type="button" id="upLoad" value="提交" style="width: 100px;height: 30px" οnclick="upLoadData()" />
<input type="button" value="查询" style="width: 100px;height: 30px" οnclick="query()"/>
</div>
</form>
</fieldset>
</div>
<div><hr>操作信息:<a href="javascript:;" οnclick="downloadTemplate()">下载模版</a><hr></div>
<div id="dataListDiv">
<c:if test="${not empty list}">
<table border="1" class="dataTable">
<tr class="title">
<td>序号</td>
<td>ID</td>
<td>编号</td>
<td>名称</td>
</tr>
<c:forEach items="${list}" var="p" varStatus="in">
<tr>
<td>${in.index+1}</td>
<td>${p.id}</td>
<td>${p.code}</td>
<td>${p.name}</td>
</tr>
</c:forEach>
</table>
</c:if>
</div>
<div align="center">
<pg:pager url="${pageContext.request.contextPath}/a/b/query.mmx"
items="${totalCount}" export="currentPageNumber=pageNumber" maxPageItems="20">
<pg:index>
<pg:first unless="current">
<a href="javascript:;" οnclick="reLoadData(${pageNumber }, '${pageUrl}');">首页</a>
</pg:first>
<pg:prev>
<a href="javascript:;" οnclick="reLoadData(${pageNumber }, '${pageUrl}');">前页</a>
</pg:prev>
<pg:pages>
<c:choose>
<c:when test="${currentPageNumber eq pageNumber}">
<font color="red">${pageNumber }</font>
</c:when>
<c:otherwise>
<a href="javascript:;" οnclick="reLoadData(${pageNumber }, '${pageUrl}');">${pageNumber }</a>
</c:otherwise>
</c:choose>
</pg:pages>
<pg:next>
<a href="javascript:;" οnclick="reLoadData(${pageNumber }, '${pageUrl}');">后页</a>
</pg:next>
<pg:last unless="current">
<a href="javascript:;" οnclick="reLoadData(${pageNumber }, '${pageUrl}');">尾页</a>
</pg:last>
</pg:index>
</pg:pager>
</div>
</body>
</html>
后台代码:
@RequestMapping("/upLoadData")
public ModelAndView upLoadData(HttpServletRequest request,HttpServletResponse response){
Map<String,Object> model = new HashMap<String, Object>();
try{
// 转型为MultipartHttpRequest:
MultipartHttpServletRequest multipartHttpservletRequest = (MultipartHttpServletRequest) request;
// 获得文件:
MultipartFile file = multipartHttpservletRequest.getFile("attendance");
// 获得输入流:
InputStream input = file.getInputStream();
// 获得文件名:
String attendanceFileName = file.getOriginalFilename();
if(attendanceFileName == null||attendanceFileName.equals("")){
model.put("tip", "请导入文件!");
return new ModelAndView("admin/imei/setIMEIProperty",model);
}
int index = attendanceFileName.lastIndexOf(".");
String suffix = attendanceFileName.substring(index + 1, attendanceFileName.length());
String fileName = attendanceFileName.substring(0,index);
model.put("fileName", fileName);
Workbook xssfWorkbook = null;
if ("xls".equals(suffix)) {
xssfWorkbook = new HSSFWorkbook(input);
} else if ("xlsx".equals(suffix)) {
xssfWorkbook = new XSSFWorkbook(input);
}
Sheet sheet = xssfWorkbook.getSheetAt(0);
if (sheet == null) {
model.put("tip", "没有数据!");
return new ModelAndView("a/b/c",model);
}
if(sheet.getLastRowNum()<1){
model.put("tip", "模板中没有数据:模板数据为空,导入失败!");
return new ModelAndView("a/b/c",model);
}
List<p> pList = new ArrayList<p>();
List<String> productIdList = new ArrayList<String>();
boolean flag = false;
int sumCount = sheet.getLastRowNum();
for (int rowNum = 1; rowNum <= sumCount; rowNum++) {
if (sheet.getRow(rowNum) != null) {
Row row = sheet.getRow(rowNum);
p info = new p();
String id = ExcelUtil.getValue(row.getCell(0));
String code = ExcelUtil.getValue(row.getCell(1));
String name = ExcelUtil.getValue(row.getCell(2));
if(id.equals("")||code.equals("")||name.equals("")){
flag = true;
continue;
}
productIdList.add(id);
info.setid(Integer.valueOf(id));
info.setcode(code);
info.setName(name);
info.setCreateTime(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
info.setOperator("ren");
imeiProductSetList.add(info);
}
}
//返回已经存在的productIdList
List<Integer> returnProductIdList = iMEIProductService.queryIMEIProductId(productIdList);
/*for (Integer productId : returnProductIdList) {
for (Iterator<ImeiProductSet> iterator = imeiProductSetList.iterator(); iterator.hasNext();) {
p p = iterator.next();
if(p.getId() == productId){
iterator.remove();
}
}
}*/
//将excel中数据存入imei_product_set表中
if(!imeiProductSetList.isEmpty()){
iMEIProductService.saveImeiProductSet(imeiProductSetList);
}
if(flag||returnProductIdList.size()>0){
model.put("tip","导入失败!");
}else{
model.put("tip","导入成功!");
}
}catch(Exception e){
e.printStackTrace();
log.error("上传数据时出现异常", e);
model.put("tip","导入失败!");
}
return new ModelAndView("a/b/c",model);
}
mapper.java
<span style="white-space:pre"> </span>@Override
public void savep(List<p> list) {
this.getSession().insert(list);
}
@Override
public List<p> queryp(int pageNo, int pageSize) {
return this.getSession().selectList(null, new RowBounds(pageNo,pageSize));
}
@Override
public Integer querypCount(Map<String, String> map) {
return this.getSession().selectOne(map);
}
@Override
public List<Integer> queryId(List<String> idList) {
return this.getSession().selectList(idList);
}
mapper.xml
<insert id="savep" parameterType="java.util.List">
insert into p (id, code, name) values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.id},#{item.code},#{item.name})
</foreach>
</insert>
<select id="queryp" resultMap="iMEIProductSet">
select * from p ORDER BY id DESC
</select>
<select id="querypCount" resultType="java.lang.Integer">
select count(*) from p
</select>
<select id="queryId" parameterType="java.util.List" resultType="java.lang.Integer">
select id from p where id in
<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
#{item}
</foreach>
</select>