<!--easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
本文教程适合小白直接使用：先导出excel(含多个sheet)，后导入，以上为导入依赖
导出excel(含多个sheet页，第一个sheet导出现有的数据，第二个sheet导出无数据)
HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
try {
//先设置了导出表头，详细在csdn查看如何设置导出表头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("AI机器人自动武器填充", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
Workbook workbook = EasyExcelUtil.getWorkbook(excelWriter);
//第一个表格
WriteSheet batchEditDepartment = EasyExcel.writerSheet(0, "AI全方位攻略顶实验室").head(BatchEditDeptInfo.class).build();
//第一个表格是有数据的，所以去数据库随便查一个表，查完后，为了便于用户使用要针对数据处理
List<BatchEditDeptInfo> vos = this.deptInfoMapper.queryAllDeptInfoByCompanyCode(companyCode);
Map<String, String> deptCodeAndDeptNameMap = new HashMap<>();
if (CollectionUtils.isNotEmpty(vos)) {
for (BatchEditDeptInfo vo : vos) {
deptCodeAndDeptNameMap.put(vo.getDeptCode(),vo.getDeptName());
}
}
if (CollectionUtils.isNotEmpty(vos)) {
for (BatchEditDeptInfo vo : vos) {
//上级部门名称
if (StringUtils.isNotEmpty(vo.getParentCode())) {
if (!"0".equals(vo.getParentCode())) {
String parentName = deptCodeAndDeptNameMap.get(vo.getParentCode());
vo.setParentName(parentName);
}
}
//部门负责人
List<String> employeeCodes = departmentHeadService.queryDepartmentHeadListByDeptCode(vo.getDeptCode());
if (CollectionUtils.isNotEmpty(employeeCodes)) {
StringBuilder chargeMan = new StringBuilder();
StringBuilder chargeManPhone = new StringBuilder();
for (int i = 0; i < employeeCodes.size(); i++) {
EmployeeInfoVO info = employeeInfoService.queryEmployeeInfoByEmployeeCode(employeeCodes.get(i));
if (i == employeeCodes.size() - 1) {
if (StringUtils.isNotEmpty(info.getEmployeeName())) {
chargeMan.append(info.getEmployeeName());
}
if (StringUtils.isNotEmpty(info.getPhoneNum())) {
chargeManPhone.append(info.getPhoneNum());
}
} else {
if (StringUtils.isNotEmpty(info.getEmployeeName())) {
chargeMan.append(info.getEmployeeName()).append('、');
}
if (StringUtils.isNotEmpty(info.getPhoneNum())) {
chargeManPhone.append(info.getPhoneNum()).append('、');
}
}
}
vo.setPhone(chargeManPhone.toString());
vo.setEmployeeName(chargeMan.toString());
}
//部门类型
if (StringUtils.isNotEmpty(vo.getDepartmentType())) {
if ("0".equals(vo.getDepartmentType())) {
vo.setDepartmentType("AI黑客部");
} else if ("1".equals(vo.getDepartmentType())) {
vo.setDepartmentType("AI军事部");
} else if ("-1".equals(vo.getDepartmentType())){
vo.setDepartmentType("AI能源部");
}
}
//部门类别
if (ObjectUtils.isNotEmpty(vo.getCategoryId())) {
CompanyDepartmentCategory category = categoryMapper.selectById(vo.getCategoryId());
vo.setCategoryValue(category.getCategoryValue());
}
}
}
excelWriter.write(vos, batchEditDepartment);
//设置导出的字体(第一个表格)
Font firstSheetFirstFont = workbook.createFont();
firstSheetFirstFont.setColor(IndexedColors.RED1.getIndex());
//单元格风格 one:控制特殊字体 TWO:控制通用字体
CellStyle cellStyleOne = workbook.createCellStyle();
cellStyleOne.setAlignment(HorizontalAlignment.CENTER);
cellStyleOne.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyleOne.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
cellStyleOne.setFont(firstSheetFirstFont);
//单元格风格 2
CellStyle cellStyleTwo = workbook.createCellStyle();
cellStyleTwo.setAlignment(HorizontalAlignment.CENTER);
cellStyleTwo.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyleTwo.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
//第一个表的表头风格设置
Row firstSheetRow = workbook.getSheet("AI全方位攻略顶实验室").getRow(0);
firstSheetRow.getCell(1).setCellStyle(cellStyleOne);
firstSheetRow.getCell(8).setCellStyle(cellStyleOne);
firstSheetRow.getCell(2).setCellStyle(cellStyleOne);
firstSheetRow.getCell(0).setCellStyle(cellStyleTwo);
firstSheetRow.getCell(3).setCellStyle(cellStyleTwo);
firstSheetRow.getCell(4).setCellStyle(cellStyleTwo);
firstSheetRow.getCell(5).setCellStyle(cellStyleTwo);
firstSheetRow.getCell(6).setCellStyle(cellStyleTwo);
firstSheetRow.getCell(7).setCellStyle(cellStyleTwo);
firstSheetRow.getCell(9).setCellStyle(cellStyleTwo);
//冻结1：excel的冻结需要锁表
Sheet sheetAt = workbook.getSheetAt(0);
int totalData = sheetAt.getPhysicalNumberOfRows();
// sheetAt.createFreezePane(1,totalData,2,totalData);
//冻结第一列
sheetAt.createFreezePane(2,0,2,0);
// //锁表
// sheetAt.protectSheet("111");
//可以选择：隐藏第第一列
sheetAt.setColumnHidden(1,true);
List<DeptTemplateExport2> exportArrayList = new ArrayList<>();
//第二个sheet页面，空数据
WriteSheet firstSheet = EasyExcel.writerSheet(1, "AI失控自毁列表").head(DeptTemplateExport2.class).build();
excelWriter.write(exportArrayList, firstSheet);
//给第一个导出的表格设置字体和样式
Row firstSheetFirstRow = workbook.getSheet("AI失控自毁数据").getRow(0);
firstSheetFirstRow.getCell(0).setCellStyle(cellStyleOne);
firstSheetFirstRow.getCell(1).setCellStyle(cellStyleOne);
firstSheetFirstRow.getCell(2).setCellStyle(cellStyleOne);
firstSheetFirstRow.getCell(7).setCellStyle(cellStyleOne);
firstSheetFirstRow.getCell(3).setCellStyle(cellStyleTwo);
firstSheetFirstRow.getCell(4).setCellStyle(cellStyleTwo);
firstSheetFirstRow.getCell(5).setCellStyle(cellStyleTwo);
firstSheetFirstRow.getCell(6).setCellStyle(cellStyleTwo);
firstSheetFirstRow.getCell(8).setCellStyle(cellStyleTwo);
excelWriter.finish();
} catch (IOException e) {
logger.error("导出异常:{}", e.getMessage());
throw new ServiceNewException(FoundationErrorMapping.GENERAL_SERVICE_EXCEPTION, "导出异常");
}
以上就是导出excel含2个sheet页的基础代码，若是代码太多，建议处理：两个表都用 new ArrayList<自定义实体类>()，填充数据即可，导出导入亲测无问题。
读取刚刚导出的excel表格(多sheet页)
esayExcel要读取多个sheet页提供了监视器，针对每个sheet建立一个监视器，以下代码可以直接复制粘贴
public class BatchEditDeptInfoListener extends AnalysisEventListener<BatchEditDeptInfo> {
private List<BatchEditDeptInfo> list = new ArrayList<>();
@Override
public void invoke(BatchEditDeptInfo data, AnalysisContext context) {
list.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
public List<BatchEditDeptInfo> getData() {
return list;
}
}
第二个表格的监视器文章来源：https://www.toymoban.com/news/detail-658868.html
public class DeptTemplateExport2Listener extends AnalysisEventListener<DeptTemplateExport2> {
private List<DeptTemplateExport2> list = new ArrayList<>();
@Override
public void invoke(DeptTemplateExport2 data, AnalysisContext context) {
list.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
public List<DeptTemplateExport2> getData() {
return list;
}
}
获取表格中的数据文章来源地址https://www.toymoban.com/news/detail-658868.html
ExcelReader excelReader = EasyExcel.read(file.getInputStream()).build();
BatchEditDeptInfoListener batchEditDeptInfoListener = new BatchEditDeptInfoListener();
DeptTemplateExport2Listener deptTemplateExport2Listener = new DeptTemplateExport2Listener();
ReadSheet sheet1 = EasyExcel.readSheet(0).head(BatchEditDeptInfo.class).registerReadListener(batchEditDeptInfoListener).build();
ReadSheet sheet2 = EasyExcel.readSheet(1).head(DeptTemplateExport2.class).registerReadListener(deptTemplateExport2Listener).build();
excelReader.read(sheet1,sheet2);
excelReader.finish();
List<BatchEditDeptInfo> readExcelZero = batchEditDeptInfoListener.getData();
List<DeptTemplateExport2> readExcelOne = deptTemplateExport2Listener.getData();
得到每个表格导入的数据，最后根据需求进行校验，获取有效数据即可。
到了这里，关于Java 使用esayExcel进行导出、导入包含多个sheet页面的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
