If you merge cells by Excel, export excelutils

One ：Excel to update

package com.qrqy.common.core.annotation;

import com.qrqy.common.core.utils.poi.ExcelHandlerAdapter;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import java.math.BigDecimal;

/** *  Custom export Excel Data annotation  * * @author qrqy */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel
{
    

    /** *  Merge cells  */
    public boolean isMerge() default false;
    /** *  Whether it is a consolidation condition  */
    public boolean isCondition() default false;
    /** *  Export at excel Middle order  */
    public int sort() default Integer.MAX_VALUE;

    /** *  Export to Excel The name of . */
    public String name() default "";

    /** *  Date format ,  Such as : yyyy-MM-dd */
    public String dateFormat() default "";

    /** *  Read content to expression  ( Such as : 0= male ,1= Woman ,2= Unknown ) */
    public String readConverterExp() default "";

    /** *  Separator , Read the contents of the string group  */
    public String separator() default ",";

    /** * BigDecimal  precision   Default :-1( Not on by default BigDecimal format ) */
    public int scale() default -1;

    /** * BigDecimal  Rounding rules   Default :BigDecimal.ROUND_HALF_EVEN */
    public int roundingMode() default BigDecimal.ROUND_HALF_EVEN;

    /** *  Export type （0 Numbers  1 character string ） */
    public ColumnType cellType() default ColumnType.STRING;

    /** *  Export at excel The height of each column in the   Unit as character  */
    public double height() default 14;

    /** *  Export at excel The width of each column in the   Unit as character  */
    public double width() default 16;

    /** *  Suffixes of words , Such as % 90  become 90% */
    public String suffix() default "";

    /** *  When the value is empty , Default value of field  */
    public String defaultValue() default "";

    /** *  Prompt information  */
    public String prompt() default "";

    /** *  You can only select the contents of columns that cannot be entered . */
    public String[] combo() default {
    };

    /** *  Whether to export data , Responding to needs : Sometimes we need to export a template , This is the title, but the content needs to be filled in manually . */
    public boolean isExport() default true;

    /** *  Property name in another class , Support multi-level acquisition , Separated by decimal points  */
    public String targetAttr() default "";

    /** *  Whether to automatically count data , Add the sum of statistics in the last row  */
    public boolean isStatistics() default false;

    /** *  Export field alignment （0： Default ;1： Keep to the left ;2： In the middle ;3： Keep right ） */
    public Align align() default Align.AUTO;

    /** *  Custom data processor  */
    public Class<?> handler() default ExcelHandlerAdapter.class;

    /** *  Custom data processor parameters  */
    public String[] args() default {
    };

    public enum Align
    {
    
        AUTO(0), LEFT(1), CENTER(2), RIGHT(3);
        private final int value;

        Align(int value)
        {
    
            this.value = value;
        }

        public int value()
        {
    
            return this.value;
        }
    }

    /** *  Field type （0： Export import ;1： Export only ;2： Import only ） */
    Type type() default Type.ALL;

    public enum Type
    {
    
        ALL(0), EXPORT(1), IMPORT(2);
        private final int value;

        Type(int value)
        {
    
            this.value = value;
        }

        public int value()
        {
    
            return this.value;
        }
    }

    public enum ColumnType
    {
    
        NUMERIC(0), STRING(1), IMAGE(2);
        private final int value;

        ColumnType(int value)
        {
    
            this.value = value;
        }

        public int value()
        {
    
            return this.value;
        }
    }
}

Two ：ExcelUtils Tool class

package com.qrqy.common.core.utils.poi;


import com.qrqy.common.core.annotation.Excel;
import com.qrqy.common.core.annotation.Excels;
import com.qrqy.common.core.text.Convert;
import com.qrqy.common.core.utils.DateUtils;
import com.qrqy.common.core.utils.StringUtils;
import com.qrqy.common.core.utils.file.FileTypeUtils;
import com.qrqy.common.core.utils.file.ImageUtils;
import com.qrqy.common.core.utils.reflect.ReflectUtils;
import com.qrqy.common.core.web.domain.AjaxResult;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;
import java.util.stream.Collectors;

/** * Excel Related treatment  * * @author sfwx */
public class ExcelUtils<T> {
    
    private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);

    /** * Excel sheet Maximum number of rows , Default 65536 */
    public static final int sheetSize = 65536;

    /** *  Worksheet name  */
    private String sheetName;

    /** *  Export type （EXPORT: Derived data ;IMPORT： Import template ） */
    private Excel.Type type;

    /** *  Workbook object  */
    private Workbook wb;

    /** *  Worksheet object  */
    private Sheet sheet;

    /** *  List of styles  */
    private Map<String, CellStyle> styles;

    /** *  Import and export data list  */
    private List<T> list;

    /** *  Annotation list  */
    private List<Object[]> fields;

    /** *  The current line number  */
    private int rownum;

    /** *  title  */
    private String title;

    /** *  Maximum height  */
    private short maxHeight;

    /** *  Statistical list  */
    private Map<Integer, Double> statistics = new HashMap<Integer, Double>();

    /** *  Digital format  */
    private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");

    /** *  Cell column to merge  */
    public ArrayList<Integer> mergeCells = new ArrayList<>();

    /** *  Entity object  */
    public Class<T> clazz;

    public ExcelUtils(Class<T> clazz) {
    
        this.clazz = clazz;
    }

    public void init(List<T> list, String sheetName, String title, Excel.Type type) {
    
        if (list == null) {
    
            list = new ArrayList<T>();
        }
        this.list = list;
        this.sheetName = sheetName;
        this.type = type;
        this.title = title;
        createExcelField();
        createWorkbook();
        createTitle();
    }

    /** *  establish excel First line title  */
    public void createTitle() {
    
        if (StringUtils.isNotEmpty(title)) {
    
            Row titleRow = sheet.createRow(rownum == 0 ? rownum++ : 0);
            titleRow.setHeightInPoints(30);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellStyle(styles.get("title"));
            titleCell.setCellValue(title);
            sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(),
                    this.fields.size() - 1));
        }
    }

    /** *  Yes excel By default, the first index name of the form is converted to list * * @param is  Input stream  * @return  Set after conversion  */
    public List<T> importExcel(InputStream is) throws Exception {
    
        return importExcel(is, 0);
    }

    /** *  Yes excel By default, the first index name of the form is converted to list * * @param is  Input stream  * @param titleNum  The number of lines occupied by the title  * @return  Set after conversion  */
    public List<T> importExcel(InputStream is, int titleNum) throws Exception {
    
        return importExcel(StringUtils.EMPTY, is, titleNum);
    }

    /** *  Yes excel The form specifies that the table index name is converted to list * * @param sheetName  Table index name  * @param titleNum  The number of lines occupied by the title  * @param is  Input stream  * @return  Set after conversion  */
    public List<T> importExcel(String sheetName, InputStream is, int titleNum) throws Exception {
    
        this.type = Excel.Type.IMPORT;
        this.wb = WorkbookFactory.create(is);
        List<T> list = new ArrayList<T>();
        //  If specified sheet name , Then take the specified sheet The content in   Otherwise, it will point to the... By default 1 individual sheet
        Sheet sheet = StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0);
        if (sheet == null) {
    
            throw new IOException(" file sheet non-existent ");
        }

        //  Get the row subscript of the last non empty row , For example, the total number of rows is n, The returned is n-1
        int rows = sheet.getLastRowNum();

        if (rows > 0) {
    
            //  Define a map To hold excel The sequence number of the column and field.
            Map<String, Integer> cellMap = new HashMap<String, Integer>();
            //  Get header 
            Row heard = sheet.getRow(titleNum);
            for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++) {
    
                Cell cell = heard.getCell(i);
                if (StringUtils.isNotNull(cell)) {
    
                    String value = this.getCellValue(heard, i).toString();
                    cellMap.put(value, i);
                } else {
    
                    cellMap.put(null, i);
                }
            }
            //  Only when there is data   Get all... Of the class field.
            List<Object[]> fields = this.getFields();
            Map<Integer, Object[]> fieldsMap = new HashMap<Integer, Object[]>();
            for (Object[] objects : fields) {
    
                Excel attr = (Excel) objects[1];
                Integer column = cellMap.get(attr.name());
                if (column != null) {
    
                    fieldsMap.put(column, objects);
                }
            }
            for (int i = titleNum + 1; i <= rows; i++) {
    
                //  From 2 The row starts fetching data , By default, the first row is the header .
                Row row = sheet.getRow(i);
                //  Judge whether the current line is empty 
                if (isRowEmpty(row)) {
    
                    continue;
                }
                T entity = null;
                for (Map.Entry<Integer, Object[]> entry : fieldsMap.entrySet()) {
    
                    Object val = this.getCellValue(row, entry.getKey());

                    //  If there is no instance, create a new .
                    entity = (entity == null ? clazz.newInstance() : entity);
                    //  from map Get the corresponding column field.
                    Field field = (Field) entry.getValue()[0];
                    Excel attr = (Excel) entry.getValue()[1];
                    //  Get type , And set the value according to the object type .
                    Class<?> fieldType = field.getType();
                    if (String.class == fieldType) {
    
                        String s = Convert.toStr(val);
                        if (StringUtils.endsWith(s, ".0")) {
    
                            val = StringUtils.substringBefore(s, ".0");
                        } else {
    
                            String dateFormat = field.getAnnotation(Excel.class).dateFormat();
                            if (StringUtils.isNotEmpty(dateFormat)) {
    
                                val = DateUtils.parseDateToStr(dateFormat, (Date) val);
                            } else {
    
                                val = Convert.toStr(val);
                            }
                        }
                    } else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val))) {
    
                        val = Convert.toInt(val);
                    } else if (Long.TYPE == fieldType || Long.class == fieldType) {
    
                        val = Convert.toLong(val);
                    } else if (Double.TYPE == fieldType || Double.class == fieldType) {
    
                        val = Convert.toDouble(val);
                    } else if (Float.TYPE == fieldType || Float.class == fieldType) {
    
                        val = Convert.toFloat(val);
                    } else if (BigDecimal.class == fieldType) {
    
                        val = Convert.toBigDecimal(val);
                    } else if (Date.class == fieldType) {
    
                        if (val instanceof String) {
    
                            val = DateUtils.parseDate(val);
                        } else if (val instanceof Double) {
    
                            val = DateUtil.getJavaDate((Double) val);
                        }
                    } else if (Boolean.TYPE == fieldType || Boolean.class == fieldType) {
    
                        val = Convert.toBool(val, false);
                    }
                    if (StringUtils.isNotNull(fieldType)) {
    
                        String propertyName = field.getName();
                        if (StringUtils.isNotEmpty(attr.targetAttr())) {
    
                            propertyName = field.getName() + "." + attr.targetAttr();
                        } else if (StringUtils.isNotEmpty(attr.readConverterExp())) {
    
                            val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());
                        } else if (!attr.handler().equals(ExcelHandlerAdapter.class)) {
    
                            val = dataFormatHandlerAdapter(val, attr);
                        }
                        ReflectUtils.invokeSetter(entity, propertyName, val);
                    }
                }
                list.add(entity);
            }
        }
        return list;
    }


    /** *  Yes list The data source imports the data in it into excel Forms  * * @param list  Export data set  * @param sheetName  The name of the worksheet  * @return  result  */
    public AjaxResult exportExcel(List<T> list, String sheetName, String filePath)
    {
    
        return exportExcel(list, sheetName, StringUtils.EMPTY,filePath);
    }


    /** *  Yes list The data source imports the data in it into excel Forms  * * @param list  Export data set  * @param sheetName  The name of the worksheet  * @param title  title  * @return  result  */
    public AjaxResult exportExcel(List<T> list, String sheetName, String title,String filePath)
    {
    
        this.init(list, sheetName, title, Excel.Type.EXPORT);
        return exportExcel(filePath);
    }

    /** *  Yes list The data source imports the data in it into excel Forms  * * @return  result  */
    public AjaxResult exportExcel(String filePath)
    {
    
        OutputStream out = null;
        try
        {
    
            writeSheet();
            // Generating uniqueness 
            //String filename = encodingFilename(sheetName);
            sheetName+= ".xlsx";
            out = new FileOutputStream(getAbsoluteFile(sheetName,filePath));
            wb.write(out);
        }
        catch (Exception e)
        {
    
            log.error(" export Excel abnormal {}", e.getMessage());
        }
        finally
        {
    
            IOUtils.closeQuietly(wb);
            IOUtils.closeQuietly(out);
        }
        return AjaxResult.success(sheetName);
    }


    /** *  Encoded file name  */
    public String encodingFilename(String filename)
    {
    
        filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
        return filename;
    }


    /** *  Get download path  * * @param filename  File name  */
    public String getAbsoluteFile(String filename,String filePath)
    {
    
        String downloadPath =filePath+filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists())
        {
    
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }

    /** *  Yes list The data source imports the data in it into excel Forms  * * @param response  Return the data  * @param list  Export data set  * @param sheetName  The name of the worksheet  * @return  result  * @throws IOException */
    public void exportExcel(HttpServletResponse response, List<T> list, String sheetName) {
    
        exportExcel(response, list, sheetName, StringUtils.EMPTY);
    }

    /** *  Yes list The data source imports the data in it into excel Forms  * * @param response  Return the data  * @param list  Export data set  * @param sheetName  The name of the worksheet  * @param title  title  * @return  result  * @throws IOException */
    public void exportExcel(HttpServletResponse response, List<T> list, String sheetName, String title) {
    
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        this.init(list, sheetName, title, Excel.Type.EXPORT);
        exportExcel(response);
    }

    /** *  Yes list The data source imports the data in it into excel Forms  * * @param sheetName  The name of the worksheet  * @return  result  */
    /** *  Yes list The data source imports the data in it into excel Forms  * * @param sheetName  The name of the worksheet  * @return  result  */
    public void importTemplateExcel(HttpServletResponse response, String sheetName) {
    
        importTemplateExcel(response, sheetName, StringUtils.EMPTY);
    }

    /** *  Yes list The data source imports the data in it into excel Forms  * * @param sheetName  The name of the worksheet  * @param title  title  * @return  result  */
    public void importTemplateExcel(HttpServletResponse response, String sheetName, String title) {
    
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        this.init(null, sheetName, title, Excel.Type.IMPORT);
        exportExcel(response);
    }

    /** *  Yes list The data source imports the data in it into excel Forms  * * @return  result  */
    public void exportExcel(HttpServletResponse response) {
    
        try {
    
            writeSheet();
            wb.write(response.getOutputStream());
        } catch (Exception e) {
    
            log.error(" export Excel abnormal {}", e.getMessage());
        } finally {
    
            IOUtils.closeQuietly(wb);
        }
    }

    /** *  Create and write data to Sheet */
    public void writeSheet() {
    
        //  Take out the total number of sheet.
        int sheetNo = Math.max(1, (int) Math.ceil(list.size() * 1.0 / sheetSize));
        for (int index = 0; index < sheetNo; index++) {
    
            createSheet(sheetNo, index);

            //  Produce a line 
            Row row = sheet.createRow(rownum);
            int column = 0;
            //  Write the column header name of each field 
            for (Object[] os : fields) {
    
                Excel excel = (Excel) os[1];
                this.createCell(excel, row, column++);
            }
            if (Excel.Type.EXPORT.equals(type)) {
    
                fillExcelData(index, row);
                addStatisticsRow();
            }
        }
    }

    /** *  fill excel data  * * @param index  Serial number  * @param row  Cell row  */
    public void fillExcelData(int index, Row row) {
    
        int startNo = index * sheetSize;
        int endNo = Math.min(startNo + sheetSize, list.size());
        for (int i = startNo; i < endNo; i++) {
    
            row = sheet.createRow(i + 1 + rownum - startNo);
            //  Get the exported object .
            T vo = (T) list.get(i);
            int column = 0;
            for (Object[] os : fields) {
    
                Field field = (Field) os[0];
                Excel excel = (Excel) os[1];
                this.addCell(excel, row, vo, field, column++);
            }
        }
        // merge cell ( You need to pass in the sequence number of the column to be merged )
        if(mergeCells.size() > 0){
    
            for (int mergeCell : mergeCells) {
    
                mergeCells(sheet, mergeCell, 1, sheet.getLastRowNum());
            }
        }
    }

    /** *  Create a table style  * * @param wb  Workbook object  * @return  List of styles  */
    private Map<String, CellStyle> createStyles(Workbook wb) {
    
        //  Write records , Corresponding to each record excel A line in the table 
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = wb.createFont();
        titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBold(true);
        style.setFont(titleFont);
        styles.put("title", style);

        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font totalFont = wb.createFont();
        totalFont.setFontName("Arial");
        totalFont.setFontHeightInPoints((short) 10);
        style.setFont(totalFont);
        styles.put("total", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.LEFT);
        styles.put("data1", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        styles.put("data2", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.RIGHT);
        styles.put("data3", style);

        return styles;
    }

    /** *  Create cells  */
    public Cell createCell(Excel attr, Row row, int column) {
    
        //  Create column 
        Cell cell = row.createCell(column);
        //  Write in the listed information 
        cell.setCellValue(attr.name());
        setDataValidation(attr, row, column);
        cell.setCellStyle(styles.get("header"));
        return cell;
    }

    /** *  Set cell information  * * @param value  Cell values  * @param attr  Annotations associated  * @param cell  Cell information  */
    public void setCellVo(Object value, Excel attr, Cell cell) {
    
        if (Excel.ColumnType.STRING == attr.cellType()) {
    
            cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());
        } else if (Excel.ColumnType.NUMERIC == attr.cellType()) {
    
            if (StringUtils.isNotNull(value)) {
    
                cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));
            }
        } else if (Excel.ColumnType.IMAGE == attr.cellType()) {
    
            ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
            String imagePath = Convert.toStr(value);
            if (StringUtils.isNotEmpty(imagePath)) {
    
                byte[] data = ImageUtils.getImage(imagePath);
                getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
                        cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
            }
        }
    }

    /** *  Get canvas  */
    public static Drawing<?> getDrawingPatriarch(Sheet sheet) {
    
        if (sheet.getDrawingPatriarch() == null) {
    
            sheet.createDrawingPatriarch();
        }
        return sheet.getDrawingPatriarch();
    }

    /** *  Get picture type , Set the picture insertion type  */
    public int getImageType(byte[] value) {
    
        String type = FileTypeUtils.getFileExtendName(value);
        if ("JPG".equalsIgnoreCase(type)) {
    
            return Workbook.PICTURE_TYPE_JPEG;
        } else if ("PNG".equalsIgnoreCase(type)) {
    
            return Workbook.PICTURE_TYPE_PNG;
        }
        return Workbook.PICTURE_TYPE_JPEG;
    }

    /** *  Create a table style  */
    public void setDataValidation(Excel attr, Row row, int column) {
    
        if (attr.name().indexOf(" notes ：") >= 0) {
    
            sheet.setColumnWidth(column, 6000);
        } else {
    
            //  Set column width 
            sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
        }
        //  If the prompt information is set, put the mouse up to prompt .
        if (StringUtils.isNotEmpty(attr.prompt())) {
    
            //  The default setting here is 2-101 Column prompt .
            setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
        }
        //  If set combo Property, this column can only be selected and cannot be entered 
        if (attr.combo().length > 0) {
    
            //  The default setting here is 2-101 Columns can only be selected and cannot be entered .
            setXSSFValidation(sheet, attr.combo(), 1, 100, column, column);
        }
    }

    /** *  Add cells  */
    public Cell addCell(Excel attr, Row row, T vo, Field field, int column) {
    
        Cell cell = null;
        try {
    
            //  Set row height 
            row.setHeight(maxHeight);
            //  according to Excel The setting in determines whether to export , Some cases need to be left blank , I want users to fill in this column .
            if (attr.isExport()) {
    
                //  establish cell
                cell = row.createCell(column);
                int align = attr.align().value();
                cell.setCellStyle(styles.get("data" + (align >= 1 && align <= 3 ? align : "")));

                //  Used to read the properties in the object 
                Object value = getTargetValue(vo, field, attr);
                String dateFormat = attr.dateFormat();
                String readConverterExp = attr.readConverterExp();
                String separator = attr.separator();
                if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value)) {
    
                    cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
                } else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value)) {
    
                    cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, separator));
                } else if (value instanceof BigDecimal && -1 != attr.scale()) {
    
                    cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).toString());
                } else if (!attr.handler().equals(ExcelHandlerAdapter.class)) {
    
                    cell.setCellValue(dataFormatHandlerAdapter(value, attr));
                } else {
    
                    //  Set column type 
                    setCellVo(value, attr, cell);
                }
                addStatisticsData(column, Convert.toStr(value), attr);
            }
        } catch (Exception e) {
    
            log.error(" export Excel Failure {}", e);
        }
        return cell;
    }

    /** *  Set up  POI XSSFSheet  Cell prompt  * * @param sheet  Forms  * @param promptTitle  Prompt title  * @param promptContent  Prompt content  * @param firstRow  Go ahead  * @param endRow  End line  * @param firstCol  Start the column  * @param endCol  End column  */
    public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
                              int firstCol, int endCol) {
    
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        dataValidation.createPromptBox(promptTitle, promptContent);
        dataValidation.setShowPromptBox(true);
        sheet.addValidationData(dataValidation);
    }

    /** *  Setting the value of some columns can only enter prefabricated data , Show drop-down box . * * @param sheet  To set up sheet. * @param textlist  The contents displayed in the drop-down box  * @param firstRow  Go ahead  * @param endRow  End line  * @param firstCol  Start the column  * @param endCol  End column  * @return  Set up sheet. */
    public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
    
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //  Load the contents of the drop-down list 
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
        //  Set the cell on which data validity is loaded , The four parameters are ： Start line 、 Terminate line 、 Start column 、 Terminate column 
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        //  Data validity objects 
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        //  Handle Excel Compatibility issues 
        if (dataValidation instanceof XSSFDataValidation) {
    
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
    
            dataValidation.setSuppressDropDownArrow(false);
        }

        sheet.addValidationData(dataValidation);
    }

    /** *  Parse the exported value  0= male ,1= Woman ,2= Unknown  * * @param propertyValue  Parameter values  * @param converterExp  Translation notes  * @param separator  Separator  * @return  The parsed value  */
    public static String convertByExp(String propertyValue, String converterExp, String separator) {
    
        StringBuilder propertyString = new StringBuilder();
        String[] convertSource = converterExp.split(",");
        for (String item : convertSource) {
    
            String[] itemArray = item.split("=");
            if (StringUtils.containsAny(separator, propertyValue)) {
    
                for (String value : propertyValue.split(separator)) {
    
                    if (itemArray[0].equals(value)) {
    
                        propertyString.append(itemArray[1] + separator);
                        break;
                    }
                }
            } else {
    
                if (itemArray[0].equals(propertyValue)) {
    
                    return itemArray[1];
                }
            }
        }
        return StringUtils.stripEnd(propertyString.toString(), separator);
    }

    /** *  Reverse parse value   male =0, Woman =1, Unknown =2 * * @param propertyValue  Parameter values  * @param converterExp  Translation notes  * @param separator  Separator  * @return  The parsed value  */
    public static String reverseByExp(String propertyValue, String converterExp, String separator) {
    
        StringBuilder propertyString = new StringBuilder();
        String[] convertSource = converterExp.split(",");
        for (String item : convertSource) {
    
            String[] itemArray = item.split("=");
            if (StringUtils.containsAny(separator, propertyValue)) {
    
                for (String value : propertyValue.split(separator)) {
    
                    if (itemArray[1].equals(value)) {
    
                        propertyString.append(itemArray[0] + separator);
                        break;
                    }
                }
            } else {
    
                if (itemArray[1].equals(propertyValue)) {
    
                    return itemArray[0];
                }
            }
        }
        return StringUtils.stripEnd(propertyString.toString(), separator);
    }

    /** *  Data processor  * * @param value  Data values  * @param excel  Data annotation  * @return */
    public String dataFormatHandlerAdapter(Object value, Excel excel) {
    
        try {
    
            Object instance = excel.handler().newInstance();
            Method formatMethod = excel.handler().getMethod("format", new Class[]{
    Object.class, String[].class});
            value = formatMethod.invoke(instance, value, excel.args());
        } catch (Exception e) {
    
            log.error(" Cannot format data  " + excel.handler(), e.getMessage());
        }
        return Convert.toStr(value);
    }

    /** *  Total statistics  */
    private void addStatisticsData(Integer index, String text, Excel entity) {
    
        if (entity != null && entity.isStatistics()) {
    
            Double temp = 0D;
            if (!statistics.containsKey(index)) {
    
                statistics.put(index, temp);
            }
            try {
    
                temp = Double.valueOf(text);
            } catch (NumberFormatException e) {
    
            }
            statistics.put(index, statistics.get(index) + temp);
        }
    }

    /** *  Create statistics row  */
    public void addStatisticsRow() {
    
        if (statistics.size() > 0) {
    
            Row row = sheet.createRow(sheet.getLastRowNum() + 1);
            Set<Integer> keys = statistics.keySet();
            Cell cell = row.createCell(0);
            cell.setCellStyle(styles.get("total"));
            cell.setCellValue(" total ");

            for (Integer key : keys) {
    
                cell = row.createCell(key);
                cell.setCellStyle(styles.get("total"));
                cell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));
            }
            statistics.clear();
        }
    }

    /** *  obtain bean Attribute value in  * * @param vo  Entity object  * @param field  Field  * @param excel  annotation  * @return  The final attribute value  * @throws Exception */
    private Object getTargetValue(T vo, Field field, Excel excel) throws Exception {
    
        Object o = field.get(vo);
        if (StringUtils.isNotEmpty(excel.targetAttr())) {
    
            String target = excel.targetAttr();
            if (target.indexOf(".") > -1) {
    
                String[] targets = target.split("[.]");
                for (String name : targets) {
    
                    o = getValue(o, name);
                }
            } else {
    
                o = getValue(o, target);
            }
        }
        return o;
    }

    /** *  With the properties of the class get Method to get the value in the form of  * * @param o * @param name * @return value * @throws Exception */
    private Object getValue(Object o, String name) throws Exception {
    
        if (StringUtils.isNotNull(o) && StringUtils.isNotEmpty(name)) {
    
            Class<?> clazz = o.getClass();
            Field field = clazz.getDeclaredField(name);
            field.setAccessible(true);
            o = field.get(o);
        }
        return o;
    }

    /** *  Get all defined fields  */
    private void createExcelField() {
    
        this.fields = getFields();
        this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
        this.maxHeight = getRowHeight();
    }

    /** *  Get field annotation information  */
    public List<Object[]> getFields() {
    
        List<Object[]> fields = new ArrayList<Object[]>();
        List<Field> tempFields = new ArrayList<>();
        tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
        tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
        for (Field field : tempFields) {
    
            //  Single note 
            if (field.isAnnotationPresent(Excel.class)) {
    
                Excel attr = field.getAnnotation(Excel.class);
                if (attr != null && (attr.type() == Excel.Type.ALL || attr.type() == type)) {
    
                    field.setAccessible(true);
                    fields.add(new Object[]{
    field, attr});
                }
            }

            //  Multi annotation 
            if (field.isAnnotationPresent(Excels.class)) {
    
                Excels attrs = field.getAnnotation(Excels.class);
                Excel[] excels = attrs.value();
                for (Excel attr : excels) {
    
                    if (attr != null && (attr.type() == Excel.Type.ALL || attr.type() == type)) {
    
                        field.setAccessible(true);
                        fields.add(new Object[]{
    field, attr});
                    }
                }
            }
        }
        // Filter out the position of the cells to be merged 
        if(fields.size()>0){
    
            int column = 0;
            for (Object[] os : fields){
    
                Excel excel = (Excel) os[1];
                if(excel.isMerge()){
    
                    this.mergeCells.add(column);
                }
                column++;
            }
        }
        return fields;
    }

    /** *  Get the maximum row height according to the annotation  */
    public short getRowHeight() {
    
        double maxHeight = 0;
        for (Object[] os : this.fields) {
    
            Excel excel = (Excel) os[1];
            maxHeight = maxHeight > excel.height() ? maxHeight : excel.height();
        }
        return (short) (maxHeight * 20);
    }

    /** *  Create a workbook  */
    public void createWorkbook() {
    
        this.wb = new SXSSFWorkbook(500);
        this.sheet = wb.createSheet();
        wb.setSheetName(0, sheetName);
        this.styles = createStyles(wb);
    }

    /** *  Create sheet  * * @param sheetNo sheet Number  * @param index  Serial number  */
    public void createSheet(int sheetNo, int index) {
    
        //  Set the name of the worksheet .
        if (sheetNo > 1 && index > 0) {
    
            this.sheet = wb.createSheet();
            this.createTitle();
            wb.setSheetName(index, sheetName + index);
        }
    }

    /** *  Get cell values  * * @param row  Get the line  * @param column  Get cell column number  * @return  Cell values  */
    public Object getCellValue(Row row, int column) {
    
        if (row == null) {
    
            return row;
        }
        Object val = "";
        try {
    
            Cell cell = row.getCell(column);
            if (StringUtils.isNotNull(cell)) {
    
                if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA) {
    
                    val = cell.getNumericCellValue();
                    if (DateUtil.isCellDateFormatted(cell)) {
    
                        val = DateUtil.getJavaDate((Double) val); // POI Excel  Date format conversion 
                    } else {
    
                        if ((Double) val % 1 != 0) {
    
                            val = new BigDecimal(val.toString());
                        } else {
    
                            val = new DecimalFormat("0").format(val);
                        }
                    }
                } else if (cell.getCellType() == CellType.STRING) {
    
                    val = cell.getStringCellValue();
                } else if (cell.getCellType() == CellType.BOOLEAN) {
    
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellType() == CellType.ERROR) {
    
                    val = cell.getErrorCellValue();
                }

            }
        } catch (Exception e) {
    
            return val;
        }
        return val;
    }

    /** *  Judge whether it is an empty line  * * @param row  Judged line  * @return */
    private boolean isRowEmpty(Row row) {
    
        if (row == null) {
    
            return true;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
    
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
    
                return false;
            }
        }
        return true;
    }

    /** * @Author mr * @Description //TODO  merge cell  * @param sheet * @param cellLine  Merged column number  * @param startRow  Go ahead  * @param endRow  Biko  * @Date 2020-06-29 **/
    private void mergeCells(Sheet sheet, int cellLine,int startRow, int endRow) {
    
        //  Get the data of the first row , For later comparison 
        String s_will = getCellValue(sheet.getRow(startRow),cellLine).toString();
        // Number of merged rows 
        int count = 0;
        // Data comparison , Start with the second line 
        for (int i = 2; i <= endRow; i++) {
    
            // Take out the data of the next row 
            String s_current = getCellValue(sheet.getRow(i),cellLine).toString();
            // Compare with the data in the opposite first row 
            if (s_will.equals(s_current) && !s_will.equals("")) {
    
                // Add one to the number of merged rows 
                count++;
            } else {
    
                // If the number of merged rows is greater than or equal to 1 Just merge , Now merge the number of rows to be merged 
                if(count >= 1){
    
                    // Merge the number of rows from the start row to the merge 
                    sheet.addMergedRegion(new CellRangeAddress( startRow, startRow+count,cellLine , cellLine));
                }
                // Initialization data , Make the next cell merge 
                startRow = i;
                s_will = s_current;
                count = 0;
            }
            // The last cell is merged 
            if (i == endRow && count > 0) {
    
                sheet.addMergedRegion(new CellRangeAddress(startRow,startRow+count ,cellLine , cellLine));
            }
        }
    }
}

Two ： Call mode

 Entity 
 /** *  Business address -- There is no need to merge  isCondition = true Do not participate in the merger  */
    @Excel(name = " Business address ",isCondition = true)
    private String businessAddress;
    /** *  goods - name --- Need merger  isCondition = true Do not participate in the merger  */
    @Excel(name = " goods - name ",isMerge = true)
    private String productName;
    /** *  goods - Number  isCondition = false Do not write by default  isMerge =true  Participation in Merger  */
    @Excel(name = " goods - Number ",isMerge = true)
    private Integer productCount;
    /** *  goods - The unit price  isCondition = false Do not write by default  isMerge =true  Participation in Merger  */
    @Excel(name = " goods - The unit price ",isMerge = true)
    private BigDecimal productAmount;

ExcelUtils<TbCheckBatchDetails> util = new ExcelUtils<TbCheckBatchDetails>(TbCheckBatchDetails.class);
util = new ExcelUtils<TbCheckBatchDetails>(TbCheckBatchDetails.class);
util.exportExcel(list,  " test ","D://");