Java POI的封装

加入以下依赖

	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi</artifactId>
	    <version>4.0.1</version>
	</dependency>
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-ooxml</artifactId>
	    <version>4.0.1</version>
	</dependency>
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-ooxml-schemas</artifactId>
	    <version>4.0.1</version>
	</dependency>

因为不确定每个单元格的类型，写一个方法来获取Excel个单元格的值

	public Object getValue(Cell cell){
		Object value=null;
		switch (cell.getCellType()) {
		case STRING:
			value=cell.getStringCellValue();
			break;
		case BOOLEAN:
			value=cell.getBooleanCellValue();
			break;
		case NUMERIC:
			if(DateUtil.isCellDateFormatted(cell)){
				value=cell.getDateCellValue();
			}else{
				value=cell.getNumericCellValue();
			}
			break;
		case FORMULA:
			value=cell.getCellFormula();
			break;
		default:
			break;
		}
		return value;
	}

批量导入数据到Ecel

	public <T>  List<T> excelImport(Class<T> c,String excelAdminPath,List<String>params) throws IOException, InstantiationException, IllegalAccessException, IllegalArgumentException, ParseException{
		List<T> result=new ArrayList<>();
		Workbook workbook=new XSSFWorkbook(excelAdminPath);
		Sheet sheet=workbook.getSheetAt(0);
		int count=sheet.getLastRowNum();
		Row row2=sheet.getRow(1);
		short lastCellNum2=row2.getLastCellNum();
		for(int j=0;j<lastCellNum2;j++){
			StringBuilder sb=new StringBuilder();
			Cell cell=row2.getCell(j);
			Object value=getValue(cell);
					sb.append(value+" ");
		}
		Field fields[]=c.getDeclaredFields();//反射获取所有属性
		SimpleDateFormat sdfm=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		for(int i=2;i<count+1;i++){
			Row row=sheet.getRow(i);
			short lastCellNum=row.getLastCellNum();
			T obj=c.newInstance();
			for(int j=0;j<lastCellNum;j++){
				Cell cell=row.getCell(j);
				Object value=getValue(cell);
				for(Field field:fields){
					ExcelAttr anno=field.getAnnotation(ExcelAttr.class);//获取ExcelAttr注解，注解在下面
					if(anno!=null){
						int sort=anno.sort();
						if(j==sort){
							field.setAccessible(true);
							if(field.getType().equals(Double.class)){
								field.set(obj, Double.parseDouble(value+""));
							}else if(field.getType().equals(Integer.class)){
								if(params.contains(field.getName())){
									System.out.println(value);
									double value2=(double) value;
									int value1=(int) value2;
									field.set(obj, value1);
								}else{
									field.set(obj, Integer.parseInt(value+""));
								}
							}else if(field.getType().equals(Date.class)){
								field.set(obj, sdfm.parse(value+""));
							}else{
								if(value instanceof Double){
									value=value+"";
								}
								field.set(obj, value);
							}
						}
					}
				}
				
			}
			result.add(obj);
		}
		return result;
	}

ExcelAttr注解

@Retention(RetentionPolicy.RUNTIME)//运行期
@Target(ElementType.FIELD)//该注解使用在字段上
public @interface ExcelAttr {
	int sort();
}

此次POI操作的类Admin

public class Admin implements Serializable {
    /**
	 * 
	 */
	private static final long serialVersionUID = -2981532790465030915L;

	/**
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column admin.id
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
	
	@ExcelAttr(sort=0)
	private Integer id;

    @Override
	public String toString() {
		return "Admin [id=" + id + ", username=" + username + ", password=" + password + ", realname=" + realname
				+ ", email=" + email + ", createtime=" + createtime + "]";
	}

	/**
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column admin.username
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    @ExcelAttr(sort=1)
    private String username;

    /**
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column admin.password
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    @ExcelAttr(sort=2)
    private String password;

    /**
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column admin.realname
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    @ExcelAttr(sort=3)
    private String realname;

    /**
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column admin.email
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    @ExcelAttr(sort=4)
    private String email;

    /**
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column admin.createtime
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    @ExcelAttr(sort=5)
    private Date createtime;

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column admin.id
     *
     * @return the value of admin.id
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    public Integer getId() {
        return id;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column admin.id
     *
     * @param id the value for admin.id
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column admin.username
     *
     * @return the value of admin.username
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    public String getUsername() {
        return username;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column admin.username
     *
     * @param username the value for admin.username
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    public void setUsername(String username) {
        this.username = username == null ? null : username.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column admin.password
     *
     * @return the value of admin.password
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    public String getPassword() {
        return password;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column admin.password
     *
     * @param password the value for admin.password
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    public void setPassword(String password) {
        this.password = password == null ? null : password.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column admin.realname
     *
     * @return the value of admin.realname
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    public String getRealname() {
        return realname;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column admin.realname
     *
     * @param realname the value for admin.realname
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    public void setRealname(String realname) {
        this.realname = realname == null ? null : realname.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column admin.email
     *
     * @return the value of admin.email
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    public String getEmail() {
        return email;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column admin.email
     *
     * @param email the value for admin.email
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    public void setEmail(String email) {
        this.email = email == null ? null : email.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column admin.createtime
     *
     * @return the value of admin.createtime
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    public Date getCreatetime() {
        return createtime;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column admin.createtime
     *
     * @param createtime the value for admin.createtime
     *
     * @mbggenerated Wed Dec 25 16:21:51 GMT+08:00 2019
     */
    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }
}

导出数据到Excel

public <T>  void  exportExcel  (Class<T> c,List<T> list,String title,Map<Integer, Integer> sheelStyle,String memberExcelPath) throws IOException, IllegalArgumentException, IllegalAccessException{
		Field[] fieldss = c.getDeclaredFields();
		Field[] fields=new Field[fieldss.length-1];
		for(int i=1;i<fieldss.length;i++){
			fields[i-1]=fieldss[i];
		}
	
		int count=fields.length;
	
		// 创建一个07的工作簿
		Workbook workbook = new XSSFWorkbook();
		// 创建一个sheet
		Sheet sheet = workbook.createSheet(title);
		int size=list.size();
	
		Row row2=sheet.createRow(0);
		Cell cell2=row2.createCell(0);
		for(Entry<Integer, Integer> entry:sheelStyle.entrySet()){
			sheet.setColumnWidth(entry.getKey(), entry.getValue());
		}
		CellRangeAddress address=new CellRangeAddress(0, 0, 0, 6);
	
		sheet.addMergedRegion(address);
		cell2.setCellValue(title);
		Font font2=workbook.createFont();
		font2.setFontName("宋体正文");
		font2.setBold(true);
		CellStyle cellStyle2=workbook.createCellStyle();
	
		cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
		cellStyle2.setAlignment(HorizontalAlignment.CENTER);//水质居中
	
		cellStyle2.setFont(font2);
		cell2.setCellStyle(cellStyle2);
		Row row=sheet.createRow(1);
		for(int i=0;i<count;i++){
		
			//设置在第几列，从0开始
			Cell cell=row.createCell(i);
			//设置单元格的值
			cell.setCellValue(fields[i].getName());
			/**
			 * 设置边框样式
			 */
			CellStyle cellStyle=workbook.createCellStyle();
			//设置边框
			cellStyle.setBorderTop(BorderStyle.DOUBLE);
			cellStyle.setBorderBottom(BorderStyle.DOTTED);
			cellStyle.setBorderLeft(BorderStyle.DOTTED);
			cellStyle.setBorderRight(BorderStyle.DOTTED);
			
			//设置字体
			Font font=workbook.createFont();
			font.setFontName("宋体正文");
			cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
			cellStyle.setAlignment(HorizontalAlignment.CENTER);//水质居中
			cellStyle.setFont(font);
			cell.setCellStyle(cellStyle);
		}
		
		SimpleDateFormat sdfm=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		for (int i = 2; i < size + 2; i++) {
			Row row1 = sheet.createRow(i);
			sheet.setColumnWidth(i, 25 * 256);
			for (int j = 0; j < count; j++) {
				// 设置单元格的值
				Cell cell = row1.createCell(j);
				for(Field f:fields){
					T t = list.get(i - 2);
					ExcelAttr anno=f.getAnnotation(ExcelAttr.class);
					if(anno!=null){
						int sort=anno.sort();
						if(sort==j){
							f.setAccessible(true);
							if(!f.getType().equals(Date.class)){
								cell.setCellValue(f.get(t)+"");
								
							}else{
								cell.setCellValue(sdfm.format(f.get(t)));
							}
						}
					}
				}
			
				/**
				 * 设置边框样式
				 */
				CellStyle cellStyle = workbook.createCellStyle();
				// 设置边框
				cellStyle.setBorderTop(BorderStyle.DOUBLE);
				cellStyle.setBorderBottom(BorderStyle.DOTTED);
				cellStyle.setBorderLeft(BorderStyle.DOTTED);
				cellStyle.setBorderRight(BorderStyle.DOTTED);

				// 设置字体
				Font font = workbook.createFont();
				font.setFontName("宋体正文");
				cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 设置垂直居中
				cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水质居中
				cellStyle.setFont(font);
				cell.setCellStyle(cellStyle);
			}
		}

		FileOutputStream outputStream = new FileOutputStream(memberExcelPath);
		workbook.write(outputStream);
		outputStream.close();
	}

Controller调用

	@Resource
	private ExcelUtil excelUtil;
	@RequestMapping("/downadmin")
	@ResponseBody
	public Result download() {
		Result result = new Result();
		try {
			List<Admin> admins=as.findAll();
			Map<Integer, Integer> sheelStyle=new HashMap<Integer, Integer>();
			sheelStyle.put(0, 10*256);
			sheelStyle.put(1, 25*256);
			sheelStyle.put(2, 45*256);
			sheelStyle.put(3, 25*256);
			sheelStyle.put(4, 25*256);
			sheelStyle.put(5, 35*256);
			excelUtil.exportExcel(Admin.class, admins, "管理员信息表", sheelStyle, BASE_PATH+"/excel/admin.xlsx");
			result.setCode(200);
			result.setMessage("excel/admin.xlsx");
		} catch (RuntimeException e) {
			e.printStackTrace();
			result.setCode(500);
			result.setMessage("下载失败！");
		} catch (IllegalAccessException e) {
			result.setCode(500);
			// TODO Auto-generated catch block
			result.setMessage("下载失败！");
			e.printStackTrace();
		} catch (IOException e) {
			result.setCode(500);
			// TODO Auto-generated catch block
			result.setMessage("下载失败！");
			e.printStackTrace();
		}
		return result;
	}
	
	@RequestMapping("/importadmin")
	@ResponseBody
	public Result upload(@RequestParam("file1")MultipartFile file) {
		Result result = new Result();
		String filename=file.getOriginalFilename();
		filename=UUID.randomUUID().toString().substring(0,8)+filename;
		File dest=new File(BASE_PATH+"/excel/"+filename);
		try {
			file.transferTo(dest);
			List<String> params=new ArrayList<>();
			params.add("id");
			List<Admin> admins=excelUtil.excelImport(Admin.class, BASE_PATH+"/excel/"+filename, params);
			as.insertAdmin(admins);
			result.setCode(200);
			result.setMessage("上传成功");
		} catch (RuntimeException e) {
			e.printStackTrace();
			result.setCode(500);
			result.setMessage("上传失败！");
		} catch (IllegalAccessException e) {
			result.setCode(500);
			// TODO Auto-generated catch block
			result.setMessage("上传失败！");
			e.printStackTrace();
		} catch (IOException e) {
			result.setCode(500);
			// TODO Auto-generated catch block
			result.setMessage("上传失败！");
			e.printStackTrace();
		} catch (InstantiationException e) {
			result.setCode(500);
			// TODO Auto-generated catch block
			result.setMessage("上传失败！");
			e.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}