单元格格式

单元格格式

import java.awt.Color;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
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.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.DefaultIndexedColorMap;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;

public class CellStyleUtil {

    //字体Calibri, 字号10
    public static Font Calibri10(Workbook workbook) {
        Font font = workbook.createFont();
        font.setFontName("Calibri");
        font.setFontHeightInPoints((short) 10);
        return font;
    }

    //字体Calibri, 字号10, 加粗
    public static Font Calibri10Bold(Workbook workbook) {
        Font font = Calibri10(workbook);
        font.setBold(true);
        return font;
    }

    //字体Calibri, 字号10, 白色
    public static Font whiteCalibri10(Workbook workbook) {
        Font font = Calibri10(workbook);
        font.setColor(IndexedColors.WHITE.getIndex());
        return font;
    }

    //字体Calibri, 字号10, 加粗，白色
    public static Font whiteCalibri10Bold(Workbook workbook) {
        Font font = Calibri10Bold(workbook);
        font.setColor(IndexedColors.WHITE.getIndex());
        return font;
    }

    //左对齐
    public static CellStyle textLeft(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(Calibri10(workbook));
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        return cellStyle;
    }
    
    //左对齐,自动换行
    public static CellStyle textLeftWrapText(Workbook workbook) {
        CellStyle cellStyle = textLeft(workbook); 
        cellStyle.setWrapText(true);
        return cellStyle;
    }
    
    // 左对齐,单元格边框
    public static CellStyle textLeftBorder(Workbook workbook) {
        CellStyle cellStyle = textLeft(workbook);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        return cellStyle;
    }

    // 自定义背景色black
    public static CellStyle textBlackLeft(Workbook workbook) {

        XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
        cellStyle.setFont(whiteCalibri10Bold(workbook));
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(new XSSFColor(new Color(13, 13, 13), new DefaultIndexedColorMap()));
        return cellStyle;
    }

    // 自定义背景色blue
    public static CellStyle textBlueLeft(Workbook workbook) {

        XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
        cellStyle.setFont(Calibri10(workbook));
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(new XSSFColor(new Color(221, 235, 247), new DefaultIndexedColorMap()));
        return cellStyle;
    }

    //自动换行, 自定义背景色
    public static CellStyle textBoldGreyWrpText(Workbook workbook) {

        XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
        cellStyle.setFont(Calibri10Bold(workbook));
        cellStyle.setWrapText(true);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(new XSSFColor(new Color(217, 217, 217), new DefaultIndexedColorMap()));
        return cellStyle;
    }
    
    //数字保留整数
    public static CellStyle numberRight(Workbook workbook) {

        DataFormat numberFormat = workbook.createDataFormat();
        XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
        cellStyle.setFont(Calibri10(workbook));
        cellStyle.setDataFormat(numberFormat.getFormat("#,##0"));
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        return cellStyle;

    }

    //数字保留整数, 背景色
    public static CellStyle numberBlackRight(Workbook workbook) {

        DataFormat numberFormat = workbook.createDataFormat();
        XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
        cellStyle.setFont(whiteCalibri10(workbook));
        cellStyle.setDataFormat(numberFormat.getFormat("#,##0"));
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(new XSSFColor(new Color(13, 13, 13), new DefaultIndexedColorMap()));
        return cellStyle;

    }

    //数字保留两位小数, 背景色
    public static CellStyle numberBoldScale2BlackRight(Workbook workbook) {

        DataFormat numberFormat = workbook.createDataFormat();
        XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
        cellStyle.setFont(whiteCalibri10Bold(workbook));
        cellStyle.setDataFormat(numberFormat.getFormat("#,##0.00"));
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(new XSSFColor(new Color(13, 13, 13), new DefaultIndexedColorMap()));
        return cellStyle;

    }


    //百分数
    public static CellStyle percentScale2Right(Workbook workbook) {

        DataFormat numberFormat = workbook.createDataFormat();
        CellStyle cellStyleNumber = workbook.createCellStyle();
        cellStyleNumber.setDataFormat(numberFormat.getFormat("0.0%"));
        cellStyleNumber.setFont(Calibri10(workbook));
        cellStyleNumber.setAlignment(HorizontalAlignment.RIGHT);
        return cellStyleNumber;
    }
    
    //日期格式
    public static CellStyle dateFont(Workbook workbook) {
        // time formatter
        DataFormat dateFormat = workbook.createDataFormat();
        CellStyle cellStyleDate = workbook.createCellStyle();
        cellStyleDate.setDataFormat(dateFormat.getFormat("yyyyMMdd"));
        cellStyleDate.setFont(Calibri10(workbook));
        return cellStyleDate;
    }


}