//主页面
<input class="btn btn-primary" id="importFile" type="button" value="导入" style="outline: none;margin-bottom:2px;margin-top:2px;margin-right:10px;height: 26px;padding-top: 2px;" onclick="importFileInput()" />
function importFileInput){
layui.use('layer', function(){
layer.ready(function(){
var layer = layui.layer;
layer.config({
extend: 'mySkin/mySkin.css' //加载您的扩展样式
});
layer.open({
skin: 'layui-layer-ljc',
type: 2,
title:'弹出窗右上角名字',
area: ['570px', '220px'],
offset: maxHeight,
shade: [0.3, '#ccc'],
shadeClose: false,
content:"后台上传地址",
btn: ['导入', '取消'],
btnAlign: 'c',//按钮居中
yes:function(index,layero){
var paddingHeight=maxHeight+80,
file=$(layero).find("iframe")[0].contentWindow.uploadFile,//文件
ret=$(layero).find("iframe")[0].contentWindow.importPo();
if(ret==false){
layer.msg('请选择导入文件！', {offset: paddingHeight,icon: 7});
return false;
}
layer.close(index);
var indexLoad =layer.load(2,{
offset: maxHeight,
shade: [0.3, '#ccc'],
shadeClose: false,
});
importDateFunction(file);
}
});
$(':focus').blur();//取消焦点 防止空格键、回车键二次触发点击事件
});
});
}
function importDateFunction(file){
layui.use('layer', function(){
layer.ready(function(){
var layer = layui.layer;
layer.config({
extend: 'mySkin/mySkin.css' //加载您的扩展样式
});
var form = new FormData();
form.append("file", file.files[0]);
$.ajax({
type: "POST",
url: "后台导入地址",
data:form,
async:true,
processData:false,//这个很有必要，不然不行
contentType: false,
// mimeType:"multipart/form-data",
success: function(data){
layer.close(indexLoad);
if(data.fileIsNull == true){
layer.msg('导入文件不能为空！', {offset:maxHeight,icon: 7});
return false;
}if(data.list.length>0){
for (var i = 0; i < data.list.length; i++) { //遍历表格
addnewrecord(data.list[i],"new",1);
}
if(promptInfo != ""){
layer.msg('导入成功'+data.list.length+'条！'+promptInfo, {offset:maxHeight,icon: 1});
}else{
layer.msg('导入成功！共计'+data.list.length+'条！', {offset:maxHeight,icon: 1});
}
}else{
if(promptInfo != ""){
layer.msg('导入失败！'+promptInfo, {offset:maxHeight,icon: 7});
}else{
layer.msg('导入失败！', {offset:maxHeight,icon: 7});
}
}
},
error:function(e) {}
});
return false;
});
});
}
//弹出窗
<form:form id="inputForm" modelAttribute="实体名" action="" method="post" class="form-search" enctype="multipart/form-data">
<sys:message content="${message}"/>
<table class="table table-striped table-condensed">
<tr>
<td class="tdcss">文件：</td>
<td class="curWidth">
<input id="uploadFile" name="file" type="file"/>
<div style='margin-top: 5px'>
<font color="red">重要提示：</font><br>1.*****<br>2.*****！
</div>
</td>
</tr>
</table>
</form:form>
function importPo(){
var val = $("#uploadFile").val();
if(''==val){
return false;
}else{
return true;
}
}
import org.apache.poi.*
import org.apache.commons.*
/**
* 后台导入
* @param file
* @param officeId 页面表格中含有数据 部门id
* @param officeIds 导入人员所有的部门权限 部门id："id1,id2.."
* @param redirectAttributes
* @return
*/
@RequestMapping(value = "import", method=RequestMethod.POST)
@ResponseBody
public Map<String, Object> importFile(MultipartFile file,@RequestParam String officeId,@RequestParam String officeIds,RedirectAttributes redirectAttributes) {
int totalNum=0;
boolean importUserPermissions=false;
boolean officeTableTag=false;
boolean officeTag=false;
boolean mounthTypeTag=false;
boolean fileIsNull=true;
String mounthLenInfo="";
String errLenInfo="";
String companyName="";
String errempLenInfo="";
String errmoneyLenInfo="";
String errawardLenInfo="";
String errMonthLenInfo="";
String errNoteLenInfo="";
Map<String, Object> vars = Maps.newHashMap();
List<SysIncentivePaymentDetail> list = new ArrayList<SysIncentivePaymentDetail>();
try {
Workbook workBook = getWorkBook(file);
//文件包含多个sheet
for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
//Sheet sheetAt = workBook.getSheetAt(i);//sheet个数
Sheet sheetAt;
if (workBook instanceof SXSSFWorkbook) {
SXSSFWorkbook sxssfWorkbook = (SXSSFWorkbook) workBook;
sheetAt = sxssfWorkbook.getXSSFWorkbook().getSheetAt(i);
} else {
sheetAt = workBook.getSheetAt(i);
}
int lastRowNum = sheetAt.getPhysicalNumberOfRows();//每个sheet里总行数
if(lastRowNum >1) {
String officeIdTag="";//导入文件中部门id
String DatePattern = "^(?:([0-9]{4}-(?:(?:0?[1,3-9]|1[0-2])|(?:0?[13578]|1[02])))|([0-9]{4}-(?:0?[1-9]|1[0-2])))$";
Pattern p = Pattern.compile(DatePattern);
for (int m = 1; m < lastRowNum; m++) {//第一行是表头，所以不要，m从1开始
int len=m+1;
Row row = sheetAt.getRow(m);
boolean emptyRow = isEmptyRow(row);
if(!emptyRow && row != null) {
fileIsNull=false;
boolean lenDate=true;
Iterator<Cell> it = row.iterator();
while (it.hasNext()) {
Cell cell = it.next();
/* CELL_TYPE_NUMERIC = 0; // 数值类型
CELL_TYPE_STRING = 1; // 字符串类型
CELL_TYPE_FORMULA = 2; // 公式类型
CELL_TYPE_BLANK = 3; // 空格类型
CELL_TYPE_BOOLEAN = 4; // 布尔类型
CELL_TYPE_ERROR = 5; // 错误
*/
if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {//非空校验
if(cell.getColumnIndex() == 0) {//第一列
if(errempLenInfo.equals("")) {
errempLenInfo=len+"";
}else {
errempLenInfo=errempLenInfo+"、"+len;
}
}else if(cell.getColumnIndex() == 1) {//第二列
if(errmoneyLenInfo.equals("")) {
errmoneyLenInfo=len+"";
}else {
errmoneyLenInfo=errmoneyLenInfo+"、"+len;
}
}else if(cell.getColumnIndex() == 2) {//第三列
if(errawardLenInfo.equals("")) {
errawardLenInfo=len+"";
}else {
errawardLenInfo=errawardLenInfo+"、"+len;
}
}else if(cell.getColumnIndex() == 3) {//第四列
if(errMonthLenInfo.equals("")) {
errMonthLenInfo=len+"";
}else {
errMonthLenInfo=errMonthLenInfo+"、"+len;
}
}else if(cell.getColumnIndex() == 4) {//第五列
if(errNoteLenInfo.equals("")) {
errNoteLenInfo=len+"";
}else {
errNoteLenInfo=errNoteLenInfo+"、"+len;
}
}
if(errLenInfo.equals("")) {
errLenInfo=len+"";
}else {
errLenInfo=errLenInfo+"、"+len;
}
lenDate=false;
}
}
if(!lenDate) {
continue;
}
SysIncentivePaymentDetail sysIncentivePaymentDetail = new SysIncentivePaymentDetail();
//第1列：row.getCell(0).getNumericCellValue()
String oaId="";
if(row.getCell(0).getCellType()==Cell.CELL_TYPE_NUMERIC) {
oaId=row.getCell(0).getNumericCellValue()+"";
oaId=oaId.substring(0, oaId.indexOf("."));
}else if(row.getCell(0).getCellType()==Cell.CELL_TYPE_STRING) {
oaId=row.getCell(0).getStringCellValue().replaceAll(" ", "");
}
SysHumanResources obj=sysHumanResourcesService.getInfoByEmpNumber(oaId);
sysIncentivePaymentDetail.setName(obj.getName());
sysIncentivePaymentDetail.setEmpnumber(oaId);
sysIncentivePaymentDetail.setOffice(obj.getOffice());
if(!officeIdTag.equals("") && !officeIdTag.equals(obj.getOffice().getId())) {
officeTableTag=true;//用于校验导入文件信息中 部门是否一致 部门不一致
break;
}
//第2列： row.getCell(1).getStringCellValue().replaceAll(" ", "")
String totalmoney="";
if(row.getCell(1).getCellType()==Cell.CELL_TYPE_NUMERIC) {
totalmoney=row.getCell(1).getNumericCellValue()+"";
}else if(row.getCell(1).getCellType()==Cell.CELL_TYPE_STRING) {
totalmoney=row.getCell(1).getStringCellValue().replaceAll(" ", "");
}
sysIncentivePaymentDetail.setTotalmoney(totalmoney);
//第3列： row.getCell(2).getStringCellValue().replaceAll(" ", "")
String prize="";
if(row.getCell(2).getCellType()==Cell.CELL_TYPE_NUMERIC) {
prize=row.getCell(2).getNumericCellValue()+"";
}else if(row.getCell(2).getCellType()==Cell.CELL_TYPE_STRING) {
prize=row.getCell(2).getStringCellValue().replaceAll(" ", "");
}
sysIncentivePaymentDetail.setPrize(prize);
//第4列： row.getCell(3).getStringCellValue().replaceAll(" ", "")
String issueMonth="";
if(row.getCell(3).getCellType()==Cell.CELL_TYPE_NUMERIC) {
issueMonth=row.getCell(3).getNumericCellValue()+"";
}else if(row.getCell(3).getCellType()==Cell.CELL_TYPE_STRING) {
issueMonth=row.getCell(3).getStringCellValue().replaceAll(" ", "");
}
if(issueMonth.length()>6) {
String mounthInfo = issueMonth.substring(0, 7);
Matcher matcher = p.matcher(mounthInfo);
if (matcher.matches()) {//日期格式正确
sysIncentivePaymentDetail.setIssueMonth(mounthInfo);
}else {
//日期格式不正确
mounthTypeTag=true;
if(mounthLenInfo.equals("")) {
mounthLenInfo=len+"";
}else {
mounthLenInfo=mounthLenInfo+"、"+len;
}
}
}else {
//日期格式不正确
mounthTypeTag=true;
if(mounthLenInfo.equals("")) {
mounthLenInfo=len+"";
}else {
mounthLenInfo=mounthLenInfo+"、"+len;
}
}
//第5列： row.getCell(4).getStringCellValue().replaceAll(" ", "")
String remarks="";
if(row.getCell(4).getCellType()==Cell.CELL_TYPE_NUMERIC) {
remarks=row.getCell(4).getNumericCellValue()+"";
}else if(row.getCell(4).getCellType()==Cell.CELL_TYPE_STRING) {
remarks=row.getCell(4).getStringCellValue().replaceAll(" ", "");
}
sysIncentivePaymentDetail.setRemarks(remarks);
totalNum++;
list.add(sysIncentivePaymentDetail);
if(list.size()>0) {
officeIdTag=list.get(0).getOffice().getId();
}
}
}
if(!officeTableTag && mounthLenInfo.equals("")) {//
//页面中原有数据部门id:officeId 导入文件中部门id:officeIdTag
if(officeId!=null && !officeId.equals("") && !officeId.equals(officeIdTag)) {
officeTag=true;
}
if(officeIds.indexOf(officeIdTag)<0) {
//导入信息中人员所在部门，超出导入人的部门权限
importUserPermissions=true;
Office company = officeService.get(officeIdTag);
companyName=company.getName();
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
vars.put("list", list);
vars.put("mounthTypeTag", mounthTypeTag);
vars.put("mounthLenInfo", mounthLenInfo);
vars.put("errLenInfo", errLenInfo);
vars.put("companyName", companyName);
vars.put("importUserPermissions", importUserPermissions);
vars.put("officeTableTag", officeTableTag);
vars.put("officeTag", officeTag);
vars.put("totalNum", totalNum);
vars.put("fileIsNull", fileIsNull);
vars.put("errempLenInfo", errempLenInfo);
vars.put("errmoneyLenInfo", errmoneyLenInfo);
vars.put("errawardLenInfo", errawardLenInfo);
vars.put("errMonthLenInfo", errMonthLenInfo);
vars.put("errNoteLenInfo", errNoteLenInfo);
return vars;
}
public static boolean isEmptyRow(Row row) {
if (row == null || row.toString().isEmpty()) {
return true;
} else {
int count=0;
int rowCount=row.getLastCellNum()-row.getFirstCellNum();
Iterator<Cell> it = row.iterator();
//boolean isEmpty = true;
while (it.hasNext()) {
Cell cell = it.next();
if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK || StringUtils.isEmpty((cell+"").trim())){
count += 1;
}
}
if (count == rowCount) {
return true;
}
return false;
}
}
public static Workbook getWorkBook(MultipartFile file) {
//获得文件名
String fileName = file.getOriginalFilename();
//创建Workbook工作薄对象，表示整个excel
Workbook workbook = null;
try {
//获取excel文件的io流
InputStream is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if(fileName.endsWith("xls")){
//2003
POIFSFileSystem poifsFileSystem = new POIFSFileSystem(is);
workbook = new HSSFWorkbook(poifsFileSystem);
}else if(fileName.endsWith("xlsx")){
//2007 及2007以上
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
e.printStackTrace();
}
return workbook;
}