springboot项目实现导出excel动态设置表头

原创已于 2024-09-13 17:46:06 修改 · 748 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#spring boot #excel #后端

于 2024-09-13 17:43:28 首次发布

java 专栏收录该内容

12 篇文章

订阅专栏

在这里插入图片描述

pom.xml

<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.9</version>
		</dependency>

ExcelCustomExportUtil.java

package com.jiuzhu.server.common.utils;

import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static org.apache.poi.ss.usermodel.CellStyle.ALIGN_CENTER;
import static org.apache.poi.ss.usermodel.CellStyle.VERTICAL_CENTER;

public class ExcelCustomExportUtil {
    public static void exportExcel(HttpServletResponse response, List<String> headers, List<List<Object>> data) throws IOException {
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建工作表
        Sheet sheet = workbook.createSheet("sheet1");
        Map<String, CellStyle> styles = createStyles(workbook);

        CellStyle style = sheet.getWorkbook().createCellStyle();
        // 设置字体为黑体
        Font font = sheet.getWorkbook().createFont();
        font.setFontName("黑体"); // 或者使用 "SimHei" 表示黑体
        font.setBoldweight((short) 1);      // 设置为粗体（黑体通常是粗体）
        style.setFont(font);
        style.setAlignment(ALIGN_CENTER);
        style.setVerticalAlignment(VERTICAL_CENTER);
        font.setColor(IndexedColors.WHITE.getIndex()); // 设置字体颜色为白色

        // 创建表头
        Row headerRow = sheet.createRow(0);
//        headerRow.setHeightInPoints(30);
        for (int i = 0; i < headers.size(); i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellStyle(styles.get("header"));
            cell.setCellValue(headers.get(i));
        }

        // 填充数据
        int rowIndex = 1;
        for (List<Object> rowData : data) {
            Row row = sheet.createRow(rowIndex++);
            for (int i = 0; i < rowData.size(); i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(rowData.get(i).toString());
            }
        }

        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=example.xlsx");

        // 将工作簿写入输出流
        try (OutputStream outputStream = response.getOutputStream()) {
            workbook.write(outputStream);
        }
        // 关闭工作簿
//        workbook.close();
//        workbook.cloneSheet(0);
    }
    /**
     * 创建表格样式
     *
     * @param wb 工作薄对象
     * @return 样式列表
     */
    private static Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

        CellStyle style = wb.createCellStyle();
        style.setAlignment(ALIGN_CENTER);
        style.setVerticalAlignment(VERTICAL_CENTER);
        Font titleFont = wb.createFont();
        titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(titleFont);
        styles.put("title", style);

        style = wb.createCellStyle();
        style.setVerticalAlignment(VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(CellStyle.BORDER_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(CellStyle.ALIGN_LEFT);
        styles.put("data1", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(ALIGN_CENTER);
        styles.put("data2", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        styles.put("data3", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
//		style.setWrapText(true);
        style.setAlignment(ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        return styles;
    }
}

使用

@RequestMapping("exportSearchList")
    public void exportSearchList(HttpServletResponse response) throws IOException {
        // 初始化表头和数据
        List<String> headers = Arrays.asList("列1", "列2", "列3");
        List<List<Object>> data = new ArrayList<>();
        data.add(Arrays.asList("数据1", "数据2", "数据3"));
        data.add(Arrays.asList("数据4", "数据5", "数据6"));

        ExcelCustomExportUtil.exportExcel(response, headers, data);
    }