org json maven地址 maven org.apache.poi

1.选择文件

2.选择文件后点击上传文件

3.当文件数据重复，提示请检查Excel第…行，请修改后再次提交

4.数据没有任何问题，则提示成功上传…条数据

JAVA+maven实现Excel批量上传 ajaxUpload

1.在pom.xml中添加的依赖。

<dependency>
		<groupId>commons-io</groupId>
		<artifactId>commons-io</artifactId>
		<version>1.3.2</version>
	</dependency>
	<dependency>
		<groupId>commons-fileupload</groupId>
		<artifactId>commons-fileupload</artifactId>
		<version>1.2.1</version>
	</dependency>

	<!-- poi excel文件写入与读取 -->
	<dependency>
		 <groupId>org.apache.poi</groupId>
		 <artifactId>poi-ooxml</artifactId>
		 <version>4.0.1</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		 <artifactId>poi</artifactId>
		 <version>4.0.1</version>
	</dependency>
	<dependency>
  		 <groupId>org.apache.poi</groupId>
   		 <artifactId>poi-examples</artifactId>
		<version>4.0.1</version>
	</dependency>
	<dependency>
		 <groupId>org.apache.poi</groupId>
		<artifactId>poi-excelant</artifactId>
		<version>4.0.1</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml-schemas</artifactId>
		<version>4.0.1</version>
	</dependency>
	<dependency>
		<groupId>org.apache.xmlbeans</groupId>
		<artifactId>xmlbeans</artifactId>
		<version>3.1.0</version>
	</dependency>

<!-- 定义文件上传解析器 -->
 <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
 <!-- 设定默认编码 -->
 <property value="UTF-8" name="defaultEncoding"/>
 <property value="1048576" name="maxUploadSize"/>
 </bean>

3.Excel数据封装类

/**
 *• Excel数据封装类
• @author
• 
*/
 public class ExcelBean implements java.io.Serializable{

private static final long serialVersionUID = 1L;
	private String headTextName; //列头（标题）名
    private String propertyName; //对应字段名
    private Integer cols; //合并单元格数
    private XSSFCellStyle cellStyle;
    public ExcelBean(){
    }
    public ExcelBean(String headTextName, String propertyName){
        this.headTextName = headTextName;
        this.propertyName = propertyName;
    }
    public ExcelBean(String headTextName, String propertyName, Integer cols) {
        super();
        this.headTextName = headTextName;
        this.propertyName = propertyName;
        this.cols = cols;
    }
    public String getHeadTextName() {
        return headTextName;
    }

    public void setHeadTextName(String headTextName) {
        this.headTextName = headTextName;
    }

    public String getPropertyName() {
        return propertyName;
    }

4.

/**
• Excel导入工具类
• @author
• 
/
 public class ExcelUtil {
 private final static String excel2003L =".xls"; //2003- 版本的excel
 private final static String excel2007U =".xlsx"; //2007+ 版本的excel
 /*
 * Excel导入
 */
 public static List<List> getBankListByExcel(InputStream in, String fileName) throws Exception{
 List<List> list = null;
 //创建Excel工作薄
 Workbook work = getWorkbook(in,fileName);
 if(null == work){
 throw new Exception(“创建Excel工作薄为空！”);
 }
 Sheet sheet = null;//页数
 Row row = null;//行数
 Cell cell = null;//列数
 list = new ArrayList<List>();
 //遍历Excel中所有的sheet
 // for (int i = 0; i < work.getNumberOfSheets(); i++) {
 //指录入第一个工作簿
 for (int i = 0; i < 1; i++) {
 sheet = work.getSheetAt(i);
 if(sheetnull){continue;}
 //遍历当前sheet中的所有行
 //包涵头部，所以要小于等于最后一列数,这里也可以在初始值加上头部行数，以便跳过头部
 for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
 //读取一行
 row = sheet.getRow(j);
 //去掉空行和表头
 if(rownull||row.getFirstCellNum()==j){continue;}
 //遍历所有的列
 List li = new ArrayList();
 li.add(j+1);
 for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
 cell = row.getCell(y);
 //Excel表格行数
 System.out.println(“开始转换”);
 li.add(getCellValue(cell));

}
            list.add(li);
        }
    }
    return list;
}
/**
 * 描述：根据文件后缀，自适应上传文件的版本
 */
public static  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
    Workbook wb = null;
    String fileType = fileName.substring(fileName.lastIndexOf("."));
    if(excel2003L.equals(fileType)){
        wb = new HSSFWorkbook(inStr);  //2003-
    }else if(excel2007U.equals(fileType)){
        wb = new XSSFWorkbook(inStr);  //2007+
    }else{
        throw new Exception("解析的文件格式有误！");
    }
    return wb;
}
/**
 * 描述：对表格中数值进行格式化
 */
public static  Object getCellValue(Cell cell){
    Object value = null;
    DecimalFormat df = new DecimalFormat("0");  //格式化字符类型的数字
    SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
    DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
    switch (cell.getCellType()) {
        case STRING:
            value = cell.getRichStringCellValue().getString();
            break;
        case NUMERIC:
            if("General".equals(cell.getCellStyle().getDataFormatString())){
                value = df.format(cell.getNumericCellValue());
            }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
                value = sdf.format(cell.getDateCellValue());
            }else{
                value = df2.format(cell.getNumericCellValue());
            }
            break;
        case BOOLEAN:
            value = cell.getBooleanCellValue();
            break;
        case BLANK:
            value = "";
            break;
        default:
            break;
    }
    return value;
}

service层

/**
 * Excel实现批量上传
 */
public Map<String,String> importExcel(MultipartFile file, HttpServletRequest request) {
	Map<String,String> map = new HashMap<String,String>();
	//执行成功的数量
	int count = 0;
	//重名
	String duplicate = "";
	//格式错误
	String error ="";
	//是否执行添加
	int row=0;
	try {

// 获取请求文件的输入流
 InputStream in = file.getInputStream();
 // 获取IO流数据
 List<List> listob = ExcelUtil.getBankListByExcel(in, file.getOriginalFilename());
 // 创建用户列表集合
 List userList = new ArrayList();
 // 遍历listob数据，把数据放到List中
 for (int i = 0; i < listob.size(); i++) {
 List ob = listob.get(i);
 // 实例化用户实体类
 User user = new User();
 // 实例化角色实体类
 Role role = new Role();
 // 验证用户账号为否唯一
 User user1 = userMapper.getUserByAccount(String.valueOf(ob.get(2)).trim());
 // 格式验证
 if(String.valueOf(ob.get(2)).trim().matches("^([A-Za-z0-9]+KaTeX parse error: Expected 'EOF', got '&' at position 6: )+") &̲& String.valueO…)+")
 &&(String.valueOf(ob.get(4)).trim().equals(“男”) || String.valueOf(ob.get(4)).trim().equals(“女”)) ) {
 //System.out.println(“ob正确：”+ob);
 // 如果用户账号重复
 if (user1 != null) { 
 System.out.println(“重名：” +String.valueOf(ob.get(0)).trim());
 //向duplicate重名字符串里直接重名的Excel行数
 duplicate+=String.valueOf(ob.get(0)).trim()+",";
 row++; 
 } else {//格式验证通过
 user.setUserName(String.valueOf(ob.get(1)).trim());
 user.setAccount(String.valueOf(ob.get(2)).trim());
 user.setPassWord(String.valueOf(ob.get(3)).trim());
 user.setSex(String.valueOf(ob.get(4)).trim());
 user.setPhone(String.valueOf(ob.get(5)).trim());
 user.setAddress(String.valueOf(ob.get(6)).trim());
 user.setEmail(String.valueOf(ob.get(7)).trim());
 role.setRoleId(Integer.valueOf(String.valueOf(ob.get(8))));
 user.setRole(role);
 System.out.println(“user:”+user);
 //添加到用户集合汇总
 userList.add(user);
 } 
 }else {//格式错误
 row++;
 //向error字符串追加格式错误的行数
 error+=String.valueOf(ob.get(0)).trim()+",";
 }
 }
 //批量插入
 if(row==0) {//没有发生任何错误，执行批量添加
 System.out.println(“userList=”+userList);
 count = this.userMapper.insertUsers(userList);
 System.out.println(“count=” + count);
 }
 }catch (IOException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } catch (Exception e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 }

//去掉账号重复字符串的最后一个逗号
	if(duplicate.contains(",")) {
		duplicate=duplicate.substring(0,duplicate.length()-1);
	}
	//去掉账号错误字符串的最后一个逗号
	if(error.contains(",")) {
		error=error.substring(0,error.length()-1);
	}
	//重名
	map.put("duplicate", duplicate);
	//格式错误
	map.put("error", error);
	//成功个数
	map.put("success",String.valueOf(count));
	System.out.println("map="+map);
	return map;
}

Controller层

/**
 *  Excel批量导入数据，注册用户
 * @param file 文件
 * @param request request请求
 * @return 上传结果
 */
@RequestMapping(value = "/importExcel", produces = "text/html;charset=utf-8")
@ResponseBody
public String importExcel(MultipartFile file, HttpServletRequest request) {
	// 调用导入方法
	Map<String,String> map = userService.importExcel(file, request);
    JSONObject obj = new JSONObject();
    obj.put("list", map);
    String json = obj.toString();
	// 返回结果
	return json;
}

jsp

<form method="post" enctype="multipart/form-data" id="form1">
					<a class="btn btn-primary"  style="margin-top:-3px;" 
					href='${pageContext.request.contextPath }/user/download.action'>
						上传模板下载
					</a>
					<a href="javascript:;" class="a-upload" style="margin-left:10px;"> <input type="file"
						name="file" class="btn btn-primary" id="excelFile"  >Excel批量添加>></a> 
						<span style="display:inline-block;;height:40px;display:none;color: green;font-size:12px;" class="span_tip">999</span>
						<input type="button" id="fileForm"
						style="margin-top: -3px; margin-left: 10px;"
						class="btn btn-primary" value="上传">

				</form>

jquery

<script type="text/javascript">
	$(function(){
	//Excel批量上传
	$("#fileForm").click(function(){  
		   if(checkData()){  
		    	$.ajaxFileUpload({
		            url:"${pageContext.request.contextPath}/user/importExcel",
		            type:"post",
		            fileElementId :"excelFile",
		           // dateType:"json",
		            success:function (data) {
		            	var str = $(data).find("body").text();//获取返回的字符串
		                var json = $.parseJSON(str);//把字符串转化为json对象
		            	$.each(json,function(i,val){			            	
		            		if(val.success==null && val.duplicate==null && val.error==null || (val.success==0 
		            				&& val.duplicate==null && val.error==null)){//Java插入到mysql发生错误执行(避免其他不可控因素出现问题)
		            			alert("错误，请重新打开网页");
		            			return false;
		            		}
		            		else if(val.success!=0 && val.success!=null){ //成功时执行
		            			alert("成功导入"+val.success+"条数据");
		            			window.location.href="${pageContext.request.contextPath}/user/userList.action";
		            			return false;
		            		}else{//发生常规错误，我们把各个发生错误的行数以及类型添加到错误提示框
		            			$(".getWrong").css("display","block");
		            			var $duplicate = "";
		            			var $error="";
		            			if(val.duplicate!=null && val.duplicate!=""){
		            				$duplicate ="账号重复:请检查Excel第" +val.duplicate+"行(账号已存在)" +"</br><hr>";
		            			}
		            			if(val.error!=null && val.error!=""){
		            				$error="格式错误:请检查Excel第"+val.error+"行数据(数据格式错误)"+"</br><hr>";
		            			}
			            		$("#detailWrong").html($duplicate+$error);	            			
		            			return false;			            		
		            		}
	                       //alert(val.duplicate);		                                                                      
	                    }); 
		            },
		            error:function(erro){
		                console.log(erro);
		            }
		        });
		    }       
	 });