springboot整合poi

依赖

<!-- poi依赖--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.0.1</version></dependency><!-- poi对于excel 的支持依赖--><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.0.1</version></dependency><!-- poi对于excel 的支持依赖--><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.0.1</version></dependency>

列表实体类UserInfo

public class UserInfo {private int id;private String name;private String sex;private int age;private String telephone;private String address;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 String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}public String getTelephone() {return telephone;}public void setTelephone(String telephone) {this.telephone = telephone;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public UserInfo() {}//方便数据导入时转换public UserInfo(Object[] args){/** DecimalFormat 用法* /p/b3699d73142e* Integer.valueOf 返回的时包装类 Integer.parseInt() 返回的是int*///因为传进来的args 的赋值是从1开始的this.id=new Double(args[0].toString()).intValue();this.name=args[1].toString();this.age=new Double(args[2].toString()).intValue();this.sex=args[3].toString();this.telephone=args[4].toString();this.address=args[5].toString();}@Overridepublic String toString() {return "UserInfo{" +"id=" + id +", name='" + name + '\'' +", sex='" + sex + '\'' +", age=" + age +", telephone='" + telephone + '\'' +", address='" + address + '\'' +'}';}}

EXCEL数据导出

制作导出模板

EXCEL数据导出工具类

/*** 用于 Excel下载的工具类*/public class DownloadUtils {/**** @param byteArrayOutputStream 输出字节流* @param response* @param returnName 输出到客户端的文件名* @throws IOException*/public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {response.setContentType("application/octet-stream");returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1")); //保存的文件名,必须和页面编码一致,否则乱码// response.addHeader("Content-Disposition","attachment;filename=total.xls");response.addHeader("Content-Disposition","attachment;filename="+returnName);response.setContentLength(byteArrayOutputStream.size());response.addHeader("Content-Length", "" + byteArrayOutputStream.size());ServletOutputStream outputStream = response.getOutputStream(); //取得输出流byteArrayOutputStream.writeTo(outputStream);//写到输出流byteArrayOutputStream.close();//关闭outputStream.flush(); //刷数据}}

EXCEL数据导出接口

/*** 导出数据 提前制作好的excel模板*/@GetMapping("exportTemplate")public void exportTemplate(HttpServletResponse response) throws IOException {//1.获取数据//人为构造的数据，实际是要从数据库中查的List<UserInfo> users=new ArrayList<>();for(int i=0;i<20;i++){UserInfo userInfo=new UserInfo();int rs = (int) ((Math.random() * 9 + 1) * Math.pow(10, 8 - 1));userInfo.setId(i+1);userInfo.setName("zhangsan"+i);userInfo.setAge(23);userInfo.setSex("man");userInfo.setTelephone("188"+rs);userInfo.setAddress("随便编个地址");users.add(userInfo);}//2.加载模板Resource resource = new ClassPathResource("excel/user_info.xlsx");FileInputStream fis = new FileInputStream(resource.getFile());//3.根据模板创建工作簿Workbook wb = new XSSFWorkbook(fis);//4.读取工作表Sheet sheet = wb.getSheetAt(0);//5.抽取第2行的公共样式 , 因为第一行 为标题 第2行是数据 下标为1Row row = sheet.getRow(1);CellStyle styles[] = new CellStyle[row.getLastCellNum()];Cell cell = null;for (int i = 0; i < row.getLastCellNum(); i++) {cell = row.getCell(i);styles[i] = cell.getCellStyle();}//6.构造单元格int rowIndex=1;for (UserInfo userInfo:users) {//创建每一行，同excel的第二行开始row=sheet.createRow(rowIndex++);//第一列cell = row.createCell(0);//设置单元格样式cell.setCellStyle(styles[0]);//写入数据 序号cell.setCellValue(userInfo.getId());//第2列cell = row.createCell(1);cell.setCellStyle(styles[1]);cell.setCellValue(userInfo.getName());cell = row.createCell(2);cell.setCellStyle(styles[2]);cell.setCellValue(userInfo.getAge());cell = row.createCell(3);cell.setCellStyle(styles[3]);cell.setCellValue(userInfo.getSex());cell = row.createCell(4);cell.setCellStyle(styles[4]);cell.setCellValue(userInfo.getTelephone());cell = row.createCell(5);cell.setCellStyle(styles[5]);cell.setCellValue(userInfo.getAddress());}//7.下载ByteArrayOutputStream os=new ByteArrayOutputStream();wb.write(os);String fileName = "用户信息-"+new Date().getTime()+ ".xlsx";new DownloadUtils().download(os,response,fileName);}

访问http://localhost:8080/exportTemplate，导出后的数据

EXCEL数据导入

EXCEL数据导入接口

//跳转到EXCEL导入数据页面@GetMapping("/jumpFileUpload")public String jumpFileUpload(){return "/fileUpload";}/*** excel 文件上传* postman 上传文件 操作指南/article/425e69e614f472be14fc166f.html*/@PostMapping("/upload")@ResponseBodypublic String upload(@RequestParam(name = "file") MultipartFile file) throws IOException {//1.解析Excel//1.1.根据Excel文件创建工作簿Workbook wb = new XSSFWorkbook(file.getInputStream());//1.2.获取SheetSheet sheet = wb.getSheetAt(0);//参数：索引//1.3.获取Sheet中的每一行，和每一个单元格//2.获取用户数据列表List<UserInfo> list = new ArrayList<>();System.out.println("最后一行的下标 :" + sheet.getLastRowNum());for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {Row row = sheet.getRow(rowNum);//根据索引获取每一个行// System.out.println("列数"+row.getLastCellNum());Object[] values = new Object[row.getLastCellNum()];for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {Cell cell = row.getCell(cellNum);Object value = getCellValue(cell);values[cellNum] = value;}UserInfo user = new UserInfo(values);list.add(user);}//3.批量保存用户for (UserInfo userInfo: list) {System.out.println(userInfo.toString());}return "SUCCESS";}public static Object getCellValue(Cell cell) {//1.获取到单元格的属性类型CellType cellType = cell.getCellType();//2.根据单元格数据类型获取数据Object value = null;switch (cellType) {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;}

EXCEL数据导入页面

这里使用的是thymeleaf页面

thymeleaf依赖

<!--thymeleaf引擎模板--><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-thymeleaf</artifactId></dependency>

页面

<!DOCTYPE html><html lang="en" xmlns:th=""><head></head><body><div style="text-align: center">导入EXCEL文件<form th:action="@{/upload}" method="post" enctype="multipart/form-data"><input type="file" name="file"/><input type="submit" value="导入数据"/></form></div></body></html>

得到数据

注解方式

springboot整合poi注解方式（使用EXCEL模板导出导入）