org.apache.poi.hssf.usermodel.HSSFCellStyle Java Examples

@SuppressWarnings("deprecation")
private CellStyle baseCellStyle(){
	CellStyle cellStyle = wb.createCellStyle();
	cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 

	cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
			
	cellStyle.setWrapText(true);
	cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	
	Font font = wb.createFont(); 
	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
	font.setFontName("宋体"); 
	font.setFontHeight((short) 200); 
	cellStyle.setFont(font); 
	
	return cellStyle;
}
 

private CellStyle baseCellStyle(){
	CellStyle cellStyle = wb.createCellStyle();
	cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 

	cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
			
	cellStyle.setWrapText(true);
	cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	
	Font font = wb.createFont(); 
	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
	//font.setFontName("宋体"); 
	font.setFontHeight((short) 200); 
	cellStyle.setFont(font); 
	
	return cellStyle;
}
 

public static HSSFCellStyle copyCellStyle(final HSSFWorkbook workbook, final HSSFCellStyle style) {

        final HSSFCellStyle newCellStyle = workbook.createCellStyle();

        newCellStyle.setAlignment(style.getAlignment());
        newCellStyle.setBorderBottom(style.getBorderBottom());
        newCellStyle.setBorderLeft(style.getBorderLeft());
        newCellStyle.setBorderRight(style.getBorderRight());
        newCellStyle.setBorderTop(style.getBorderTop());
        newCellStyle.setBottomBorderColor(style.getBottomBorderColor());
        newCellStyle.setDataFormat(style.getDataFormat());
        newCellStyle.setFillBackgroundColor(style.getFillBackgroundColor());
        newCellStyle.setFillForegroundColor(style.getFillForegroundColor());
        newCellStyle.setFillPattern(style.getFillPattern());
        newCellStyle.setHidden(style.getHidden());
        newCellStyle.setIndention(style.getIndention());
        newCellStyle.setLeftBorderColor(style.getLeftBorderColor());
        newCellStyle.setLocked(style.getLocked());
        newCellStyle.setRightBorderColor(style.getRightBorderColor());
        newCellStyle.setRotation(style.getRotation());
        newCellStyle.setTopBorderColor(style.getTopBorderColor());
        newCellStyle.setVerticalAlignment(style.getVerticalAlignment());
        newCellStyle.setWrapText(style.getWrapText());

        final HSSFFont font = workbook.getFontAt(style.getFontIndex());
        newCellStyle.setFont(font);

        return newCellStyle;
    }
 

protected void addBlankElement(HSSFCellStyle cellStyle, boolean repeatValue, String currentColumnName) throws JRException {
	if (columnNames.size() > 0) {
		if (columnNames.contains(currentColumnName) && !currentRow.containsKey(currentColumnName) && isColumnReadOnTime(currentRow, currentColumnName)) {	// the column is for export but was not read yet and comes in the expected order
			addBlankCell(cellStyle, currentRow, currentColumnName); 
		} else if ( (columnNames.contains(currentColumnName) && !currentRow.containsKey(currentColumnName) && !isColumnReadOnTime(currentRow, currentColumnName)) 	// the column is for export, was not read yet, but it is read after it should be
				|| (columnNames.contains(currentColumnName) && currentRow.containsKey(currentColumnName)) ) {	// the column is for export and was already read
			
			if(rowIndex == 1 && getCurrentItemConfiguration().isWriteHeader()) {
				writeReportHeader();
			}
			
			writeCurrentRow(currentRow, repeatedValues);
			currentRow.clear();
			addBlankCell(cellStyle, currentRow, currentColumnName);
		}
		
		// set auto fill columns
		if(repeatValue) {
			if (repeatValue && currentColumnName != null && currentColumnName.length() > 0 && cellStyle != null) {
				addBlankCell(cellStyle, repeatedValues, currentColumnName);
			}
		} else {
			repeatedValues.remove(currentColumnName);
		}
	}
}
 

private CellStyle createContentStyle(){
	CellStyle cellStyle = wb.createCellStyle(); 
	
	cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐 
	cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐 
	cellStyle.setWrapText(false);// 指定单元格自动换行 

	// 设置单元格字体 
	Font font = wb.createFont(); 
	//font.setFontName("微软雅黑"); 
	font.setFontHeight((short) 200); 
	cellStyle.setFont(font); 
	cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	return cellStyle ;
}
 

/**
 * 首列样式
 * @return
 */
private CellStyle createFirstCellStyle(){
	CellStyle cellStyle = baseCellStyle();
	Font font = wb.createFont();
	//font.setFontName("微软雅黑"); 
	font.setFontHeight((short) 180);
	cellStyle.setFont(font);
	
	cellStyle.setWrapText(false);
	
	cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
	cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	

	return cellStyle;
}
 

private static CellStyle createHeaderStyle(Workbook workbook){
	// 生成一个样式
	CellStyle style = workbook.createCellStyle();

	// 设置表头的样式
	style.setFillForegroundColor(HSSFColor.WHITE.index);
	style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

	// 生成表头的字体
	Font font = workbook.createFont();
	font.setColor(HSSFColor.VIOLET.index);
	font.setFontHeightInPoints((short) 12);
	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	// 把字体应用到当前的样式
	style.setFont(font);
	return style;
}
 

@Test
public void testFastExport() throws ResourceException, ReportProcessingException, IOException {
  // This establishes a baseline for the second test using the slow export.

  final MasterReport report = DebugReportRunner.parseLocalReport( "Prd-5391.prpt", Prd5391IT.class );
  final ByteArrayOutputStream bout = new ByteArrayOutputStream();
  FastExcelReportUtil.processXls( report, bout );

  final HSSFWorkbook wb = new HSSFWorkbook( new ByteArrayInputStream( bout.toByteArray() ) );
  final HSSFSheet sheetAt = wb.getSheetAt( 0 );
  final HSSFRow row = sheetAt.getRow( 0 );
  final HSSFCell cell0 = row.getCell( 0 );

  // assert that we are in the correct export type ..
  final HSSFCellStyle cellStyle = cell0.getCellStyle();
  final HSSFColor fillBackgroundColorColor = cellStyle.getFillBackgroundColorColor();
  final HSSFColor fillForegroundColorColor = cellStyle.getFillForegroundColorColor();
  Assert.assertEquals( "0:0:0", fillBackgroundColorColor.getHexString() );
  Assert.assertEquals( "FFFF:8080:8080", fillForegroundColorColor.getHexString() );

  HSSFFont font = cellStyle.getFont( wb );
  Assert.assertEquals( "Times New Roman", font.getFontName() );
}
 

/**
 * exce表头单元格样式处理
 * @param workbook
 * @return
 */
public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
	// 产生Excel表头
	HSSFCellStyle titleStyle = workbook.createCellStyle();
	titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE); // 设置边框样式
	titleStyle.setBorderLeft((short) 2); // 左边框
	titleStyle.setBorderRight((short) 2); // 右边框
	titleStyle.setBorderTop((short) 2); // 左边框
	titleStyle.setBorderBottom((short) 2); // 右边框
	titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE); // 顶边框
	titleStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); // 填充的背景颜色
	titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

	titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充图案

	return titleStyle;
}
 

private HSSFCellStyle createMatrixCellStyle(final HSSFWorkbook workbook, final HSSFCellStyle matrixHeaderTemplateCellStyle, final boolean top, final boolean right, final boolean bottom, final boolean left) {
    final HSSFCellStyle cellStyle = POIUtils.copyCellStyle(workbook, matrixHeaderTemplateCellStyle);

    if (top) {
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
    }
    if (right) {
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    }
    if (bottom) {
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    }
    if (left) {
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    }

    return cellStyle;
}
 

@Override
public boolean equals(Object obj) {
    if (obj instanceof HSSFStyleCacheKey) {
        HSSFStyleCacheKey objKey = (HSSFStyleCacheKey) obj;
        HSSFCellStyle objStyle = objKey.style;
        if (style == objStyle) {
            return true;
        }
        if (objStyle == null) {
            return false;
        }
        if (format == null) {
            if (objKey.format != null) {
                return false;
            }
        } else if (!format.equals(objKey.format)) {
            return false;
        }
        return true;
    } else {
        return false;
    }
}
 

/**
 * 单元格垂直对齐
 */
private String convertVerticalAlignToHtml(short verticalAlignment) {
	String align = "middle";
	switch (verticalAlignment) {
		case HSSFCellStyle.VERTICAL_BOTTOM: {
			align = "bottom";
		} break;
		case HSSFCellStyle.VERTICAL_CENTER: {
			align = "center";
		} break;
		case HSSFCellStyle.VERTICAL_TOP: {
			align = "top";
		} break;
	}
	return align;
}
 

public void importValues(				
	CellType cellType,
	HSSFCellStyle cellStyle,
	Object cellValue,
	String formula,
	Hyperlink link
	) 
{
	if(!CellType.FORMULA.equals(cellType))
	{
		this.cellType = cellType;
	}
	this.cellStyle = cellStyle;
	this.cellValue = cellValue;
	this.formula = formula;
	this.link = link;
}
 

public static List<HSSFCellStyle> copyCellStyle(final HSSFWorkbook workbook, final HSSFRow row) {
    final List<HSSFCellStyle> cellStyleList = new ArrayList<HSSFCellStyle>();

    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {

        final HSSFCell cell = row.getCell(colNum);
        if (cell != null) {
            final HSSFCellStyle style = cell.getCellStyle();
            final HSSFCellStyle newCellStyle = copyCellStyle(workbook, style);
            cellStyleList.add(newCellStyle);
        } else {
            cellStyleList.add(null);
        }
    }

    return cellStyleList;
}
 

private HSSFCellStyle createHSSFCellStyle(Workbook wb, int[] bgColor, int[] fontColor, int fontSize) {
	HSSFWorkbook workbook = (HSSFWorkbook) wb;
	HSSFPalette palette = workbook.getCustomPalette();
	
	palette.setColorAtIndex((short) 9, (byte) fontColor[0], (byte) fontColor[1], (byte) fontColor[2]);
	palette.setColorAtIndex((short) 10, (byte) bgColor[0], (byte) bgColor[1], (byte) bgColor[2]);

	HSSFFont titleFont = workbook.createFont();
	titleFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
	titleFont.setFontName("宋体");
	titleFont.setColor((short) 9);
	titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	titleFont.setFontHeightInPoints((short) fontSize);

	HSSFCellStyle titleStyle = (HSSFCellStyle) createBorderCellStyle(workbook, true);
	titleStyle.setFont(titleFont);
	titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	titleStyle.setFillForegroundColor((short) 10);
	titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
	titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

	return titleStyle;
}
 

protected void setNumericCell(HSSFCell cell, BigDecimal value, HSSFWorkbook workbook)
{
    if(logger.isDebugEnabled())
        logger.debug("setNumericCell(cell={}, value={}, workbook={}) - start", 
            new Object[] {cell, value, workbook} );

    cell.setCellValue( ((BigDecimal)value).doubleValue() );

    HSSFDataFormat df = workbook.createDataFormat();
    int scale = ((BigDecimal)value).scale();
    short format;
    if(scale <= 0){
        format = df.getFormat("####");
    }
    else {
        String zeros = createZeros(((BigDecimal)value).scale());
        format = df.getFormat("####." + zeros);
    }
    if(logger.isDebugEnabled())
        logger.debug("Using format '{}' for value '{}'.", String.valueOf(format), value);
    
    HSSFCellStyle cellStyleNumber = workbook.createCellStyle();
    cellStyleNumber.setDataFormat(format);
    cell.setCellStyle(cellStyleNumber);
}
 

@Override
public void addBackgroundColourToStyle(Workbook workbook, CellStyle style, String colour) {
	if(colour == null) {
		return ;
	}
	if(IStyle.TRANSPARENT_VALUE.equals(colour)) {
		return ;
	}
	if(style instanceof HSSFCellStyle) {
		HSSFCellStyle cellStyle = (HSSFCellStyle)style;
		short colourIndex = getHColour((HSSFWorkbook)workbook, colour);
		if( colourIndex > 0 ) {
			cellStyle.setFillForegroundColor(colourIndex);
			cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		}
	}
}
 

/**
 * 单元格小平对齐
 */
private String convertAlignToHtml(short alignment) {
	String align = "left";
	switch (alignment) {
		case HSSFCellStyle.ALIGN_LEFT: {
			align = "left";
		} break;
		case HSSFCellStyle.ALIGN_CENTER: {
			align = "center";
		} break;
		case HSSFCellStyle.ALIGN_RIGHT: {
			align = "right";
		} break;
	}
	return align;
}
 

protected HSSFCellStyle getLoadedCellStyle(
	FillPatternType mode,
	short backcolor,
	HorizontalAlignment horizontalAlignment,
	VerticalAlignment verticalAlignment,
	short rotation,
	HSSFFont font,
	BoxStyle box,
	boolean isWrapText,
	boolean isCellLocked,
	boolean isCellHidden,
	boolean isShrinkToFit
	)
{
	StyleInfo style = new StyleInfo(mode, backcolor, horizontalAlignment, verticalAlignment, rotation, font, box, isWrapText, isCellLocked, isCellHidden, isShrinkToFit);
	return getLoadedCellStyle(style);
}
 

/**
 * 创建 表头
 * 
 * @param title
 * @param sheet
 * @param workbook
 * @param feildWidth
 */
private static int createHeaderRow(ExcelTitle entity, Sheet sheet,
		HSSFWorkbook workbook, int feildWidth) {
	Row row = sheet.createRow(0);
	row.setHeight((short) 900);
	createStringCell(row, 0, entity.getTitle(), getHeaderStyle(workbook,entity),null);
	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, feildWidth));
	if(entity.getSecondTitle()!=null){
		row = sheet.createRow(1);
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		createStringCell(row, 0, entity.getSecondTitle(), style,null);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, feildWidth));
		return 2;
	}
	return 1;
}
 

public HSSFCellStyle processCellStyle(HSSFCellStyle cellStyle) {
    HSSFCellStyle cachedCellStyle = cellStyles.get(new HSSFStyleCacheKey(cellStyle));
    if (cachedCellStyle == null)
        cellStyles.put(new HSSFStyleCacheKey(cellStyle), cellStyle);
    else
        cellStyle = cachedCellStyle;

    return cellStyle;
}
 

public static HSSFCellStyle getTwoStyle(HSSFWorkbook workbook) {
	// 产生Excel表头
	HSSFCellStyle style = workbook.createCellStyle();
	style.setBorderLeft((short) 1); // 左边框
	style.setBorderRight((short) 1); // 右边框
	style.setBorderBottom((short) 1);
	style.setBorderTop((short) 1);
	style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index); // 填充的背景颜色
	style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充图案
	return style;
}
 

/**
 * 表明的Style
 * @param workbook
 * @return
 */
public static HSSFCellStyle getHeaderStyle(HSSFWorkbook workbook, ExcelTitle entity) {
	HSSFCellStyle titleStyle = workbook.createCellStyle();
	Font font = workbook.createFont();
	font.setFontHeightInPoints((short) 24);
	titleStyle.setFont(font);
	titleStyle.setFillForegroundColor(entity.getColor()); 
	titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	return titleStyle;
}
 

public CellSettings(
	CellType cellType,
	HSSFCellStyle cellStyle,
	Object cellValue,
	String formula,
	Hyperlink link
	) 
{
	this.cellType = cellType;
	this.cellStyle = cellStyle;
	this.cellValue = cellValue;
	this.formula = formula;
	this.link = link;
}
 

public static HSSFCellStyle getTwoStyle(HSSFWorkbook workbook, boolean isWarp) {
	HSSFCellStyle style = workbook.createCellStyle();
	style.setBorderLeft((short) 1); // 左边框
	style.setBorderRight((short) 1); // 右边框
	style.setBorderBottom((short) 1);
	style.setBorderTop((short) 1);
	style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index); // 填充的背景颜色
	style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充图案
	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	if(isWarp){style.setWrapText(true);}
	return style;
}
 

public static HSSFCellStyle getOneStyle(HSSFWorkbook workbook, boolean isWarp) {
	HSSFCellStyle style = workbook.createCellStyle();
	style.setBorderLeft((short) 1); // 左边框
	style.setBorderRight((short) 1); // 右边框
	style.setBorderBottom((short) 1);
	style.setBorderTop((short) 1);
	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	if(isWarp){style.setWrapText(true);}
	return style;
}
 

public static void createSheetInUserModel2File(HSSFWorkbook workbook,
		ExcelTitle entity, Class<?> pojoClass, Collection<?> dataSet) {
	try {
		Sheet sheet = workbook.createSheet(entity.getSheetName());
		//创建表格属性
		Map<String,HSSFCellStyle> styles = createStyles(workbook);
		Drawing patriarch = sheet.createDrawingPatriarch();
		List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
		// 得到所有字段
		Field fileds[] = ExcelPublicUtil.getClassFields(pojoClass);
		ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
		String targetId = null;
		if (etarget != null) {
			targetId = etarget.id();
		}
		getAllExcelField(targetId, fileds, excelParams, pojoClass, null);
		sortAllParams(excelParams);
		int index = 0;
		int feildWidth = getFieldWidth(excelParams);
		if (entity.getTitle() != null) {
			int i =  createHeaderRow(entity, sheet, workbook, feildWidth);
			sheet.createFreezePane(0, 2+i, 0, 2+i);
			index += i;
		} else {
			sheet.createFreezePane(0, 2, 0, 2);
		}
		createTitleRow(entity,sheet, workbook, index, excelParams);
		index += 2;
		setCellWith(excelParams, sheet);
		Iterator<?> its = dataSet.iterator();
		while (its.hasNext()) {
			Object t = its.next();
			index += createCells(patriarch,index, t, excelParams, sheet, workbook
					,styles);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

private static Map<String, HSSFCellStyle> createStyles(HSSFWorkbook workbook) {
	Map<String, HSSFCellStyle> map = new HashMap<String, HSSFCellStyle>();
	map.put("one", getOneStyle(workbook,false));
	map.put("oneWrap", getOneStyle(workbook,true));
	map.put("two", getTwoStyle(workbook,false));
	map.put("twoWrap", getTwoStyle(workbook,true));
	return map;
}
 

protected HSSFCellStyle getLoadedCellStyle(
	FillPatternType mode,
	short backcolor,
	HorizontalAlignment horizontalAlignment,
	VerticalAlignment verticalAlignment,
	short rotation,
	HSSFFont font,
	BoxStyle box,
	boolean isCellLocked,
	boolean isCellHidden,
	boolean isShrinkToFit
	) 
{
	return getLoadedCellStyle(new StyleInfo(mode, backcolor, horizontalAlignment, verticalAlignment, rotation, font, box, true, isCellLocked, isCellHidden, isShrinkToFit));
}
 

public static HSSFCellStyle getOneStyle(HSSFWorkbook workbook) {
	// 产生Excel表头
	HSSFCellStyle style = workbook.createCellStyle();
	style.setBorderLeft((short) 1); // 左边框
	style.setBorderRight((short) 1); // 右边框
	style.setBorderBottom((short) 1);
	style.setBorderTop((short) 1);
	return style;
}