java 解析上传的Excel文件

java poi解析上传的Excel文件

 

 

/**
 * @Author
 * @ClassName AnalysisImportExcelUntil
 * @Description 解析上传的excel数据
 * @Date 2019/11/23 13:43
 * @Version 1.0
 */
public class AnalysisImportExcelUntil {

    private static final Logger log = Logger.getLogger(AnalysisImportExcelUntil.class);


    /**
     * 解析上传的excel， 默认只解析第一张Sheet
     *
     * @param file     excel
     * @param startRow 数据开始行
     * @return List<String [ ]>
     * @throws IOException
     */
    public static List<String[]> getExcelData(MultipartFile file, int startRow) throws IOException {
        int resultSize = 0;
        ArrayList<String[]> resultData = new ArrayList<>(resultSize);
        if (!checkFile(file)) {
            log.error("上传的excel文件格式有问题");
            return resultData;
        }
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);
        if (workbook != null) {
            //获取第一张sheet工作表
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                return resultData;
            }
            // 重新初始化List结果大小
            resultSize = sheet.getLastRowNum() + 1;
            //获得当前sheet的开始行
            int firstRowNum = sheet.getFirstRowNum();
            //获得当前sheet的结束行
            int lastRowNum = sheet.getLastRowNum();
            //循环除了startRow的所有行,如果要循环除第一行以外的就firstRowNum+1
            for (int rowNum = firstRowNum + startRow; rowNum <= lastRowNum; rowNum++) {
                //获得当前行
                Row row = sheet.getRow(rowNum);
                if (rowIsEmpty(row)) {
                    break;
                }
                //获得当前行的开始列
                int firstCellNum = row.getFirstCellNum();
                //获得当前行的列数
                int lastCellNum = row.getLastCellNum();
                String[] cells = new String[lastCellNum];
                //循环当前行
                for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    cells[cellNum] = getCellValue(cell);
                }
                resultData.add(cells);
            }
            workbook.close();
        }
        return resultData;
    }


    /**
     * 检查文件格式
     *
     * @param file
     * @throws IOException
     */
    public static boolean checkFile(MultipartFile file) throws IOException {
        if (null == file) {
            log.error("文件不存在！");
            return false;
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {
            log.error(fileName + "不是excel文件");
            return false;
        }
        return true;
    }

    /**
     * 获取工作簿对象
     *
     * @param file
     * @return
     */
    public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith("xls")) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith("xlsx")) {
                //2007 及2007以上
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            log.error(e.getMessage());
        }
        return workbook;
    }

    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                //数字
                cellValue = stringDateProcess(cell);
                break;
            case STRING:
                //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN:
                //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK:
                //空值
                cellValue = "";
                break;
            case ERROR:
                //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }


    public static String stringDateProcess(Cell cell) {
        String result = new String();
        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();
            result = sdf.format(date);
        } else if (cell.getCellStyle().getDataFormat() == 58) {
            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            double value = cell.getNumericCellValue();
            Date date = org.apache.poi.ss.usermodel.DateUtil
                    .getJavaDate(value);
            result = sdf.format(date);
        } else {
            double value = cell.getNumericCellValue();
            CellStyle style = cell.getCellStyle();
            DecimalFormat format = new DecimalFormat();
            String temp = style.getDataFormatString();
            // 单元格设置成常规
            if (temp.equals("General")) {
                format.applyPattern("#");
            }
            result = format.format(value);
        }

        return result;
    }


    /**
     * @return * @param null
     * @Author
     * @Description //TODO 判断excel的row是否全为空
     * @Date 2019/12/2 19:30
     * @Param
     */
    public static boolean rowIsEmpty(Row row) {
        if (null == row) {
            return true;
        }
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellTypeEnum() != BLANK) {
                return false;
            }
        }
        return true;
    }


}