在后端项目中导出/导入Excel表格，亲测具体实现步骤为：

1.在pom.xml 中加入POI导入依赖

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.1</version>
</dependency>

2.controller 层的内容

做导出数据到Excel表的功能时,主要是将所需导出的数据先给查询出来,然后再利用所写的工具类中的方法处理一下数据,保存到Excel中.

我这用的工具类是 POIUtils.

下面直接看代码:

//导出数据到Excel表
@GetMapping("/export")public ResponseEntity<byte[]> exportData(){//先从数据库获取到所需导出成Excel的表格数据List<Employee> list = (List<Employee>)empBasicService.getEmployee().getData();return POIUtils.employee2Excel(list);}//Excel表导入数据@PostMapping("/import")public RespBean importData(MultipartFile file) throws IOException {List<Employee> list = POIUtils.excel2Employee(file,nationService.getAllNations(),politicsstatusService.getAllPoliticsstatus(),departmentService.getAllDepartmentsWithOutChildren(),positionService.getAllPosition(),jobLevelService.getAllJobLevels());if(empBasicService.addEmps(list) == list.size()){System.out.println("上传成功!");return RespBean.ok("上传成功!");}return RespBean.error("上传失败!");}

package org.javaboy.vhr.utils;import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.javaboy.vhr.model.*;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.util.MultiValueMap;
import org.springframework.web.multipart.MultipartFile;import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;public class POIUtils {public static ResponseEntity<byte[]> employee2Excel(List<Employee> list) {//1.创建一个Excel文档HSSFWorkbook workbook = new HSSFWorkbook();//2.创建文档摘要workbook.createInformationProperties();//3.获取并配置文档信息DocumentSummaryInformation information = workbook.getDocumentSummaryInformation();//文档类别information.setCategory("员工信息");//文档管理员information.setManager("wzy");//设置公司信息information.setCompany("从一而终");//4.获取文档摘要信息SummaryInformation summaryInfo = workbook.getSummaryInformation();//文档标题summaryInfo.setTitle("员工信息表");//文档作者summaryInfo.setAuthor("wzy");//备注信息summaryInfo.setComments("本文档由 Java 编程提供");//5.创建样式//创建标题行的样式HSSFCellStyle headerStyle = workbook.createCellStyle();headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index );headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//创建日期的样式HSSFCellStyle dateCellStyle = workbook.createCellStyle();dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));HSSFSheet sheet = workbook.createSheet("员工信息表");//设置列的宽度sheet.setColumnWidth(0,5*256);sheet.setColumnWidth(1,12*256);sheet.setColumnWidth(2,10*256);sheet.setColumnWidth(3,5*256);sheet.setColumnWidth(4,12*256);sheet.setColumnWidth(5,20*256);sheet.setColumnWidth(6,10*256);sheet.setColumnWidth(7,10*256);sheet.setColumnWidth(8,16*256);sheet.setColumnWidth(9,12*256);sheet.setColumnWidth(10,15*256);sheet.setColumnWidth(11,20*256);sheet.setColumnWidth(12,20*256);sheet.setColumnWidth(13,14*256);sheet.setColumnWidth(14,12*256);sheet.setColumnWidth(15,14*256);sheet.setColumnWidth(16,16*256);sheet.setColumnWidth(17,12*256);sheet.setColumnWidth(18,10*256);sheet.setColumnWidth(19,12*256);sheet.setColumnWidth(20,12*256);sheet.setColumnWidth(21,12*256);sheet.setColumnWidth(22,12*256);sheet.setColumnWidth(23,12*256);sheet.setColumnWidth(24,12*256);//6.创建标题行HSSFRow r0 = sheet.createRow(0);HSSFCell c0 = r0.createCell(0);c0.setCellValue("编号");c0.setCellStyle(headerStyle);HSSFCell c1 = r0.createCell(1);c1.setCellStyle(headerStyle);c1.setCellValue("姓名");HSSFCell c2 = r0.createCell(2);c2.setCellStyle(headerStyle);c2.setCellValue("工号");HSSFCell c3 = r0.createCell(3);c3.setCellStyle(headerStyle);c3.setCellValue("性别");HSSFCell c4 = r0.createCell(4);c4.setCellStyle(headerStyle);c4.setCellValue("出生日期");HSSFCell c5 = r0.createCell(5);c5.setCellStyle(headerStyle);c5.setCellValue("身份证号码");HSSFCell c6 = r0.createCell(6);c6.setCellStyle(headerStyle);c6.setCellValue("婚姻状况");HSSFCell c7 = r0.createCell(7);c7.setCellStyle(headerStyle);c7.setCellValue("民族");HSSFCell c8 = r0.createCell(8);c8.setCellStyle(headerStyle);c8.setCellValue("籍贯");HSSFCell c9 = r0.createCell(9);c9.setCellStyle(headerStyle);c9.setCellValue("政治面貌");HSSFCell c10 = r0.createCell(10);c10.setCellStyle(headerStyle);c10.setCellValue("电子邮件");HSSFCell c11 = r0.createCell(11);c11.setCellStyle(headerStyle);c11.setCellValue("电话号码");HSSFCell c12 = r0.createCell(12);c12.setCellStyle(headerStyle);c12.setCellValue("联系地址");HSSFCell c13 = r0.createCell(13);c13.setCellStyle(headerStyle);c13.setCellValue("所属部门");HSSFCell c14 = r0.createCell(14);c14.setCellStyle(headerStyle);c14.setCellValue("职位");HSSFCell c15 = r0.createCell(15);c15.setCellStyle(headerStyle);c15.setCellValue("职称");HSSFCell c16 = r0.createCell(16);c16.setCellStyle(headerStyle);c16.setCellValue("聘用形式");HSSFCell c17 = r0.createCell(17);c17.setCellStyle(headerStyle);c17.setCellValue("最高学历");HSSFCell c18 = r0.createCell(18);c18.setCellStyle(headerStyle);c18.setCellValue("专业");HSSFCell c19 = r0.createCell(19);c19.setCellStyle(headerStyle);c19.setCellValue("毕业院校");HSSFCell c20 = r0.createCell(20);c20.setCellStyle(headerStyle);c20.setCellValue("入职日期");HSSFCell c21 = r0.createCell(21);c21.setCellStyle(headerStyle);c21.setCellValue("转正日期");HSSFCell c22 = r0.createCell(22);c22.setCellStyle(headerStyle);c22.setCellValue("合同起始日期");HSSFCell c23 = r0.createCell(23);c23.setCellStyle(headerStyle);c23.setCellValue("合同终止日期");HSSFCell c24 = r0.createCell(24);c24.setCellStyle(headerStyle);c24.setCellValue("合同期限(年)");for (int i = 0; i < list.size(); i++) {Employee emp = list.get(i);HSSFRow row = sheet.createRow(i + 1);row.createCell(0).setCellValue(emp.getId());row.createCell(1).setCellValue(emp.getName());row.createCell(2).setCellValue(emp.getWorkID());row.createCell(3).setCellValue(emp.getGender());HSSFCell cell4 = row.createCell(4);cell4.setCellStyle(dateCellStyle);cell4.setCellValue(emp.getBirthday());row.createCell(5).setCellValue(emp.getIdCard());row.createCell(6).setCellValue(emp.getWedlock());row.createCell(7).setCellValue(emp.getNation().getName());row.createCell(8).setCellValue(emp.getNativePlace());row.createCell(9).setCellValue(emp.getPoliticsstatus().getName());row.createCell(10).setCellValue(emp.getEmail());row.createCell(11).setCellValue(emp.getPhone());row.createCell(12).setCellValue(emp.getAddress());row.createCell(13).setCellValue(emp.getDepartment().getName());row.createCell(14).setCellValue(emp.getPosition().getName());row.createCell(15).setCellValue(emp.getJobLevel().getName());row.createCell(16).setCellValue(emp.getEngageForm());row.createCell(17).setCellValue(emp.getTiptopDegree());row.createCell(18).setCellValue(emp.getSpecialty());row.createCell(19).setCellValue(emp.getSchool());HSSFCell cell20 = row.createCell(20);cell20.setCellStyle(dateCellStyle);cell20.setCellValue(emp.getBeginDate());HSSFCell cell21 = row.createCell(21);cell21.setCellStyle(dateCellStyle);cell21.setCellValue(emp.getConversionTime());HSSFCell cell22 = row.createCell(22);cell22.setCellStyle(dateCellStyle);cell22.setCellValue(emp.getBeginContract());HSSFCell cell23 = row.createCell(23);cell23.setCellStyle(dateCellStyle);cell23.setCellValue(emp.getEndContract());row.createCell(24).setCellValue(emp.getContractTerm());}ByteArrayOutputStream baos = new ByteArrayOutputStream();HttpHeaders headers = new HttpHeaders();try {headers.setContentDispositionFormData("attachment",new String("员工表.xls".getBytes("UTF-8"),"ISO-8859-1"));headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);workbook.write(baos);} catch (IOException e) {e.printStackTrace();}return new ResponseEntity<byte[]>(baos.toByteArray(),headers, HttpStatus.CREATED);}/*** Excel 解析成 员工数据集合* @param file* @param allNations* @param allPoliticsstatus* @param allDepartments* @param allPosition* @param allJobLevels* @return*/public static List<Employee> excel2Employee(MultipartFile file, List<Nation> allNations,List<Politicsstatus> allPoliticsstatus, List<Department> allDepartments,List<Position> allPosition, List<JobLevel> allJobLevels) {List<Employee> list = new ArrayList<>();Employee emp = null;//有异常,利用 try catch 捕获异常try {//1.创建 workbook 对象HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());//2.获取 workbook 中表单的数量int numberOfSheets = workbook.getNumberOfSheets();for (int i = 0; i < numberOfSheets; i++) {//3.获取表单HSSFSheet sheet = workbook.getSheetAt(i);//4.获取表单行数int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();for (int j = 0; j <physicalNumberOfRows; j++) {//5.跳过标题行if(j==0){continue;//跳过第一行 标题行 ,不解析}//6.获取行HSSFRow row = sheet.getRow(j);if(row == null){continue;   //跳过中间有空行的情况}//7.获取表单中的列int physicalNumberOfCells = row.getPhysicalNumberOfCells();emp = new Employee();for (int k = 0; k < physicalNumberOfCells; k++) {HSSFCell cell = row.getCell(k);switch (cell.getCellType()){case STRING:String cellValue = cell.getStringCellValue();switch (k){case 1: emp.setName(cellValue); break;case 2: emp.setWorkID(cellValue); break;case 3: emp.setGender(cellValue); break;case 5: emp.setIdCard(cellValue); break;case 6: emp.setWedlock(cellValue); break;case 7:int nationIndex = allNations.indexOf(new Nation(cellValue));emp.setNationId(allNations.get(nationIndex).getId());break;case 8: emp.setNativePlace(cellValue); break;case 9:int polIndex = allPoliticsstatus.indexOf(new Politicsstatus(cellValue));emp.setPoliticId(allPoliticsstatus.get(polIndex).getId());break;case 10: emp.setEmail(cellValue); break;case 11: emp.setPhone(cellValue); break;case 12: emp.setAddress(cellValue); break;case 13:int depIndex = allDepartments.indexOf(new Department(cellValue));emp.setDepartmentId(allDepartments.get(depIndex).getId());break;case 14:int posIndex = allPosition.indexOf(new Position(cellValue));emp.setPosId(allPosition.get(posIndex).getId());break;case 15:int jobLevelsIndex = allJobLevels.indexOf(new JobLevel(cellValue));emp.setJobLevelId(allJobLevels.get(jobLevelsIndex).getId());break;case 16: emp.setEngageForm(cellValue); break;case 17: emp.setTiptopDegree(cellValue); break;case 18: emp.setSpecialty(cellValue); break;case 19: emp.setSchool(cellValue); break;}break;default:{switch (k){case 4: emp.setBirthday(cell.getDateCellValue()); break;case 20: emp.setBeginDate(cell.getDateCellValue()); break;case 21: emp.setConversionTime(cell.getDateCellValue()); break;case 22: emp.setBeginContract(cell.getDateCellValue()); break;case 23: emp.setEndContract(cell.getDateCellValue()); break;case 24: emp.setContractTerm(cell.getNumericCellValue()); break;}}break;}}list.add(emp);}}} catch (IOException e) {e.printStackTrace();}return list;}
}