EasyExcel实现excel导出【设置自定义样式--案例分析】

最新推荐文章于 2024-04-23 18:52:22 发布

java_life_style

最新推荐文章于 2024-04-23 18:52:22 发布

阅读量6.5k

点赞数 1

分类专栏： excel【JAVA后端】文章标签： java 后端 excel spring boot

本文链接：https://blog.csdn.net/weixin_41221717/article/details/122250243

版权

excel【JAVA后端】专栏收录该内容

3 篇文章 2 订阅

订阅专栏

需求描述

实现导出所有用户的信息数据到excel表格，并将用户按照ID进行分组，5个为1组，将对应用户ID能够被5整除的excel对应行，将excel表格的背景标黄，凸显出分组的情况

需求分析

实现导出需求的step：
   1、获取需要导出的用户列表数据
   2、使用现有开源的组件实现将用户列表数据导出到excel表格中（选择EasyExcel）
   3、设置导出的excel数据的样式

show code

引入EasyExcel的maven依赖：

        <!-- 阿里开源EXCEL -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.2</version>
        </dependency>

用户的数据类设计：


import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.metadata.BaseRowModel;
import com.fasterxml.jackson.annotation.JsonFormat;

import java.util.Date;

public class UserExcelPO extends BaseRowModel {

    @ColumnWidth(20)
    @ExcelProperty(value = "用户ID", index = 0)
    private Long userId;

    @ColumnWidth(40)
    @ExcelProperty(value = "用户名称", index = 1)
    private String username;

    @ColumnWidth(10)
    @ExcelProperty(value = "年龄", index = 2)
    private Integer age;

    @ColumnWidth(80)
    @ExcelProperty(value = "地址", index = 3)
    private String address;

    @JsonFormat(pattern = "yyyy-MM-dd")
    @ColumnWidth(15)
    @ExcelProperty(value = "注册日期（天）", index = 4)
    private Date registerDate;

	// 省略getter、setter
}

excel导出的样式处理类设计：


import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

public class EasyExcelCellWriteHandler implements CellWriteHandler {

    public EasyExcelCellWriteHandler() {
    }

    /**
     * Excel sheet最大行数，默认65536
     */
    public static final int sheetSize = 65536;
    private static final Logger LOGGER = LoggerFactory.getLogger(EasyExcelCellWriteHandler.class);

    /**
     * 相同的行，设置同一样式
     */
    private Integer rowIndex = null;

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        //LOGGER.info("第{}行，第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex());
        Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
        Map<String, CellStyle> styles = this.createStyles(workbook);
        if (isHead) {
            CellStyle cellStyle = styles.get("header");
            cell.setCellStyle(cellStyle);
        } else if (cell.getColumnIndex() == 0 && cell.getNumericCellValue() % 5 == 0) {
            CellStyle cellStyle = styles.get("index");
            cell.setCellStyle(cellStyle);
            rowIndex = cell.getRowIndex();
        } else if(Objects.nonNull(rowIndex) && rowIndex.intValue() == cell.getRowIndex()) {
            // 相同的行，设置同一样式
            CellStyle cellStyle = styles.get("index");
            cell.setCellStyle(cellStyle);
            rowIndex = cell.getRowIndex();
        } else {
            CellStyle cellStyle = styles.get("data");
            cell.setCellStyle(cellStyle);
        }


    }

    /**
     * 创建表格样式
     *
     * @param wb 工作薄对象
     * @return 样式列表
     */
    private Map<String, CellStyle> createStyles(Workbook wb) {
        // 写入各条记录,每条记录对应excel表中的一行
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle 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);

        // index索引的#F6E3CE背景色
        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.TAN.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        styles.put("index", style);

        return styles;
    }
}

用户列表数据导出接口设计：


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.opencv.domain.po.UserExcelPO;
import com.opencv.utils.EasyExcelCellWriteHandler;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;


@RestController
@RequestMapping("/export")
@Api(tags = "导出excel数据测试")
public class ExportExcelController {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExportExcelController.class);

    private static List<UserExcelPO> list = new ArrayList<>();
    private static String[] road = "重庆大厦,黑龙江路,十梅庵街,遵义路,湘潭街,瑞金广场,仙山街,仙山东路,仙山西大厦,白沙河路,赵红广场,机场路,民航街,长城南路,流亭立交桥,虹桥广场,长城大厦,礼阳路,风岗街,中川路,白塔广场,兴阳路,文阳街,绣城路,河城大厦,锦城广场,崇阳街,华城路,康城街,正阳路,和阳广场,中城路,江城大厦,顺城路,安城街,山城广场,春城街,国城路,泰城街,德阳路,明阳大厦,春阳路,艳阳街,秋阳路,硕阳街,青威高速,瑞阳街,丰海路,双元大厦,惜福镇街道,夏庄街道,古庙工业园,中山街,太平路,广西街,潍县广场,博山大厦,湖南路,济宁街,芝罘路,易州广场,荷泽四路,荷泽二街,荷泽一路,荷泽三大厦,观海二广场,广西支街,观海一路,济宁支街,莒县路,平度广场,明水路,蒙阴大厦,青岛路,湖北街,江宁广场,郯城街,天津路,保定街,安徽路,河北大厦,黄岛路,北京街,莘县路,济南街,宁阳广场,日照街,德县路,新泰大厦,荷泽路,山西广场,沂水路,肥城街,兰山路,四方街,平原广场,泗水大厦,浙江路,曲阜街,寿康路,河南广场,泰安路,大沽街,红山峡支路,西陵峡一大厦,台西纬一广场,台西纬四街,台西纬二路,西陵峡二街,西陵峡三路,台西纬三广场,台西纬五路,明月峡大厦,青铜峡路,台西二街,观音峡广场,瞿塘峡街,团岛二路,团岛一街,台西三路,台西一大厦,郓城南路,团岛三街,刘家峡路,西藏二街,西藏一广场,台西四街,三门峡路,城武支大厦,红山峡路,郓城北广场,龙羊峡路,西陵峡街,台西五路,团岛四街,石村广场,巫峡大厦,四川路,寿张街,嘉祥路,南村广场,范县路,西康街,云南路,巨野大厦,西江广场,鱼台街,单县路,定陶街,滕县路,钜野广场,观城路,汶上大厦,朝城路,滋阳街,邹县广场,濮县街,磁山路,汶水街,西藏路,城武大厦,团岛路,南阳街,广州路,东平街,枣庄广场,贵州街,费县路,南海大厦,登州路,文登广场,信号山支路,延安一街,信号山路,兴安支街,福山支广场,红岛支大厦,莱芜二路,吴县一街,金口三路,金口一广场,伏龙山路,鱼山支街,观象二路,吴县二大厦,莱芜一广场,金口二街,海阳路,龙口街,恒山路,鱼山广场,掖县路,福山大厦,红岛路,常州街,大学广场,龙华街,齐河路,莱阳街,黄县路,张店大厦,祚山路,苏州街,华山路,伏龙街,江苏广场,龙江街,王村路,琴屿大厦,齐东路,京山广场,龙山路,牟平街,延安三路,延吉街,南京广场,东海东大厦,银川西路,海口街,山东路,绍兴广场,芝泉路,东海中街,宁夏路,香港西大厦,隆德广场,扬州街,郧阳路,太平角一街,宁国二支路,太平角二广场,天台东一路,太平角三大厦,漳州路一路,漳州街二街,宁国一支广场,太平角六街,太平角四路,天台东二街,太平角五路,宁国三大厦,澳门三路,江西支街,澳门二路,宁国四街,大尧一广场,咸阳支街,洪泽湖路,吴兴二大厦,澄海三路,天台一广场,新湛二路,三明北街,新湛支路,湛山五街,泰州三广场,湛山四大厦,闽江三路,澳门四街,南海支路,吴兴三广场,三明南路,湛山二街,二轻新村镇,江南大厦,吴兴一广场,珠海二街,嘉峪关路,高邮湖街,湛山三路,澳门六广场,泰州二路,东海一大厦,天台二路,微山湖街,洞庭湖广场,珠海支街,福州南路,澄海二街,泰州四路,香港中大厦,澳门五路,新湛三街,澳门一路,正阳关街,宁武关广场,闽江四街,新湛一路,宁国一大厦,王家麦岛,澳门七广场,泰州一路,泰州六街,大尧二路,青大一街,闽江二广场,闽江一大厦,屏东支路,湛山一街,东海西路,徐家麦岛函谷关广场,大尧三路,晓望支街,秀湛二路,逍遥三大厦,澳门九广场,泰州五街,澄海一路,澳门八街,福州北路,珠海一广场,宁国二路,临淮关大厦,燕儿岛路,紫荆关街,武胜关广场,逍遥一街,秀湛四路,居庸关街,山海关路,鄱阳湖大厦,新湛路,漳州街,仙游路,花莲街,乐清广场,巢湖街,台南路,吴兴大厦,新田路,福清广场,澄海路,莆田街,海游路,镇江街,石岛广场,宜兴大厦,三明路,仰口街,沛县路,漳浦广场,大麦岛,台湾街,天台路,金湖大厦,高雄广场,海江街,岳阳路,善化街,荣成路,澳门广场,武昌路,闽江大厦,台北路,龙岩街,咸阳广场,宁德街,龙泉路,丽水街,海川路,彰化大厦,金田路,泰州街,太湖路,江西街,泰兴广场,青大街,金门路,南通大厦,旌德路,汇泉广场,宁国路,泉州街,如东路,奉化街,鹊山广场,莲岛大厦,华严路,嘉义街,古田路,南平广场,秀湛路,长汀街,湛山路,徐州大厦,丰县广场,汕头街,新竹路,黄海街,安庆路,基隆广场,韶关路,云霄大厦,新安路,仙居街,屏东广场,晓望街,海门路,珠海街,上杭路,永嘉大厦,漳平路,盐城街,新浦路,新昌街,高田广场,市场三街,金乡东路,市场二大厦,上海支路,李村支广场,惠民南路,市场纬街,长安南路,陵县支街,冠县支广场,小港一大厦,市场一路,小港二街,清平路,广东广场,新疆路,博平街,港通路,小港沿,福建广场,高唐街,茌平路,港青街,高密路,阳谷广场,平阴路,夏津大厦,邱县路,渤海街,恩县广场,旅顺街,堂邑路,李村街,即墨路,港华大厦,港环路,馆陶街,普集路,朝阳街,甘肃广场,港夏街,港联路,陵县大厦,上海路,宝山广场,武定路,长清街,长安路,惠民街,武城广场,聊城大厦,海泊路,沧口街,宁波路,胶州广场,莱州路,招远街,冠县路,六码头,金乡广场,禹城街,临清路,东阿街,吴淞路,大港沿,辽宁路,棣纬二大厦,大港纬一路,贮水山支街,无棣纬一广场,大港纬三街,大港纬五路,大港纬四街,大港纬二路,无棣二大厦,吉林支路,大港四街,普集支路,无棣三街,黄台支广场,大港三街,无棣一路,贮水山大厦,泰山支路,大港一广场,无棣四路,大连支街,大港二路,锦州支街,德平广场,高苑大厦,长山路,乐陵街,临邑路,嫩江广场,合江路,大连街,博兴路,蒲台大厦,黄台广场,城阳街,临淄路,安邱街,临朐路,青城广场,商河路,热河大厦,济阳路,承德街,淄川广场,辽北街,阳信路,益都街,松江路,流亭大厦,吉林路,恒台街,包头路,无棣街,铁山广场,锦州街,桓台路,兴安大厦,邹平路,胶东广场,章丘路,丹东街,华阳路,青海街,泰山广场,周村大厦,四平路,台东西七街,台东东二路,台东东七广场,台东西二路,东五街,云门二路,芙蓉山村,延安二广场,云门一街,台东四路,台东一街,台东二路,杭州支广场,内蒙古路,台东七大厦,台东六路,广饶支街,台东八广场,台东三街,四平支路,郭口东街,青海支路,沈阳支大厦,菜市二路,菜市一街,北仲三路,瑞云街,滨县广场,庆祥街,万寿路,大成大厦,芙蓉路,历城广场,大名路,昌平街,平定路,长兴街,浦口广场,诸城大厦,和兴路,德盛街,宁海路,威海广场,东山路,清和街,姜沟路,雒口大厦,松山广场,长春街,昆明路,顺兴街,利津路,阳明广场,人和路,郭口大厦,营口路,昌邑街,孟庄广场,丰盛街,埕口路,丹阳街,汉口路,洮南大厦,桑梓路,沾化街,山口路,沈阳街,南口广场,振兴街,通化路,福寺大厦,峄县路,寿光广场,曹县路,昌乐街,道口路,南九水街,台湛广场,东光大厦,驼峰路,太平山,标山路,云溪广场,太清路".split(",");

    static {
        UserExcelPO userExcelPO = null;
        for (int i = 1; i < 2000; i++) {
            userExcelPO = new UserExcelPO();
            userExcelPO.setUsername(getName());
            userExcelPO.setUserId((long) (i + 1000));
            userExcelPO.setAge(18);
            userExcelPO.setRegisterDate(new Date());
            userExcelPO.setAddress(getRoad());
            list.add(userExcelPO);
        }
    }

    /**
     * 返回地址
     *
     * @return
     */
    public static String getRoad() {
        int index = getNum(0, road.length - 1);
        String first = road[index];
        String second = String.valueOf(getNum(11, 150)) + "号";
        String third = "-" + getNum(1, 20) + "-" + getNum(1, 10);
        return first + second + third;
    }

    public static int getNum(int start, int end) {
        return (int) (Math.random() * (end - start + 1) + start);
    }

    public static String getName() {
        Random random = new Random();
        String[] Surname = {"赵", "钱", "孙", "李", "周", "吴", "郑", "王", "冯", "陈", "褚", "卫", "蒋", "沈", "韩", "杨", "朱", "秦", "尤", "许",
                "何", "吕", "施", "张", "孔", "曹", "严", "华", "金", "魏", "陶", "姜", "戚", "谢", "邹", "喻", "柏", "水", "窦", "章", "云", "苏", "潘", "葛", "奚", "范", "彭", "郎",
                "鲁", "韦", "昌", "马", "苗", "凤", "花", "方", "俞", "任", "袁", "柳", "酆", "鲍", "史", "唐", "费", "廉", "岑", "薛", "雷", "贺", "倪", "汤", "滕", "殷",
                "罗", "毕", "郝", "邬", "安", "常", "乐", "于", "时", "傅", "皮", "卞", "齐", "康", "伍", "余", "元", "卜", "顾", "孟", "平", "黄", "和",
                "穆", "萧", "尹", "姚", "邵", "湛", "汪", "祁", "毛", "禹", "狄", "米", "贝", "明", "臧", "计", "伏", "成", "戴", "谈", "宋", "茅", "庞", "熊", "纪", "舒",
                "屈", "项", "祝", "董", "梁", "杜", "阮", "蓝", "闵", "席", "季"};
        String girl = "秀娟英华慧巧美娜静淑惠珠翠雅芝玉萍红娥玲芬芳燕彩春菊兰凤洁梅琳素云莲真环雪荣爱妹霞香月莺媛艳瑞凡佳嘉琼勤珍贞莉桂娣叶璧璐娅琦晶妍茜秋珊莎锦黛青倩婷姣婉娴瑾颖露瑶怡婵雁蓓纨仪荷丹蓉眉君琴蕊薇菁梦岚苑婕馨瑗琰韵融园艺咏卿聪澜纯毓悦昭冰爽琬茗羽希宁欣飘育滢馥筠柔竹霭凝晓欢霄枫芸菲寒伊亚宜可姬舒影荔枝思丽 ";
        String boy = "伟刚勇毅俊峰强军平保东文辉力明永健世广志义兴良海山仁波宁贵福生龙元全国胜学祥才发武新利清飞彬富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪心邦承乐绍功松善厚庆磊民友裕河哲江超浩亮政谦亨奇固之轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德行时泰盛雄琛钧冠策腾楠榕风航弘";
        int index = random.nextInt(Surname.length - 1);
        // 获得一个随机的姓氏
        String name = Surname[index];
        // 可以根据这个数设置产生的男女比例
        int i = random.nextInt(3);
        if (i == 2) {
            int j = random.nextInt(girl.length() - 2);
            if (j % 2 == 0) {
                name = "女-" + name + girl.substring(j, j + 2);
            } else {
                name = "女-" + name + girl.substring(j, j + 1);
            }

        } else {
            int j = random.nextInt(girl.length() - 2);
            if (j % 2 == 0) {
                name = "男-" + name + boy.substring(j, j + 2);
            } else {
                name = "男-" + name + boy.substring(j, j + 1);
            }

        }

        return name;
    }

    @GetMapping("/data/excel")
    @ApiOperation(value = "导出用户数据")
    public void exportExcelData(HttpServletResponse response) {
        try {
            String fileName = URLEncoder.encode("用户数据导出.xlsx", "UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName);
            response.setContentType("application/vnd.ms-excel");
            exportExcel(response, fileName);
            response.getOutputStream().flush();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    @GetMapping("/data/excel/next")
    @ApiOperation(value = "导出用户数据")
    public void exportExcelDataNext(HttpServletResponse response) {
        try {
            String fileName = URLEncoder.encode("用户数据导出.xlsx", "UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName);
            response.setContentType("application/vnd.ms-excel");
            exportExcelNext(response, fileName);
            response.getOutputStream().flush();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private void exportExcel(HttpServletResponse response, String fileName) throws IOException {
        // ExcelWriter writer = EasyExcel.write(response.getOutputStream()).excelType(ExcelTypeEnum.XLSX).needHead(true).build();
        ExcelWriter writer = new ExcelWriter(response.getOutputStream(), ExcelTypeEnum.XLSX, true);


        /** 设置SHEET
         WriteSheet sheet = EasyExcel.writerSheet(0, "用户列表数据导出")
         .sheetNo(1)
         .head(UserExcelPO.class)
         .registerWriteHandler(new EasyExcelCellWriteHandler())
         .build();
         writer.write(list, sheet); **/

        //实例化表单
        com.alibaba.excel.metadata.Sheet sheet = new com.alibaba.excel.metadata.Sheet(1, 0, UserExcelPO.class);
        sheet.setSheetName("用户列表数据导出");
        //输出
        writer.write(list, sheet);

        // 关闭流
        writer.finish();
    }


    private void exportExcelNext(HttpServletResponse response, String fileName) throws IOException {
        //ExcelWriter writer = new ExcelWriter(response.getOutputStream(), ExcelTypeEnum.XLSX, true);
        ExcelWriter writer = EasyExcel.write(response.getOutputStream()).excelType(ExcelTypeEnum.XLSX).needHead(true).build();

        // 设置SHEET
        WriteSheet sheet = EasyExcel.writerSheet(1, "用户列表数据导出")
                .sheetNo(1)
                .head(UserExcelPO.class)
                .registerWriteHandler(new EasyExcelCellWriteHandler())
                .build();
        writer.write(list, sheet);

        // 关闭流
        writer.finish();
    }
}

业务代码实现分析

excel导出的实体类设计分析：

1、继承BaseRowModel是为了兼容老版本的excel导出实现，若使用的是：/export/data/excel/next接口实现的excel数据导出，可以不用继承

2、设置相关的EasyExcel导出的相关注解标识

设置对应excel列的宽度：@ColumnWidth(excelColumnLength)

设置对应excel列的header标题名称、设置excel列的index:

@ExcelProperty(value = "excleColumnName", index = indexNo)

excel导出样式设计类分析：

1、使用EasyExcel导出excel数据，若要对excel的样式进行自定义设置，需要自定义拦截器：EasyExcelCellWriteHandler 实现 CellWriteHandler接口，重写afterCellDispose方法

2、createStyles方法设置excel的表格样式，按照自定义设置去实现excel表格对应的列样式

3、使用表格的列下标和列值进行表格样式设置（即用户id能被5整除的列）：

cell.getColumnIndex() == 0 && cell.getNumericCellValue() % 5 == 0

4、使用全局变量控制相同的用户ID的行设置同一样式（需要在用户ID能被5整除的列中设置rowIndex）：

private Integer rowIndex = null; // 相同的行，设置同一样式

Objects.nonNull(rowIndex) && rowIndex.intValue() == cell.getRowIndex()

excel导出接口类分析：

1、模拟生成待导出的用户列表数据：

private static List<UserExcelPO> list = new ArrayList<>();

2、设置导出的excel文件响应格式：

response.setHeader("Content-Disposition", "attachment;fileName=" + fileName); response.setContentType("application/vnd.ms-excel");

3、导出excel的2种方式对比：

方式①：

ExcelWriter writer = new ExcelWriter(response.getOutputStream(), ExcelTypeEnum.XLSX, true);
com.alibaba.excel.metadata.Sheet sheet = new com.alibaba.excel.metadata.Sheet(1, 0, UserExcelPO.class);
        sheet.setSheetName("用户列表数据导出");
        //输出
        writer.write(list, sheet);

        // 关闭流
        writer.finish();

方式②：

 ExcelWriter writer = EasyExcel.write(response.getOutputStream()).excelType(ExcelTypeEnum.XLSX).needHead(true).build();
        // 设置SHEET
        WriteSheet sheet = EasyExcel.writerSheet(1, "用户列表数据导出")
                .sheetNo(1)
                .head(UserExcelPO.class)
                .registerWriteHandler(new EasyExcelCellWriteHandler())
                .build();
        writer.write(list, sheet);

        // 关闭流
        writer.finish();

方式①中的new ExcelWriter()方法已标注过期， new com.alibaba.excel.metadata.Sheet()方法也标注过期；

推荐使用方式②中的方法实现excel导出

注意点：

（1）设置导出的excelType要选择ExcelTypeEnum.XLSX，否则会报错，报不支持设置过多xxxx的excel样式

（2）使用writer关闭流就可以了，若回收response中的outputStream，则会偶现抛出输出流异常，到时excel文件导出是正确的数据，怀疑是writer的关闭流中引用了outputStream对象，若回收该对象，会触发IO异常（偶发）

（3）EasyExcel.write()方法可以传文件生成的路径进去，生成excel文件后，再读到response的文件输出流中

测试结果

swagger接口测试：

① /export/data/excel

② /export/data/excel/next

浏览器接口测试：

① http://localhost:8080/export/data/excel

② http://localhost:8080/export/data/excel/next

总结

EasyExcel相比poi，封装程度更多，只需要简单的API调用就能完成excel的相关操作，使用相关的操作需要更加深入去查阅对应的API文档

java_life_style

关注

1
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
EasyExcel实现excel导出【设置自定义样式--案例分析】

需求描述实现导出所有用户的信息数据到excel表格，并将用户按照ID进行分组，5个为1组，将对应用户ID能够被5整除的excel对应行，将excel表格的背景标黄，凸显出分组的情况需求分析实现导出需求的step： 1、获取需要导出的用户列表数据 2、使用现有开源的组件实现将用户列表数据导出到excel表格中（选择EasyExcel） 3、设置导出的excel数据的样式 show code引入EasyExcel的maven依赖： ...
复制链接

扫一扫