Java基于POI读取Excel工具类

为什么为封装此工具类？

由于公司供应链部门业务需要，对Excel处理这块有较为严苛的要求。为了提高开发效率，从实际项目出发封装了通用自定义读取Excel工具类。  

功能概述

1. 支持读取全部excel数据
2. 支持自定义读取（自定义读取行、列）数据
3.  支持读取以title为基准的数据（只读取title下的行）
4. 支持读取图片（返回图片的位置和流）
5.  支持设置sheet index读取
6.  支持读取函数值
7.  支持annation读取列表值

使用注意点

由于操作Excel文件一般较大，建议对JVM调优，调大堆内存空间，将大对象直接放在老年代，具体调优方式，网上资料很多。

核心代码

/**
 * 读取excel文件内容 
 * 仅支持xlsx格式
 * @author Vachel.Wang
 * @date 2016年6月16日 下午5:10:18  
 * @version V1.2
 */
public class ReadExcel {
	
	private XSSFWorkbook workbook = null;
	private XSSFSheet sheet;
    private XSSFRow row;
    private int sheetIndex = 0 ;
    private int rowIndex = 0 ;
    private int cellIndex = 0 ;
   
    
	public static ReadExcel me(InputStream s) throws IOException{
		return new ReadExcel(s);
	}
	
	public ReadExcel(InputStream s) throws IOException{
			workbook = new XSSFWorkbook(s);
			setSheetIndex(0);
	}
	
	public <T> List<T> readCoustomTemplet(Class<T> clazz) throws InstantiationException, IllegalAccessException {
	    List<T> list = new ArrayList<T>();
	    
	    XSSFRow titleRow = sheet.getRow(rowIndex-1);
	    ArrayList<String> rowArr = new ArrayList<String>();
	    int cells = titleRow.getPhysicalNumberOfCells();
	    for (int j = cellIndex; j < cells; j++) {
            rowArr.add(getStringCellValue(titleRow.getCell(j)));
        }
	    
	    List<Field> allFields = getMappedFiled(clazz, null);
        Map<String, Field> fieldsMap = new HashMap<String, Field>();//定义一个map用于存放列的序号和field.
        for (Field field : allFields) {
            if (field.isAnnotationPresent(ExcelField.class)) {
                ExcelField attr = field.getAnnotation(ExcelField.class);
                field.setAccessible(true);
                fieldsMap.put(attr.title(), field);
            }
        }
        
        //row = sheet.getRow(rowIndex);
        for (int i = rowIndex ; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            T entity = null;
            for (int j = cellIndex; j < cells; j++) {
                String c = getStringCellValue(row.getCell(j));
                entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建.
                Field field = fieldsMap.get(rowArr.get(j));// 从map中得到对应列的field.
               
                if (field==null) {
                    continue;
                }
                
                Class<?> fieldType = field.getType();
                if (String.class == fieldType) {
                    field.set(entity, String.valueOf(c));
                } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
                    field.set(entity, Integer.parseInt(c));
                } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
                    field.set(entity, Long.valueOf(c));
                } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
                    field.set(entity, Float.valueOf(c));
                } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
                    field.set(entity, Short.valueOf(c));
                } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
                    field.set(entity, Double.valueOf(c));
                } else if (Character.TYPE == fieldType) {
                    if ((c != null) && (c.length() > 0)) {
                        field.set(entity, Character.valueOf(c.charAt(0)));
                    }
                }
            }
            if (entity != null) {
                list.add(entity);
            }
        }
	    
	    return list;
    }
	
	private List<Field> getMappedFiled(Class clazz, List<Field> fields) {
        if (fields == null) {
            fields = new ArrayList<Field>();
        }

        Field[] allFields = clazz.getDeclaredFields();// 得到所有定义字段
        // 得到所有field并存放到一个list中.
        for (Field field : allFields) {
            if (field.isAnnotationPresent(ExcelField.class)) {
                fields.add(field);
            }
        }
        if (clazz.getSuperclass() != null && !clazz.getSuperclass().equals(Object.class)) {
            getMappedFiled(clazz.getSuperclass(), fields);
        }

        return fields;
    }
	
	/**
	 * 读取自定义读取方式数据
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public List<ArrayList<String>> readCoustom() throws FileNotFoundException, IOException{
		List<ArrayList<String>> excelData = new ArrayList<ArrayList<String>>();
		for (int i = rowIndex ; i < sheet.getPhysicalNumberOfRows(); i++) {
			ArrayList<String> rowArr = new ArrayList<String>();
			row = sheet.getRow(i);
			for (int j = cellIndex; j < row.getPhysicalNumberOfCells(); j++) {
				rowArr.add(getStringCellValue(row.getCell(j)));
			}
			excelData.add(rowArr);
		}
		return excelData;
	}
	/**
	 * 以title列为标准读取自定义数据
	 * @param titleIndex
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public List<ArrayList<String>> readCoustomByTitle(int titleIndex) throws FileNotFoundException, IOException{
		XSSFRow titleRow = sheet.getRow(titleIndex);
		List<ArrayList<String>> excelData = new ArrayList<ArrayList<String>>();
		for (int i = rowIndex ; i < sheet.getPhysicalNumberOfRows(); i++) {
			ArrayList<String> rowArr = new ArrayList<String>();
			row = sheet.getRow(i);
			for (int j = cellIndex; j < titleRow.getPhysicalNumberOfCells(); j++) {
				rowArr.add(getStringCellValue(row.getCell(j)));
			}
			excelData.add(rowArr);
		}
		return excelData;
	}
	
	/**
	 * 读取自定义读取非空数据
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public List<ArrayList<String>> readCoustomExcludeNull() throws FileNotFoundException, IOException{
		List<ArrayList<String>> excelData = new ArrayList<ArrayList<String>>();
		for (int i = rowIndex ; i < sheet.getPhysicalNumberOfRows(); i++) {
			ArrayList<String> rowArr = new ArrayList<String>();
			row = sheet.getRow(i);
			for (int j = cellIndex; j < row.getPhysicalNumberOfCells(); j++) {
				String cellValue = getStringCellValue(row.getCell(j)); 
				if(!cellValue.equals(""))
					rowArr.add(cellValue);
			}
			excelData.add(rowArr);
		}
		return excelData;
	}
	
	/**
	 * 读取所有
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public List<ArrayList<String>> readAll() throws FileNotFoundException, IOException{
		List<ArrayList<String>> excelData = new ArrayList<ArrayList<String>>();
		for (int i = 0 ; i < sheet.getPhysicalNumberOfRows(); i++) {
			ArrayList<String> rowArr = new ArrayList<String>();
			row = sheet.getRow(i);
			for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
				rowArr.add(getStringCellValue(row.getCell(j)));
			}
			excelData.add(rowArr);
		}
		return excelData;
	}
	
	/**
	 * 以title行为标准来读其它所有取列值
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public List<ArrayList<String>> readAllByTitle(int titleIndex) throws FileNotFoundException, IOException{
		XSSFRow titleRow = sheet.getRow(titleIndex);
		List<ArrayList<String>> excelData = new ArrayList<ArrayList<String>>();
		for (int i = 0 ; i < sheet.getPhysicalNumberOfRows(); i++) {
			ArrayList<String> rowArr = new ArrayList<String>();
			row = sheet.getRow(i);
			if(row==null)
				continue;
			for (int j = 0; j < titleRow.getPhysicalNumberOfCells(); j++) {
				rowArr.add(getStringCellValue(row.getCell(j)));
			}
			excelData.add(rowArr);
		}
		return excelData;
	}

	/**
	 * 读取title行下的所有数据
	 * @param titleIndex
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
     */
	public List<ArrayList<String>> readNextByTitle(int titleIndex) throws FileNotFoundException, IOException{
		XSSFRow titleRow = sheet.getRow(titleIndex);
		List<ArrayList<String>> excelData = new ArrayList<ArrayList<String>>();
		for (int i = titleIndex+1 ; i < sheet.getPhysicalNumberOfRows(); i++) {
			ArrayList<String> rowArr = new ArrayList<String>();
			row = sheet.getRow(i);
			if(row==null){
			    continue;
			}
			for (int j = 0; j < titleRow.getPhysicalNumberOfCells(); j++) {
				rowArr.add(getStringCellValue(row.getCell(j)));
			}
			excelData.add(rowArr);
		}
		return excelData;
	}
	
	/**
	 * 读取非空数据
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public List<ArrayList<String>> readAllExcludeNull() throws FileNotFoundException, IOException{
		List<ArrayList<String>> excelData = new ArrayList<ArrayList<String>>();
		for (int i = 0 ; i < sheet.getPhysicalNumberOfRows(); i++) {
			ArrayList<String> rowArr = new ArrayList<String>();
			row = sheet.getRow(i);
			for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
				String cellValue = getStringCellValue(row.getCell(j)); 
				if(!cellValue.equals(""))
					rowArr.add(cellValue);
			}
			excelData.add(rowArr);
		}
		return excelData;
	}
	
	/**
	 * 读取行的所有列的数据
	 * @param rowIndex
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public ArrayList<String> readRowData(int rowIndex) throws FileNotFoundException, IOException{
		ArrayList<String> rowDataArr = new ArrayList<String>();
		row = sheet.getRow(rowIndex);
		for (int i = 0 ; i < row.getPhysicalNumberOfCells(); i++) {
			String cellStr = getStringCellValue(row.getCell(i));
			rowDataArr.add(cellStr);
		}
		return rowDataArr;
	}
	
	/**
	 * 读取行的所有列的非空数据
	 * @param rowIndex
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public ArrayList<String> readRowExcludeNullData(int rowIndex) throws FileNotFoundException, IOException{
		ArrayList<String> rowDataArr = new ArrayList<String>();
		row = sheet.getRow(rowIndex);
		for (int i = 0 ; i < row.getPhysicalNumberOfCells(); i++) {
			String cellValue = getStringCellValue(row.getCell(i));
			if(!cellValue.equals(""))
				rowDataArr.add(cellValue);
		}
		return rowDataArr;
	}
	
	/**
	 * 读取读列的数据 
	 * 空为 ""
	 * @param rowIndex
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public String readCellData(int rowIndex , int cellIndex) throws FileNotFoundException, IOException{
		row = sheet.getRow(rowIndex);
		return getStringCellValue(row.getCell(cellIndex));
	}
	
	/**
	 * 获取所有图片 
	 * @return
	 */
	public List<XSSFPictureData> readPictures() {
		return workbook.getAllPictures();
	}
	
	/**
	 * 读取单元格内容，支持获取函数内容
	 * @param cell
	 * @return
	 */
	public static String getStringCellValue(Cell cell) {
        String strCell = "";
        if(cell==null) return strCell;
        switch (cell.getCellType()) {
	        case Cell.CELL_TYPE_STRING:	// get String data 
	        	strCell = cell.getRichStringCellValue().getString().trim();
	            break;
	        case Cell.CELL_TYPE_NUMERIC:	// get date or number data 
	            if (DateUtil.isCellDateFormatted(cell)) {
	                strCell = com.gjw.utils.DateUtil.formatYYYYMMDDHHMMSS(cell.getDateCellValue());
	            } else {
	            	BigDecimal numberic=new BigDecimal(cell.getNumericCellValue());
	            	strCell = numberic.toPlainString();
	            }
	            break;
	        case Cell.CELL_TYPE_BOOLEAN:	// get boolean data 
	        	strCell = String.valueOf(cell.getBooleanCellValue());
	            break;
	        case Cell.CELL_TYPE_FORMULA:	// get expression data 
	        	FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
	            evaluator.evaluateFormulaCell(cell);
	            CellValue cellValue = evaluator.evaluate(cell);
				if(StringUtil.isNotBlank(cellValue.getStringValue())){
					strCell = String.valueOf(cellValue.getStringValue()) ;
				}else{
					strCell = String.valueOf(cellValue.getNumberValue()) ;
				}
	            break;
	        default:
	        	strCell = "";
	    }
        return strCell;
    }
	
	/**
	 * 设置读取sheet下标，默认为0
	 * @param sheetIndex
	 * @return
	 */
	public ReadExcel setSheetIndex(int sheetIndex){
		this.sheetIndex = sheetIndex ;
		sheet = workbook.getSheetAt(sheetIndex);
		return this;
	}
	/**
	 * 设置读取sheet下标，默认为0
	 * @param rowIndex
	 * @return
	 */
	public ReadExcel setRowIndex(int rowIndex){
		this.rowIndex = rowIndex ;
		return this;
	}
    /**
	 * 设置读取cell下标，默认为0
	 * @param rowIndex
	 * @return
	 */
	public ReadExcel setCellIndex(int cellIndex){
		this.cellIndex = cellIndex ;
		return this;
	}
	/**
	 * 获取sheet总数
	 * @return
	 */
	public int getSheetCount(){
		return this.workbook.getNumberOfSheets();
	}
	/**
	 * 根据sheet下标获取sheet名称
	 * @param index
	 * @return
	 */
	public String getSheetNameByIndex(int index){
		return this.workbook.getSheetAt(index).getSheetName();
	}
	public String getCurrentSheetName(){
		return this.workbook.getSheetAt(sheetIndex).getSheetName();
	}
	/**
	 * 获取Excel2007图片
	 * @param sheetNum 当前sheet编号
	 * @param sheet 当前sheet对象
	 * @param workbook 工作簿对象
	 * @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
	 */
	public static Map<String, PictureData> getSheetPictrues(int sheetNum,
															  XSSFSheet sheet, XSSFWorkbook workbook) {
		Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();

		for (POIXMLDocumentPart dr : sheet.getRelations()) {
			if (dr instanceof XSSFDrawing) {
				XSSFDrawing drawing = (XSSFDrawing) dr;
				List<XSSFShape> shapes = drawing.getShapes();
				for (XSSFShape shape : shapes) {
					XSSFPicture pic = (XSSFPicture) shape;
					XSSFClientAnchor anchor = pic.getPreferredSize();
					CTMarker ctMarker = anchor.getFrom();
					String picIndex = String.valueOf(sheetNum) + "_"
							+ ctMarker.getRow() + "_" + ctMarker.getCol();
					sheetIndexPicMap.put(picIndex, pic.getPictureData());
				}
			}
		}

		return sheetIndexPicMap;
	}

	public static void printImg(List<Map<String, XSSFPictureData>> sheetList) throws IOException {

		for (Map<String, XSSFPictureData> map : sheetList) {
			Object key[] = map.keySet().toArray();
			for (int i = 0; i < map.size(); i++) {
				// 获取图片流
				XSSFPictureData pic = map.get(key[i]);
				// 获取图片索引
				String picName = key[i].toString();
				// 获取图片格式
				String ext = pic.suggestFileExtension();

				byte[] data = pic.getData();

				FileOutputStream out = new FileOutputStream("D:\\pic" + picName + "." + ext);
				out.write(data);
				out.close();
			}
		}

	}

	public XSSFWorkbook getWorkbook() {
		return workbook;
	}

	public XSSFSheet getSheet() {
		return sheet;
	}

	public int getSheetIndex() {
		return sheetIndex;
	}

	public static void main(String[] args) throws IOException {
		/*InputStream inputStream = new FileInputStream("d:\\success3.xlsx");
		try {
            List<ScmInvoiceImportLog> list = ReadExcel.me(inputStream).setSheetIndex(0).setRowIndex(1).readCoustomTemplet(ScmInvoiceImportLog.class);
            System.out.println(list);
        } catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }*/

		// list all demo
		/*List<ArrayList<String>> list = ReadExcel.me(inputStream).setSheetIndex(0).readAllByTitle(4);
		for(ArrayList<String> rowArray : list){
			for(String string : rowArray){
				System.out.print(string+"\t");
			}
			System.out.println();
		}*/
		// read picture demo
		/*List<XSSFPictureData> readPictures = ReadExcel.me(inputStream).readPictures();
		for(XSSFPictureData pictureData:readPictures){
			int type = pictureData.getPictureType();
			if(type == Workbook.PICTURE_TYPE_JPEG){
				File file = new File("d:\\"+System.currentTimeMillis()+".jpg");
				OutputStream outputStream = new FileOutputStream(file);
				outputStream.write(pictureData.getData());
				outputStream.flush();
				outputStream.close();
			}
		}*/
		/*ReadExcel readExcel = ReadExcel.me(inputStream).setSheetIndex(0);
		Map<String, PictureData> dataMap =  readExcel.getSheetPictrues(readExcel.getSheetIndex(),readExcel.getSheet(),readExcel.getWorkbook());
		for(Map.Entry<String,PictureData> entry : dataMap.entrySet()){
			System.out.println("key = "+entry.getKey()+" , value = "+entry.getValue());
		}*/
	}
}

@Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {

	/**
	 * 导出字段名（默认调用当前字段的“get”方法，如指定导出字段为对象，请填写“对象名.对象属性”，例：“area.name”、“office.name”）
	 */
	String value() default "";
	
	/**
	 * 导出字段标题（需要添加批注请用“**”分隔，标题**批注，仅对导出模板有效）
	 */
	String title();
	
	/**
	 * 字段类型（0：导出导入；1：仅导出；2：仅导入）
	 */
	int type() default 0;

	/**
	 * 导出字段对齐方式（0：自动；1：靠左；2：居中；3：靠右）
	 */
	int align() default 0;
	
	/**
	 * 导出字段字段排序（升序）
	 */
	int sort() default 0;

	/**
	 * 如果是字典类型，请设置字典的type值
	 */
	String dictType() default "";
	
	/**
	 * 反射类型
	 */
	Class<?> fieldType() default Class.class;
	
	/**
	 * 字段归属组（根据分组导出导入）
	 */
	int[] groups() default {};
}