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;//合并的唯一標識，根據指定的列，確定數(shù)據是否相同
    private int totalRow;//總行數(shù)

    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列或多列 數(shù)據組成唯一值
     * @param totalRow      總行數(shù)(從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[0]);
                int end = Integer.parseInt(spCol[1]);
                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();
        //每一行的最大列數(shù)
        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;//已經進入到合并單元格操作里面了，執(zhí)行一次就行
                }

            }
        }
    }

    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);
        }
    }
}

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. 第一種解析，傳入的是數(shù)字形式的日期，形如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年間隔的天數(shù)計算目標日期
            LocalDate localDate = LocalDate.of(1900, 1, 1);

            //excel 有些奇怪的bug, 導致日期數(shù)差2
            localDate = localDate.plusDays(cellIntValue - 2);

            // 轉換為ZonedDateTime（如果需要時區(qū)信息）
            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) {
                    // 如果有異常，捕捉異常后繼續(xù)解析
                    //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;

/**
 * 企業(yè)信息
 */
@RestController
@RequestMapping("/detail")
public class CooperationDetailController extends BaseController {


  /**
     * 企業(yè)信息
     * 
     * @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
public class SysOrganizationServiceImpl extends ServiceImpl<SysOrganizationMapper, SysOrganization> implements ISysOrganizationService {

    @Override
    public void exportInfo(HttpServletResponse response, QueryWrapper<SysOrganization> queryWrapper) {
        String templateFileName = "";
        try {
            templateFileName = cuworConfig.getFilePath() + "/template/企業(yè)導出模板.xlsx";
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 這里URLEncoder.encode可以防止中文亂碼 當然和 FastExcel 沒有關系
            String fileName = URLEncoder.encode("企業(yè)數(shù)據", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            //獲取要導出的數(shù)據 DTO
            List<SysOrganizationExcelDTO> dataList = data(queryWrapper);
            int mergeRowIndex = 2;                      // 從那一行開始合并  -- 跳過表頭
            int[] uniqueCol = {0, 7};                  //根據指定的列，確定相同的數(shù)據
            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("企業(yè)數(shù)據")
                    .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());
            }
        }
    }

    /**
     * 獲得要到出的數(shù)據
     */
    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); 
                //組裝數(shù)據
                result.add(info);
            }
        }
        return result;
    }
}  

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 {

    /**
     * 統(tǒng)一社會信用代碼
     */
    //@ExcelProperty(value = "統(tǒng)一社會信用代碼")
    @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;
    /**
     * 崗位數(shù)量
     */
    @ExcelProperty(index = 5)
    private Integer jobQty;
	
    /**
     * 填報日期*
     */
    @ExcelProperty(index = 6, converter = ExcelDateConverter.class)
    private Date inputDate;
    /**
     * 填報人
     */
    @ExcelProperty(index = 7)
    private String inputUser;

    ......省略get set
 
}