spring boot 整合poi实现导出的excel字体颜色，大小，边框等配置

最新推荐文章于 2024-08-29 09:00:00 发布

心起

最新推荐文章于 2024-08-29 09:00:00 发布

阅读量360

点赞数 1

本文链接：https://blog.csdn.net/seeblood/article/details/137677382

版权

本文介绍了前端通过JSON格式传递数据，后端使用ApachePOI库处理这些数据并将之转换为Excel文件的过程，展示了如何设置单元格样式和宽度。

摘要由CSDN通过智能技术生成

传入的前端json格式如下，前端直接传入json，后端根据json生成excel

const test =  [
  [
    { value: 100, fontSize: 14, color: '#f00', background: '#000', fontWeight: 'bold', width: 200 },
    { value: 70, fontSize: 14, color: '#f00', background: '#000', fontWeight: 'bold', width: 100 },
    { value: 70, fontSize: 14, color: '#f00', background: '#000', fontWeight: 'bold', width: 300 },
    { value: 50, fontSize: 14, color: '#f00', background: '#000', fontWeight: 'bold', width: 200 },
  ]
]

后端代码如下，直接接收然后就会导出excel

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.zichan.assets.entity.Assets;
import com.ruoyi.zichan.assets.service.IAssetsService;
import com.ruoyi.zichan.units.CommonUnit;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;


@ApiOperation("把传递的数据导出为excel")
    @PostMapping("/generate")
    public void generate(HttpServletResponse response, @RequestBody List<List<Map<String, Object>>> data) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Data Sheet");

        int rowNum = 0;
        for (List<Map<String, Object>> rowData : data) {
            Row row = sheet.createRow(rowNum++);
            int colNum = 0;
            for (Map<String, Object> cellData : rowData) {
                Cell cell = row.createCell(colNum++);

                // 设置单元格的值
                String value = (String) cellData.getOrDefault("value", "");
                cell.setCellValue(value);

                CellStyle style = workbook.createCellStyle();
                Font font = workbook.createFont();

                // 设置字体大小，如果没有提供则使用默认值12
                Integer fontSize = (Integer) cellData.getOrDefault("fontSize", 12);
                short fontSizeShort = fontSize.shortValue();
                font.setFontHeightInPoints(fontSizeShort);

                // 设置字体颜色，如果没有提供则使用黑色
                Integer color = (Integer) cellData.getOrDefault("color", (int) IndexedColors.BLACK.getIndex());
                short colorShort = color.shortValue();
                font.setColor(colorShort);

                // 设置背景色，如果没有提供则不设置背景
                Integer background = (Integer) cellData.getOrDefault("background", null);
                short backgroundShort = background != null ? background.shortValue() : IndexedColors.WHITE.getIndex();
                style.setFillForegroundColor(backgroundShort);
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

                // 设置居中显示
                if (cellData.containsKey("center")) {
                    style.setAlignment(HorizontalAlignment.CENTER);
                    style.setVerticalAlignment(VerticalAlignment.CENTER);
                }

                // 设置边框
                style.setBorderBottom(BorderStyle.THIN);
                style.setBorderLeft(BorderStyle.THIN);
                style.setBorderTop(BorderStyle.THIN);
                style.setBorderRight(BorderStyle.THIN);

                // 设置字体加粗，如果没有提供则默认为不加粗
                Boolean isBold = Optional.ofNullable((String) cellData.get("fontWeight"))
                        .map(String::toLowerCase)
                        .map(s -> "true".equals(s))
                        .orElse(false);
                font.setBold(isBold);

                // 应用字体到样式，然后将样式应用到单元格
                style.setFont(font);
                cell.setCellStyle(style);

                // 设置列宽，如果没有提供则不设置列宽
                Integer width = (Integer) cellData.getOrDefault("width", null);
                if (width != null) {
                    sheet.setColumnWidth(colNum - 1, width * 256);
                }
            }
        }

        workbook.write(response.getOutputStream());
        workbook.close();
    }