Excel数据解析数据去重

最新推荐文章于 2023-01-16 17:53:26 发布

cailizhu833

最新推荐文章于 2023-01-16 17:53:26 发布

阅读量928

点赞数

本文链接：https://blog.csdn.net/cailizhu833/article/details/88787913

版权

1.java代码

    @RequestMapping("/parseExcel")
    public @ResponseBody JSONObject importExcelPeople(HttpServletRequest req,@RequestParam(value="file",required=false)MultipartFile multipartFile)  {
    	JSONObject json = new JSONObject();
    	StringBuffer stringBuffer=new StringBuffer();
    	String personId=null;
    	List<Map<String,Object>> toDepts =null;
    	Map<String,String> depts = new HashMap<String,String>();
    	try {
    		req.setCharacterEncoding("utf-8");
    		HttpSession session = req.getSession();
    		//获取session缓存的用户对象
    		User user = (User) session.getAttribute("user");
    		/*
    		 * 单点登录后，session会保存员工编号
    		 */
    		personId = user.getUserName();      
    	} catch (Exception e) {
    		// TODO: handle exception
    	}
    	
    	
    	Workbook book = ExcelUtils.getWorkBook(multipartFile);
    	Sheet sheet = book.getSheetAt(0);
    	for (Row row : sheet) {
    		// 一行数据 对应 一个区域对象
    		if (row.getRowNum() == 0) {
    			// 第一行 跳过
    			continue;
    		}
    		// 跳过空行
    		if (row.getCell(0) == null
    				|| StringUtils.isEmpty(row.getCell(0).toString())) {
    			continue;
    		}
    		
    		WorkOutPeople people = new WorkOutPeople();
    		row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
    		Map<String,Object> ehrPeople = new HashMap<String, Object>();
    		if(result.size() > 0) {
    			ehrPeople = result.get(0);
           此处加入你要解析的内容,使用row.getCell(0).toString()进行数据解析
    			peoples.add(people);
    		}else{
    			stringBuffer.append(row.getCell(0).toString()).append("\n");
    		}
    		
    	}
    	//自定义临时的集合listTemp 添加empNum
    	List listTemp = new ArrayList(); 
    	//自定义empnum
    	List listJSON = new ArrayList();
    	HashMap<Object, Integer> hashMap=new HashMap<>();
        //遍历集合并将empNum保存数据
    	for(int i=0;i<peoples.size();i++){  
            if(!listTemp.contains(peoples.get(i).getEmpNum())){  
                //如果empNum不为存在添加
            	listTemp.add(peoples.get(i).getEmpNum());  
                listJSON.add(peoples.get(i));
            }else{
            	//如果存在则进行保存数量操作
            	if(hashMap.containsKey(peoples.get(i).getEmpNum())){
            		//多次则加
            		hashMap.put(peoples.get(i).getEmpNum(), hashMap.get(peoples.get(i).getEmpNum())+1);
            	}else{
            		//第一次empNum 为1
            		hashMap.put(peoples.get(i).getEmpNum(),1);
            	}   	          	
            }  
        }
    
        json.put("Total", JSONArray.toJSONString(hashMap));	
    	json.put("peoples",JSONArray.toJSONString(listJSON));
    	json.put("userLess", stringBuffer);
    	//json.put("msg", "undefined");
    	return json;
    }

工具类

import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

/**
 * 帮助Excel读写的工具类
 * 
 * @author fuyongdi
 * @date 2018年1月9日 上午10:39:35
 * @since JDK 1.7
 */
public class ExcelUtils {

	/**
	 * 日志
	 */
	private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

	/**
	 * excel2003的格式名
	 */
	public static final String EXCEL_2003 = "xls";

	/**
	 * excel2007的格式名
	 */
	public static final String EXCEL_2007 = "xlsx";

	
	/**  
	 * <p>Title: checkFile</p>  
	 * <p>Description: 检查传输进来的文件是否合法</p>  
	 * @param file  
	 */  
	public static void checkFile(MultipartFile file) {

		if (null == file) {
			logger.error("文件不存在！");
		}

		String fileName = file.getOriginalFilename();

		if (!fileName.endsWith(EXCEL_2003) && !fileName.endsWith(EXCEL_2007)) {
			logger.error(fileName + "不是合法的excel文件");
		}
	}

	/**
	 * 通过传递进来的MultipartFile获取Workbook 
	 * 根据不同类型的excel返回不同类型的Workbook
	 * @param myfile
	 * @return
	 */
	public static Workbook getWorkBook(MultipartFile myfile) {

		checkFile(myfile);

		//获取文件名
		String fileName = myfile.getOriginalFilename();

		Workbook workbook = null;

		InputStream is = null;
		try{
			is = myfile.getInputStream();//读取文件内容
			if (fileName.endsWith(EXCEL_2003)) {
				workbook = new HSSFWorkbook(is);
			} else if (fileName.endsWith(EXCEL_2007)) {
				workbook = new XSSFWorkbook(is);
			}

		} catch (IOException e) {
			logger.info("建立Workbook出错");
		} finally{
			if(is != null){
				try {
					is.close();//关闭文件流
				} catch (IOException e) {
					logger.error(e.getMessage());
				}
			}
		}

		return workbook;
	}

	/**
	 * 将Excel的Cell转为String格式并取出Cell中的内容 
	 * 用来防止取出Excel中的长数字时出错
	 * @param row
	 * @param col
	 * @return
	 */
	public static String getCellStringInfo(Row row, int col) {
		String cellValue = "";
		Cell cell = row.getCell(col);

		if (cell == null) {
			return cellValue;
		}

		cell.setCellType(Cell.CELL_TYPE_STRING);
		cellValue = cell.getStringCellValue();
		return cellValue;
	}
}

springmvc.xml配置文件

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
    <property name="maxUploadSize" value="10485760"/>
    <property name="defaultEncoding" value="UTF-8"/>
</bean>

4.js代码

function importExp(){
	//获取每一个tr
	var tr =$('#sdf_list').find('tr');
	//获取uploadexcel的id
	
    var form = document.getElementById('uploadexcel'),
    //初始化变量
    formData = new FormData(form);
    var name = $("#upfile").val();
    //对应workNum的获取值
    var worknum1 = $("#workNum").val();
    //进行值的添加string
    formData.append("file",$("#upfile")[0].files[0]);
    formData.append("name",name);

	
    //对应excel的
    $.ajax({
        url : '../../../xx/xxe.do',
        type : 'POST',
        async : true,
        data : formData,
        // 告诉jQuery不要去处理发送的数据
        processData : false,
        // 告诉jQuery不要去设置Content-Type请求头
        contentType : false,
        beforeSend:function(){
            //console.log("正在进行，请稍候");
        },
        success: function(data){

        },
        error:function(data) {
            alert("导入失败"); 
        },
    }); 
}