注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

曹兆领的博客

有志者事竟成,破釜沉舟,百二秦关终属楚!

 
 
 

日志

 
 

excel导入时上下标问题  

2013-02-04 16:53:04|  分类: 软件应用 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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

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 = "<sup>" +temp+"</sup>";

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

                }

                                     //判断下标

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

                         temp = "<sub>" +temp+"</sub>";

                         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 = "<sup>" +temp1+"</sup>";

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

             }

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

                    temp1 = "<sub>" +temp1+"</sub>";

                    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 = "<sup>" +temp+"</sup>";

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

                }

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

                         temp = "<sub>" +temp+"</sub>";

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

                }

                value += temp;

                             }

             return value;

                   }

                   return cell.toString();

         }

  评论这张
 
阅读(761)| 评论(2)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017