outter.java

ERO系统.针对生产型、分销型、服务型企业等不同的行业特点

    public Vector getRowItemsValues(String s, int i)
        throws Exception
    {
        Vector vector = new Vector();
        int j = getTablesName().indexOf(s);
        if(j == -1)
            throw new Exception("Table not found");
        HSSFSheet hssfsheet = wb.getSheetAt(j);
        HSSFRow hssfrow = hssfsheet.getRow((short)i);
        Iterator iterator = hssfrow.cellIterator();
        int k;
        for(k = 0; iterator.hasNext(); k++)
        {
            HSSFCell hssfcell = (HSSFCell)iterator.next();
        }

        for(int l = 0; l < k; l++)
        {
            HSSFCell hssfcell1 = hssfrow.getCell((short)l);
            pump(vector, hssfcell1);
        }

        return vector;
    }

    public Vector getTablesName()
    {
        Vector vector = new Vector();
        for(int i = 0; i < wb.getNumberOfSheets(); i++)
            vector.addElement(wb.getSheetName(i));

        return vector;
    }

    public boolean hasBlank()
    {
        for(int i = 0; i < wb.getNumberOfSheets(); i++)
        {
            HSSFSheet hssfsheet = wb.getSheetAt(i);
            String s = wb.getSheetName(i);
            for(int j = 0; j < getRowCount(hssfsheet); j++)
            {
                HSSFRow hssfrow = hssfsheet.getRow(j);
                for(int k = 0; k < getColumnCount(hssfsheet); k++)
                {
                    HSSFCell hssfcell = hssfrow.getCell((short)k);
                    if(hssfcell == null)
                    {
                        message += s + "有的行或者列(列" + (k + 1) + ":行" + (j + 1) + ")为空(" + s + "记录总行数是" + getRowCount(hssfsheet) + "吗?)<br>";
                        return true;
                    }
                }

            }

        }

        return false;
    }

    private boolean isBlankColumn(int i, HSSFSheet hssfsheet)
    {
        int j = getRowCount(hssfsheet);
        for(int k = 0; k < j; k++)
        {
            HSSFRow hssfrow = hssfsheet.getRow(k);
            if(hssfrow == null)
                return true;
            HSSFCell hssfcell = hssfrow.getCell((short)i);
            if(hssfcell == null)
                return true;
            if(hssfcell.getCellType() != 3)
                return false;
        }

        return true;
    }

    private boolean isBlankRow(HSSFRow hssfrow)
    {
        if(hssfrow == null)
            return true;
        Iterator iterator = hssfrow.cellIterator();
        int i;
        for(i = 0; iterator.hasNext(); i++)
        {
            HSSFCell hssfcell = (HSSFCell)iterator.next();
        }

        for(int j = 0; j < i; j++)
        {
            HSSFCell hssfcell1 = hssfrow.getCell((short)j);
            if(hssfcell1 == null)
                return true;
            if(hssfcell1.getCellType() != 3)
                return false;
        }

        return true;
    }

    public boolean isMerged()
    {
        for(int i = 0; i < wb.getNumberOfSheets(); i++)
        {
            HSSFSheet hssfsheet = wb.getSheetAt(i);
            if(hssfsheet.getNumMergedRegions() > 0)
            {
                message += "有列合并的情况<br>";
                return true;
            }
        }

        return false;
    }

    public static void main(String args[])
    {
        Outter outter = new Outter();
        String s = "oo1.xls";
        outter.setFile(s);
        try
        {
            outter.reparedOut();
            System.out.println("rowcount:" + outter.getRowCount("Sheet1"));
        }
        catch(Exception exception)
        {
            exception.printStackTrace();
        }
        if(!outter.validate())
        {
            System.out.println("message:" + outter.getMessage());
        } else
        {
            Vector vector = outter.getTablesName();
            try
            {
                for(int i = 0; i < vector.size(); i++)
                {
                    System.out.println("vt:" + vector.elementAt(i));
                    System.out.println("rownum=" + outter.getRowCount((String)vector.elementAt(i)));
                    Vector vector1 = outter.getItemsName((String)vector.elementAt(i));
                    for(int j = 0; j < vector1.size(); j++)
                    {
                        Vector vector2 = outter.getItemValues((String)vector1.elementAt(j), (String)vector.elementAt(i));
                        for(int k = 0; k < vector2.size(); k++)
                            System.out.println("value:" + vector2.elementAt(k));

                    }

                }

            }
            catch(Exception exception1)
            {
                exception1.printStackTrace();
            }
        }
    }

    private void pump(Vector vector, HSSFCell hssfcell)
    {
        String s = getCellType(hssfcell);
        if(s.equals("INT"))
        {
            DecimalFormat decimalformat = new DecimalFormat("##");
            String s1 = String.valueOf(decimalformat.format(hssfcell.getNumericCellValue()));
            vector.addElement(s1);
        } else
        if(s.equals("DOUBLE"))
        {
            DecimalFormat decimalformat1 = new DecimalFormat("##.##");
            String s2 = String.valueOf(decimalformat1.format(hssfcell.getNumericCellValue()));
            vector.addElement(s2);
        } else
        if(s.equals("STRING"))
            vector.addElement(hssfcell.getStringCellValue());
        else
        if(s.equals("DATE"))
        {
            SimpleDateFormat simpledateformat = new SimpleDateFormat("yyyy-MM-dd");
            String s3 = String.valueOf(simpledateformat.format(hssfcell.getDateCellValue()));
            vector.addElement(s3);
        }
    }

    private void pump(Vector vector, HSSFCell hssfcell, String s)
    {
        String s1 = getCellType(hssfcell);
        if(s1.equals("INT"))
        {
            DecimalFormat decimalformat = new DecimalFormat("##");
            String s2 = String.valueOf(decimalformat.format(hssfcell.getNumericCellValue()));
            vector.addElement(s2);
        } else
        if(s1.equals("INT1"))
        {
            DecimalFormat decimalformat1 = new DecimalFormat("##");
            String s3 = String.valueOf(decimalformat1.format(hssfcell.getNumericCellValue()));
            vector.addElement(s3);
        } else
        if(s1.equals("DOUBLE"))
        {
            DecimalFormat decimalformat2 = new DecimalFormat("##.##");
            String s4 = String.valueOf(decimalformat2.format(hssfcell.getNumericCellValue()));
            vector.addElement(s4);
        } else
        if(s1.equals("STRING"))
            vector.addElement(hssfcell.getStringCellValue());
        else
        if(s1.equals("DATE"))
        {
            SimpleDateFormat simpledateformat = new SimpleDateFormat("yyyy-MM-dd");
            String s5 = String.valueOf(simpledateformat.format(hssfcell.getDateCellValue()));
            vector.addElement(s5);
        } else
        if(s1.equals("BLANK"))
            if(s.equals("STRING"))
                vector.addElement(" ");
            else
            if(s.equals("INT"))
                vector.addElement(new Integer(0));
            else
            if(s.equals("DOUBLE"))
                vector.addElement(new Double(0.0D));
            else
            if(s.equals("DATE"))
                vector.addElement("0000-00-00");
            else
            if(s.equals("UNKNOWN"))
                vector.addElement(" ");
    }

    private void removeColumn(int i, HSSFSheet hssfsheet)
    {
        for(int j = 0; j < getRowCount(hssfsheet); j++)
        {
            HSSFRow hssfrow = hssfsheet.getRow(j);
            HSSFCell hssfcell = hssfrow.getCell((short)i);
            if(hssfrow != null)
                hssfrow.removeCell(hssfcell);
        }

    }

    public void reparedOut()
        throws Exception
    {
        if(fileName == null)
            throw new Exception("Exception: File should be asigned!");
        try
        {
            FileInputStream fileinputstream = new FileInputStream(fileName);
            POIFSFileSystem poifsfilesystem = new POIFSFileSystem(fileinputstream);
            wb = new HSSFWorkbook(poifsfilesystem);
        }
        catch(Exception exception)
        {
            exception.printStackTrace();
        }
    }

    public void setFile(String s)
    {
        fileName = getPath() + "/conf/excel_import/" + s;
    }

    public boolean validate()
    {
        return !isMerged() && !hasBlank() && !blankTitle() && validateValue();
    }

    public boolean validateType(HSSFCell hssfcell, String s, String s1)
    {
        try
        {
            String s2 = getItemType(s, s1);
            switch(hssfcell.getCellType())
            {
            case 1: // '\001'
                if(s2.equals("INT") || s2.equals("DOUBLE") || s2.equals("DATE"))
                    return false;
                break;
            }
        }
        catch(Exception exception)
        {
            exception.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean validateValue()
    {
        for(int i = 0; i < wb.getNumberOfSheets(); i++)
        {
            HSSFSheet hssfsheet = wb.getSheetAt(i);
            String s = wb.getSheetName(i);
            for(int j = 1; j < getRowCount(hssfsheet); j++)
            {
                HSSFRow hssfrow = hssfsheet.getRow(j);
                HSSFRow hssfrow1 = hssfsheet.getRow(0);
                for(int k = 0; k < getColumnCount(hssfsheet); k++)
                {
                    HSSFCell hssfcell = hssfrow1.getCell((short)k);
                    String s1 = hssfcell.getStringCellValue();
                    HSSFCell hssfcell1 = hssfrow.getCell((short)k);
                    if(!validateType(hssfcell1, s1, s))
                    {
                        message += "第" + (k + 1) + "列 : 第" + (j + 1) + "行变量类型不符合<br>";
                        return false;
                    }
                }

            }

        }

        return true;
    }
}