include.excel_import.XlsValidator.java Source code

Introduction

Here is the source code for include.excel_import.XlsValidator.java

Source

/*
 *this file is part of nseer erp
 *Copyright (C)2006-2010 Nseer(Beijing) Technology co.LTD/http://www.nseer.com 
 *
 *This program is free software; you can redistribute it and/or
 *modify it under the terms of the GNU General Public License
 *as published by the Free Software Foundation; either
 *version 2 of the License, or (at your option) any later version.
 */
package include.excel_import;

import java.io.InputStream;
import java.io.FileInputStream;
import java.util.Iterator;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;

public class XlsValidator extends Path {
    private String fileName = null;
    private HSSFWorkbook wb = null;
    private StringBuffer message = new StringBuffer("");
    private XlsInfo xlsInfo = null;

    /**
     * excel??
     * ?
     * @param fileName ??
     */
    public void setFile(String fileName) throws Exception {
        this.fileName = getPath() + "/conf/excel_import/" + fileName;
        if (fileName == null) {
            throw new Exception("Exception: ?Excel??");
        }
        try {
            InputStream input = new FileInputStream(this.fileName);
            POIFSFileSystem fs = new POIFSFileSystem(input);
            wb = new HSSFWorkbook(fs);
            xlsInfo = new XlsInfo(wb);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public boolean validate() {
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            HSSFSheet sheet = wb.getSheetAt(i);
            if (validateSheet(sheet)) {
                String sheetName = wb.getSheetName(i);
                if (validateTitle(sheet, sheetName)) {
                    if (validateContent()) {
                        if (validateValue()) {
                            return true;
                        }
                    } // end of if ()
                }
                sheetName = null;
            }
            sheet = null;
        }
        return false;
    }

    private boolean validateSheet(HSSFSheet sheet) {
        if (sheet == null) {
            message.append("sheet");
            return false;
        } else if (sheet.getNumMergedRegions() > 0) {
            message.append("?<br>");
            return false;
        }
        return true;
    }

    /**
     * ???? 
     * @return true:
     */
    private boolean validateTitle(HSSFSheet sheet, String sheetName) {

        HSSFRow row = sheet.getRow((short) 0);
        if (row == null) {
            //
            message.append(",SHEET");
            return false;
        }
        Iterator cells = row.cellIterator();
        int size = 0;
        while (cells.hasNext()) {
            HSSFCell cell = (HSSFCell) cells.next();
            size++;
        }
        for (int j = 0; j < size - 1; j++) {
            HSSFCell cell = row.getCell((short) j);
            if (cell == null) {
                return false;
            } else {
                if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING) {
                    message.append(""/*sheetName*/).append("");
                    message.append(j + 1).append("?<br>");
                    return false;
                }
                if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                    message.append(""/*sheetName*/).append("").append(j + 1).append("<br>");
                    return false;
                }
            }
        }
        return true;
    }

    /**
     * validateValue
     * 
     * @return false:???
     */
    public boolean validateType(HSSFCell cell, String columnName, String sheetName) {
        try {
            String itemType = xlsInfo.getColumnType(columnName, sheetName);
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                if (itemType.equals("INT") || itemType.equals("DOUBLE") || itemType.equals("DATE")) {
                    return false;
                }
                break;
            default:
                break;
            }
            itemType = null;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean validateContent() {
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            HSSFSheet sheet = wb.getSheetAt(i);
            String sheetName = wb.getSheetName(i);
            int rowCount = xlsInfo.getRowCount(sheetName);
            for (int j = 1; j < rowCount; j++) {
                HSSFRow row = sheet.getRow(j);
                for (int n = 0; n < xlsInfo.getColumnCount(sheetName); n++) {
                    HSSFCell cell = row.getCell((short) n);
                    if (cell == null) {
                        message.append(""/*sheetName*/).append("(");
                        message.append(n + 1).append(":");
                        message.append(j + 1).append(")(");
                        message.append(""/*sheetName*/).append("");
                        message.append(rowCount).append("??)<br>");
                        return false;
                    }
                }
            }
        }
        return true;
    }

    public boolean validateValue() {
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            HSSFSheet sheet = wb.getSheetAt(i);
            String sheetName = wb.getSheetName(i);
            int rowCount = xlsInfo.getRowCount(sheetName);
            for (int j = 1; j < rowCount; j++) {
                HSSFRow row = sheet.getRow(j);
                HSSFRow row1 = sheet.getRow(0);//??itemname
                for (int n = 0; n < xlsInfo.getColumnCount(sheetName); n++) {
                    HSSFCell cell1 = row1.getCell((short) n);
                    String itemname = cell1.getStringCellValue();
                    HSSFCell cell = row.getCell((short) n);
                    if (!validateType(cell, itemname, sheetName)) {
                        message.append("").append(n + 1);
                        message.append(" : ").append(j + 1);
                        message.append("????<br>");
                        return false;
                    }
                    cell1 = null;
                    itemname = null;
                    cell = null;
                }
                row = null;
                row1 = null;
            }
            sheet = null;
            sheetName = null;
        }
        return true;
    }

    public String getMessage() {
        return message.toString();
    }

}