<!--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;
}
}
第二个表格的监视器
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;
}
}
获取表格中的数据
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();
得到每个表格导入的数据,最后根据需求进行校验,获取有效数据即可。
今天的文章excel java导出_oracle导入一个表的全部数据分享到此就结束了,感谢您的阅读。
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/84729.html