java实现Excel的导入、导出
一、Excel的导入
导入可采用两种方式,一种是JXL,另一种是POI,但前者不能读取高版本的Excel(07以上),后者更具兼容性。由于对两种方式都进行了尝试,就都贴出来分享(若有错误,请给予指正)
方式一、JXL导入 所需jar包JXL.jar
publicstaticListreadExcelByJXL(StringfilePath){ List infoList=newArrayList (); Map >map=newHashMap >(); infoList.clear(); try{ InputStreamis=newFileInputStream(filePath); Workbookworkbook=Workbook.getWorkbook(is); //获取第1张表 Sheetsheet=workbook.getSheet(0); //获取总的列数 intcolumns=sheet.getColumns(); //获取总的行数 introws=sheet.getRows(); //先列后行(j,i) for(inti=1;i contentList=newArrayList (); contentList.clear(); for(intj=1;j >entry:map.entrySet()){ List list=entry.getValue(); PutStorageInfostorageInfo=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(Exceptione){ e.printStackTrace(); } returninfoList; }
方式二、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
publicstaticListreadExcelByPOI(StringfilePath){ List infoList=newArrayList (); Map >map=newHashMap >(); infoList.clear(); try{ InputStreamis=newFileInputStream(filePath); intindex=filePath.lastIndexOf("."); Stringpostfix=filePath.substring(index+1); Workbookworkbook=null; if("xls".equals(postfix)){ workbook=newHSSFWorkbook(is); }elseif("xlsx".equals(postfix)){ workbook=newXSSFWorkbook(is); } //获取第1张表 Sheetsheet=workbook.getSheetAt(0); //总的行数 introws=sheet.getLastRowNum(); //总的列数--->最后一列为null则有问题,读取不完整,将表头的数目作为总的列数,没有的则补为null intcolumns=sheet.getRow(0).getLastCellNum(); //先列后行 for(inti=1;i<=rows;i++){ Rowrow=sheet.getRow(i); if(null!=row&&row.getFirstCellNum()==-1){//这一行是空行,不读取 continue; } //这一行的总列数 //columns=row.getLastCellNum(); List contentList=newArrayList (); contentList.clear(); for(intj=1;j >entry:map.entrySet()){ List list=entry.getValue(); PutStorageInfostorageInfo=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(Exceptione){ e.printStackTrace(); } returninfoList; }
二、Excel导出
采用JXL实现
publicstaticvoidcreatExcel(ListstorageInfoList,StringfileName){ try{ OutputStreamos=newFileOutputStream(fileName); //创建可写的工作薄 WritableWorkbookworkbook=Workbook.createWorkbook(os); //创建第一张表 WritableSheetsheet=workbook.createSheet("Sheet1",0); //设置根据内容自动宽度 CellViewcellView=newCellView(); cellView.setAutosize(true); //在下边for循环中为每一列设置 //设置列宽度,此种方式参数的意思,i-->对应的行或列j-->要设置的宽度 //sheet.setColumnView(0,100); //sheet.setRowView(0,300); //设置字体加粗且背景颜色为黄色 WritableFontboldFont=newWritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//黑体 WritableCellFormatcellrFormate=newWritableCellFormat(boldFont); cellrFormate.setBackground(Colour.YELLOW); //先添加表头 List titleList=getTitleList(); //循环创建单元格,先列后行 for(inti=0;i storageInfoList){ String[][]content=newString[storageInfoList.size()][11]; for(inti=0;i getTitleList(){ List list=newArrayList (); list.add("ItemNo."); list.add("Productcode"); list.add("Sort"); list.add("Brand"); list.add("ProductName"); list.add("Quantity(Pieces)"); list.add("Content"); list.add("NetWeight"); list.add("Country"); list.add("Bestbeforedate"); list.add("Price(EURO)"); list.add("Remarks"); returnlist; }
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持毛票票。