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

目录

 一、在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.？以上的版本

        
            com.alibaba
            easyexcel
            2.1.7
        

   
            org.apache.commons
            commons-collections4
            4.1
   

二、编写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 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 getExcelExportData() {
        ExcelVo excelVo = new ExcelVo();
        List 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 data = ExcelService.getExcelExportData();
        ExcelUtils.exportExcel(response,data);
    }
 
    //方式二：通过前端传值测试，前端传的excelVo 字段和ExcelVo类里面的字段对应
    @GetMapping("/exportTwo")
    public void aliExportDetail(@RequestBody List excelVo, HttpServletResponse response) throws Exception {
        ExcelUtils.exportExcel(response,excelVo);
    }
}

六、测试导出是否成功

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

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

如果不知道端口号，

配置一个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