读取excel中百分比处理

具体代码：

操作合并单元格工具类：

package com.haoyao.demo.config;

import com.haoyao.demo.data2.model.PoiModel;
import org.apache.commons.compress.utils.Lists;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 *  Excel合并单元格操作  工具类
 */
public class ReadExcelUtil {
    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){


        String cellValue = "";
        switch (cell.getCellType()) {
            case STRING://字符串类型
                cellValue = cell.getStringCellValue();
                if(cellValue.trim().equals("")||cellValue.trim().length()<=0)
                    cellValue.isEmpty();
                break;
            case NUMERIC: //数值类型
                //判断数值类型中是不是日期格式
                if (HSSFDateUtil.isCellDateFormatted(cell))
                {
                    SimpleDateFormat sdf = null;
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                            .getBuiltinFormat("h:mm")) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                }else {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }

                break;
            case FORMULA: //公式
                cell.setCellType(CellType.FORMULA);
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case BLANK:
                cellValue.isEmpty();
                break;
            case BOOLEAN:
                break;
            case ERROR:
                break;
            default:
                break;
        }
        return cellValue;


//        if(cell == null) return "";
//        return cell.getStringCellValue();
    }


    /**
     * 合并单元格处理,获取合并行
     * @param sheet
     * @return List<CellRangeAddress>
     */
    public static List<CellRangeAddress> getCombineCell(Sheet sheet)
    {
        List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();

        int sheetmergerCount = sheet.getNumMergedRegions();   //获得一个 sheet 中合并单元格的数量

        for(int i = 0; i<sheetmergerCount;i++)   //遍历所有的合并单元格
        {
            //获得合并单元格保存进list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }


    /**
     * 判断cell是否为合并单元格，是的话返回合并行数和列数（只要在合并区域中的cell就会返回合同行列数，但只有左上角第一个有数据）
     * @param listCombineCell  上面获取的合并区域列表
     * @param cell
     * @param sheet
     * @return
     * @throws Exception
     */
    public  static Map<String,Object> CombineCell(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet)
            throws Exception{
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;

        Boolean flag=false;
        int mergedRow=0;   //行数
        int mergedCol=0;   //列数
        Map<String,Object> result=new HashMap<>();
        result.put("flag",flag);
        for(CellRangeAddress ca:listCombineCell)
        {
            //获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            //判断cell是否在合并区域之内，在的话返回true和合并行列数
            if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)
            {
                if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)
                {
                    flag=true;
                    mergedRow=lastR-firstR+1;
                    mergedCol=lastC-firstC+1;
                    result.put("flag",true);
                    result.put("mergedRow",mergedRow);
                    result.put("mergedCol",mergedCol);
                    break;
                }
            }
        }
        return result;
    }





    /**
     *
     * @param listCombineCell   获取合并单元格
     * @param cell      //sheet.getRow(i).getCell(0)  ： 获取第i行第0格
     * @param sheet    //获得第一个工作簿
     * @return
     */
    public static int getRowNum(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet){
        int xr = 0;
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        for(CellRangeAddress ca:listCombineCell)
        {

            firstC = ca.getFirstColumn();   // 起始行
            lastC = ca.getLastColumn();    // 结束行
            firstR = ca.getFirstRow();    // 起始列
            lastR = ca.getLastRow();     // 结束列
            if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)
            {
                if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)
                {
                    xr = lastR;
                }
            }
        }
        return xr;
    }




    /**
     *
     * @param listCombineCell   获取合并单元格
     * @param cell      //sheet.getRow(i).getCell(0)  ： 获取第i行第0格
     * @param sheet    //获得第一个工作簿
     * @return
     */
    public static int getColumnNum(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet){
        int xr = 0;
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        for(CellRangeAddress ca:listCombineCell)
        {

            firstC = ca.getFirstColumn();   // 起始行
            lastC = ca.getLastColumn();    // 结束行
            firstR = ca.getFirstRow();    // 起始列
            lastR = ca.getLastRow();     // 结束列
            if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)
            {
                if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)
                {
                    xr = lastR;
                }
            }
        }
        return xr;
    }



    /**
     * 判断单元格是否为合并单元格，是的话则将单元格的值返回
     * @param listCombineCell 存放合并单元格的list
     * @param cell 需要判断的单元格
     * @param sheet sheet
     * @return
     */
    public static String isCombineCell(List<CellRangeAddress> listCombineCell, Cell cell, Sheet sheet)
            throws Exception{
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        String cellValue = null;
        for(CellRangeAddress ca:listCombineCell)
        {
//获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)
            {
                if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)
                {
                    Row fRow = sheet.getRow(firstR);
                    Cell fCell = fRow.getCell(firstC);
                    cellValue = getCellValue(fCell);
                    break;
                }
            }
            else
            {
                cellValue = "";
            }
        }
        return cellValue;
    }
    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public String getMergedRegionValue(Sheet sheet ,int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();
        for(int i = 0 ; i < sheetMergeCount ; i++){
            //获取单元格位置
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell) ;
                }
            }
        }
        return null ;
    }
    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    public static boolean isMergedRegion(Sheet sheet,int row ,int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }


}

百分比数据转换操作：

  // 转化double类型
  Double groussRate = Double.parseDouble(ReadExcelUtil.getCellValue(row.getCell(16)));
  //毛利率
  incomeModel1.setGrossrate(new DecimalFormat("0").format(groussRate*100)+"%" );  
              

结果：