Spring MVC环境下用poi技术实现Excel的导入导出

Spring MVC环境下用poi技术实现Excel的导入导出

• 博客分类： 
• Java EE

excel导入导出 poi Spring MVC 

在web项目中，对数据的导入导出是非常实用和常见的，而excel文件则是十分常见的格式。

Excel导入——解析已存在的excel文件，并把里面的数据一一对应，插入到数据库表中，同时在页面上显示出来。（通常数据库里面会有一张字段与该excel表头一一对应的表）；

Excel导出——把数据库中的表的数据导出，保存在excel文件中。

 

poi相对于jxl，是更为成熟的excel解析技术。这段时间折腾了几天，终于弄清楚了过程，特此记录。

一些主要的基本概念：poi-3.8-x.jar

HSSFWokbook——对应excel文件的工作薄

HSSFSheet——对应excel文件的表空间

HSSFRow——对应excel文件的表的行

HSSFCell——对应excel文件的表单元格

HSSFCellStyle——对应excel文件的表单元格样式

 

一、Excel导入（需上传已存在的excel文件）

已经存在的excel文件：

 

jsp页面：

Html代码  

1. <form name="" class="k-form col3" id="M2100F004">  
2.     <label class="k-field-label">文件导入:</label>  
3.         <input class="form-control k-field-file" data-allowblank="false" type="file" id="file" name="excelFile" />  
4.     <button type="button" class="xxx" type="SUBMIT" onclick="importExcel">导入</button>  
5. </form>  

 

上传的js：

Js代码  

1. $.ajaxFileUpload({  
2.     url : "demo/demo-importExcel.json",  
3.     //dataType : 'json',  
4.     secureuri : false,  
5.     fileElementId : 'file',  
6.     success : function(res, status) { //服务器成功响应处理函数  
7.         if (status) {  
8.             //some code  
9.         },  
10.         error : function(res, status, e) {//服务器响应失败处理函数  
11.             alert("导入数据异常：文件导入过程异常。");  
12.         }  
13.     });  
14. }else{  
15.     alert("导入数据异常：系统只支持Excel模板文件导入,请选择正确的模板文件.");  
16.     return;  
17.     }  
18. }  

 

对应的后台：

Java代码  

1. @RequestMapping(value = "/demo/demo-importExcel.json")  
2.     @ResponseBody  
3.     public String importExcel( @RequestParam(value = "excelFile") MultipartFile excelFile,HttpServletRequest request) throws BiffException, IOException, KPromptException{  
4.         if (null == excelFile) {  
5.             result = "模板文件为空,请选择文件";  
6.             return result;  
7.         }  
8.                   
9. //      String path = request.getSession().getServletContext().getRealPath("demo2");  
10.           
11.         String path = "E:\\demo";  
12.         //容错处理  
13.         File dir = new File(path);  
14.         if(!dir.exists()) {  
15.             dir.mkdirs();  
16.         }  
17.         String fileName = excelFile.getOriginalFilename();//report.xls  
18.         String fileName2 = excelFile.getName();//excelFile  
19.           
20.         InputStream fis = excelFile.getInputStream();  
21.          
22.        List<Map<String, Stirng>>  data = ExcelImportUtil..parseExcel(fis);  
23.            //解析到的数据就可以做一些数据库的插入操作了……  
24.        return "success";  
25.     }  

 

重点来了，excel导入我把它封装成了一个工具方法：

Java代码  

1. public class ExcelImportUtil {  
2.     public static List<Map<String, String>> parseExcel(InputStream fis) {  
3.         List<Map<String, String>> data = new ArrayList<Map<String, String>>();;  
4.         try {  
5.             HSSFWorkbook book = new HSSFWorkbook(fis);  
6.             HSSFSheet sheet = book.getSheetAt(0);  
7.             int firstRow = sheet.getFirstRowNum();  
8.             int lastRow = sheet.getLastRowNum();  
9.             //除去表头和第一行  
10. //          ComnDao dao = SysBeans.getComnDao();  
11.             for(int i = firstRow + 1; i<lastRow+1; i++) {  
12.                 Map map = new HashMap();  
13.                   
14.                 HSSFRow row = sheet.getRow(i);  
15.                 int firstCell = row.getFirstCellNum();  
16.                 int lastCell = row.getLastCellNum();  
17.                   
18.                   
19.                 for(int j=firstCell; j<lastCell; j++) {  
20.                       
21.                     HSSFCell cell2 = sheet.getRow(firstRow + 1).getCell(j);  
22.                     String key = cell2.getStringCellValue();  
23.                       
24.                     HSSFCell cell = row.getCell(j);  
25.                       
26.                     if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {  
27.                         cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
28.                     }  
29.                     String val = cell.getStringCellValue();  
30.                       
31. //              System.out.println(val);  
32.                       
33.                     if(i == firstRow + 1) {  
34.                         break;  
35.                     }else{  
36.                         map.put(key, val);  
37.                           
38.                     }  
39. //              System.out.println(map);  
40.                 }  
41.                 if(i != firstRow + 1) {  
42.                     data.add(map);  
43.                     System.out.println(map);  
44.                 }  
45.             }  
46.             System.out.println(data);  
47.         } catch (IOException e) {  
48.             e.printStackTrace();  
49.         }  
50.         return data;  
51.     }  
52. }  

 

可看到数据库中的结果：

 

注意：

1）poi的api读取excel文件时，最常见的两种cell的数据格式是Numeric和String，所以当是数字格式的时候，需要把它的cell type转成String，否则会出现Cannot get a numeric value from a text cell的错误。对应的，设计相应数据库表最好都是Varchar2格式的。

 

2）需注意需要解析的只是表的数据，所以表的标题应该不在解析范围内；但表头需要解析，他们对应数据库表里的字段；row和cell的位置都是从0开始，

 

二、Excel导出（这里用到了spring的AbstractExcelView）

首先页面：

Html代码  

1. <a    href="demo/demo-exportExcel.json" data-descript="导出测试">导出</a>  
2.   
3. <!-- 或者抽离出一个js-->  
4. var exportExcel = function(){  
5.         var url = "demo/demo-exportExcel.json";  
6.         //window.open(url);  
7.                 location.href = url;//具体为啥改用这个，是个坑。。强烈建议这个  
8.             }  

 对应的后台：

第一步：excel导出的主要工具类：

Java代码  

1. public class ExcelExportUtil {  
2.       
3.     public static HSSFWorkbook generateExcel(List<Map<String, String>> list, String title) {  
4.         HSSFWorkbook book = new HSSFWorkbook();  
5.         try{  
6.             File desFile = new File("d:\\人员表.xls");  
7.             FileOutputStream fos = new FileOutputStream(desFile);  
8.                         HSSFSheet sheet = book.createSheet("Sheet1");  
9.             sheet.autoSizeColumn(1, true);//自适应列宽度  
10.             //样式设置  
11.             HSSFCellStyle style = book.createCellStyle();  
12.             style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);  
13.               style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
14.               style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
15.               style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
16.               style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
17.               style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
18.               style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
19.               // 生成一个字体  
20.               HSSFFont font = book.createFont();  
21.               font.setColor(HSSFColor.VIOLET.index);  
22.               font.setFontHeightInPoints((short) 12);  
23.               font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
24.               // 把字体应用到当前的样式  
25.               style.setFont(font);  
26.               
27.                 
28.               HSSFCellStyle style2 = book.createCellStyle();  
29.                   style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
30.                   //设置上下左右边框  
31.                   style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
32.                   style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
33.                   style2.setBorderRight(HSSFCellStyle.BORDER_THIN);  
34.                   style2.setBorderTop(HSSFCellStyle.BORDER_THIN);  
35.                   style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
36.                     
37.               //填充表头标题  
38.               int colSize = list.get(0).entrySet().size();  
39.               System.out.println("size:" + colSize);  
40.               //合并单元格供标题使用(表名)  
41.               sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colSize-1));  
42.               HSSFRow firstRow = sheet.createRow(0);//第几行（从0开始）  
43.               HSSFCell firstCell = firstRow.createCell(0);  
44.               firstCell.setCellValue(title);  
45.               firstCell.setCellStyle(style);  
46.                 
47.               //填充表头header  
48.               HSSFRow row = sheet.createRow(1);  
49.               Set<Entry<String, String>> set = list.get(0).entrySet();  
50.               List<Entry<String, String>> l = new ArrayList<Map.Entry<String,String>>(set);  
51.               System.out.println("l:" + l.size());  
52.               for(int i=0; i< l.size(); i++) {  
53.                   String key = l.get(i).getKey();  
54.                   System.out.println(key);  
55.                   HSSFCell cell = row.createCell(i);  
56.                   cell.setCellValue(key);  
57.                   cell.setCellStyle(style2);  
58.               }  
59.                 
60.               //填充表格内容  
61.               System.out.println("list:" + list.size());  
62.               for(int i=0; i<list.size(); i++) {  
63.                   HSSFRow row2 = sheet.createRow(i+2);//index：第几行  
64.                   Map<String, String> map = list.get(i);  
65.                   Set<Entry<String, String>> set2 = map.entrySet();  
66.                   List<Entry<String, String>> ll = new ArrayList(set2);  
67.                   for(int j=0; j<ll.size(); j++) {  
68.                       String val = ll.get(j).getValue();  
69.                       HSSFCell cell = row2.createCell(j);//第几列：从0开始  
70.                       cell.setCellValue(val);  
71.                       cell.setCellStyle(style2);  
72.                   }  
73.               }  
74.                 
75. //           book.write(fos);   
76. //           fos.close();  
77.         } catch(Exception ex) {  
78.             ex.printStackTrace();  
79.         }  
80.         return book;  
81.     }  
82.       
83. }  

 注意：

1）合并单元格的语法，这里new CellRangeAddress(0, 0, 0, colSize-1)底层为：

Java代码  

1. public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)  
2.  {  
3.    super(firstRow, lastRow, firstCol, lastCol);  
4.  }  

 因此表示合并第一行第0-colSize-1列，这常常用于放置表的标题。

2）createRow(int rownum)和createCell(int cellnum)表示创建第几行或第几列，都是从0开始。

 

第二步，在第一步得到HSSFWorkbook的基础上，得到excel文件：涉及的jar包：spring-webmvc-4.x.jar

 

Java代码  

1. public class ViewExcel extends AbstractExcelView {  
2.   
3.     @Override  
4.     protected void buildExcelDocument(Map<String, Object> map,  
5.             HSSFWorkbook book, HttpServletRequest request, HttpServletResponse response)  
6.             throws Exception {  
7. //        String filename = "人员信息.xls";//设置下载时客户端Excel的名称         
8. //          filename = encodeFilename(filename, request);//处理中文文件名      
9.             response.setContentType("application/vnd.ms-excel");         
10.             response.setHeader("Content-disposition", "attachment;filename=" + filename);         
11.             OutputStream ouputStream = response.getOutputStream();         
12.             book.write(ouputStream);         
13.             ouputStream.flush();         
14.             ouputStream.close();     
15.   
16.     }  
17. }  

 

第三步，控制层：

 

Java代码  

1. @RequestMapping(value = "/demo/demo-exportExcel.json")  
2.     @ResponseBody  
3.     public ModelAndView report(ModelMap model, HttpServletRequest request, HttpServletResponse response) {  
4.          ViewExcel viewExcel = new ViewExcel();        
5.           Map obj = null;    
6.           System.out.println("response:" + response);  
7.           //获取数据库表生成的workbook    
8.           Map condition = new HashMap();    
9.               //这里是从数据库里查数据并组装成我们想要的数据结构的过程，略。。  
10.               List<Map<String, String>>  data = dao.xxxx;  
11.           HSSFWorkbook workbook = ExcelExportUtil.generateExcel(data, "人员信息表");    
12.           try {    
13.            viewExcel.buildExcelDocument(obj, workbook, request, response);    
14.           } catch (Exception e) {    
15.         // TODO Auto-generated catch block    
16.               e.printStackTrace();    
17.           }    
18.           return new ModelAndView(viewExcel, model);       
19.     }  

 

需注意：下载时是否弹出下载选择框是由浏览器的下载选项设置决定的，并不是代码决定的——特没有节操，害我折腾了N久！！

成果：

 

顺便提一下，做的过程遇到的一些j2se的语法问题：

List<String>转换为String[]的方法：

String[] strs = list.toArray(new String[list.size()])即需指定String[]类型的参数，如果直接这样：String[] strs = (String[]) list.toArray();会出现类型转换的异常:

[Ljava.lang.Object; cannot be cast to [Ljava.lang.String;