导出EXCEL

在实际的开发过程当中，我们会遇到一些比较复杂的导出需求，例如需要导出的实体类中需要嵌套集合对象等，正好最近碰到了所以分享出来，希望对大家有帮助

---

一、POI是什么

简单的说就是Apache软件基金会提供的一个库，这个库里有API，API赋予了Java程序更强大的读和写的能力

二、使用步骤

这里是结果

1、POM.XML

    <dependencies>
        <dependency>
            <groupId>com.monitorjbl</groupId>
            <artifactId>xlsx-streamer</artifactId>
            <version>2.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.10</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.6.0</version>
        </dependency>
    </dependencies>

2、常量

    public static final Integer ZERO = 0;
    public static final Integer ONE = 1;
    public static final Integer UNQUALIFIED_INFO_INDEX = 24;
    public static final String[] TITLE_LIST = new String[]{
            "汉皇重色思倾国", "御宇多年求不得", "杨家有女初长成", "养在深闺人未识", "天生丽质难自弃", "一朝选在君王侧",
            "回眸一笑百媚生", "六宫粉黛无颜色", "春寒赐浴华清池", "温泉水滑洗凝脂", "侍儿扶起娇无力", "始是新承恩泽时",
            "云鬓花颜金步摇", "芙蓉帐暖度春宵", "春宵苦短日高起", "从此君王不早朝", "承欢侍宴无闲暇", "春从春游夜专夜",
            "后宫佳丽三千人", "三千宠爱在一身", "金屋妆成娇侍夜", "玉楼宴罢醉和春", "姊妹弟兄皆列土", "可怜光彩生门户",
            "花冠不整下堂来", "风吹仙袂飘飖举", "犹似霓裳羽衣舞", "在天愿作比翼鸟", "在地愿为连理枝", "天长地久有时尽",
            "此恨绵绵无绝期", "综合评估说明", "处理情况", "合格类型"};

该处为标题

3、实体类

package cn.itsaxon.vo;

import lombok.*;

import java.io.Serializable;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Getter
@Setter
public class ExportVO implements Serializable {
    private String orderId = "";
    private String customerProvince = "";
    private String customerArea = "";
    private String dealerName = "";
    private String customerName = "";
    private String customerTel = "";
    private String customerIdCard = "";
    private String inverterList = "";
    private String inverterNum = "";
    private String cooperationName = "";
    private String customerAddress = "";
    private String roofType = "";
    private String singlePower = "";
    private String actualNum = "";
    private String actualPower = "";
    private String onGridModel = "";
    private String installDate = "";
    private String taskType = "";
    private String startInspectionDate = "";
    private String endInspectionDate = "";
    private String taskOperatorName = "";
    private String auditor = "";
    private String taskStatus = "";
    private String acceptResult = "";
    private String overTimeStatus = "";
    private String proposalSubmitDate = "";
    private String resultSubmitDate = "";
    private String onGridPeriod= "";
    private String riskAssessment = "";
    private String applyDescribe = "";
    private String handlingOpinions = "";
    private String completedFlag = "";
    private String unqualifiedInfo = "";
}

4、表格工具类

package cn.itsaxon.utils;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;

/**
 * Excel工具类
 *
 * @author itsaxon
 * @date 2021-03-17 16:39
 */
public class ExcelUtil {

    /**
     * 将Sheet列号变为列名
     *
     * @param index 列号, 从0开始
     * @return 0->A; 1->B...26->AA
     */
    public static String index2ColName(int index) {
        if (index < 0) {
            return null;
        }
        // A的Unicode码
        int num = 65;
        String colName = "";
        do {
            if (colName.length() > 0) {
                index--;
            }
            int remainder = index % 26;
            colName = ((char) (remainder + num)) + colName;
            index = (int) ((index - remainder) / 26);
        } while (index > 0);
        return colName;
    }

    /**
     * 根据表元的列名转换为列号
     *
     * @param colName 列名, 从A开始
     * @return A1->0; B1->1...AA1->26
     */
    public static int colName2Index(String colName) {
        int index = -1;
        // A的Unicode码
        int num = 65;
        int length = colName.length();
        for (int i = 0; i < length; i++) {
            char c = colName.charAt(i);
            // 确定指定的char值是否为数字
            if (Character.isDigit(c)) break;
            index = (index + 1) * 26 + (int) c - num;
        }
        return index;
    }

    public static XSSFFont getFont(XSSFWorkbook workbook, String fontName, int fontSize) {
        XSSFFont font = workbook.createFont();
        font.setFontName(fontName);
        font.setFontHeightInPoints((short) fontSize);
        return font;
    }

    public static XSSFFont getFont(XSSFWorkbook workbook, String fontName, int fontSize, int color) {
        XSSFFont font = getFont(workbook, fontName, fontSize);
        font.setColor((short) color);
        return font;
    }

    public static XSSFFont getFont(XSSFWorkbook workbook, String fontName, int fontSize, boolean bold) {
        XSSFFont font = getFont(workbook, fontName, fontSize);
        font.setBold(bold);
        return font;
    }

    public static XSSFFont getFont(XSSFWorkbook workbook, String fontName, int fontSize, boolean bold, int color) {
        XSSFFont font = getFont(workbook, fontName, fontSize);
        font.setBold(bold);
        font.setColor((short) color);
        return font;
    }

    public static XSSFFont getFont(XSSFWorkbook workbook, String fontName, int fontSize, boolean bold, byte[] color) {
        XSSFFont font = getFont(workbook, fontName, fontSize);
        font.setBold(bold);
        font.setColor(new XSSFColor(color, new DefaultIndexedColorMap()));
        return font;
    }

    public static XSSFCellStyle getStyle(XSSFWorkbook workbook, XSSFFont font, byte[] color) {
        XSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);
        if (null != color) {
            style.setFillForegroundColor(new XSSFColor(color, new DefaultIndexedColorMap()));
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        return style;
    }

    public static XSSFCellStyle getStyle(XSSFWorkbook workbook, XSSFFont font, byte[] color, boolean isCenter) {
        XSSFCellStyle style = getStyle(workbook, font, color);
        if (isCenter) {
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
        }
        return style;
    }

    public static XSSFCellStyle getStyle(XSSFWorkbook workbook, XSSFFont font, byte[] color, boolean isCenter, BorderStyle borderStyle) {
        XSSFCellStyle style = getStyle(workbook, font, color);
        if (isCenter) {
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
        }
        if (null != borderStyle) {
            style.setBorderBottom(borderStyle);
            style.setBorderLeft(borderStyle);
            style.setBorderRight(borderStyle);
            style.setBorderTop(borderStyle);
        }
        return style;
    }

    public static XSSFCellStyle getStyle(XSSFWorkbook workbook, XSSFFont font, byte[] color, boolean isCenter, BorderStyle topStyle, BorderStyle bottomStyle) {
        XSSFCellStyle style = getStyle(workbook, font, color);
        if (isCenter) {
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
        }
        if (null != topStyle) {
            style.setBorderTop(topStyle);
        }
        if (null != bottomStyle) {
            style.setBorderBottom(bottomStyle);
        }
        return style;
    }

    public static XSSFCellStyle getStyle(XSSFWorkbook workbook, XSSFFont font, byte[] color, boolean isCenter, BorderStyle topStyle, BorderStyle bottomStyle, BorderStyle lrStyle) {
        XSSFCellStyle style = getStyle(workbook, font, color);
        if (isCenter) {
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
        }
        if (null != topStyle) {
            style.setBorderTop(topStyle);
        }
        if (null != bottomStyle) {
            style.setBorderBottom(bottomStyle);
        }
        if (null != lrStyle) {
            style.setBorderLeft(lrStyle);
            style.setBorderRight(lrStyle);
        }
        return style;
    }

    public static XSSFCellStyle getStyle(XSSFWorkbook workbook, XSSFFont font, byte[] color, boolean isCenter, BorderStyle topStyle, BorderStyle bottomStyle, BorderStyle leftStyle, BorderStyle rightStyle) {
        XSSFCellStyle style = getStyle(workbook, font, color);
        if (isCenter) {
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
        }
        if (null != topStyle) {
            style.setBorderTop(topStyle);
        }
        if (null != bottomStyle) {
            style.setBorderBottom(bottomStyle);
        }
        if (null != leftStyle) {
            style.setBorderLeft(leftStyle);
        }
        if (null != rightStyle) {
            style.setBorderRight(rightStyle);
        }
        return style;
    }
}

5、导出业务实现

代码如下：

package cn.itsaxon.service.serviceImpl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import cn.itsaxon.contract.Constants;
import cn.itsaxon.vo.ExportVO;
import cn.itsaxon.utils.ExcelUtil;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;

public class ExportService {

    /**
     * 导出
     *
     * @param exportVOList
     * @throws IOException
     */
    public static void export(List<ExportVO> exportVOList) throws IOException {
        XSSFWorkbook workbook;
        // 创建工作簿
        workbook = new XSSFWorkbook();
        // 创建sheet
        Sheet sheet = workbook.createSheet("Sheet0");
        sheet.autoSizeColumn(1);
        // 创建标题行
        Row titleRow = sheet.createRow(0);
        // 定义标题样式
        CellStyle titleStyle = ExcelUtil.getStyle(workbook, ExcelUtil.getFont(workbook, "宋体", 10, true), null, true);
        // 写入样式
        for (int i = 0; i < Constants.TITLE_LIST.length; i++) {
            Cell title = titleRow.createCell(i);
            title.setCellStyle(titleStyle);
            title.setCellValue(Constants.TITLE_LIST[i]);
        }
        if (CollectionUtils.isEmpty(exportVOList)) {
            return;
        }
        // 定义内容样式
        CellStyle style = ExcelUtil.getStyle(workbook, ExcelUtil.getFont(workbook, "宋体", 8, false, new byte[]{(byte) 0, (byte) 176, (byte) 80}), null, true);
        // 一条工单的开始行数
        int startCell = Constants.ONE;
        // 一条工单的结束行数
        int endCell = Constants.ONE;
        // 不合格项的索引
        int unqualifiedInfoIndex = Constants.UNQUALIFIED_INFO_INDEX;
        // 写入内容
        int m, x, y, z;
        for (m = 0; m < exportVOList.size(); m++) {
            Row row = sheet.createRow(startCell);
            // 不合格项之前的数据
            createCellInfoBefore(row, style, exportVOList, m);
            // 不合格项的数据
            Cell unqualifiedCell = row.createCell(unqualifiedInfoIndex);
            unqualifiedCell.setCellStyle(style);
            // 获取不合格项数组
            String[] temp = StrUtil.split(exportVOList.get(m).getUnqualifiedInfo(), ",");
            // 转集合
            List<String> unqualifiedInfoList = Arrays.asList(temp);
            // 不合格项填充 合格项不处理（默认为空）
            if (CollectionUtil.isNotEmpty(unqualifiedInfoList)) {
                unqualifiedCell.setCellValue(unqualifiedInfoList.get(Constants.ZERO));
            }
            // 不合格项之后的数据
            createCellInfoAfter(row, style, exportVOList, m);
            // 空或一个不合格项
            if (CollectionUtil.isEmpty(unqualifiedInfoList) || unqualifiedInfoList.size() <= 1) {
                // 重置下一行的行数
                startCell++;
                // 记录结束行数
                if (m > 0) {
                    endCell++;
                }
                continue;
            }
            // 多个不合格项
            for (x = 1; x < unqualifiedInfoList.size(); x++) {
                // 每进来一次说明多写一行
                endCell++;
                row = sheet.createRow(x + startCell);
                // 不合格项之前的数据
                createCellInfoBefore(row, style, exportVOList, m);
                // 不合格项数据
                Cell unqualifiedCellMore = row.createCell(unqualifiedInfoIndex);
                unqualifiedCellMore.setCellStyle(style);
                unqualifiedCellMore.setCellValue(unqualifiedInfoList.get(x));
                // 不合格项之后的数据
                createCellInfoAfter(row, style, exportVOList, m);
            }
            // 记录结束行数 在外层先写一行 所以自增
            if (m > 0) {
                endCell++;
            }
            // 合并单元格：起始行，结束行，起始列，结束列
            for (y = 0; y < unqualifiedInfoIndex; y++) {
                mergedRegion(startCell, endCell, y, y, sheet);
            }
            for (z = unqualifiedInfoIndex + 1; z < Constants.TITLE_LIST.length; z++) {
                mergedRegion(startCell, endCell, z, z, sheet);
            }
            // 记录下一行开始行数
            startCell = endCell + Constants.ONE;
        }
        File file = new File("F:\\呆猫.xlsx");
        FileOutputStream fout = new FileOutputStream(file);
        workbook.write(fout);
        fout.close();
    }


    /**
     * 合并单元格
     *
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     */
    public static void mergedRegion(int firstRow, int lastRow, int firstCol, int lastCol, Sheet sheet) {
        CellRangeAddress cellTemp = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(cellTemp);
    }

    /**
     * 写入多数据项之前的记录
     *
     * @param row
     * @param style
     * @param exportVOList
     * @param m
     */
    public static void createCellInfoBefore(Row row, CellStyle style, List<ExportVO> exportVOList, int m) {
        Cell customerProvinceCell = row.createCell(0);
        customerProvinceCell.setCellStyle(style);
        customerProvinceCell.setCellValue(exportVOList.get(m).getCustomerProvince());
        Cell customerAreaCell = row.createCell(1);
        customerAreaCell.setCellStyle(style);
        customerAreaCell.setCellValue(exportVOList.get(m).getCustomerArea());
        Cell dealerNameCell = row.createCell(2);
        dealerNameCell.setCellStyle(style);
        dealerNameCell.setCellValue(exportVOList.get(m).getDealerName());
        Cell customerNameCell = row.createCell(3);
        customerNameCell.setCellStyle(style);
        customerNameCell.setCellValue(exportVOList.get(m).getCustomerName());
        Cell customerTelCell = row.createCell(4);
        customerTelCell.setCellStyle(style);
        customerTelCell.setCellValue(exportVOList.get(m).getCustomerTel());
        Cell customerIdCardCell = row.createCell(5);
        customerIdCardCell.setCellStyle(style);
        customerIdCardCell.setCellValue(exportVOList.get(m).getCustomerIdCard());
        Cell inverterListCell = row.createCell(6);
        inverterListCell.setCellStyle(style);
        inverterListCell.setCellValue(exportVOList.get(m).getInverterList());
        Cell inverterNumCell = row.createCell(7);
        inverterNumCell.setCellStyle(style);
        inverterNumCell.setCellValue(exportVOList.get(m).getInverterNum());
        Cell cooperationNameCell = row.createCell(8);
        cooperationNameCell.setCellStyle(style);
        cooperationNameCell.setCellValue(exportVOList.get(m).getCooperationName());
        Cell customerAddressCell = row.createCell(9);
        customerAddressCell.setCellStyle(style);
        customerAddressCell.setCellValue(exportVOList.get(m).getCustomerAddress());
        Cell roofTypeCell = row.createCell(10);
        roofTypeCell.setCellStyle(style);
        roofTypeCell.setCellValue(exportVOList.get(m).getRoofType());
        Cell singlePowerCell = row.createCell(11);
        singlePowerCell.setCellStyle(style);
        singlePowerCell.setCellValue(exportVOList.get(m).getSinglePower());
        Cell actualNumCell = row.createCell(12);
        actualNumCell.setCellStyle(style);
        actualNumCell.setCellValue(exportVOList.get(m).getActualNum());
        Cell actualPowerCell = row.createCell(13);
        actualPowerCell.setCellStyle(style);
        actualPowerCell.setCellValue(exportVOList.get(m).getActualPower());
        Cell onGridModelCell = row.createCell(14);
        onGridModelCell.setCellStyle(style);
        onGridModelCell.setCellValue(1);
        Cell installDateCell = row.createCell(15);
        installDateCell.setCellStyle(style);
        installDateCell.setCellValue(exportVOList.get(m).getInstallDate());
        Cell startInspectionDateCell = row.createCell(16);
        startInspectionDateCell.setCellStyle(style);
        startInspectionDateCell.setCellValue(exportVOList.get(m).getStartInspectionDate());
        Cell endInspectionDateCell = row.createCell(17);
        endInspectionDateCell.setCellStyle(style);
        endInspectionDateCell.setCellValue(exportVOList.get(m).getEndInspectionDate());
        Cell taskOperatorCell = row.createCell(18);
        taskOperatorCell.setCellStyle(style);
        taskOperatorCell.setCellValue(exportVOList.get(m).getTaskOperatorName());
        Cell startInspectionDateCell2 = row.createCell(19);
        startInspectionDateCell2.setCellStyle(style);
        startInspectionDateCell2.setCellValue(exportVOList.get(m).getStartInspectionDate());
        Cell endInspectionDateCell3 = row.createCell(20);
        endInspectionDateCell3.setCellStyle(style);
        endInspectionDateCell3.setCellValue(exportVOList.get(m).getEndInspectionDate());
        Cell taskOperatorCell2 = row.createCell(21);
        taskOperatorCell2.setCellStyle(style);
        taskOperatorCell2.setCellValue(exportVOList.get(m).getTaskOperatorName());
        Cell managerCell = row.createCell(22);
        managerCell.setCellStyle(style);
        managerCell.setCellValue(exportVOList.get(m).getAuditor());
        Cell taskStatusCell = row.createCell(23);
        taskStatusCell.setCellStyle(style);
        taskStatusCell.setCellValue(1);
    }

    /**
     * 写入多数据项之后的记录
     *
     * @param row
     * @param style
     * @param exportVOList
     * @param m
     */
    public static void createCellInfoAfter(Row row, CellStyle style, List<ExportVO> exportVOList, int m) {
        Cell acceptResultCell = row.createCell(25);
        acceptResultCell.setCellStyle(style);
        acceptResultCell.setCellValue(exportVOList.get(m).getAcceptResult());
        Cell overTimeStatusCell = row.createCell(26);
        overTimeStatusCell.setCellStyle(style);
        overTimeStatusCell.setCellValue(1);
        Cell proposalSubmitDateCell = row.createCell(27);
        proposalSubmitDateCell.setCellStyle(style);
        proposalSubmitDateCell.setCellValue(exportVOList.get(m).getProposalSubmitDate());
        Cell resultSubmitDateCell = row.createCell(28);
        resultSubmitDateCell.setCellStyle(style);
        resultSubmitDateCell.setCellValue(exportVOList.get(m).getResultSubmitDate());
        Cell onGridPeriodCell = row.createCell(29);
        onGridPeriodCell.setCellStyle(style);
        onGridPeriodCell.setCellValue(exportVOList.get(m).getOnGridPeriod());
        Cell riskAssessmentCell = row.createCell(30);
        riskAssessmentCell.setCellStyle(style);
        riskAssessmentCell.setCellValue(1);
        Cell applyDescribeCell = row.createCell(31);
        applyDescribeCell.setCellStyle(style);
        applyDescribeCell.setCellValue(exportVOList.get(m).getApplyDescribe());
        Cell handlingOpinions = row.createCell(32);
        handlingOpinions.setCellStyle(style);
        handlingOpinions.setCellValue(exportVOList.get(m).getHandlingOpinions());
        Cell completedFlag = row.createCell(33);
        completedFlag.setCellStyle(style);
        completedFlag.setCellValue(exportVOList.get(m).getCompletedFlag().equals(1));
    }

    public static void main(String[] args) throws IOException {
        List<ExportVO> list = new ArrayList<ExportVO>();
        ExportVO eo = ExportVO.builder()
                .orderId("itsaxon")
                .customerProvince("itsaxon")
                .customerArea("itsaxon")
                .dealerName("itsaxon")
                .customerName("itsaxon")
                .customerTel("itsaxon")
                .customerIdCard("itsaxon")
                .inverterList("itsaxon")
                .inverterNum("itsaxon")
                .cooperationName("itsaxon")
                .customerAddress("itsaxon")
                .roofType("itsaxon")
                .singlePower("itsaxon")
                .actualNum("itsaxon")
                .actualPower("itsaxon")
                .onGridModel("itsaxon")
                .installDate("itsaxon")
                .taskType("itsaxon")
                .startInspectionDate("itsaxon")
                .endInspectionDate("itsaxon")
                .taskOperatorName("itsaxon")
                .auditor("itsaxon")
                .taskStatus("itsaxon")
                .acceptResult("itsaxon")
                .overTimeStatus("itsaxon")
                .proposalSubmitDate("itsaxon")
                .resultSubmitDate("itsaxon")
                .onGridPeriod("itsaxon")
                .riskAssessment("itsaxon")
                .applyDescribe("itsaxon")
                .handlingOpinions("itsaxon")
                .completedFlag("itsaxon")
                .unqualifiedInfo("1,2,3")
                .build();
			//造数据 省略
        export(list);
    }
}

总结

喜欢就收藏吧，感谢看完