1. 引言

文件导出时支持设置单元格样式; 通过XSSFCellStyle 样式来封装单元格的背景颜色, 文本排版, 文本字体样式等. 示例如下:

        // 字体样式
        Font redFont = workBook.createFont();
        redFont.setColor(HSSFColor.DARK_RED.index); 
        redFont.setFontHeightInPoints((short) 30);
        redFont.setFontName("微软雅黑");
        redFont.setItalic(true);
        // 单元格样式
        XSSFCellStyle redStyle = workBook.createCellStyle();
        redStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        redStyle.setAlignment(HorizontalAlignment.RIGHT);
        redStyle.setFont(redFont);

2. 字体颜色设置

本身Font 接口提供了几个颜色常量, 同时提供了setColor 方法支持自定义颜色.

        Font redFont = workBook.createFont();
        redFont.setColor(Font.COLOR_RED);
        Font blueFont = workBook.createFont();
        blueFont.setColor(HSSFColor.DARK_BLUE.index);

2.1 Font.java

/**
 * Document: 
 * 		http://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Font.html
 */
package org.apache.poi.ss.usermodel;

public interface Font {

    /**
     * normal type of black color.
     */
    public final static short COLOR_NORMAL = 0x7fff;

    /**
     * Dark Red color
     */
    public final static short COLOR_RED = 0xa;

    /**
     * set the color for the font
     * @param color to use
     * @see #COLOR_NORMAL Note: Use this rather than HSSFColor.AUTOMATIC for default font color
     * @see #COLOR_RED
     */
    void setColor(short color);

    /**
     * get the color for the font
     * @return color to use
     * @see #COLOR_NORMAL
     * @see #COLOR_RED
     * @see org.apache.poi.hssf.usermodel.HSSFPalette#getColor(short)
     */
    short getColor();

    /**
     * get the index within the XSSFWorkbook (sequence within the collection of Font objects)
     * 
     * @return unique index number of the underlying record this Font represents (probably you don't care
     *  unless you're comparing which one is which)
     */
    public short getIndex();
    ...
}

2.2 HSSFColor.java

颜色0x 值有在HSSFColor 源码中封装一些常用的颜色. 使用方式直接调用颜色索引值即可. 结合setColor()

public class HSSFColor implements Color {

    private static Map<Integer,HSSFColor> indexHash;
    private static Map<HSSFColorPredefined,HSSFColor> enumList;

    private java.awt.Color color;
    private int index;
    private int index2;

    /**
     * Predefined HSSFColors with their given palette index (and an optional 2nd index)
     *
     * @since POI 3.16 beta 2
     */
    public enum HSSFColorPredefined {
        BLACK                (0x08,   -1, 0x000000),
        BROWN                (0x3C,   -1, 0x993300),
        OLIVE_GREEN          (0x3B,   -1, 0x333300),
        DARK_GREEN           (0x3A,   -1, 0x003300),
        DARK_TEAL            (0x38,   -1, 0x003366),
        DARK_BLUE            (0x12, 0x20, 0x000080),
        INDIGO               (0x3E,   -1, 0x333399),
        GREY_80_PERCENT      (0x3F,   -1, 0x333333),
        ORANGE               (0x35,   -1, 0xFF6600),
        DARK_YELLOW          (0x13,   -1, 0x808000),
        GREEN                (0x11,   -1, 0x008000),
        TEAL                 (0x15, 0x26, 0x008080),
        BLUE                 (0x0C, 0x27, 0x0000FF),
        BLUE_GREY            (0x36,   -1, 0x666699),
        GREY_50_PERCENT      (0x17,   -1, 0x808080),
        RED                  (0x0A,   -1, 0xFF0000),
        LIGHT_ORANGE         (0x34,   -1, 0xFF9900),
        LIME                 (0x32,   -1, 0x99CC00),
        SEA_GREEN            (0x39,   -1, 0x339966),
        AQUA                 (0x31,   -1, 0x33CCCC),
        LIGHT_BLUE           (0x30,   -1, 0x3366FF),
        VIOLET               (0x14, 0x24, 0x800080),
        GREY_40_PERCENT      (0x37,   -1, 0x969696),
        PINK                 (0x0E, 0x21, 0xFF00FF),
        GOLD                 (0x33,   -1, 0xFFCC00),
        YELLOW               (0x0D, 0x22, 0xFFFF00),
        BRIGHT_GREEN         (0x0B,   -1, 0x00FF00),
        TURQUOISE            (0x0F, 0x23, 0x00FFFF),
        DARK_RED             (0x10, 0x25, 0x800000),
        SKY_BLUE             (0x28,   -1, 0x00CCFF),
        PLUM                 (0x3D, 0x19, 0x993366),
        GREY_25_PERCENT      (0x16,   -1, 0xC0C0C0),
        ROSE                 (0x2D,   -1, 0xFF99CC),
        LIGHT_YELLOW         (0x2B,   -1, 0xFFFF99),
        LIGHT_GREEN          (0x2A,   -1, 0xCCFFCC),
        LIGHT_TURQUOISE      (0x29, 0x1B, 0xCCFFFF),
        PALE_BLUE            (0x2C,   -1, 0x99CCFF),
        LAVENDER             (0x2E,   -1, 0xCC99FF),
        WHITE                (0x09,   -1, 0xFFFFFF),
        CORNFLOWER_BLUE      (0x18,   -1, 0x9999FF),
        LEMON_CHIFFON        (0x1A,   -1, 0xFFFFCC),
        MAROON               (0x19,   -1, 0x7F0000),
        ORCHID               (0x1C,   -1, 0x660066),
        CORAL                (0x1D,   -1, 0xFF8080),
        ROYAL_BLUE           (0x1E,   -1, 0x0066CC),
        LIGHT_CORNFLOWER_BLUE(0x1F,   -1, 0xCCCCFF),
        TAN                  (0x2F,   -1, 0xFFCC99),

        /**
         * Special Default/Normal/Automatic color.<p>
         * <i>Note:</i> This class is NOT in the default Map returned by HSSFColor.
         * The index is a special case which is interpreted in the various setXXXColor calls.
         */
        AUTOMATIC            (0x40,   -1, 0x000000);

        private HSSFColor color;

        HSSFColorPredefined(int index, int index2, int rgb) {
            this.color = new HSSFColor(index, index2, new java.awt.Color(rgb));
        }

        /**
         * @see HSSFColor#getIndex()
         */
        public short getIndex() {
            return color.getIndex();
        }

        /**
         * @see HSSFColor#getIndex2()
         */
        public short getIndex2() {
            return color.getIndex2();
        }

        /**
         * @see HSSFColor#getTriplet()
         */
        public short [] getTriplet() {
            return color.getTriplet();
        }

        /**
         * @see HSSFColor#getHexString()
         */
        public String getHexString() {
            return color.getHexString();
        }

        /**
         * @return (a copy of) the HSSFColor assigned to the enum
         */
        public HSSFColor getColor() {
            return new HSSFColor(getIndex(), getIndex2(), color.color);
        }
    }
...
}

3. 样例演示

3.1 前端报表

3.2 文件导出

3.3 后端代码

关键导出方法

    /**
     * 写入sheet页-适用2007及以上
     *      API > http://poi.apache.org/apidocs/dev/org/apache/poi/ss/util/CellRangeAddress.html
     * @param workBook    工作簿
     * @param sheetNum    sheet页个数
     * @param sheetSize   每个sheet页行数
     * @param headerDataList  表头数据
     * @param data        数据
     * @param headerCellRangeAddressList 标题合并
     * @param exportSource  业务来源
     */
    public static void writeSheetDataFor2007(XSSFWorkbook workBook, Integer sheetNum, Integer sheetSize,
                                             List<LinkedHashMap<String, Object>> headerDataList, List<LinkedHashMap<String, Object>> data,
                                             List<CellRangeAddress> headerCellRangeAddressList,
                                             Integer exportSource) {

        // start 样式开始
        // 公用垂直居中样式\公用垂直靠左样式\公用垂直靠右样式
        XSSFCellStyle centerStyle = workBook.createCellStyle();
        centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        centerStyle.setAlignment(HorizontalAlignment.CENTER);
        XSSFCellStyle leftStyle = workBook.createCellStyle();
        leftStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        leftStyle.setAlignment(HorizontalAlignment.LEFT);
        XSSFCellStyle rightStyle = workBook.createCellStyle();
        rightStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        rightStyle.setAlignment(HorizontalAlignment.RIGHT);

        // 粗体居中
        XSSFCellStyle blodStyle = workBook.createCellStyle();
        blodStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        blodStyle.setAlignment(HorizontalAlignment.CENTER);
        Font blodFont = workBook.createFont();
        blodFont.setBold(true);
        blodStyle.setFont(blodFont);

        // 红色字体居右\蓝色字体居右
        XSSFCellStyle redStyle = workBook.createCellStyle();
        Font redFont = workBook.createFont();
        redFont.setColor(HSSFColor.DARK_RED.index);
        redStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        redStyle.setAlignment(HorizontalAlignment.RIGHT);
        redStyle.setFont(redFont);

        XSSFCellStyle blueStyle = workBook.createCellStyle();
        Font blueFont = workBook.createFont();
        blueFont.setColor(HSSFColor.DARK_BLUE.index);
        blueStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        blueStyle.setAlignment(HorizontalAlignment.RIGHT);
        blueStyle.setFont(blueFont);
        // end 样式结束

        // 封装单元格样式集合, 包括颜色, 排版
        List<XSSFCellStyle> cellStyleList = new ArrayList<>();
        LinkedHashMap<String, Object> headerData = headerDataList.get(0);

        for (int i = 0; i < sheetNum; i++) {
            // 创建新的sheet 页
            XSSFSheet sheet = workBook.createSheet();
            sheet.setDefaultColumnWidth((short) 15);

            //写入标题行, 标题行数
            int headerDataListSize = headerDataList.size();
            cellStyleList.clear();
            for (int n = 0; n < headerDataList.get(0).size(); n++ ) {
                // 标题行样式
                cellStyleList.add(blodStyle);
            }
            writeRowDataFor2007(sheet, headerData, headerDataList, workBook, 0, 0, cellStyleList);

            // 写入行数据
            // 自定义单元格样式 for (int n = 0; n < headerDataList.get(0).size(); n++ )
            if (!CollectionUtils.isEmpty(data)) {
                cellStyleList.clear();

                // 首列居中, 其他列红色居右// 首列居中, 其他列蓝色居右// 首列居中, 其他列黑色居右
                List<XSSFCellStyle> redStyleList = new ArrayList<>();
                List<XSSFCellStyle> blueStyleList = new ArrayList<>();
                List<XSSFCellStyle> blackStyleList = new ArrayList<>();
                redStyleList.add(centerStyle);
                blueStyleList.add(centerStyle);
                blackStyleList.add(centerStyle);
                // 前两列居中, 其他列红色居右// 前两列居中, 其他列蓝色居右// 前两列居中, 其他列黑色居右
                List<XSSFCellStyle> redRowStyleList = new ArrayList<>();
                List<XSSFCellStyle> blueRowStyleList = new ArrayList<>();
                List<XSSFCellStyle> blackRowStyleList = new ArrayList<>();
                redRowStyleList.addAll(Arrays.asList(centerStyle, centerStyle));
                blueRowStyleList.addAll(Arrays.asList(centerStyle, centerStyle));
                blackRowStyleList.addAll(Arrays.asList(centerStyle, centerStyle));
                for (int n = 1; n < headerDataList.get(0).size(); n++ ) {
                    // 标题行样式
                    redStyleList.add(redStyle);
                    redRowStyleList.add(redStyle);
                    blueStyleList.add(blueStyle);
                    blueRowStyleList.add(blueStyle);
                    blackStyleList.add(rightStyle);
                    blackRowStyleList.add(rightStyle);
                }

                //当前页总行数
                Integer totalRowNum = data.size() - (i + headerDataListSize) * sheetSize >= 0 ? sheetSize : data.size() - sheetSize * i;

                /*创建新的单元格范围。索引从零开始。
                firstRow - 第一行索引
                lastRow - 最后一行（包括）的索引必须等于或大于 firstRow
                firstCol - 第一列索引
                lastCol - 最后一列（包括）的索引必须等于或大于 firstCol*/
                for (int j = 0; j < totalRowNum; j++) {
                    switch ((j + 1) % 3) {
                        case 0:
                            // j + 1 是考虑表头行数
                            // 下标0-n, 从1开始, 每3 个合并
                            sheet.addMergedRegion(new CellRangeAddress(j-1,j+1,0,0));
                            sheet.addMergedRegion(new CellRangeAddress(j-1,j+1,1,1));
                            writeRowDataFor2007(sheet, headerData, data, workBook, j + headerDataListSize, i * sheetSize + j, blueRowStyleList);
                            break;
                        case 1:
                            writeRowDataFor2007(sheet, headerData, data, workBook, j + headerDataListSize, i * sheetSize + j, blackRowStyleList);
                            break;
                        case 2:
                            writeRowDataFor2007(sheet, headerData, data, workBook, j + headerDataListSize, i * sheetSize + j, redRowStyleList);
                            break;
                        default:
                            break;
                    }
                }
            }
        }
    }

    /**
     * 写入行数据-适用2007及以上
     *
     * @param sheet      当前sheet页对象
     * @param titleMap   标题数据
     *                   titleMap映射data, 来使行数据的列对应标题列
     * @param data       数据
     * @param rowNum     行号
     * @param startIndex 数据项指标项
     * @param cellStyleList     每行的单元格样式集合
     */
    public static void writeRowDataFor2007(XSSFSheet sheet, LinkedHashMap titleMap, List<LinkedHashMap<String, Object>> data, XSSFWorkbook workBook, Integer rowNum, Integer startIndex, List<XSSFCellStyle> cellStyleList) {

        // 创建Excel 行
        XSSFRow rowTemp = sheet.createRow(rowNum);
        Map<String, Object> rowData = data.get(startIndex);
        if (rowData != null && !rowData.isEmpty()) {
            Iterator it = titleMap.keySet().iterator();
            int indexIt = 0;
            while (it.hasNext()) {
                // 创建单元格
                XSSFCell dataCell = rowTemp.createCell(indexIt);
                dataCell.setCellValue(String.valueOf(rowData.get(it.next())));
                // 设置单元格样式
                dataCell.setCellStyle(cellStyleList.get(indexIt));
                indexIt++;
            }
        }
    }

Power By niaonao