FastExcel Merge Cells (equivalent rows of data, to be merged)

Use FastExcel data export: official website:/fastexcel/zh-CN

demand (economics)

Credit Code, Filler, Unique, merge: Credit Code, Unit Name, Filler, row, and center vertically.

reasoning

There is no need to do a column merge here, so the RowWriteHandler is used
Thoughts.

• Specify a unique value to determine the equivalent row of data based on one or more cells (ExcelCellMergeStrategy. uniqueCol in the code)
• Determine whether the data in the unique column of the current row and the previous row are equal, if they are equal continue, the number of rows to be merged mergeCount + 1
• If the current row and the previous row are not equal, it means that the previous data need to be merged. At the same time, the current row will be used as the starting row for the next merge.

realization

Excel export cell full merge strategy

package ;


import ;
import ;
import ;

import .*;

import ;

import ;
import ;

/**
 * Excel导出单元格全量合并策略
 */
public class ExcelCellMergeStrategy implements RowWriteHandler {

    private int mergeRowIndex;//Which line to start merging
    private List<Integer> mergeColumnIndex = new ArrayList<>();//excelConsolidated columns
    private int[] uniqueCol;//Unique identification of the merger，Based on the specified column，Determine if the data are the same
    private int totalRow;//Total number of lines

    private int lastRow;
    private int firstCol;
    private int lastCol;
    private int firstRow;

    private int mergeCount = 1;

    /**
     * @param mergeRowIndex
     * @param mergeColIndex The range of support is as follows：0-3,6,9
     * @param uniqueCol     unique identification，1Columns or multiple columns Data Composition Unique Values
     * @param totalRow      Total number of lines(surname Cong0commencement）： -1  + Skipped table headers
     */
    public ExcelCellMergeStrategy(int mergeRowIndex, Object[] mergeColIndex, int[] uniqueCol, int totalRow) {
         = mergeRowIndex;
        for (Object item : mergeColIndex) {
            if (().contains("-")) {
                String[] spCol = ().split("-");
                int start = (spCol[0]);
                int end = (spCol[1]);
                for (int i = start; i <= end; i++) {
                    (i);
                }
            } else {
                int colIndex = (());
                (colIndex);
            }

        }
         = uniqueCol;
         = 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) {
        //current line
        int curRowIndex = ();
        //Maximum number of columns per row
        short lastCellNum = ();
        //current line为commencement合并行时，marking
        if (curRowIndex == mergeRowIndex) {
            //assign an initial value first line
            firstRow = curRowIndex;
        }
        //commencement合并位置
        if (curRowIndex > mergeRowIndex && !(0).getStringCellValue().equals("")) {
            for (int i = 0; i < lastCellNum; i++) {
                if ((i)) {
                    //current line号 current line对象 Merged identification bits
                    mergeWithPrevAnyRow((), curRowIndex, row, uniqueCol);
                    break;//It's already inside the merge cell operation，Just do it once.
                }

            }
        }
    }

    public void mergeWithPrevAnyRow(Sheet sheet, int curRowIndex, Row row, int[] uniqueCol) {
        Object currentData = "";
        Object preData = "";
        for (int col : uniqueCol) {
            currentData = currentData + (col).getStringCellValue();
            Row preRow = ().getRow(curRowIndex - 1);
            preData = preData + (col).getStringCellValue();
        }

        //Determining whether to merge cells
        boolean curEqualsPre = (preData);
        //Determine that the previous one is the same as the next one besides Identical marking bits
        if (curEqualsPre) {
            lastRow = curRowIndex;
            mergeCount++;
        }
        //excelIn-process consolidation
        if (!curEqualsPre && mergeCount > 1) {
            mergeSheet(firstRow, lastRow, mergeColumnIndex, sheet);
            mergeCount = 1;
        }

        //excelMerge at the end
        if (mergeCount > 1 && totalRow == curRowIndex) {
            mergeSheet(firstRow, lastRow, mergeColumnIndex, sheet);
            mergeCount = 1;
        }
        //Reset the next row to be merged
        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);
            (cellRangeAddress);

            // Set the style of the merged cell to vertically centered.
            CellStyle style = ().createCellStyle();
            ();
            //();
            Cell mergedCell = (firstRow).getCell(colNum, .CREATE_NULL_AS_BLANK);
            (style);
        }
    }
}

Date format conversion

EasyExcel => FastExcel, import support for multiple time formats

package ;

import ;
import ;
import ;
import ;
import ;
import ;

import ;
import ;
import ;
import ;
import ;
import ;
import org.;
import org.;

/**
 * Date format converter
 */
public class ExcelDateConverter implements Converter<Date> {
    private static final Logger log = ();
    // Define all date formats to try
    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 ;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return ;
    }


    @Override
    public Date convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
                                  GlobalConfiguration globalConfiguration) throws Exception {
        String cellValue = "";
        if (().equals()) {
            long cellIntValue = ().longValue();
            if (cellIntValue > 19900100) {
                try {
                    // 1. The first parse，The date passed in is in numeric form，appearing beyyyyMMdd
                    SimpleDateFormat originalFormat = new SimpleDateFormat("yyyyMMdd");
                    return ((cellIntValue));
                } catch (Exception e) {
                    ("exception when parse numerical time with format yyyyMMdd");
                    cellValue=(cellIntValue);
                }
            }

            // 2. The second parse， excelis from1900Starting from year,Eventually, by calculating the difference between the1900Target date for calculating the number of days between years
            LocalDate localDate = (1900, 1, 1);

            //excel peculiarbug, Differences in the number of dates resulting2
            localDate = (cellIntValue - 2);

            // convert toZonedDateTime（If time zone information is required）
            ZonedDateTime zonedDateTime = (());
            return (());
        } else if (().equals()) {
            // 3. The third parse
            Date date = null;
            cellValue = ();
            for (SimpleDateFormat format : formats) {
                try {
                    date = (cellValue);
                    if (date != null) {
                        // This step formats the date asJavaFormat of expectations
                        return date;
                    }
                } catch (Exception e) {
                    // If there is an anomaly，Continue parsing after catching an exception
                    //((), e);
                }
            }
        }
        // It didn't work.，throw an exception
        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 = (value);
        return new WriteCellData<>(dateValue);
    }
}

interface code

Export Code

package ;

import ;
import ;
import ;
import ;
import ;
import .*;
import ;

import ;
import ;
import ;
import ;

/**
 * Corporate Information
 */
@RestController
@RequestMapping("/detail")
public class CooperationDetailController extends BaseController {


  /**
     * Corporate Information
     *
     * @return
     */
    @PostMapping("/export")
    public void exportInfo(HttpServletRequest request, HttpServletResponse response, @RequestBody Map<String, Object> param) {
        try {
            Page page = buildPage(param, );
            QueryWrapper<SysOrganization> queryWrapper = buildQueryWrapper(, param);
            (response, queryWrapper);
        } catch (Exception ex) {
            ((), ex);
        }
    }
}

Service

@Service
public class SysOrganizationServiceImpl extends ServiceImpl<SysOrganizationMapper, SysOrganization> implements ISysOrganizationService {

    @Override
    public void exportInfo(HttpServletResponse response, QueryWrapper<SysOrganization> queryWrapper) {
        String templateFileName = "";
        try {
            templateFileName = () + "/template/Enterprise Export Templates.xlsx";
            ("application/");
            ("utf-8");
            // Here you can prevent Chinese garbled code Of course. FastExcel it doesn't matter
            String fileName = ("Enterprise Data", "UTF-8").replaceAll("\\+", "%20");
            ("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            //Get the data to be exported DTO
            List<SysOrganizationExcelDTO> dataList = data(queryWrapper);
            int mergeRowIndex = 2; // Merge from that line -- Skip Table Header
            int[] uniqueCol = {0, 7}; //Based on the specified column，Identifying the same data
            Object[] mergeColIndex = {"0-1", 6, 7}; //Columns to be merged
            int totalRow = () - 1 + mergeRowIndex;
            // Here you need to set not to close the stream
            ExcelCellMergeStrategy excelCellMergeStrategy = new ExcelCellMergeStrategy(mergeRowIndex, mergeColIndex, uniqueCol, totalRow);
            ((), )
                    .needHead(false)
                    .withTemplate(templateFileName)
                    .autoCloseStream()
                    .registerWriteHandler(excelCellMergeStrategy) //Merge Cells
                    .sheet("Enterprise Data")
                    .doWrite(dataList);
        } catch (Exception e) {
            // reprovisionresponse
            ();
            ("application/json");
            ("utf-8");
            //abnormalities，Throw to the front end JSON
            ApiResult result = new ApiResult(6001, "Failed to download file " + templateFileName + " " + ());
            try {
                ().println((result));
            } catch (IOException ex) {
                ((), ex);
                throw new CustomException(());
            }
        }
    }

    /**
     * Getting the data to go out
     */
    private List<SysOrganizationExcelDTO> data(QueryWrapper<SysOrganization> queryWrapper) {
        IPage list = (new Page(1, 10000), queryWrapper);
        List<SysOrganizationExcelDTO> result = new ArrayList<>();
        for (Object obj : ()) {
            if (obj instanceof SysOrganization) {
                SysOrganization item = (SysOrganization) obj;
                SysOrganizationExcelDTO info = new SysOrganizationExcelDTO();
                (item, info);
                //Assembly data
                (info);
            }
        }
        return result;
    }
}  

DTO

package ;


import ;
import ;
import ;
import ;

import ;
import ;

/**
 * Excel Exported Uses
 */
public class SysOrganizationExcelDTO implements Serializable {

    /**
     * Unified Social Credit Code (USCC)
     */
    //@ExcelProperty(value = "Unified Social Credit Code (USCC)")
    @ExcelProperty(index = 0)
    private String unifiedSocialCode;

    /**
     * Name of organization
     */
    @ExcelProperty(index = 1)
    private String orgName;

    /**
     * Job Category Name
     */
    @ExcelProperty(index = 2)
    private String jobBigName;
    /**
     * Post Medium Category Name
     */
    @ExcelProperty(index = 3)
    private String jobMiddleName;
    /**
     * Name of the job subcategory
     */
    @ExcelProperty(index = 4)
    private String jobSmallName;
    /**
     * Number of positions
     */
    @ExcelProperty(index = 5)
    private Integer jobQty;
	
    /**
     * Date of filling*
     */
    @ExcelProperty(index = 6, converter = )
    private Date inputDate;
    /**
     * filler
     */
    @ExcelProperty(index = 7)
    private String inputUser;

    ......an omissionget set
 
}