导入excel数据到数据库

9 篇文章 0 订阅

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可自行设置







  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值