org.apache.poi.xssf.usermodel.XSSFRichTextString 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;
	}
}
 

@Override
public void endElement(String uri, String localName, String name)
        throws SAXException {
    // Process the last contents as required.
    // Do now, as characters() may be called more than once
    if(nextIsString) {
        int idx = Integer.parseInt(lastContents);
        lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
        nextIsString = false;
    }

    // v => contents of a cell
    // Output after we've seen the string contents
    if("v".equals(name)) {
        System.out.println(lastContents);
    }
}
 

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 "";
}
 

/**
 * 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();
}
 

@Test
public void testExcel() throws IOException {
	String filepath = "E:\\ConvertTester\\excel\\abcd.xlsx";
	File originFile = new File(filepath);
	InputStream in = new FileInputStream(originFile);
	XSSFWorkbook workbook = new XSSFWorkbook(in);
	XSSFSheet sheet = workbook.createSheet("testSheet");

	XSSFDrawing drawing = sheet.createDrawingPatriarch();
	
	XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, (short) 2, 4, (short) 13, 26);

	XSSFTextBox textbox = drawing.createTextbox(anchor);
	XSSFRichTextString rtxt = new XSSFRichTextString("ekozhan");
	XSSFFont font = workbook.createFont();
	font.setColor((short) 27);
	font.setBold(true);
	font.setFontHeightInPoints((short) 192);
	font.setFontName("Verdana");
	rtxt.applyFont(font);
	textbox.setText(rtxt);
	textbox.setLineStyle(XSSFShape.EMU_PER_POINT);
	textbox.setNoFill(true);
	workbook.write(new FileOutputStream(filepath));
	workbook.close();
}
 

/**
 * 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();
}
 

/**
 * 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();
}
 

/**
 * 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;
    }
}
 

/**
 * 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;
    }
}
 

/**
 * 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;
    }
}
 

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

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

/**
 * (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) {
		}
	}
}
 

/**
 * 构建excel的表头
 * 
 * @param filename
 * @param headerList
 */
private void buildExcelHead(String filename, HSSFWorkbook workbook) {
    // Initialize
    List<String> headerList = Lists.newArrayList();
    for (Object[] os : annotationList) {
        String t = ((ExcelField) os[0]).title();
        headerList.add(t);
    }

    sheet = workbook.createSheet("导出数据");

    // Create header
    Row headerRow = sheet.createRow(rownum++);
    headerRow.setHeightInPoints(16);
    for (int i = 0; i < headerList.size(); i++) {
        Cell cell = headerRow.createCell(i);
        String[] ss = StringUtils.split(headerList.get(i), "**", 2);
        if (ss.length == 2) {
            cell.setCellValue(ss[0]);
            Comment comment = sheet.createDrawingPatriarch().createCellComment(
                    new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
            comment.setString(new XSSFRichTextString(ss[1]));
            cell.setCellComment(comment);
        } else {
            cell.setCellValue(headerList.get(i));
        }
        sheet.autoSizeColumn(i);
    }
    for (int i = 0; i < headerList.size(); i++) {
        int colWidth = sheet.getColumnWidth(i) * 2;
        sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
    }
}
 

/**
 * Get an item from the shared string table
 * 
 * @param idx index of the entry
 * @return entry
 * 
 */
@Override
public RichTextString getItemAt(int idx) {
	try {
		return new XSSFRichTextString(this.getString(idx));
	} catch (IOException e) {
		LOG.error("Cannot read from temporary shared String table. Exception: " + e);
	}
	return new XSSFRichTextString("");
}
 

/**
 * 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;
    }
}
 

/**
 * 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();
}
 

@Test
public void testFastExcelRendering() throws Exception {
  URL resource = getClass().getResource( "rich-text-sample1.prpt" );
  ResourceManager mgr = new ResourceManager();
  MasterReport report = (MasterReport) mgr.createDirectly( resource, MasterReport.class ).getResource();
  report.getReportConfiguration()
      .setConfigProperty( ClassicEngineCoreModule.COMPLEX_TEXT_CONFIG_OVERRIDE_KEY, "true" );
  report.getReportHeader().getElement( 0 ).getStyle().setStyleProperty( TextStyleKeys.DIRECTION, TextDirection.LTR );
  report.getReportHeader().getElement( 1 ).getStyle().setStyleProperty( TextStyleKeys.DIRECTION, TextDirection.RTL );
  report.getReportHeader().removeElement( 0 );
  report.getReportHeader().getStyle().setStyleProperty( ElementStyleKeys.BACKGROUND_COLOR, Color.YELLOW );
  report.getReportFooter().clear();

  ExpressionRuntime runtime =
      new GenericExpressionRuntime( new DefaultTableModel(), 0, new DefaultProcessingContext( report ) );

  RichTextStyleResolver resolver = new RichTextStyleResolver( runtime.getProcessingContext(), report );
  resolver.resolveRichTextStyle( report );

  XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
  ExcelColorProducer colorProducer = new StaticExcelColorSupport();
  ExcelFontFactory ff = new ExcelFontFactory( hssfWorkbook, colorProducer );
  CreationHelper ch = hssfWorkbook.getCreationHelper();
  FastExcelTextExtractor te = new FastExcelTextExtractor( colorProducer, ff, ch );

  Element element = report.getReportHeader().getElement( 0 );
  Object compute = te.compute( element, runtime );
  assertTrue( compute instanceof RichTextString );
  XSSFRichTextString rt = (XSSFRichTextString) compute;
  assertEquals( 4, rt.numFormattingRuns() );
}
 

@Test
public void testExcelRendering() throws Exception {
  URL resource = getClass().getResource( "rich-text-sample1.prpt" );
  ResourceManager mgr = new ResourceManager();
  MasterReport report = (MasterReport) mgr.createDirectly( resource, MasterReport.class ).getResource();
  report.getReportConfiguration()
      .setConfigProperty( ClassicEngineCoreModule.COMPLEX_TEXT_CONFIG_OVERRIDE_KEY, "true" );
  report.getReportHeader().getElement( 0 ).getStyle().setStyleProperty( TextStyleKeys.DIRECTION, TextDirection.LTR );
  report.getReportHeader().getElement( 1 ).getStyle().setStyleProperty( TextStyleKeys.DIRECTION, TextDirection.RTL );
  report.getReportHeader().removeElement( 0 );
  report.getReportHeader().getStyle().setStyleProperty( ElementStyleKeys.BACKGROUND_COLOR, Color.YELLOW );
  report.getReportFooter().clear();

  LogicalPageBox logicalPageBox = DebugReportRunner.layoutPage( report, 0 );

  RenderNode second = MatchFactory.findElementByName( logicalPageBox, "second" );
  assertTrue( second instanceof RenderBox );

  ExcelOutputProcessorMetaData metaData =
      new ExcelOutputProcessorMetaData( ExcelOutputProcessorMetaData.PAGINATION_FULL );
  metaData.initialize( report.getConfiguration() );

  XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
  ExcelColorProducer colorProducer = new StaticExcelColorSupport();
  ExcelFontFactory ff = new ExcelFontFactory( hssfWorkbook, colorProducer );
  CreationHelper ch = hssfWorkbook.getCreationHelper();
  ExcelTextExtractor te = new ExcelTextExtractor( metaData, colorProducer, ch, ff );

  Object compute = te.compute( (RenderBox) second );
  assertTrue( compute instanceof RichTextString );
  XSSFRichTextString rt = (XSSFRichTextString) compute;
  assertEquals( 4, rt.numFormattingRuns() );
}
 

private void doSetInnerSpan(Cell cell, Td td) {
    if (td.getFonts() == null || td.getFonts().isEmpty()) {
        return;
    }
    RichTextString richText = isHssf ? new HSSFRichTextString(td.getContent()) : new XSSFRichTextString(td.getContent());
    for (com.github.liaochong.myexcel.core.parser.Font font : td.getFonts()) {
        Font f = FontStyle.getFont(font.getStyle(), fontMap, () -> workbook.createFont(), customColor);
        richText.applyFont(font.getStartIndex(), font.getEndIndex(), f);
    }
    cell.setCellValue(richText);
}
 

/**
 * 根据数据类型获取数据
 *
 * @param value
 * @return
 */
private String getCellValue(String value) {
    switch (cellFormatStr) {
        case INLINESTR:
            return new XSSFRichTextString(value).toString();
        default:
            return String.valueOf(value);
    }
}
 

/**
 * Returns the contents of the cell, with no formatting applied
 *
 * @return
 */
String unformattedContents() {
  switch(currentCell.getType()) {
    case "s":           //string stored in shared table
      if (!lastContents.isEmpty()) {
          int idx = Integer.parseInt(lastContents);
          return new XSSFRichTextString(sst.getEntryAt(idx)).toString();
      }
      return lastContents;
    case "inlineStr":   //inline string (not in sst)
      return new XSSFRichTextString(lastContents).toString();
    default:
      return lastContents;
  }
}
 

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 "";
}
 

@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
    Integer relativeRowIndex, Boolean isHead) {
    if (isHead) {
        Sheet sheet = writeSheetHolder.getSheet();
        Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
        // 在第一行 第二列创建一个批注
        Comment comment =
            drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short)1, 0, (short)2, 1));
        // 输入批注信息
        comment.setString(new XSSFRichTextString("创建批注!"));
        // 将批注添加到单元格对象中
        sheet.getRow(0).getCell(1).setCellComment(comment);
    }
}
 

@Override
protected void setStringValue(XlsxReadContext xlsxReadContext) {
    // This is a special form of string
    CellData tempCellData = xlsxReadContext.xlsxReadSheetHolder().getTempCellData();
    XSSFRichTextString richTextString = new XSSFRichTextString(tempCellData.getStringValue());
    tempCellData.setStringValue(richTextString.toString());
}
 

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 "";
}
 

/**
 * 既にコメントが設定されているときのコメントの装飾を設定する。
 * 既存のコメントの装飾をコピーするが、そのとき、１つ目のフォント設定のみとする。
 * 
 * @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());
    }
    
}
 

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

@Override
public void endElement(String uri, String localName, String qualifiedName)
  throws SAXException {

  if (uri != null && ! uri.equals(NS_SPREADSHEETML)) {
    return;
  }

  String thisStr = null;

  // v => contents of a cell
  if (isTextTag(localName)) {
    valueIsOpen = false;

    // Process the value contents as required, now we have it all
    switch (nextDataType) {
      case BOOLEAN:
        char first = value.charAt(0);
        thisStr = first == '0' ? "F" : "T";
        break;

      case ERROR:
        thisStr = "ERROR:" + value.toString();
        break;

      case FORMULA:
        thisStr = value.toString();
        break;

      case INLINE_STRING:
        // TODO: Can these ever have formatting on them?
        XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
        thisStr = rtsi.toString();
        break;

      case SST_STRING:
        String sstIndex = value.toString();
        try {
          int idx = Integer.parseInt(sstIndex);
          XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
          thisStr = rtss.toString();
        } catch (NumberFormatException ex) {
          logger.log(POILogger.ERROR, "Failed to parse SST index '" + sstIndex, ex);
        }
        break;

      case NUMBER:
        thisStr = value.toString();
        break;

      default:
        thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
        break;
    }
    output.cell(column, thisStr, cellStyleStr);

  } else if ("is".equals(localName)) {
    isIsOpen = false;
  } else if ("row".equals(localName)) {
    // Finish up the row
    output.endRow(rowNum);

    // some sheets do not have rowNum set in the XML, Excel can read them so we should try to read them as well
    nextRowNum = rowNum + 1;
  }
}
 

/**
 * (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 (cRow.getRowNumber() == 1) {
			if (!validateLangs(cRow)) {
				throw new SAXException("LANG-CODE-ERORR");
			}
			if (isHasDuplicateLangCode()) {
				throw new SAXException("DUPLICATE-LANG-CODE-ERORR");
			}
			if (!validateAppend()) {
				throw new SAXException("DIFF--SRC-LANG-CODE");
			}
			String xmlDecl = TmxTemplet.genertateTmxXmlDeclar();
			tmxWriter.writeXmlString(xmlDecl);
			TmxHeader header = TmxTemplet.generateTmxHeader(getSrcLang(), "unknown", "sentence",
					"Microsoft Excel", null, null, null);
			tmxWriter.writeXmlString("<tmx version=\"1.4\">\n");
			tmxWriter.writeHeader(TmxTemplet.header2Xml(header));
			tmxWriter.writeXmlString("<body>\n");
		} else {
			if (cache_size > cache.size()) {
				if (cRow.getCells() != null) {
					cache.add(cRow.toTmxTu());
				}
			} else {
				if (monitor.isCanceled()) {
					throw new SAXException("");
				}
				writeTmxTU(cache, tmxWriter);
				monitor.worked(1);
			}
		}

		lastElementName = null;
	}
	if (!isSheredString) {
		if ("t".equals(qName)) {
			cCell.setCellConentent(lastCellContent.toString().trim());
			cCell.setLangCode(langCodes.get(cCell.getCellNumber()));
			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());
				cCell.setLangCode(langCodes.get(cCell.getCellNumber()));
				lastCellContent.delete(0, lastCellContent.length());
			} catch (NumberFormatException e) {

			}

		}
	}
}