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

/**
 * 创建文本类型的Cell
 * 
 * @param row
 * @param index
 * @param text
 * @param style
 * @param entity
 * @param workbook
 */
private static void createStringCell(Row row, int index, String text,
		ExcelExportEntity entity, Workbook workbook) {
	Cell cell = row.createCell(index);
	switch (entity.getType()) {
	case 1:
		RichTextString Rtext = workbook instanceof HSSFWorkbook ? new HSSFRichTextString(
				text) : new XSSFRichTextString(text);
		cell.setCellValue(Rtext);
		break;
	case 2:
		cell.setCellType(Cell.CELL_TYPE_FORMULA);
		cell.setCellFormula(entity.getCellFormula());
		break;
	}
}
 

/**
 * 03版本复杂数据
 * @param rich
 * @return
 */
private String getHSSFRichString(HSSFRichTextString rich) {
    int nums = rich.numFormattingRuns();
    StringBuilder sb = new StringBuilder();
    String text = rich.toString();
    int currentIndex = 0;
    sb.append(text.substring(0, rich.getIndexOfFormattingRun(0)));
    for (int i = 0; i < nums; i++) {
        sb.append("<span ");
        sb.append("class='font_" + rich.getFontOfFormattingRun(i));
        sb.append("_");
        sb.append(cssRandom);
        sb.append("'>");
        currentIndex = rich.getIndexOfFormattingRun(i);
        if (i < nums - 1) {
            sb.append(XmlEscapers.xmlContentEscaper().escape(
                text.substring(currentIndex, rich.getIndexOfFormattingRun(i + 1))));
        } else {
            sb.append(XmlEscapers.xmlContentEscaper().escape(
                text.substring(currentIndex, text.length())));
        }
        sb.append("</span>");
    }
    return sb.toString();
}
 

protected void setColumnData(final Map<String, String> keywordsValueMap, final ColumnTemplate columnTemplate, final HSSFRow row, final NormalColumn normalColumn, final TableView tableView, final int order) {

        for (final int columnNum : columnTemplate.columnTemplateMap.keySet()) {
            final HSSFCell cell = row.createCell(columnNum);
            final String template = columnTemplate.columnTemplateMap.get(columnNum);

            String value = null;
            if (KEYWORD_ORDER.equals(template)) {
                value = String.valueOf(order);

            } else {
                value = getColumnValue(keywordsValueMap, normalColumn, tableView, template);
            }

            try {
                final double num = Double.parseDouble(value);
                cell.setCellValue(num);

            } catch (final NumberFormatException e) {
                final HSSFRichTextString text = new HSSFRichTextString(value);
                cell.setCellValue(text);
            }
        }
    }
 

public static String getCellValue(final HSSFSheet sheet, final int r, final int c) {
    final HSSFRow row = sheet.getRow(r);

    if (row == null) {
        return null;
    }

    final HSSFCell cell = row.getCell(c);

    if (cell == null) {
        return null;
    }

    final HSSFRichTextString cellValue = cell.getRichStringCellValue();

    return cellValue.toString();
}
 

@Override
protected void printHeader(List<String> header, ByteArrayOutputStream out) {
	wb = new HSSFWorkbook();
       sheet = wb.createSheet("NextReports");

       HSSFRow headerRow = sheet.createRow(0);
       int col = 0;        
	if (header != null) {
		for (String s : header) {
			HSSFCell cell = headerRow.createCell(col);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			if (s == null) {
				s = "";
			}
			cell.setCellValue(new HSSFRichTextString(s));
			col++;
		}
	}		
}
 

public static CellLocation findCell(final HSSFSheet sheet, final String str, final int colNum) {
    for (int rowNum = sheet.getFirstRowNum(); rowNum < sheet.getLastRowNum() + 1; rowNum++) {
        final HSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            continue;
        }

        final HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }
        final HSSFRichTextString cellValue = cell.getRichStringCellValue();

        if (!Check.isEmpty(cellValue.getString())) {
            if (cellValue.getString().equals(str)) {
                return new CellLocation(rowNum, (short) colNum);
            }
        }
    }

    return null;
}
 

public static Integer findMatchColumn(final HSSFRow row, final String str) {
    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
        final HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }

        if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
            continue;
        }

        final HSSFRichTextString cellValue = cell.getRichStringCellValue();

        if (cellValue.getString().matches(str)) {
            return Integer.valueOf(colNum);
        }
    }

    return null;
}
 

public static Integer findColumn(final HSSFRow row, final String str) {
    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
        final HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }

        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            final HSSFRichTextString cellValue = cell.getRichStringCellValue();

            if (str.equals(cellValue.getString())) {
                return Integer.valueOf(colNum);
            }
        }
    }

    return null;
}
 

public static Integer findColumn(HSSFRow row, String str) {
	for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
		HSSFCell cell = row.getCell(colNum);

		if (cell == null) {
			continue;
		}

		if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
			HSSFRichTextString cellValue = cell.getRichStringCellValue();

			if (str.equals(cellValue.getString())) {
				return Integer.valueOf(colNum);
			}
		}
	}

	return null;
}
 

protected HSSFRichTextString getRichTextString(JRStyledText styledText, short forecolor, JRFont defaultFont, Locale locale) {
	String text = styledText.getText();
	HSSFRichTextString richTextStr = new HSSFRichTextString(text);
	int runLimit = 0;
	AttributedCharacterIterator iterator = styledText.getAttributedString().getIterator();

	while(runLimit < styledText.length() && (runLimit = iterator.getRunLimit()) <= styledText.length()) {
		Map<Attribute,Object> attributes = iterator.getAttributes();
		JRFont runFont = attributes.isEmpty()? defaultFont : new JRBaseFont(attributes);
		short runForecolor = attributes.get(TextAttribute.FOREGROUND) != null  
			? getWorkbookColor((Color)attributes.get(TextAttribute.FOREGROUND)).getIndex() 
			: forecolor;
		HSSFFont font = getLoadedFont(runFont, runForecolor, attributes, locale);
		richTextStr.applyFont(iterator.getIndex(), runLimit, font);
		iterator.setIndex(runLimit);
	}
	return richTextStr;
}
 

protected HSSFRichTextString getRichTextString(JRStyledText styledText, short forecolor, JRFont defaultFont, Locale locale)
{
	String text = styledText.getText();
	HSSFRichTextString richTextStr = new HSSFRichTextString(text);
	int runLimit = 0;
	AttributedCharacterIterator iterator = styledText.getAttributedString().getIterator();

	while(runLimit < styledText.length() && (runLimit = iterator.getRunLimit()) <= styledText.length())
	{
		Map<Attribute,Object> attributes = iterator.getAttributes();
		JRFont runFont = attributes.isEmpty()? defaultFont : new JRBaseFont(attributes);
		short runForecolor = attributes.get(TextAttribute.FOREGROUND) != null ? 
				getWorkbookColor((Color)attributes.get(TextAttribute.FOREGROUND)).getIndex() :
				forecolor;
		HSSFFont font = getLoadedFont(runFont, runForecolor, attributes, locale);
		richTextStr.applyFont(iterator.getIndex(), runLimit, font);
		iterator.setIndex(runLimit);
	}
	return richTextStr;
}
 

public static Integer findMatchColumn(HSSFRow row, String str) {
	for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
		HSSFCell cell = row.getCell(colNum);

		if (cell == null) {
			continue;
		}

		if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING) {
			continue;
		}

		HSSFRichTextString cellValue = cell.getRichStringCellValue();

		if (cellValue.getString().matches(str)) {
			return Integer.valueOf(colNum);
		}
	}

	return null;
}
 

public String getHtmlValue(Cell cell) {
    if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()
        || Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        return cell.getStringCellValue();
    } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
        if (cell.getRichStringCellValue().numFormattingRuns() == 0) {
            return XmlEscapers.xmlContentEscaper().escape(cell.getStringCellValue());
        } else if (is07) {
            return getXSSFRichString((XSSFRichTextString) cell.getRichStringCellValue());
        } else {
            return getHSSFRichString((HSSFRichTextString) cell.getRichStringCellValue());
        }
    }
    return "";
}
 

public static CellLocation findCell(HSSFSheet sheet, String str, int colNum) {
	for (int rowNum = sheet.getFirstRowNum(); rowNum < sheet
			.getLastRowNum() + 1; rowNum++) {
		HSSFRow row = sheet.getRow(rowNum);
		if (row == null) {
			continue;
		}

		HSSFCell cell = row.getCell(colNum);

		if (cell == null) {
			continue;
		}
		HSSFRichTextString cellValue = cell.getRichStringCellValue();

		if (!Check.isEmpty(cellValue.getString())) {
			if (cellValue.getString().equals(str)) {
				return new CellLocation(rowNum, (short) colNum);
			}
		}
	}

	return null;
}
 

@Override
protected void writeDirectTestData(final ERTable table, final Map<NormalColumn, String> data, final String database) {
    final HSSFRow row = sheet.createRow(rowNum++);

    int col = 0;

    for (final NormalColumn column : table.getExpandedColumns()) {
        final HSSFCell cell = row.createCell(col++);

        final String value = Format.null2blank(data.get(column));

        if (value == null || "null".equals(value.toLowerCase())) {

        } else {
            cell.setCellValue(new HSSFRichTextString(value));
        }
    }
}
 

public static String getCellValue(HSSFSheet sheet, int r, int c) {
	HSSFRow row = sheet.getRow(r);
	
	if (row == null) {
		return null;
	}
	
	HSSFCell cell = row.getCell(c);
	
	if (cell == null) {
		return null;
	}
	
	HSSFRichTextString cellValue = cell.getRichStringCellValue();

	return cellValue.toString();
}
 

@Test
public void exportExcelWithStyle() {
	try {
		String filePath = TestUtil.DOC_PATH + File.separator
				+ Globals.EXPORT_PRODUCT;
		OutputStream os = new FileOutputStream(filePath);
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(Globals.SHEETNAME);
		HSSFRichTextString richString = new HSSFRichTextString(
				TestUtil.RICH_TEXT_STRING);
		HSSFFont font = wb.createFont();
		font.setColor(IndexedColors.BLUE.index);
		richString.applyFont(font);
		sheet.createRow(0).createCell(0).setCellValue(richString);
		wb.write(os);
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

/**
 * 03版本复杂数据
 * 
 * @param rich
 * @return
 */
private String getHSSFRichString(HSSFRichTextString rich) {
	int nums = rich.numFormattingRuns();
	StringBuilder sb = new StringBuilder();
	String text = rich.toString();
	int currentIndex = 0;
	sb.append(text.substring(0, rich.getIndexOfFormattingRun(0)));
	for (int i = 0; i < nums; i++) {
		sb.append("<span ");
		sb.append("class='font_" + rich.getFontOfFormattingRun(i));
		sb.append("_");
		sb.append(cssRandom);
		sb.append("'>");
		currentIndex = rich.getIndexOfFormattingRun(i);
		if (i < nums - 1) {
			sb.append(XmlEscapers.xmlContentEscaper().escape(text.substring(currentIndex, rich.getIndexOfFormattingRun(i + 1))));
		} else {
			sb.append(XmlEscapers.xmlContentEscaper().escape(text.substring(currentIndex, text.length())));
		}
		sb.append("</span>");
	}
	return sb.toString();
}
 

@Override
protected void writeDirectTestData(ERTable table,
		Map<NormalColumn, String> data, String database) {
	HSSFRow row = this.sheet.createRow(this.rowNum++);

	int col = 0;

	for (NormalColumn column : table.getExpandedColumns()) {
		HSSFCell cell = row.createCell(col++);

		String value = Format.null2blank(data.get(column));

		if (value == null || "null".equals(value.toLowerCase())) {

		} else {
			cell.setCellValue(new HSSFRichTextString(value));
		}
	}
}
 

/**
 * 03版本复杂数据
 * 
 * @param rich
 * @return
 */
private String getHSSFRichString(HSSFRichTextString rich) {
	int nums = rich.numFormattingRuns();
	StringBuilder sb = new StringBuilder();
	String text = rich.toString();
	int currentIndex = 0;
	sb.append(text.substring(0, rich.getIndexOfFormattingRun(0)));
	for (int i = 0; i < nums; i++) {
		sb.append("<span ");
		sb.append("class='font_" + rich.getFontOfFormattingRun(i));
		sb.append("_");
		sb.append(cssRandom);
		sb.append("'>");
		currentIndex = rich.getIndexOfFormattingRun(i);
		if (i < nums - 1) {
			sb.append(XmlEscapers.xmlContentEscaper().escape(text.substring(currentIndex, rich.getIndexOfFormattingRun(i + 1))));
		} else {
			sb.append(XmlEscapers.xmlContentEscaper().escape(text.substring(currentIndex, text.length())));
		}
		sb.append("</span>");
	}
	return sb.toString();
}
 

@Override
public SpreadsheetExporter addHeader(String... values) {
    CellStyle cellStyle = createHeaderStyle();
    Row headerRow = dataSheet.createRow(rowCount++);
    for (int i = 0; i < values.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(new HSSFRichTextString(values[i]));
    }
    return this;
}
 

/**
 * Adds a cell to the row
 * @param row the row
 * @param cellStyle cell style
 * @param index index of the cell
 * @param value value to be added
 */
private static void addCell(HSSFRow row, HSSFCellStyle cellStyle, int index, String value) {
	HSSFCell cell = row.createCell(index);
	if (cellStyle != null) {
		cell.setCellStyle(cellStyle);
	}
	cell.setCellValue(new HSSFRichTextString(value));
}
 

@Override
public SpreadsheetExporter addHeader(String... values) {
    CellStyle cellStyle = createHeaderStyle();
    Row headerRow = dataSheet.createRow(rowCount++);
    for (int i = 0; i < values.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(new HSSFRichTextString(values[i]));
    }
    return this;
}
 

@Override
protected void writeRepeatTestData(ERTable table,
		RepeatTestData repeatTestData, String database) {

	for (int i = 0; i < repeatTestData.getTestDataNum(); i++) {
		HSSFRow row = this.sheet.createRow(this.rowNum++);

		int col = 0;

		for (NormalColumn column : table.getExpandedColumns()) {
			HSSFCell cell = row.createCell(col++);

			RepeatTestDataDef repeatTestDataDef = repeatTestData
					.getDataDef(column);

			String value = this.getMergedRepeatTestDataValue(i,
					repeatTestDataDef, column);

			if (value == null || "null".equals(value.toLowerCase())) {

			} else {
				cell.setCellValue(new HSSFRichTextString(value));
			}
		}
	}

}
 

/**
 * 既にコメントが設定されているときのコメントの装飾を設定する。
 * 既存のコメントの装飾をコピーするが、そのとき、１つ目のフォント設定のみとする。
 * 
 * @param toRichText コピー先
 * @param fromrichText コピー元
 */
protected void copyCommentFormat(final RichTextString toRichText, final RichTextString fromrichText) {
    
    if(toRichText instanceof XSSFRichTextString) {
        toRichText.applyFont(((XSSFRichTextString)fromrichText).getFontOfFormattingRun(0));
        
    } else if(toRichText instanceof HSSFRichTextString) {
        toRichText.applyFont(((HSSFRichTextString)fromrichText).getFontOfFormattingRun(0));
        
    } else {
        logger.warn("not suuported exdcel format comment : {}", toRichText.getClass().getName());
    }
    
}
 

public static String getCellValue(HSSFSheet sheet, CellLocation location) {
	HSSFRow row = sheet.getRow(location.r);
	HSSFCell cell = row.getCell(location.c);

	HSSFRichTextString cellValue = cell.getRichStringCellValue();

	return cellValue.toString();
}
 

public static void setCellValue(HSSFSheet sheet, CellLocation location,
		String value) {
	HSSFRow row = sheet.getRow(location.r);
	HSSFCell cell = row.getCell(location.c);

	HSSFRichTextString text = new HSSFRichTextString(value);
	cell.setCellValue(text);
}
 

protected void setColumnData(Map<String, String> keywordsValueMap,
		ColumnTemplate columnTemplate, HSSFRow row,
		NormalColumn normalColumn, TableView tableView, int order) {

	for (int columnNum : columnTemplate.columnTemplateMap.keySet()) {
		HSSFCell cell = row.createCell(columnNum);
		String template = columnTemplate.columnTemplateMap.get(columnNum);

		String value = null;
		if (KEYWORD_ORDER.equals(template)) {
			value = String.valueOf(order);

		} else {
			value = this.getColumnValue(keywordsValueMap, normalColumn,
					tableView, template);
		}

		try {
			double num = Double.parseDouble(value);
			cell.setCellValue(num);

		} catch (NumberFormatException e) {
			HSSFRichTextString text = new HSSFRichTextString(value);
			cell.setCellValue(text);
		}
	}
}
 

@Override
protected void writeTableHeader(ERDiagram diagram, ERTable table) {
	String sheetName = table.getPhysicalName();
	this.sheet = this.workbook.createSheet(sheetName);

	this.rowNum = 0;
	HSSFRow row = this.sheet.createRow(this.rowNum++);

	int col = 0;

	for (NormalColumn column : table.getExpandedColumns()) {
		HSSFCell cell = row.createCell(col++);
		cell.setCellValue(new HSSFRichTextString(column.getPhysicalName()));
	}
}
 

/**
 * 构建头部
 */
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 ++ ;
}