POI读取 Excel-蒲公英云

可解决整形变浮点型、日期读取变成整型或其他未知类型、数字变成科学计数法等问题。

依赖：

<dependency>
   <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

工具类：

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelReadUtil { 
    public static void main(String[] args) { 
        String path = "C:\\Users\\Administrator\\Documents\\tmp_template\\模板.xlsx";
        try { 
            List<List<String>> result = new ExcelReadUtil().read(path);
            System.out.println("总行数："+result.size());
            for (int i = 0; i < result.size(); i++) { 
                List<String> model = result.get(i);
                System.out.println(model.toString());
            }
        } catch (Exception e) { 
            e.printStackTrace();
        }
    }
    public static List<List<String>> read(String path) throws Exception{ 
        File file=new File(path);
        String ext=FilenameUtils.getExtension(file.getName());
        if("xls".equalsIgnoreCase(ext)){ 
            return readXls(path);
        }else if("xlsx".equalsIgnoreCase(ext)){ 
            return readXlsx(path);
        }
        return null;
    }
    public static List<List<String>> read(String pathDir,String fileName) throws Exception{ 
        File file=new File(pathDir,fileName);
        return read(file.getAbsolutePath());
    }
    /** * * @Title: readXls * @Description: 处理xls文件 * @param @param path * @param @return * @param @throws Exception 设定文件 * @return List<List<String>> 返回类型 * @throws * */
    private static List<List<String>> readXls(String path) throws Exception { 
        InputStream is = new FileInputStream(path);
        // HSSFWorkbook 标识整个excel
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        List<List<String>> result = new ArrayList<List<String>>();
        int size = hssfWorkbook.getNumberOfSheets();
        // 循环每一页，并处理当前循环页
       // for (int numSheet = 0; numSheet < size; numSheet++) { //遍历所有的sheet页
        for (int numSheet = 0; numSheet < 1; numSheet++) {     //numSheet<1，即 只读取第一个sheet页。
            // HSSFSheet 标识某一页
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);  
            if (hssfSheet == null) { 
                continue;
            }
            // 处理当前页，循环读取每一行
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { //第1行是标题，要跳过，从第2行开始读，即下标是1。
                // HSSFRow表示行
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if(hssfRow==null){ 
                    break;
                }
                int minColIx = hssfRow.getFirstCellNum();
                int maxColIx = hssfRow.getLastCellNum();
                List<String> rowList = new ArrayList<String>();
                // 遍历改行，获取处理每个cell元素
                for (int colIx = minColIx; colIx < maxColIx; colIx++) { 
                    // HSSFCell 表示单元格
                    HSSFCell cell = hssfRow.getCell(colIx);
                    if (cell == null) { 
                        rowList.add("");
                    }else{ 
                        rowList.add(getStringVal(cell));
                    }
                }
                result.add(rowList);
            }
        }
        return result;
    }
    private static List<List<String>> readXlsx(String path) throws Exception { 
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        List<List<String>> result = new ArrayList<List<String>>();
        // 循环每一页，并处理当前循环页
        if(xssfWorkbook.iterator().hasNext()){  //只读取第一个sheet页
      // for (XSSFSheet xssfSheet : xssfWorkbook) { //遍历所有的sheet页
            XSSFSheet xssfSheet=xssfWorkbook.iterator().next();  //只读取第一个sheet 页
           // if (xssfSheet == null) { 
            // continue;
           // }
            // 处理当前页，循环读取每一行
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {  //第1行是标题，要跳过，从第2行开始读，即下标是1。
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if(xssfRow==null){ 
                    break;
                }
                int minColIx = xssfRow.getFirstCellNum();
                int maxColIx = xssfRow.getLastCellNum();
                List<String> rowList = new ArrayList<String>();
                for (int colIx = minColIx; colIx < maxColIx; colIx++) { 
                    XSSFCell cell = xssfRow.getCell(colIx);
                    if (cell == null) { 
                        rowList.add("");
                    }else{ 
                        //rowList.add(cell.toString());
                        rowList.add(getStringVal(cell));
                    }
                }
                result.add(rowList);
            }
        }
        return result;
    }
       /** * 改造poi默认的toString（）方法如下 */
    private static String getStringVal(Cell cell) { 
        switch (cell.getCellType()) { 
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue() ? "true" : "false";
            case Cell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();
            case Cell.CELL_TYPE_NUMERIC:
                String cellvalue = "";  
                short format = cell.getCellStyle().getDataFormat();  
                if(format == 14 || format == 31 || format == 57 || format == 58){    //excel中的时间格式 
                    Date date = cell.getDateCellValue();
                    cellvalue = DateFormatUtils.format(date, "yyyy-MM-dd");
                }  
                // 判断当前的cell是否为 Date 
                else if (HSSFDateUtil.isCellDateFormatted(cell)) {                    
                    Date date = cell.getDateCellValue();  
                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");  
                    cellvalue= formater.format(date);  
                } else {  // 如果是纯数字 
                    // 取得当前Cell的数值 
                    cellvalue = NumberToTextConverter.toText(cell.getNumericCellValue());   
                }
                return  cellvalue;  
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            default:
                return "";
        }
    }
}