JAVA poi Excel导入导出，可自定义单元格样式和锁定单元格，可设置单元格下拉数据

依赖

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

工具类
ps : 有三个类，一个实体，一个读取工具类，一个导出工具类
1，实体

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.awt.*;
import java.io.Serializable;
import java.util.List;
//数据实体
public class ExcelData implements Serializable {

    private static final long serialVersionUID = 6133772627258154184L;
    /**
     * 备注(可空，没有备注)
     */
    private List<RemarkData> remarkList;
    /**
     * 表头
     */
    private List<String> titles;
    /**
     * 表头行高（可空，默认行高）
     */
    private Integer titleHeight;

    /**
     * 数据--不可锁定（Object为单元格内容）
     */
    private List<List<Object>> rows;
    /**
     * 数据--可设置锁定不可编辑
     */
    private List<List<RowData>> rowsLock;
    /**
     * 下拉框设置数据（可空，不设置下拉）
     */
    private List<SelectData> selectDataList;

    /**
     * 页签名称
     */
    private String name;

    /**
     * 总数
     */
    private int total;

    public List<RemarkData> getRemarkList() {
        return remarkList;
    }

    public void setRemarkList(List<RemarkData> remarkList) {
        this.remarkList = remarkList;
    }

    public List<String> getTitles() {
        return titles;
    }

    public void setTitles(List<String> titles) {
        this.titles = titles;
    }

    public Integer getTitleHeight() {
        return titleHeight;
    }

    public void setTitleHeight(Integer titleHeight) {
        this.titleHeight = titleHeight;
    }

    public List<List<Object>> getRows() {
        return rows;
    }

    public void setRows(List<List<Object>> rows) {
        this.rows = rows;
    }

    public List<List<RowData>> getRowsLock() {
        return rowsLock;
    }

    public void setRowsLock(List<List<RowData>> rowsLock) {
        this.rowsLock = rowsLock;
    }

    public List<SelectData> getSelectDataList() {
        return selectDataList;
    }

    public void setSelectDataList(List<SelectData> selectDataList) {
        this.selectDataList = selectDataList;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getTotal() {
        return total;
    }

    public void setTotal(int total) {
        this.total = total;
    }

    public static RemarkData getRemarkData(String content,HorizontalAlignment horizontalAlignment,IndexedColors indexedColors,Color groundColor,Boolean isLocked,Integer mergedCell,Integer rowHeight,Integer fontSize) {
        RemarkData remarkData= new RemarkData();
        remarkData.setContent(content);
        remarkData.setHorizontalAlignment(horizontalAlignment);
        remarkData.setIndexedColors(indexedColors);
        remarkData.setGroundColor(groundColor);
        remarkData.setLocked(isLocked);
        remarkData.setMergedCell(mergedCell);
        remarkData.setRowHeight(rowHeight);
        remarkData.setFontSize(fontSize);
        return remarkData;
    }
    public static class RemarkData{
        //内容
        private String content;
        //水平位置布局（水平居中：HorizontalAlignment.CENTER，水平居左：HorizontalAlignment.LEFT）
        private HorizontalAlignment horizontalAlignment;
        //字体颜色（黑色：IndexedColors.BLACK，红色：IndexedColors.RED）
        private IndexedColors indexedColors;
        //背景颜色（可空，默认背景颜色）
        private Color groundColor;
        //是否锁定不可编辑
        private Boolean isLocked=false;
        //合并几行单元格
        private Integer mergedCell;
        //行高（可空，取默认）
        private Integer rowHeight;
        //字体大小（可空，取默认）
        private Integer fontSize;

        public String getContent() {
            return content;
        }

        public void setContent(String content) {
            this.content = content;
        }

        public HorizontalAlignment getHorizontalAlignment() {
            return horizontalAlignment;
        }

        public void setHorizontalAlignment(HorizontalAlignment horizontalAlignment) {
            this.horizontalAlignment = horizontalAlignment;
        }

        public IndexedColors getIndexedColors() {
            return indexedColors;
        }

        public void setIndexedColors(IndexedColors indexedColors) {
            this.indexedColors = indexedColors;
        }

        public Color getGroundColor() {
            return groundColor;
        }

        public void setGroundColor(Color groundColor) {
            this.groundColor = groundColor;
        }

        public Boolean getLocked() {
            return isLocked;
        }

        public void setLocked(Boolean locked) {
            isLocked = locked;
        }

        public Integer getMergedCell() {
            return mergedCell;
        }

        public void setMergedCell(Integer mergedCell) {
            this.mergedCell = mergedCell;
        }

        public Integer getRowHeight() {
            return rowHeight;
        }

        public void setRowHeight(Integer rowHeight) {
            this.rowHeight = rowHeight;
        }

        public Integer getFontSize() {
            return fontSize;
        }

        public void setFontSize(Integer fontSize) {
            this.fontSize = fontSize;
        }
    }
    public static RowData getRowData(Object content,Boolean isLocked) {
        RowData rowData= new RowData();
        rowData.setContent(content);
        rowData.setLocked(isLocked);
        return rowData;
    }
    public static class RowData {
        //内容
        private Object content;
        //是否锁定不可编辑
        private Boolean isLocked=false;

        public Object getContent() {
            return content;
        }

        public void setContent(Object content) {
            this.content = content;
        }

        public Boolean getLocked() {
            return isLocked;
        }

        public void setLocked(Boolean locked) {
            isLocked = locked;
        }
    }
    public static SelectData getSelectData(List<String> selectList,int firstRow,int lastRow,int firstCol,int lastCol) {
        SelectData data= new SelectData();
        data.setSelectList(selectList);
        data.setFirstRow(firstRow);
        data.setLastRow(lastRow);
        data.setFirstCol(firstCol);
        data.setLastCol(lastCol);
        return data;
    }
    public static class SelectData {
        //下拉框中的值
        List<String> selectList;
        //下标-从0开始
        int firstRow;
        int lastRow;
        int firstCol;
        int lastCol;

        public List<String> getSelectList() {
            return selectList;
        }

        public void setSelectList(List<String> selectList) {
            this.selectList = selectList;
        }

        public int getFirstRow() {
            return firstRow;
        }

        public void setFirstRow(int firstRow) {
            this.firstRow = firstRow;
        }

        public int getLastRow() {
            return lastRow;
        }

        public void setLastRow(int lastRow) {
            this.lastRow = lastRow;
        }

        public int getFirstCol() {
            return firstCol;
        }

        public void setFirstCol(int firstCol) {
            this.firstCol = firstCol;
        }

        public int getLastCol() {
            return lastCol;
        }

        public void setLastCol(int lastCol) {
            this.lastCol = lastCol;
        }
    }
}

2，读取工具类

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ReadExcel {

	protected final Log log = LogFactory.getLog(ReadExcel.class);
	private static DataFormatter formatter=new DataFormatter();
	// 总行数
	private int totalRows = 0;

	// 总条数
	private int totalCells = 0;

	// 错误信息接收器
	private String errorMsg;

	// 构造方法
	public ReadExcel() {
	}

	public String getErrorInfo() {
		return errorMsg;
	}

	/**
	 * 此方法两个参数InputStream是字节流。isExcel2003是excel是2003还是2007版本
	 *
	 * @param is
	 * @param isExcel2003
	 * @return
	 * @throws IOException
	 */
	public List<Map<String,Object>> getExcelInfo(InputStream is, boolean isExcel2003,List<String> columns,int startRow) {
	    
	    List<Map<String,Object>> retList = null;
	    try {
	        /** 根据版本选择创建Workbook的方式 */
	        Workbook wb = null;
	        // 当excel是2003时
	        if (isExcel2003) {
	            wb = new HSSFWorkbook(is);
	        } else {
	            log.info("before");
	            wb = new XSSFWorkbook(is);
	            log.info("after");
	        }
	        retList = readExcelValueMap(wb, columns,startRow);
	    } catch (Exception e) {
	        log.error("getExcelInfo exception : ",e);
	        log.info(e.getMessage());
	    }
	    return retList;
	}

	
    /** 描述 :读EXCEL文件
     * @Description:  
     * @param:        @param fileName
     * @param:        @param Mfile
     * @param:        @param columns
     * @param:        @param startRow    >=0
     * @param:        @return    
     * @return:       List<Map<String,Object>>    
     * @author        wangjunqi
     * @Date          2019年3月8日 上午10:05:54 
     */
    public List<Map<String, Object>> getExcelInfoMap(MultipartFile Mfile, List<String> columns, int startRow) {

        List<Map<String, Object>> retList = null;
        InputStream is = null;
        try {
            // 验证文件名是否合格
            if (!validateExcel(Mfile.getOriginalFilename())) {
                return null;
            }
            // 判断文件时2003版本还是2007版本
            boolean isExcel2003 = true;
            if (isExcel2007(Mfile.getOriginalFilename())) {
                isExcel2003 = false;
            }
            is = Mfile.getInputStream();
            retList = getExcelInfo(is, isExcel2003,columns,startRow);
            is.close();
        } catch (Exception e) {
            log.error("getExcelInfo exception : ", e);
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    is = null;
                    e.printStackTrace();
                }
            }
        }
        return retList;
    }

	// 得到总列数
	public int getTotalCells() {
		return totalCells;
	}

	// 得到总行数
	public int getTotalRows() {
		return totalRows;
	}

	/**
	 * 描述：验证EXCEL文件
	 *
	 * @param filePath
	 * @return
	 */
	public boolean validateExcel(String filePath) {
		if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
			errorMsg = "文件名不是excel格式";
			return false;
		}
		return true;
	}
	 /** 读取Excel里面的信息
     * @Description:  
     * @param:        @param wb
     * @param:        @param columns
     * @param:        @param startRow   起始行>=1
     * @param:        @return    
     * @return:       List<Map<String,Object>>    
     * @author        wangjunqi
     * @Date          2019年3月8日 上午9:59:46 
     */
    private List<Map<String,Object>> readExcelValueMap(Workbook wb, List<String> columns, int startRow) {
        startRow = startRow>=0?startRow:0;
        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(0);

        // 得到Excel的行数
        this.totalRows = sheet.getPhysicalNumberOfRows();

        Row titleRow;
        // 得到Excel的列数(前提是有行数)
        if (totalRows >= 2 && sheet.getRow(1) != null) {
            titleRow = sheet.getRow(1);
            this.totalCells = titleRow.getPhysicalNumberOfCells();
        }

        List<Map<String,Object>> retList = new ArrayList<Map<String,Object>>();
        for (int r = startRow; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (isRowEmpty(row)) {
                continue;
            }
            Map<String,Object> retMap = new HashMap<String,Object>();
            // 循环Excel的列
            totalCells = columns.size();
            int length = totalCells<columns.size()?totalCells:columns.size();
            for (int c = 0; c < length; c++) {
                Cell cell = row.getCell(c);
                String value = formatter.formatCellValue(cell);
                value = StringUtils.isNotEmpty(value) ? value : "";
                retMap.put(columns.get(c) , value);
            }

            retList.add(retMap);
        }
        return retList;
    }
    public boolean isRowEmpty(Row row) {
		for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
			Cell cell = row.getCell(c);
			if (cell != null) {
				return false;
			}
		}
		return true;
	}
    // 解决excel类型问题，获得数值
 	public String getValue(Cell cell) {
 		String value = "";
 		if (null == cell) {
 			return value;
 		}
 		return cell.getStringCellValue().toString();
 	}
	// @描述：是否是2003的excel，返回true是2003 
	public static boolean isExcel2003(String filePath)  {  
	    return filePath.matches("^.+\\.(?i)(xls)$");  
	}  
	 
	//@描述：是否是2007的excel，返回true是2007 
	public static boolean isExcel2007(String filePath)  {  
	    return filePath.matches("^.+\\.(?i)(xlsx)$");  
	}  
}

3，导出工具类

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelUtils {
    static Logger logger= LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * 使用浏览器选择路径下载
     * @param response
     * @param fileName
     * @param data
     * @throws Exception
     */
    public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
        // 告诉浏览器用什么软件可以打开此文件
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));
        exportExcel(data, response.getOutputStream());
    }

    public static int generateExcel(ExcelData excelData, String path) throws Exception {
        File f = new File(path);
        FileOutputStream out = new FileOutputStream(f);
        return exportExcel(excelData, out);
    }

    private static int exportExcel(ExcelData data, OutputStream out) throws Exception {
        XSSFWorkbook wb = new XSSFWorkbook();
        int rowIndex = 0;
        try {
            String sheetName = data.getName();
            if (null == sheetName) {
                sheetName = "Sheet1";
            }
            XSSFSheet sheet = wb.createSheet(sheetName);
            if(CollectionUtils.isEmpty(data.getRows()) && CollectionUtils.isNotEmpty(data.getRowsLock())){
                //设置表格锁定
                sheet.enableLocking();
            }
            rowIndex = writeExcel(wb, sheet, data);
            wb.write(out);
        } catch (Exception e) {
            logger.error("exportExcel系统异常：",e);
        } finally {
            //此处需要关闭 wb 变量
            out.close();
        }
        return rowIndex;
    }

    /**
     * 表不显示字段
     * @param wb
     * @param sheet
     * @param data
     * @return
     */
//    private static int writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
//        int rowIndex = 0;
//        writeTitlesToExcel(wb, sheet, data.getTitles());
//        rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
//        autoSizeColumns(sheet, data.getTitles().size() + 1);
//        return rowIndex;
//    }

    /**
     * 表显示字段
     * @param wb
     * @param sheet
     * @param data
     * @return
     */
    private static int writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
        if(CollectionUtils.isNotEmpty(data.getSelectDataList())){
            //设置下拉列表填充--row和col从0开始
            for(ExcelData.SelectData selectData : data.getSelectDataList()){
                setDropdownToExcel(sheet,selectData.getSelectList().toArray(new String[selectData.getSelectList().size()]),selectData.getFirstRow(),selectData.getLastRow(),selectData.getFirstCol(),selectData.getLastCol());
            }
        }
        int rowIndex = 0;
        rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles(),data.getRemarkList(),data.getTitleHeight());
        if(CollectionUtils.isNotEmpty(data.getRows())){
            rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
        }else if(CollectionUtils.isNotEmpty(data.getRowsLock())){
            rowIndex = writeRowsToExcelAllowLock(wb, sheet, data.getRowsLock(), rowIndex);
        }
        //设置列宽
        autoSizeColumns(sheet, data.getTitles().size() + 1);
        return rowIndex;
    }
    /**
     * 设置表头
     *
     * @param wb
     * @param sheet
     * @param titles
     * @return
     */
    private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles, List<ExcelData.RemarkData> remarkList,Integer titleHeight) {

        int rowIndex = 0;
        //设置备注
        if(CollectionUtils.isNotEmpty(remarkList)){
            for(ExcelData.RemarkData remarkData : remarkList){
                //备注样式
                XSSFCellStyle remarkStyleLeft = getTitleStyle(wb,remarkData.getHorizontalAlignment(),remarkData.getIndexedColors(),remarkData.getGroundColor(),remarkData.getLocked(),false,false,remarkData.getFontSize());
                //设置备注行
                Row remarkRow = sheet.createRow(rowIndex);
                if(remarkData.getRowHeight()!=null){
                    remarkRow.setHeightInPoints(remarkData.getRowHeight()); // 行高
                }else{
                    remarkRow.setHeightInPoints(20); // 行高
                }
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, remarkData.getMergedCell()-1));
                Cell remarkCell = remarkRow.createCell(0);
                remarkCell.setCellValue(remarkData.getContent());
                remarkCell.setCellStyle(remarkStyleLeft);
                rowIndex++;
            }
        }
        //标题样式水平居中
        XSSFCellStyle titleStyle = getTitleStyle(wb,HorizontalAlignment.CENTER,IndexedColors.BLACK,null,true,true,false,null);
        //标题样式水平居中换行
        XSSFCellStyle titleStyleWrapText = getTitleStyle(wb,HorizontalAlignment.CENTER,IndexedColors.BLACK,null,true,true,true,null);

        //设置标题
        Row titleRow = sheet.createRow(rowIndex);
        if(titleHeight!=null){
            titleRow.setHeightInPoints(titleHeight); // 行高
        }else{
            titleRow.setHeightInPoints(20); // 行高
        }
        int colIndex = 0;
        for (String field : titles) {
            Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(field);
            if(field.indexOf("\r\n")>-1) {
                cell.setCellStyle(titleStyleWrapText);
            }else {
                cell.setCellStyle(titleStyle);
            }
            colIndex++;
        }
        rowIndex++;
        return rowIndex;
    }
    /**
     * 获取表头样式
     */
    private static XSSFCellStyle getTitleStyle(XSSFWorkbook wb,HorizontalAlignment horizontalAlignment,IndexedColors indexedColors,Color groundColor,boolean isLocked,boolean isSetBorder,boolean isWrapText,Integer fontSize) {
        Font titleFont = wb.createFont();
        //设置字体
        titleFont.setFontName("Century");
        //设置粗体
        titleFont.setBold(true);
        //设置字号
        if(fontSize==null){
            fontSize=12;
        }
        titleFont.setFontHeightInPoints(Short.valueOf(String.valueOf(fontSize)));
        //设置颜色
        titleFont.setColor(indexedColors.index);

        XSSFCellStyle titleStyle = wb.createCellStyle();
        //水平居中
        titleStyle.setAlignment(horizontalAlignment);
        //垂直居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        if(isWrapText){
            titleStyle.setWrapText(true);
        }
        //设置图案颜色
        if(groundColor!=null){
            titleStyle.setFillForegroundColor(new XSSFColor(groundColor));
        }else{
            titleStyle.setFillForegroundColor(new XSSFColor(new Color(218, 218, 218)));
        }
        //设置图案样式
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);
        titleStyle.setLocked(isLocked);
        if(isSetBorder){
            setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
        }
        return titleStyle;
    }

    /**
     * 设置内容
     *
     * @param wb
     * @param sheet
     * @param rows
     * @param rowIndex
     * @return
     */
    private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
        int colIndex;

        XSSFCellStyle dataStyle = getCellStyle(wb,false,false);
        XSSFCellStyle dataStyleWrapText = getCellStyle(wb,true,false);
        if(rows!=null && !rows.isEmpty()){
            for (List<Object> rowData : rows) {
                Row dataRow = sheet.createRow(rowIndex);
                dataRow.setHeightInPoints(20);
                colIndex = 0;
                for (Object cellData : rowData) {
                    Cell cell = dataRow.createCell(colIndex);
                    if (cellData != null) {
                        cell.setCellValue(cellData.toString());
                        if(cellData.toString().indexOf("\r\n")>-1) {
                            cell.setCellStyle(dataStyleWrapText);
                        }else {
                            cell.setCellStyle(dataStyle);
                        }
                    } else {
                        cell.setCellValue("");
                        cell.setCellStyle(dataStyle);
                    }
                    colIndex++;
                }
                rowIndex++;
            }
        }
        return rowIndex;
    }

    /**
     * 设置内容--支持设置锁定不可编辑
     *
     * @param wb
     * @param sheet
     * @param rowsLock
     * @param rowIndex
     * @return
     */
    private static int writeRowsToExcelAllowLock(XSSFWorkbook wb, Sheet sheet, List<List<ExcelData.RowData>> rowsLock, int rowIndex) {
        int colIndex;

        XSSFCellStyle dataStyle = getCellStyle(wb,false,false);
        XSSFCellStyle dataStyleWrapText = getCellStyle(wb,true,false);
        XSSFCellStyle dataStyleLocked = getCellStyle(wb,false,true);
        XSSFCellStyle dataStyleWrapTextLocked = getCellStyle(wb,true,true);
        if(CollectionUtils.isNotEmpty(rowsLock)){
            for (List<ExcelData.RowData> rowDataList : rowsLock) {
                Row dataRow = sheet.createRow(rowIndex);
                dataRow.setHeightInPoints(20);
                colIndex = 0;
                for (ExcelData.RowData rowData : rowDataList) {
                    Cell cell = dataRow.createCell(colIndex);
                    boolean isLock=rowData.getLocked();
                    String content=rowData.getContent() == null?"":rowData.getContent().toString();
                    cell.setCellValue(content);
                    if(content.indexOf("\r\n")>-1) {
                        if(isLock){
                            cell.setCellStyle(dataStyleWrapTextLocked);
                        }else{
                            cell.setCellStyle(dataStyleWrapText);
                        }
                    }else {
                        if(isLock){
                            cell.setCellStyle(dataStyleLocked);
                        }else{
                            cell.setCellStyle(dataStyle);
                        }
                    }
                    colIndex++;
                }
                rowIndex++;
            }
        }
        return rowIndex;
    }

    /**
     * 自动调整列宽
     *
     * @param sheet
     * @param columnNumber
     */
    private static void autoSizeColumns(Sheet sheet, int columnNumber) {
        for (int i = 0; i < columnNumber; i++) {
//            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
            sheet.setColumnWidth(i, 3500);
//            int newWidth = (int) (sheet.getColumnWidth(i) + 100);
//            if (newWidth > orgWidth) {
//                sheet.setColumnWidth(i, newWidth);
//            } else {
//                sheet.setColumnWidth(i, orgWidth);
//            }
        }
    }

    /**
     * 获取单元格样式
     */
    private static XSSFCellStyle getCellStyle(XSSFWorkbook wb,boolean isWrapText,boolean isLocked) {
        Font dataFont = wb.createFont();
        dataFont.setFontName("SimHei");
        dataFont.setFontHeightInPoints((short) 12);
        dataFont.setColor(IndexedColors.BLACK.index);
        XSSFCellStyle dataStyle = wb.createCellStyle();
        if(isWrapText){
            dataStyle.setWrapText(true);
        }
        dataStyle.setAlignment(HorizontalAlignment.LEFT);//水平布局
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直布局
        dataStyle.setFont(dataFont);
        dataStyle.setLocked(isLocked);
        setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
        return dataStyle;
    }
    /**
     * 设置边框
     *
     * @param style
     * @param border
     * @param color
     */
    private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(BorderSide.TOP, color);
        style.setBorderColor(BorderSide.LEFT, color);
        style.setBorderColor(BorderSide.RIGHT, color);
        style.setBorderColor(BorderSide.BOTTOM, color);
    }
    /**
     * <p>
     * Description: 设置下拉框到excel
     * </p>
     * @author tanxin
     * @param sheet
     * @param selectList 下拉框中的值
     * @param firstRow 下标-从0开始
     * @param lastRow
     * @param firstCol
     * @param lastCol
     */
    private static void setDropdownToExcel(Sheet sheet,String[] selectList, int firstRow, int lastRow, int firstCol, int lastCol ){
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol)  ;
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint createExplicitListConstraint = dataValidationHelper.createExplicitListConstraint(selectList);
        DataValidation createValidation = dataValidationHelper.createValidation(createExplicitListConstraint, regions);
        //处理Excel兼容性问题
        if (createValidation instanceof XSSFDataValidation) {
            createValidation.setSuppressDropDownArrow(true);
            createValidation.setShowErrorBox(true);
        } else {
            createValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(createValidation);
    }
}

测试类

	//导入
    @ResponseBody
    @PostMapping("/imports")
    public ResultVo<List<JSONObject>> imports(@ApiParam("导入文件") @RequestParam("file") MultipartFile file ) {
        try{
            // 处理EXCEL
	        ReadExcel readExcel = new ReadExcel();
	        // 获得解析excel方法，此处按列排序设置key，userName为第一列
	        List<String> columns = new ArrayList<>();
	        columns.add("userName");
	        columns.add("age");
	        columns.add("sex");
	        //读取数据，数字表示从第几行开始，第一行为0
	        List<Map<String, Object>> retList = readExcel.getExcelInfoMap(file,columns,1);
	        for(Map<String, Object> obj : retList) {
	        	String userName=obj.get("userName")==null?"":obj.get("userName").toString();
	        }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

	//导出
	@ResponseBody
   @PostMapping("/download")
   public void download(@RequestBody Bean Bean, HttpServletResponse response ) throws Exception {
       //设置下拉填充列表数据
       List<String> nameSelect=new ArrayList<>();
        nameSelect.add("1");
        nameSelect.add("2");
        nameSelect.add("3");
        //设置下拉，第5-51行，4-11列，单元格填充下拉数据
        List<ExcelData.SelectData> selectDataList=new ArrayList<>();
        selectDataList.add(ExcelData.getSelectData(nameSelect,4,50,3,10));
        //备注
        int remarkMergedCell=11;//设置单行单元格合并数
        List<ExcelData.RemarkData> remarkList=new ArrayList<>();
        remarkList.add(ExcelData.getRemarkData("备注1", HorizontalAlignment.LEFT, IndexedColors.WHITE,new Color(62, 30, 102),true,remarkMergedCell,30,20));
        remarkList.add(ExcelData.getRemarkData("备注2", HorizontalAlignment.LEFT, IndexedColors.RED,new Color(255, 255, 255),true,remarkMergedCell,null,null));
        remarkList.add(ExcelData.getRemarkData("备注3", HorizontalAlignment.LEFT, IndexedColors.BLACK,new Color(255, 255, 255),true,remarkMergedCell,null,null));
        //设置数据
        List<List<ExcelData.RowData>> rowsLock = new ArrayList<>();
        for(int i=0;i<50;i++){
            List<ExcelData.RowData> row = new ArrayList<>();
            row.add(ExcelData.getRowData("张三",true));//为true则锁定单元格不可编辑
            row.add(ExcelData.getRowData("20",true));
            row.add(ExcelData.getRowData("男",true));
            rowsLock.add(row);
        }
        ExcelData data = new ExcelData();
        data.setName("数据");
        //设置表头
        List<String> titles = new ArrayList<>();
        titles.add("姓名");
        titles.add("年龄");
        titles.add("性别");
        data.setRemarkList(remarkList);
        data.setTitles(titles);
        data.setTitleHeight(35);//标题行高
        data.setRowsLock(rowsLock);//设置导出数据
        data.setSelectDataList(selectDataList);//下拉数据设置
        ExcelUtils.exportExcel(response, "数据文件" ,data);
   }