java实现Excel的导入、导出

一、Excel的导入

导入可采用两种方式，一种是JXL，另一种是POI，但前者不能读取高版本的Excel(07以上)，后者更具兼容性。由于对两种方式都进行了尝试，就都贴出来分享(若有错误，请给予指正)

方式一、JXL导入  所需jar包 JXL.jar

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

publicstaticList<PutStorageInfo> readExcelByJXL(String filePath){ List<PutStorageInfo> infoList =newArrayList<PutStorageInfo>(); Map<String,List<String>> map =newHashMap<String,List<String>>();   infoList.clear(); try { InputStream is =newFileInputStream(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<String> contentList =newArrayList<String>();   contentList.clear(); for ( int j = 1 ; j < columns; j++){   contentList.add(sheet.getCell(j,i).getContents()); }   map.put( "StorageInfo" +i, contentList); }   //遍历map集合，封装成bean for (Map.Entry<String,List<String>> entry : map.entrySet()){ List<String> list = entry.getValue(); PutStorageInfo storageInfo =newPutStorageInfo();   storageInfo.setProductcode(list.get( 0 ));   storageInfo.setProductsort(list.get( 1 ));   storageInfo.setProductbrand(list.get( 2 ));   storageInfo.setProductname(list.get( 3 ));   storageInfo.setProductquantity(list.get( 4 ));   storageInfo.setProductcontent(list.get( 5 ));   storageInfo.setProductnetweight(list.get( 6 ));   storageInfo.setProductcountry(list.get( 7 ));   storageInfo.setProductpdate(list.get( 8 ));   storageInfo.setProductprice(list.get( 9 ));   storageInfo.setProductmark(list.get( 10 ));     infoList.add(storageInfo); }   is.close(); } catch (Exception e){   e.printStackTrace(); } return infoList; }

方式二、POI导入 

所需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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68

publicstaticList<PutStorageInfo> readExcelByPOI(String filePath){ List<PutStorageInfo> infoList =newArrayList<PutStorageInfo>(); Map<String,List<String>> map =newHashMap<String,List<String>>();   infoList.clear(); try { InputStream is =newFileInputStream(filePath);   int index = filePath.lastIndexOf( "." ); String postfix = filePath.substring(index+ 1 );   Workbook workbook = null ; if ( "xls" .equals(postfix)){   workbook =newHSSFWorkbook(is); }elseif( "xlsx" .equals(postfix)){   workbook =newXSSFWorkbook(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<String> contentList =newArrayList<String>();   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( "StorageInfo" +i, contentList); }   //遍历map集合，封装成bean for (Map.Entry<String,List<String>> entry : map.entrySet()){ List<String> list = entry.getValue(); PutStorageInfo storageInfo =newPutStorageInfo();   storageInfo.setProductcode(list.get( 0 ));   storageInfo.setProductsort(list.get( 1 ));   storageInfo.setProductbrand(list.get( 2 ));   storageInfo.setProductname(list.get( 3 ));   storageInfo.setProductquantity(list.get( 4 ));   storageInfo.setProductcontent(list.get( 5 ));   storageInfo.setProductnetweight(list.get( 6 ));   storageInfo.setProductcountry(list.get( 7 ));   storageInfo.setProductpdate(list.get( 8 ));   storageInfo.setProductprice(list.get( 9 ));   storageInfo.setProductmark(list.get( 10 ));     infoList.add(storageInfo); }   is.close(); } catch (Exception e){   e.printStackTrace(); }   return infoList; }

二、Excel导出

采用JXL实现

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

publicstaticvoid creatExcel(List<PutStorageInfo> storageInfoList,String fileName){ try { OutputStream os =newFileOutputStream(fileName); //创建可写的工作薄 WritableWorkbook workbook =Workbook.createWorkbook(os); //创建第一张表 WritableSheet sheet = workbook.createSheet( "Sheet1" , 0 ); //设置根据内容自动宽度 CellView cellView =newCellView();   cellView.setAutosize( true ); //在下边for循环中为每一列设置   //设置列宽度,此种方式参数的意思，i-->对应的行或列 j-->要设置的宽度 // sheet.setColumnView(0, 100); // sheet.setRowView(0, 300); //设置字体加粗且背景颜色为黄色 WritableFont boldFont =newWritableFont(WritableFont.ARIAL, 10 ,WritableFont.BOLD); //黑体 WritableCellFormat cellrFormate =newWritableCellFormat(boldFont);   cellrFormate.setBackground(Colour.YELLOW); //先添加表头 List<String> titleList = getTitleList(); //循环创建单元格,先列后行 for ( int i = 0 ; i < titleList.size(); i++){ //sheet.setColumnView(i, cellView);   sheet.setColumnView(i, 20 );   Label label =newLabel(i, 0 , titleList.get(i), cellrFormate);   sheet.addCell(label); }   LogUtil.logOut(JXLWriteExcel. class ,storageInfoList.size()+ "" );   String[][] content = convertToArr(storageInfoList); //设置content的自适应当前列的宽度，文本太对会自动换行 new Label(j, i+1, content[i][j-1],contentFormat); WritableCellFormat contentFormat =newWritableCellFormat();   contentFormat.setWrap( true );   //然后添加入库信息条目 for ( int i = 0 ; i < storageInfoList.size(); i++){ Label labelID =newLabel( 0 ,i+ 1 ,(i+ 1 )+ "" );   sheet.addCell(labelID);   for ( int j = 1 ; j < titleList.size(); j++){ Label label =newLabel(j, i+ 1 , content[i][j- 1 ]);   sheet.addCell(label); } }   //把创建的内容写入到输出流中，并关闭输出流 workbook.write();   workbook.close();   os.close();   //将存储了入库bean的list清空 storageInfoList.clear();   } catch (Exception e){   e.printStackTrace(); } }   privatestaticString[][] convertToArr(List<PutStorageInfo> storageInfoList){ String[][] content =newString[storageInfoList.size()][ 11 ]; for ( int i = 0 ; i < storageInfoList.size(); i++){ PutStorageInfo info = storageInfoList.get(i); //每个bean中总项有11项 content[i][ 0 ]= info.getProductcode();   content[i][ 1 ]= info.getProductsort();   content[i][ 2 ]= info.getProductbrand();   content[i][ 3 ]= info.getProductname();   content[i][ 4 ]= info.getProductquantity();   content[i][ 5 ]= info.getProductcontent();   content[i][ 6 ]= info.getProductnetweight();   content[i][ 7 ]= info.getProductcountry();   content[i][ 8 ]= info.getProductpdate();   content[i][ 9 ]= info.getProductprice();   content[i][ 10 ]= info.getProductmark(); } return content;   }   privatestaticList<String> getTitleList(){ List<String> list =newArrayList<String>();   list.add( "Item No." );   list.add( "Product code" );   list.add( "Sort" );   list.add( "Brand" );   list.add( "Product Name" );   list.add( "Quantity(Pieces)" );   list.add( "Content" ); list.add( "Net Weight" );   list.add( "Country" );   list.add( "Best before date" );   list.add( "Price(EURO)" );   list.add( "Remarks" );   return list; }

转自：http://www.jb51.net/article/117205.htm