Java Code Examples for org.apache.poi.ss.usermodel.Cell#setCellStyle()

/**
 * @param wb
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
		sheet.setColumnWidth(i, COL_WIDTH*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
	}
}
 

public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

private void createHeader(final Table table, final Translator translator, final int cdcnt, final Sheet exportSheet) {

        Row headerRow = exportSheet.createRow(0);
        for (int c = 0; c < cdcnt; c++) {
            ColumnDescriptor cd = table.getColumnDescriptor(c);
            if (cd instanceof StaticColumnDescriptor) {
                // ignore static column descriptors - of no value in excel download!
                continue;
            }
            String headerKey = cd.getHeaderKey();
            String headerVal = cd.translateHeaderKey() ? translator.translate(headerKey) : headerKey;
            Cell cell = headerRow.createCell(c);
            cell.setCellValue(headerVal);
            cell.setCellStyle(headerCellStyle);
        }
    }
 

private void buildSheet(SXSSFWorkbook wb,VariableCategory vc,XSSFCellStyle style){
	String name=vc.getName();
	Sheet sheet=wb.createSheet(name);
	Row row=sheet.createRow(0);
	List<Variable> variables=vc.getVariables();
	for(int i=0;i<variables.size();i++){
		sheet.setColumnWidth(i,4000);
		Cell cell=row.createCell(i);
		Variable var=variables.get(i);
		cell.setCellValue(var.getLabel());
		cell.setCellStyle(style);
	}
}
 

private void setColumnStyles(Row row, CellStyle style, int... colNums) {
	for(int j : colNums) {
		Cell cell = row.getCell(j);
		if (cell != null) {
			cell.setCellStyle(style);
		}
	}
}
 

/**
 * Create cell cell.
 *
 * @param value the value
 * @return the cell
 */
public Cell createCell(double value) {

    Cell cell = this.getNextCell(CellType.NUMERIC);
    cell.setCellValue(value);
    cell.setCellStyle(this.style.getNumberCs());
    return cell;
}
 

/**
 * セルの書式をコピーする。
 * <p>コピー先のセルの種類は、空セルとする。</p>
 * <p>結合情報も列方向の結合をコピーする。</p>
 *
 * @since 2.0
 * @param fromCell コピー元
 * @param toCell コピー先
 */
private void copyCellStyle(final Cell fromCell, final Cell toCell) {

    final CellStyle style = fromCell.getCellStyle();
    toCell.setCellStyle(style);
    toCell.setCellType(CellType.BLANK);

    // 横方向に結合されている場合、結合情報のコピーする。（XlsArrayColumns用）
    final Sheet sheet = fromCell.getSheet();
    final CellRangeAddress mergedRegion = POIUtils.getMergedRegion(sheet, fromCell.getRowIndex(), fromCell.getColumnIndex());
    final int mergedSize = POIUtils.getColumnSize(mergedRegion);

    if(POIUtils.getColumnSize(mergedRegion) >= 2) {
        CellRangeAddress newMergedRegion = POIUtils.getMergedRegion(sheet, toCell.getRowIndex(), toCell.getColumnIndex());
        if(newMergedRegion != null) {
            // 既に結合している場合 - 通常はありえない。
            return;
        }

        newMergedRegion = POIUtils.mergeCells(sheet,
                mergedRegion.getFirstColumn(), toCell.getRowIndex(), mergedRegion.getLastColumn(), toCell.getRowIndex());

        // 結合先のセルの書式も設定する
        // 中間のセルの設定
        for(int i=1; i < mergedSize; i++) {
            Cell mergedFromCell = POIUtils.getCell(sheet, toCell.getColumnIndex()+i, fromCell.getRowIndex());

            Cell mergedToCell = POIUtils.getCell(sheet, toCell.getColumnIndex()+i, toCell.getRowIndex());
            mergedToCell.setCellStyle(mergedFromCell.getCellStyle());
            mergedToCell.setCellType(CellType.BLANK);
        }

    }

}
 

/**
 * Create cell cell.
 *
 * @param str the str
 * @return the cell
 */
public Cell createCell(String str) {

    Cell cell = this.getNextCell(CellType.STRING);
    cell.setCellValue(str);
    cell.setCellStyle(this.style.getStringCs());
    return cell;
}
 

/**
 * 构建头部
 */
private void createHead(){
	titleRow = sheet.createRow(rowNum);
	if(table!=null && table.getTableproperty()!=null){
		for(TableProperties tp : table.getTableproperty()){
			Cell cell2 = titleRow.createCell(table.getTableproperty().indexOf(tp)); 
			cell2.setCellStyle(firstStyle); 
			cell2.setCellValue(new HSSFRichTextString(tp.getName()));
		}
	}
	rowNum ++ ;
}
 

/**
 * @param wb
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	sheet.setColumnWidth(NAMESPACE_COL, NAMESPACE_COL_WIDTH*256);
	sheet.setDefaultColumnStyle(NAMESPACE_COL, defaultStyle);
	Cell extractedHeaderCell = row.createCell(NAMESPACE_COL);
	extractedHeaderCell.setCellStyle(headerStyle);
	extractedHeaderCell.setCellValue(NAMESPACE_TEXT_TITLE);
	
	sheet.setColumnWidth(CHECKSUM_COL, CHECKSUM_COL_WIDTH*256);
	sheet.setDefaultColumnStyle(CHECKSUM_COL, defaultStyle);
	Cell checksumHeaderCell = row.createCell(CHECKSUM_COL);
	checksumHeaderCell.setCellStyle(headerStyle);
	checksumHeaderCell.setCellValue(CHECKSUM_TEXT_TITLE);
	
	for (int i = FIRST_DOC_ID_COL; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
		sheet.setColumnWidth(i, DOC_ID_COL_WIDTH*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
	}
}
 

public static void cloneCell( Cell cNew, Cell cOld ){
	cNew.setCellComment( cOld.getCellComment() );
	cNew.setCellStyle( cOld.getCellStyle() );
	cNew.setCellType( cOld.getCellType() );
	
	switch ( cNew.getCellType() ){
		case Cell.CELL_TYPE_BOOLEAN:{
			cNew.setCellValue( cOld.getBooleanCellValue() );
			break;
		}
		case Cell.CELL_TYPE_NUMERIC:{
			cNew.setCellValue( cOld.getNumericCellValue() );
			break;
		}
		case Cell.CELL_TYPE_STRING:{
			cNew.setCellValue( cOld.getStringCellValue() );
			break;
		}
		case Cell.CELL_TYPE_ERROR:{
			cNew.setCellValue( cOld.getErrorCellValue() );
			break;
		}
		case Cell.CELL_TYPE_FORMULA:{
			cNew.setCellFormula( cOld.getCellFormula() );
			break;
		}
		case Cell.CELL_TYPE_BLANK:{
			cNew.setCellValue( cOld.getNumericCellValue() );
			break;
		}
			
	}
	
}
 

public void postProcessXLS(Object document) {  
    HSSFWorkbook wb = (HSSFWorkbook) document;  
    HSSFSheet sheet = wb.getSheetAt(0);  
    HSSFRow header = sheet.getRow(0);  
      
    HSSFCellStyle cellStyle = wb.createCellStyle();    
    cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);  
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
      
    for(int i=0; i < header.getPhysicalNumberOfCells();i++) {  
        HSSFCell cell = header.getCell(i);  
          
        cell.setCellStyle(cellStyle);  
    }  
    
    Row row=sheet.createRow((short)sheet.getLastRowNum()+3);
    Cell cellDisclaimer = row.createCell(0);
    HSSFFont customFont= wb.createFont();
    customFont.setFontHeightInPoints((short)10);
    customFont.setFontName("Arial");
    customFont.setColor(IndexedColors.BLACK.getIndex());
    customFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    customFont.setItalic(true);
    
    cellDisclaimer.setCellValue("Disclaimer");
    HSSFCellStyle cellStyleDisclaimer = wb.createCellStyle();
    cellStyleDisclaimer.setFont(customFont);
    cellDisclaimer.setCellStyle(cellStyleDisclaimer);
    
    Row row1=sheet.createRow(sheet.getLastRowNum()+2);
    Cell cellDisclaimerContent1 = row1.createCell(0);
    cellDisclaimerContent1.setCellValue("The information contained in this website is for information purposes only, and does not constitute, nor is it intended to constitute, the provision of financial product advice.");
    
    Row row2=sheet.createRow(sheet.getLastRowNum()+1);
    Cell cellDisclaimerContent2 = row2.createCell(0);
    cellDisclaimerContent2.setCellValue("This website is intended to track the investor account summary information,investments and transaction in a partcular period of time. ");
    
}
 

private Cell createCell(Row row, short column, CellStyle cellStyle) {
	Cell cell = row.createCell(column);
	if (cellStyle != null) {
		cell.setCellStyle(cellStyle);
	}
	
	return cell;
}
 

@Override
protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
    if (contentCellStyleList == null || contentCellStyleList.isEmpty()) {
        return;
    }
    cell.setCellStyle(contentCellStyleList.get(relativeRowIndex % contentCellStyleList.size()));
}
 

@Override
public void printLine(String... fields) {
	int cellIdx = 0;
	Row row = iSheet.createRow(iRowNum++);
	int nrLines = 1;
	for (int idx = 0; idx < fields.length; idx++) {
		if (iHiddenColumns.contains(idx)) continue;
		if (iHiddenColumns.contains(idx)) continue;
		Cell cell = row.createCell(cellIdx ++);
		
		String f = fields[idx];
		if (f == null || f.isEmpty() || (iCheckLast && f.equals(iLastLine == null || idx >= iLastLine.length ? null : iLastLine[idx]))) f = "";
		
		boolean number = sNumber.matcher(f).matches();
		
		cell.setCellStyle(iStyles.get(number ? "number" : "plain"));
		if (f == null || f.isEmpty()) {
		} else if (number) {
			try {
				cell.setCellValue(Double.valueOf(f));
			} catch (NumberFormatException e) {
				cell.setCellValue(f);
			}
		} else {
			nrLines = Math.max(nrLines, f.split("\n").length);
			cell.setCellValue(f);
		}
	}
	if (nrLines > 1)
		row.setHeightInPoints(nrLines * iSheet.getDefaultRowHeightInPoints() + 1f);
	iLastLine = fields;
}
 

private void formatRow( Sheet	sheet, CellStyle style, int rowNo ){
	Row	row = sheet.getRow( rowNo );
	if ( row == null )
		return;
	
	int cellInRow = row.getLastCellNum()+1;
	
	for ( int c = 0; c < cellInRow; c++ ){
		Cell	cell	= row.getCell( c, Row.CREATE_NULL_AS_BLANK );
		cell.setCellStyle( style );
	}
}
 

/**
 * 构建头部
 */
@SuppressWarnings("deprecation")
private void createHead(){
	Row row = sheet.createRow(rowNum); 

	// 设置第一行 
	Cell cell = row.createCell(0); 
	row.setHeight((short) 1100); 

	// 定义单元格为字符串类型 
	cell.setCellType(HSSFCell.ENCODING_UTF_16); 
	cell.setCellValue(new XSSFRichTextString(this.headTitle)); 

	// 指定合并区域 
	if(rowNum>0) {
		sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum,0,(cellNumber-1))); 
	}

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

	// 设置单元格字体 
	Font font = wb.createFont(); 
	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
	font.setFontName("宋体"); 
	font.setFontHeight((short) 400); 
	cellStyle.setFont(font); 
	cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	cell.setCellStyle(cellStyle); 
	for(int i=1;i<this.cellNumber;i++){
		Cell cell3= row.createCell(i); 
		cell3.setCellStyle(cellStyle);
	}
	rowNum ++;
}
 

/**
 * 合并列
 */
private void mergeColumns(Sheet sheet, CellStyle cellStyle) {
    // 行数
    int rowsCount = sheet.getPhysicalNumberOfRows();
    // 列数
    int colsCount = sheet.getRow(0).getPhysicalNumberOfCells();

    Row row = null;
    Cell cell1 = null;
    Cell cell2 = null;

    int colSpan = 0;

    for (int r = 0; r < rowsCount; r++) {
        row = sheet.getRow(r);
        // 重置
        colSpan = 0;
        row = sheet.getRow(r);
        for (int c = 0; c < colsCount; c++) {
            cell1 = row.getCell(c);
            cell2 = row.getCell(c + 1);
            if (cell1 == null) {// 如果当前单元格是空的，跳过，继续当前行的后一个单元格查找
                if (c == colsCount - 1) {
                    break;
                } else {
                    continue;
                }
            }
            if (cell2 == null) {// 说明当前行已经到最后一个单元格了
                if (colSpan >= 1) {// 判断colSpan是否大于等于1，大于1就要合并了
                    // 合并行中连续相同的值的单元格
                    sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                    break;
                }
            }
            if (cell1 != null && cell2 != null) {
                // 如果当前单元格和下一个单元格内容相同，那么colSpan加1
                if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                    colSpan++;
                } else {
                    // 如果当前单元格和下一个不等，那么判断colSpan是否大于等于1
                    if (colSpan >= 1) {
                        // 合并行中连续相同的值的单元格
                        sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                        Cell nowCell = sheet.getRow(r).getCell(c - colSpan);
                        nowCell.setCellStyle(cellStyle);
                        // 合并后重置colSpan
                        colSpan = 0;
                        continue;
                    }
                }
            }

        }
    }

}
 

/**
 * Construye la hoja de Excel
 *
 * @return Instancia de la clase XSSFWorkbook con los datos almacenados en
 * el buffer de datos
 */
private XSSFWorkbook buildSpreadsheet() {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();
    System.out.println("Building spreadsheet...");
    System.out.println("Buffer size: " + mainBuffer.size());
    for (int i = 0; i <= mainBuffer.get(0).size(); i++) {
        Row row = sheet.createRow(i);
        for (int j = 0; j < variableList.size(); j++) {
            Cell cell = row.createCell(j);
            if (i == 0) {
                cell.setCellValue((String) variableList.get(j));
            } else {
                Object value = classList.get(j);
                if ((value instanceof String) || (value.equals(String.class))) {
                    cell.setCellValue((String) mainBuffer.get(j).get(i - 1));
                } else if ((value instanceof Boolean) || (value.equals(Boolean.class))) {
                    cell.setCellValue((Boolean) mainBuffer.get(j).get(i - 1));
                } else if ((value instanceof Date) || (value.equals(Date.class))) {

                    CellStyle cellStyle = workbook.createCellStyle();
                    CreationHelper createHelper = workbook.getCreationHelper();
                    cellStyle.setDataFormat(
                            createHelper.createDataFormat().getFormat(dateStringFormat));
                    cell.setCellValue((Date) mainBuffer.get(j).get(i - 1));
                    cell.setCellStyle(cellStyle);

                } else if ((value instanceof Integer) || (value.equals(Integer.class))) {
                    cell.setCellValue((Integer) mainBuffer.get(j).get(i - 1));
                } else if ((value instanceof Long) || (value.equals(Long.class))) {
                    cell.setCellValue((Long) mainBuffer.get(j).get(i - 1));
                } else if ((value instanceof Float) || (value.equals(Float.class))) {
                    cell.setCellValue((Float) mainBuffer.get(j).get(i - 1));
                } else if ((value instanceof Double) || (value.equals(Double.class))) {
                    cell.setCellValue((Double) mainBuffer.get(j).get(i - 1));
                }
            }
        }
    }
    return workbook;
}
 

@SuppressWarnings("unchecked")
private int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception {
	String pieBase64Content = SimpleUtils.getPNGBase64Content( (String)context.get("pieCanvasToData") );
	String barBase64Content = SimpleUtils.getPNGBase64Content( (String)context.get("barCanvasToData") );
	BufferedImage pieImage = SimpleUtils.decodeToImage( pieBase64Content );
	BufferedImage barImage = SimpleUtils.decodeToImage( barBase64Content );
	ByteArrayOutputStream pieBos = new ByteArrayOutputStream();
	ImageIO.write( pieImage, "png", pieBos );
	pieBos.flush();
	ByteArrayOutputStream barBos = new ByteArrayOutputStream();
	ImageIO.write( barImage, "png", barBos );
	barBos.flush();		
	SimpleUtils.setCellPicture(wb, sh, pieBos.toByteArray(), 0, 0);		
	SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), 0, 9);		
	int row = 21;
	
	List< Map<String, Object> > chartDatas = (List< Map<String, Object> >)context.get("chartDatas");
	String year = (String)context.get("year");
	
	
	XSSFCellStyle cellHeadStyle = wb.createCellStyle();
	cellHeadStyle.setFillForegroundColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#f5f5f5" ) ) );
	cellHeadStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND  );				
	
	XSSFFont cellHeadFont = wb.createFont();
	cellHeadFont.setBold(true);
	//cellHeadFont.setColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#000000" ) ) );		
	cellHeadStyle.setFont( cellHeadFont );
	
	int titleRow = row - 1;
	int titleCellSize = 14;
	Row headRow = sh.createRow( titleRow );
	for (int i=0; i<titleCellSize; i++) {
		Cell headCell = headRow.createCell( i );
		headCell.setCellStyle(cellHeadStyle);
		headCell.setCellValue( "Perspectives metrics gauge ( " + year + " )" );					
	}
	sh.addMergedRegion( new CellRangeAddress(titleRow, titleRow, 0, titleCellSize-1) );
	
	int cellLeft = 10;
	int rowSpace = 17;
	for (Map<String, Object> data : chartDatas) {							
		Map<String, Object> nodeData = (Map<String, Object>) ( (List<Object>)data.get("datas") ).get(0); 
		String pngImageData = SimpleUtils.getPNGBase64Content( (String)nodeData.get("outerHTML") );			
		BufferedImage imageData = SimpleUtils.decodeToImage( pngImageData );
		ByteArrayOutputStream imgBos = new ByteArrayOutputStream();
		ImageIO.write( imageData, "png", imgBos );
		imgBos.flush();		
		SimpleUtils.setCellPicture(wb, sh, imgBos.toByteArray(), row, 0);
		
		XSSFColor bgColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("bgColor") ) );
		XSSFColor fnColor = new XSSFColor( SimpleUtils.getColorRGB4POIColor( (String)nodeData.get("fontColor") ) );			
		
		XSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFillForegroundColor( bgColor );
		cellStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND  );				
		
		XSSFFont cellFont = wb.createFont();
		cellFont.setBold(true);
		cellFont.setColor(fnColor);			
		
		cellStyle.setFont(cellFont);
		
		int perTitleCellSize = 4;
		Row nowRow = sh.createRow(row);
		for (int i=0; i<perTitleCellSize; i++) {
			Cell cell1 = nowRow.createCell(cellLeft);
			cell1.setCellStyle(cellStyle);
			cell1.setCellValue( (String)nodeData.get("name") );				
		}
		sh.addMergedRegion( new CellRangeAddress(row, row, cellLeft, cellLeft+perTitleCellSize-1) );
		
		nowRow = sh.createRow(row+1);
		Cell cell2 = nowRow.createCell(cellLeft);
		cell2.setCellValue( "Target: " + String.valueOf( nodeData.get("target") ) );			
		
		nowRow = sh.createRow(row+2);
		Cell cell3 = nowRow.createCell(cellLeft);
		cell3.setCellValue( "Min: " + String.valueOf( nodeData.get("min") ) );				
		
		nowRow = sh.createRow(row+3);
		Cell cell4 = nowRow.createCell(cellLeft);
		cell4.setCellValue( "Score: " + String.valueOf( nodeData.get("score") ) );				
		
		row += rowSpace;			
	}
	
	return row;
}