原文链接：https://blog.csdn.net/fanrenxiang/article/details/80985879
项目中经常需要使用导入导出功能来加快数据的操作，尤其是一些项目的后台管理系统，特此奉上我们项目中使用到导入导出代码，均可以实际使用。准备工作：pom中加入以下依赖:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version> 3.15-beta2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version> 3.15-beta2</version> </dependency>基本思路：读取到文件--->创建表格并把文件流内容读取到表格中--->解析表格--->持久化
package com.simons.cn.springbootdemo.util; import com.simons.cn.springbootdemo.bean.Movie; import com.simons.cn.springbootdemo.service.Weixin.IndexService; import org.apache.poi.ss.usermodel.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.util.FileCopyUtils; import org.springframework.web.multipart.MultipartFile; import java.io.File; import java.io.FileInputStream; import java.util.ArrayList; import java.util.List; @Service public class FilePortUtil { private static final Logger log = LoggerFactory.getLogger(FilePortUtil.class); @Autowired private IndexService indexService; /** * 导入功能 * * @param multipartFile * @return */ public int fileImport(MultipartFile multipartFile) { File file = null; Workbook workbook = null; int totalNum = 0; /*得到的path是 /D:/springbootdemo/target/springbootdemo/WEB-INF/classes/ */ String path = FilePortUtil.class.getClassLoader().getResource( "/").getPath(); /*拼接后的path就是 /D:/springbootdemo/target/springbootdemo/WEB-INF/电影正式资源.xlsx /*/ path = path.substring( 0, path.indexOf( "WEB-INF") + "WEB-INF".length()) + "/" + multipartFile.getOriginalFilename(); file = new File(path); try { /*把文件流copy读取到文件中*/ FileCopyUtils.copy(multipartFile.getBytes(), file); workbook = WorkbookFactory.create( new FileInputStream(file)); List<Movie> list = new ArrayList<>(); /*遍历sheet页*/ for ( int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); if (sheet == null) { continue; } /*统计导入的总条数，要是你的excell包含了表头，就不用加1了*/ if (sheet.getLastRowNum() > 0) { totalNum += sheet.getLastRowNum() + 1; } /*遍历行，这里j的初始值取1是因为我的表格里第一行是表头*/ for ( int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) { Row row = sheet.getRow(j); /*解析列，下标从0开始*/ Cell cell2 = row.getCell( 2); Cell cell3 = row.getCell( 3); if (cell2 == null || cell3 == null) { continue; } String name = this.getCellValue(cell2); String original = this.getCellValue(cell3); /*我这里省略了很多数据清洗、校验的代码*/ Movie movie = new Movie(); movie.setName(name); movie.setOriginal(original); list.add(movie); } /*持久化：批量新增*/ indexService.insertBatch(list); } /*解析完删除此路径下的文件*/ file.delete(); return totalNum; } catch (Exception e) { e.printStackTrace(); log.error( "导入功能公用类异常exception={}", e); } return totalNum; } public String getCellValue(Cell cell) { if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { Double d = cell.getNumericCellValue(); return String.valueOf(d.intValue()); } return String.valueOf(cell.getStringCellValue()); } }一般来说，每个导入功能处理的逻辑不太一样，里面的校验、数据对象也不太一样，所以我这里就没有封装成公用类而仅仅写成service，里面具体的逻辑交由程序员填充。
导出功能是最常用的，下面的代码中用到了反射的思想，比如，你查询出来的List<T>结果集，只想导出指定的那些字段数据，就非常方便了，代码如下:
基本思路：创建表格对象--->将数据set进表格--->将表格流写入response返回
package com.simons.cn.springbootdemo.util; import org.apache.commons.collections.CollectionUtils; import org.apache.commons.lang.time.DateFormatUtils; import org.apache.poi.hssf.usermodel.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletResponse; import java.beans.PropertyDescriptor; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.util.Date; import java.util.Iterator; import java.util.List; public class FilePortUtil { private static final Logger log = LoggerFactory.getLogger(FilePortUtil.class); /** * 导出功能 * 注意：泛型T类字段名和containBean集合里字段名字的一致性 * * @param response * @param title 表名 * @param headers 表头 * @param list 数据集 * @param containBean 数据集类型字段 * @param <T> * @throws Exception */ public static <T> void exportExcel(HttpServletResponse response, String title, String[] headers, List<T> list, List<String> containBean) throws Exception { HSSFWorkbook workbook = null; try { workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(title); HSSFRow row = sheet.createRow( 0); /*创建第一行表头*/ for ( short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } Iterator<T> it = list.iterator(); int index = 0; while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); /*反射得到字段*/ Field[] fields = t.getClass().getDeclaredFields(); /*如果需要匹配*/ if (CollectionUtils.isNotEmpty(containBean)) { for ( int j = 0; j < containBean.size(); j++) { for ( int i = 0; i < fields.length; i++) { Field field = fields[i]; if (!field.getName().equals(containBean.get(j))) continue; /*给每一列set值*/ setCellValue(t, field, row, j); } } } else { for ( int i = 0; i < fields.length; i++) { Field field = fields[i]; setCellValue(t, field, row, i); } } } /*application/vnd.ms-excel告诉浏览器要下载的是个excel*/ response.setContentType( "application/vnd.ms-excel;charset=UTF-8"); /*请求头设置，Content-Disposition为下载标识，attachment标识以附件方式下载*/ response.addHeader( "Content-Disposition", "attachment;filename=" + new String((title).getBytes(), "ISO8859-1") + ".xls"); workbook.write(response.getOutputStream()); } finally { if (workbook != null) { workbook.close(); } } } /** * 设置每一行中的列 * * @param t * @param field * @param row * @param index * @param <T> */ private static <T> void setCellValue(T t, Field field, HSSFRow row, int index) { HSSFCell cell = row.createCell(index); Object value = invoke(t, field); String textValue = null; if (value != null) { if (value instanceof Date) { Date date = (Date) value; textValue = DateFormatUtils.format(date, "yyyy-MM-dd HH:mm:ss"); } else { textValue = value.toString(); } } if (textValue != null) { cell.setCellValue(textValue); } } /** * 反射映射数据集字段 * * @param t * @param field * @param <T> * @return */ private static <T> Object invoke(T t, Field field) { try { String fieldName = field.getName(); PropertyDescriptor pd = new PropertyDescriptor(fieldName, t.getClass()); Method method = pd.getReadMethod(); return method.invoke(t); } catch (Exception e) { return null; } } } @ResponseBody @RequestMapping(value = "/fileport", method = RequestMethod.GET) public void filePort(HttpServletResponse response) { //导出的表名 String title = "测试导出活动参与记录"; //表中第一行表头字段 String[] headers = { "主键id", "用户名", "活动id", "奖品id", "中奖时间"}; //实际数据结果集 List<UserReward> listObject = userRewardDao.queryUserActivityInfo( "2018shuangdan_act", "2018shuangdan_evt", "sdthanks"); //具体需要写入excel需要哪些字段，这些字段取自UserReward类，也就是上面的实际数据结果集的泛型 List<String> listColumn = Arrays.asList( "id", "username", "actId", "rewardId", "winRewardTime"); try { FilePortUtil.exportExcel(response, title, headers, listObject, listColumn); } catch (Exception e) { e.printStackTrace(); } }