EasyExcel导出配置类

最新推荐文章于 2024-05-31 10:46:10 发布

码暇枫楠

最新推荐文章于 2024-05-31 10:46:10 发布

阅读量339

点赞数

分类专栏： EasyExcel 超级详细文章标签： java java spring

本文链接：https://blog.csdn.net/refreshed_God/article/details/121677847

版权

超级详细同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

EasyExcel

7 篇文章 0 订阅

订阅专栏

自适应列宽配置类

自动适应列宽配置类通用导出实现自适应列宽

package com.gaiaworks.cn.opm.biz.util.excel;

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * excel自适应列宽
 */
public class CustomCellWriteUtil extends AbstractColumnWidthStyleStrategy {
    private static final int MAX_COLUMN_WIDTH = 255;
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);

    public CustomCellWriteUtil() {
    }

    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = (Map) CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap(16);
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }
            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }
                Integer maxColumnWidth = (Integer) ((Map) maxColumnWidthMap).get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    ((Map) maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), 7250);
                }
            }
        }
    }

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

创建下拉框注解

下拉框创建注解配置类详解

package com.gaiaworks.cn.opm.biz.util.excel;

import java.lang.annotation.*;

/**
 * 注解:自定义标记导出excel的下拉数据集
 */
@Documented
// 作用在字段上
@Target(ElementType.FIELD)
// 运行时有效
@Retention(RetentionPolicy.RUNTIME)
public @interface DropDownSetField {
    // 固定下拉内容
    String[] source() default {};

    // 注解内的名称，解析时要注意对应
    String name() default "";
}

解析下拉框注解类

解析下拉框注解填充内容添加下拉map信息调用insertMap方法即可

package com.gaiaworks.cn.opm.biz.util.excel;

import java.util.Map;
import java.util.Optional;

/**
 * 处理导入excel下拉框注解的工具类
 */
public class ResolveDropAnnotationUtil {

    public static String[] resove(DropDownSetField dropDownSetField, String[] strings) {
        if (!Optional.ofNullable(dropDownSetField).isPresent()) {
            return null;
        }
 
        // 获取固定下拉信息
        String[] source = dropDownSetField.source();
        if (null != source && source.length > 0) {
            return source;
        }
 
        if (null != strings && strings.length > 0) {
            try {
                String[] dynamicSource = strings;
                if (null != dynamicSource && dynamicSource.length > 0) {
                    return dynamicSource;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }
    
 
    //插入到map中
    public static void insertMap(Map<Integer, String[]> map, String[] params, DropDownSetField dropDownSetField, int i) {
        String[] sources = ResolveDropAnnotationUtil.resove(dropDownSetField, params);
        if (null != sources && sources.length > 0) {
            map.put(i, sources);
        }
    }
}

导出样式设置

导出样式只需要调用静态方法getStyleStrategy() 调用即可

package com.gaiaworks.cn.opm.biz.util.excel;

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.gaiaworks.cn.opm.component.constants.CommonConstant;
import com.gaiaworks.cn.opm.component.constants.Symbol;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.web.multipart.MultipartFile;

import java.lang.reflect.Field;
import java.util.List;

@Slf4j
public class ExcelUtil {
    /**
     * 设置excel样式
     *
     * @return
     */
    public static HorizontalCellStyleStrategy getStyleStrategy() {
        // 头的策略  样式调整
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 头背景 浅绿
        headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        WriteFont headWriteFont = new WriteFont();
        // 头字号
        headWriteFont.setFontHeightInPoints((short) 14);
        // 字体样式
        headWriteFont.setFontName("宋体");
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 自动换行
        headWriteCellStyle.setWrapped(false);
        // 设置细边框
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        // 设置边框颜色 25灰度
        headWriteCellStyle.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headWriteCellStyle.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headWriteCellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headWriteCellStyle.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 水平对齐方式
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 内容的策略 宋体
        WriteCellStyle contentStyle = new WriteCellStyle();
        // 设置垂直居中
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置 水平居中
//        contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        WriteFont contentWriteFont = new WriteFont();
        // 内容字号
        contentWriteFont.setFontHeightInPoints((short) 12);
        // 字体样式
        contentWriteFont.setFontName("宋体");
        contentStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentStyle);
    }

    public static Boolean isEmpty(String className, Object excel){
        try {
            Class clazz = Class.forName(className);
            Field[] fields = clazz.getDeclaredFields();
            boolean isAccessible;
            for (Field field : fields) {
                isAccessible = field.isAccessible();
                // 允许通过反射访问该字段
                field.setAccessible(true);
                if (ObjectUtils.isEmpty(field.get(excel))) {
                    field.setAccessible(isAccessible);
                    continue;
                }
                return Boolean.FALSE;
            }
        } catch (ClassNotFoundException | IllegalAccessException e) {
            log.error("Excel.isEmpty参数: className [{}], excel [{}]", className, excel, e);
            return Boolean.FALSE;
        }
        return Boolean.TRUE;
    }

    public static Boolean checkExcel(String fileName){
        String substringFileName = fileName.substring(fileName.lastIndexOf(Symbol.POINT) + 1);
        List<String> names = CommonConstant.EXCELTYE;
        for (String name : names) {
            if (name.equals(substringFileName)) {
                return true;
            }
        }
        return false;
    }

}

下拉框超出解决限制方法

导出下拉框超出限制导出解决方法

package com.gaiaworks.cn.opm.biz.excel.module.targetSelf;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.Map;

public class TargetSelfImportHandler implements SheetWriteHandler {

	/**
	 * 下拉框内容map Integer数据所在列数，string[]下拉数据列表
	 */
	private Map<Integer, String[]> spinnerMap;
	/**
	 * 导出数据的list大小
	 */
	private int dataSize;

	public TargetSelfImportHandler(Map<Integer, String[]> spinnerMap, int dataSize) {
		this.spinnerMap = spinnerMap;
		this.dataSize = dataSize;
	}

	@Override
	public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

	}

	@Override
	public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
		//获取一个workbook
		Sheet sheet = writeSheetHolder.getSheet();
		//设置下拉框
		DataValidationHelper helper = sheet.getDataValidationHelper();
		//定义sheet的名称
		String hiddenName = "hidden";
		//1.创建一个隐藏的sheet 名称为 hidden
		Workbook workbook = writeWorkbookHolder.getWorkbook();
		Sheet hidden = workbook.createSheet(hiddenName);
		for (Map.Entry<Integer, String[]> entry : spinnerMap.entrySet()) {
			//下拉框的起始行,结束行,起始列,结束列
			CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());
			//获取excel列名
			String excelLine = getExcelLine(entry.getKey());

			//2.循环赋值
			String[] values = entry.getValue();
			for (int i = 0, length = values.length; i < length; i++) {
				// 3:表示你开始的行数  3表示 你开始的列数
				Row row = hidden.getRow(i);
				if (row == null) {
					row = hidden.createRow(i);
				}
				row.createCell(entry.getKey()).setCellValue(values[i]);
			}

			//4.  =hidden!$H:$1:$H$50  sheet为hidden的 H1列开始H50行数据获取下拉数组
			String refers = "="+hiddenName + "!$"+excelLine+
				"$1:$"+excelLine +"$"+ (values.length);
			//5 将刚才设置的sheet引用到你的下拉列表中
			DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
			DataValidation dataValidation = helper.createValidation(constraint, addressList);

			// 阻止输入非下拉选项的值
			dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
			dataValidation.setShowErrorBox(true);
			dataValidation.setSuppressDropDownArrow(true);
			dataValidation.createErrorBox("提示","此值与单元格定义格式不一致");
			// validation.createPromptBox("填写说明：","填写内容只能为下拉数据集中的单位，其他单位将会导致无法入仓");
			writeSheetHolder.getSheet().addValidationData(dataValidation);
		}

		// 设置指定列的格式为文本
		CellStyle cellStyle = workbook.createCellStyle();
		DataFormat dataFormat = workbook.createDataFormat();
		cellStyle.setDataFormat(dataFormat.getFormat("@"));
		for (int i = 0; i < dataSize; i ++){
			sheet.setDefaultColumnStyle(i,cellStyle);
		}

		//设置列为隐藏
		int hiddenIndex = workbook.getSheetIndex("hidden");
		if (!workbook.isSheetHidden(hiddenIndex)) {
			workbook.setSheetHidden(hiddenIndex, true);
		}
	}

	/**
	 * @Description 返回excel列标A-Z-AA-ZZ
	 * @Author chou
	 * @Date 2020/9/8
	 * @param num 列数
	 * @return java.lang.String
	 */
	public static String getExcelLine(int num) {
		String line = "";
		int first = num/26;
		int second = num % 26;
		if (first>0) {
			line = (char)('A'+first-1)+"";
		}
		line += (char)('A'+second)+"";
		return line;
	}
}