js代码
$('#grid').datagrid({
//文件upload
$("#myfile").change(function(){
if( $("#myfile").val() == null || $("#myfile").val() == ''){
return;
}
$.ajaxFileUpload({
url : "${pageContext.request.contextPath}/visit/importVisitRecord",
type : 'post',
secureuri : false, //是否启用安全提交,默认为false
fileElementId : 'myfile',//文件选择框的id属性
contentType : "text/html; charset=utf-8",
dataType : 'text',
success : function(data, status) {
if (data.length >=3) {
$.messager.alert('提示', data, 'info',function(){
window.location.reload();
});
} else {
$.messager.alert('提示', data);
}
},
error : function(data, status, e) {
alert('upload Failed!');
}
});
});
function importData(){
$('input:file').trigger('click').trigger('change');
}
html代码
<span οnclick="importData();" class="buttonStyle" style="margin-left:60px;float:left;">导入</span>
@RequestMapping(value="/importVisitRecord", method = RequestMethod.POST)
public ResponseEntity<?> importVisitRecord(@RequestParam("myfile") CommonsMultipartFile myfile,HttpServletRequest request){
Map<String, Object> result = new HashMap<String, Object>();
try {
InputStream is = myfile.getInputStream();
if (myfile != null && !myfile.isEmpty()) {
//获取上传文件的文件名
String originalFileName = myfile.getOriginalFilename();
if (originalFileName != null && !originalFileName.equals("")) {
String[] fileNameArr = originalFileName.split("\\.");
String suffix = fileNameArr[fileNameArr.length - 1];
if (null != suffix && !"".equals(suffix) && ("xls".equals(suffix) || "xlsx".equals(suffix))) {
try {
//String ftpPath = PropsUtil.getProperty("xlsxFile.location");
String ftpPath = "d:/";
//将字节流转化为字符流,并保存到指定位置
FileUtils.copyInputStreamToFile(is, new File(ftpPath + File.separator, originalFileName));
//读取excel文件中的内容
List<List<String>> list = ExcelUtils.readExcel(new File(ftpPath + originalFileName));
if (list != null && list.size()!= 0) {
if(list.get(0).toString().equals("[id, 计划id , 客户ID , 开始拜访时间 , 完成拜访时间 , 拜访反馈 , 自我评价 , 拜访时长, 用户ID, 是否已删除 , 创建时间 , 更新时间 ]")){
if (list.size()>1) {
list.remove(0);
try {
result = visitRecordService.addVistitRecordFromImportData(list);
if (result != null && result.size() != 0) {
//result.put("result", "success");
result.put("信息", "execl文件导入成功" );
}else{
result.put(MobileKey.MSG, "execl文件导入失败" );
}
} catch (Exception e) {
result.put(MobileKey.MSG, e.getMessage() );
throw new RestException(e.getMessage());
}
}else{
result.put(MobileKey.MSG, "execl无数据可导入" );
}
}else{
result.put(MobileKey.MSG, "execl文件数据标题错误" );
}
}else{
result.put(MobileKey.MSG, "execl为空,无数据可导入" );
}
//delete sourceFile
//DeleteFolder(PropsUtil.get("datamanager.FTPPath"));
} catch (IOException e) {
result.put(MobileKey.MSG, "execl文件导入失败" );
}
} else {
result.put(MobileKey.MSG, "请选择execl文件进行导入" );
}
} else {
result.put(MobileKey.MSG, "请选择需要导入的文件" );
}
}
} catch (IOException e) {
logger.error("IOException: " + e);
throw new RestException(e.getMessage());
}
return new ResponseEntity<Map<String, Object>>(result, HttpStatus.OK);
}
ExcelUtils.java
package com.visionet.project.app.common.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtils {
/**
* 对外提供读取excel 的方法
* */
public static List<List<String>> readExcel(File file) throws IOException {
String fileName = file.getName();
String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
.substring(fileName.lastIndexOf(".") + 1);
if ("xls".equals(extension)) {
return read2003Excel(file);
} else if ("xlsx".equals(extension)) {
return read2007Excel(file);
} else {
throw new IOException("请选择execl文件进行导入");
}
}
/**
* 读取 office 2003 excel
*
* @throws IOException
* @throws FileNotFoundException
*/
private static List<List<String>> read2003Excel(File file) throws IOException {
List<List<String>> list = new ArrayList<List<String>>();
List<List<String>> lists=new ArrayList<List<String>>();
HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
HSSFSheet sheet = hwb.getSheetAt(0);
String value = null;
HSSFRow row = null;
HSSFCell cell = null;
for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row == null) {
continue;
}
List<String> linked = new ArrayList<String>();
for (int j = sheet.getRow(1).getFirstCellNum();j <= sheet.getRow(1).getLastCellNum()-1; j++) {
cell = row.getCell(j);
if (cell == null) {
value=null;
linked.add(value);
}else{
DecimalFormat df = new DecimalFormat("0");// 格式化 number String
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
DecimalFormat nf = new DecimalFormat("0");// 格式化数字
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
break;
case XSSFCell.CELL_TYPE_BLANK:
value = null;
break;
default:
value = cell.toString();
}
linked.add(value);
}
}
linked.add((i + 1)+"");
list.add(linked);
}
hwb.close();
for (List<String> str : list) {
int count=0;
for (int i = 0; i < str.size()-1; i++) {
if(str.get(i)!=null && !str.get(i).equals("")){
count++;
}
}
if(count!=0){
lists.add(str);
}
}
return lists;
}
/**
* 读取Office 2007 excel
* */
private static List<List<String>> read2007Excel(File file)
throws IOException {
List<List<String>> list = new ArrayList<List<String>>();
List<List<String>> lists = new ArrayList<List<String>>();
// 构造 XSSFWorkbook 对象,strPath 传入文件路径
XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
// 读取第一章表格内容
XSSFSheet sheet = xwb.getSheetAt(0);
String value = null;
XSSFRow row = null;
XSSFCell cell = null;
for (int i = sheet.getFirstRowNum(); i <= sheet
.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row == null) {
continue;
}
List<String> linked = new ArrayList<String>();
for (int j = sheet.getRow(1).getFirstCellNum(); j <= sheet
.getRow(1).getLastCellNum() - 1; j++) {
cell = row.getCell(j);
if (cell == null) {
value = null;
linked.add(value);
} else {
DecimalFormat df = new DecimalFormat("0");// 格式化 number
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
DecimalFormat nf = new DecimalFormat("0");// 格式化数字
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
break;
case XSSFCell.CELL_TYPE_BLANK:
value = null;
break;
default:
value = cell.toString();
}
linked.add(value);
}
}
//linked.add((i + 1) + "");
list.add(linked);
}
xwb.close();
for (List<String> str : list) {
int count=0;
for (int i = 0; i < str.size()-1; i++) {
if(str.get(i)!=null && !str.get(i).equals("")){
count++;
}
}
if(count!=0){
lists.add(str);
}
}
return lists;
}
}
ServiceImpl 层addVistitRecordFromImportData方法
@Override
public Map<String, Object> addVistitRecordFromImportData(List<List<String>> list) {
if (list == null || list.size() == 0) {
return null;
}
//用于记录添加了多少数据
int count = 0;
//记录错误的个数
int failCount = 0;
//存入数据处理信息
Map<String,Object> message = new HashMap<String,Object>();
StringBuffer msg = new StringBuffer();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
VisitRecord recordDto = new VisitRecord();
for (int i = 0; i < list.size(); i++) {
if (list.get(i).get(0) != null && !list.get(i).get(0).equals("") && (list.get(i).get(1) != null && !list.get(i).get(1).equals("")) && (list.get(i).get(2) != null && !list.get(i).get(2).equals("")) && (list.get(i).get(8) != null && !list.get(i).get(8).equals(""))) {
try {
recordDto.setStart_visit_time(sdf.parse(list.get(i).get(3)));
recordDto.setComplete_visit_time(sdf.parse(list.get(i).get(4)));
recordDto.setCreate_time(sdf.parse(list.get(i).get(10)));
recordDto.setUpdate_time(sdf.parse(list.get(i).get(11)));
} catch (Exception e) {
msg.append( "第" + (i+2) +"有时间格式不正确,请核对!:---");
throw new RestException("第" + list.get(i).get(3) + "或" + list.get(i).get(4) + "或" + list.get(i).get(11) + "或" + list.get(i).get(12) + "行拜访时间格式不正确,请核对!");
}
if (recordDto.getComplete_visit_time().getTime() - recordDto.getStart_visit_time().getTime() >= 0 && recordDto.getUpdate_time().getTime() - recordDto.getCreate_time().getTime() >= 0) {
try {
recordDto.setVisit_interval(Integer.parseInt(list.get(i).get(7)));
} catch (NumberFormatException e1) {
msg.append("第"+(i+2)+"行,拜访时长数据格式不对:---");
throw new RestException("拜访时长数据格式不对");
}
for (int j = (i + 1); j <= list.size(); j++) {
if(j == list.size()){
if (list.get(i).get(1) != null && !list.get(i).get(1).equals("") && list.get(i).get(2) != null && !list.get(i).get(2).equals("") && list.get(i).get(8) != null && !list.get(i).get(8).equals("")) {
if (list.get(i).get(0) != null && !list.get(i).get(0).equals("")) {
recordDto.setId(list.get(i).get(0));
}
if (list.get(i).get(1) != null && !list.get(i).get(1).equals("")) {
recordDto.setPlan_id(list.get(i).get(1));
}
if (list.get(i).get(2) != null && !list.get(i).get(2).equals("")) {
recordDto.setCustomer_id(list.get(i).get(2));
}
if (list.get(i).get(5) != null && !list.get(i).get(5).equals("")) {
recordDto.setVisit_feedback(list.get(i).get(5));
}
if (list.get(i).get(6) != null && !list.get(i).get(6).equals("")) {
recordDto.setSelf_assessment(list.get(i).get(6));
}
if (list.get(i).get(8) != null && !list.get(i).get(8).equals("")) {
recordDto.setOwner_id(list.get(i).get(8));
}
if (list.get(i).get(9) != null && !list.get(i).get(9).equals("")) {
try {
recordDto.setIs_deleted(Integer.parseInt(list.get(i).get(9)));
} catch (NumberFormatException e) {
msg.append("第"+(i+2)+"是否已删除列值格式不正确,请核对:---!");
throw new RestException("第" + list.get(i).get(9) + "is_deleted值不正确,请核对!");
}
}
}
}else{
if (list.get(j).get(0) != null && !list.get(j).get(0).equals("") && (list.get(i).get(1) != null && !list.get(i).get(1).equals("")) && (list.get(i).get(2) != null && !list.get(i).get(2).equals("")) && (list.get(i).get(8) != null && !list.get(i).get(8).equals(""))) {
if (list.get(i).get(0).equals(list.get(j).get(0))) {
System.out.println("第" + list.get(i).get(0) + "与" + list.get(j).get(0) + "id相同,请核对!");
throw new RestException("第" + list.get(i).get(0) + "与" + list.get(j).get(0) + "id相同,请核对!");
} else {
if (list.get(i).get(0) != null && !list.get(i).get(0).equals("")) {
recordDto.setId(list.get(i).get(0));
}
if (list.get(i).get(1) != null && !list.get(i).get(1).equals("")) {
recordDto.setPlan_id(list.get(i).get(1));
}
if (list.get(i).get(2) != null && !list.get(i).get(2).equals("")) {
recordDto.setCustomer_id(list.get(i).get(2));
}
if (list.get(i).get(5) != null && !list.get(i).get(5).equals("")) {
recordDto.setVisit_feedback(list.get(i).get(5));
}
if (list.get(i).get(6) != null && !list.get(i).get(6).equals("")) {
recordDto.setSelf_assessment(list.get(i).get(6));
}
if (list.get(i).get(8) != null && !list.get(i).get(8).equals("")) {
recordDto.setOwner_id(list.get(i).get(8));
}
if (list.get(i).get(9) != null && !list.get(i).get(9).equals("")) {
try {
recordDto.setIs_deleted(Integer.parseInt(list.get(i).get(9)));
} catch (NumberFormatException e) {
msg.append("第"+(i+2)+"是否已删除列值格式不正确,请核对!:---");
throw new RestException("第" + list.get(i).get(9) + "is_deleted值不正确,请核对!");
}
}
}
} else {
msg.append("第"+(i+2)+"行必填项存在为空现象,请核对!(id、计划id、客户id、用户id不能为空):---");
throw new RestException("行必填项存在为空现象,请核对!");
}
}
}
count ++;
} else {
try {
msg.append("第"+(i+2)+"行开始拜访时间大于结束拜访时间或创建时间大雨更新时间");
throw new RestException("第" + list.get(i).get(3) + "或"+ list.get(i).get(10) +"行开始拜访时间大于结束拜访时间或创建时间大雨更新时间");
} catch (Exception e) {
e.printStackTrace();
}
}
visitRecordDao.save(recordDto);
} else {
msg.append("第"+(i+2)+"行必填项存在为空现象,请核对:---");
throw new RestException("第" + list.get(i).get(0) + "行必填项存在为空现象,请核对!");
}
}
failCount = list.size()-count;
message.put("错误信息:", msg);
message.put("总共导入的记录数:", count+"");
message.put("错误个数", failCount);
return message;
}
MobileKey可自行设置