Java中使用alibaba easyexcel导出Excel，合并单元格

系统管理员 2024-04-08 11:08 9阅读 0赞

![f73fbeeceb0744f492b1e26810f42780.png][]

**目录**

一、在pom.xml中引入 alibaba easyexcel maven 版本为2.1.7

注意：！！！本案例不可用2.2.？以上的版本

二、编写ExcelVo

三、编写ExcelUtils

四、编写ExcelService 准备数据，可以写在Controller层

五、编写Controller层，调用接口

六、测试导出是否成功

--------------------

### 一、在pom.xml中引入 alibaba easyexcel maven 版本为2.1.7 ###

#### 注意：！！！本案例不可用2.2.？以上的版本 ####

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

<dependency>
                <groupId>org.apache.commons</groupId>
                <artifactId>commons-collections4</artifactId>
                <version>4.1</version>
       </dependency>

### 二、编写ExcelVo ###

@Data
    @ColumnWidth(value = 20)   //列宽度 注解可以写在类上方，也可以写在字段上方
    @ContentRowHeight(value = 30)  //列高度
    @HeadRowHeight(value = 35)  //表头高度
    public class ExcelVo {
        @ExcelProperty(value = "表头单元格一", index = 0)
        private String headOne;
    
        @ExcelProperty(value = {"表头单元格二", "Come"}, index = 1)
        private String headTwoCome;
        @ExcelProperty(value = {"表头单元格二", "On"}, index = 2)
        private String headTwoOn;
    
        @ExcelProperty(value = {"表头单元格三", "Come"}, index = 3)
        private String headThreeCome;
        @ExcelProperty(value = {"表头单元格三", "On"}, index = 4)
        private String headThreeOn;
    
        @ExcelProperty(value = {"表头单元格四", "Come"}, index = 5)
        private String headFourCome;
        @ExcelProperty(value = {"表头单元格四", "On"}, index = 6)
        private String headFourOn;
    
        @ExcelProperty(value = {"表头单元格五", "Come"}, index = 7)
        private String headFiveCome;
        @ExcelProperty(value = {"表头单元格五", "On"}, index = 8)
        private String headFiveOn;
    
        @ExcelProperty(value = {"表头单元格六", "Come"}, index = 9)
        private String headSixCome;
        @ExcelProperty(value = {"表头单元格六", "On"}, index = 10)
        private String headSixOn;
    
        @ColumnWidth(value = 30) 
        @ExcelProperty(value = "表头单元格七", index = 11)
        private String headSeven;
    
    }

### 三、编写ExcelUtils ###

import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.write.builder.ExcelWriterBuilder;
    import com.alibaba.excel.write.merge.OnceAbsoluteMergeStrategy;
    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.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
    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 springboot.redis.demo.model.RetailTargetExcelVo;
    
    import javax.servlet.http.HttpServletResponse;
    import java.net.URLEncoder;
    import java.util.List;
    
    
    public class ExcelUtils {
        public static void exportExcel(HttpServletResponse response, List<ExcelVo> data)throws Exception{
            //1、设置数据表格的样式
            //  ---------- 头部样式 ----------
            WriteCellStyle headStyle = new WriteCellStyle();
            // 字体样式
            WriteFont headFont = new WriteFont();
            headFont.setFontHeightInPoints((short) 11);
            headFont.setFontName("宋体");
            headFont.setColor(IndexedColors.BLACK.index);
            headStyle.setWriteFont(headFont);
    
            WriteCellStyle contentStyle = new WriteCellStyle();
            //垂直居中
            contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //水平居中
            contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            // 设置边框
            // bodyStyle.setBorderTop(BorderStyle.DOUBLE);
            contentStyle.setBorderLeft(BorderStyle.THIN);
            contentStyle.setBorderRight(BorderStyle.THIN);
            contentStyle.setBorderBottom(BorderStyle.THIN);
            WriteFont writeFont = new WriteFont();
            //加粗
            //字体大小为11
            writeFont.setFontHeightInPoints((short) 11);
            writeFont.setFontName("宋体");
            writeFont.setColor(IndexedColors.BLACK.index);
            contentStyle.setWriteFont(writeFont);
    
            // 创建单元格策略1 参数1为头样式【不需要头部，设置为null】，参数2位表格内容样式
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headStyle, contentStyle);
    
            // 创建策略2
    //        HorizontalCellStyleStrategy dataTableStrategy = new HorizontalCellStyleStrategy(headStyle,bodyStyle);
    
            // 设置数据表格的行高   null表示使用原来的行高
            // SimpleRowHeightStyleStrategy rowHeightStrategy3 = new SimpleRowHeightStyleStrategy( null, (short) 18);
    
            //循环合并策略
    //     LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);
    
           //一次绝对合并策略
           OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy1 = new OnceAbsoluteMergeStrategy(0, 1, 1, 2); //0，1表示第1行到第2行 1,2表示第2列到第3列 
                OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy2 = new OnceAbsoluteMergeStrategy(0, 1, 3, 4);
                OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy3 = new OnceAbsoluteMergeStrategy(0, 1, 5, 6);
                OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy4 = new OnceAbsoluteMergeStrategy(0, 1, 7, 8);
                OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy5 = new OnceAbsoluteMergeStrategy(0, 1, 9, 10);
    
    //      response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("文件名", "UTF-8");
            response.setHeader("content-type", "application/octet-stream");
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream(), ExcelVo.class)
    //              .registerWriteHandler(loopMergeStrategy)  // 循环合并策略
                    .registerWriteHandler(onceAbsoluteMergeStrategy1)
                    .registerWriteHandler(onceAbsoluteMergeStrategy2)
                    .registerWriteHandler(onceAbsoluteMergeStrategy3)
                    .registerWriteHandler(onceAbsoluteMergeStrategy4)
                    .registerWriteHandler(onceAbsoluteMergeStrategy5)
    //                .registerWriteHandler(dataTableStrategy)    //策略2
                    .registerWriteHandler(horizontalCellStyleStrategy);  //策略1
    
            excelWriterBuilder.sheet("sheet名称").doWrite(data);
        }
    }

### 四、编写ExcelService 准备数据，可以写在Controller层 ###

public class ExcelService {
        public static List<ExcelVo> getExcelExportData() {
            ExcelVo excelVo = new ExcelVo();
            List<ExcelVo> list = new ArrayList<>();
    
            for (int i = 0; i < 3; i++) {
                excelVo.setHeadOne("qqqq");
                excelVo.setHeadTwoCome("wwww");
                excelVo.setHeadTwoOn("eee");
                excelVo.setHeadThreeCome("rrr");
                excelVo.setHeadThreeOn("bgbd");
                excelVo.setHeadFourCome("qoqo");
                excelVo.setHeadFourOn("规格");
                excelVo.setHeadFiveCome("项链");
                excelVo.setHeadFiveOn("等等");
                excelVo.setHeadSixCome("测试");
                excelVo.setHeadSixOn("测试");
                excelVo.setHeadSeven("测试");
                list.add(excelVo);
            }
    
            return  list;
        }
    }

### 五、编写Controller层，调用接口 ###

@RestController
    public class ExcelController {
        
        //方式一：准备测试数据 ExcelService.getExcelExportData()
        @GetMapping("/export")
        public void export(HttpServletResponse response) throws Exception {
            List<ExcelVo> data = ExcelService.getExcelExportData();
            ExcelUtils.exportExcel(response,data);
        }
    
        //方式二：通过前端传值测试，前端传的excelVo 字段和ExcelVo类里面的字段对应
        @GetMapping("/exportTwo")
        public void aliExportDetail(@RequestBody List<ExcelVo> excelVo, HttpServletResponse response) throws Exception {
            ExcelUtils.exportExcel(response,excelVo);
        }
    }

### 六、测试导出是否成功 ###

在浏览器地址栏中访问：[http://localhost:端口号/export][http_localhost_export]

![225633a7f1bb498db781ba0a35d7df27.png][]

这样就可以弹出来要保存文件的地址，点击保存，就保存成功了

如果不知道端口号，

配置一个application.yml文件 在文件中简单配置server port 就可以，如果有用到redis和数据库，就要配置redis和数据库

# 端口号配置
    server:
      port: 8081
    
    # spring相关 配置
    spring:
      redis:  # 配置redis
        database: 0
        host: localhost
        port: 6379
      datasource:# 配置数据库
        name: 
        url: jdbc:mysql://ip地址:3306/test?useSSL=false
        username: 
        password: 
        driver-class-name: com.mysql.jdbc.Driver #mysql 驱动程序
      profiles:
        active: def,dev,master 
    
    # 配置mybatis
    mybatis:
      mapper-locations: classpath:*mapper/*.xml 
      type-aliases-package: com.oda.mall.entity

[f73fbeeceb0744f492b1e26810f42780.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/a3aaac838500447f89fc806c30a32d57.png
[http_localhost_export]: http://localhost:8081/export
[225633a7f1bb498db781ba0a35d7df27.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/b0fb7377e9ce4dae9f6c16a6e2c93f49.png