Nemo

Nemo 关注TA

路漫漫其修远兮,吾将上下而求索。

Nemo

Nemo

关注TA

路漫漫其修远兮,吾将上下而求索。

  •  普罗旺斯
  • 负责帅就完事了
  • 写了1,493,291字

该文章投稿至Nemo社区   Java  板块 复制链接


ExcelUtil

发布于 2016/06/16 17:17 1,768浏览 0回复 5,492

private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
 //成功 
public static final Integer STATUS_OK = Integer.valueOf(1)
//失败 
public static final Integer STATUS_NO = Integer.valueOf(0)
/**  * 私有化构造器  */ 
private ExcelUtils(){
} 
/**  * 获取excel文件中的数据对象  *  
     * @param is excel  
     * @param excelColumnNames excel中每个字段的英文名(应该与pojo对象的字段名一致,顺序与excel一致)  
     * @return excel每行是list一条记录,map是对应的"字段名-->"  
     * @throws Exception  
*/ 
public static List<Map<String, Object>> getImportData(InputStream is, List<String> excelColumnNames) throws Exception { 
    logger.debug("InputStream:{}", is);  
    if (is == null) { return Collections.emptyList();  }
    Workbook workbook = null;  
    try 
        //拿到excel  
        workbook = Workbook.getWorkbook(is);  
        } catch (BiffException e) { 
            logger.error(e.getMessage(), e);  
            return Collections.EMPTY_LIST;  
        } catch (IOException e) { 
            logger.error(e.getMessage(), e)
            return Collections.EMPTY_LIST;  
        } 
        logger.debug("workbook:{}", workbook);  
        if (workbook == null) { return Collections.emptyList();  
        //第一个sheet  
        Sheet sheet = workbook.getSheet(0);  
        //行数  
        int rowCounts = sheet.getRows() - 1;  
        logger.debug("rowCounts:{}", rowCounts);  
        List<Map<String, Object>> list = new ArrayList<>(rowCounts - 1);  
        //双重for循环取出数据  
        for(int i = 1; i < rowCounts; i++){
            Map<String, Object> params = new HashedMap();  
            //i,j i: j: 
            for(int j = 0; j < excelColumnNames.size(); j++){
                Cell cell = sheet.getCell(j, i);  
                params.put(excelColumnNames.get(j), cell.getContents());  
            }
            list.add(params);  
            } return list
}
 /**  
   * 获取导入数据为对象的List  
   *  
   * @param data  
   * @param clazz  
   * @param excelColumnNames  
   * @param checkExcel  
   * @param <T>  
   * @return  
   * @throws Exception  
   */ 
public static <T> List<T> makeData(List<Map<String, String>> data, Class<T> clazz, List<String> excelColumnNames, CheckExcel checkExcel) throws Exception { 
    if(data == null || data.isEmpty() || clazz == null || checkExcel == null) { 
        return Collections.EMPTY_LIST;  
    }
    List<T> result = new ArrayList<T>(data.size());  
    for(Map<String, String> d : data) { 
        if(checkExcel != null && !checkExcel.check(d)) { continue;  
        T entity = clazz.newInstance();  
        for(String column : excelColumnNames) {
            BeanUtils.setProperty(entity, column, d.get(column));  
        }
        result.add(entity);  
    } 
    return result
}


使用:
List<String> ls = new ArrayList<String>()
ls.add("pnumber")
ls.add("hwd")
ls.add("loss")
ls.add("centerM")
ls.add("pierNo")
ls.add("startM")
ls.add("endM")
ls.add("capHeight")
ls.add("cat")
InputStream is = new FileInputStream("/home/nemo/VirtualBox VMs/common/CT.xls")
List<Map<String,Object>> res = getImportData(is,ls);


                    
点赞(0)
点了个评