上传解析Excel

前言

项目中上传解析Excel的功能，非常普遍，因为方便，接下来就写一个工具类对Excel进行解析

具体方案

引入poi 阿帕奇依赖

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

工具类的书写

public class ExcelUtils {

    private final static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    // 文件类型 后缀
    public static final String EXCEL_2003_SUFFIX = ".xls";
    public static final String EXCEL_2007_SUFFIX = ".xlsx";


    /**
     * @Descriptioin: 读取Excel转换为内存中的List,方便校验
     *  Todo:非常抱歉这个传参有误使用下面的方法
     * @param fileName
     * @return
     
    private static List<Object[]> readExcelToList(String fileName) {
        String suffixName = fileName.substring(fileName.lastIndexOf("."));
        InputStream inputStream;
        try {
            inputStream = new FileInputStream(new File(fileName));
            if(EXCEL_2003_SUFFIX.equalsIgnoreCase(suffixName)){
                return readOld(inputStream);
            }else if(EXCEL_2007_SUFFIX.equalsIgnoreCase(suffixName)){
                return readNew(inputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
      return new ArrayList<Object[]>();
    }
*/


   /**
     * @Descriptioin: 读取Excel转换为内存中的List,方便校验
     *    本方法是修改后的，可放心使用
     * @param fileName
     * @return
     */
    private static List<Object[]> readExcelToList(MultipartFile file) {
        String fileName = file.getOriginalFilename();
        String suffixName = fileName.substring(fileName.lastIndexOf("."));
        InputStream inputStream;
        try {
            inputStream = new FileInputStream(new File(fileName));
            if(EXCEL_2003_SUFFIX.equalsIgnoreCase(suffixName)){
                return readOld(inputStream);
            }else if(EXCEL_2007_SUFFIX.equalsIgnoreCase(suffixName)){
                return readNew(inputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
      return new ArrayList<Object[]>();
    }


    /**
    * List<Object[]> 返回的是不包含excel标题头 纯数据的集合
    */
    public static List<Object[]>  readOld(InputStream inputStream) throws IOException {
        HSSFWorkbook  hssfWorkbook = null;
        List<Object[]> list = new ArrayList<Object[]>();
        try {
            hssfWorkbook = new HSSFWorkbook(inputStream);
            HSSFSheet xssfSheet = hssfWorkbook.getSheetAt(0);
            HSSFRow hssfRow = null;
            HSSFCell hssfCell = null;
            Object cellValue = null;
                for(int i=1 ; i<= xssfSheet.getPhysicalNumberOfRows();i++){
                    hssfRow = xssfSheet.getRow(i);
                    if (null == hssfRow){
                        continue;
                    }
                    Object[] values = new Object[xssfSheet.getRow(0).getLastCellNum()];
                    for(int j=0; j<=hssfRow.getLastCellNum();j++){
                        hssfCell = hssfRow.getCell(j);
                        if(null == hssfCell){
                            continue;
                        }
                        switch (hssfCell.getCellType()) {
                            case Cell.CELL_TYPE_STRING:
                                cellValue = hssfCell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if(DateUtil.isCellDateFormatted(hssfCell)){
                                    cellValue = DateUtil.getJavaDate(hssfCell.getNumericCellValue());
                                    break;
                                }else{
                                    cellValue = NumberToTextConverter.toText(hssfCell.getNumericCellValue());
                                    break;
                                }
                            case Cell.CELL_TYPE_FORMULA:
                                cellValue = hssfCell.getCellFormula();
                                break;
                            case Cell.CELL_TYPE_BOOLEAN:
                                 cellValue = hssfCell.getBooleanCellValue();
                                 break;
                            case Cell.CELL_TYPE_BLANK:
                                cellValue = "";
                                break;
                            default:
                                cellValue = cellValue.toString();
                        }
                        values[j] = cellValue;
                    }
                    list.add(values);
                }
                return list;
        } catch (IOException e) {
            logger.error("文件格式有误，请检查：{}",e);
            return null;
        }finally {
            if(null != inputStream) inputStream.close();

            if(null !=hssfWorkbook) hssfWorkbook.close();

        }
    }

  /**
    * List<Object[]> 返回的是不包含excel标题头 纯数据的集合
    */
    public static List<Object[]> readNew(InputStream inputStream) throws IOException {
        XSSFWorkbook  xssfWorkbook = null;
        List<Object[]> list = new ArrayList<Object[]>();
        try {
            xssfWorkbook = new XSSFWorkbook(inputStream);
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
            XSSFRow xssfRow = null;
            XSSFCell xssfCell = null;
            Object cellValue = null;
                for(int i=1 ; i<= xssfSheet.getPhysicalNumberOfRows();i++){
                    xssfRow = xssfSheet.getRow(i);
                    if (null == xssfRow){
                        continue;
                    }

                    Object[] values = new Object[xssfSheet.getRow(0).getLastCellNum()];
                    for(int j=0; j<=xssfRow.getLastCellNum();j++){
                        xssfCell = xssfRow.getCell(j);
                        if(null == xssfCell){
                            continue;
                        }
                        switch (xssfCell.getCellType()) {
                            case Cell.CELL_TYPE_STRING:
                                cellValue = xssfCell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if(DateUtil.isCellDateFormatted(xssfCell)){

                                    cellValue = DateUtil.getJavaDate(xssfCell.getNumericCellValue());
                                    break;
                                }else{
                                    cellValue = NumberToTextConverter.toText(xssfCell.getNumericCellValue());
                                    break;
                                }
                            case Cell.CELL_TYPE_BOOLEAN:
                                cellValue = xssfCell.getBooleanCellValue();
                                break;
                            case Cell.CELL_TYPE_FORMULA:
                                cellValue = xssfCell.getCellFormula();
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                cellValue = "";
                                break;
                            default:
                                cellValue = cellValue.toString();
                        }
                        values[j] = cellValue;
                    }
                    list.add(values);
                }
            return list;
        } catch (IOException e) {
            logger.error("文件格式有误，请检查：{}",e);
            return null;
        }finally {
            if(null != inputStream) inputStream.close();
            if(null !=xssfWorkbook) xssfWorkbook.close();
        }
    }

}