1.前端页面代码
excelImport : function() {
var me = this;
Ext.create('app.system.common.CommonExcelImportWindow',{
tempUrl:'blacklistController/exportTemplate.do',
importUrl:'blacklistController/excelImport.do',
title:"导入黑名单",
grid:me.blacklistGrid
}).show();
}
2.导入页面
/**
* excel导入
*/
Ext.define('app.system.common.CommonExcelImportWindow', {
extend : 'Ext.window.Window',
height : 180,
width : 480,
layout : 'fit',
modal : true,
param : null,
grid : null,
tempUrl : null,
importUrl : null,
title : null,
initComponent : function() {
var me =this;
if(!me.title){
me.title = 'Excel导入';
}
me.editForm = Ext.create('Ext.form.Panel', {
layout: "anchor",
border:false,
items: [{
margin : '30 5 0 10',
xtype : 'filefield',
name : 'file',
fieldLabel : '请选择Excel文件',
width : 400,
allowBlank : false,
buttonText : '浏览',
buttonConfig : {
iconCls : 'fa fa-folder-open-o',
}
}]
});
me.items=[me.editForm];
me.initBtns();
this.callParent(arguments);
},
initBtns:function(){
var me = this;
me.xzBtn = Ext.create('Ext.button.Button', {
text : '模板下载',
iconCls : 'fa fa-file-excel-o',
handler : function(){
window.location.href = me.tempUrl;
}
});
me.nextBtn = Ext.create('Ext.button.Button', {
text : '上传',
iconCls : 'fa fa-upload',
handler : function(){
me.doUpload();
}
});
me.closeBtn = Ext.create('Ext.button.Button', {
text : '关闭',
iconCls : 'fa fa-close',
handler : function(){
me.hide();
}
});
me.buttons=[me.xzBtn,'->', me.nextBtn,me.closeBtn];
},
doUpload:function(){
var me =this;
if (me.editForm.isValid()) {
me.editForm.submit({
url : me.importUrl,
waitMsg : '正在导入中,请稍候...',
params : me.param,
timeout : 1200000,
success : function(form, action) {
var success = action.result.success;
var resultMsg = action.result.result;
if(resultMsg){
Ext.toastErrorInfo(resultMsg);
}else{
Ext.toastInfo('所选的文件已成功上传!');
me.grid.store.reload();
me.hide();
}
},
failure : function(form, action) {
if(action.result){
Ext.toastErrorInfo(action.result.result);// 错误信息
}
}
})
}
}
});
3.后端controller代码
/**
* 导出Excel模板
*
* @param date
* @param response
*/
@RequestMapping(value = "/exportTemplate.do")
@ResponseBody
public void exportTemplate(HttpServletResponse response) {
blacklistService.exportTemplate(response);
}
/**
* Excel导入任务
*
* @param request
* @return
*/
@SuppressWarnings("all")
@RequestMapping(value = "/excelImport.do", produces = "application/json; charset=utf-8")
@ResponseBody
public String excelImport(HttpSession paramHttpSession, HttpServletRequest request, HttpServletResponse response) {
UserSession userSession = SessionListener.getInstance().getUserSession(paramHttpSession);
String resultMsg = null;
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
InputStream in = null;
List<List<Object>> listob = null;
MultipartFile file = multipartRequest.getFile("file");
if (file.isEmpty()) {
resultMsg = "请重新上传正确的文件";
}
try {
in = file.getInputStream();
} catch (IOException e) {
e.printStackTrace();
}
Workbook workbook = PmsExcelUtil.createWorkbook(in);
try {
resultMsg = blacklistService.insertExcelImport(workbook);
if(StringUtils.isNotEmpty(resultMsg)){
return PmsUtils.response(resultMsg);
}else{
return PmsUtils.response(null);
}
}catch (Exception e) {
return PmsUtils.response("EXCEL导入失败");
}
}
4.后端service代码
static final String[] headers = { "姓名", "性别(0:男,1:女)", "联系电话", "证件类型(0:身份证,1:护照,2:其他)", "证件号码", "通讯地址", "状态(0:未加入)", "取消日期" };
/**
* 下载excel导入模板
* **/
@SuppressWarnings("rawtypes")
public void exportTemplate(HttpServletResponse response) {
String title = "黑名单导入";
String fileName = PmsUtils.uft82Iso(title);
PmsExcelUtil excelUtil = new PmsExcelUtil();
String[] eg = { "张三", "0", "1293948393", "0", "350403003020212",
"厦门软件园", "0", "2019-03-12" };
XSSFWorkbook workbook = excelUtil.getExcelTemplateForXlsx(title,
headers, eg);
PmsExcelUtil.exportExcelForXlsx(workbook, response, fileName ,null);
}
/**
* 导入黑名单
*
* @param workbook
* @return
*/
public String insertExcelImport(Workbook workbook) {
String result = "";
Sheet sheet = workbook.getSheetAt(0);
// 内容的首行号
int firstBobyRowNum = 2;
// 最大行号
int lastRowNum = sheet.getLastRowNum();
if (!sheet.getRow(1).getCell(4).toString().equals("证件号码")) {
return "EXCEL内容缺少关键列[ 证件号码],无法导入";
}
// 遍历当前sheet中的所有行
for (int rowNum = firstBobyRowNum; rowNum <= lastRowNum; rowNum++) {
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
String name = "";
String sex = "";
String telephone = "";
String certificateType = "";
String certificateNum = "";
String address = "";
String state = "";
// String putInDate = "";
String cancelDate = "";
if (StringUtils.isEmpty(getExcelCellValue(row.getCell(4)))) {
result += "第" + (rowNum - 1) + "行数据,证件号码为空!\\n";
continue;
} else {
certificateNum = getExcelCellValue(row.getCell(4));
}
Map<String, Object> props = new HashMap<String, Object>();
props.put("certificateNum", certificateNum);
Blacklist blacklist = (Blacklist) commonDao.getObjectByProperties(
Blacklist.class, props);
if (blacklist != null) {
result += "第" + (rowNum - 1) + "行数据,证件号码已存在!\\n";
continue;
} else {
BlacklistVo blacklistVo = new BlacklistVo();
name = getExcelCellValue(row.getCell(0));
blacklistVo.setName(name);
sex = getExcelCellValue(row.getCell(1));
blacklistVo.setSex(sex);
telephone = getExcelCellValue(row.getCell(2));
blacklistVo.setTelephone(telephone);
;
certificateType = getExcelCellValue(row.getCell(3));
blacklistVo.setCertificateType(certificateType);
blacklistVo.setCertificateNum(certificateNum);
address = getExcelCellValue(row.getCell(5));
blacklistVo.setAddress(address);
state = getExcelCellValue(row.getCell(6));
blacklistVo.setState(state);
// putInDate = getExcelCellValue(row.getCell(7));
// blacklistVo.setPutInDate(putInDate);
cancelDate = getExcelCellValue(row.getCell(8));
blacklistVo.setCancelDate(cancelDate);
saveBlacklist(blacklistVo);
}
}
return result;
}
public static String getExcelCellValue(Cell cell) {
String ret = "";
try {
if (cell == null) {
ret = "";
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
ret = (cell.getStringCellValue().trim());
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
ret = NumberToTextConverter.toText(cell.getNumericCellValue());
String temp = ret.substring(ret.indexOf(".") + 1, ret.length());
try {
if (Integer.parseInt(temp) == 0) {
ret = ret.substring(0, ret.indexOf("."));
}
} catch (Exception ex) {
}
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {// 有公式的Excel单元格
try {
ret = String.valueOf(cell.getStringCellValue());
} catch (IllegalStateException e) {
ret = String.valueOf(cell.getNumericCellValue());
}
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR) {
ret = "" + cell.getErrorCellValue();
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
ret = "" + cell.getBooleanCellValue();
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
ret = "";
}
} catch (Exception ex) {
ex.printStackTrace();
ret = "";
}
return ret;
}