使用POI对两表的数据对比筛选

2表的第八列与1表的第一列进行匹配，将匹配到的1表数据，放入表3中

package com.peier.controller;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.junit.Test;

public class test02 {
	@Test
    public void readExr01() throws Exception {
		ArrayList<String> list = new ArrayList<String>();
		// 1. 获取读取文件的输入流\\\C:\Users\durian\Desktop
        FileInputStream in2 = new FileInputStream("C:\\Users\\durian\\Desktop\\新建文件夹\\2.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(in2);
        HSSFSheet sheet = workbook.getSheetAt(0);
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        int maxRow1 =sheet.getLastRowNum();
        //System.out.println("行数"+maxRow1);
        for (int j = 1; j < maxRow1 + 1; j++) {
        	//HSSFRow outrow = outsheet.createRow(j);//
            Row row = sheet.getRow(j);
            if (row != null) {
            int maxRow = row.getLastCellNum();
            //System.out.println("列数"+maxRow);
            //for (int i = 0; i < maxRow + 1; i++) {
            ///短的第八列
            int i=7;
            	String value ="";
            	Cell cell = row.getCell(i);
            	HSSFCell outcell;
            	if (cell != null) {
	            	int cellType=cell.getCellType();
	            	switch (cellType) {
						case Cell.CELL_TYPE_BOOLEAN:
							System.out.println(cell.getBooleanCellValue());
							break;
						case Cell.CELL_TYPE_NUMERIC:
							System.out.println(cell.getNumericCellValue());
							break;
						case Cell.CELL_TYPE_STRING:
							//System.out.println(cell.getStringCellValue());
							//readExcel03(cell.getStringCellValue());
							list.add(cell.getStringCellValue());
							break;
						case Cell.CELL_TYPE_BLANK:
							break;
						case Cell.CELL_TYPE_ERROR:
							System.out.println(cell.getErrorCellValue());
							break;
						case Cell.CELL_TYPE_FORMULA:
							System.out.println(evaluator.evaluate(cell).getNumberValue());
							break;
					}
            	}else {
            		System.out.println(" ");
            	}
            //}
            }
        }
        in2.close();
        ArrayList chack01 = chack01(list,evaluator);
        chack(chack01,evaluator);
	}
	public ArrayList chack01(ArrayList list,FormulaEvaluator evaluator02) throws Exception {
		
		ArrayList<Integer> arrayList = new ArrayList<Integer>();
		FileInputStream in2 = new FileInputStream("C:\\Users\\durian\\Desktop\\新建文件夹\\1.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(in2);
        HSSFSheet sheet = workbook.getSheetAt(0);
        //FormulaEvaluator evaluator02 = workbook.getCreationHelper().createFormulaEvaluator();
        int maxRow1 =sheet.getLastRowNum();
        //System.out.println("行数"+maxRow1);
        for (int j = 1; j < maxRow1 + 1; j++) {
        	//HSSFRow outrow = outsheet.createRow(j);//
            Row row = sheet.getRow(j);
            if (row != null) {
            int maxRow = row.getLastCellNum();
            //System.out.println("列数"+maxRow);
            //for (int i = 0; i < maxRow + 1; i++) {
            ///长的第一列
            int i=0;
            	String value ="";
            	Cell cell = row.getCell(i);
            	HSSFCell outcell;
            	if (cell != null) {
	            	int cellType=cell.getCellType();
	            	switch (cellType) {
						case Cell.CELL_TYPE_BOOLEAN:
							System.out.println(cell.getBooleanCellValue());
							break;
						case Cell.CELL_TYPE_NUMERIC:
							System.out.println(cell.getNumericCellValue());
							break;
						case Cell.CELL_TYPE_STRING:
							//System.out.println(cell.getStringCellValue());
							for(int s=0;s<list.size();s++) {
								if(cell.getStringCellValue().equals(list.get(s))) {
									arrayList.add(j);
								}
							}
							break;
						case Cell.CELL_TYPE_BLANK:
							break;
						case Cell.CELL_TYPE_ERROR:
							System.out.println(cell.getErrorCellValue());
							break;
						case Cell.CELL_TYPE_FORMULA:
							System.out.println(evaluator02.evaluate(cell).getNumberValue());
							break;
					}
            	}else {
            		System.out.println(" ");
            	}
            //}
            }
        }
        in2.close();
        return arrayList;
	}
	public void chack(ArrayList list,FormulaEvaluator evaluator03) throws Exception {
		list.add(0, 0);
		// 1. 获取读取文件的输入流\\\C:\Users\durian\Desktop
        FileInputStream in = new FileInputStream("C:\\Users\\durian\\Desktop\\新建文件夹\\1.xls");
        // 2. 创建workbook，把输入流传递进去
        HSSFWorkbook workbook = new HSSFWorkbook(in);
        HSSFWorkbook outwork = new HSSFWorkbook();///后缀线是输出代码
        HSSFSheet outsheet = outwork.createSheet();
        // 3. 根据workbook获取sheet
        HSSFSheet sheet = workbook.getSheetAt(0);
        //FormulaEvaluator evaluator03 = workbook.getCreationHelper().createFormulaEvaluator();
        int maxRow1 =sheet.getLastRowNum();
        System.out.println("行数"+maxRow1);
        
        for (int j = 0; j < list.size(); j++) {
        	HSSFRow outrow = outsheet.createRow(j);//输出的行
            Row row = sheet.getRow((Integer)list.get(j));//读出的行
            if (row != null) {
            int maxRow = row.getLastCellNum();//读出的行数
            System.out.println("列数"+maxRow);

            for (int i = 0; i < maxRow + 1; i++) {
            	String value ="";
            	Cell cell = row.getCell(i);
            	HSSFCell outcell;
            	if (cell != null) {
	            	int cellType=cell.getCellType();
	            	switch (cellType) {
						case Cell.CELL_TYPE_BOOLEAN:
							System.out.println(cell.getBooleanCellValue());
							outcell =outrow.createCell(i);
			            	outcell.setCellValue(cell.getBooleanCellValue());///
							break;
						case Cell.CELL_TYPE_NUMERIC:
							System.out.println(cell.getNumericCellValue());
							outcell =outrow.createCell(i);
			            	outcell.setCellValue(cell.getNumericCellValue());///
							break;
						case Cell.CELL_TYPE_STRING:
							System.out.println(cell.getStringCellValue());
							outcell =outrow.createCell(i);
							outcell.setCellValue(cell.getStringCellValue());///
							break;
						case Cell.CELL_TYPE_BLANK:
							break;
						case Cell.CELL_TYPE_ERROR:
							System.out.println(cell.getErrorCellValue());
							outcell =outrow.createCell(i);
			            	outcell.setCellValue(cell.getErrorCellValue());///
							break;
						case Cell.CELL_TYPE_FORMULA:
							System.out.println(evaluator03.evaluate(cell).getNumberValue());
							outcell =outrow.createCell(i);
			            	outcell.setCellValue(evaluator03.evaluate(cell).getNumberValue());///
							break;
					}
            	}else {
            		System.out.println(" ");
            		outcell =outrow.createCell(i);
	            	outcell.setCellValue("");///
            	}
            }}
        }
        FileOutputStream out = new FileOutputStream("C:\\Users\\durian\\Desktop\\新建文件夹\\3.xls");/
        outwork.write(out);/
        out.close();//
        in.close();
	}
}