Excel 批量导入

最新推荐文章于 2024-07-30 23:45:03 发布

侠道

最新推荐文章于 2024-07-30 23:45:03 发布

阅读量1.2k

点赞数

分类专栏： JAVA项目实例

本文链接：https://blog.csdn.net/qq_36107346/article/details/88393093

版权

JAVA项目实例专栏收录该内容

4 篇文章 0 订阅

订阅专栏

目录标题

1.最终的效果图
2.开发步骤

1.最终的效果图

在这里插入图片描述

2.开发步骤

2.1 环境配置

环境	版本
Eclipse	2018-12 (4.10.0)
poi	3.14
poi-ooxml	3.14

2.2 POM 文件的引入

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->  
<dependency>  
    <groupId>org.apache.poi</groupId>  
    <artifactId>poi</artifactId>  
    <version>3.14</version>  
</dependency>  
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->  
<dependency>  
    <groupId>org.apache.poi</groupId>  
    <artifactId>poi-ooxml</artifactId>  
    <version>3.14</version>  
</dependency>

2.3 web开发

<script type="text/javascript">
	function uploadFile() {

		 if($("#userUploadFile").val() == ""){
			 alert("请选择要上传的文件");
		}else{

		$("#uploadForm").form("submit", {
			success : function(data) {
				var obj = eval('(' + data + ')');
				if (obj.error == 0) {
					
					//插入有误信息
 					var errorStr=obj.errorDesc;
					
					if(errorStr.indexOf("**") != -1){
						for (var i=0;i<100;i++)
						{
							errorStr= errorStr.replace('**','\n');
						}
						$("#err_e").css("display","block");
						$("#err_info").css("display","block");
						$("#err_info").text(String(errorStr));
					}else if(errorStr !=""){
						$("#err_e").css("display","block");
						$("#err_info").css("display","block");
						$("#err_info").text(String(errorStr));
					}else{
						$("#err_e").css("display","none");
		 				$("#err_info").css("display","none");
					}
					
					//插入成功信息
					var successStr=obj.successDesc;
					if(successStr.indexOf("**") != -1){
						for (var i=0;i<100;i++)
						{
							successStr= successStr.replace('**','\n');
						}
						$("#err_s").css("display","block");
						$("#success_info").css("display","block");
						$("#success_info").text(String(successStr));
					}else{
						$("#err_s").css("display","none");
		 				$("#success_info").css("display","none");
					}
					
					return ;
					
					
					/* parent.showWarning(obj.desc);  */
					/* location.reload(); */
					 
				}
			},
			error : function(e) {
				$.messager.alert("系统提示", "操作失败。");
			}

		});
		}
	}
</script>

<body>

	<div>
		<form id="uploadForm"
			action="h8h/gpm/partnerEmployee.action?action=excelBatchImport"
			method="post" enctype="multipart/form-data">
			<table cellpadding="0" cellspacing="0" border="0"
				style="width: 600px; margin: 20px auto; text-algin: left;">

				<tr>
					<td colspan="4"><input id="dyId" type="hidden" /></td>
				</tr>
				<tr>
					<td colspan="3"><a
						href="/gpm/downloadExcel/employeeTemplate.xls" id="downloadModel"
						name="downloadModel" style="margin-left: 7px;"><u>点击下载人员模板</u></a>
				</tr>
				<tr>
					<td colspan="4"><br /></td>
				</tr>
				<tr>
					<td colspan="3"><input type="file" id="userUploadFile"
						name="model.userUploadFile" style="width: 200px;"></td>
				</tr>
				<!-- background:url('/center/images/uploadImg.png') no-repeat 0px 10px; -->
				<tr>
					<td colspan="4"><br /></td>
				</tr>
				<tr>
					<td colspan="4" style="text-align: center;">
						<hr style="width: 720px; border-width: 0.3px; margin-left: -10px;">
						<a href="javascript:void(0)" class="easyui-linkbutton"
						iconCls="silk-execl" onclick="uploadFile()">上传</a>
					</td>
				</tr>

			</table>
		</form>
	</div>
	<div>

<table style="width: 300px;">
<tr>

<td>
<span id="err_e"
			style="width: 360px; display: none; color: red;text-align:center;" >录入有误提示：</span>
</td>

	<td>
<span id="err_s"
			style="width: 360px; display: none; color: blue;text-align:center;">录入成功提示：</span>
</td>	

		
		</tr>
		<tr>
		<td>
		<textarea id="err_info" rows="12" cols="" style="width: 360px; display: none; color: red;text-align:center;">
		
		</textarea>
		</td>
		<td>
		<textarea id="success_info" rows="12" cols="" style="width: 360px; display: none; color: blue;text-align:center;">
		
		</textarea>
		</td>
		
		
		</tr>
</table>
	</div>

</body>

2.4 服务端开发

    private static final long serialVersionUID = -3456781L;
	private PartnerEmployeeForm model = new PartnerEmployeeForm();

	@Override
	public BaseObject getObject() {
		// TODO Auto-generated method stub
		return model;
	}

	/**
	 * EXCEL 批量导入员工账号
	 */
	public void excelBatchImport() throws Exception {
		//实例化从Excel接收数据的集合	
		List<PartnerEmployeeForm> listEmployee = new ArrayList<PartnerEmployeeForm>();
		//实例化返回map
		Map<String, String> resMap = new HashMap<String, String>(2);
		resMap.put("error", "0");
		StringBuffer errorBuffer = new StringBuffer();
		StringBuffer successBuffer = new StringBuffer();
		boolean IsTemplate=false;
		
		
		//判断是否是传输错误
		if (model.getUserUploadFile()==null) {
			errorBuffer.append("网络传输参数错误**");
		}else {
		// EXCEL 数据解析
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(model.getUserUploadFile()));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet hssfSheet = wb.getSheetAt(0); // 获取第一个Sheet页
		if (hssfSheet != null) {
			
			HSSFRow hssfRow0 = hssfSheet.getRow(0);
			String str0=formatCell(hssfRow0.getCell((short) 0));
			String str1=formatCell(hssfRow0.getCell((short) 1));
			String str2=formatCell(hssfRow0.getCell((short) 2));
			String str3=formatCell(hssfRow0.getCell((short) 3));
			String str4=formatCell(hssfRow0.getCell((short) 4));
			String str5=formatCell(hssfRow0.getCell((short) 5));
			
			if (str0.trim().equals("所属公司") &&
					str1.trim().equals("所属部门") &&
					str2.trim().equals("员工姓名") &&
					str3.trim().equals("手机号码") &&
					str4.trim().equals("初始密码") &&
					str5.trim().equals("收款编码")) {

			//从第二行开始遍历
			for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow == null) {
					continue;
				}else if (hssfRow.getCell((short) 0)==null &&
						hssfRow.getCell((short) 1)==null &&
						hssfRow.getCell((short) 2)==null &&
						hssfRow.getCell((short) 3)==null &&
						hssfRow.getCell((short) 4)==null &&
						hssfRow.getCell((short) 5)==null ) {
					continue;
				} 

				//把遍历的数据写入 PartnerEmployeeForm 对象中
				PartnerEmployeeForm mmodel = new PartnerEmployeeForm();
			    
				mmodel.setCompanyName(formatCell(hssfRow.getCell((short) 0)));
				mmodel.setDepartmentName(formatCell(hssfRow.getCell((short) 1)));
				mmodel.setName(formatCell(hssfRow.getCell((short) 2)));
				mmodel.setAccountId(formatCell(hssfRow.getCell((short) 3)));
				mmodel.setPassword(formatCell(hssfRow.getCell((short) 4)));
				mmodel.setReceiptCodeNo(formatCell(hssfRow.getCell((short) 5)));
				
				//公司Id的填入
				if (mmodel.getCompanyName()!=null) {
					long companyId=companyService.getpartnerCompanyId(mmodel.getCompanyName());
					if (companyId>0) {
						mmodel.setCompanyId(companyId);
					}
				}
				
				//部门Id的填入
				if (mmodel.getDepartmentName()!=null) {
					long departmentId=departmetService.getIdByDepartmentName(mmodel.getDepartmentName());
					if (departmentId>0) {
						mmodel.setDepartmentId(departmentId);
					}
				}
			
				listEmployee.add(mmodel);
			}
			//行号
			int i=1;
			
			//批量新增员工账户逻辑
			for (PartnerEmployeeForm from : listEmployee) {
				i++;
				
				if (from.getCompanyId() == null || from.getAccountId() == null || from.getName() == null
						|| from.getPassword() == null) {
					
					errorBuffer.append("【第 "+i+" 行】"+"参数错误**");
				} else {
					int count = service.getEmployeeAccountIdTotal(from);
					if (count > 0) {

						errorBuffer.append("【第 "+i+" 行】"+from.getAccountId()+"账号已存在**");
					} else {
						int n_count = service.getEmployeeNameTotal(from);
						if (n_count > 0) {

							errorBuffer.append("【第 "+i+" 行】"+from.getName()+"该姓名已存在**");
						} else {
							from.setPassword(MD5Util.MD5(from.getPassword()));
							from.setCreator(model.getUserInfo().getUserName());
							long id = service.saveEmployee(from);
							if (id > 0) {

								successBuffer.append("【第 "+i+" 行】"+from.getAccountId()+"账号新增成功**");
							} else {

								errorBuffer.append("【第 "+i+" 行】"+from.getAccountId()+"账号新增失败**");
							}
						}
					}
				}	
			}
		}else {
			IsTemplate=true;
			errorBuffer.append("模板有误，请下载指定人员模板。");
		}
		}
		}
		
		if (listEmployee.size()==0 && IsTemplate==false) {
			errorBuffer.append("数据录入不能为空，导入模板失败。");
		}

		resMap.put("errorDesc", errorBuffer.toString());
		resMap.put("successDesc", successBuffer.toString());
		ResponseUtil.writeJson(response, resMap);

	}

	@SuppressWarnings("deprecation")
	public static String formatCell(HSSFCell hssfCell){
		   String cellValue = "";
		   if (hssfCell == null) {
				return null;
		   }
		        
		   DecimalFormat decimalFormat = new DecimalFormat("#");
		   //根据自己的情况进行类型添加
		   switch (hssfCell.getCellType()) {
			 case HSSFCell.CELL_TYPE_NUMERIC:               
		       cellValue=decimalFormat.format(hssfCell.getNumericCellValue()).toString().trim();
						break;
			 case HSSFCell.CELL_TYPE_STRING:
				cellValue = hssfCell.getStringCellValue().toString().trim();
					break;
				}
				return cellValue;
	}

public class PartnerEmployeeForm extends BaseObject{

	private static final long serialVersionUID = -123456L;

	private Long id;
	private Long companyId;           //公司ID
	private String companyName;       //公司名称
	private Long departmentId; // 部门id
	private String departmentName; //部门名称
	private String name;              //姓名
	private String mobile;            //手机号码
	private String accountId;         //登陆账号
	private String password;          //密码
	private Integer status;           //状态（1-有效，0-冻结）
	private String creator;           //创建人

	
	private File userUploadFile;

	public File getUserUploadFile() {
		return userUploadFile;
	}

	public void setUserUploadFile(File userUploadFile) {
		this.userUploadFile = userUploadFile;
	}
	....get/set....
	}