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

2023-02-18 00:00:00 合并 导出 单元格

《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.?以上的版本

        <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

《Java中使用alibaba easyexcel导出Excel,合并单元格》

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

如果不知道端口号,

配置一个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
    原文作者:SUMMERENT
    原文地址: https://blog.csdn.net/SUMMERENT/article/details/126478625
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。

相关文章