easyui页面代码:
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-add" plain="true" οnclick="importExcel()">excel导入</a>
<div id="importExcel" class="easyui-dialog"
data-options="closed:true,iconCls:'icon-add'"
style="width: 400px; padding: 10px;">
<form id="importForm" method="post" enctype="multipart/form-data">
<!-- 使用easyui插件上传文件的时候,后面的图标显示不出来 -->
<!-- <input data-options="buttonText:'请选择文件(*.xsl)'" name="uploadFile" style="width:330px" class="easyui-filebox" /> -->
请选择文件(*.xsl):<input type="file" name="file">
</form>
</div>
//导入excel
function importExcel(){
$('#importForm').form('clear');
$('#importExcel').dialog({
closed: false,
modal:true,
title: "添加信息",
buttons: [{
text: '确定',
iconCls: 'icon-ok',
handler: addList
}, {
text: '取消',
iconCls: 'icon-cancel',
handler: function () {
$('#importExcel').dialog('close');
}
}]
});
}
//easyui页面导入时候的遮盖层
function addList(){
$("<div class=\"datagrid-mask\"></div>").css({ display: "block", width: "100%", height: $(window).height() }).appendTo("body");
$("<div class=\"datagrid-mask-msg\"></div>").html("正在处理,请稍候。。。").appendTo("body").css({ display: "block", left: (($(document.body).outerWidth(true) - 190) / 2), top: (($(window).height() - 45) / 2) });
$('#importExcel').dialog('close');
$('#importForm').form('submit', {
url:"<%=request.getContextPath()%>/card/every/importExcel",
success:function(data){
var obj = eval("("+data+")");
if (data.success) {
alert(data.message+"---");
$.messager.alert({
title : "提示",
msg : data.message,
fn:function(){
gridLoad_time();
}
});
} else {
alert(data.message+"===");
$.messager.alert("提示", data.message, 'error');
}
$(".datagrid-mask").remove();
$(".datagrid-mask-msg").remove();
}
});
}
springMVC的controller代码:
@RequestMapping(value="importExcel",produces="application/json;charset=utf-8")
@ResponseBody
public String importExcel(HttpServletRequest request,HttpServletResponse response,Card card){
InputStream in = null;
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartRequest.getFile("file");
if (file.isEmpty()) {
return JSONUtil.toFormJSON(false, "文件不存在");
}
String fileName = file.getOriginalFilename();
if (!fileName.endsWith("xls")) {
return JSONUtil.toFormJSON(false, "仅支持Excel-2003格式");
}
CardVo cardVo = new CardVo();
List<Card> cardList = cardBiz.getCardByIf(cardVo);
List<Map> cardMap = new ArrayList<>();
List<Card> deleteCard = new ArrayList<>();
Map<String, String> insertMap = null;
HSSFWorkbook hwb = null;
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell1 = null;
HSSFCell cell2 = null;
Card tempCard = null;
try {
hwb = new HSSFWorkbook(file.getInputStream());
sheet = hwb.getSheetAt(0);
Iterator<Row> rows = sheet.iterator();
int i = 0;
DecimalFormat df = new DecimalFormat("0");
while(rows.hasNext()){
row = (HSSFRow) rows.next();
if (i % 500 == 0) {
if (i!=0) {
cardMap.add(insertMap);
}
insertMap = new HashMap<>();
}
if (i!=0) {
cell1 = row.getCell(0);
cell2 = row.getCell(1);
if(cell2.getCellType()==Cell.CELL_TYPE_NUMERIC){
insertMap.put(cell1.getStringCellValue(), df.format(cell2.getNumericCellValue()));
}else{
insertMap.put(cell1.getStringCellValue(), cell2.getStringCellValue());
}
}
i++;
}
if (i!=0 && i % 500 != 0) {
cardMap.add(insertMap);
}
for(Card cd : cardList){
for(int k = 0 ; k < cardMap.size() ; k ++){
insertMap = cardMap.get(k);
if (insertMap.containsKey(cd.getNumber())) {
tempCard = new Card();
tempCard.setId(cd.getId());
deleteCard.add(tempCard);
}
}
}
//此处批量删除原来已有的卡号和密码
int countDelete = cardBiz.deleteSome(deleteCard);
int num = 0;
for(int j = 0 ; j < cardMap.size() ; j ++){
tempCard = new Card();
tempCard.setCreateTime(DateUtil.getDate());
//此处批量添加卡号和密码
int countInsert = cardBiz.insertSome(cardMap.get(j), tempCard);
num ++ ;
}
if (num > 0) {
System.out.println("导入excel成功");
return JSONUtil.toFormJSON(true, "导入excel成功");
}else{
return JSONUtil.toFormJSON(false, "没有要导入的数据");
}
} catch (IOException e) {
e.printStackTrace();
}
return JSONUtil.toFormJSON(false, "导入excel失败");
}
mapper中的代码:
public int insertSome(@Param("map")Map<String, String> map,@Param("card")Card card);
public int deleteSome(List<Card> list);
xml中的批量删除和添加的代码:
<insert id="insertSome">
INSERT INTO t_card(number,password,create_time) VALUES
<foreach item="password" collection="map" separator="," index="number">
(#{number},#{password},now())
</foreach>
</insert>
<delete id="deleteSome" parameterType="java.util.List">
DELETE FROM t_card WHERE id IN
<foreach collection="list" item="card" separator="," open="(" close=")">
#{card.id}
</foreach>
</delete>