excle解析

------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 

   private ReadSheetResult readSheet(Sheet sheetInput, Sheet sheetOutput) {
        int rowCount = sheetInput.getPhysicalNumberOfRows();
        if (rowCount < 2) {
            return new ReadSheetResult();
        }
        int successCount = 0;
        int errorCount = 0;
        int colZcmc = -1;
        int colDanj = -1;
        int colShul = -1;
        int colJiaz = -1;
        int colCaigxs = -1;
        int colShengccj = -1;
        int colPinp =-1;
        int colGuigxh = -1;
        int colGuobfl = -1;
        Row headerRow = sheetInput.getRow(0);
        Row headerRowOutput = sheetOutput.createRow(0);
        int colCount = headerRow.getPhysicalNumberOfCells();
        for (int i = 0; i < colCount; ++i) {
            Cell cell = headerRow.getCell(i);
            int cellType = cell.getCellType();
            if (cellType != Cell.CELL_TYPE_STRING) {
                throw new ImportFileIncorrectException("表头应该都是字符串类型");
            }
            String val = cell.getStringCellValue();
            switch (val) {
            case COL_ZCMC:
                colZcmc = i;
                break;
            case COL_DANJ:
                colDanj = i;
                break;
            case COL_SHUL:
                colShul = i;
                break;
            case COL_JIAZ:
                colJiaz = i;
                break;
            case COL_CAIGFS:
                colCaigxs = i;
                break;
            case COL_SHENGCCJ:
                colShengccj = i;
                break;
            case COL_PINP:
                 colPinp = i;
                break; 
            case COL_GUIGXH:
                colGuigxh = i;
                break;      
            case COL_GUOBFL:
                colGuobfl = i;
                break;    

            }

            Cell cellOutput = headerRowOutput.createCell(i);
            copyCell(cell, cellOutput);
            sheetOutput.setColumnWidth(i, sheetInput.getColumnWidth(i));
        }
        if (colZcmc == -1) {
            throw new ImportFileIncorrectException("导入文件中没有'资产名称'列: sheet=" + sheetInput.getSheetName());
        }
        if (colDanj == -1) {
            throw new ImportFileIncorrectException("导入文件中没有'单价'列: sheet=" + sheetInput.getSheetName());
        }
        if (colShul == -1) {
            throw new ImportFileIncorrectException("导入文件中没有'数量（面积）'列: sheet=" + sheetInput.getSheetName());
        }
        if (colJiaz == -1) {
            throw new ImportFileIncorrectException("导入文件中没有'价值'列: sheet=" + sheetInput.getSheetName());
        }
        if (colCaigxs == -1) {
            throw new ImportFileIncorrectException("导入文件中没有'采购组织形式'列: sheet=" + sheetInput.getSheetName());
        }
        if (colShengccj == -1) {
            throw new ImportFileIncorrectException("导入文件中没有'厂商'列: sheet=" + sheetInput.getSheetName());
        }
        if (colPinp == -1) {
            throw new ImportFileIncorrectException("导入文件中没有'品牌'列: sheet=" + sheetInput.getSheetName());
        }
        if (colGuigxh == -1) {
            throw new ImportFileIncorrectException("导入文件中没有'规格型号'列: sheet=" + sheetInput.getSheetName());
        }
        if (colGuobfl == -1) {
            throw new ImportFileIncorrectException("导入文件中没有'国标分类'列: sheet=" + sheetInput.getSheetName());
        }

        int colError = colCount;
        Cell headerCellError = headerRowOutput.createCell(colError);
        headerCellError.setCellValue("错误信息");
        copyCellStyle(headerRow.getCell(colZcmc), headerCellError);
        sheetOutput.setColumnWidth(colError, 40 * 256);

        int outputRowIdx = 1;

//        CellStyle sourceStyle = sheetInput.getRow(1).getCell(colError - 1).getCellStyle();
        CellStyle cellStyleError = sheetOutput.getWorkbook().createCellStyle();
        cellStyleError.setAlignment(CellStyle.ALIGN_LEFT);
        cellStyleError.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        Font fontError = sheetOutput.getWorkbook().createFont();
        fontError.setColor(IndexedColors.RED.index);
        cellStyleError.setBorderBottom((short)0);
        cellStyleError.setBorderLeft((short)0);
        cellStyleError.setBorderRight((short)0);
        cellStyleError.setBorderTop((short)0);
        cellStyleError.setBottomBorderColor((short)8);
        cellStyleError.setFont(fontError);
        cellStyleError.setLeftBorderColor((short)8);
        cellStyleError.setRightBorderColor((short)8);
        cellStyleError.setTopBorderColor((short)8);
        cellStyleError.setWrapText(true);
        List<JSONObject> array  = new ArrayList<>();
        List<BaseData>  cglist =  baseService.queryAll(BASEDATA_CAIGXS);
        Map<String,Object> cgmap = new HashMap<>();
        cglist.stream().forEach(x->{
            JsonNode node =JSONObjectUtils.toJson(x);
            cgmap.put(x.getTitle(), node);
        });
        
        List<BaseData>  gblist =  baseService.queryAll(BASEDATA_GUOBFL);
        Map<String,Object> gbmap = new HashMap<>();
        gblist.stream().forEach(y->{
            JsonNode node =JSONObjectUtils.toJson(y);
            gbmap.put(y.getTitle(), node);
        });
        
        for (int i = 1; i < rowCount; ++i) {
            boolean rowSuccess = true;
            Row row = sheetInput.getRow(i);
            List<String> errors = new ArrayList<>();

            Cell cellZcmc = row.getCell(colZcmc);
            Cell cellDanj = row.getCell(colDanj);
            Cell cellShul = row.getCell(colShul);
            Cell cellJiaz = row.getCell(colJiaz);
            Cell cellCaigxs = row.getCell(colCaigxs);
            Cell cellShengccj = row.getCell(colShengccj);
            Cell cellPinp = row.getCell(colPinp);
            Cell cellGuigxh = row.getCell(colGuigxh);
            Cell cellGuobfl = row.getCell(colGuobfl);

            
            Pair<String, String> zcmcVal = getStringCellValue(cellZcmc, COL_ZCMC, false);
            if (StringUtils.isNotEmpty(zcmcVal.getValue1())) {
                rowSuccess = false;
                errors.add(zcmcVal.getValue1());
            }
            String zcmc = zcmcVal.getValue0();

            
            Pair<Double, String> danjVal = getDoubleCellValue(cellDanj, COL_DANJ, true);
            if (StringUtils.isNotEmpty(danjVal.getValue1())) {
                rowSuccess = false;
                errors.add(danjVal.getValue1());
            }
            Double danj = danjVal.getValue0();

            Pair<Double, String> shullVal = getDoubleCellValue(cellShul, COL_SHUL, true);
            if (StringUtils.isNotEmpty(shullVal.getValue1())) {
                rowSuccess = false;
                errors.add(shullVal.getValue1());
            }
            Double shul = shullVal.getValue0();

            Pair<Double, String> jiazVal = getJIAZCellValue(cellJiaz, COL_JIAZ,danj,shul, true);
            if (StringUtils.isNotEmpty(jiazVal.getValue1())) {
                rowSuccess = false;
                errors.add(jiazVal.getValue1());
            }
            Double jiaz = jiazVal.getValue0();

            Pair<Object, String> caigxsVal = getCGBaseDataCellValue(cellCaigxs, COL_CAIGFS, true,cgmap);
            if (StringUtils.isNotEmpty(caigxsVal.getValue1())) {
                rowSuccess = false;
                errors.add(caigxsVal.getValue1());
            }
            
            Object caigxs = caigxsVal.getValue0();
                    
            String shengccj = getCellValue(cellShengccj);
            
            String pinp = getCellValue(cellPinp);
            
            String guigxh =  getCellValue(cellGuigxh);
            
            
            Pair<Object, String> guobflVal = getGBBaseDataCellValue(cellGuobfl, COL_GUOBFL, false,gbmap);
            if (StringUtils.isNotEmpty(guobflVal.getValue1())) {
                rowSuccess = false;
                errors.add(guobflVal.getValue1());
            }
            Object guobfl = guobflVal.getValue0();
            

            
            
            if (rowSuccess) {
                successCount++;
                JSONObject  obj = new JSONObject();
                obj.put("zicmc",zcmc);
                obj.put("danj", danj);
                obj.put("shul", shul);
                obj.put("jiaz", jiaz);
                obj.put("caigzzxs",caigxs);
                obj.put("shengccj", shengccj);
                obj.put("pinp", pinp);
                obj.put("guigxh", guigxh);
                obj.put("zicfl",guobfl);
                array.add(obj);
                
            }

            if (!rowSuccess) {
                errorCount++;
                Row rowOutput = sheetOutput.createRow(outputRowIdx++);
                for (int j = 0; j < colCount; ++j) {
                    Cell sourceCell = row.getCell(j);
                    Cell targetCell = rowOutput.createCell(j);
                    copyCell(sourceCell, targetCell);
                }
                String errorMsg = StringUtils.convertListToString(errors, '\n');
                Cell cellError = rowOutput.createCell(colError);
                cellError.setCellValue(errorMsg);
                cellError.setCellStyle(cellStyleError);
            }

        }
        
        return new ReadSheetResult(successCount, errorCount,array);
    }

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

public ImportExcelResult importExcel(InputStream in) throws IOException, InvalidFormatException {
        Assert.notNull(in, "InputStream 'in' must not be null");

        boolean success = true;
        int successCount = 0;
        int errorCount = 0;
        List<JSONObject> jsonarry = new ArrayList<JSONObject>();
        Workbook wbInput = WorkbookFactory.create(in);
        SXSSFWorkbook wbOutput = new SXSSFWorkbook(-1);
        
        for (int i = 0, count = wbInput.getNumberOfSheets(); i < count; ++i) {
            Sheet sheetInput = wbInput.getSheetAt(i);
            Sheet sheetOutput = wbOutput.createSheet(sheetInput.getSheetName());
            ReadSheetResult readSheetResult = readSheet(sheetInput, sheetOutput);
            success &= readSheetResult.isSuccess();
            successCount += readSheetResult.getSuccessCount();
            errorCount += readSheetResult.getErrorCount();
            jsonarry.addAll(readSheetResult.getJsonArray());
            PaneInformation paneInfo = sheetInput.getPaneInformation();
            if (paneInfo != null && paneInfo.isFreezePane()) {
                sheetOutput.createFreezePane(paneInfo.getVerticalSplitLeftColumn(), paneInfo.getHorizontalSplitTopRow());
            }
        }

        if (success) {
            wbOutput.dispose();
            return ImportExcelResult.success(successCount,jsonarry);
        } else {
            UUID fileId = UUID.randomUUID();
            File resultFile = TempFile.getTempFile(fileId, DepreciationConstants.EXCEL_TYPE);
            // 把检验结果存到一个临时文件中
            try (OutputStream out = new FileOutputStream(resultFile)) {
                wbOutput.write(out);
            }
            wbOutput.dispose();
            return ImportExcelResult.error(successCount, errorCount, fileId,jsonarry,resultFile);
        }
    }

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

public class TempFile {

    public static File createTempFile(UUID fileId, String type) throws IOException {
        return File.createTempFile(fileId.toString(), "." + type);
    }

    public static File getTempFile(UUID fileId, String type) {
        return new File(IOUtils.getTempDir(), fileId.toString() + "." + type);
    }

}

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    private void copyCell(Cell source, Cell target) {
        copyCellValue(source, target);
        copyCellStyle(source, target);
    }

    private void copyCellValue(Cell source, Cell target) {
        if(source == null) return;
        int type = source.getCellType();
        switch (type) {
        case Cell.CELL_TYPE_BLANK:
            // nothing to do
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            target.setCellValue(source.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            target.setCellErrorValue(source.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            target.setCellFormula(source.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            target.setCellValue(source.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            target.setCellValue(source.getStringCellValue());
            break;
        default:
            break;
        }
    }

    private void copyCellStyle(Cell source, Cell target) {
        if( source == null) return;
        CellStyle sourceStyle = source.getCellStyle();
        CellStyle targetStyle = target.getSheet().getWorkbook().createCellStyle();
        targetStyle.setAlignment(sourceStyle.getAlignment());
        targetStyle.setBorderBottom(sourceStyle.getBorderBottom());
        targetStyle.setBorderLeft(sourceStyle.getBorderLeft());
        targetStyle.setBorderRight(sourceStyle.getBorderRight());
        targetStyle.setBorderTop(sourceStyle.getBorderTop());
        targetStyle.setBottomBorderColor(sourceStyle.getBottomBorderColor());
        targetStyle.setDataFormat(sourceStyle.getDataFormat());
        targetStyle.setFillBackgroundColor(sourceStyle.getFillBackgroundColor());
        targetStyle.setFillForegroundColor(sourceStyle.getFillForegroundColor());
        targetStyle.setFillPattern(sourceStyle.getFillPattern());

        short sourceFontIdx = sourceStyle.getFontIndex();
        Font sourceFont = source.getSheet().getWorkbook().getFontAt(sourceFontIdx);
        Font targetFont = target.getSheet().getWorkbook().createFont();
        targetFont.setBoldweight(sourceFont.getBoldweight());
        targetFont.setCharSet(sourceFont.getCharSet());
        targetFont.setColor(sourceFont.getColor());
        targetFont.setFontHeight(sourceFont.getFontHeight());
        targetFont.setFontName(sourceFont.getFontName());
        targetFont.setItalic(sourceFont.getItalic());
        targetFont.setStrikeout(sourceFont.getStrikeout());
        targetFont.setTypeOffset(sourceFont.getTypeOffset());
        targetFont.setUnderline(sourceFont.getUnderline());

        targetStyle.setFont(targetFont);
        targetStyle.setHidden(sourceStyle.getHidden());
        targetStyle.setIndention(sourceStyle.getIndention());
        targetStyle.setLeftBorderColor(sourceStyle.getLeftBorderColor());
        targetStyle.setLocked(sourceStyle.getLocked());
        targetStyle.setRightBorderColor(sourceStyle.getRightBorderColor());
        targetStyle.setRotation(sourceStyle.getRotation());
        targetStyle.setTopBorderColor(sourceStyle.getTopBorderColor());
        targetStyle.setVerticalAlignment(sourceStyle.getVerticalAlignment());
        targetStyle.setWrapText(sourceStyle.getWrapText());

        target.setCellStyle(targetStyle);
    }
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    private Pair<String, String> getStringCellValue(Cell cell, String colName, boolean allowEmpty) {
        String msg = null;
        if(cell==null) {
            msg = "单元格'" + colName + "'不允许为空";
            return new Pair<>(null, msg);
        }
        int cellType = cell.getCellType();
        String value = null;
        if (cellType == Cell.CELL_TYPE_STRING || cellType == Cell.CELL_TYPE_BLANK) {
            value = cell.getStringCellValue();
        } else {
            msg = "单元格'" + colName + "'不是字符串类型";
            return new Pair<>(value, msg);
        }
        if (!allowEmpty && StringUtils.isEmptyWithTrim(value)) {
            msg = "单元格'" + colName + "'不允许为空";
        }

        return new Pair<>(value, msg);
    }

 

private Pair<Double, String> getDoubleCellValue(Cell cell, String colName, boolean allowEmpty) {
        String msg = null;
        if(cell==null) {
            msg = "单元格'" + colName + "'不允许为空";
            return new Pair<>(null, msg);
        }
        int cellType = cell.getCellType();
        Double value = null;
        if (cellType == Cell.CELL_TYPE_NUMERIC) {
            value = cell.getNumericCellValue();
        } else if (cellType == Cell.CELL_TYPE_STRING || cellType == Cell.CELL_TYPE_BLANK) {
            String valStr = cell.getStringCellValue();
            if (!allowEmpty && StringUtils.isEmptyWithTrim(valStr)) {
                msg = "单元格'" + colName + "'不允许为空";
            }
            try {
                value = Convert.toDouble(valStr);
            } catch (Exception e) {
                msg = "单元格'" + colName + "'是字符串类型，但无法转为数值";
            }
        } else {
            msg = "单元格'" + colName + "'不是数值类型，也不是可以转为数值类型的字符串";
        }
        return new Pair<>(value, msg);
    }

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------