Java POI Excel操作，List实体类的一些操作

使用List的实体类做Excel表的比对。日常操作够用
菜鸡一枚，可能有考虑不周的地方

关键词：两列同时比对，不同数据类型

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;

/*
 * 比较两个表特定的两列数据，输出匹配成功的新值，保存到特定的Excel表中
 * 由于匹配的列1和列2存在倒置也能匹配的情况，没有使用Map（即key不存在唯一性）
 * 汇总表只有一份，避免重复读取，直接遍历整表，保存必要的数据到List
 * 考虑到表中不同数据的值也不同，使用实体类
 * 直接对匹配生效的数据集成统计到结果List，存在相同情况（同一类型）的作为同一行，长度累加
 * 最后输出数据到新的记录表
 */
public class rawSelect {
	//表头自拟
	//路径
	static String tablepath = "G:\\XXX.xls";
	static String consequencepath = "G:\\XXX.xls";

	//待匹配的单一汇总表
	List<gdclass> teabag = new ArrayList<gdclass>();
	//待输出的筛选结果
	List<conclusion> milk = new ArrayList<conclusion>();
	
	//标记
	int numyg = 0;
	int numqx = 0;
	int numnotfound = 0;

	//递归遍历样本,递归时匹配
	private void selectFile(File file) {
		File[] files = file.listFiles();
		for (File file2 : files) {
			//递归调用
			if (file2.isDirectory()) {
				selectFile(file2);
			}
			if (file2.isFile() && file2.getName().endsWith("XX.xls")) {
				try {
					String filePath = file2.getAbsolutePath();
//						System.out.println(filePath);

					FileInputStream fis = new FileInputStream(filePath);
					BufferedInputStream bis = new BufferedInputStream(fis);
					POIFSFileSystem pFileSystem = new POIFSFileSystem(bis);
					HSSFWorkbook hssfWorkbook = new HSSFWorkbook(pFileSystem);
					HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
					
					//选取
					Iterator<Row> rowI = hssfSheet.iterator();
					rowI.next();
					while(rowI.hasNext()) {
						HSSFRow row = (HSSFRow) rowI.next();
						//起点
						String s = row.getCell(1).getStringCellValue();
						//终点
						String e = row.getCell(2).getStringCellValue();
						numyg++;
						numqx++;
					    boolean findPoint = false; 
						//匹配
						for (gdclass gd : teabag) {
							if ((s.equals(gd.getStartP()) && e.equals(gd.getEndP())) ||
									(s.equals(gd.getEndP()) && e.equals(gd.getStartP()))) {
								
								//返回等级
								String level = row.getCell(8).getStringCellValue();
								boolean hasfind = false;
									for(int z = 0 ;z < milk.size();z++) {
										conclusion temp = milk.get(z);
										if(temp.getTubeR().equals(gd.getTubeR())&&temp.getLevel().equals(level)) {
											temp.setLength(temp.getLength()+gd.getTubeL());
											temp.setNumber(temp.getNumber()+1);
											hasfind = true;
											break;
										}
									}
									if(!hasfind) {
										milk.add(new conclusion(gd.getTubeR(), level, 1, gd.getTubeL()));
									}
									findPoint = true;
									break;
							}
						}
						if(!findPoint) {
							numnotfound ++;
						}
					}
					hssfWorkbook.close();
					fis.close();
					bis.close();
					pFileSystem.close();
				}
				catch (Exception e) {
					// TODO: handle exception
				}
			}
		}
	}
	
	//保存数据到teabag
	private void referTable() {
		File file = new File(tablepath);
		if (file.exists()) {
			try {
				FileInputStream s = new FileInputStream(file);
				HSSFWorkbook ref = new HSSFWorkbook(s);
				HSSFSheet sheet = ref.getSheet("XX");//对应页，看自己需要
				Iterator<Row> rowIter = sheet.iterator();
				rowIter.next();
				while (rowIter.hasNext()) {
					HSSFRow row = (HSSFRow) rowIter.next();
					//读取所需数据
					String cell0 = row.getCell(0).getStringCellValue();
					String cell1 = row.getCell(1).getStringCellValue();
					Double cell6 = row.getCell(6).getNumericCellValue();
					Double cell7 = row.getCell(7).getNumericCellValue();
					gdclass gd = new gdclass(cell0, cell1, cell6, cell7);
					teabag.add(gd);
				}
				ref.close();
				s.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
		}
	}
	
	//输出结果
	private void outputConclusion() {
		try {
			FileInputStream fis = new FileInputStream(consequencepath);
			BufferedInputStream bis = new BufferedInputStream(fis);
			POIFSFileSystem pFileSystem = new POIFSFileSystem(bis);
			HSSFWorkbook hssfWorkbook = new HSSFWorkbook(pFileSystem);
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
			HSSFSheet hssfSheet1 = hssfWorkbook.getSheetAt(1);
			
			for (int i = 0; i < milk.size(); i++) {
				HSSFRow row = hssfSheet.createRow(i+1);
				conclusion temp = milk.get(i);
				//
				HSSFCell c1 = row.createCell(0);
				c1.setCellType(CellType.NUMERIC);
				c1.setCellValue(temp.getTubeR());
				///大概这样，类型不确定可以自己写一个getCellType之类的函数
				///需要累加结果的话，输出的时候累加就好，此处省略后面内容...
			}
			
			
			FileOutputStream fos = new FileOutputStream("G:\\XXXX.xls");
			hssfWorkbook.write(fos);
			fos.close();
			hssfWorkbook.close();
			fis.close();
			bis.close();
			pFileSystem.close();
			
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	

	//汇总表对应类别的实体类
	public class gdclass{
		private String startP;
		private String endP;
		private Double tubeR;
		private Double tubeL;
		
		public gdclass(String startP,String endP,Double tubeR,Double tubeL) {
			this.setStartP(startP);
			this.setEndP(endP);
			this.setTubeR(tubeR);
			this.setTubeL(tubeL);
		}

		public String getStartP() {
			return startP;
		}

		public void setStartP(String startP) {
			this.startP = startP;
		}
		//一万个get set飘过
	}
	
	//输出结果的实体类
	public class conclusion {
		private Double tubeR;
		private String level;
		private int number;
		private Double Length;
		
		public conclusion(Double tubeR,String level,int number,Double Length) {
			this.setTubeR(tubeR);
			this.setLevel(level);
			this.setNumber(number);
			this.setLength(Length);
		}
		public Double getTubeR(){
			return tubeR;
		}
		public void setTubeR(Double tubeR) {
			this.tubeR = tubeR;
		}
		//一万个get set飘过
		
	}
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		rawSelect rs = new rawSelect();
		//读汇总表保存数据
		rs.referTable();
		//选有效结果
		String targetpath = "G:\\需遍历文件夹及子文件夹下的文件的父目录名";
		File file = new File(targetpath);
		rs.selectFile(file);
		//整合输出
		rs.outputConclusion();
		return;
	}
}