通过jxls + poi 解决导出excel表格和动态合并单元格

技术：jxls：通过模板导出数据；

           poi：解决第一列中相同值的单元格合并；

第一步：依赖导入

<dependency>
    <groupId>net.sf.jxls</groupId>
    <artifactId>jxls-core</artifactId>
    <version>1.0.6</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.17</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>3.15</version>
</dependency>

第二部：上传需求规定的excel模板

模板编辑：

源码：

 将模板存到数据库：

//Controller 层

@RequestMapping(value = "/saveMixedShipExTemplate", method = RequestMethod.POST)
	public JsonResult saveMixedShipExTemplate (@RequestParam("file") MultipartFile file) throws IOException{
		InputStream is = file.getInputStream();
		boolean rs = templateService.saveMixedShipExTemplate(is);
		try {
			is.close();
		} catch (IOException e) {
			log.debug("InputStream关闭失败！");
		}
		if (rs) {
			return JsonResult.success();
		}else {
			return JsonResult.error(ReturnEnum.FAILURE);
		}
	}
//返回值类型
public class JsonResult<T> implements Serializable {
    private static final long serialVersionUID = 2652608027682835212L;
    private boolean success;
    private String errorCode = "";
    private String message = "";
    private T data;

//Service 层

public boolean saveMixedShipExTemplate(InputStream inputStream) throws IOException {
        if (inputStream == null) {
            return false;
        }
        byte[] data = inputStreamToByte(inputStream);//将文件保存到字节数组中
        Template template = new Template();
        template.setFileName("exportMixedShipAndInfo");
        template.setFileContent(data);
        TemplateDomain templateDomain = new TemplateDomain(template);
        // 校验是否已存在模板，若存在则更新，否则插入新模板
        BigDecimal id = templateMapper.countTemplate("exportMixedShipAndInfo");
        if (id == null) {// 新增
            templateDomain.create();
            templateMapper.saveTemplate(template);
        }else {// 更新
            template.setId(id);
            templateDomain.update();
            templateMapper.updateTemplateById(template);
        }
        return true;
    }

//定义Domian设置创建人、创建时间，更新时间...
public class TemplateDomain extends BaseDomain<Template>{

	public TemplateDomain(Template e) {
		super(e);
	}

//读取InputStream的内容并保存到byte数组
    private byte [] inputStreamToByte(InputStream is) throws IOException {
        ByteArrayOutputStream bAOutputStream = new ByteArrayOutputStream();
        int ch;
        while((ch = is.read() ) != -1){
            bAOutputStream.write(ch);
        }
        byte data [] =bAOutputStream.toByteArray();
        bAOutputStream.close();
        return data;
    }

	public void create() {
		BigDecimal userId = getUserId();
		po.setId(generateNewId());
		po.setCreateTime(now());
		po.setCreateUserid(userId);
		po.setUpdateTime(po.getCreateTime());
		po.setUpdateUserid(userId);
		po.setIsDelete(Constant.DEFAULT_NUM_0);
	}
	
	public void update() {
		BigDecimal userId = getUserId();
		po.setUpdateTime(now());
		po.setUpdateUserid(userId);
		po.setIsDelete(Constant.DEFAULT_NUM_0);
	}
}
public abstract class BaseDomain<E> {
    protected E po;
    private static final IdWorker idWorker = IdWorker.getInstance();

    protected BaseDomain(E e) {
        this.po = e;
    }

    public E getPO() {
        return po;
    }

    protected BigDecimal generateNewId() {
        return idWorker.nextId();
    }

    protected Date now() {
        return new Date();
    }

    protected BigDecimal getUserId() {
        String userId = BaseContextHandler.getUserID();
        if (CommonValidator.checkStringEmpty(userId))
            return null;

        return new BigDecimal(userId);
    }

    protected String getTenantId() {
        String tenantId = BaseContextHandler.getTenantId();
        if (CommonValidator.checkStringEmpty(tenantId) || tenantId==null) {
            return "DeFault";
        }
        return tenantId;
    }
}

第三部：编写导出程序

//Service 层   
 public void exportMixedShipAndInfo(BigDecimal id) throws Exception {
        //获取列表数据
        MixedShipResult mixedShipAndInfo = this.getMixedShipAndInfo(id);
        Map<String, Object> exeMap = new HashMap<String, Object>();
        exeMap.put("entity", mixedShipAndInfo);
        exeMap.put("list", mixedShipAndInfo.getInfoList());
        //获取导出模板
            Map<String, Blob> map = templateMapper.getTemplate("exportMixedShipAndInfo");
        byte[] byt = blobToBytes(map.get(Constant.TEMPLATE_FIELD_FILE_CONTENT));
        InputStream is = new ByteArrayInputStream(byt);
        //将数据传入模板中
        XLSTransformer transformer = new XLSTransformer();
        Workbook workbook = transformer.transformXLS(is, exeMap);

        //动态合并单元格需要的逻辑
        String[] names = {"单位名称"};
        List<String> needColNames = Arrays.asList(names);
        ExcelUtils.mergeSameCellByMainCol(workbook, needColNames, 4);

        //导出excel
        ExportUtil exportUtil=new ExportUtil();
        exportUtil.export(workbook);
    }

通过模板名称获取模板

  <select id="getTemplate" resultType="java.util.Map" >
    select FILE_CONTENT
    from CLOUD_UPLOAD_TEMPLATE
    where FILE_NAME = #{name,jdbcType=VARCHAR} and is_delete=0 and rownum=1
    order by update_time desc
  </select>

其中公共类：ExcelUtils 和  ExportUtils

@Slf4j
public class ExportUtil {

    /**
     * @param workbook,fileName
     * @author 杨光
     * @description 导出公用方法
     * @date 创建时间：2020年4月7日
     */
    public void export(Workbook workbook) throws Exception {
        HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment; filename=export.xls" );
        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
        response.setHeader("Pragma", "public");
        response.setDateHeader("Expires", System.currentTimeMillis() + 1000L);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
    }

}
//合并单元格公共类
public class ExcelUtils {
// list 的第一个为需要做合并判断逻辑的 ， list中其他的合并逻辑跟第一个相同
    public static Workbook mergeSameCellByMainCol(Workbook workbook, List<String> needMergeColName, Integer startMergeRowNum) {

        List<MergeRecord> mergeRecords = new ArrayList<>();
        List<Integer> needMergeColNum = new ArrayList<>();
        Sheet sheet = workbook.getSheetAt(0);
        if (workbook != null && needMergeColName != null) {

            // 遍历获取所有相同单元格,并记录
            // 遍历表头,获取需要合并的 colnum ,
            Row firstRow = sheet.getRow(2);
            if (firstRow != null) {
                // 找出所有需要合并单元格的列号
                for (String needName : needMergeColName) {
                    for (int i = 0; i < firstRow.getLastCellNum(); i++) {
                        String cellContent = firstRow.getCell(i).getStringCellValue();
                        if (StringUtils.isNotEmpty(cellContent)) {
                            if (cellContent.equals(needName)) {
                                needMergeColNum.add(i);
                                break;
                            }
                        }
                    }
                }
            }
            // 找出所有需要合并的单元格
            Integer needCol = needMergeColNum.get(0);
            Integer allRows = sheet.getLastRowNum();
            String sameContent = null;
            Integer firstRowNum = null;
            Integer lastRowNum = null;
            Integer firstColNum = null;
            Integer lastColNum = null;
            MergeRecord mergeRecord = null;
            for (int rowNum = startMergeRowNum; rowNum < allRows + 1; rowNum++) {
                // 如果本行与下一行的值一样 ，则需要新建一个合并区域
                Row row = sheet.getRow(rowNum);
                Row nextRow = sheet.getRow(rowNum + 1);
                String cellVal = row.getCell(needCol).getStringCellValue();
                String nextCellVal = null;
                if (nextRow != null) {
                    nextCellVal = nextRow.getCell(needCol).getStringCellValue();
                }
                // 如果当行值等于相同值的话 ，合并行加1
                if (sameContent != null && sameContent.equals(nextCellVal)) {
                    lastRowNum = rowNum + 1;
                    continue;
                }
                // 如果下一行的值与当前值不相同 或者已经到最后一行，那么结束本次的合并单元格任务
                if (((sameContent != null && !sameContent.equals(nextCellVal))) || (nextRow == null && sameContent != null)) {
                    mergeRecord = new MergeRecord();
                    mergeRecord.setMergeContent(sameContent);
                    mergeRecord.setCulomnNum(needCol);
                    mergeRecord.setFirstRow(firstRowNum);
                    mergeRecord.setLastRow(lastRowNum);
                    mergeRecord.setFirstCol(firstColNum);
                    mergeRecord.setLastCol(lastColNum);
                    mergeRecords.add(mergeRecord);

                    // 将相同内容置空 ，并寻找下一个需要合并的单元格
                    sameContent = null;
                }
                // 如果第一次两个值一样的话
                if (cellVal.equals(nextCellVal)) {
                    sameContent = cellVal;
                    firstRowNum = rowNum;
                    lastRowNum = rowNum + 1;
                    firstColNum = needCol;
                    lastColNum = needCol;
                }
            }
            // 其他列的合并情况跟第一列都一样
            List<MergeRecord> lastAll = new ArrayList<>();
            for (int i = 1; i < needMergeColNum.size(); i++) {
                List<MergeRecord> tempList = new ArrayList<>();
                for (MergeRecord record : mergeRecords) {
                    MergeRecord mergeRecord1 = new MergeRecord();
                    mergeRecord1.setFirstRow(record.getFirstRow());
                    mergeRecord1.setLastRow(record.getLastRow());
                    mergeRecord1.setFirstCol(needMergeColNum.get(i));
                    mergeRecord1.setLastCol(needMergeColNum.get(i));

                    tempList.add(mergeRecord1);
                }
                lastAll.addAll(tempList);
            }
            mergeRecords.addAll(lastAll);
        }
        // 找完所有的单元格后 ，开始合并
        for (MergeRecord mergeRecord : mergeRecords) {
            sheet.addMergedRegion(new CellRangeAddress(mergeRecord.getFirstRow(), mergeRecord.getLastRow(), mergeRecord.getFirstCol(), mergeRecord.getLastCol()));
        }
        return workbook;
    }

/**
     *
     * @Title: blobToBytes
     * @Description: 将Blob转换为byte数组
     * @param: @param blob
     * @param: @return
     * @return: byte[]
     * @throws
     */

public static byte[] blobToBytes(Blob blob) {
        BufferedInputStream is = null;
        try {
            is = new BufferedInputStream(blob.getBinaryStream());
            byte[] bytes = new byte[(int) blob.length()];
            int len = bytes.length;
            int offset = 0;
            int read = 0;
            while (offset < len
                    && (read = is.read(bytes, offset, len - offset)) >= 0) {
                offset += read;
            }
            return bytes;
        } catch (Exception e) {
            return null;
        } finally {
            try {
                is.close();
                is = null;
            } catch (IOException e) {
                return null;
            }
        }
    }
}