Java XSSFRichTextString類代碼示例

import org.apache.poi.xssf.usermodel.XSSFRichTextString; //導入依賴的package包/類
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 "";
}
 

import org.apache.poi.xssf.usermodel.XSSFRichTextString; //導入依賴的package包/類
/**
 * Tries to format the contents of the last contents appropriately based on the type of cell and the discovered
 * numeric format.
 *
 * @return
 */
String formattedContents() {
    switch (currentCell.getType()) {
    case "s": // string stored in shared table
        int idx = Integer.parseInt(lastContents);
        return new XSSFRichTextString(sst.getEntryAt(idx)).toString();
    case "inlineStr": // inline string (not in sst)
        return new XSSFRichTextString(lastContents).toString();
    case "str": //
        return lastContents;
    case "e": // error type
        return StringUtils.EMPTY;// "ERROR:  " + lastContents;
    case "n": // numeric type
        if (currentCell.getNumericFormat() != null && lastContents.length() > 0) {
            return dataFormatter.formatRawCellContents(Double.parseDouble(lastContents), currentCell.getNumericFormatIndex(),
                    currentCell.getNumericFormat());
        } else {
            return lastContents;
        }
    default:
        return lastContents;
    }
}
 

import org.apache.poi.xssf.usermodel.XSSFRichTextString; //導入依賴的package包/類
/**
 * 07版本複雜數據
 * @param rich
 * @return
 */
private String getXSSFRichString(XSSFRichTextString rich) {
    int nums = rich.numFormattingRuns();
    StringBuilder sb = new StringBuilder();
    String text = rich.toString();
    int currentIndex = 0, lastIndex = 0;
    for (int i = 1; i <= nums; i++) {
        sb.append("<span ");
        try {
            sb.append("class='font_" + getFontIndex(rich.getFontOfFormattingRun(i - 1)));
            sb.append("_");
            sb.append(cssRandom);
            sb.append("'");
        } catch (Exception e) {
        }
        sb.append(">");
        currentIndex = rich.getIndexOfFormattingRun(i) == -1 ? text.length()
            : rich.getIndexOfFormattingRun(i);
        sb.append(
            XmlEscapers.xmlContentEscaper().escape(text.substring(lastIndex, currentIndex)));
        sb.append("</span>");
        lastIndex = currentIndex;
    }
    return sb.toString();
}
 

import org.apache.poi.xssf.usermodel.XSSFRichTextString; //導入依賴的package包/類
/**
 * 07版本複雜數據
 * @param rich
 * @return
 */
private String getXSSFRichString(XSSFRichTextString rich) {
    int nums = rich.numFormattingRuns();
    StringBuilder sb = new StringBuilder();
    String text = rich.toString();
    int currentIndex = 0, lastIndex = 0;
    for (int i = 1; i <= nums; i++) {
        sb.append("<span ");
        try {
            sb.append("class='font_" + getFontIndex(rich.getFontOfFormattingRun(i - 1)));
            sb.append("_");
            sb.append(cssRandom);
            sb.append("'");
        } catch (Exception e) {
        }
        sb.append(">");
        currentIndex = rich.getIndexOfFormattingRun(i) == -1 ? text.length() : rich
            .getIndexOfFormattingRun(i);
        sb.append(XmlEscapers.xmlContentEscaper().escape(
            text.substring(lastIndex, currentIndex)));
        sb.append("</span>");
        lastIndex = currentIndex;
    }
    return sb.toString();
}
 

import org.apache.poi.xssf.usermodel.XSSFRichTextString; //導入依賴的package包/類
/**
 * Gets the font index of the <code>Font</code> in use at the specified
 * position in the given <code>RichTextString</code>.
 * @param richTextString The <code>RichTextString</code>.
 * @param fmtIndex The 0-based index of the formatting run.
 * @return The font index: If HSSF, a <code>short</code>.  If XSSF, an
 *    <code>XSSFFont</code>.
 */
public static Object getFontAtIndex(RichTextString richTextString, int fmtIndex)
{
   if (richTextString instanceof HSSFRichTextString)
   {
      // Returns a short.
      return ((HSSFRichTextString) richTextString).getFontAtIndex(fmtIndex);
   }
   else if (richTextString instanceof XSSFRichTextString)
   {
      try
      {
         // Instead of returning null, getFontAtIndex (eventually) throws a
         // NullPointerException.  It extracts a "CTRElt" from an array, and
         // it extracts a "CTRPrElt" from the "CTRElt".  The "CTRprElt" can
         // be null if there is no font at the formatting run.  Then, when
         // creating a "CTFont", it calls a method on the null "CTRPrElt".
         // Return an XSSFFont.
         return ((XSSFRichTextString) richTextString).getFontAtIndex(fmtIndex);
      }
      catch (NullPointerException e)
      {
         // Detect this case and return null.
         if (DEBUG)
            System.err.println("    NullPointerException caught!");
         return null;
      }
   }
   else
      throw new IllegalArgumentException("Unexpected RichTextString type: " +
         richTextString.getClass().getName() + ": " + richTextString.getString());
}
 

import org.apache.poi.xssf.usermodel.XSSFRichTextString; //導入依賴的package包/類
@SuppressWarnings("resource")
@Test
public void testAddRow() {
  Calendar cal = Calendar.getInstance();
  Date date = new Date();
  writer.addRow("def");
  writer
      .addRow(null, true, cal, date, 1.1, new HSSFRichTextString("Hello!"),
          new XSSFRichTextString("World."), new HSSFWorkbook()
              .getCreationHelper().createHyperlink(HyperlinkType.URL),
          123, "abc");
  assertEquals("def", writer.getWorkbook().getSheetAt(0).rowIterator().next()
      .cellIterator().next().getStringCellValue());
  WorkbookWriter.openXLSX().addRow(new HSSFRichTextString("Hello!"),
      new XSSFRichTextString("World."));
  WorkbookWriter.openXLS().addRow(new HSSFRichTextString("Hello!"),
      new XSSFRichTextString("World."));

}
 

import org.apache.poi.xssf.usermodel.XSSFRichTextString; //導入依賴的package包/類
private void createHeader(Map<String, CellStyle> styles,
                          XSSFSheet sheet,
                          List<String> header) {
    final XSSFRow rowhead = sheet.createRow(0);

    int i = 0;
    for (String headerColumn : header) {
        rowhead.createCell(i).setCellValue(new XSSFRichTextString(headerColumn));
        rowhead.getCell(i).setCellStyle(styles.get("header"));
        i++;
    }
}
 

import org.apache.poi.xssf.usermodel.XSSFRichTextString; //導入依賴的package包/類
/**
 * Resolve the given value with respect to whether it is a reference to element in shared strings table.
 * Also decode the final value.
 *
 * @param value Value read in element.
 * @param lookupNextValueInSST Whether the value is an index into the shared strings table.
 * @return
 */
private String resolveValue(String value, boolean lookupNextValueInSST) {
  if(lookupNextValueInSST) {
    int idx = (int)Double.parseDouble(value);
    return new XSSFRichTextString(sst.getEntryAt(idx)).toString();
  }

  return new XSSFRichTextString(value).toString();
}
 

import org.apache.poi.xssf.usermodel.XSSFRichTextString; //導入依賴的package包/類
public static void writeExcel(HttpServletResponse response,List<String> list) throws Exception {
	response.setContentType("application/vnd.ms-excel");//文件格式,此處設置為excel
	response.setHeader("Content-Disposition","attachment;filename=file.xls");//此處設置了下載文件的默認名稱
	ServletOutputStream sos = response.getOutputStream();
    //創建一個新的excel
	XSSFWorkbook wb = new XSSFWorkbook();//XSSFWorkbook
	/**
	 * 采用現成Excel模板
	 * 用這種方式得先保證每個cell有值,不然會報空指針
	 * 有時我們用row.getCell(i)會得到null,那麽此時就要用Iterator<Cell> it = row.cellIterator();
	 * XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File("D://a.xlsx")));
	 * XSSFSheet sheet = wb.getSheet("Sheet1");
	 * row[i] = sheet.getRow(i);
	 * headerCell[j] = row[i].getCell(j);
	 */
	//創建sheet頁
	XSSFSheet sheet = wb.createSheet("sheet1");//sheet名
	//創建行數
	XSSFRow[] row = new XSSFRow[list.size()];
	//插入數據
	for (int i = 0; i < row.length; i++) {
		row[i] = sheet.createRow(i);
		sheet.setDefaultColumnWidth(30);//設置列的長度
		String info[] = list.get(i).split(",");
		XSSFCell[] headerCell = new XSSFCell[info.length];
		for (int j = 0; j < headerCell.length; j++) {
			headerCell[j] = row[i].createCell(j);
			headerCell[j].setCellValue(new XSSFRichTextString(info[j]));
			/**設置模板樣式*/
			//headerCell[j].setCellStyle(setStyle(wb));
		}
	}
	wb.write(sos);
	wb.close();
    sos.flush();
    sos.close();
    response.flushBuffer();
}
 

import org.apache.poi.xssf.usermodel.XSSFRichTextString; //導入依賴的package包/類
private Font getFontForFormattingRun(RichTextString richText, int i) {
    if (richText instanceof HSSFRichTextString) {
        HSSFRichTextString hssfRichText = (HSSFRichTextString) richText;
        return ((PoiWorkbook.PoiHssfWorkbook) getWorkbook()).getFont(hssfRichText.getFontOfFormattingRun(i)).getFont();
    } else {
        return getWorkbook().getFont(((XSSFRichTextString) richText).getFontOfFormattingRun(i)).getFont();
    }
}
 

import org.apache.poi.xssf.usermodel.XSSFRichTextString; //導入依賴的package包/類
@Override
public void handleInlineString(XSSFCellStyle style, String inlineString) {
    XSSFRichTextString rtsi = new XSSFRichTextString(inlineString);
    rawValues.add(rtsi.toString().toUpperCase());
    formattedValues.add('"' + rtsi.toString() + '"');

}
 

import org.apache.poi.xssf.usermodel.XSSFRichTextString; //導入依賴的package包/類
@Override
public void handleSharedStringsTableIndex(XSSFCellStyle style, String sharedStringsTableIndex) {
    try {
        int idx = Integer.parseInt(sharedStringsTableIndex);
        XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
        rawValues.add(rtss.toString());
        formattedValues.add('"' + rtss.toString() + '"');

    } catch (NumberFormatException ex) {
        rawValues.add("ERROR");
        formattedValues.add("ERROR");
    }

}
 

import org.apache.poi.xssf.usermodel.XSSFRichTextString; //導入依賴的package包/類
public void writeCoverSheet(String projectName,String creatorName,
		String creatorEmail,String organizationName,String toolInfo) {
	
	sheet.getRow(ROW_10).getCell(COL_D).setCellValue(
			new XSSFRichTextString(projectName));
	sheet.getRow(ROW_10).getCell(COL_F).setCellValue(
			new XSSFRichTextString(DateUtil.getCurrentTime("%1$tY-%1$tm-%1$te (%1$ta)")));
	sheet.getRow(ROW_11).getCell(COL_D).setCellValue(
			new XSSFRichTextString(creatorEmail));
	sheet.getRow(ROW_12).getCell(COL_D).setCellValue(
			new XSSFRichTextString(organizationName));
	sheet.getRow(ROW_13).getCell(COL_D).setCellValue(
			new XSSFRichTextString(toolInfo));
}
 

import org.apache.poi.xssf.usermodel.XSSFRichTextString; //導入依賴的package包/類
/**
 * (non-Javadoc)
 * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, java.lang.String, java.lang.String)
 */
@Override
public void endElement(String uri, String localName, String qName) throws SAXException {
	// 處理excel的行
	if ("row".equals(qName)) {
		if (cacheSize > rowCache.size() && cRow.getCells() != null) {
			rowCache.add(cRow);
		} else {
			rowsHandler.handleRows(rowCache);
			rowCache.clear();
		}
		lastElementName = null;
		if (monitor.isCanceled()) {
			throw new OperationCanceledException();
		}
		monitor.worked(1);
	} else if ("t".equals(qName) && !isSheredString) {
		cCell.setCellConentent(lastCellContent.toString().trim());
		lastCellContent.delete(0, lastCellContent.length());
	} else if ("v".equals(qName)) {
		int idx = -1;
		try {
			idx = Integer.parseInt(lastCellContent.toString().trim());
			XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
			cCell.setCellConentent(rtss.toString());
			lastCellContent.delete(0, lastCellContent.length());
		} catch (NumberFormatException e) {
		}
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFRichTextString; //導入依賴的package包/類
/**
 * Returns the contents of the cell, with no formatting applied
 *
 * @return
 */
String unformattedContents() {
    switch (currentCell.getType()) {
    case "s": // string stored in shared table
        int idx = Integer.parseInt(lastContents);
        return new XSSFRichTextString(sst.getEntryAt(idx)).toString();
    case "inlineStr": // inline string (not in sst)
        return new XSSFRichTextString(lastContents).toString();
    default:
        return lastContents;
    }
}