java处理excel

package  com.jiao;
import  java.io.ByteArrayInputStream;
import  java.io.ByteArrayOutputStream;
import  java.io.FileInputStream;
import  java.io.IOException;
import  java.io.ObjectInputStream;
import  java.io.ObjectOutputStream;

import  java.io.FileOutputStream;
import  java.util.ArrayList;
import  java.util.List;

import  org.apache.poi.hssf.usermodel.HSSFCell;
import  org.apache.poi.hssf.usermodel.HSSFDateUtil;
import  org.apache.poi.hssf.usermodel.HSSFRow;
import  org.apache.poi.hssf.usermodel.HSSFSheet;
import  org.apache.poi.hssf.usermodel.HSSFWorkbook;

public   class  POItest  ... {

    public static String outputFile   = "c:/Temp/test.xls";
    List list = new ArrayList();
    public static String fileToBeRead = "D://work//new_bmp2//bpm2_v1_00//data//sql//system-data-part3.xls";
    //public static String fileToBeRead = "D://FF.xls";
    public void CreateExcel() ...{
        try ...{
            // 创建新的Excel 工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 在Excel工作簿中建一工作表，其名为缺省值
            // 如要新建一名为"效益指标"的工作表，其语句为：
            // HSSFSheet sheet = workbook.createSheet("效益指标");
            HSSFSheet sheet = workbook.createSheet();
            // 在索引0的位置创建行（最顶端的行）
            HSSFRow row = sheet.createRow((short)0);
            // 在索引0的位置创建单元格（左上端）
            HSSFCell cell = row.createCell((short)0);
            // 定义单元格为字符串类型
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 在单元格中输入一些内容
            cell.setCellValue("sweater");
            // 新建一输出文件流
            FileOutputStream fOut = new FileOutputStream(outputFile);
            // 把相应的Excel 工作簿存盘
            workbook.write(fOut);
            fOut.flush();
            // 操作结束，关闭文件
            fOut.close();
            System.out.println("文件生成...");
        } catch(Exception e) ...{
            System.out.println("已运行 xlCreate() : " + e);
        }
    }

    /** *//**
     * 读取excel，遍历各个小格获取其中信息，并判断其是否是手机号码，并对正确的手机号码进行显示 注意： 1.sheet，
     * 以0开始，以workbook.getNumberOfSheets()-1结束
     * 2.row，以0开始(getFirstRowNum)，以getLastRowNum结束
     * 3.cell，以0开始(getFirstCellNum)，以getLastCellNum结束, 结束的数目不知什么原因与显示的长度不同，可能会偏长
     */

    public void readExcel() ...{
        // 将被表示成1.3922433397E10的数转化为13922433397
        // DecimalFormat df = new DecimalFormat("#");
        try ...{
            // 创建对Excel工作簿文件的引用
            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
            fileToBeRead));
            // System.out.println("===SheetsNum===" +
            // workbook.getNumberOfSheets());//获取sheet数
            for(int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) ...{
                if(null != workbook.getSheetAt(numSheets)) ...{
                    HSSFSheet aSheet = workbook.getSheetAt(numSheets);// 获得一个sheet
                    if(!aSheet.isDisplayFormulas())// 判断是显示aSheet中公式的值
                        aSheet.setDisplayFormulas(true);
                    // System.out.println("+++getFirstRowNum+++" +
                    // aSheet.getFirstRowNum());
                    // System.out.println("+++getLastRowNum+++" +
                    // aSheet.getLastRowNum());
                    for(int rowNumOfSheet = 0; rowNumOfSheet <= aSheet
                    .getLastRowNum(); rowNumOfSheet++) ...{
                        if(null != aSheet.getRow(rowNumOfSheet)) ...{
                            HSSFRow aRow = aSheet.getRow(rowNumOfSheet);
                            // System.out.println(">>>getFirstCellNum<<<"+aRow.getFirstCellNum());
                            // System.out.println(">>>getLastCellNum<<<"+aRow.getLastCellNum());
                            for(short cellNumOfRow = 0; cellNumOfRow <= aRow
                            .getLastCellNum(); cellNumOfRow++) ...{
                                // System.out.println(">>>rowNumOfSheet<<<"+rowNumOfSheet);
                                // System.out.println(">>>cellNumOfRow<<<"+cellNumOfRow);
                                if(null != aRow.getCell(cellNumOfRow)) ...{
                                    HSSFCell aCell = aRow.getCell(cellNumOfRow);
                                    int cellType = aCell.getCellType();
                                    switch(cellType) ...{
                                        case HSSFCell.CELL_TYPE_NUMERIC :// Numeric
                                            String strCell = String.valueOf(aCell.getNumericCellValue());
                                            if(HSSFDateUtil.isCellDateFormatted(aCell))
                                            ...{
                                                // double sa =
                                                // aCell.getNumericCellValue();
                                                // Date dd =
                                                // HSSFDateUtil.getJavaDate(sa);
                                                // HSSFDataFormat tt = new
                                                // HSSFDataFormat(workbook);
                                                // dd.toString();
                                                System.out.println(aCell.getDateCellValue());
                                            }
                                            else
                                                System.out.println(strCell);
                                            break;
                                        case HSSFCell.CELL_TYPE_STRING :// String
                                            strCell = aCell.getStringCellValue();
                                            System.out.println(strCell);
                                            break;
                                        case HSSFCell.CELL_TYPE_FORMULA :// formula
                                            // strCell =aCell.getCellFormula();
                                            strCell = String.valueOf(aCell.getNumericCellValue());
                                            System.out.println(strCell);
                                            break;
                                        case HSSFCell.CELL_TYPE_BLANK :// blank
                                            strCell = aCell.getStringCellValue();
                                            System.out.println(strCell);
                                            break;
                                        default :
                                            System.out.println("----------------格式读入不正确！");// 其它格式的数据
                                    }
                                }
                            }
                        }
                    }
                }

                System.out.println("//sheet  end///");
            }

        } catch(Exception e) ...{
            System.out.println("ReadExcelError" + e);
        }

    }

    //阅读指定行和列的单元格的值,注意：行、列下标是从0开始的

    public void getSpecial(int numRow, int numCol) ...{
        try ...{
            // 创建对Excel工作簿文件的引用
            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));
            if(null != workbook.getSheet("BPM_LABEL")) ...{
                HSSFSheet aSheet = workbook.getSheet("BPM_LABEL");//workbook.getSheetAt(0);//获得一个sheet
                System.out.println("==========="+aSheet.getLastRowNum());
                if(numRow == -1)...{
                    numRow = aSheet.getLastRowNum();
                }
                for(int i = 2; i <= numRow; i++) ...{
                    HSSFRow aRow = aSheet.getRow(i);
                    MessageBean bean = new MessageBean();
                    for(int j= 0 ; j < numCol ; j ++ )...{
                        HSSFCell aCell = aRow.getCell((short)j);
                        if(aCell != null) ...{
                            int cellType = aCell.getCellType();
                            getValue(cellType, aCell);
                            if(j==0)...{
                                bean.setId(getValue(cellType, aCell));
                            }else if(j== 1)...{
                                bean.setMessageKey(getValue(cellType, aCell));
                            }else if(j==2)...{
                                bean.setMessageValue(getValue(cellType, aCell));
                            }
                        }
                    }
                    list.add(bean);
                }
            }
        } catch(Exception e) ...{
            System.out.println("ReadExcelError" + e);
        }
    }

    private String getValue(int cellType , HSSFCell aCell) ...{
        String strCell = "";
        switch(cellType) ...{
            case 0 ://Numeric
                strCell = String.valueOf(aCell.getNumericCellValue());
                if(HSSFDateUtil.isCellDateFormatted(aCell))...{
                    System.out.println("00000000000000000" + aCell.getDateCellValue());
                }else...{
                    return "ID:"+strCell.substring(0,strCell.indexOf("."));//System.out.println(strCell);
                }
                break;
            case 1 ://String
                strCell = aCell.getStringCellValue();
                //return strCell;//System.out.println("111111111111111111" + strCell);
                break;
            case 2 ://formula
                //aSheet.setDisplayFormulas(true);
                strCell = String.valueOf(aCell.getNumericCellValue());
                //return strCell;// System.out.println("2222222222222222222" + strCell);
                break;
            case 3 ://blank
                strCell = aCell.getStringCellValue();
                //return strCell;//System.out.println("3333333333333333333" + strCell);
                break;
            default :
                System.out.println("----------------格式读入不正确！");//其它格式的数据
        }
        return strCell;
    }
    
       public static void main(String[] args) ...{

        POItest poi = new POItest();

        //poi.CreateExcel();

        //poi.readExcel();

        poi.getSpecial(-1, (short)3);//第五行第五列的数据值 

        poi.findSame();
    }
}