java 实现excel中上下角标处理实现原理

 数据批量导入时 excel中比较难处理的 不外乎 上下角标、全角半角区分、化学方程式、分子式等问题。一般处理上下角标的方式是手动在excel单元格中添加^上标和_下标。这种方式针对少说还可以。如果是海量的效率就比较低 java处理方式可以采用引入apache中 hssf、ss、xssf包中的excel样式处理类 进行进行批量标示 定位上下角标 并以^上标和_下标这种形式存储，即可在前台页面正常展现上下角标形式 实现原理

package com.nqh.platform;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.NumberFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
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;

 

2003版本：

      /**

          *  判断上下标

          *  2003版

          *  cell 表示传入的单元格对象 book 表示传入的当前工作薄对象 

          **/

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

                   HSSFWorkbook workbook = null;

                   HSSFFont font = null; 

                   HSSFRichTextString rts = null;

                   HSSFCellStyle style = null;

                   int fromIndex = 0;

                   int toIndex = 0;

                   String value = "";

                   //处理上下标

                   workbook = (HSSFWorkbook)book;

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

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

                  

                   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 = workbook.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.toString();

         }

 

 

 

2007版本：

/**

          *  判断上下标

          *  2007版

          **/

         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.toString();

         }