Java实现Excel导入两种方式源代码分享

Java开发中经常会遇到需要导出Excel数据表或根据Excel中数据生成对应的数据库表,为大家讲述两种Excel导入方式,分别是利用JXL和POI技术实现Excel导入,注意的是JXL不能读取高版本的Excel(07版本以上),而POI兼容性相对不错。

1. Java实现Excel导入

方式一:JXL导入Excel

JXL是用java完成的一个项目,使用jxl.jar可以方便的来操做excel(对于excel2010并不支持)

jxl官方下载地址:http://sourceforge.net/projects/jxl/

jxl官方文档:http://jxl.sourceforge.net/javadoc/index.html

public static List readExcelByJXL(String filePath){
    List infoList =new ArrayList();
    Map<String,List> map =new HashMap<String,List>();
     infoList.clear();
    try{
    InputStream is =new FileInputStream(filePath);
    Workbook workbook =Workbook.getWorkbook(is);
    //获取第1张表
    Sheet sheet = workbook.getSheet(0);
    //获取总的列数
    int columns = sheet.getColumns();
    //获取总的行数
    int rows = sheet.getRows();
    //先列后行(j,i)
    for(int i =1; i < rows; i++){
    List contentList =new ArrayList();
     contentList.clear();
    for(int j =1; j < columns; j++){
     contentList.add(sheet.getCell(j,i).getContents());
    }
     map.put("info"+i, contentList);
    }
     
    //遍历map集合,封装成bean
    for(Map.Entry<String,List> entry : map.entrySet()){
    List list = entry.getValue();
    ProductInfo info =new ProductInfo();
      info.setProductcode(list.get(0));
      info.setProductsort(list.get(1));
      info.setProductbrand(list.get(2));
      info.setProductname(list.get(3));
      info.setProductquantity(list.get(4));
      info.setProductcontent(list.get(5));
      info.setProductnetweight(list.get(6));
      info.setProductcountry(list.get(7));
      info.setProductpdate(list.get(8));
      info.setProductprice(list.get(9));
      info.setProductmark(list.get(10));
      infoList.add(info);
    }
    is.close();
    }catch(Exception e){
      e.printStackTrace();
    }
  return infoList;
}

方式二:POI导入Excel

所需jar包,包含如下:

poi-3.6-20091214.jar

poi-ooxml-3.6-20091214.jar

poi-ooxml-schemas-3.6-20091214.jar

xmlbeans-2.3.0.jar

dom4j-1.6.1.jar

jdom-2.0.6.jar

public static List readExcelByPOI(String filePath){
    List infoList =new ArrayList();
    Map<String,List> map =new HashMap<String,List>();
     infoList.clear();
    try{
    InputStream is = new FileInputStream(filePath);
     
    int index = filePath.lastIndexOf(".");
    String postfix = filePath.substring(index+1);
     
    Workbook workbook =null;
    if("xls".equals(postfix)){
     workbook =new HSSFWorkbook(is);
    }elseif("xlsx".equals(postfix)){
     workbook =new XSSFWorkbook(is);
    }
    //获取第1张表
    Sheet sheet = workbook.getSheetAt(0);
    //总的行数
    int rows = sheet.getLastRowNum();
    //总的列数--->最后一列为null则有问题,读取不完整,将表头的数目作为总的列数,没有的则补为null
    int columns = sheet.getRow(0).getLastCellNum();
    //先列后行
    for(int i =1; i <= rows; i++){
      Row row = sheet.getRow(i);
     if(null!= row && row.getFirstCellNum()==-1){//这一行是空行,不读取
     continue;
    }
    //这一行的总列数
    // columns = row.getLastCellNum();
    List contentList =new ArrayList();
     contentList.clear();
    for(int j =1; j < columns; j++){
    if(row.getCell(j)!=null){
     row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
     contentList.add(row.getCell(j).getStringCellValue());
    }else{
     contentList.add("");
    }
    }
     map.put("info"+i, contentList);
    }
     
    //遍历map集合,封装成bean
    for(Map.Entry<String,List> entry : map.entrySet()){
    List list = entry.getValue();
    ProductInfo info =new ProductInfo();
      info.setProductcode(list.get(0));
      info.setProductsort(list.get(1));
      info.setProductbrand(list.get(2));
      info.setProductname(list.get(3));
      info.setProductquantity(list.get(4));
      info.setProductcontent(list.get(5));
      info.setProductnetweight(list.get(6));
      info.setProductcountry(list.get(7));
      info.setProductpdate(list.get(8));
      info.setProductprice(list.get(9));
      info.setProductmark(list.get(10));
      infoList.add(info);
    }
    is.close();
    }catch(Exception e){
      e.printStackTrace();
    }
    return infoList;
}
点赞

发表评论

电子邮件地址不会被公开。必填项已用 * 标注