EasyExcle导出
最近做OA的时候有个导出 ，导出 某个项目的所有人员 某个月的考勤信息，每天都要展示出来详情。而我们的需求存在 一天有两种状态，上午 出勤状态 和下午出勤状态，但是 数据肯定只会存在一条，只不过用两个字段区分了
用的是EasyExcle
话不多说直接看效果图
左边到备注的是合并单元格的行，当然左边的列头也是固定的，右边 是月的第一天到最后一天的时间
首先 导出的入口是公用的适配器
@ApiOperation(value = "文件导入")
@RequestMapping(value = "exportFile/{exportType}")
@ResponseBody
public Result<?> exportFile(@PathVariable String exportType,
HttpServletRequest request, HttpServletResponse response) {
Result<?> result = new Result<>();
try {
if (StringUtils.isBlank(exportType)) {
throw new PreconditionCheckFailedException("请提供导出类型！");
}
ExportHandler<?> handler = exportStrategy.getHandler(exportType);
if (handler == null) {
throw new PreconditionCheckFailedException("不支持的导出类型！");
}
handler.exportData(request, response);
result.setMessage("导出成功！");
result.setSuccess(true);
} catch (Exception e) {
e.printStackTrace();
SystemLogUtils.IMPORT_EXPORT_LOG.error("导出文件失败", e);
result.setSuccess(false);
result.setMessage("导出文件失败");
}
return result;
}
这个地方的 我们这个是通过不同的type 导出不同的文件，公司公用的导出接口
@Component
public class ExportStrategy {
private static Map<String, ExportHandler<?>> exportHandlerMap = new HashMap <String, ExportHandler<?>>();
public static void registHandler(ExportHandler<?> handler) {
if (ExportStrategy.exportHandlerMap.containsKey(handler.fetchHandlerKey())) {
throw new PreconditionCheckFailedException("ExportStrategy存在同名handler(" + handler.fetchHandlerKey() + ")，请修改！");
}
ExportStrategy.exportHandlerMap.put(handler.fetchHandlerKey(), handler);
}
public ExportHandler<?> getHandler(String handlerKey) {
return ExportStrategy.exportHandlerMap.get(handlerKey);
}
}
导出处理器
public abstract class ExportHandler<T> {
/**
* 构造函数
*/
public ExportHandler() {
ExportStrategy.registHandler(this);
}
/**
* 获取本策略支持的组织或人员策略bean的唯一标识
* @return
*/
public abstract String fetchHandlerKey();
/**
* 导出数据
* @param request
*/
public abstract void exportData(HttpServletRequest request, HttpServletResponse response);
}
**下面就是具体的实现了 因为要合并 前面到备注的信息 0~16列，所以这边添加了很多信息 head 方法 也是需要 手动 动态添加所需要的列，data（）方法 的值 需要和 head() 中的对应 **
@Override
public void exportData(HttpServletRequest request, HttpServletResponse response) {
OutputStream out = null;
ExcelWriter excelWriter = null;
try {
out = EasyExcelUtils.dealResponse(response, "月度考勤统计");
//需要合并的列
int[] mergeColumeIndex = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16};
// 从那一列开始合并
int mergeRowIndex = 0;
excelWriter = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex)).build();
List<List<List<String>>> lists = data(request);
for (int i = 0; i < lists.size(); i++) {
//这里 需要指定写用哪个class去写
WriteSheet writeSheet = EasyExcel.writerSheet(i, organizationService.getById(map.get(Integer.valueOf(i))).getName()).head(head(request)).build();
excelWriter.write(lists.get(i), writeSheet);
}
excelWriter.finish();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
private List<List<List<String>>> data(HttpServletRequest request) {
List<List<List<String>>> listList = new ArrayList<>();
AttendanceMonthApplyQueryDto queryDto = new AttendanceMonthApplyQueryDto();
String startDate = WebUtils.getStringValue(request, "startDate");
if (StringUtils.isNotEmpty(startDate)) {
queryDto.setStartDate(startDate);
}
String endDate = WebUtils.getStringValue(request, "endDate");
if (StringUtils.isNotEmpty(endDate)) {
queryDto.setEndDate(endDate);
}
List<Long> ids = WebUtils.getLongList(request, "dataIds", ",");
if (StringUtils.isEmpty(ids)) {
long departId = WebUtils.getLongValue(request, "departId", -1);
if (StringUtils.isNotNull(departId) && departId > 0) {
queryDto.setDepartId(departId);
}
Integer stepStatus = WebUtils.getIntValue(request, "stepStatus", -1);
if (StringUtils.isNotNull(stepStatus) && stepStatus > 0) {
queryDto.setStepStatus(stepStatus);
}
} else {
queryDto.setIdsList(ids);
}
List<AttendanceMonthApplyListVo> attendanceMonthApplyPageList = attendanceMonthApplyService.getAttendanceMonthApplyPageList(queryDto);
int c = 0;
for (AttendanceMonthApplyListVo attendanceMonthApplyListVo : attendanceMonthApplyPageList) {
List<List<String>> lists = new ArrayList<>();
AttendanceMonthApplyQueryDto applyQueryDto = new AttendanceMonthApplyQueryDto();
applyQueryDto.setAttendanceApplyId(attendanceMonthApplyListVo.getDataId());
boolean rowData = WebUtils.getBooleanValue(request, "rawData", false);
applyQueryDto.setRowData(rowData);
List<AttendanceMonthApplyDto> applyDtos = attendanceMonthApplyService.listExportAttendanceMonthApplyDetail(applyQueryDto);
if (CollectionUtils.isEmpty(applyDtos)) {
continue;
}
map.put(c, applyDtos.get(0).getApplyDetailVacation().getDepartId());
// String yearMonth = applyDtos.get(0).getYearMonth();
String times = startDate+"-01";
Date date1 = JodaTimeUtils.parseDate(times, DateConsts.DATE_FORMAT_Y_M_D);
LocalDate firstDay = date1.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
String lastMonthDay = getLastMonthDay(date1);
Date date2 = JodaTimeUtils.parseDate(lastMonthDay, DateConsts.DATE_FORMAT_Y_M_D);
LocalDate lastDay = date2.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
List<org.joda.time.LocalDate> localDates = JodaTimeUtils.listDateBetweenStartAndEnd(date1, date2);
for (int i = 0; i < applyDtos.size(); i++) {
AttendanceMonthApplyDto statisticsListVo = applyDtos.get(i);
List<String> dataList = new ArrayList<>();
dataList.add(String.valueOf(i + 1));
dataList.add(statisticsListVo.getApplyDetailVacation().getUserName());
dataList.add(statisticsListVo.getApplyDetailVacation().getProjectName());
dataList.add(statisticsListVo.getApplyDetailVacation().getAttendance().toString());
dataList.add(statisticsListVo.getApplyDetailVacation().getOfficialBusiness().toString());
dataList.add(statisticsListVo.getApplyDetailVacation().getStatutoryHoliday().toString());
dataList.add(statisticsListVo.getApplyDetailVacation().getLeaveAbsence().toString());
dataList.add(statisticsListVo.getApplyDetailVacation().getSickLeave().toString());
dataList.add(statisticsListVo.getApplyDetailVacation().getMarriageLeave().toString());
dataList.add(statisticsListVo.getApplyDetailVacation().getYearLeave().toString());
dataList.add(statisticsListVo.getApplyDetailVacation().getMaternityLeave().toString());
dataList.add(statisticsListVo.getApplyDetailVacation().getBereavementLeave().toString());
dataList.add(statisticsListVo.getApplyDetailVacation().getPaternityLeave().toString());
dataList.add(statisticsListVo.getApplyDetailVacation().getMiscarriageLeave().toString());
dataList.add(statisticsListVo.getApplyDetailVacation().getPaternityLeave().toString());
dataList.add(statisticsListVo.getApplyDetailVacation().getStatutoryHolidays().toString());
dataList.add(statisticsListVo.getApplyDetailVacation().getRemark());
List<AttendanceMonthApplyDetailDay> detailDay = statisticsListVo.getDetailDay();
detailDay = detailDay.stream().sorted(Comparator.comparing(AttendanceMonthApplyDetailDay::getAttendanceTime)).collect(Collectors.toList());
Map<String, List<AttendanceMonthApplyDetailDay>> collect = detailDay.stream().collect(Collectors.groupingBy(AttendanceMonthApplyDetailDay::getAttendanceMonthDay));
for (org.joda.time.LocalDate localDate : localDates) {
String string = localDate.toString();
List<AttendanceMonthApplyDetailDay> applyDetailDays = collect.get(string);
if (StringUtils.isNotEmpty(applyDetailDays)){
AttendanceMonthApplyDetailDay applyDetailDay = applyDetailDays.get(0);
Integer festivalsType = applyDetailDay.getFestivalsType();
if (StringUtils.isNotNull(festivalsType)){
String desc = DicUtils.getDesc(AttendanceConsts.attendance_status, applyDetailDay.getFestivalsType());
dataList.add(desc);
}else {
dataList.add("-");
}
}else {
dataList.add("-");
}
}
List<String> dataList2 = new ArrayList<>();
dataList2.add(String.valueOf(i + 1));
dataList2.add(statisticsListVo.getApplyDetailVacation().getUserName());
dataList2.add(statisticsListVo.getApplyDetailVacation().getProjectName());
dataList2.add(statisticsListVo.getApplyDetailVacation().getAttendance().toString());
dataList2.add(statisticsListVo.getApplyDetailVacation().getOfficialBusiness().toString());
dataList2.add(statisticsListVo.getApplyDetailVacation().getStatutoryHoliday().toString());
dataList2.add(statisticsListVo.getApplyDetailVacation().getLeaveAbsence().toString());
dataList2.add(statisticsListVo.getApplyDetailVacation().getSickLeave().toString());
dataList2.add(statisticsListVo.getApplyDetailVacation().getMarriageLeave().toString());
dataList2.add(statisticsListVo.getApplyDetailVacation().getYearLeave().toString());
dataList2.add(statisticsListVo.getApplyDetailVacation().getMaternityLeave().toString());
dataList2.add(statisticsListVo.getApplyDetailVacation().getBereavementLeave().toString());
dataList2.add(statisticsListVo.getApplyDetailVacation().getPaternityLeave().toString());
dataList2.add(statisticsListVo.getApplyDetailVacation().getMiscarriageLeave().toString());
dataList2.add(statisticsListVo.getApplyDetailVacation().getPaternityLeave().toString());
dataList2.add(statisticsListVo.getApplyDetailVacation().getStatutoryHolidays().toString());
dataList2.add(statisticsListVo.getApplyDetailVacation().getRemark());
for (org.joda.time.LocalDate localDate : localDates) {
String string = localDate.toString();
List<AttendanceMonthApplyDetailDay> applyDetailDays = collect.get(string);
if (StringUtils.isNotEmpty(applyDetailDays)){
AttendanceMonthApplyDetailDay applyDetailDay = applyDetailDays.get(0);
Integer festivalsAfterType = applyDetailDay.getFestivalsAfterType();
if (StringUtils.isNotNull(festivalsAfterType)){
String desc = DicUtils.getDesc(AttendanceConsts.attendance_status, festivalsAfterType);
dataList2.add(desc);
}else {
dataList2.add("-");
}
}else {
dataList2.add("-");
}
}
lists.add(dataList);
lists.add(dataList2);
}
listList.add(lists);
c = c + 1;
}
return listList;
}
public List<List<String>> head(HttpServletRequest request) {
List<List<String>> list = new ArrayList<>();
List<String> head0 = new ArrayList<>();
head0.add("序号");
List<String> head1 = new ArrayList<>();
head1.add("姓名");
List<String> head2 = new ArrayList<>();
head2.add("项目");
List<String> head4 = new ArrayList<>();
head4.add("出勤");
List<String> head6 = new ArrayList<>();
head6.add("公出");
List<String> head7 = new ArrayList<>();
head7.add("法定假日");
List<String> head8 = new ArrayList<>();
head8.add("事假");
List<String> head9 = new ArrayList<>();
head9.add("病假");
List<String> head10 = new ArrayList<>();
head10.add("婚假");
List<String> head11 = new ArrayList<>();
head11.add("年休假");
List<String> head12 = new ArrayList<>();
head12.add("产假");
List<String> head13 = new ArrayList<>();
head13.add("丧假");
List<String> head14 = new ArrayList<>();
head14.add("陪产假");
List<String> head15 = new ArrayList<>();
head15.add("小产假");
List<String> head16 = new ArrayList<>();
head16.add("育儿假");
List<String> head18 = new ArrayList<>();
head18.add("法定假日出勤");
List<String> head19 = new ArrayList<>();
head19.add("备注");
list.add(head0);
list.add(head1);
list.add(head2);
list.add(head4);
list.add(head6);
list.add(head7);
list.add(head8);
list.add(head9);
list.add(head10);
list.add(head11);
list.add(head12);
list.add(head13);
list.add(head14);
list.add(head15);
list.add(head16);
list.add(head18);
list.add(head19);
String startDate = WebUtils.getStringValue(request, "startDate");
startDate = startDate+"-01";
Date date1 = JodaTimeUtils.parseDate(startDate, DateConsts.DATE_FORMAT_Y_M_D);
LocalDate firstDay = date1.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
String lastMonthDay = getLastMonthDay(date1);
Date date2 = JodaTimeUtils.parseDate(lastMonthDay, DateConsts.DATE_FORMAT_Y_M_D);
LocalDate lastDay = date2.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
while (!firstDay.isAfter(lastDay)) {
List<String> head = new ArrayList<>();
head.add(String.valueOf(Integer.parseInt(firstDay.format(DateTimeFormatter.ofPattern("yyyyMMdd"))) % 100));
list.add(head);
firstDay = firstDay.plusDays(1);
}
return list;
}
/**
* 获取指定日期对应的月份最后一天
* @param dt
* @return
*/
public String getLastMonthDay(Date dt) {
//获取当前月最后一天
Calendar ca = Calendar.getInstance();
ca.setTime(dt);
int days = ca.getActualMaximum(Calendar.DAY_OF_MONTH) - 1;
String firstDay = DateUtils.parseDateToStr("yyyy-MM-01", dt);
Date startDate = null;
try {
startDate = DateUtils.parseDate(firstDay, "yyyy-MM-dd");
} catch (ParseException e) {
e.printStackTrace();
return "";
}
Date date = DateUtils.addDays(startDate, days);
// 获取最后一天
String lastDay = DateUtils.parseDateToStr("yyyy-MM-dd", date);
return lastDay;
}
主要这里有个很重要自己实现的导出策略 mergeWithPrevRow 需要自己实现 我这边的业务 跟上一个单元格 合并（根据自己的业务，根据某个值来合并单元格）
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
int curRowIndex = cell.getRowIndex();
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* 当前单元格向上合并
*
* @param writeSheetHolder
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 将当前单元格数据与上一个单元格数据比较
Boolean dataBool = preData.equals(curData);
//根据第一个列 序号的是否相等再合并
Cell cell1 = cell.getSheet().getRow(curRowIndex).getCell(0);
Object d1 = cell1.getCellTypeEnum() == CellType.STRING ? cell1.getStringCellValue() : cell1.getNumericCellValue();
Cell cell2 = cell.getSheet().getRow(curRowIndex - 1).getCell(0);
Object d2 = cell2.getCellTypeEnum() == CellType.STRING ? cell2.getStringCellValue() : cell2.getNumericCellValue();
Boolean bool = d1.equals(d2) ? true : false;
// 当前cell
Object data = cell.getCellType() == CellType.STRING.getCode() ? cell.getStringCellValue() : cell.getNumericCellValue();
// 上面的Cell
Cell upCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object upData = upCell.getCellType() == CellType.STRING.getCode() ? upCell.getStringCellValue() : upCell.getNumericCellValue();
if (bool ) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex) ) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
276
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
