java使用阿里开源（easyExcel ）导入导出(列锁定单元格、表头合并、导出类型限制、锁定单元格增加底色、设置密码、隐藏列，下拉框，设置提示信息 等)
示例图
导入pom依赖
<!-- easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.4</version>
</dependency>
编写工具类
- 复杂表头样式信息
/**
* 复杂表头样式信息，包含需要自定义的表头坐标及样式
*/
public class ComplexHeadStyles {
/**
* 表头横坐标 - 行
* */
private Integer x;
/**
* 表头纵坐标 - 列
* */
private Integer y;
/**
* 内置颜色 背景颜色
* */
private Short indexColor;
// 字体 颜色 由列控制
private Short colorIndex;
public ComplexHeadStyles(){
}
public ComplexHeadStyles(Integer x, Integer y, Short indexColor){
this.x=x;
this.y=y;
this.indexColor=indexColor;
}
public ComplexHeadStyles(Integer x, Integer y, Short indexColor, Short colorIndex) {
this.x = x;
this.y = y;
this.indexColor = indexColor;
this.colorIndex = colorIndex;
}
private void setCroods(Integer x, Integer y){
this.x=x;
this.y=y;
}
//get set 方法省略
}
2.导出相关工具类：
public class EasyExcelParams {
/**
* 文件名
*/
private String fileName;
/**
* sheet名
*/
private String sheetName;
/**
* 是否需要表头
*/
private Boolean needHead;
/**
* 导出数据
*/
private List data;
/**
* 数据模型类型
*/
private Class dataModelClazz;
/**
* 响应
*/
private HttpServletResponse response;
/**
* 单元格样式
*/
private ExcelStyleConfig styleConfig;
/**
* 合并索引数
*/
private List<MergeCellIndex> mergeCellIndices;
@Override
public String toString() {
return "EasyExcelParams{" +
"fileName='" + fileName + '\'' +
", sheetName='" + sheetName + '\'' +
", needHead=" + needHead +
", data=" + data +
", dataModelClazz=" + dataModelClazz +
", response=" + response +
", styleConfig=" + styleConfig +
", mergeCellIndices=" + mergeCellIndices +
'}';
}
@NoArgsConstructor
@AllArgsConstructor
public static class MergeCellIndex {
/**
* 开始行
*/
private Integer firstRowIndex;
/**
* 结束行
*/
private Integer lastRowIndex;
/**
* 开始列
*/
private Integer firstColumnIndex;
/**
* 结束列
*/
private Integer lastColumnIndex;
@Override
public String toString() {
return "MergeCellIndex{" +
"firstRowIndex=" + firstRowIndex +
", lastRowIndex=" + lastRowIndex +
", firstColumnIndex=" + firstColumnIndex +
", lastColumnIndex=" + lastColumnIndex +
'}';
}
//get set .....
}
/**
* 不合并和不锁定构造
*/
public EasyExcelParams(String fileName, String sheetName, Boolean needHead, List data, Class dataModelClazz, HttpServletResponse response) {
this.fileName = fileName;
this.sheetName = sheetName;
this.needHead = needHead;
this.data = data;
this.dataModelClazz = dataModelClazz;
this.response = response;
}
/**
* 对于非空字典判空
*/
public boolean isValid() {
return ObjectUtils.allNotNull(fileName, data, response, dataModelClazz);
//get set ....
}
3.导出工具类：封装一些方法导出数据到excel，导出自动关闭流，不需要手动去关闭
@Component
public class EasyExcelUtil {
private static final String EXCEL_SECRET_CODE = "设置表格密码";
private static final String EXCEL_VERSION = "1.0";
private EasyExcelUtil() {
}
/**
* 导出2007版Excel
*/
public static void exportExcel2007Format(EasyExcelParams params) throws IOException {
exportExcel(params);
}
/**
* 导出Excel实现
*/
private static void exportExcel(EasyExcelParams params) throws IOException {
Validate.isTrue(params.isValid(), "参数错误!");
prepareResponds(params.getFileName(), params.getResponse());
ServletOutputStream outputStream = params.getResponse().getOutputStream();
ExcelWriterBuilder builder = new ExcelWriterBuilder();
builder.sheet(params.getSheetName());
builder.head(params.getDataModelClazz());
builder.file(outputStream);
builder.excelType(ExcelTypeEnum.XLSX);
builder.needHead(true);
builder.registerWriteHandler(params.getStyleConfig());
WriteSheet sheet = new WriteSheet();
sheet.setSheetName(params.getSheetName());
sheet.setSheetNo(1);
ExcelWriter writer = builder.build();
writer.write(params.getData(), sheet);
if (!CollectionUtils.isEmpty(params.getMergeCellIndices())) {
for (EasyExcelParams.MergeCellIndex mergeCellIndex : params.getMergeCellIndices()) {
writer.merge(mergeCellIndex.getFirstRowIndex(), mergeCellIndex.getLastRowIndex(), mergeCellIndex.getFirstColumnIndex(), mergeCellIndex.getLastColumnIndex());
}
}
writer.finish();
outputStream.close();
}
/**
* 将文件输出到浏览器(导出)
*/
private static void prepareResponds(String fileName, HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8'zh_cn'" + fileName + ExcelTypeEnum.XLSX.getValue());
}
/**
* 校验导入文件是否是Excel格式
*/
public static boolean checkExcelStyle(MultipartFile file) {
String filename = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf('.'));
return filename.equals(ExcelTypeEnum.XLSX.getValue());
}
/**
* 检查Excel的密码
*/
public static boolean checkExcelPassword(String secretCode) {
return EXCEL_SECRET_CODE.equals(secretCode);
}
/**
* 检查Excel的密码
*/
public static boolean checkExcelVersion(String version) {
return EXCEL_VERSION.equals(version);
}
}
4.导出格式配置类 导出样式配置类，需要继承（CellWriteHandler）写入handler类来重写里边的方法做逻辑处理，支持隐藏列，锁定列，表单保护密码，表单背景颜色，数据格式校验限制等
public class ExcelStyleConfig extends AbstractCellStyleStrategy implements CellWriteHandler {
/**
* 复杂表头自定义样式队列，先进先出，方便存储
* */
private ArrayBlockingQueue<ComplexHeadStyles> headStylesQueue;
/**
* WorkBoot
* */
private Workbook workbook;
/**
* 需要锁定的列集合
*/
private List<Integer> columnList;
/**
* 样式类
*/
private CellStyle cellStyle;
/**
* 隐藏索引数
*/
private List<Integer> hiddenIndices;
/**
* 限制那一列为数值型 开头结尾
*/
private List<Integer> columnNumList;
/**
* 限制哪一行为数值开头结尾集合
*/
private List<Integer> rowNumList;
/**
* 使用行 （以哪一行为准 为导出的表格复杂表头位置、宽度 ）
*/
private Integer useRow=0;
/**
* 下拉框设置字段
*/
// 批注<列的下标,批注内容>
private HashMap<Integer,String> annotationsMap;
// 下拉框值
private HashMap<Integer,String[]> dropDownMap;
public ExcelStyleConfig(List<Integer> columnList) {
this.columnList = columnList;
}
public ExcelStyleConfig(ArrayBlockingQueue<ComplexHeadStyles> headStylesQueue,HashMap<Integer,String> annotationsMap,HashMap<Integer,String[]> dropDownMap,Integer useRow) {
this.headStylesQueue=headStylesQueue;
this.annotationsMap=annotationsMap;
this.dropDownMap=dropDownMap;
this.useRow=useRow;
}
public ExcelStyleConfig(List<Integer> columnList, List<Integer> columnNumList, List<Integer> hiddenIndices, List<Integer> rowNumList) {
this.columnList = columnList;
this.hiddenIndices = hiddenIndices;
this.columnNumList = columnNumList;
this.rowNumList = rowNumList;
}
public ExcelStyleConfig(List<Integer> columnList, List<Integer> columnNumList) {
this.columnList = columnList;
this.columnNumList = columnNumList;
}
public ExcelStyleConfig(List<Integer> columnList, List<Integer> columnNumList, List<Integer> hiddenIndices) {
this.columnList = columnList;
this.columnNumList = columnNumList;
this.hiddenIndices = hiddenIndices;
}
public ExcelStyleConfig(List<Integer> columnList, List<Integer> columnNumList, List<Integer> hiddenIndices,HashMap<Integer,String> annotationsMap,HashMap<Integer,String[]> dropDownMap) {
this.columnList = columnList;
this.columnNumList = columnNumList;
this.hiddenIndices = hiddenIndices;
this.annotationsMap=annotationsMap;
this.dropDownMap=dropDownMap;
}
public ExcelStyleConfig(ArrayBlockingQueue<ComplexHeadStyles> headStylesQueue,List<Integer> columnList, List<Integer> columnNumList, List<Integer> hiddenIndices,HashMap<Integer,String> annotationsMap,HashMap<Integer,String[]> dropDownMap) {
this.headStylesQueue=headStylesQueue;
this.columnList = columnList;
this.columnNumList = columnNumList;
this.hiddenIndices = hiddenIndices;
this.annotationsMap=annotationsMap;
this.dropDownMap=dropDownMap;
}
public ExcelStyleConfig(ArrayBlockingQueue<ComplexHeadStyles> headStylesQueue,List<Integer> columnList, List<Integer> columnNumList, List<Integer> hiddenIndices,Integer useRow,HashMap<Integer,String> annotationsMap,HashMap<Integer,String[]> dropDownMap) {
this.headStylesQueue=headStylesQueue;
this.columnList = columnList;
this.columnNumList = columnNumList;
this.hiddenIndices = hiddenIndices;
this.useRow=useRow;
this.annotationsMap=annotationsMap;
this.dropDownMap=dropDownMap;
}
/**
* 在创建单元格之前调用
*/
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
cellStyle = writeSheetHolder.getSheet().getWorkbook().createCellStyle();
}
/**
* 在单元格创建后调用
*/
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 下边框
cellStyle.setBorderBottom(BorderStyle.THIN);
// 左边框
cellStyle.setBorderLeft(BorderStyle.THIN);
// 上边框
cellStyle.setBorderTop(BorderStyle.THIN);
// 右边框
cellStyle.setBorderRight(BorderStyle.THIN);
// 水平对齐方式
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 垂直对齐方式
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setLocked(false);
if (!CollectionUtils.isEmpty(hiddenIndices) && hiddenIndices.contains(cell.getColumnIndex())) {
// 设置隐藏列
writeSheetHolder.getSheet().setColumnHidden(cell.getColumnIndex(), true);
}
if (!CollectionUtils.isEmpty(columnList) && columnList.contains(cell.getColumnIndex())) {
// 设置表单保护密码
writeSheetHolder.getSheet().protectSheet("whrcan-password");
// 设置锁定单元格
cellStyle.setLocked(true);
//设置背景颜色
cellStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
}
if (!CollectionUtils.isEmpty(columnNumList) && columnNumList.contains(cell.getColumnIndex())) {
// --- 数据有效性 只允许输入整数 ---
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
//校验数值格式 只能输入整数0 -xx 范围内
DataValidationConstraint constraintNum = new XSSFDataValidationConstraint(
DataValidationConstraint.ValidationType.INTEGER,
DataValidationConstraint.OperatorType.BETWEEN, "0", "100000000");
CellRangeAddressList regionNumber = new CellRangeAddressList(BigInteger.ONE.intValue(), rowNumList.get(rowNumList.size() - 1), columnNumList.get(0), columnNumList.get(columnNumList.size() - 1));
DataValidation validationNum = helper.createValidation(constraintNum, regionNumber);
//输入错误提示
validationNum.createErrorBox("输入值错误", "请输入0-100000000之间的数字");
validationNum.setShowErrorBox(true);
sheet.addValidationData(validationNum);
}
// 填充单元格样式
cell.setCellStyle(cellStyle);
}
/**
* 在单元上的所有操作完成后调用
*/
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if(isHead){
// 设置列宽
Sheet sheet = writeSheetHolder.getSheet();
if (1==cell.getRowIndex()){
//获取当前列 设置宽度
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
setColumnWidth(writeSheetHolder,cellDataList,cell,head,cell.getRowIndex(),isHead);
writeSheetHolder.getSheet().getRow(0).setHeight((short)(1.8*256));
}
Drawing<?> drawing = sheet.createDrawingPatriarch();
cell.setCellStyle(cellStyle);
if (null != annotationsMap && annotationsMap.containsKey(cell.getColumnIndex())&& useRow==cell.getRowIndex()) {
// 批注内容
String context = annotationsMap.get(cell.getColumnIndex());
// 创建绘图对象
cell.removeCellComment();
Comment comment=drawing.createCellComment(new XSSFClientAnchor(0, 0, 0,0, (short) cell.getColumnIndex(), 1, (short) 0, 0));
comment.setString(new XSSFRichTextString(context));
cell.setCellComment(comment);
}
setHeadCellStyle(cell,head,relativeRowIndex);
if(null != dropDownMap &&
!dropDownMap.isEmpty() &&
dropDownMap.containsKey(cell.getColumnIndex())){
String[] datas = dropDownMap.get(cell.getColumnIndex());
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
DataValidationConstraint dvConstraint = dvHelper
.createExplicitListConstraint(datas);
CellRangeAddressList addressList = null;
DataValidation validation = null;
for (int i = 1; i < 1000; i++) {
addressList = new CellRangeAddressList(i, i, cell.getColumnIndex(), cell.getColumnIndex());
validation = dvHelper.createValidation(
dvConstraint, addressList);
sheet.addValidationData(validation);
}
}
}
}
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<>();
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth= this.dataLength(cellDataList, cell, isHead);
if (2==cell.getColumnIndex()||4==cell.getColumnIndex()||7==cell.getColumnIndex()){
columnWidth=columnWidth/2+1;
}
if (columnWidth >= 0) {
if (columnWidth > 255) {
columnWidth = 255;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
/**
* 计算当前列的宽度
*/
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = (CellData)cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch(type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
@Override
protected void initCellStyle(Workbook workbook) {
// 初始化信息时，保存Workbook对象，转换时需要使用
this.workbook=workbook;
}
@Override
protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
WriteCellStyle writeCellStyle=new WriteCellStyle();
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontName("宋体");
headWriteFont.setFontHeightInPoints((short)12);
headWriteFont.setBold(true);
if(headStylesQueue !=null && ! headStylesQueue.isEmpty()){
ComplexHeadStyles complexHeadStyle=headStylesQueue.peek();
// 取出队列中的自定义表头信息，与当前坐标比较，判断是否相符
if(cell.getColumnIndex() == complexHeadStyle.getY() && relativeRowIndex.equals(complexHeadStyle.getX())){
// 设置自定义的表头样式
writeCellStyle.setFillForegroundColor(complexHeadStyle.getIndexColor());
// 样式出队
headStylesQueue.poll();
}
if (cell.getColumnIndex() == complexHeadStyle.getY() && useRow==cell.getRowIndex()) {
// 设置字体颜色
headWriteFont.setColor(complexHeadStyle.getColorIndex());
}
}
writeCellStyle.setWriteFont(headWriteFont);
// WriteCellStyle转换为CellStyle
CellStyle headCellStyle = StyleUtil.buildHeadCellStyle(workbook, writeCellStyle);
// 设置表头样式
cell.setCellStyle(headCellStyle);
}
@Override
protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
}
}
5.导入监听:使用easyexcel导入excel时需要自写一个监听器去实现 （AnalysisEventListener）类，泛型为你需要导入数据对应实体类，在 （invoke）方法中获取数据做相应的逻辑处理最终放入全局集合中进行保存使用。
@NoArgsConstructor
public class UploadStandardDeviceListener extends AnalysisEventListener<DtoRoomExportExcel> {
private static final Logger log = LoggerFactory.getLogger(UploadStandardDeviceListener.class);
// private List<DtoFwxxExportExcel> detailList;
private List<DtoRoomExportExcel> detailList;
@Autowired
public UploadStandardDeviceListener(List<DtoRoomExportExcel> detailList) {
this.detailList = detailList;
}
@Override
public void invoke(DtoRoomExportExcel data, AnalysisContext analysisContext) {
// log.info("导入当前标准库配置监听器解析数据:{}", JSON.toJSONString(data, true));
detailList.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
/**
* 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
*
* @param exception 异常
* @param context 上下文
*/
@Override
public void onException(Exception exception, AnalysisContext context) {
log.error("解析失败,但是继续解析下一行:{}", exception.getMessage());
//如果是某一个单元格的转换异常,获取具体行号
//如果要获取头的信息 配合 invokeHeadMap 使用
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
log.error("第{}行,第{}列请输入数值类型", excelDataConvertException.getRowIndex(),
excelDataConvertException.getColumnIndex());
}
}
public List<DtoRoomExportExcel> getDetailList() {
return detailList;
}
public void setDetailList(List<DtoRoomExportExcel> detailList) {
this.detailList = detailList;
}
public static Logger getLog() {
return log;
}
@Override
public String toString() {
return "UploadStandardDeviceListener{" +
"detailList=" + detailList +
'}';
}
}
实体编写
public class DtoRoomExportExcel extends BaseRowModel {
@ExcelProperty(value = {"门牌号", "楼栋号*"}, index = 0)
private String buildingName;
@ExcelProperty(value = {"门牌号", "单 元*"}, index = 1)
private String unit;
@ExcelProperty(value = {"门牌号", "单元号*\n（排序）"}, index = 2)
private String unitSort;
@ExcelProperty(value = {"门牌号", "楼 层*"}, index = 3)
private String floor;
............
}
导出相关代码
public Message<String> exportExcelFwxxBatch(HttpServletResponse response, HttpServletRequest request
) {
//获取需要导出的数据
List<DtoRoomExportExcel> houseRoomList = service.getHouseRoomList(xmId, dh);
Message<String> message = new Message<String>();
try {
//设置excel名称以及sheet名称，是否需要表头
EasyExcelParams params = new EasyExcelParams("房屋信息列表", xmmc, true, houseRoomList, DtoRoomExportExcel.class, response);
//设置样式 校验格式 可添加表格下拉框值与表头提示信息，下标从0开始
//WhrcanConstants.annotationsMap 表格导出提示信息 HashMap<Integer, String>
//WhrcanConstants.dropDownMap 下拉框值 HashMap<Integer, String[]>
ArrayBlockingQueue<ComplexHeadStyles> complexHeadStyles = DtoRoomExportExcel.complexHeadStylesArrayBlockingQueue();
ExcelStyleConfig(complexHeadStyles, Lists.newArrayList(0, 1, 2, 3, 4, 5, 6, 7, 9, 10), null, Lists.newArrayList(22, 23), 1, WhrcanConstants.annotationsMap, WhrcanConstants.dropDownMap));
//导出数据
EasyExcelUtil.exportExcel2007Format(params);
} catch (IOException e) {
log.error("导出房源信息异常,错误信息:" + e.getMessage(), e);
}
return message;
}
导入相关代码
public Message<String> importExcelFwxxBatch(@RequestParam("file") MultipartFile file) {
Message<String> message = new Message<String>();
try {
UploadStandardDeviceListener listener = new UploadStandardDeviceListener(Lists.newArrayList());
log.info("获取导入时监听数据对象,开始解析数据 ： ");
//导入数据
EasyExcel.read(file.getInputStream(), DtoRoomExportExcel.class, listener).sheet().doRead();
List<DtoRoomExportExcel> detailList = listener.getDetailList();
if (enable) log.info("获取导入excel中数据:{}", JSON.toJSONString(detailList,true));
if (!detailList.isEmpty()) {
log.info("解析数据成功，开始导入数据 ： ");
service.updateRoomExportExcel(detailList);
}
} catch (IOException e) {
log.error("导入房源信息异常,错误信息:" + e.getMessage(), e);
}
message.setSuccess(true);
message.setMsg("导入成功！");
return message;
}
参考链接
导入导出详细过程：https://www.freesion.com/article/78771026459/
https://www.freesion.com/article/659875205/
增加下拉选择框：https://www.jianshu.com/p/ec386297d5ba
自定义表头（标题）样式策略：https://www.jianshu.com/p/5298f5090f52
