SpringBoot中如何进行Excel的导入和导出

首先，需要配置pox.xml文件中的依赖：

	<dependency>
		<groupId>commons-fileupload</groupId>
		<artifactId>commons-fileupload</artifactId>
		<version>1.3.1</version>
	</dependency>
	
	<!-- 集成lombok 框架 -->
	<dependency>
		<groupId>org.projectlombok</groupId>
		<artifactId>lombok</artifactId>
	</dependency>
	
	<!--阿里巴巴 fastjson -->
	<dependency>
		<groupId>com.alibaba</groupId>
		<artifactId>fastjson</artifactId>
		<version>1.2.30</version>
	</dependency>

	<dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>3.0.3</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>3.0.3</version> </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>3.0.3</version>
    </dependency>

然后，导入ExcelUtiles.java工具类（自建）：

package com.hfut.utiles;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
 
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

public class ExcelUtiles {
	public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,
            String fileName, boolean isCreateHeader, HttpServletResponse response){
					ExportParams exportParams = new ExportParams(title, sheetName);
					exportParams.setCreateHeadRows(isCreateHeader);
					defaultExport(list, pojoClass, fileName, response, exportParams);
					}
					
					public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,
					            HttpServletResponse response){
					defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
					}
					
					public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
					defaultExport(list, fileName, response);
					}
					
					private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName,
					               HttpServletResponse response, ExportParams exportParams) {
					Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
					if (workbook != null); downLoadExcel(fileName, response, workbook);
					}
					
					private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
					try {
					response.setCharacterEncoding("UTF-8");
					response.setHeader("content-Type", "application/vnd.ms-excel");
					response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
					workbook.write(response.getOutputStream());
					} catch (IOException e) {
					//throw new NormalException(e.getMessage());
					}
					}
					
					private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
					Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
					if (workbook != null);
					downLoadExcel(fileName, response, workbook);
					}
					
					public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
					if (StringUtils.isBlank(filePath)){
					return null;
					}
					ImportParams params = new ImportParams();
					params.setTitleRows(titleRows);
					params.setHeadRows(headerRows);
					List<T> list = null;
					try {
					list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
					}catch (NoSuchElementException e){
					//throw new NormalException("模板不能为空");
					} catch (Exception e) {
					e.printStackTrace();
					//throw new NormalException(e.getMessage());
					} return list;
					}
					
					public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
					if (file == null){ return null;
					}
					ImportParams params = new ImportParams();
					params.setTitleRows(titleRows);
					params.setHeadRows(headerRows);
					List<T> list = null;
					try {
					list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
					}catch (NoSuchElementException e){
					// throw new NormalException("excel文件不能为空");
					} catch (Exception e) {
					//throw new NormalException(e.getMessage());
					System.out.println(e.getMessage());
					}
					return list;
}
}

之后，配置Entity中的类：

package com.hfut.entity;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

import javax.persistence.*;
import java.util.Date;

@SuppressWarnings("unused")
@Entity
@Table(name = "seckill")
public class User {
	
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Excel(name="编号",orderNum="0")
	private int id;
	
	@Column(name="name")
	@Excel(name="姓名",orderNum="1")
	private String name;
	
	@Column(name="age")
	@Excel(name="年龄",orderNum="2")
	private int age;
	
	@Column(name="hobby")
	@Excel(name="爱好",orderNum="3")
	private String hobby;
	
	public User(){
		
	}
	public User(int id, String name, int age, String hobby) {
		super();
		this.id = id;
		this.name = name;
		this.age = age;
		this.hobby = hobby;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getHobby() {
		return hobby;
	}
	public void setHobby(String hobby) {
		this.hobby = hobby;
	}
	
	 @Override
	    public String toString() {
	        return "userExcel{" +
	                "id='" + id + '\'' +
	                ", name=" + name +
	                ", age=" + age +
	                ", hobby=" + hobby +
	                '}';
	    }
	
}

配置完成后，就是编写controller中的导入导出函数：

@GetMapping("/exportInfo")
    public void export(HttpServletResponse response) {
        System.out.println(1+"*/*/*/*/*/*/*/*/*/*/*/*");
//        模拟从数据库获取需要导出的数据
        List<User> list = (List<User>) userService.showAll();
//         导出操作
		ExcelUtiles.exportExcel(list, "测试名", "什么名字", User.class, "测试.xls", response);
 
    }
	
	@PostMapping("/importInfo")
    public String importInfo(@RequestParam("file") MultipartFile file) {
		System.out.println("开始导入人员信息");
        ImportParams importParams = new ImportParams();
        // 数据处理
        importParams.setHeadRows(1);
        importParams.setTitleRows(1);
 
        // 需要验证
        importParams.setNeedVerfiy(true);
 
        try {
            ExcelImportResult<User> result = ExcelImportUtil.importExcelMore(file.getInputStream(), User.class,
                    importParams);
 
            List<User> successList = result.getList();
            for (User user : successList) {
              System.out.println(user);
              boolean result1 = userService.selectById(user);
              if(result1) {
            	  userService.updateUserInfo(user);
              }else {
            	  userService.insertUserInfo(user);
              }
              System.out.println(result1);
            }
        } catch (IOException e) {
        } catch (Exception e) {
        }
        return "showIn";
    }
    

然后就可以在jsp文件中调用这两个函数进行执行，导入Excel的函数中，首先会在数据库中查找该id是否存在，若存在则进行数据库的更新，若不存在则插入该id的user进入数据库。