EasyExcel导入导出样式、自适应列宽、自适应行高

m0_73900214

16157人浏览 · 2023-05-23 11:14:54

m0_73900214 · 2023-05-23 11:14:54 发布

一、先加依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>

二、话不多说，直接先看导出样式设置，可以根据自己实际情况调整：

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
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;


public class EasyExcelUtils {
    /**
     * 设置excel样式
     */
    public static HorizontalCellStyleStrategy getStyleStrategy() {
        // 头的策略  样式调整
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 头背景 浅绿
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        // 头字号
        headWriteFont.setFontHeightInPoints((short) 12);
        // 字体样式
        headWriteFont.setFontName("宋体");
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 自动换行
        headWriteCellStyle.setWrapped(true);
        // 设置细边框
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        // 设置边框颜色 25灰度
        headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        // 水平对齐方式
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 内容的策略 宋体
        WriteCellStyle contentStyle = new WriteCellStyle();
        // 设置垂直居中
        contentStyle.setWrapped(true);
        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);
    }
}

三、自适应列宽

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

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


public class CustomCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy {

    private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            // 单元格文本长度大于60换行
            if (columnWidth >= 0) {
                if (columnWidth > 60) {
                    columnWidth = 60;
                }
                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    Sheet sheet = writeSheetHolder.getSheet();
                    sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }
            }
        }
    }
    /**
     * 计算长度
     * @param cellDataList
     * @param cell
     * @param isHead
     * @return
     */
    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData<?> cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        // 换行符（数据需要提前解析好）
                        int index = cellData.getStringValue().indexOf("\n");
                        return index != -1 ?
                                cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

四、自适应行高

import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;

import java.util.Iterator;

public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
    /**
     * 默认高度
     */
    private static final Integer DEFAULT_HEIGHT = 300;

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        Iterator<Cell> cellIterator = row.cellIterator();
        if (!cellIterator.hasNext()) {
            return;
        }
        // 默认为 1行高度
        int maxHeight = 1;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getCellTypeEnum() == CellType.STRING) {
                String value = cell.getStringCellValue();
                int len = value.length();
                int num = 0;
                if (len > 50) {
                    num = len % 50 > 0 ? len / 50 : len / 2 - 1;
                }
                if (num > 0) {
                    for (int i = 0; i < num; i++) {
                        value = value.substring(0, (i + 1) * 50 + i) + "\n" + value.substring((i + 1) * 50 + i, len + i);
                    }
                }
                if (value.contains("\n")) {
                    int length = value.split("\n").length;
                    maxHeight = Math.max(maxHeight, length) + 1;
                }
            }
        }
        row.setHeight((short) ((maxHeight) * DEFAULT_HEIGHT));
    }
}

五、导出

先创建导出实体类

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.terton.aisp.ssp.entity.Feedback;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelIgnoreUnannotated // 只导出带有ExcelProperty注解的字段
public class ExcelFeedbackVO extends Feedback {

    /**
     * 站点ID
     */
    // 设置表头名称 ，索引最好带上，不会出问题。
    @ExcelProperty(value = "站点id", index = 0)
    private String siteId;
    /**
     * 搜索内容
     */
    @ExcelProperty(value = "搜索内容", index = 1)
    private String title;
    /**
     * 反馈内容
     */
    @ExcelProperty(value = "反馈内容", index = 2)
    private String content;
    /**
     * IP地址
     */
    @ExcelProperty(value = "搜索IP", index = 3)
    private String ip;

}

2、创建一个对外暴露的接口。

@PostMapping("/downloadBack")    
public void downloadBack(HttpServletResponse response, String siteId, Integer content) throws TException, IOException {
        List<Feedback> list = feedbackService.searchBack(siteId, content);
        setResponse(response, "列表");
        EasyExcel.write(response.getOutputStream(), ExcelFeedbackVO.class /* 引用需要导出的字段*/)
                .sheet("列表")
                .registerWriteHandler(new CustomCellWriteWidthConfig()) /*自适应列宽*/
                .registerWriteHandler(new CustomCellWriteHeighConfig()) /*自适应行高（根据自己情况选择使用，我这里没用到）*/
                .registerWriteHandler(EasyExcelUtils.getStyleStrategy()) /*引用样式*/
                .doWrite(list); /* 想到导出模板的话，此处给一个空的集合就好*/
    }

3、设置响应体信息（由于我这个项目导入导出比较多，把此方法变成公共方法）

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;

public class SetResponse {
    /**

     * 导出设置响应信息
     *
     * @param response 响应
     */
    public static void setResponse(HttpServletResponse response,String fileName) {
        // 文件名
        String sheetName = URLEncoder.encode(fileName, StandardCharsets.UTF_8) + ".xlsx";
        // contentType 响应内容的类型
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        // 设置字符
        response.setCharacterEncoding("utf-8");
        // 设置文件名
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + sheetName);
    }

}

4、访问接口

模板

正常导出

六、导入

1、监听器（关键）

注意事项：监听器里面不能使用注入的形式将service注入进来，所以可以使用构造器

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.terton.aisp.common.enums.ExcelNameModelEnum;
import com.terton.aisp.common.util.PingYinUtil;
import com.terton.aisp.word.entity.MassWord;
import com.terton.aisp.word.enums.UploadBatchCountEnum;
import com.terton.aisp.word.service.IMassWordService;
import com.terton.aisp.word.service.impl.MassWordServiceImpl;
import com.terton.aisp.word.vo.ExcelMassWordModelVO;
import com.terton.framework.util.StringUtil;
import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Slf4j
@AllArgsConstructor
@NoArgsConstructor
public class UploadMassWordListener extends AnalysisEventListener<ExcelMassWordModelVO> {
    private IMassWordService massWordService;
    private String siteId;
    private Integer wordLevel;
    private String userName;
    private List<ExcelMassWordModelVO> list = new ArrayList<>();
    private List<String> massWordList = new ArrayList<>();
    private List<String> massWords = new ArrayList<>();
    private Integer successNum = 0;
    private Integer errorNum = 0;

    /**
     * 表头校验 此方法里面可进行对导入的excel的格式进行判断，先执行
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
             // headMap 表头数据，拿出进行判断就好
    }

    /**
     * 每读一行触发一次 
     *
     * @param modelVO         ”
     * @param analysisContext “
     */
    @Override
    public void invoke(ExcelMassWordModelVO modelVO, AnalysisContext analysisContext) {
       log.info("读取到一条数据：{}",modelVO)
       // 可以对数据进行判断是否有效。 根据自己的实际业务逻辑判断，
       // 判断完成的数据可以用一个List来接收
       // 为了防止数据过多，数据一直存在内存中，这里可以设置一个阈值，当list中的数据超过这个值，就先把这批数据添加到数据库。
       if (StringUtil.notBlankAndNull(modelVO.getMassWord()) && StringUtil.notBlankAndNull(modelVO.getTargetWord())) {
            if (!CollectionUtils.isEmpty(massWords) && massWords.contains(modelVO.getMassWord())) {
                throw new RuntimeException(ExcelNameModelEnum.FailImport.RESULT_FIELD.getText() + modelVO.getMassWord());
            }
            massWords.add(modelVO.getMassWord());
            massWordList.add(modelVO.getMassWord());
            list.add(modelVO);
            try {
                if (list.size() >= UploadBatchCountEnum.MASS_WORD.getCount() /*我这里阈值设置的是500*/) {
                    save();
                }
            } catch (Exception e) {
                throw new RuntimeException(e.getMessage());
            }
        }
        
    }

    /**
     * 完毕触发
     *
     * @param analysisContext ”
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 写数据库
        if (!CollectionUtils.isEmpty(list)) {
            save();
        }
        massWords.clear();
        log.info("成功写入数量:{}", successNum);
        log.info("失败写入数量:{}", errorNum);
        successNum = 0;
        errorNum = 0;
    }

    private void save() {
        try {
            List<MassWord> recordList = massWordService.getByMasswordsAndSiteId(massWordList, siteId);
            if (!CollectionUtils.isEmpty(recordList)) {
                List<String> collect = recordList.stream().map(MassWord::getMassWord).collect(Collectors.toList());
                if (!CollectionUtils.isEmpty(collect)) {
                    throw new RuntimeException(ExcelNameModelEnum.FailImport.RESULT_FIELD.getText() + collect);
                }
            }
            for (ExcelMassWordModelVO wordModelVO : list) {
                MassWord newMassWord = getMassWord(wordModelVO);
                massWordService.save(newMassWord);
                successNum += 1;
            }
        } catch (Exception e) {
            errorNum += 1;
            throw new RuntimeException(e.getMessage());
        }
        massWordList.clear();
        list.clear();
    }

    private MassWord getMassWord(ExcelMassWordModelVO wordModelVO) {
        MassWord massWord = new MassWord();
        massWord.setMassWord(wordModelVO.getMassWord());
        massWord.setTargetWord(wordModelVO.getTargetWord());
        massWord.setWordLevel(wordLevel);
        massWord.setSiteId(siteId);
        massWord.setAcronym(PingYinUtil.getFirstSpell(wordModelVO.getMassWord()));
        massWord.setFullPinYin(PingYinUtil.getFullSpell(wordModelVO.getMassWord()));
        massWord.setCrTime(new Date());
        massWord.setCrUser(userName);
        return massWord;
    }

    public UploadMassWordListener(MassWordServiceImpl massWordService, String siteId,Integer wordLevel, String userName) {
        this.massWordService = massWordService;
        this.siteId = siteId;
        this.wordLevel = wordLevel;
        this.userName = userName;
    }

}

2、导入与监听器的使用。

    public void upload(MultipartFile file, String siteId, Integer wordLevel,String userName) throws IOException {
        UploadMassWordListener listener = new UploadMassWordListener(this, siteId, wordLevel, userName);
        EasyExcel.read(file.getInputStream(), ExcelMassWordModelVO.class, listener)
                .sheet(0)
                .headRowNumber(1)
                .doRead();
    }

到这里大概就结束了，希望对你有用。

GitCode 开源社区

旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。

更多推荐

[转载]在Windows环境下安装GNU Radio

转自：在Windows环境下安装GNURadio_恐弱智_新浪博客GNU Radio是用Python开发的，大部分开源的工程能够在Linux环境下运行良好，而Windows下却运行的很勉强，而且安装配置都很复杂。GNU Radio算是个例外了，不光提供了Windows的二进制安装，还有比较详细的说明。我是Python小白，所以折腾了好久才弄好，特意记录下来，免得以后再装还折腾。GNU Radio的

GitCode 开源社区

centOS 8 使用dnf安装Docker

DNF是什么？CentOS 8使用YUM软件包管理器版本v4.0.4。现在，该版本使用DNF(已删除YUM)。DNF是软件包管理器。它会在Linux发行版上安装，执行更新并删除软件包。使用DNF安装Docker跳过具有损坏依赖性的程序包一个有效的解决方案是使您的CentOS 8系统使用以下--nobest命令安装最符合条件的版本：sudo dnf install docker...

GitCode 开源社区

定时同步数据库表(mysql+linux+crontab)

sync.sh里面的参数需要改变，ip/username/password/database/tablesync.sh#!/bin/sh# Please change the IP and password of the data source db.# Then change the table name.filename=/home/nington/db/$(date +%Y-%m