JAVA+maven利用Poi,ajaxUpload 实现Excel批量上传

最新推荐文章于 2022-04-04 16:32:06 发布

Luo_Tuo

最新推荐文章于 2022-04-04 16:32:06 发布

阅读量372

点赞数 1

文章标签： java spring mysql

本文链接：https://blog.csdn.net/Luo_Tuo/article/details/106252764

版权

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\dots$ )+")
&&(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);
		            }
		        });
		    }       
	 });

});

Luo_Tuo

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
JAVA+maven利用Poi,ajaxUpload 实现Excel批量上传

JAVA+maven实现Excel批量上传 ajaxUpload1.在pom.xml中添加的依赖。 <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>1.3.2</version> </dependency> <dependency> <grou
复制链接

扫一扫