spring boot + POI 导出和导入Excel表格详细步骤
程序员文章站
2024-03-21 13:10:10
...
在后端项目中导出/导入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;
}
}
上一篇： 单链表插入带头结点和不带头结点