POI excel 使用

1. maven

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.0.1</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.0.1</version>
</dependency>

2. 导入的util

 

 /** 
     * 判断Excel的版本,获取Workbook 
     * @param in 
     * @param filename 
     * @return 
     * @throws IOException 
     */  
    public static Workbook getWorkbok(InputStream in,File file) throws IOException{  
        Workbook wb = null;  
        if(file.getName().endsWith(EXCEL_XLS)){  //Excel 2003  
            wb = new HSSFWorkbook(in);  
        }else if(file.getName().endsWith(EXCEL_XLSX)){  // Excel 2007/2010  
            wb = new XSSFWorkbook(in);  
        }  
        return wb;  
    }  

 public static void main(String[] args) throws Exception {  
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");  
        try {  
            // 同时支持Excel 2003、2007  
            File excelFile = new File("d:/product.xlsx"); // 创建文件对象  
            FileInputStream in = new FileInputStream(excelFile); // 文件流  
            checkExcelVaild(excelFile);  
            Workbook workbook = getWorkbok(in,excelFile);  
            //Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel2003/2007/2010都是可以处理的  
  
            int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量  
            /** 
             * 设置当前excel中sheet的下标：0开始 
             */  
//            Sheet sheet = workbook.getSheetAt(0);   // 遍历第一个Sheet  
            Sheet sheet = workbook.getSheetAt(2);   // 遍历第三个Sheet  
            
            //获取总行数
//          System.out.println(sheet.getLastRowNum());
            
            // 为跳过第一行目录设置count  
            int count = 0;
            for (Row row : sheet) {
            	try {
            		// 跳过第一和第二行的目录  
                    if(count < 2 ) {
                        count++;  
                        continue;  
                    }
                    
                    //如果当前行没有数据，跳出循环  
                    if(row.getCell(0).toString().equals("")){  
                    	return;
                    }
                    
                    //获取总列数(空格的不计算)
                    int columnTotalNum = row.getPhysicalNumberOfCells();
                    System.out.println("总列数：" + columnTotalNum);
                    
                    System.out.println("最大列数：" + row.getLastCellNum());
                    
                    //for循环的，不扫描空格的列
//                    for (Cell cell : row) { 
//                    	System.out.println(cell);
//                    }
                    int end = row.getLastCellNum();
                    for (int i = 0; i < end; i++) {
                    	Cell cell = row.getCell(i);
                    	if(cell == null) {
                    		System.out.print("null" + "\t");
                    		continue;
                    	}
                    	
                    	Object obj = getValue(cell);
                    	System.out.print(obj + "\t");
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        }
    }
    
    private static Object getValue(Cell cell) {
    	Object obj = null;
    	switch (cell.getCellTypeEnum()) {
	        case BOOLEAN:
	            obj = cell.getBooleanCellValue(); 
	            break;
	        case ERROR:
	            obj = cell.getErrorCellValue(); 
	            break;
	        case NUMERIC:
	            obj = cell.getNumericCellValue(); 
	            break;
	        case STRING:
	            obj = cell.getStringCellValue(); 
	            break;
	        default:
	            break;
    	}
    	return obj;
    }
}

 

3. 合并单元格的excel 处理

https://blog.csdn.net/a919423654/article/details/68946507

 List<CellRangeAddress> cras = getCombineCell(sheet);
            //isMergedRegion(Sheet sheet,int row ,int column);判断是不是合并单元格\
            int count = sheet.getLastRowNum()+1;//总行数
           
            List<InspectionReport> irs = new ArrayList<>();
            for(int i = 1; i < count;i++){
            	rowIndex = i;
            	Row row = sheet.getRow(i);
            	InspectionReport ir = new InspectionReport();
            	
            	ir.setReportName(getCellValue(row.getCell(0)));
            	ir.setShift(Double.valueOf(getCellValue(row.getCell(1))).intValue());
            	ir.setLine(getCellValue(row.getCell(2)));
            	ir.setStationCode(getCellValue(row.getCell(3)));
            	ir.setArea(Double.valueOf(getCellValue(row.getCell(4))).intValue());
            	ir.setReportStatus(Double.valueOf(getCellValue(row.getCell(5))).intValue());
            	
            	List<InspectionItem> items = new ArrayList<>();
            	if(isMergedRegion(sheet,i,0)){
            		int lastRow = getRowNum(cras,sheet.getRow(i).getCell(0),sheet);
        			
        			for(;i<=lastRow;i++){
        				row = sheet.getRow(i);
        				InspectionItem item = new InspectionItem();
        				item.setItem(getCellValue(row.getCell(6)));
        				item.setMethod(getCellValue(row.getCell(7)));
        				item.setMode(getCellValue(row.getCell(8)));
        				item.setStandardValue(getCellValue(row.getCell(9)));
        				item.setDeviationValue(getCellValue(row.getCell(10)));
        				String pinci = getCellValue(row.getCell(11));
        				Double d = Double.valueOf(pinci);
        				item.setFrequency(d.intValue());
        				items.add(item);
        			}
        			i--;
            	}else{
        			row = sheet.getRow(i);
    				InspectionItem item = new InspectionItem();
    				item.setItem(getCellValue(row.getCell(6)));
    				item.setMethod(getCellValue(row.getCell(7)));
    				item.setMode(getCellValue(row.getCell(8)));
    				item.setStandardValue(getCellValue(row.getCell(9)));
    				item.setDeviationValue(getCellValue(row.getCell(10)));
    				String pinci = getCellValue(row.getCell(11));
    				Double d = Double.valueOf(pinci);
    				item.setFrequency(d.intValue());
    				items.add(item);
            	}
            	ir.setItems(items);
            	irs.add(ir);
            	
            }



public String getCellValue(Cell cell){    
	    if(cell == null) return "";    
	    if(cell.getCellType() == Cell.CELL_TYPE_STRING){    
	        return cell.getStringCellValue();    
	    }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){    
	        return String.valueOf(cell.getBooleanCellValue());    
	    }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){    
	        return cell.getCellFormula() ;    
	    }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){    
	        return String.valueOf(cell.getNumericCellValue());    
	    }
	    return "";    
	}
/** 
    * 合并单元格处理,获取合并行 
    * @param sheet 
    * @return List<CellRangeAddress> 
	*/  
    public List<CellRangeAddress> getCombineCell(Sheet sheet)  
    {  
        List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();  
        //获得一个 sheet 中合并单元格的数量  
        int sheetmergerCount = sheet.getNumMergedRegions();  
        //遍历所有的合并单元格  
        for(int i = 0; i<sheetmergerCount;i++)   
        {  
            //获得合并单元格保存进list中  
            CellRangeAddress ca = sheet.getMergedRegion(i);  
            list.add(ca);  
        }  
        return list;  
    }
    
    private int getRowNum(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet){
    	int xr = 0;
    	int firstC = 0;  
        int lastC = 0;  
        int firstR = 0;  
        int lastR = 0;  
    	for(CellRangeAddress ca:listCombineCell)  
        {
            //获得合并单元格的起始行, 结束行, 起始列, 结束列  
            firstC = ca.getFirstColumn();  
            lastC = ca.getLastColumn();  
            firstR = ca.getFirstRow();  
            lastR = ca.getLastRow();  
            if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)   
            {  
                if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)   
                {  
                	xr = lastR;
                } 
            }  
            
        }
    	return xr;
    	
    }
    /** 
     * 判断单元格是否为合并单元格，是的话则将单元格的值返回 
     * @param listCombineCell 存放合并单元格的list 
     * @param cell 需要判断的单元格 
     * @param sheet sheet 
     * @return 
     */ 
     public String isCombineCell(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet)
     throws Exception{ 
         int firstC = 0;  
         int lastC = 0;  
         int firstR = 0;  
         int lastR = 0;  
         String cellValue = null;  
         for(CellRangeAddress ca:listCombineCell)  
         {
             //获得合并单元格的起始行, 结束行, 起始列, 结束列  
             firstC = ca.getFirstColumn();  
             lastC = ca.getLastColumn();  
             firstR = ca.getFirstRow();  
             lastR = ca.getLastRow();  
             if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)   
             {  
                 if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)   
                 {  
                     Row fRow = sheet.getRow(firstR);  
                     Cell fCell = fRow.getCell(firstC);  
                     cellValue = getCellValue(fCell);  
                     break;  
                 } 
             }  
             else  
             {  
                 cellValue = "";  
             }  
         }  
         return cellValue;  
     }
	
	/**   
	* 获取合并单元格的值   
	* @param sheet   
	* @param row   
	* @param column   
	* @return   
	*/    
	public String getMergedRegionValue(Sheet sheet ,int row , int column){    
	    int sheetMergeCount = sheet.getNumMergedRegions();    
	        
	    for(int i = 0 ; i < sheetMergeCount ; i++){    
	        CellRangeAddress ca = sheet.getMergedRegion(i);    
	        int firstColumn = ca.getFirstColumn();    
	        int lastColumn = ca.getLastColumn();    
	        int firstRow = ca.getFirstRow();    
	        int lastRow = ca.getLastRow();    
	            
	        if(row >= firstRow && row <= lastRow){    
	            if(column >= firstColumn && column <= lastColumn){    
	                Row fRow = sheet.getRow(firstRow);    
	                Cell fCell = fRow.getCell(firstColumn);    
	                return getCellValue(fCell) ;    
	            }    
	        }    
	    }    
	        
	    return null ;    
	}
	
	
	/**  
	* 判断指定的单元格是否是合并单元格  
	* @param sheet   
	* @param row 行下标  
	* @param column 列下标  
	* @return  
	*/  
	private boolean isMergedRegion(Sheet sheet,int row ,int column) {  
	  int sheetMergeCount = sheet.getNumMergedRegions();  
	  for (int i = 0; i < sheetMergeCount; i++) {  
		CellRangeAddress range = sheet.getMergedRegion(i);  
		int firstColumn = range.getFirstColumn();  
		int lastColumn = range.getLastColumn();  
		int firstRow = range.getFirstRow();  
		int lastRow = range.getLastRow();  
		if(row >= firstRow && row <= lastRow){  
			if(column >= firstColumn && column <= lastColumn){  
				return true;  
			}  
		}
	  }  
	  return false;  
	}

4. 将读取的数据存入Java对象中 反射方式

https://blog.csdn.net/Revivedsun/article/details/54882870

接着遍历各个列，由于对象中字段的顺序与Excel列顺序保持一致，因此使用同样的列索引即可获取Excel列单元与对象成员。

 private final static Map<Class<?>,TypeHandler> typeHandlerMap = new HashMap<>();

    static {
        typeHandlerMap.put(Integer.class, new IntegerHandler());
        typeHandlerMap.put(Double.class, new DoubleHandler());
        typeHandlerMap.put(String.class, new StringHandler());
    }

    public static <T> List<T> importExcel(File excelFile,Class<T> type) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
        XSSFWorkbook workBook = new XSSFWorkbook(excelFile);
        List<T> result = new ArrayList<T>();
        XSSFSheet sheet = workBook.getSheetAt(0);

        for(int ri = sheet.getFirstRowNum() + 1 ; ri <= sheet.getLastRowNum(); ri++) {
            Row row = sheet.getRow(ri);
            T object = type.newInstance();
            Field[] fields = object.getClass().getDeclaredFields();
            Field.setAccessible(fields, true);
            for(int ci = row.getFirstCellNum(); ci < row.getLastCellNum(); ci++) {
                Cell cell = row.getCell(ci);
                Object value = getCellValue(cell,fields[ci]);
                fields[ci].set(object, value);
            }
            result.add(object);
            Field.setAccessible(fields, false);
        }
        return result;
    }


    private static Object getCellValue(Cell cell,Field field) 
    {
        TypeHandler handler = typeHandlerMap.get(field.getType());
        if(handler == null) {
            return null;
        }
        return handler.handle(cell, field);
    }

public interface TypeHandler {
    public Object handle(Cell cell,Field field);
}

public class IntegerHandler implements TypeHandler{

    @Override
    public Object handle(Cell cell, Field field) {
        int type = cell.getCellType();

        if(type == Cell.CELL_TYPE_NUMERIC) {
            return (int)cell.getNumericCellValue();
        }
        else if(type == Cell.CELL_TYPE_STRING) {
            try {
                return Integer.valueOf(cell.getStringCellValue());
            }catch(NumberFormatException e) {
                return null;
            }
        } 
        else {
            return null;
        }
    }
}

public class DoubleHandler implements TypeHandler{

    @Override
    public Object handle(Cell cell, Field field) {
        int type = cell.getCellType();
        if(type == Cell.CELL_TYPE_NUMERIC) {
            return cell.getNumericCellValue();
        }
        else if(type == Cell.CELL_TYPE_STRING) {
            try {
                return Double.valueOf(cell.getNumericCellValue());
            }catch(NumberFormatException e) {
                return null;
            }
        } 
        else {
            return null;
        }
    }

}


public class StringHandler implements TypeHandler{

    @Override
    public Object handle(Cell cell, Field field) {

        int type = cell.getCellType();

        if(type != Cell.CELL_TYPE_STRING) {
            return "";
        } 
        else {
            return cell.getStringCellValue();
        }
    }

}