Excel列动态导出

列动态导出的Excel，大致整了一下，没有过多深入。

 

1、首先定义列样式

package a.b.common.util.excel.stream;

import java.util.Objects;

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;

/**
 * <p>
 * ExcelCellStyle, 单元格样式
 * </p>
 * 如需帮助, 请使用 {@link org.apache.poi.ss.util.CellUtil }
 *
 * @author Yifei
 * @version 1.0.0
 * @date 2019/10/4 03:08
 */
public class ExcelCellStyle {

    private HorizontalAlignment alignment;

    private VerticalAlignment verticalAlignment;

    private BorderStyle borderBottom;

    private BorderStyle borderLeft;

    private BorderStyle borderRight;

    private BorderStyle borderTop;

    /**
     * 参照 {@link org.apache.poi.ss.usermodel.IndexedColors}
     */
    private short bottomBorderColor;

    private FillPatternType fillPattern;

    /**
     * 参照 {@link org.apache.poi.ss.usermodel.IndexedColors}
     */
    private short fillForegroundColor;

    /**
     * 参照 {@link org.apache.poi.ss.usermodel.IndexedColors}
     */
    private short fillBackgroundColor;

    private boolean hidden;

    /**
     * indent - number of spaces
     */
    private short indention;

    /**
     * 参照 {@link org.apache.poi.ss.usermodel.IndexedColors}
     */
    private short leftBorderColor;

    private boolean locked;

    /**
     * 参照 {@link org.apache.poi.ss.usermodel.IndexedColors}
     */
    private short rightBorderColor;

    /**
     * rotation degrees (between 0 and 180 degrees)
     */
    private short rotation;

    /**
     * 参照 {@link org.apache.poi.ss.usermodel.IndexedColors}
     */
    private short topBorderColor;

    private boolean wrapText;

    public ExcelCellStyle() {
        // nothing to do
    }

    public HorizontalAlignment getAlignment() {
        return alignment;
    }

    public void setAlignment(HorizontalAlignment alignment) {
        this.alignment = alignment;
    }

    public VerticalAlignment getVerticalAlignment() {
        return verticalAlignment;
    }

    public void setVerticalAlignment(VerticalAlignment verticalAlignment) {
        this.verticalAlignment = verticalAlignment;
    }

    public BorderStyle getBorderBottom() {
        return borderBottom;
    }

    public void setBorderBottom(BorderStyle borderBottom) {
        this.borderBottom = borderBottom;
    }

    public BorderStyle getBorderLeft() {
        return borderLeft;
    }

    public void setBorderLeft(BorderStyle borderLeft) {
        this.borderLeft = borderLeft;
    }

    public BorderStyle getBorderRight() {
        return borderRight;
    }

    public void setBorderRight(BorderStyle borderRight) {
        this.borderRight = borderRight;
    }

    public BorderStyle getBorderTop() {
        return borderTop;
    }

    public void setBorderTop(BorderStyle borderTop) {
        this.borderTop = borderTop;
    }

    public short getBottomBorderColor() {
        return bottomBorderColor;
    }

    public void setBottomBorderColor(short bottomBorderColor) {
        this.bottomBorderColor = bottomBorderColor;
    }

    public FillPatternType getFillPattern() {
        return fillPattern;
    }

    public void setFillPattern(FillPatternType fillPattern) {
        this.fillPattern = fillPattern;
    }

    public short getFillForegroundColor() {
        return fillForegroundColor;
    }

    public void setFillForegroundColor(short fillForegroundColor) {
        this.fillForegroundColor = fillForegroundColor;
    }

    public short getFillBackgroundColor() {
        return fillBackgroundColor;
    }

    public void setFillBackgroundColor(short fillBackgroundColor) {
        this.fillBackgroundColor = fillBackgroundColor;
    }

    public boolean isHidden() {
        return hidden;
    }

    public void setHidden(boolean hidden) {
        this.hidden = hidden;
    }

    public short getIndention() {
        return indention;
    }

    public void setIndention(short indention) {
        this.indention = indention;
    }

    public short getLeftBorderColor() {
        return leftBorderColor;
    }

    public void setLeftBorderColor(short leftBorderColor) {
        this.leftBorderColor = leftBorderColor;
    }

    public boolean isLocked() {
        return locked;
    }

    public void setLocked(boolean locked) {
        this.locked = locked;
    }

    public short getRightBorderColor() {
        return rightBorderColor;
    }

    public void setRightBorderColor(short rightBorderColor) {
        this.rightBorderColor = rightBorderColor;
    }

    public short getRotation() {
        return rotation;
    }

    public void setRotation(short rotation) {
        this.rotation = rotation;
    }

    public short getTopBorderColor() {
        return topBorderColor;
    }

    public void setTopBorderColor(short topBorderColor) {
        this.topBorderColor = topBorderColor;
    }

    public boolean isWrapText() {
        return wrapText;
    }

    public void setWrapText(boolean wrapText) {
        this.wrapText = wrapText;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (o == null || getClass() != o.getClass()) {
            return false;
        }
        ExcelCellStyle that = (ExcelCellStyle) o;
        return bottomBorderColor == that.bottomBorderColor && fillForegroundColor == that.fillForegroundColor && fillBackgroundColor == that.fillBackgroundColor && hidden == that.hidden && indention == that.indention && leftBorderColor == that.leftBorderColor && locked == that.locked && rightBorderColor == that.rightBorderColor && rotation == that.rotation && topBorderColor == that.topBorderColor && wrapText == that.wrapText && alignment == that.alignment && verticalAlignment == that.verticalAlignment && borderBottom == that.borderBottom && borderLeft == that.borderLeft && borderRight == that.borderRight && borderTop == that.borderTop && fillPattern == that.fillPattern;
    }

    @Override
    public int hashCode() {
        return Objects.hash(alignment, verticalAlignment, borderBottom, borderLeft, borderRight, borderTop, bottomBorderColor, fillPattern, fillForegroundColor, fillBackgroundColor, hidden, indention, leftBorderColor, locked, rightBorderColor, rotation, topBorderColor, wrapText);
    }

    @Override
    public String toString() {
        return "{" + "alignment=" + alignment + ", verticalAlignment=" + verticalAlignment + ", borderBottom=" + borderBottom + ", borderLeft=" + borderLeft + ", borderRight=" + borderRight + ", borderTop=" + borderTop + ", bottomBorderColor=" + bottomBorderColor + ", fillPattern=" + fillPattern + ", fillForegroundColor=" + fillForegroundColor + ", fillBackgroundColor=" + fillBackgroundColor + ", hidden=" + hidden + ", indention=" + indention + ", leftBorderColor=" + leftBorderColor + ", locked=" + locked + ", rightBorderColor=" + rightBorderColor + ", rotation=" + rotation + ", topBorderColor=" + topBorderColor + ", wrapText=" + wrapText + '}';
    }
}

 

2、定义列

package a.b.common.util.excel.stream;

import java.util.Map;
import java.util.function.Function;

import org.springframework.lang.NonNull;

/**
 * <p>
 * ExcelColumnTemplate 单元格,
 * {@code T}为{@code Model}形式或者@{code Map<String, Object>}
 * {@code V}为字段对应的类型, 例如: String/Integer/Double/Date...
 * </p>
 *
 * @author Yifei
 * @version 1.0.0
 * @date 2019/10/1 20:24
 */
@SuppressWarnings({"WeakerAccess", "unused"})
public class ExcelColumnTemplate<T> {

    /**
     * 列名
     */
    private String columnName;

    /**
     * 取值方式：索引，适用于T为 {@code Map<String, Object>} 形式
     * 取值时将会通过 T.get(dataIndex) 方式获取
     */
    private String dataIndex;

    /**
     * 取值方式：lambda，适用于T为{@code Model} 形式
     * 取值时将会通过 dataFunc.apply(T) 方式获取
     */
    private Function<T, Object> dataFunc;

    /**
     * 格式化，为null时不进行格式。
     * 时间/日期格式化使用 {@link org.apache.poi.xssf.usermodel.XSSFDataFormat#getFormat(String)}提供的方法进行格式化，
     * 其他类型使用{@link java.lang.String#format(String, Object...)}提供的方法进行格式化。
     */
    private String format;

    /**
     * 转化映射
     */
    private Map<String, String> convertMap;

    /**
     * 单元格样式, 当该接口不为null时, 通过单元格值, 获取style的id
     */
    private Function<Object, String> cellStyleFunc;

    public ExcelColumnTemplate() {
        // nothing to do
    }

    public ExcelColumnTemplate(String columnName, @NonNull Function<T, Object> dataFunc) {
        this(columnName, dataFunc, "");
    }

    public ExcelColumnTemplate(String columnName, @NonNull Function<T, Object> dataFunc, String format) {
        this(columnName, null, dataFunc, format, null, null);
    }

    public ExcelColumnTemplate(String columnName, @NonNull Function<T, Object> dataFunc, Map<String, String> convertMap) {
        this(columnName, null, dataFunc, null, convertMap, null);
    }

    public ExcelColumnTemplate(String columnName, @NonNull Function<T, Object> dataFunc, Function<Object, String> cellStyleFunc) {
        this(columnName, null, dataFunc, null, null, cellStyleFunc);
    }

    public ExcelColumnTemplate(String columnName, @NonNull Function<T, Object> dataFunc, String format, Function<Object, String> cellStyleFunc) {
        this(columnName, null, dataFunc, format, null, cellStyleFunc);
    }

    public ExcelColumnTemplate(String columnName, @NonNull Function<T, Object> dataFunc, Map<String, String> convertMap, Function<Object, String> cellStyleFunc) {
        this(columnName, null, dataFunc, null, convertMap, cellStyleFunc);
    }

    public ExcelColumnTemplate(String columnName, @NonNull String dataIndex) {
        this(columnName, dataIndex, null, null, null, null);
    }

    public ExcelColumnTemplate(String columnName, @NonNull String dataIndex, String format) {
        this(columnName, dataIndex, null, format, null, null);
    }

    public ExcelColumnTemplate(String columnName, @NonNull String dataIndex, Map<String, String> convertMap) {
        this(columnName, dataIndex, null, null, convertMap, null);
    }

    public ExcelColumnTemplate(String columnName, @NonNull String dataIndex, Function<Object, String> cellStyleFunc) {
        this(columnName, dataIndex, null, null, null, cellStyleFunc);
    }

    public ExcelColumnTemplate(String columnName, @NonNull String dataIndex, String format, Function<Object, String> cellStyleFunc) {
        this(columnName, dataIndex, null, format, null, cellStyleFunc);
    }

    public ExcelColumnTemplate(String columnName, @NonNull String dataIndex, Map<String, String> convertMap, Function<Object, String> cellStyleFunc) {
        this(columnName, dataIndex, null, null, convertMap, cellStyleFunc);
    }

    public ExcelColumnTemplate(String columnName, String dataIndex, Function<T, Object> dataFunc, String format, Map<String, String> convertMap, Function<Object, String> cellStyleFunc) {
        if (dataIndex == null && dataFunc == null) {
            throw new IllegalArgumentException("arguments dataIndex and dataFunc can't both null");
        }
        this.columnName = columnName;
        this.dataIndex = dataIndex;
        this.dataFunc = dataFunc;
        this.format = format;
        this.convertMap = convertMap;
        this.cellStyleFunc = cellStyleFunc;
    }

    public String getColumnName() {
        return columnName;
    }

    public void setColumnName(String columnName) {
        this.columnName = columnName;
    }

    public String getDataIndex() {
        return dataIndex;
    }

    public void setDataIndex(String dataIndex) {
        this.dataIndex = dataIndex;
    }

    public Function<T, Object> getDataFunc() {
        return dataFunc;
    }

    public void setDataFunc(Function<T, Object> dataFunc) {
        this.dataFunc = dataFunc;
    }

    public String getFormat() {
        return format;
    }

    public void setFormat(String format) {
        this.format = format;
    }

    public Map<String, String> getConvertMap() {
        return convertMap;
    }

    public void setConvertMap(Map<String, String> convertMap) {
        this.convertMap = convertMap;
    }

    public Function<Object, String> getCellStyleFunc() {
        return cellStyleFunc;
    }

    public void setCellStyleFunc(Function<Object, String> cellStyleFunc) {
        this.cellStyleFunc = cellStyleFunc;
    }
}

 

 

3、导出工具

package a.b.common.util.excel.stream;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLProperties;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import a.b.common.util.Encodes;

/**
 * <p>
 * ExportExcel2
 * </p>
 *
 * @author Yifei
 * @version 1.0.0
 * @date 2019/10/1 20:27
 */
public class ExportExcel2<E> {

    public static final String FONT_ARIAL = "Arial";

    private static final Logger logger = LoggerFactory.getLogger(ExportExcel2.class);

    /**
     * 工作薄对象
     */
    private SXSSFWorkbook wb;

    /**
     * 工作表对象
     */
    private SXSSFSheet sheet;

    /**
     * 工作页
     */
    private int sheetIndex = 0;

    /**
     * 样式列表
     */
    private Map<String, CellStyle> cellStyleMap;

    /**
     * 当前行号
     */
    private int rowNum;

    /**
     * 工作表的列
     */
    private List<ExcelColumnTemplate<E>> columnList;

    /**
     * 泛化类
     */
    private Class<E> clazz;

    public ExportExcel2(Class<E> clazz, List<ExcelColumnTemplate<E>> columnList) {
        this.clazz = clazz;
        this.columnList = columnList;
        this.init(null, null);
    }

    public ExportExcel2(Class<E> clazz, String sheetName, String title, List<ExcelColumnTemplate<E>> columnList) {
        this.clazz = clazz;
        this.columnList = columnList;
        this.init(sheetName, title);
    }

    public ExportExcel2 addCellStyle(Map<String, ExcelCellStyle> styleMap) {

        return this;
    }

    /**
     * 实验性功能
     * 如果title为空，则不加title
     *
     * @param sheetName 新sheet页面名称
     * @param title     title
     * @return ExportExcel2
     */
    public ExportExcel2 newSheet(String sheetName, String title) {
        // 建立新的sheet对象（excel的表单）
        if (StringUtils.isNotBlank(sheetName)) {
            this.sheet = wb.createSheet(sheetName);
        } else {
            this.sheet = wb.createSheet(String.format("Sheet%d", (++sheetIndex)));
        }
        // 重置row
        rowNum = 0;
        // add title
        this.addTitle(title, columnList.size());
        return this;
    }

    /**
     * 实验性功能
     * 当{@code E} 为 {@code Map} 时, 效果最好
     *
     * @param newColumnList 新列
     * @return ExportExcel2
     */
    public ExportExcel2 changeColumnTemplate(List<ExcelColumnTemplate<E>> newColumnList) {
        return this.changeColumnTemplate(newColumnList, true);
    }

    /**
     * 实验性功能
     * 当{@code E} 为 {@code Map} 时, 效果最好
     *
     * @param newColumnList 新列
     * @param makeHeader    是否生成新的Header
     * @return ExportExcel2
     */
    public ExportExcel2 changeColumnTemplate(List<ExcelColumnTemplate<E>> newColumnList, boolean makeHeader) {
        if (newColumnList == null || newColumnList.isEmpty()) {
            throw new IllegalArgumentException("newColumnList cant not null or empty!");
        }
        this.columnList = newColumnList;
        if (makeHeader) {
            this.addHeader();
        }
        return this;
    }

    /**
     * 添加数据
     *
     * @return list 数据列表
     */
    public ExportExcel2 setDataList(Iterable<E> iterableData) {

        boolean isFromMap = false;
        if (clazz.isAssignableFrom(Map.class)) {
            isFromMap = true;
        }

        for (E data : iterableData) {
            int columnNum = 0;
            Row row = this.addRow();
            for (ExcelColumnTemplate<E> col : columnList) {
                Object cellValue = null;
                // 取值
                if (col.getDataFunc() != null) {
                    cellValue = col.getDataFunc().apply(data);
                } else if (isFromMap) {
                    cellValue = ((Map) data).get(col.getDataIndex());
                }
                // 映射转换
                if (!CollectionUtils.isEmpty(col.getConvertMap())) {
                    cellValue = col.getConvertMap().get(cellValue == null ? null : String.valueOf(cellValue));
                }
                this.addCell(row, columnNum++, cellValue, col);
            }
            logger.trace("Write success: [{}] ", row.getRowNum());
        }
        return this;
    }

    /**
     * 输出数据流
     *
     * @param os 输出数据流
     */
    public ExportExcel2 write(OutputStream os) throws IOException {
        wb.write(os);
        return this;
    }

    /**
     * 输出到数据流
     *
     * @param os       数据流
     * @param version  版本号
     * @param revision 修订号
     * @return ExportExcel2
     * @throws IOException IOException
     */
    public ExportExcel2 write(OutputStream os, String version, Long revision) throws IOException {
        POIXMLProperties poixmlProperties = wb.getXSSFWorkbook().getProperties();
        POIXMLProperties.CoreProperties coreProperties = poixmlProperties.getCoreProperties();
        // 修订号 Long类型
        coreProperties.setRevision(String.valueOf(revision));
        // 版本号 String
        coreProperties.getUnderlyingProperties().setVersionProperty(version);
        wb.write(os);
        return this;
    }

    /**
     * 输出到客户端
     *
     * @param fileName 输出文件名
     */
    public ExportExcel2 write(HttpServletResponse response, String fileName) throws IOException {
        response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + Encodes.urlEncode(fileName));
        write(response.getOutputStream());
        return this;
    }

    /**
     * 输出到文件
     *
     * @param name 文件名
     */
    public ExportExcel2 writeFile(String name) throws IOException {
        FileOutputStream os = new FileOutputStream(name);
        this.write(os);
        return this;
    }

    /**
     * 清理临时文件
     */
    public ExportExcel2 dispose() {
        wb.dispose();
        return this;
    }

    private void init(String sheetName, String title) {
        if (columnList == null || columnList.isEmpty()) {
            throw new RuntimeException("columnList cant not null or empty!");
        }

        this.wb = new SXSSFWorkbook(100);
        // 样式表
        if (this.cellStyleMap == null) {
            this.cellStyleMap = this.createStyles(wb);
        } else {
            this.cellStyleMap.putAll(this.createStyles(wb));
        }
        // 建立新的sheet对象（excel的表单）
        if (StringUtils.isNotBlank(sheetName)) {
            this.sheet = wb.createSheet(sheetName);
        } else {
            this.sheet = wb.createSheet(String.format("Sheet%d", (++sheetIndex)));
        }

        // add title
        this.addTitle(title, columnList.size());
        // add header
        this.addHeader();
    }

    private void addTitle(String title, int columnSize) {
        // Create title
        if (StringUtils.isNotBlank(title)) {
            Row titleRow = sheet.createRow(rowNum++);
            // 设置行高
            titleRow.setHeightInPoints(30);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellStyle(cellStyleMap.get("title"));
            // 设置单元格的值
            titleCell.setCellValue(title);
            sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), 0, columnSize - 1));
        }
    }

    private void addHeader() {
        // Create header
        Row headerRow = sheet.createRow(rowNum++);
        headerRow.setHeightInPoints(16);
        for (int i = 0; i < columnList.size(); i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellStyle(cellStyleMap.get("header"));
            cell.setCellValue(columnList.get(i).getColumnName());
            sheet.trackAllColumnsForAutoSizing();
            sheet.autoSizeColumn(i);
        }
        for (int i = 0; i < columnList.size(); i++) {
            int colWidth = sheet.getColumnWidth(i) * 2;
            colWidth = colWidth < 3000 ? 3000 : (colWidth > 10496 ? 10496 : colWidth);
            sheet.setColumnWidth(i, colWidth);
        }
    }

    /**
     * 创建表格样式
     *
     * @param wb 工作薄对象
     * @return 样式列表
     */
    private Map<String, CellStyle> createStyles(SXSSFWorkbook wb) {
        Map<String, CellStyle> styles = new HashMap<>(16);

        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = wb.createFont();
        titleFont.setFontName(FONT_ARIAL);
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBold(titleFont.getBold());
        style.setFont(titleFont);
        styles.put("title", style);

        style = wb.createCellStyle();
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName(FONT_ARIAL);
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.LEFT);
        styles.put("data1", style);

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

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

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName(FONT_ARIAL);
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(headerFont.getBold());
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        return styles;
    }

    /**
     * 添加一行
     *
     * @return 行对象
     */
    private Row addRow() {
        return sheet.createRow(rowNum++);
    }

    /**
     * 添加单元格
     *
     * @param row       单元格所在行
     * @param columnNum 列序号
     * @param cellValue 单元格值
     * @param column    单元格格式
     */
    private void addCell(Row row, int columnNum, Object cellValue, ExcelColumnTemplate<E> column) {
        Cell cell = row.createCell(columnNum);
        if (column.getCellStyleFunc() != null) {
            cell.setCellStyle(this.cellStyleMap.get(column.getCellStyleFunc().apply(cellValue)));
        }
        if (cellValue == null) {
            cell.setCellType(CellType.BLANK);
        } else if (cellValue instanceof String) {
            cell.setCellValue((String) cellValue);
        } else if (cellValue instanceof Double) {
            cell.setCellValue((Double) cellValue);
        } else if (cellValue instanceof Date) {
            cell.setCellValue((Date) cellValue);
        } else if (cellValue instanceof Boolean) {
            cell.setCellValue((Boolean) cellValue);
        } else if (cellValue instanceof Calendar) {
            cell.setCellValue((Calendar) cellValue);
        } else if (cellValue instanceof RichTextString) {
            cell.setCellValue((RichTextString) cellValue);
        } else {
            cell.setCellType(CellType._NONE);
        }
    }
}

 

 

4、使用样例

package a.b.example.controller;

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

import javax.servlet.http.HttpServletResponse;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import a.b.common.util.excel.stream.ExcelColumnTemplate;
import a.b.common.util.excel.stream.ExportExcel2;

import io.swagger.annotations.Api;

/**
 * ExportExampleController
 *
 * @author WYF
 * @date 2020/4/26 10:36
 */
@RestController
@RequestMapping("/v1/sys/excel-test")
@Api("导出实验")
public class ExportExampleController {

    private static final Logger logger = LoggerFactory.getLogger(ExportExampleController.class);

    @GetMapping("/export")
    public void test(HttpServletResponse httpServletResponse) {

        try {
            // 动态列数
            int columnCount = 5;
            List<ExcelColumnTemplate<Map>> colList = new ArrayList<>();
            for (int col = 0; col < columnCount; col++) {
                String colIndex = "col" + (col + 1);
                // ExcelColumnTemplate 的构造参数，列名+索引
                colList.add(new ExcelColumnTemplate<>("列名" + colIndex, String.valueOf(col)));
            }

            // 总共3行
            int rowCount = 3;
            // 构建数据
            List<Map> dataList = new ArrayList<>();
            for (int rowNum = 0; rowNum < rowCount; rowNum++) {
                Map<String, String> item = new HashMap<>(columnCount);
                for (int col = 0; col < columnCount; col++) {
                    // 设置值， 索引+值内容
                    item.put(String.valueOf(col), "哈哈");
                }
                dataList.add(item);
            }

            new ExportExcel2<>(Map.class, "第一页", "导出列表", colList).setDataList(dataList).write(httpServletResponse, "导出测试.xlsx").dispose();
        } catch (IOException e) {
            logger.error("导出Excel出错", e);
        }

    }

}