excel处理

package utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;

import pojo.Case;
import pojo.WriteBack;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;



public class ExcelUtils {
	/*
	 * 	根据caseId取行号
	 */
	public static Map<String, Integer> rowIdentifierRowIndexMap = new HashMap<String, Integer>();
	/*
	 * 	列名和列号的映射
	 */
	public static Map<String, Integer> cellNameCellIndexMap = new HashMap<String, Integer>();
	
	public static List<WriteBack> writeBacks = new ArrayList<WriteBack>();

	static {
		loadRowIdentifierRowNumMapAndCellNameCellNumMap("src/test/resources/测试用例.xlsx","用例");
	}
	
	public static void loadRowIdentifierRowNumMapAndCellNameCellNumMap(String path,String sheetName) {
		FileInputStream fileInputStream = null ;
		try {
			fileInputStream = new FileInputStream(new File(path));
			/*
			 * 	新建一个excel工作簿
			 */
			Workbook workbook = WorkbookFactory.create(fileInputStream);
			/*
			 * 	新建一个sheet表
			 */
			Sheet sheet = workbook.getSheet(sheetName);
			/*
			 * 	获取第一行数据-标题行
			 */
			Row titleRow = sheet.getRow(0);
			/*
			 * 	获取第一行数据最后一列的列号
			 */
			int lastCellNum = titleRow.getLastCellNum();
			/*
			 * 	循环处理每一列，取出每一列里面的字段名，保存到数组
			 */
			String[] fields = new String[lastCellNum];
			/*
			 * 	循环拿到并处理每一列
			 */
			for (int i = 0; i < lastCellNum; i++) {
				/*
				 * 	根据列索引获取对应的列
				 */
				Cell cell = titleRow.getCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
				/*
				 * 	设置列的类型为字符串
				 */
				cell.setCellType(CellType.STRING);
				/*
				 * 	获取列的值
				 */
				String titlt = cell.getStringCellValue();
				titlt = titlt.substring(0, titlt.indexOf("("));
				int cellIndex = cell.getAddress().getColumn();
				cellNameCellIndexMap.put(titlt, cellIndex);
//				System.out.println("列索引："+"【"+cellIndex+"】;"+"对应的字段值：【"+titlt+"】");
				}
			/*
			 * 	获取最后一行行索引
			 */
			int lastRowIndex = sheet.getLastRowNum();
			/*
			 * 	循环处理每一行数据，每一行对应一行case用例对象
			 */
			for (int i = 1; i <= lastRowIndex; i++) {
				/*
				 * 	拿到一个数据行
				 */
				Row dataRow = sheet.getRow(i);
				if ( isEmptyRow(dataRow)||dataRow == null) {
					continue;
				}
			
				/*
				 * 	根据列索引获取对应的列
				 */
				Cell cell = dataRow.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
				/*
				 * 	设置列的类型为字符串
				 */
				cell.setCellType(CellType.STRING);
				/*
				 * 	获取第一列值caseId
				*/
				String caseId = cell.getStringCellValue();
				if (caseId.trim().length() == 0) {
					continue;
				}
				/*
				 * 	获取行索引
				 */
				int rowIndex = dataRow.getRowNum();
				rowIdentifierRowIndexMap.put(caseId, rowIndex);
				System.out.println("行索引："+"【"+rowIndex+"】;"+"对应的caseId值：【"+caseId+"】");
				}
		}catch (Exception e) {
				// TODO: handle exception
			e.printStackTrace();
			}
	}
	
	/**
	 * 	加载excel数据
	 * @param <T>
	 * @param path
	 * @param sheetName
	 * @param clazz
	 * @return
	 */
	public static <T> List<T> load(String path,String sheetName,Class<T> clazz) {
		List<T> list = new ArrayList<T>();
		FileInputStream fileInputStream = null ;
		try {
			fileInputStream = new FileInputStream(new File(path));
			/*
			 * 	新建一个excel工作簿
			 */
			Workbook workbook = WorkbookFactory.create(fileInputStream);
			/*
			 * 	新建一个sheet表
			 */
			Sheet sheet = workbook.getSheet(sheetName);
			/*
			 * 	获取第一行数据-标题行
			 */
			Row titleRow = sheet.getRow(0);
			/*
			 * 	获取第一行数据最后一列的列号
			 */
			int lastCellNum = titleRow.getLastCellNum();
			/*
			 * 	循环处理每一列，取出每一列里面的字段名，保存到数组
			 */
			String[] fields = new String[lastCellNum];
			/*
			 * 	循环拿到并处理每一列
			 */
			for (int i = 0; i < lastCellNum; i++) {
				/*
				 * 	根据列索引获取对应的列
				 */
				Cell cell = titleRow.getCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
				/*
				 * 	设置列的类型为字符串
				 */
				cell.setCellType(CellType.STRING);
				/*
				 * 	获取列的值
				 */
				String titlt = cell.getStringCellValue();
				titlt = titlt.substring(0, titlt.indexOf("("));
				fields[i] = titlt;
			}
			/*
			 * 	获取最后一行行索引
			 */
			int lastRowIndex = sheet.getLastRowNum();
			/*
			 * 	循环处理每一行数据，每一行对应一行case用例对象
			 */
			for (int i = 1; i <= lastRowIndex; i++) {
				
				/*
				 * 	每循环一行准备一个对象，通过字节码创建对象
				 */
				T obj = clazz.newInstance();
				/*
				 * 	拿到一个数据行
				 */
				Row dataRow = sheet.getRow(i);
				if (dataRow == null || isEmptyRow(dataRow)) {
					continue;
				}
				/*
				 * 	根据列索引获取对应的列
				 */
				Cell cell = dataRow.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
				/*
				 * 	设置列的类型为字符串
				 */
				cell.setCellType(CellType.STRING);
				/*
				 * 	获取第一列值caseId
				*/
				String caseId = cell.getStringCellValue();
				if (caseId.trim().length() == 0) {
					continue;
				}
				/*
				 *	拿到此数据行上的每一列，将数据封装到对象
				 */
				for (int j = 0; j < lastCellNum; j++) {
					/*
					 * 	根据列索引获取对应的列
					 */
					Cell cell1 = dataRow.getCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
					/*
					 * 	设置列的类型为字符串
					 */
					cell1.setCellType(CellType.STRING);
					/*
					 * 	获取列的值
					 */
					String value = cell1.getStringCellValue();
					/*
					 * 	封装到对象(已经拿到属性值，通过反射放到对象)
					 * 	获取要反射的方法名
					 */
					String methodName = "set"+fields[j];
					/*
					 * 	获取要反射的方法对象
					 */
					Method method = clazz.getMethod(methodName, String.class);
					/*
					 * 	完成反射调用
					 */
					method.invoke(obj, value);
				}
				list.add(obj);
			}
			return list;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeInputStream(fileInputStream);
		}
		return null; 
	}


	private static void closeInputStream(FileInputStream fileInputStream) {
		try {
			fileInputStream.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	
	/**
	 *	判断每行数据中的列都有数据返回true，否则返回false
	 * @param dataRow
	 * @return
	 */
	private static boolean isEmptyRow(Row dataRow) {
		/*
		 * 	获取行数据最后一列列号
		 */
		int lastCellNum = dataRow.getLastCellNum();
		for (int i = 0; i < lastCellNum; i++) {
			/*
			 * 	根据列索引获取对应的列
			 */
			Cell cell = dataRow.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
			/*
			 * 	设置列的类型为字符串
			 */
			cell.setCellType(CellType.STRING);
			/*
			 * 	获取列的值
			 */
			String value = cell.getStringCellValue().trim();

			if (value == null || value.length() == 0  ){
				return true;
			}
		}
		return false;
	}
	
	public static void writeBackData(String path,String sheetName,String caseId,String cellName,String result) {
		FileInputStream fileInputStream = null ;
		FileOutputStream outputStream = null;
		try {
			fileInputStream = new FileInputStream(new File(path));
			/*
			 * 	新建一个excel工作簿
			 */
			Workbook workbook = WorkbookFactory.create(fileInputStream);
			/*
			 * 	新建一个sheet表
			 */
			Sheet sheet = workbook.getSheet(sheetName);
			int rowNum = rowIdentifierRowIndexMap.get(caseId);
			Row row = sheet.getRow(rowNum);
			int cellNum = cellNameCellIndexMap.get(cellName);
			Cell cell = row.getCell(cellNum, MissingCellPolicy.CREATE_NULL_AS_BLANK);
			cell.setCellType(CellType.STRING);
			cell.setCellValue(result);
			outputStream = new FileOutputStream(new File(path));
			workbook.write(outputStream);			
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			closeOutputStream(outputStream);
			closeInputStream(fileInputStream);
			
		}
	}

	public static void batchWriteBackBDatas(String path,String sheetName) {
		FileInputStream fileInputStream = null ;
		FileOutputStream outputStream = null;
		try {
			fileInputStream = new FileInputStream(new File(path));
			/*
			 * 	新建一个excel工作簿
			 */
			Workbook workbook = WorkbookFactory.create(fileInputStream);
			/*
			 * 	新建一个sheet表
			 */
			Sheet sheet = workbook.getSheet(sheetName);
			for (WriteBack writeBack : writeBacks) {
				/*
				int rowNum = rowIdentifierRowIndexMap.get(writeBack.getCaseId());
				Row row = sheet.getRow(rowNum);
				int cellNum = cellNameCellIndexMap.get(writeBack.getCellName());
				Cell cell = row.getCell(cellNum, MissingCellPolicy.CREATE_NULL_AS_BLANK);
				cell.setCellType(CellType.STRING);
				cell.setCellValue(writeBack.getResult());*/
			}
			outputStream = new FileOutputStream(new File(path));
			workbook.write(outputStream);			
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			closeOutputStream(outputStream);
			closeInputStream(fileInputStream);
		}
	}

	private static void closeOutputStream(FileOutputStream outputStream) {
		try {
			outputStream.close();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
	}
	
	
	public static void main(String[] args) {
		writeBackData("src/test/resources/测试用例.xlsx","用例", "2", "Desc","zzzzzzzzzzzz");
		List<Case> load = load("src/test/resources/测试用例.xlsx","用例", Case.class);
		for (Case case1 : load) {
			System.out.println(case1.toString());
		}
	}
}