poi的excel解析工具类

2019独角兽企业重金招聘Python工程师标准>>>

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by shea on 2018/9/20.
 */
public class ExcelUtil {
    private static DecimalFormat df = new DecimalFormat("0");             //数字格式，防止长数字成为科学计数法形式，或者int变为double形式
    //获取excel解析引擎
    public static Workbook getExcelEngine(String fileName, InputStream in){
        Workbook workbook = null;
        try {
            if(fileName.endsWith("xls")){
                workbook=new HSSFWorkbook(in);
            }else {
                workbook=new XSSFWorkbook(in);
            }
            return workbook;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
    //判断单元格类型,返回指定值
    public static String getCellValue(Cell cell){
        int cellType = cell.getCellType();
        String res="";
        switch (cellType){
            case 0:
                SimpleDateFormat sdf = null;
                short format = cell.getCellStyle().getDataFormat();
                if(format == 14 || format == 31 || format == 57 || format == 58
                        || (176<=format && format<=178) || (182<=format && format<=196)
                        || (210<=format && format<=213) || (208==format )){//日期
                    sdf = new SimpleDateFormat("yyyy-MM-dd");
                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(Double.parseDouble(n));
                        res=sdf.format(date);
                 else if(formatIndex == 20 || formatIndex == 32 || formatIndex==183 || (200<=formatIndex && formatIndex<=209)){//时间
                   sdf = new SimpleDateFormat("HH:mm");
                        Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(Double.parseDouble(n));
                        res = sdf.format(date);
                }else {
                    cell.setCellType(0);
                    res =String.valueOf(cell.getNumericCellValue());
                    //科学计算法
                    if(res.contains("E")) {
                       res= df.format(cell.getNumericCellValue());
                    }
                }

                break;
            case 1:
                cell.setCellType(1);
                res = cell.getStringCellValue();
                break;
            case 2://公式---也强转为string
                cell.setCellType(1);
                res = cell.getStringCellValue();
                break;
            case 3://空值
                cell.setCellType(3);
                res = "";
            case 4://布尔值
                cell.setCellType(4);
                res = String.valueOf( cell.getBooleanCellValue());
                break;
            default:
               res="";
                break;
        }
        return res;
    }
    /**
     * 获取指定行列的值
     * @param sheet
     * @param rowNum 行--- 0表示第一行
     * @param colNum 列--- 0表示第一列
     * @return
     */
    public static String getRowCell(Sheet sheet, int rowNum, int colNum){
        Row row = sheet.getRow(rowNum);
        Cell cell = row.getCell(colNum);
        return  getCellValue(cell);
    }

    /**
     * execl解析----统一封装到list<String[]>
     * @param sheet
     * @param rowBegin 起始行
     * @param colBegin 起始列
     * @param maxRows   最大行
     * @param maxCols  最大列
     * @return
     * @throws IOException
     */
    public static List<String[]>  adjustExcelParse(Sheet sheet, int rowBegin,int colBegin,int maxRows,int maxCols) {
        //将每一个单元格，设置为 String类型
        //解析
        try {
            Row row;
            Cell cell;
            String[] arr;
            List<String[]> datas = new ArrayList<String[]>();
            //判断最大行数是否为-1，若为-1，则取最大行值
            int endRows=sheet.getLastRowNum()+1;
            if(-1!=maxRows){
                endRows=maxRows;
            }
            for(int i=rowBegin-1;i<endRows;i++){
                arr = new String[maxCols-colBegin+1];//封装每一列的结果
                row = sheet.getRow(i);
                if(null == row) continue;
                for(int j=colBegin-1;j<maxCols;j++){
                    cell = row.getCell(j);
                    //if (null==cell) continue;
                    if(cell==null||cell.equals("")||cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)continue;
                    String cellValue = getCellValue(cell);
                    arr[j-colBegin+1] = cellValue;
                }
                datas.add(arr);
            }
            return datas;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 特殊情况，走特殊逻辑
     */

    /**
     * 日期格式转换
     */
    public static String formatDateCell(int nums){
        Calendar c = new GregorianCalendar(1900,0,-1);
        Date d = c.getTime();
        Date _d = DateUtils.addDays(d,nums); //42605是距离1900年1月1日的天数
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        String format = sdf.format(_d);
        return format;
    }
}

 

转载于:https://my.oschina.net/shea1992/blog/2208984