Java 类org.apache.poi.ss.usermodel.CellStyle 实例源码

/**
 * Returns the default data cell style. Obtained from:
 * http://svn.apache.org/repos/asf/poi
 * /trunk/src/examples/src/org/apache/poi/ss/examples/TimesheetDemo.java
 *
 * @param wb the wb
 * @return the cell style
 */
protected CellStyle defaultDataCellStyle(final Workbook wb) {
    CellStyle style;
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setWrapText(true);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(BorderStyle.THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setDataFormat(doubleDataFormat);
    return style;
}

private void makeHeader ( final List<Field> columns, final HSSFSheet sheet )
{
    final Font font = sheet.getWorkbook ().createFont ();
    font.setFontName ( "Arial" );
    font.setBoldweight ( Font.BOLDWEIGHT_BOLD );
    font.setColor ( HSSFColor.WHITE.index );

    final CellStyle style = sheet.getWorkbook ().createCellStyle ();
    style.setFont ( font );
    style.setFillForegroundColor ( HSSFColor.BLACK.index );
    style.setFillPattern ( PatternFormatting.SOLID_FOREGROUND );

    final HSSFRow row = sheet.createRow ( 0 );

    for ( int i = 0; i < columns.size (); i++ )
    {
        final Field field = columns.get ( i );

        final HSSFCell cell = row.createCell ( i );
        cell.setCellValue ( field.getHeader () );
        cell.setCellStyle ( style );
    }
}

/** 创建每个CellStyle，并放到map之中
 * @param workbook
 * @param styleInfo
 * @param dataFormat 
 * @return
 */
public static CellStyle doCreateCellStyle(Workbook workbook,ExportCellStyleInfo styleInfo,String dataFormat) {
    if(styleInfo != null ){
        CellStyle cellStyle = workbook.createCellStyle();
        setStyleValue(ExportCellStyleInfo.class, cellStyle, styleInfo);
        if(styleInfo.getFontStyleInfo() != null){
            Font fontStyle = workbook.createFont();
            setStyleValue(ExportFontStyleInfo.class, fontStyle, styleInfo.getFontStyleInfo());
            cellStyle.setFont(fontStyle);
        }
        if(!StringUtils.isEmpty(dataFormat)){
            short format = workbook.createDataFormat().getFormat(dataFormat);
            cellStyle.setDataFormat(format);
        }
        return cellStyle;
    }
    return null;
}

public void setCellStyleFont(Workbook workbook, CellStyle style, int i) {
    Font font = workbook.createFont();
    if (i == 0) {
        // 正常
    } else if (i == 4) {
        // 下划线
        font.setUnderline(Font.U_SINGLE);
        style.setFont(font);
    } else if (i == 2) {
        // 倾斜
        font.setItalic(true);
        style.setFont(font);
    } else if (i == 1) {
        // 加粗
        font.setBold(true);
        style.setFont(font);
    }
}

/** 创建不需要合并单元格的表头
 * @param fieldInfoMap
 * @param headCellStyleMap
 * @param row
 * @param fieldList
 * @param cellNum
 */
private static void doCreateSheetSingleHeadRow(Map<Field, ExportFieldInfo> fieldInfoMap,
        Map<Field, CellStyle> headCellStyleMap, Row row, List<SortableField> fieldList, int cellNum) {

    Cell cell;
    String value;
    for(int i = 0 ; i < fieldList.size() ; i++ , cellNum++){
        Field field = fieldList.get(i).getField();
        value = fieldInfoMap.get(field).getHeadName();
        cell = row.createCell(cellNum);
        cell.setCellValue(value);
        if( headCellStyleMap != null && headCellStyleMap.get(field) != null ){
            cell.setCellStyle(headCellStyleMap.get(field));
        }
    }
}

public CellStyle createIndentationCellStyle(Workbook workbook, int s) {
    CellStyle dataStyle1 = this.createBorderCellStyle(workbook, true);
    Font dataFont = workbook.createFont();
    dataFont.setColor((short) 12);
    dataFont.setFontHeightInPoints((short) 10);
    dataStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    dataStyle1.setFillForegroundColor((short) 11);
    dataStyle1.setFont(dataFont);
    dataStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
    dataStyle1.setAlignment(HorizontalAlignment.LEFT);
    dataStyle1.setIndention(Short.valueOf(String.valueOf((s))));
    return dataStyle1;
}

/**
 * 获取单元格式样式
 *
 * @param wb
 * @param color
 * @return
 */
private static HSSFCellStyle getCellStyle(HSSFWorkbook wb, int color) {
    if (STYLE_MAP.get(color) != null) {
        return STYLE_MAP.get(color);
    }
    HSSFCellStyle style = wb.createCellStyle();
    if (color != -1) {
        style.setFillForegroundColor(Short.valueOf(color + ""));
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setAlignment(CellStyle.ALIGN_CENTER);
    }
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(HSSFColor.BLACK.index);
    style.setLeftBorderColor(HSSFColor.BLACK.index);
    style.setRightBorderColor(HSSFColor.BLACK.index);
    style.setTopBorderColor(HSSFColor.BLACK.index);
    STYLE_MAP.put(color, style);
    return style;
}

private void formatCellDate(Sheet sheet, Cell cell, String format) {
    CellStyle style = wb.createCellStyle();
    CreationHelper createHelper = wb.getCreationHelper();
    style.setDataFormat(createHelper.createDataFormat().getFormat(format));
    cell.setCellStyle(style);

}

/**
 * 合并行
 */
//TODO 暂时支持两行表头
private void mergeRows(Sheet sheet, CellStyle cellStyle, ExcelMeta excelMeta) {

    Row row = null;
    Cell cell = null;
    String[] lastRowVals = new String[excelMeta.getTitleColumnNum()];
    for (int r = 0; r < excelMeta.getTitleRowNum(); r++) {
        for (int c = 0; c < excelMeta.getTitleColumnNum(); c++) {
            row = sheet.getRow(r);
            cell = row.getCell(c);
            if (r == 0) {
                lastRowVals[c] = cell.getStringCellValue();
            } else {
                if (StringUtils.equals(lastRowVals[c], cell.getStringCellValue())) {
                    cell.setCellValue("");
                    sheet.addMergedRegion(new CellRangeAddress(0, r, c, c));
                    Cell nowCell = sheet.getRow(0).getCell(c);
                    nowCell.setCellStyle(cellStyle);
                }
            }

        }
    }

}

/**
 * Returns the default title style. Obtained from:
 * http://svn.apache.org/repos/asf/poi
 * /trunk/src/examples/src/org/apache/poi/ss/examples/TimesheetDemo.java
 *
 * @param wb the wb
 * @return the cell style
 */
protected CellStyle defaultTitleCellStyle(final Workbook wb) {
    CellStyle style;
    final Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFont(titleFont);
    return style;
}

/**
 * Returns the default header style. Obtained from:
 * http://svn.apache.org/repos/asf/poi
 * /trunk/src/examples/src/org/apache/poi/ss/examples/TimesheetDemo.java
 *
 * @param wb the wb
 * @return the cell style
 */
protected CellStyle defaultHeaderCellStyle(final Workbook wb) {
    CellStyle style;
    final Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 11);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setFont(monthFont);
    style.setWrapText(true);
    return style;
}

/**
 * 获取模板指定位置上的已转为本工作薄的单元格样式
 * 
 * 目前看，只用于2003的版本(*.xls)，2007的版本是可以直接 setCellStyle() 方法设置字体的。
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-03-18
 * @version     v1.0
 *
 * @param i_RTemplate  模板对象
 * @param i_IDX        单元格样式在模板中的索引位置
 * @return
 */
public synchronized CellStyle getCellStyle(RTemplate i_RTemplate ,int i_IDX)
{
    CellStyle v_DataCellStyle = this.cellStyles.getRow(i_RTemplate ,String.valueOf(i_IDX));

    if ( v_DataCellStyle == null )
    {
        v_DataCellStyle = this.workbook.createCellStyle();

        CellStyle v_TemplateCellStyle = i_RTemplate.getTemplateSheet().getWorkbook().getCellStyleAt(i_IDX);
        ExcelHelp.copyCellStyle(v_TemplateCellStyle ,v_DataCellStyle);

        v_DataCellStyle.setFont(this.getFont(i_RTemplate ,v_TemplateCellStyle.getFontIndex()));

        this.cellStyles.putRow(i_RTemplate ,String.valueOf(i_IDX) ,v_DataCellStyle);
    }

    return v_DataCellStyle;
}

/**
 * 获取合并单元格式
 *
 * @param sheet
 * @param row
 * @param columnFrom
 * @param columnTo
 * @return
 */
private static void setRegionBorder(HSSFSheet sheet, int row, int columnFrom, int columnTo) {
    CellRangeAddress region = new CellRangeAddress(row, row, columnFrom, columnTo);
    sheet.addMergedRegion(region);
    final short border = CellStyle.BORDER_THIN;
    HSSFWorkbook wb = sheet.getWorkbook();
    RegionUtil.setBorderBottom(border, region, sheet, wb);
    RegionUtil.setBorderTop(border, region, sheet, wb);
    RegionUtil.setBorderLeft(border, region, sheet, wb);
    RegionUtil.setBorderRight(border, region, sheet, wb);
    RegionUtil.setBottomBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
    RegionUtil.setTopBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
    RegionUtil.setLeftBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
    RegionUtil.setRightBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
}

/**
 * 创建一个新的样式，样式从i_DataCell中克隆出来。
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-09-11
 * @version     v1.0
 *
 * @param i_ID         标记ID。由调用者设定
 * @param i_DataCell   被克隆的单元格样式
 * @return
 */
public synchronized CellStyle getCellStyleByCopy(String i_ID ,Cell i_DataCell ,RTemplate i_RTemplate)
{
    Short     v_CellStyleID = this.cellStylesByCopy.get(i_ID);
    CellStyle v_CellStyle   = null;

    if ( v_CellStyleID == null )
    {
        v_CellStyle = this.workbook.createCellStyle();

        ExcelHelp.copyCellStyle(i_DataCell.getCellStyle(), v_CellStyle);

        // 2017-09-20 动态格式的ID均加 Short.MAX_VALUE
        this.cellStyles.putRow(i_RTemplate ,String.valueOf(Short.MAX_VALUE + v_CellStyle.getIndex()) ,v_CellStyle);
        this.cellStylesByCopy.put(i_ID ,v_CellStyle.getIndex());
    }
    else
    {
        v_CellStyle = this.getCellStyle(i_RTemplate ,Short.MAX_VALUE + v_CellStyleID);
    }

    return v_CellStyle;
}

/**
 * 小数格式
 *
 * @return CellStyle
 */
public CellStyle getDecimalStyle() {
    if (buildInStyleMap.containsKey(DECIMAL_STYLE_KEY)) {
        return buildInStyleMap.get(DECIMAL_STYLE_KEY);
    }

    CellStyle decimalStyle = workbook.createCellStyle();//小数样式
    if (!buildInFormatMap.containsKey(DATA_FORMAT_KEY)) {
        DataFormat dataFormat = workbook.createDataFormat();
        buildInFormatMap.put(DATA_FORMAT_KEY, dataFormat);
    }
    decimalStyle.setDataFormat(buildInFormatMap.get(DATA_FORMAT_KEY).getFormat("0.00"));
    this.setCommonStyle(decimalStyle);
    buildInStyleMap.put(DECIMAL_STYLE_KEY, decimalStyle);
    return decimalStyle;
}

/**
 * 日期样式 yyyy/MM/dd
 *
 * @return CellStyle
 */
public CellStyle getDate8Style() {

    if (buildInStyleMap.containsKey(DATE_8_STYLE_KEY)) {
        return buildInStyleMap.get(DATE_8_STYLE_KEY);
    }

    CellStyle date8Style = workbook.createCellStyle();//年月日样式
    if (!buildInFormatMap.containsKey(DATA_FORMAT_KEY)) {
        DataFormat dataFormat = workbook.createDataFormat();
        buildInFormatMap.put(DATA_FORMAT_KEY, dataFormat);
    }
    date8Style.setDataFormat(buildInFormatMap.get(DATA_FORMAT_KEY).getFormat("yyyy/MM/dd"));
    this.setCommonStyle(date8Style);
    buildInStyleMap.put(DATE_8_STYLE_KEY, date8Style);
    return date8Style;
}

/**
 * 根据格式，创建返回样式对象
 *
 * @param format 格式
 * @return 样式对象
 */
public CellStyle getCustomFormatStyle(String format) {

    //存在对应格式直接返回
    if (customFormatStyleMap.containsKey(format)) {
        return customFormatStyleMap.get(format);
    }
    CellStyle customDateStyle = workbook.createCellStyle();
    if (!buildInFormatMap.containsKey(DATA_FORMAT_KEY)) {
        DataFormat dataFormat = workbook.createDataFormat();
        buildInFormatMap.put(DATA_FORMAT_KEY, dataFormat);
    }
    customDateStyle.setDataFormat(buildInFormatMap.get(DATA_FORMAT_KEY).getFormat(format));
    this.setCommonStyle(customDateStyle);
    //放入map缓存
    customFormatStyleMap.put(format, customDateStyle);

    return customDateStyle;
}

/**
 * 设置通用的对齐居中、边框等
 *
 * @param style 样式
 */
private void setCommonStyle(CellStyle style) {
    // 设置单元格居中对齐、自动换行
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setWrapText(true);

    //设置单元格字体
    if (!buildInFontMap.containsKey(FONT_KEY)) {
        Font font = workbook.createFont();
        //通用字体
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        buildInFontMap.put(FONT_KEY, font);
    }
    style.setFont(buildInFontMap.get(FONT_KEY));

    // 设置单元格边框为细线条
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setBorderTop(CellStyle.BORDER_THIN);
}

public CellStyle builderValueCellStyle(ReportForm reportFormModel, Workbook workbook) {
    List<ReportFormData> list = reportFormModel.getListReportFormDataModel();
    ReportFormData reportFormDataModel;
    if (list.size() > 0) {
        reportFormDataModel = list.get(0);
        int dataAlign = reportFormDataModel.getDataAlign();
        int dataStyle = reportFormDataModel.getDataStyle();
        CellStyle valueStyle = createBorderCellStyle(workbook, reportFormModel.isShowBorder());
        setCellStyleAligment(valueStyle, dataAlign);
        valueStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        this.setCellStyleFont(workbook, valueStyle, dataStyle);
        return valueStyle;
    }
    return null;

}

/** 实际创建CellStyle Map
 * @param workbook
 * @param exportInfo
 * @param isHead
 * @return
 */
private static Map<Field, CellStyle> doCreateStyleMap(Workbook workbook, ExportInfo exportInfo,boolean isHead) {
    Map<ExportCellStyleInfo, CellStyle> tempCacheMap = new HashMap<ExportCellStyleInfo, CellStyle>();
    Map<Field, CellStyle> styleMap = new HashMap<Field, CellStyle>();
    CellStyle style;
    for(Map.Entry<Field,ExportFieldInfo> entry : exportInfo.getFieldInfoMap().entrySet()){
        ExportCellStyleInfo styleInfo ;
        if(isHead){
            styleInfo = entry.getValue().getHeadStyle();
        }else{
            styleInfo = entry.getValue().getDataStyle();
        }
        if(!StringUtils.isEmpty(entry.getValue().getDataFormat())){
            //当存在格式化时，即使是来自通用的样式，但是格式不一样，所以需要new专属格式的样式
            //由于格式化属于专属，因此也不需要放到临时缓存map之中
            style = doCreateCellStyle(workbook,styleInfo,entry.getValue().getDataFormat());         
        }else{
            style = tempCacheMap.get(styleInfo);
            if(style == null){
                style = doCreateCellStyle(workbook,styleInfo,null);
                tempCacheMap.put(styleInfo, style);
            }
        }
        if(style != null ){
            styleMap.put(entry.getKey(),style);                     
        }               
    }
    tempCacheMap.clear();
    return styleMap.isEmpty() ? null : styleMap;
}

private static <T> void createSheetDataRow(List<T> dataList, int dataRowNum,
        int listStart, int listEnd,ExportInfo exportInfo,
        Map<Field, CellStyle> dataCellStyleMap, List<Field> availableFields,Sheet sheet)
        throws IllegalAccessException, InvocationTargetException {

    Map<Field, ExportFieldInfo> fieldInfoMap = exportInfo.getFieldInfoMap();
    Cell cell;
    Field field;
    Row row;
    T obj ;
    int dataSize = dataList.size() ;
    listEnd = listEnd > dataSize ? dataSize : listEnd ;
    int fieldListSize = availableFields.size();
    int dataHightInPoint = exportInfo.getDataHightInPoint();
    for(int i = listStart ; i < listEnd ; i++ , dataRowNum ++){
        obj = dataList.get(i);
        row = sheet.createRow(dataRowNum ); 
        row.setHeightInPoints(dataHightInPoint);
        for(int j = 0 ; j < fieldListSize ; j++){
            field = availableFields.get(j);
            if(field == null ){
                continue;
            }
            cell= row.createCell(j);
            Object returnVal = obj;
            List<Method> methods = fieldInfoMap.get(field).getMethodChain();
            for(Method method : methods){
                if (returnVal == null) {
                    continue;
                }
                returnVal = method.invoke(returnVal);
            }

            setCellValue(cell, fieldInfoMap.get(field),  returnVal, obj);
            if(dataCellStyleMap != null && dataCellStyleMap.get(field) != null){
                cell.setCellStyle(dataCellStyleMap.get(field));                 
            }
        }
    }
}

public static CellStyle createHeaderStyle(Workbook workbook) {
    XSSFCellStyle headerStyle = createCellStyle(workbook);
    XSSFColor header = new XSSFColor(new byte[]{(byte) 79, (byte) 129, (byte) 189});
    headerStyle.setFillForegroundColor(header);
    headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    headerStyle.getFont().setColor(IndexedColors.WHITE.index);
    return headerStyle;
}

private int buildGridExcelHeader(ReportGrid gridModel, Sheet sheet, int starHeaderRow, Map<String, CellStyle> styles) {
    Map<Integer, Object> rowMap = new HashMap<Integer, Object>();
    this.calculateMaxHeaderLevel(gridModel, gridModel.getGridHeaderModelList());
    int maxHeaderLevel = gridModel.getMaxHeaderLevel();
    for (int i = 0; i < maxHeaderLevel; i++) {
        Row row = sheet.createRow((short) i + starHeaderRow);
        rowMap.put(i + starHeaderRow, row);
    }
    List<ReportGridHeader> topHeaders = new ArrayList<ReportGridHeader>();
    calculateGridHeadersByLevel(gridModel.getGridHeaderModelList(), 1, topHeaders);
    this.buildGridExcelHeader(sheet, rowMap, maxHeaderLevel, 1, starHeaderRow, 0, topHeaders, styles);
    return starHeaderRow + maxHeaderLevel;

}

/**
 * @param column
 * @param line
 * @param value
 * @param style
 */
private void writeValue(String column, int line, String value, CellStyle style) {
    logger.debug("Writing: [{}] at line [{}] in column [{}]", value, line, column);
    final int colIndex = columns.indexOf(column);
    final Sheet sheet = workbook.getSheetAt(0);
    final Row row = sheet.getRow(line);
    Cell cell = row.getCell(colIndex);
    if (cell != null) {
        row.removeCell(cell);
    }
    cell = row.createCell(colIndex);
    cell.setCellStyle(style);
    cell.setCellValue(value);
    saveOpenExcelFile();
}

@Override
protected void buildExcelDocument(Map<String, Object> model,
        HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response)
        throws Exception {
    // get data model which is passed by the Spring container
    List<RatingCountBean> listOfRating = (List<RatingCountBean>) model.get("listOfRatingCount");

    // create a new Excel sheet
    HSSFSheet sheet = workbook.createSheet("Feedback Report");
    sheet.setDefaultColumnWidth(30);

    // create style for header cells
    CellStyle style = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setFontName("Arial");
    style.setFillForegroundColor(HSSFColor.BLUE.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.WHITE.index);
    style.setFont(font);

    // create header row
    HSSFRow header = sheet.createRow(0);


               header.createCell(0).setCellValue("Question Number");
    header.getCell(0).setCellStyle(style);

    header.createCell(1).setCellValue("Question");
    header.getCell(1).setCellStyle(style);

    header.createCell(2).setCellValue("Very Poor");
    header.getCell(2).setCellStyle(style);

    header.createCell(3).setCellValue("Poor");
    header.getCell(3).setCellStyle(style);

    header.createCell(4).setCellValue("Good");
    header.getCell(4).setCellStyle(style);

    header.createCell(5).setCellValue("Best");
    header.getCell(5).setCellStyle(style);

               header.createCell(6).setCellValue("Excellent");
    header.getCell(6).setCellStyle(style);

    // create data rows
    int rowCount = 1;

    for (RatingCountBean rating : listOfRating) {
        HSSFRow aRow = sheet.createRow(rowCount++);
        aRow.createCell(0).setCellValue(rating.getQuestionId());
        aRow.createCell(1).setCellValue(rating.getQuestionText());
        aRow.createCell(2).setCellValue(rating.getRating_one_total_count());
        aRow.createCell(3).setCellValue(rating.getRating_two_total_count());
        aRow.createCell(4).setCellValue(rating.getRating_three_total_count());
                       aRow.createCell(5).setCellValue(rating.getRating_four_total_count());
                       aRow.createCell(6).setCellValue(rating.getRating_five_total_count());
    }
}

/**
 * 添加一个单元格
 * @param row 添加的行
 * @param column 添加列号
 * @param val 添加值
 * @param align 对齐方式（1：靠左；2：居中；3：靠右）
 * @return 单元格对象
 */
public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){
    Cell cell = row.createCell(column);
    CellStyle style = styles.get("data"+(align>=1&&align<=3?align:""));
    try {
        if (val == null){
            cell.setCellValue("");
        } else if (val instanceof String) {
            cell.setCellValue((String) val);
        } else if (val instanceof Integer) {
            cell.setCellValue((Integer) val);
        } else if (val instanceof Long) {
            cell.setCellValue((Long) val);
        } else if (val instanceof Double) {
            cell.setCellValue((Double) val);
        } else if (val instanceof Float) {
            cell.setCellValue((Float) val);
        } else if (val instanceof Date) {
            DataFormat format = wb.createDataFormat();
            style.setDataFormat(format.getFormat("yyyy-MM-dd"));
            cell.setCellValue((Date) val);
        } else {
            if (fieldType != Class.class){
                cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val));
            }else{
                cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
                        "fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val));
            }
        }
    } catch (Exception ex) {
        log.info("Set cell value ["+row.getRowNum()+","+column+"] error: " + ex.toString());
        cell.setCellValue(val.toString());
    }
    cell.setCellStyle(style);
    return cell;
}

private static List<String> createFirstRow(String sheetName,
        List<Locale> locales, Sheet sheet, CellStyle styleTitle) {
    int colIdx = 0;
    Row titleRow = sheet.createRow(0);
    sheet.setColumnWidth(colIdx, 30 * 256);
    Cell titleCell = titleRow.createCell(colIdx++);
    titleCell.setCellStyle(styleTitle);
    titleCell.setCellValue(getDefaultResourceBundle().getString(
            BaseBean.LABEL_SHOP_TRANSLARIONS_KEY));
    return createColumnHeaders(sheetName, locales, sheet, styleTitle,
            colIdx, titleRow);
}

private static List<String> createColumnHeaders(String sheetName,
        List<Locale> locales, Sheet sheet, CellStyle styleTitle,
        int colIdx, Row titleRow) {
    Cell titleCell;
    List<String> localeList = new ArrayList<String>();
    int localesSize = locales.size();
    String cellValue = null;
    for (int i = 0; i < localesSize; i++) {
        sheet.setColumnWidth(colIdx, 40 * 256);
        titleCell = titleRow.createCell(colIdx++);
        titleCell.setCellStyle(styleTitle);
        if (i < 3
                && !BaseBean.LABEL_SHOP_TRANSLARIONS.equals(sheetName)
                && StandardLanguage.isStandardLanguage(locales.get(i)
                        .getLanguage())) {
            cellValue = locales.get(i).getLanguage()
                    + StandardLanguage.COLUMN_HEADING_SUFFIX;
            titleCell.setCellValue(cellValue);
            localeList.add(cellValue);

        } else {
            cellValue = locales.get(i).getLanguage();
            titleCell.setCellValue(cellValue);
            localeList.add(cellValue);
        }
    }
    if (locales.size() == 3
            && !BaseBean.LABEL_SHOP_TRANSLARIONS.equals(sheetName)) {
        sheet.setColumnWidth(colIdx, 40 * 256);
        titleCell = titleRow.createCell(colIdx++);
        titleCell.setCellStyle(styleTitle);
        titleCell.setCellValue(ADDLANGUAGE);
    }

    return localeList;
}

private static CellStyle initializeSheet(Workbook wb, Sheet sheet) {
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    CellStyle styleTitle;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 12);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    titleFont.setFontName("Arial");
    styleTitle = wb.createCellStyle();
    styleTitle.setFont(titleFont);
    return styleTitle;
}

/**
 * Creates an excel sheet in the provided workbook using provided parameters.
 * 
 * @param input the data to put in the sheet-
 * @param sheetName the name to user for the sheet.
 * @param wb the workbook to create the sheet in.
 */
private void createSheet(   List<MessageResourceEntry> input,
                            String sheetName,
                            Workbook wb)
{
    // create a new sheet
    String name = StringUtils.isBlank(sheetName) ? this.defaultSheetName : sheetName;
    LOG.info("Create sheet with name " + name);
    Sheet sheet = wb.createSheet(name);
    sheet.setZoom(this.zoom, 100);

    Map<Locale, Integer> langs = getLanguageInformation(input);
    createHeader(sheet, langs);

    CellStyle keyStyle = sheet.getWorkbook().createCellStyle();
    keyStyle.setAlignment(CellStyle.ALIGN_LEFT);
    keyStyle.setBorderBottom(CellStyle.BORDER_THIN);
    keyStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    Font f = sheet.getWorkbook().createFont();
    f.setBoldweight(Font.BOLDWEIGHT_NORMAL);
    keyStyle.setFont(f);

    CellStyle valueStyle = sheet.getWorkbook().createCellStyle();
    valueStyle.setAlignment(CellStyle.ALIGN_LEFT);
    valueStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    valueStyle.setBorderBottom(CellStyle.BORDER_THIN);
    valueStyle.setBorderRight(CellStyle.BORDER_THIN);
    valueStyle.setBorderTop(CellStyle.BORDER_THIN);
    valueStyle.setBorderLeft(CellStyle.BORDER_THIN);
    valueStyle.setFont(f);
    valueStyle.setWrapText(true);

    CellStyle emptyStyle = sheet.getWorkbook().createCellStyle();
    emptyStyle.setAlignment(CellStyle.ALIGN_LEFT);
    emptyStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    emptyStyle.setBorderBottom(CellStyle.BORDER_THIN);
    emptyStyle.setBorderRight(CellStyle.BORDER_THIN);
    emptyStyle.setBorderTop(CellStyle.BORDER_THIN);
    emptyStyle.setBorderLeft(CellStyle.BORDER_THIN);
    emptyStyle.setFont(f);
    emptyStyle.setFillForegroundColor(IndexedColors.LAVENDER.getIndex());
    emptyStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    emptyStyle.setWrapText(true);

    LOG.info("Write data to sheet " + name);
    int rowIndex = this.languageHeaderRow + 1;
    for (MessageResourceEntry entry : input)
    {
        Row row = sheet.createRow(rowIndex);
        createContentRow(entry, row, langs, keyStyle, valueStyle, emptyStyle);
        rowIndex++;
    }
    sizeColumns(sheet, langs);
    sheet.createFreezePane(this.firstLanguageColumn, this.languageHeaderRow + 1, this.firstLanguageColumn, this.languageHeaderRow + 1);
}

/**
 * Creates the header row for the sheet provided.
 * 
 * @param sheet the sheet to create the header for
 * @param langs the languages to use in the header
 */
private void createHeader(  Sheet sheet,
                            Map<Locale, Integer> langs)
{
    LOG.info("Create header row with languages " + langs.toString());
    CellStyle key = sheet.getWorkbook().createCellStyle();
    key.setAlignment(CellStyle.ALIGN_CENTER);
    key.setBorderBottom(CellStyle.BORDER_MEDIUM);
    key.setBorderRight(CellStyle.BORDER_MEDIUM);
    Font f = sheet.getWorkbook().createFont();
    f.setBoldweight(Font.BOLDWEIGHT_BOLD);
    key.setFont(f);

    CellStyle hlang = sheet.getWorkbook().createCellStyle();
    hlang.setAlignment(CellStyle.ALIGN_CENTER);
    hlang.setBorderBottom(CellStyle.BORDER_MEDIUM);
    hlang.setBorderRight(CellStyle.BORDER_THIN);
    hlang.setFont(f);

    Row row = sheet.createRow(this.languageHeaderRow);
    Cell cell = row.createCell(this.keyColumn);
    cell.setCellStyle(key);
    cell.setCellValue("KEY");
    for (Entry<Locale, Integer> lang : langs.entrySet())
    {
        cell = row.createCell(lang.getValue());
        cell.setCellStyle(hlang);
        cell.setCellValue(lang.getKey().toString());
    }
}

private CellStyle createDateStyle(Workbook workbook) {
    CellStyle style = workbook.createCellStyle();
    style.setDataFormat(workbook.getCreationHelper().createDataFormat()
            .getFormat("m/d/yy"));

    return style;
}

private static CellStyle createBorderedStyle(Workbook wb) {
    CellStyle style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    return style;
}

public CellStyle builder(ReportTitle reportTitle, Workbook wb) {
    if (reportTitle.getStyle() == null) {
        return null;
    }
    ReportTitleStyle style = reportTitle.getStyle();
    int[] bgColor = style.getBgColor();
    int[] fontColor = style.getFontColor();
    int fontSize = style.getFontSize();
    if (wb instanceof HSSFWorkbook) {
        return createHSSFCellStyle(wb, bgColor, fontColor, fontSize);
    } else if (wb instanceof SXSSFWorkbook) {
        return createXSSFCellStyle(wb, bgColor, fontColor, fontSize);
    }
    return null;
}

public void easyStyle() throws IOException {
    Excel excel = new Excel();
    ExcelSheet sheet = excel.createSheet();
    //设置样式
    CellStyle cellStyle = ExcelStyle.builder(excel.getWorkbook())
            .align(HSSFCellStyle.ALIGN_CENTER) //设置居中
            .fondFamily("宋体")  //设置字体
            .fondSize((short) 12) //设置字体大小
            .fondWeight((short) 10) //加粗
            .border(ExcelStyle.BORDER_TOP, ExcelStyle.BORDER_LEFT, ExcelStyle.BORDER_BOTTOM, ExcelStyle.BORDER_RIGHT) //设置表格边框
            .build();
    sheet.row(1).cell(2).cellValue("2").cellStyle(cellStyle);
    excel.saveExcel("c://test1.xlsx");
}

/**
 * excel 样式
 *
 * @return
 */
public Map<String, CellStyle> createStyles(Workbook workbook) {
    Map<String, CellStyle> styles = new HashMap();
    CellStyle style = workbook.createCellStyle();
    style.setAlignment((short) 2);
    style.setVerticalAlignment((short) 1);
    Font titleFont = workbook.createFont();
    titleFont.setFontName("Arial");
    titleFont.setFontHeightInPoints((short) 16);
    titleFont.setBoldweight((short) 700);
    style.setFont(titleFont);
    styles.put("title", style);
    style = workbook.createCellStyle();
    style.setVerticalAlignment((short) 1);
    style.setBorderRight((short) 1);
    style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderLeft((short) 1);
    style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderTop((short) 1);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom((short) 1);
    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    Font dataFont = workbook.createFont();
    dataFont.setFontName("Arial");
    dataFont.setFontHeightInPoints((short) 10);
    style.setFont(dataFont);
    styles.put("data", style);
    style = workbook.createCellStyle();
    style.cloneStyleFrom((CellStyle) styles.get("data"));
    style.setAlignment((short) 1);
    styles.put("data1", style);
    style = workbook.createCellStyle();
    style.cloneStyleFrom((CellStyle) styles.get("data"));
    style.setAlignment((short) 2);
    styles.put("data2", style);
    style = workbook.createCellStyle();
    style.cloneStyleFrom((CellStyle) styles.get("data"));
    style.setAlignment((short) 3);
    styles.put("data3", style);
    style = workbook.createCellStyle();
    style.cloneStyleFrom((CellStyle) styles.get("data"));
    style.setAlignment((short) 2);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern((short) 1);
    Font headerFont = workbook.createFont();
    headerFont.setFontName("Arial");
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setBoldweight((short) 700);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    style.setFont(headerFont);
    styles.put("header", style);
    return styles;
}

/**
 * Returns the default totals row style for Double data. Obtained from:
 * http://svn.apache.org/repos/asf/poi
 * /trunk/src/examples/src/org/apache/poi/ss/examples/TimesheetDemo.java
 *
 * @param wb the wb
 * @return the cell style
 */
protected CellStyle defaultTotalsDoubleCellStyle(final Workbook wb) {
    CellStyle style;
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setDataFormat(doubleDataFormat);
    return style;
}

/**
 * Returns the default totals row style for Integer data. Obtained from:
 * http://svn.apache.org/repos/asf/poi
 * /trunk/src/examples/src/org/apache/poi/ss/examples/TimesheetDemo.java
 *
 * @param wb the wb
 * @return the cell style
 */
protected CellStyle defaultTotalsIntegerCellStyle(final Workbook wb) {
    CellStyle style;
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setDataFormat(integerDataFormat);
    return style;
}

public void setDoubleDataFormat(final String excelDoubleFormat) {
    CellStyle prevDoubleDataStyle = null;
    if (dataFormatCellStylesMap.containsKey(doubleDataFormat)) {
        prevDoubleDataStyle = dataFormatCellStylesMap.get(doubleDataFormat);
        dataFormatCellStylesMap.remove(doubleDataFormat);
    }
    doubleDataFormat = createHelper.createDataFormat().getFormat(excelDoubleFormat);
    if (null != prevDoubleDataStyle) {
        doubleCellStyle = prevDoubleDataStyle;
        doubleCellStyle.setDataFormat(doubleDataFormat);
        dataFormatCellStylesMap.put(doubleDataFormat, doubleCellStyle);
    }
}

public void setIntegerDataFormat(final String excelIntegerFormat) {
    CellStyle prevIntegerDataStyle = null;
    if (dataFormatCellStylesMap.containsKey(integerDataFormat)) {
        prevIntegerDataStyle = dataFormatCellStylesMap.get(integerDataFormat);
        dataFormatCellStylesMap.remove(integerDataFormat);
    }
    integerDataFormat = createHelper.createDataFormat().getFormat(excelIntegerFormat);
    if (null != prevIntegerDataStyle) {
        integerCellStyle = prevIntegerDataStyle;
        integerCellStyle.setDataFormat(integerDataFormat);
        dataFormatCellStylesMap.put(integerDataFormat, integerCellStyle);
    }
}