【JavaWeb】WEB下的excel批量导入功能

妖狐艹你老母 2022-06-17 20:47 246阅读 0赞

开发中使用Excel导出功能实现的整个流程。使用框架ssm。

control层 importExcel+parseDataItem：

**\[java\]**  [view plain][]   [copy][view plain]

1. @RequestMapping("importExcel.do")  
2.   **public** String importExcel(@RequestParam MultipartFile uploadfile,   
3.           HttpServletRequest request, HttpServletResponse response) \{  
4.       String fileName = uploadfile.getOriginalFilename();  
5.       **if** (StringUtils.isEmpty(fileName)) \{  
6.           System.out.println("未上传文件名为空");  
7.       \}  
8.       String tempPath = request.getSession().getServletContext().getRealPath("/")  
9.               + "upload";  
10.       File dir = **new** File(tempPath);      
11.       **if** (!tempPath.endsWith(File.separator)) \{  
12.           tempPath = tempPath + File.separator;  
13.       \}  
14.       **if** (!dir.exists()) \{  
15.           dir.mkdirs();  
16.       \}  
17.       System.out.println(tempPath);  
18.       //制作路径  
19.       String newFile = tempPath + fileName;  
20.       File file = **new** File(newFile);          
21.       **try** \{  
22.           FileCopyUtils.copy(uploadfile.getBytes(), file);  
23.           //导入excel中的内容  
24.           **this**.<span style="color:\#ff0000;">parseDataItem(newFile);  
25.           System.out.println("成功导入");  
26.       \} **catch** (IOException e) \{  
27.           e.printStackTrace();  
28.       \} **finally** \{  
29.           **try** \{  
30.               FileUtils.forceDelete(file);  
31.           \} **catch** (IOException e) \{  
32.               e.printStackTrace();  
33.               **return** "uploadfail";  
34.           \}  
35.       \}  
36.       **return** "uploadsuccess";  
37.   \}  
38.   
39.   **public** **final** **boolean** <span style="color:\#ff0000;">parseDataItem(String file)\{  
40.         
41.      **return** ysdwAnimalService.<span style="color:\#ff0000;">importExcel(file);  
42.   \}

ysdwAnimalService层 import 调用底层通用方法readExcel：

**\[java\]**  [view plain][]   [copy][view plain]

1. @Transactional  
2.     **public** **boolean** importExcel(String file) \{  
3.         **final** **int** rowStart = 2;  
4.         **final** **int** cellStrart = 0;        
5.         List<YsdwAnimal> list = **new** LinkedList<YsdwAnimal>();      
6.         YsdwAnimal ysdwAnimal = **null**;  
7.         **try** \{  
8.             List<Object> dataList = ReadExcleUtils.<span style="color:\#ff0000;">readExcel(file, **new** YsdwAnimalExcel(), rowStart, cellStrart);  
9.             **for** (Object object : dataList) \{  
10.                YsdwAnimalExcel excel = (YsdwAnimalExcel) object;  
11.                 ysdwAnimal = **new** YsdwAnimal();                 
12.                 String uuid = CommonUtils.getUuid();  
13.                 ysdwAnimal.setId(uuid);  
14.                 ysdwAnimal.setAnimalname(excel.getAnimalname());  
15.                 ysdwAnimal.setAnimalnumber(excel.getAnimalnumber());  
16.                 ysdwAnimal.setAnimaltypes(excel.getAnimaltypes());  
17.                 ysdwAnimal.setVegetationtypes(excel.getVegetationtypes());  
18.                 ysdwAnimal.setJingdu(excel.getJingdu());  
19.                 ysdwAnimal.setWeidu(excel.getWeidu());  
20.                 ysdwAnimal.setPodu(excel.getPodu());  
21.                 ysdwAnimal.setPowei(excel.getPowei());  
22.                 ysdwAnimal.setPoxiang(excel.getPoxiang());  
23.                 ysdwAnimal.setZuobiao(excel.getZuobiao());  
24.                 ysdwAnimal.setHaiba(excel.getHaiba());  
25.                 ysdwAnimal.setTianqi(excel.getTianqi());  
26.                 ysdwAnimal.setJilusj(excel.getJilusj());  
27.                 ysdwAnimal.setQita(excel.getQita());        
28.                 ysdwAnimal.setDid(excel.getDid());  
29.                 ysdwAnimal.setBaohdj(excel.getBaohdj());  
30.                 list.add(ysdwAnimal);                
31.             \}             
32.             //存库  
33.             **for** (YsdwAnimal YsdwAnimal : list) \{  
34.                 YsdwAnimalMapper.saveOrUpdate(YsdwAnimal);  
35.                 System.out.println("存库");  
36.             \}  
37.         \} **catch** (IOException e) \{  
38.             e.printStackTrace();  
39.             **return** **false**;  
40.         \}  
41.         **return** **true**;  
42.           
43.     \}

底层通用方法readExcel

**\[java\]**  [view plain][]   [copy][view plain]

1. /\*\* 
2.      \* 读取EXCLE2007的方法 
3.      \* @param filePath 文件名 
4.      \* @param obj 对象名 
5.      \* @param rowStart 起点行（从0开始） 
6.      \* @param cellStrart 起点列（从0开始） 
7.      \* @return 对象集合 
8.      \* @throws IOException 
9.      \*/  
10.     @SuppressWarnings("rawtypes")  
11.     **public** **static** List<Object> readExcel(**final** String filePath, **final** Object obj, **final** **int** rowStart, **final** **int** cellStrart) **throws** IOException \{  
12.         List<Object> aList = **null**;  
13.         **final** String extension = filePath.substring(filePath.lastIndexOf(".")+1, filePath.length());//截取文件扩展名  
14.         **final** Class c = obj.getClass();//获得类对象  
15.         <span style="color:\#cc0000;">**final** Field\[\] filds = c.getDeclaredFields();//获得该类中所有的属性  
16.         **final** File excelFile = **new** File(filePath);   
17.         **final** InputStream is = **new** FileInputStream(excelFile);// 获取文件输入流  
18.         **if** ("xlsx".equals(extension)) \{ //解析2007  
19.             aList = ReadExcleUtils.<span style="color:\#ff0000;">analyExcle2007(c, is, filds, rowStart, cellStrart);  
20.         \} **else** **if** ("xls".equals(extension)) \{  
21.             aList = ReadExcleUtils.<span style="color:\#ff0000;">analyExcle2003(c, is, filds, rowStart, cellStrart);  
22.         \} **else** \{  
23.             System.out.println("上传文件不正确");  
24.         \}  
25.         **return** aList;  
26.     \}  
27.       
28.     @SuppressWarnings("rawtypes")  
29.     **private** **static** List<Object> analyExcle2007(**final** Class c, **final** InputStream is, **final** Field\[\] filds, **final** **int** rowStart, **final** **int** cellStrart) **throws** IOException \{  
30.         Object objs = **null**;  
31.         Map<String,Object> mapAtt = **null**;  
32.         **final** List<Object> aList = **new** ArrayList<Object>();  
33.         **final** XSSFWorkbook workbook2007 = **new** XSSFWorkbook(is);// 创建Excel2007文件对象    
34.         **final** XSSFSheet sheet = workbook2007.getSheetAt(0);// 取出第一个工作表，索引是0  
35.         **final** XSSFFormulaEvaluator evaluator=**new** XSSFFormulaEvaluator(workbook2007);  
36.         **for** (**int** i = rowStart; i <= sheet.getLastRowNum(); i++) \{  
37.             String cellStr = **null**;// 单元格，最终按字符串处理  
38.             **final** XSSFRow row = sheet.getRow(i);// 获取行对象    
39.             **if** (row == **null**) \{ // 如果为空，不处理    
40.                 **continue**;    
41.             \}**else** \{  
42.                 mapAtt = **new** HashMap<String,Object>();  
43.                 **for** (**int** j = 0; j < filds.length; j++) \{  
44.                     **final** XSSFCell cell = row.getCell(j+cellStrart);  
45.                     //判断单元格的数据类型     
46.                     **if** (cell != **null**) \{  
47.                         //对时间的特殊处理  
48.                         **int** dataFormat = cell.getCellStyle().getDataFormat();  
49.                         **if** (dataFormat == 14 || dataFormat == 176 || dataFormat == 178 || dataFormat == 180 || dataFormat == 181 || dataFormat == 182) \{  
50.                             cellStr = ReadExcleUtils.getDateValue2007(cell);  
51.                         \} **else**\{  
52.                             **switch** (cell.getCellType()) \{  
53.                             **case** HSSFCell.CELL\_TYPE\_NUMERIC://数值  
54.                                 BigDecimal db = **new** BigDecimal(cell.getNumericCellValue());  
55.                                 **if** (db.toString().indexOf(".") != -1) \{  
56.                                     java.text.DecimalFormat dfomat = **new** java.text.DecimalFormat("0.000000");  
57.                                     cellStr = dfomat.format(db);  
58.                                 \}**else** \{  
59.                                     cellStr = db.toPlainString();  
60.                                 \}  
61. //                              cellStr = db.toPlainString();  
62. //                              cellStr = String.valueOf(cell.getNumericCellValue());  
63.                                 **break**;  
64.                             **case** HSSFCell.CELL\_TYPE\_STRING://字符串  
65.                                 cellStr = cell.getStringCellValue();  
66.                                 **break**;  
67.                             **case** HSSFCell.CELL\_TYPE\_BOOLEAN://布尔  
68.                                 cellStr = String.valueOf(cell.getBooleanCellValue());  
69.                                 **break**;  
70.                             **case** HSSFCell.CELL\_TYPE\_FORMULA://公式  
71.                                 cellStr = String.valueOf(evaluator.evaluate(cell).getNumberValue());  
72.                                 **break**;  
73.                             **case** HSSFCell.CELL\_TYPE\_BLANK://空值  
74.                                 cellStr =  "";  
75.                                 **break**;  
76.                             **default**:  
77.                                 cellStr = cell.getStringCellValue();  
78.                                 **break**;  
79.                             \}  
80.                         \}  
81.                     \} **else** \{  
82.                         cellStr = **null**;  
83.                     \}  
84.                     //讲单元格中的数据放入集合中  
85.                     mapAtt.put(filds\[j\].getName(), cellStr);  
86.                 \}  
87.             \}  
88.             **try** \{  
89.                 objs = c.newInstance();  
90.                 ReadExcleUtils.invokeMethod(c, objs, mapAtt);  
91.                 aList.add(objs);  
92.             \} **catch** (InstantiationException e) \{  
93.                 // TODO Auto-generated catch block  
94.                 e.printStackTrace();  
95.             \} **catch** (IllegalAccessException e) \{  
96.                 // TODO Auto-generated catch block  
97.                 e.printStackTrace();  
98.             \}  
99.         \}  
100.         **return** aList;  
101.     \}  
102.       
103.     @SuppressWarnings("rawtypes")  
104.     **private** **static** List<Object> analyExcle2003(**final** Class c, **final** InputStream is, **final** Field\[\] filds, **final** **int** rowStart, **final** **int** cellStrart) **throws** IOException \{  
105.         Object objs = **null**;  
106.         Map<String,Object> mapAtt = **null**;  
107.         **final** List<Object> aList = **new** ArrayList<Object>();  
108.         **final** HSSFWorkbook workbook2003 = **new** HSSFWorkbook(is);// 创建Excel2003文件对象   
109.         **final** HSSFSheet sheet = workbook2003.getSheetAt(0);// 取出第一个工作表，索引是0  
110.         **final** HSSFFormulaEvaluator evaluator = **new** HSSFFormulaEvaluator(workbook2003);  
111.         **for** (**int** i = rowStart; i <= sheet.getLastRowNum(); i++) \{  
112.             **final** HSSFRow row = sheet.getRow(i);// 获取行对象  
113.             **if** (row == **null**) \{ // 如果为空，不处理    
114.                 **continue**;    
115.             \}**else** \{  
116.                 mapAtt = **new** HashMap<String,Object>();  
117.                 **for** (**int** j = 0; j < filds.length; j++) \{  
118.                     String cellStr = **null**;// 单元格，最终按字符串处理  
119.                     **final** HSSFCell cell = row.getCell(j+cellStrart);  
120.                     //判断单元格的数据类型     
121.                     **if** (cell != **null**) \{  
122.                         //对时间的特殊处理  
123.                         **int** dataFormat = cell.getCellStyle().getDataFormat();  
124. //                      if (dataFormat == 14 || dataFormat == 178 || dataFormat == 180 || dataFormat == 181 || dataFormat == 182) \{   
125.                         **if** (dataFormat == 14 || dataFormat == 31 || dataFormat == 57 || dataFormat == 58) \{  
126.                             cellStr = ReadExcleUtils.getDateValue2003(cell);  
127.                         \} **else**\{  
128.                             **switch** (cell.getCellType()) \{  
129.                             **case** HSSFCell.CELL\_TYPE\_NUMERIC://数值  
130.                                 BigDecimal db = **new** BigDecimal(cell.getNumericCellValue());  
131.                                 **if** (db.toString().indexOf(".") != -1) \{  
132.                                     java.text.DecimalFormat dfomat = **new** java.text.DecimalFormat("0.000000");  
133.                                     cellStr = dfomat.format(db);  
134.                                 \}**else** \{  
135.                                     cellStr = db.toPlainString();  
136.                                 \}  
137.                                 **break**;  
138.                             **case** HSSFCell.CELL\_TYPE\_STRING://字符串  
139.                                 cellStr = cell.getStringCellValue();  
140.                                 **break**;  
141.                             **case** HSSFCell.CELL\_TYPE\_BOOLEAN://布尔  
142.                                 cellStr = String.valueOf(cell.getBooleanCellValue());  
143.                                 **break**;  
144.                             **case** HSSFCell.CELL\_TYPE\_FORMULA://公式  
145.                                 cellStr = String.valueOf(evaluator.evaluate(cell).getNumberValue());  
146.                                 **break**;  
147.                             **case** HSSFCell.CELL\_TYPE\_BLANK://空值  
148.                                 cellStr =  "";  
149.                                 **break**;  
150.                             **default**:  
151.                                 cellStr = cell.getStringCellValue();  
152.                                 **break**;  
153.                             \}  
154.                         \}  
155.                     \} **else** \{  
156.                         cellStr = **null**;  
157.                     \}  
158.                     //讲单元格中的数据放入集合中  
159.                     mapAtt.put(filds\[j\].getName(), cellStr);  
160.                 \}  
161.             \}  
162.             **try** \{  
163.                 objs = c.newInstance();  
164.                 ReadExcleUtils.invokeMethod(c, objs, mapAtt);  
165.                 aList.add(objs);  
166.             \} **catch** (InstantiationException e) \{  
167.                 // TODO Auto-generated catch block  
168.                 e.printStackTrace();  
169.             \} **catch** (IllegalAccessException e) \{  
170.                 // TODO Auto-generated catch block  
171.                 e.printStackTrace();  
172.             \}  
173.         \}  
174.         **return** aList;  
175.     \}

注意！！在底层方法种完成对excel表格字段顺序的控制，具体控制语句为：final Field\[\] filds = c.getDeclaredFields();//获得该类中所有的属性

这就是从底层开始导入excel的完整过程。

文章来源于：[http://blog.csdn.net/zh964822413/article/details/68953218][http_blog.csdn.net_zh964822413_article_details_68953218]

[view plain]: http://blog.csdn.net/zh964822413/article/details/68953218#
[http_blog.csdn.net_zh964822413_article_details_68953218]: http://blog.csdn.net/zh964822413/article/details/68953218