Excel集成poi实现导出的功能

1、添加相关依赖

<dependency>
<groupId>org.example</groupId>
<artifactId>excelPoi</artifactId>
<version>1.0</version>
<dependency>

2、然后把你得到的数据

String jsonString = "[{"你的json数据"}]"
ObjectMapper objectMapper = new ObjectMapper();
List<DeptWeekDTO> deptWeekDTOList = objectMapper.readValue(jsonString, new TypeReference<List<DeptWeekDTO>>() {});
ExportUtil.exportCompanyMonthTaskV2(deptWeekDTOList,"公司%s年%s月工作计划" );

因为这里面用到了反序列化所以介绍一下
 

ExportUtil中

package com.sict;

import com.sict.domain.DeptWeekDTO;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.awt.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.List;

public class ExportUtil {


    private static CellStyle getDefaultCellStyle(XSSFWorkbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(true);
        return cellStyle;
    }

    private static CellStyle getContentCellStyle(XSSFWorkbook workbook) {
        CellStyle cellStyle = getDefaultCellStyle(workbook);
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setFontName("方正仿宋_GBK");
        cellStyle.setFont(font);
        return cellStyle;
    }

    private static CellStyle getLeftCellStyle(XSSFWorkbook workbook, short fontSize) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(true);
        Font font = workbook.createFont();
        font.setFontHeightInPoints(fontSize);
        font.setFontName("方正仿宋_GBK");
        cellStyle.setFont(font);
        return cellStyle;
    }

    private static CellStyle getTitleCellStyle(XSSFWorkbook workbook) {
        CellStyle cellStyle = getDefaultCellStyle(workbook);
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 14);
        font.setFontName("方正楷体_GBK");
        font.setBold(true);
        cellStyle.setFont(font);
        return cellStyle;
    }

    private static void setCellValue(XSSFRow row, int columnIndex, String cellValue, CellStyle cellStyle) {
        XSSFCell cell = row.createCell(columnIndex);
        cell.setCellValue(cellValue);
        cell.setCellStyle(cellStyle);
    }

    /**
     * 导出excel
     */
    public static void exportCompanyMonthTaskV2(List<DeptWeekDTO> deptTaskList, String fileName) {
        try {
            // 创建工作簿
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet(fileName);
            setTitleV2(workbook, sheet);
            setDataV2(deptTaskList, workbook, sheet);
            // 保存工作簿为临时文件
            Path tempFilePath;
            try {
                tempFilePath = Files.createTempFile("output", ".xlsx");
                try (FileOutputStream outputStream = new FileOutputStream(tempFilePath.toFile())) {
                    workbook.write(outputStream);
                }
            } catch (IOException e) {
                e.printStackTrace();
                return;
            }
            // 弹出 Excel 文件
            try {
                Desktop.getDesktop().open(tempFilePath.toFile());
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            throw new RuntimeException();
        }
    }

    private static void setTitleV2(XSSFWorkbook workbook, XSSFSheet sheet) {
        XSSFRow row = sheet.createRow(0);
        CellStyle titleCellStyle = getTitleCellStyle(workbook);
        setCellValue(row, 0, "序号", titleCellStyle);
        setCellValue(row, 1, "部门名称", titleCellStyle);
        setCellValue(row, 2,"使用周数", titleCellStyle);
        setCellValue(row, 3, "任务条数", titleCellStyle);
        setCellValue(row, 4, "进度100%", titleCellStyle);
        setCellValue(row, 5, "100%占比", titleCellStyle);
        sheet.setColumnWidth(0, 4000);
        sheet.setColumnWidth(1, 20000);
        sheet.setColumnWidth(2, 4000);
        sheet.setColumnWidth(3, 4000);
        sheet.setColumnWidth(4, 4000);
        sheet.setColumnWidth(5, 6000);
    }

    private static void setDataV2(List<DeptWeekDTO> deptTaskList, XSSFWorkbook workbook, XSSFSheet sheet) {
        CellStyle cellStyle = getContentCellStyle(workbook);
        int index = 1;
        int dataIndex = 1;
        for (int i = 0; i< deptTaskList.size(); i++) {
            DeptWeekDTO deptWeekDTO = deptTaskList.get(i);
            XSSFRow row1 = sheet.createRow(++index);
            setCellValue(row1, 0, String.valueOf(dataIndex++), cellStyle);
            setCellValue(row1, 1, deptWeekDTO.getDeptName() ,cellStyle);
            setCellValue(row1, 2, String.valueOf(deptWeekDTO.getTotalDurationWeeks()), cellStyle);
            setCellValue(row1, 3, String.valueOf(deptWeekDTO.getTotalTask()), cellStyle);
            setCellValue(row1, 4, String.valueOf(deptWeekDTO.getHundredCount()), cellStyle);
            setCellValue(row1, 5, deptWeekDTO.getHundredProportion(), cellStyle);
        }
    }
}

调用相应的方法就可以了