Java EasyExcel 设置表头及内容字体大小

原创已于 2022-04-21 09:57:32 修改 · 1.6w 阅读

30 ·

CC 4.0 BY-SA版权

文章标签：

#java

于 2022-04-21 09:54:54 首次发布

Java 专栏收录该内容

26 篇文章

订阅专栏

该篇博客介绍了如何使用Java的EasyExcel库来导出Excel文件，并详细展示了如何设置内容和头部的样式，包括字体大小、对齐方式、边框等，以便于生成美观且易读的表格数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

controller

package com.gemantic.cnooc.controller;


import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
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.gemantic.cnooc.model.StudentExcelDto;
import com.google.common.collect.Lists;

import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;

/**
 * @Auther: Wangtianming
 * @Date: 2021/10/27 14:35
 * @Description:
 */
@RestController
@RequestMapping(path = "/student")
@Slf4j
public class StudentExcelController {

    @PostMapping(value = "exportExcel", headers = "Accept=application/octet-stream")
    @ApiOperation(value = "导出excel", notes = "导出excel")
    public void exportExcel(HttpServletResponse response) throws Exception {

        List<StudentExcelDto> pageList = Lists.newArrayList();
        StudentExcelDto a = new StudentExcelDto();
        a.setId(1l);
        a.setStudentName("小王");
        a.setStudentClass("五年纪");
        a.setAddress("北京小学");
        a.setAdmissionDate(new Date());
        a.setScore(new BigDecimal(100));
        a.setGender(1);
        pageList.add(a);
        pageList.forEach(b -> {
            if (a.getGender() == 1) {
                a.setGenderName("男");
            } else {
                a.setGenderName("女");
            }
        });

        //内容样式策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //垂直居中,水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);

        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //头策略使用默认 设置字体大小
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 导出Excel
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("班级信息", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        ExcelWriter writer = EasyExcel.write(response.getOutputStream())
                .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                .build();
        WriteSheet sheet = EasyExcel.writerSheet(0, "sheet").head(StudentExcelDto.class).build();
        writer.write(pageList, sheet);
        writer.finish();
    }

}

设置字体大小

 //内容样式策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //垂直居中,水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);

        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //头策略使用默认 设置字体大小
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(headWriteFont);

ExcelWriter writer = EasyExcel.write(response.getOutputStream())
                .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                .build();