poi excel

使用apache的poi包可以对excel进行操作读取和写入。

因excel分为xls的2003版和xlsx的2007版，poi在创建workbook时使用不同的类创建，因此需要注意区分xls。

Workbook workbook = null;
String fileExtension=FilenameUtils.getExtension(file.getOriginalFilename());
if(".xls".equals(fileExtension)){
    workbook  = new HSSFWorkbook(file.getInputStream()); //2003 xls
}else{
     workbook  = new XSSFWorkbook(file.getInputStream());  //2007 xlsx
}

※注意如果引入poi后找不到XSSFWorkbook,则可能没有引入poi-ooxml.jar

Sheet sheetWorkInfo = workbook.getSheet([sheetname]);

以下为读取excel内容装入到list<bean>中的实例:

/**
 * ExcelUtils 读取信息
 * @author DennyZhao
 *
 */
public class ExcelUtils {
    
    /**
     * 获取workbook
     * @param file
     * @return workbook
     * @throws IOException 
     * @throws FileNotFoundException 
     */
    public static Workbook getWorkBook(String filepath) throws FileNotFoundException, IOException {
        Workbook workbook = null;
        File file = new File(filepath);
        /**
         * 文件是否存在，是否为可用文件
         */
        if(!file.exists() || file.isDirectory()) {
            System.out.println("file is not exists or is a directory...");
            return null;
        }
        /**
         * 文件是否可读
         */
        if(!file.canRead()) {
            System.out.println("the file can not be readed, please confirm if you have the authority to read it.");
            return null;
        }
        /**
         * 是否为excel文件
         */
        if(!FilenameUtils.getExtension(filepath).contains("xls")) {
            System.out.println("i'm so sorry..we just support  the file of type which is excel..");
            return null;
        }
        
        String fileExtension=FilenameUtils.getExtension(file.getName());
        if("xls".equals(fileExtension)){
            workbook  = new HSSFWorkbook(new FileInputStream(file)); //2003 xls
        }else{
             workbook  = new XSSFWorkbook(new FileInputStream(file));  //2007 xlsx
        }
        return workbook;
    }
    
    /**
     * 读取数据返回对象一览
     * @param filepath
     * @param sheetName
     * @return
     * @throws IOException 
     * @throws FileNotFoundException 
     * @throws InvocationTargetException 
     * @throws IllegalAccessException 
     * @throws InstantiationException 
     */
    public static <T> List<T> getListObj(String filepath, String sheetName, Class<T> class1) throws FileNotFoundException, IOException, IllegalAccessException, InvocationTargetException, InstantiationException{
        Workbook work = getWorkBook(filepath);
        Sheet sheet = work.getSheet(sheetName);
        int rowCount = sheet.getPhysicalNumberOfRows();
        int colCount = sheet.getRow(0).getPhysicalNumberOfCells();
        List<T> listResult = new ArrayList<T>();
        
        for(int i=1;i < rowCount; i++) {
            Row row = sheet.getRow(i);
            Map<String, Object> map = new HashMap<String, Object>();
            // 判断是否已经读取完毕，第一格不能为空
            if(row.getCell(0)== null) {
                break;
            }
            
            for(int j=0; j < colCount; j++) {
                Cell cell = row.getCell(j);
                String key = sheet.getRow(0).getCell(j).getStringCellValue();
                
                Object cellValue = getCellValue(cell);
                map.put(key, cellValue);
            }
            T t = class1.newInstance();
            BeanUtils.copyProperties(t, map);
            listResult.add(t);
        }
        work.close();
        return listResult;
    }

    /**
     * 获取cellValue
     * @param cell
     */
    private static Object getCellValue(Cell cell) {
        if(cell == null) {
            return "";
        }
        CellType cellType = cell.getCellTypeEnum();
        Object obj = null;
        switch(cellType) {
        case NUMERIC:
            obj = cell.getNumericCellValue();
            break;
        default:
            obj = cell.getStringCellValue(); 
        }
        return obj;
    }