导入可采用两种方式，一种是JXL，另一种是POI，但前者不能读取高版本的Excel(07以上)，后者更具兼容性。由于对两种方式都进行了尝试，就都贴出来分享(若有错误，请给予指正)
方式一、JXL导入 所需jar包 JXL.jar
java" id="highlighter_765413">
|
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集合，封装成beanfor(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则有问题，读取不完整，将表头的数目作为总的列数，没有的则补为nullint 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集合，封装成beanfor(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;} |
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
