上下标 java_java包POI处理excel上下标的代码分享

原理是逐字检查发现，即使用sup,sub的网页标签替换。

含有2003版本和2007版本

package com.uet.common.utils;

import org.apache.commons.lang3.StringUtils;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFFont;

import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.uet.common.exception.DataAccessException;

public class PoiUtils {

public static boolean isOffice2003(String fileName){

if(StringUtils.isEmpty(fileName)){

throw new DataAccessException("名称不为能为空!");

}

if(fileName.endsWith("xlsx")){

return false;

}

return true;

}

/**

* 判断上下标 2003版 cell 表示传入的单元格对象 book 表示传入的当前工作薄对象

**/

public static String superOrSubScript2003(Cell cell, Workbook book,String fileName) {

if(!isOffice2003(fileName)){

return superOrSubScript2007(cell, book);

}

HSSFFont font = null;

HSSFRichTextString rts = null;

HSSFCellStyle style = null;

int fromIndex = 0;

int toIndex = 0;

String value = "";

// 判断当前单元格的内容是否为数字类型，如果是转换成字符串型

if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {

cell.setCellValue((cell.getNumericCellValue() + "").substring(0,

(cell.getNumericCellValue() + "").indexOf(".")));

}

// 获取单元格中的数据

rts = (HSSFRichTextString) cell.getRichStringCellValue();

// 获取每个单元格数据的style属性

style = (HSSFCellStyle) cell.getCellStyle();

font = style.getFont(book);

if (rts.numFormattingRuns() > 0) {

for (int k = 0; k < rts.numFormattingRuns(); k++) {

toIndex = rts.getIndexOfFormattingRun(k);

String temp = rts.toString().substring(fromIndex, toIndex);

System.out.println("\tSubstring [" + temp + "]");

// 判断上标

if (font.getTypeOffset() == HSSFFont.SS_SUPER) {

temp = "^{" + temp + "}";

System.out.println("\t______________发现上标");

}

// 判断下标

if (font.getTypeOffset() == HSSFFont.SS_SUB) {

temp = "_{" + temp + "}";

System.out.println("\t______________发现下标");

}

value += temp;

if (!value.equals("")) {

font = (HSSFFont) book.getFontAt(rts

.getFontOfFormattingRun(k));

}

fromIndex = toIndex;

}

toIndex = rts.length();

String temp1 = rts.toString().substring(fromIndex, toIndex);

System.out.println("\tSubstring [" + temp1 + "]");

if (font.getTypeOffset() == HSSFFont.SS_SUPER) {

temp1 = "^{" + temp1 + "}";

System.out.println("\t______________发现上标");

}

if (font.getTypeOffset() == HSSFFont.SS_SUB) {

temp1 = "_{" + temp1 + "}";

System.out.println("\t______________发现下标");

}

value += temp1;

return value;

}

return cell.getRichStringCellValue().getString();

}

public static String superOrSubScript2007(Cell cell, Workbook book) {

XSSFWorkbook workbook = null;

XSSFFont font = null;

XSSFRichTextString rts = null;

XSSFCellStyle style = null;

int runIndex = 0;

int runLength = 0;

String value = "";

// 处理上下标

workbook = (XSSFWorkbook) book;

if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {

cell.setCellValue((cell.getNumericCellValue() + "").substring(0,

(cell.getNumericCellValue() + "").indexOf(".")));

}

rts = (XSSFRichTextString) cell.getRichStringCellValue();

style = (XSSFCellStyle) cell.getCellStyle();

font = style.getFont();

if (rts.numFormattingRuns() > 1) {

for (int k = 0; k < rts.numFormattingRuns(); k++) {

runLength = rts.getLengthOfFormattingRun(k);

runIndex = rts.getIndexOfFormattingRun(k);

String temp = rts.toString().substring(runIndex,

(runIndex + runLength));

System.out.println("\tSubstring [" + temp + "]");

try {

font = rts.getFontOfFormattingRun(k);

} catch (NullPointerException npe) {

font = workbook.getFontAt(XSSFFont.DEFAULT_CHARSET);

font.setTypeOffset(XSSFFont.SS_NONE);

}

if (font.getTypeOffset() == XSSFFont.SS_SUPER) {

temp = "^{" + temp + "}";

System.out.println("\t______________发现上标");

}

if (font.getTypeOffset() == XSSFFont.SS_SUB) {

temp = "_{" + temp + "}";

System.out.println("\t______________发现下标");

}

value += temp;

}

return value;

}

return cell.getRichStringCellValue().getString();

}

}