java导入excel

导入excel

layui框架

要导入的表格是有规则的 如:

HTML

<a style="margin-right:11px" class="layui-btn layui-btn-primary" id="uploadExcel"><i class="layui-icon">&#xe67c;</i>导入学生</a>

js

  upload.render({ //允许上传的文件后缀
		    elem: '#uploadExcel',
		    url: 'student/uploadExcel.do',
		    accept: 'file', //普通文件
		    ext : 'xls|xlsx',
		    title : '请选择Excel文件',
	  		before: function(obj){ //obj参数包含的信息，跟 choose回调完全一致，可参见上文。
	  			layer.load(); //上传loading
	  		},
	  		done: function(res, index, upload){
		    	layer.closeAll('loading'); //关闭loading
		    	if(res.code == 1){
		    		`这里写代码片`top.layer.msg(res.msg, {icon: 6});
		    	}else{
		    		top.layer.msg(res.msg, {icon: 5});
		    	}
		    },
		    error: function(){
		        //请求异常回调
		    }
		  });

控制层

@ControllerOptLog(desc="导入")
	@RequestMapping(value = "uploadExcel")
	@ResponseBody
	public JsonBean uploadExcel(@RequestParam MultipartFile file) throws IOException {
		return studentService.insertExcelToDB(file);
	}

业务层

	@SuppressWarnings("finally")
	@Override
	public JsonBean insertExcelToDB(MultipartFile file) {
	    //JsonBean  封装的对象
		JsonBean jsonBean = new JsonBean(0, "Excel导入失败！", null);
		//读取Excel数据到List中  
		try {
			List<ArrayList<String>> list = new ExcelRead().readExcel(file);
			//list中存的就是excel中的数据，可以根据excel中每一列的值转换成你所需要的值（从0开始） 
			Student student = null;  
		    List<Student> liseStudent = new ArrayList<Student>();  
		    for(ArrayList<String> arr : list){    
		       //按照表格的规则循环添加
		    	if(arr.get(0)!=null&&!arr.get(0).toString().equals("")){
		    		student = new Student();    
		    		student.setName(arr.get(0).toString());
		    		if(arr.get(1).toString().equals("男")){
		    			student.setSex("1");
		    		}else if(arr.get(1).toString().equals("女")){
		    			student.setSex("0");
		    		}
		    		student.setBirthday(arr.get(2));
		    		student.setAddress(arr.get(3).toString());
		    		student.setFather_name(arr.get(4).toString());
		    		student.setFather_phone(arr.get(5).toString());
		    		student.setMather_name(arr.get(6).toString());
		    		student.setMather_phone(arr.get(7).toString());
		    		student.setPhone(arr.get(8).toString());
		    		student.setEducation(arr.get(9).toString());
		    		student.setAdd_time(arr.get(10));
		    		student.setStatus(arr.get(11).toString());
		    		student.setClassnum(arr.get(12).toString());
		    		student.setAccommodation(Integer.valueOf(arr.get(13).toString()));
		    		student.setRemake(arr.get(14).toString());
		    		
		    		liseStudent.add(student); 
		    		
		    	}
		    }
		    int i = studentMapper.insertStudentList(liseStudent);
		    if(i>0){
		    	jsonBean = new JsonBean(1, "Excel导入成功！", null);
		    }
		} catch (Exception e) {
			jsonBean = new JsonBean(0, "请查看Excel格式是否正确！", null);
			e.printStackTrace();
		}  finally{
			return jsonBean;
		}
	}

下面就是导入要用到的工具类了

package xxx.xxx.xxx.utils;
  
public class ExcelRead {      
    public int totalRows; //sheet中总行数  
    public static int totalCells; //每一行总单元格数  
    /** 
     * read the Excel .xlsx,.xls 
     * @param file jsp中的上传文件 
     * @return 
     * @throws IOException  
     */  
    public List<ArrayList<String>> readExcel(MultipartFile file) throws IOException {  
        if(file==null||ExcelUtil.EMPTY.equals(file.getOriginalFilename().trim())){  
            return null;  
        }else{  
            String postfix = ExcelUtil.getPostfix(file.getOriginalFilename());  
            if(!ExcelUtil.EMPTY.equals(postfix)){  
                if(ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){  
                    return readXls(file);  
                }else if(ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){  
                    return readXlsx(file);  
                }else{                    
                    return null;  
                }  
            }  
        }  
        return null;  
    }  
    /** 
     * read the Excel 2010 .xlsx 
     * @param file 
     * @param beanclazz 
     * @param titleExist 
     * @return 
     * @throws IOException  
     */  
    public List<ArrayList<String>> readXlsx(MultipartFile file){  
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();  
        // IO流读取文件  
        InputStream input = null;  
        XSSFWorkbook wb = null;  
        ArrayList<String> rowList = null;  
        try {  
            input = file.getInputStream();  
            // 创建文档  
            wb = new XSSFWorkbook(input);                         
            //读取sheet(页)  
            for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){  
                XSSFSheet xssfSheet = wb.getSheetAt(numSheet);  
                if(xssfSheet == null){  
                    continue;  
                }  
                totalRows = xssfSheet.getLastRowNum();                
                //读取Row,从第二行开始  
                for(int rowNum = 1;rowNum <= totalRows;rowNum++){  
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);  
                    if(xssfRow!=null){  
                        rowList = new ArrayList<String>();  
                        totalCells = xssfRow.getLastCellNum();  
                        //读取列，从第一列开始  
                        for(int c=0;c<=totalCells+1;c++){  
                            XSSFCell cell = xssfRow.getCell(c);  
                            if(cell==null){  
                                rowList.add(ExcelUtil.EMPTY);  
                                continue;  
                            }                             
                            rowList.add(ExcelUtil.getXValue(cell).trim());  
                        }     
                    list.add(rowList);                                            
                    }  
                }  
            }  
            return list;  
        } catch (IOException e) {             
            e.printStackTrace();  
        } finally{  
            try {  
                input.close();  
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
        }  
        return null;  
          
    }  
    /** 
     * read the Excel 2003-2007 .xls 
     * @param file 
     * @param beanclazz 
     * @param titleExist 
     * @return 
     * @throws IOException  
     */  
    public List<ArrayList<String>> readXls(MultipartFile file){   
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();  
        // IO流读取文件  
        InputStream input = null;  
        HSSFWorkbook wb = null;  
        ArrayList<String> rowList = null;  
        try {  
            input = file.getInputStream();  
            // 创建文档  
            wb = new HSSFWorkbook(input);                         
            //读取sheet(页)  
            for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){  
                HSSFSheet hssfSheet = wb.getSheetAt(numSheet);  
                if(hssfSheet == null){  
                    continue;  
                }  
                totalRows = hssfSheet.getLastRowNum();                
                //读取Row,从第二行开始  
                for(int rowNum = 1;rowNum <= totalRows;rowNum++){  
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);  
                    if(hssfRow!=null){  
                        rowList = new ArrayList<String>();  
                        totalCells = hssfRow.getLastCellNum();  
                        //读取列，从第一列开始  
                        for(short c=0;c<=totalCells+1;c++){  
                            HSSFCell cell = hssfRow.getCell(c);  
                            if(cell==null){  
                                rowList.add(ExcelUtil.EMPTY);  
                                continue;  
                            }                             
                            rowList.add(ExcelUtil.getHValue(cell).trim());  
                        }          
                        list.add(rowList);  
                    }                     
                }  
            }  
            return list;  
        } catch (IOException e) {             
            e.printStackTrace();  
        } finally{  
            try {  
                input.close();  
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
        }  
        return null;  
    }  
}  

package xxx.xxx.xxx.utils;

public class ExcelUtil {  
    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";  
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";  
    public static final String EMPTY = "";  
    public static final String POINT = ".";  
    public static SimpleDateFormat sdf =   new SimpleDateFormat("yyyy/MM/dd");  
    /** 
     * 获得path的后缀名 
     * @param path 
     * @return 
     */  
    public static String getPostfix(String path){  
        if(path==null || EMPTY.equals(path.trim())){  
            return EMPTY;  
        }  
        if(path.contains(POINT)){  
            return path.substring(path.lastIndexOf(POINT)+1,path.length());  
        }  
        return EMPTY;  
    }  
    /** 
     * 单元格格式 
     * @param hssfCell 
     * @return 
     */  
    @SuppressWarnings({ "static-access", "deprecation" })  
    public static String getHValue(HSSFCell hssfCell){  
         if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {  
             return String.valueOf(hssfCell.getBooleanCellValue());  
         } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {  
             String cellValue = "";  
             if(HSSFDateUtil.isCellDateFormatted(hssfCell)){                  
                 Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());  
                 cellValue = sdf.format(date);  
             }else{  
                 DecimalFormat df = new DecimalFormat("#.##");  
                 cellValue = df.format(hssfCell.getNumericCellValue());  
                 String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());  
                 if(strArr.equals("00")){  
                     cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));  
                 }    
             }  
             return cellValue;  
         } else {  
            return String.valueOf(hssfCell.getStringCellValue());  
         }  
    }  
    /** 
     * 单元格格式 
     * @param xssfCell 
     * @return 
     */  
    public static String getXValue(XSSFCell xssfCell){  
         if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {  
             return String.valueOf(xssfCell.getBooleanCellValue());  
         } else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {  
             String cellValue = "";  
             if(XSSFDateUtil.isCellDateFormatted(xssfCell)){  
                 Date date = XSSFDateUtil.getJavaDate(xssfCell.getNumericCellValue());  
                 cellValue = sdf.format(date);  
             }else{  
                 DecimalFormat df = new DecimalFormat("#.##");  
                 cellValue = df.format(xssfCell.getNumericCellValue());  
                 String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());  
                 if(strArr.equals("00")){  
                     cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));  
                 }    
             }  
             return cellValue;  
         } else {  
            return String.valueOf(xssfCell.getStringCellValue());  
         }  
    }   
 
	static class XSSFDateUtil extends DateUtil{  
	    protected static int absoluteDay(Calendar cal, boolean use1904windowing) {    
	        return DateUtil.absoluteDay(cal, use1904windowing);    
	    }   
	}
}

另外这是我的第二篇博客

jsonBean是我们封装的一个返回消息的类

package xxx.utils;

/**
 * @fileName 封装对象
 * @author xxx
 * @creteTime xxx
 */
public class JsonBean {

	int code;// 状态码

	String msg;// 提示消息

	Object data;// 返回的内容

	long count;// 总记录数

	public JsonBean() {

	}

	public JsonBean(Object data) {
		this.data = data;
	}

	public JsonBean(long count, Object data) {
		this.data = data;
		this.count = count;
	}

	public JsonBean(int code, String msg, Object data) {
		this.code = code;
		this.msg = msg;
		this.data = data;
	}

	public JsonBean(int code, String msg, Object data, long count) {
		super();
		this.code = code;
		this.msg = msg;
		this.data = data;
		this.count = count;
	}

	public JsonBean(Integer code, Object data, String msg) {
		super();
		this.code = code;
		this.data = data;
		this.msg = msg;
	}

	/**
	 * 设置没有权限返回值
	 * 
	 * @param auth
	 *            原值返回
	 * @return
	 */
	public boolean setNoAuth(boolean auth) {
		if (!auth) {
			this.data = null;
			this.setCode(Const.NO_AUTHORIZED);
			this.setMsg(Const.NO_AUTHORIZED_MSG);
		}
		return auth;
	}

	public Object getData() {
		return data;
	}

	public void setData(Object data) {
		this.data = data;
	}

	public int getCode() {
		return code;
	}

	public void setCode(int code) {
		this.code = code;
	}

	public String getMsg() {
		return msg;
	}

	public void setMsg(String msg) {
		this.msg = msg;
	}

	public long getCount() {
		return count;
	}

	public void setCount(long count) {
		this.count = count;
	}

	@Override
	public String toString() {
		return "JsonBean [code=" + code + ", msg=" + msg + ", data=" + data + ", count=" + count + "]";
	}
}