org.apache.poi.ss.usermodel.CellStyle Java Examples

@Override
protected void exportXLSLine(final Sheet sheet, final CellStyle cellStyle, final List<Object> cells,
        final int offset) {

    final org.apache.poi.ss.usermodel.Row row = sheet.createRow(sheet.getLastRowNum() + offset);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    int count = 0;
    for (final Object cellValue : cells) {
        if (++count == 3) {
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
        } else {
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
        }
        addColumn(cellStyle, row, cellValue);
    }
}
 

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 centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);

	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

public CellStyle getCellStyle(Field field) {
    CellStyle style = cellStyleMap.get(field);
    if (style == null) {
        style = book.createCellStyle();
        Font font = book.createFont();
        FieldCellStyle fieldCellStyle = field.getFieldCellStyle();


        font.setFontHeightInPoints(fieldCellStyle.getFontSize());
        font.setBold(fieldCellStyle.isBold());
        ColorUtils.setColor(book, font, fieldCellStyle.getColor());
        style.setFont(font);
        ColorUtils.setForegroundColor(book, style, fieldCellStyle.getForegroundColor());

        setStyleBorder(style, fieldCellStyle.getBorder());
        ColorUtils.setBorderColor(book, style, fieldCellStyle.getBorderColor());

        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleMap.put(field, style);
        return style;
    }
    return style;
}
 

private void setStyleBorder(CellStyle style, BorderStyle[] border) {
    if (border != null) {
        if (border[0] != null) {
            style.setBorderTop(border[0]);
        }
        if (border[1] != null) {
            style.setBorderRight(border[1]);
        }
        if (border[2] != null) {
            style.setBorderBottom(border[2]);
        }
        if (border[3] != null) {
            style.setBorderLeft(border[3]);
        }
    }
}
 

private int addLangCell(Row header, TmxSegement segment) {
	int CellNum = header.getLastCellNum();
	if (-1 == CellNum) {
		CellNum = 0;
	}
	Cell createCell = header.createCell(CellNum);
	CellStyle cellStyle = wb.createCellStyle();
	XSSFFont headerFont = (XSSFFont) wb.createFont();
	headerFont.setBold(true);
	cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
	cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
	cellStyle.setFont(headerFont);
	createCell.setCellValue(segment.getLangCode());
	createCell.setCellStyle(cellStyle);
	sh.setColumnWidth(CellNum, (100 * 7 + 5) / 7 * 256);
	return CellNum;
}
 

public boolean hasValue(Cell cell) {

        if(ADD_CELL_COLOR_AS_PLAYER || INTERPRET_COLOR_AS_VALID_CELL_VALUE) {
            CellStyle style = cell.getCellStyle();
            short color = style.getFillBackgroundColor();
            if(color != 0) {
                return true;
            }
        }
        
        String str = getCellValueAsString(cell);
        if(str == null) return false;
        if(INTERPRET_FALSE_AS_EMPTY_CELL && "false".equalsIgnoreCase(str)) return false;
        if(INTERPRET_ZERO_AS_EMPTY_CELL && "0".equalsIgnoreCase(str)) return false;
        if(INTERPRET_ZERO_LENGTH_STRING_AS_EMPTY_CELL && "".equalsIgnoreCase(str)) return false;
        
        return true;
    }
 

private void template(XSSFWorkbook workbook) throws Exception {
	XSSFSheet sheet = workbook.createSheet("人员");
	Row row = sheet.createRow(0);
	Cell cell = row.createCell(0);
	cell.setCellValue("姓名");
	cell = row.createCell(1);
	cell.setCellValue("手机号");
	cell = row.createCell(2);
	cell.setCellValue("电子邮件");
	cell = row.createCell(3);
	cell.setCellValue("唯一编码");
	cell = row.createCell(4);
	cell.setCellValue("员工号");
	cell = row.createCell(5);
	cell.setCellValue("性别");
	cell = row.createCell(6);
	cell.setCellValue("(地址)");
	CellStyle cellStyle = workbook.createCellStyle();
	cellStyle.setWrapText(true);
	IntStream.rangeClosed(0, 6).forEach(i -> {
		sheet.setDefaultColumnStyle(i, cellStyle);
	});
}
 

@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 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 );
    }
}
 

private CellStyle getDecimalNumberFormat(int j, Sheet sheet, CreationHelper createHelper, CellStyle dCellStyle) {

		if (decimalFormats.get(j) != null)
			return decimalFormats.get(j);
		String decimals = "";
		for (int i = 0; i < j; i++) {
			decimals += "0";
		}

		CellStyle cellStyleDoub = this.buildCellStyle(sheet); // cellStyleDoub is the default cell style for doubles
		cellStyleDoub.cloneStyleFrom(dCellStyle);
		DataFormat df = createHelper.createDataFormat();
		String format = "#,##0";
		if (decimals.length() > 0) {
			format += "." + decimals;
		}
		cellStyleDoub.setDataFormat(df.getFormat(format));

		decimalFormats.put(j, cellStyleDoub);
		return cellStyleDoub;
	}
 

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]);
	}
}
 

/**
 * 创建 表头改变
 * 
 * @param entity
 * @param sheet
 * @param workbook
 * @param feildWidth
 */
public int createHeaderRow(ExportParams entity, Sheet sheet, Workbook workbook, int feildWidth) {
	Row row = sheet.createRow(0);
	row.setHeight(entity.getTitleHeight());
	createStringCell(row, 0, entity.getTitle(), getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
	for (int i = 1; i <= feildWidth; i++) {
		createStringCell(row, i, "", getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
	}
	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, feildWidth));
	if (entity.getSecondTitle() != null) {
		row = sheet.createRow(1);
		row.setHeight(entity.getSecondTitleHeight());
		CellStyle style = workbook.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		createStringCell(row, 0, entity.getSecondTitle(), style, null);
		for (int i = 1; i <= feildWidth; i++) {
			createStringCell(row, i, "", getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
		}
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, feildWidth));
		return 2;
	}
	return 1;
}
 

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.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style.setFont(font);
	}
}
 

/**
 * 创建一个新的样式，样式从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)
{
    CellStyle v_NewCellStyle = this.cellStylesByCopy.get(i_ID);
    
    if ( v_NewCellStyle == null )
    {
        v_NewCellStyle = this.workbook.createCellStyle();
        
        ExcelHelp.copyCellStyle(i_DataCell.getCellStyle(), v_NewCellStyle);
        
        Font v_FromFont = this.workbook.getFontAt(i_DataCell.getCellStyle().getFontIndex());
        Font v_NewFont  = this.workbook.createFont();
        ExcelHelp.copyFont(v_FromFont ,v_NewFont);
        
        v_NewCellStyle.setFont(v_NewFont);
        
        this.cellStylesByCopy.put(i_ID ,v_NewCellStyle);
    }
    
    return v_NewCellStyle;
}
 

private static Row createHeaders(Map<String, String> headerMap,
		Sheet sheet) {
	CellStyle style = sheet.getWorkbook().createCellStyle();
	style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
	style.setFillPattern(CellStyle.SOLID_FOREGROUND);
	style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

	Row header = sheet.createRow(0);
	int columnIndex = 0;
	for (Map.Entry<String, String> entry : headerMap.entrySet()) {
		String headerText = StringUtils.defaultString(entry.getValue());
		Cell cell = createCell(header, columnIndex);
		cell.setCellValue(headerText);
		cell.setCellStyle(style);
		sheet.autoSizeColumn(columnIndex);
		columnIndex++;
	}

	return header;
}
 

/**
 * XSSFRichTextString.utfDecode()<br/>
 * value.contains("_x")<br/>
 * Pattern.compile("_x([0-9A-Fa-f]{4})_");
 */
private static void export2007WithStyle(String filePath) {
	try {
		OutputStream os = new FileOutputStream(filePath);
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet(Globals.SHEETNAME);
		XSSFCell cell = sheet.createRow(0).createCell(0);
		cell.setCellValue(TestUtil.RICH_TEXT_STRINGS[0]
				+ escape(TestUtil.REGEX + TestUtil.RICH_TEXT_STRINGS[1]
						+ TestUtil.REGEX) + TestUtil.RICH_TEXT_STRINGS[2]);
		CellStyle style = sheet.getWorkbook().createCellStyle();
		XSSFFont font = wb.createFont();
		font.setColor(IndexedColors.BLUE.index);
		style.setFont(font);
		cell.setCellStyle(style);
		// richString.applyFont(font);
		wb.write(os);
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

@Override
public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
	CellStyle style = workbook.createCellStyle();
	style.setBorderLeft((short) 1); // 左边框
	style.setBorderRight((short) 1); // 右边框
	style.setBorderBottom((short) 1);
	style.setBorderTop((short) 1);
	style.setFillForegroundColor((short) 41); // 填充的背景颜色
	style.setFillPattern(CellStyle.SOLID_FOREGROUND); // 填充图案
	style.setAlignment(CellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	style.setDataFormat(STRING_FORMAT);
	if (isWarp) {
		style.setWrapText(true);
	}
	return style;
}
 

@Test
public void testRunReport() throws BirtException, IOException {

	removeEmptyRows = false;
	InputStream inputStream = runAndRenderReport("Borders2.rptdesign", "xlsx");
	assertNotNull(inputStream);
	try {
		
		XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );
		assertEquals( "Borders Test Report 2", workbook.getSheetAt(0).getSheetName());
		
		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( 4, firstNullRow(sheet));
		
		assertBorder( sheet, 1, 2, CellStyle.BORDER_MEDIUM, CellStyle.BORDER_MEDIUM, CellStyle.BORDER_MEDIUM, CellStyle.BORDER_MEDIUM );

		assertBorder( sheet, 1, 4, CellStyle.BORDER_MEDIUM, CellStyle.BORDER_MEDIUM, CellStyle.BORDER_MEDIUM, CellStyle.BORDER_MEDIUM );
		
	} finally {
		inputStream.close();
	}
}
 

/**
 * <p>This method attempts to find an existing CellStyle that matches the <code>cell</code>'s 
 * current style plus styles properties in <code>properties</code>. A new style is created if the
 * workbook does not contain a matching style.</p>
 * 
 * <p>Modifies the cell style of <code>cell</code> without affecting other cells that use the
 * same style.</p>
 * 
 * <p>This is necessary because Excel has an upper limit on the number of styles that it supports.</p>
 * 
 * <p>This function is more efficient than multiple calls to
 * {@link #setCellStyleProperty(org.apache.poi.ss.usermodel.Cell, String, Object)}
 * if adding multiple cell styles.</p>
 * 
 * <p>For performance reasons, if this is the only cell in a workbook that uses a cell style,
 * this method does NOT remove the old style from the workbook.
 * <!-- NOT IMPLEMENTED: Unused styles should be
 * pruned from the workbook with [@link #removeUnusedCellStyles(Workbook)] or
 * [@link #removeStyleFromWorkbookIfUnused(CellStyle, Workbook)]. -->
 * </p>
 *
 * @param cell The cell to change the style of
 * @param properties The properties to be added to a cell style, as {propertyName: propertyValue}.
 * @since POI 3.14 beta 2
 */
public static void setCellStyleProperties(Cell cell, Map<String, Object> properties) {
    Workbook workbook = cell.getSheet().getWorkbook();
    CellStyle originalStyle = cell.getCellStyle();
    CellStyle newStyle = null;
    Map<String, Object> values = getFormatProperties(originalStyle);
    putAll(properties, values);

    // index seems like what index the cellstyle is in the list of styles for a workbook.
    // not good to compare on!
    int numberCellStyles = workbook.getNumCellStyles();

    for (int i = 0; i < numberCellStyles; i++) {
        CellStyle wbStyle = workbook.getCellStyleAt(i);
        Map<String, Object> wbStyleMap = getFormatProperties(wbStyle);

        // the desired style already exists in the workbook. Use the existing style.
        if (wbStyleMap.equals(values)) {
            newStyle = wbStyle;
            break;
        }
    }

    // the desired style does not exist in the workbook. Create a new style with desired properties.
    if (newStyle == null) {
        newStyle = workbook.createCellStyle();
        setFormatProperties(newStyle, workbook, values);
    }

    cell.setCellStyle(newStyle);
}
 

private void printSheetContent(Sheet sheet) {
	// printColumnHeads(sheet);
	MergedRegionHelper mergedRegionHelper = new MergedRegionHelper(sheet);
	CellValueHelper cellValueHelper = new CellValueHelper(wb, cssRandom);
	out.format("<tbody>%n");
	Iterator<Row> rows = sheet.rowIterator();
	int rowIndex = 1;
	while (rows.hasNext()) {
		Row row = rows.next();
		out.format("  <tr style='height:%spx;'>%n", row.getHeight() / 15);
		// out.format("    <td class='%s'>%d</td>%n", ROW_HEAD_CLASS,
		// row.getRowNum() + 1);
		for (int i = firstColumn; i < endColumn; i++) {
			if (mergedRegionHelper.isNeedCreate(rowIndex, i)) {
				String content = "&nbsp;";
				CellStyle style = null;
				if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
					Cell cell = row.getCell(i);
					if (cell != null) {
						style = cell.getCellStyle();
						content = cellValueHelper.getHtmlValue(cell);
					}
				}
				if (mergedRegionHelper.isMergedRegion(rowIndex, i)) {
					Integer[] rowAndColSpan = mergedRegionHelper.getRowAndColSpan(rowIndex, i);
					out.format("    <td rowspan='%s' colspan='%s' class='%s' >%s</td>%n", rowAndColSpan[0], rowAndColSpan[1], styleName(style), content);
				} else {
					out.format("    <td class='%s'>%s</td>%n", styleName(style), content);
				}
			}

		}
		out.format("  </tr>%n");
		rowIndex++;
	}
	out.format("</tbody>%n");
}
 

@Override
public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
	CellStyle style = workbook.createCellStyle();
	style.setBorderLeft((short) 1); // 左边框
	style.setBorderRight((short) 1); // 右边框
	style.setBorderBottom((short) 1);
	style.setBorderTop((short) 1);
	style.setAlignment(CellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	style.setDataFormat(STRING_FORMAT);
	if (isWarp) {
		style.setWrapText(true);
	}
	return style;
}
 

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

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

public CellStyle createBorderCellStyle(Workbook workbook, boolean showBorder) {
	CellStyle style = workbook.createCellStyle();
	if (showBorder) {
		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;
}
 

ActionResult<Wo> execute(EffectivePerson effectivePerson, Boolean stream) throws Exception {
	try (EntityManagerContainer emc = EntityManagerContainerFactory.instance().create();
			Workbook workbook = new XSSFWorkbook();
			ByteArrayOutputStream output = new ByteArrayOutputStream()) {
		ActionResult<Wo> result = new ActionResult<>();
		Business business = new Business(emc);
		Sheet sheet = workbook.createSheet("loginRecord");
		Row row = sheet.createRow(0);
		row.createCell(0).setCellValue("name");
		row.createCell(1).setCellValue("lastLoginTime");
		row.createCell(2).setCellValue("lastLoginAddress");
		row.createCell(3).setCellValue("lastLoginClient");
		int line = 0;
		Cell cell = null;
		CellStyle dateCellStyle = workbook.createCellStyle();
		CreationHelper createHelper = workbook.getCreationHelper();
		dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat(DateTools.format_yyyyMMdd));
		for (Person o : this.list(business)) {
			row = sheet.createRow(++line);
			row.createCell(0).setCellValue(o.getName());
			cell = row.createCell(1);
			if (null == o.getLastLoginTime()) {
				cell.setCellValue("");
			} else {
				cell.setCellValue(o.getLastLoginTime());
				cell.setCellStyle(dateCellStyle);
			}
			row.createCell(2).setCellValue(o.getLastLoginAddress());
			row.createCell(3).setCellValue(o.getLastLoginClient());
		}
		String name = "loginRecord_" + DateTools.formatDate(new Date()) + ".xlsx";
		workbook.write(output);
		Wo wo = new Wo(output.toByteArray(), this.contentType(stream, name), this.contentDisposition(stream, name));
		result.setData(wo);
		return result;
	}
}
 

/**
 * @param cell
 * @return
 */
@SuppressWarnings("deprecation")
private int getNumWrappedLines(Cell cell) {
	if (cell.getCellTypeEnum() == CellType.STRING) {
		String val = cell.getStringCellValue();
		if (val == null || val.isEmpty()) {
			return 1;
		}
		CellStyle style = cell.getCellStyle();
		if (style == null || !style.getWrapText()) {
			return 1;
		}
		Font font = sheet.getWorkbook().getFontAt(style.getFontIndex());
		AttributedString astr = new AttributedString(val);
		java.awt.Font awtFont = new java.awt.Font(font.getFontName(), 0, font.getFontHeightInPoints());
		float cellWidth = sheet.getColumnWidth(cell.getColumnIndex())/ 256F * 5.5F;
		astr.addAttribute(TextAttribute.FONT, awtFont);
		FontRenderContext context = new FontRenderContext(null, true, true);
		java.awt.font.LineBreakMeasurer measurer = new java.awt.font.LineBreakMeasurer(astr.getIterator(), context);
		int pos = 0;
		int numLines = 0;
		while (measurer.getPosition() < val.length()) {
			pos = measurer.nextOffset(cellWidth);
			numLines++;
			measurer.setPosition(pos);
		}
		return numLines;
	} else {	// Not a string type
		return 1;
	}
}
 

/**
 * @param source
 *            the sheet to copy.
 * @param destSheet
 *            the sheet to create.
 * @param srcRow
 *            the row to copy.
 * @param destRow
 *            the row to create.
 * @param styleMap
 *
 */
private static void copyRow(HSSFSheet source, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, List<CellStyle> styleMap) {

	Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
	short dh = source.getDefaultRowHeight();
	if (srcRow.getHeight() != dh) {
		destRow.setHeight(srcRow.getHeight());
	}
	int j = srcRow.getFirstCellNum();
	if (j < 0) {
		j = 0;
	}
	for (; j <= srcRow.getLastCellNum(); j++) {
		HSSFCell oldCell = srcRow.getCell(j);
		XSSFCell newCell = destRow.getCell(j);
		if (oldCell != null) {
			if (newCell == null) {
				newCell = destRow.createCell(j);
			}
			copyCell(oldCell, newCell, styleMap);
			CellRangeAddress mergedRegion = getMergedRegion(source, srcRow.getRowNum(), (short) oldCell.getColumnIndex());

			if (mergedRegion != null) {

				CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(),
						mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());

				CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
				if (isNewMergedRegion(wrapper, mergedRegions)) {
					mergedRegions.add(wrapper);
					destSheet.addMergedRegion(wrapper.range);
				}
			}
		}
	}

}
 

private CellStyle setStyle() {
  CellStyle cellStyle = workbook.createCellStyle();
  Font font = workbook.createFont();
  font.setBoldweight(Font.BOLDWEIGHT_BOLD);
  cellStyle.setFont(font);
  return cellStyle;
}
 

/**
 * Adds cell rotation style.
 * @throws IOException If fails
 */
@Test
public void addCellRotation() throws IOException {
    try (final Workbook wbook = new XSSFWorkbook()) {
        final short expected = (short) 45;
        final CellStyle style = wbook.createCellStyle();
        new Rotation(expected).accept(style);
        MatcherAssert.assertThat(
            style.getRotation(),
            Matchers.equalTo(expected)
        );
    }
}
 

@Override
public CellStyle supply(Workbook workbook) {
    CellStyle style = workbook.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBorderRight(BorderStyle.THIN);
    style.setBorderTop(BorderStyle.THIN);
    style.setBorderLeft(BorderStyle.THIN);
    Font font = workbook.createFont();
    font.setBold(true);
    style.setFont(font);
    return style;
}