FastExcel 合并单元格（相当的行数据，进行合并）
目录[*]需求
[*]思路
[*]实现
[*]Excel导出单元格全量合并策略
[*]日期格式转换
[*]接口代码
[*]Service
[*]DTO
使用FastExcel数据导出：官网： https://idev.cn/fastexcel/zh-CN
需求
信用代码、填报人，唯一时，将：信用代码、单位名称、填报人，进行row 合并，并垂直居中对齐
思路
这边不需要做列合并，所以采用了 RowWriteHandler
思路，
[*]指定唯一值，根据某个或多个单元格确定相当的数据行（代码中的 ExcelCellMergeStrategy. uniqueCol）
[*]判断当前行的唯一列的数据和上一行是否相等，如果相等继续，要合并的行数 mergeCount + 1
[*]如果当前行和上一行不相等，说明前面的数据需要做合并处理了。同时将当前行做为下一次待合并的起始行
实现
Excel导出单元格全量合并策略
package com.vipsoft.handler;import cn.idev.excel.write.handler.RowWriteHandler;import cn.idev.excel.write.metadata.holder.WriteSheetHolder;import cn.idev.excel.write.metadata.holder.WriteTableHolder;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import java.util.ArrayList;import java.util.List;/** * Excel导出单元格全量合并策略 */public class ExcelCellMergeStrategy implements RowWriteHandler { private int mergeRowIndex;//从哪一行开始合并 private List<Integer> mergeColumnIndex = new ArrayList<>();//excel合并的列 private int[] uniqueCol;//合并的唯一标识，根据指定的列，确定数据是否相同 private int totalRow;//总行数 private int lastRow; private int firstCol; private int lastCol; private int firstRow; private int mergeCount = 1; /** * @param mergeRowIndex * @param mergeColIndex 支持范围如：0-3,6,9 * @param uniqueCol 唯一标识，1列或多列 数据组成唯一值 * @param totalRow 总行数(从0开始）：List.size -1+ 跳过的表头 */ public ExcelCellMergeStrategy(int mergeRowIndex, Object[] mergeColIndex, int[] uniqueCol, int totalRow) { this.mergeRowIndex = mergeRowIndex; for (Object item : mergeColIndex) { if (item.toString().contains("-")) { String[] spCol = item.toString().split("-"); int start = Integer.parseInt(spCol); int end = Integer.parseInt(spCol); for (int i = start; i <= end; i++) { mergeColumnIndex.add(i); } } else { int colIndex = Integer.parseInt(item.toString()); mergeColumnIndex.add(colIndex); } } this.uniqueCol = uniqueCol; this.totalRow = totalRow; } @Override public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) { //当前行 int curRowIndex = row.getRowNum(); //每一行的最大列数 short lastCellNum = row.getLastCellNum(); //当前行为开始合并行时，标记 if (curRowIndex == mergeRowIndex) { //赋初值 第一行 firstRow = curRowIndex; } //开始合并位置 if (curRowIndex > mergeRowIndex && !row.getCell(0).getStringCellValue().equals("")) { for (int i = 0; i < lastCellNum; i++) { if (mergeColumnIndex.contains(i)) { //当前行号 当前行对象 合并的标识位 mergeWithPrevAnyRow(writeSheetHolder.getSheet(), curRowIndex, row, uniqueCol); break;//已经进入到合并单元格操作里面了，执行一次就行 } } } } public void mergeWithPrevAnyRow(Sheet sheet, int curRowIndex, Row row, int[] uniqueCol) { Object currentData = ""; Object preData = ""; for (int col : uniqueCol) { currentData = currentData + row.getCell(col).getStringCellValue(); Row preRow = row.getSheet().getRow(curRowIndex - 1); preData = preData + preRow.getCell(col).getStringCellValue(); } //判断是否合并单元格 boolean curEqualsPre = currentData.equals(preData); //判断前一个和后一个相同 并且 标识位相同 if (curEqualsPre) { lastRow = curRowIndex; mergeCount++; } //excel过程中合并 if (!curEqualsPre && mergeCount > 1) { mergeSheet(firstRow, lastRow, mergeColumnIndex, sheet); mergeCount = 1; } //excel结尾处合并 if (mergeCount > 1 && totalRow == curRowIndex) { mergeSheet(firstRow, lastRow, mergeColumnIndex, sheet); mergeCount = 1; } //重置下一个要合并的行 if (!curEqualsPre) { firstRow = curRowIndex; } } private void mergeSheet(int firstRow, int lastRow, List<Integer> mergeColumnIndex, Sheet sheet) { for (int colNum : mergeColumnIndex) { firstCol = colNum; lastCol = colNum; CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol); sheet.addMergedRegion(cellRangeAddress); // 设置合并后的单元格样式为垂直居中 CellStyle style = sheet.getWorkbook().createCellStyle(); style.setVerticalAlignment(VerticalAlignment.CENTER); //style.setAlignment(HorizontalAlignment.CENTER); Cell mergedCell = sheet.getRow(firstRow).getCell(colNum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); mergedCell.setCellStyle(style); } }}日期格式转换
EasyExcel => FastExcel ，导入支持多种时间格式
package com.vipsoft.base.util;import java.text.SimpleDateFormat;import java.time.LocalDate;import java.time.ZoneId;import java.time.ZonedDateTime;import java.time.format.DateTimeFormatter;import java.util.Date;import cn.idev.excel.converters.Converter;import cn.idev.excel.enums.CellDataTypeEnum;import cn.idev.excel.metadata.GlobalConfiguration;import cn.idev.excel.metadata.data.ReadCellData;import cn.idev.excel.metadata.data.WriteCellData;import cn.idev.excel.metadata.property.ExcelContentProperty;import org.slf4j.Logger;import org.slf4j.LoggerFactory;/** * 日期格式转换器 */public class ExcelDateConverter implements Converter<Date> { private static final Logger log = LoggerFactory.getLogger(ExcelDateConverter.class); // 定义所有要尝试的日期格式 SimpleDateFormat[] formats = { new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"), new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"), new SimpleDateFormat("yyyy/MM/dd"), new SimpleDateFormat("yyyy-MM-dd"), new SimpleDateFormat("yyyy-MM"), new SimpleDateFormat("yyyy/MM"), new SimpleDateFormat("yyyyMMdd") }; @Override public Class<Date> supportJavaTypeKey() { return Date.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } @Override public Date convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { String cellValue = ""; if (cellData.getType().equals(CellDataTypeEnum.NUMBER)) { long cellIntValue = cellData.getNumberValue().longValue(); if (cellIntValue > 19900100) { try { // 1. 第一种解析，传入的是数字形式的日期，形如yyyyMMdd SimpleDateFormat originalFormat = new SimpleDateFormat("yyyyMMdd"); return originalFormat.parse(String.valueOf(cellIntValue)); } catch (Exception e) { log.warn("exception when parse numerical time with format yyyyMMdd"); cellValue=String.valueOf(cellIntValue); } } // 2. 第二种解析， excel是从1900年开始计算,最终通过计算与1900年间隔的天数计算目标日期 LocalDate localDate = LocalDate.of(1900, 1, 1); //excel 有些奇怪的bug, 导致日期数差2 localDate = localDate.plusDays(cellIntValue - 2); // 转换为ZonedDateTime（如果需要时区信息） ZonedDateTime zonedDateTime = localDate.atStartOfDay(ZoneId.systemDefault()); return Date.from(zonedDateTime.toInstant()); } else if (cellData.getType().equals(CellDataTypeEnum.STRING)) { // 3. 第三种解析 Date date = null; cellValue = cellData.getStringValue(); for (SimpleDateFormat format : formats) { try { date = format.parse(cellValue); if (date != null) { // 这一步是将日期格式化为Java期望的格式 return date; } } catch (Exception e) { // 如果有异常，捕捉异常后继续解析 //log.error(e.getMessage(), e); } } } // 没转成功，抛出异常 throw new UnsupportedOperationException("The current operation is not supported by the current converter." + cellValue); } @Override public WriteCellData<?> convertToExcelData(Date value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); String dateValue = sdf.format(value); return new WriteCellData<>(dateValue); }} 接口代码
导出代码
package com.vipsoft.api.controller;import cn.hutool.core.util.ObjectUtil;import cn.hutool.core.util.StrUtil;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.baomidou.mybatisplus.core.metadata.IPage;import com.baomidou.mybatisplus.extension.plugins.pagination.Page;import org.springframework.web.bind.annotation.*;import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.io.InputStream;import java.util.Map;/** * 企业信息 */@RestController@RequestMapping("/detail")public class CooperationDetailController extends BaseController {/** * 企业信息 * * @return */ @PostMapping("/export") public void exportInfo(HttpServletRequest request, HttpServletResponse response, @RequestBody Map<String, Object> param) { try { Page page = buildPage(param, CooperationInfo.class); QueryWrapper<SysOrganization> queryWrapper = buildQueryWrapper(SysOrganization.class, param); cooperationDetailService.exportInfo(response, queryWrapper); } catch (Exception ex) { logger.error(ex.getMessage(), ex); } }}Service
@Servicepublic class SysOrganizationServiceImpl extends ServiceImpl<SysOrganizationMapper, SysOrganization> implements ISysOrganizationService { @Override public void exportInfo(HttpServletResponse response, QueryWrapper<SysOrganization> queryWrapper) { String templateFileName = ""; try { templateFileName = cuworConfig.getFilePath() + "/template/企业导出模板.xlsx"; response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和 FastExcel 没有关系 String fileName = URLEncoder.encode("企业数据", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); //获取要导出的数据 DTO List<SysOrganizationExcelDTO> dataList = data(queryWrapper); int mergeRowIndex = 2; // 从那一行开始合并-- 跳过表头 int[] uniqueCol = {0, 7}; //根据指定的列，确定相同的数据 Object[] mergeColIndex = {"0-1", 6, 7}; //需要合并的列 int totalRow = dataList.size() - 1 + mergeRowIndex; // 这里需要设置不关闭流 ExcelCellMergeStrategy excelCellMergeStrategy = new ExcelCellMergeStrategy(mergeRowIndex, mergeColIndex, uniqueCol, totalRow); FastExcel.write(response.getOutputStream(), SysOrganizationExcelDTO.class) .needHead(false) .withTemplate(templateFileName) .autoCloseStream(Boolean.FALSE) .registerWriteHandler(excelCellMergeStrategy) //合并单元格 .sheet("企业数据") .doWrite(dataList); } catch (Exception e) { // 重置response response.reset(); response.setContentType("application/json"); response.setCharacterEncoding("utf-8"); //异常时，向前端抛出 JSON ApiResult result = new ApiResult(6001, "下载文件失败 " + templateFileName + " " + e.getMessage()); try { response.getWriter().println(PojoUtil.pojoToJson(result)); } catch (IOException ex) { logger.error(ex.getMessage(), ex); throw new CustomException(ex.getMessage()); } } } /** * 获得要到出的数据 */ private List<SysOrganizationExcelDTO> data(QueryWrapper<SysOrganization>queryWrapper) { IPage list = this.page(new Page(1, 10000), queryWrapper); List<SysOrganizationExcelDTO> result = new ArrayList<>(); for (Object obj : list.getRecords()) { if (obj instanceof SysOrganization) { SysOrganization item = (SysOrganization) obj; SysOrganizationExcelDTO info = new SysOrganizationExcelDTO(); BeanUtils.copyProperties(item, info); //组装数据 result.add(info); } } return result; }}DTO
package com.vipsoft.base.dto;import cn.idev.excel.annotation.ExcelIgnore;import cn.idev.excel.annotation.ExcelProperty;import cn.idev.excel.annotation.format.DateTimeFormat;import com.vipsoft.base.util.ExcelDateConverter;import java.io.Serializable;import java.util.Date;/** * Excel 导出使用 */public class SysOrganizationExcelDTO implements Serializable { /** * 统一社会信用代码 */ //@ExcelProperty(value = "统一社会信用代码") @ExcelProperty(index = 0) private String unifiedSocialCode; /** * 机构名称 */ @ExcelProperty(index = 1) private String orgName; /** * 岗位大类名称 */ @ExcelProperty(index = 2) private String jobBigName; /** * 岗位中类名称 */ @ExcelProperty(index = 3) private String jobMiddleName; /** * 岗位小类名称 */ @ExcelProperty(index = 4) private String jobSmallName; /** * 岗位数量 */ @ExcelProperty(index = 5) private Integer jobQty; /** * 填报日期* */ @ExcelProperty(index = 6, converter = ExcelDateConverter.class) private Date inputDate; /** * 填报人 */ @ExcelProperty(index = 7) private String inputUser; ......省略get set }
页:
[1]