【JavaWeb】WEB下excel导出

桃扇骨 2022-06-17 20:48 114阅读 0赞

excel导出功能的过程。

control层：

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

1.   @RequestMapping(value="download.do")  
2.         **public** String download(HttpServletRequest request,HttpServletResponse response, YsdwAnimal ysdwAnimal) **throws** IOException\{  
3.              String fileName="animal";  
4.              //获取参数，然后将参数赋给ysdwAnimal中的相应值  
5.              String animalname = URLDecoder.decode(URLDecoder.decode( request.getParameter("animalname"), "utf-8"), "utf-8");  
6.              String animaltypes = URLDecoder.decode(URLDecoder.decode( request.getParameter("animaltypes"), "utf-8"), "utf-8");  
7.               
8.              ysdwAnimal.setAnimalname(animalname);  
9.              ysdwAnimal.setAnimaltypes(animaltypes);  
10.              
11.             //填充projects数据  
12.             List<YsdwAnimal> ysdwAnimalList= createData(ysdwAnimal);  
13.             List<Map<String,Object>> list=createExcelRecord(ysdwAnimalList);  
14.             //列名  
15.             String columnNames\[\]=\{ "动物名称","动物编号","动物种类","植被类型","经度","维度","坡度","坡位","坡向","坐标","海拔","天气","记录时间","备注","地点","保护等级"\};  
16.             //map中的key  
17.             String keys\[\] = \{ "animalname","animalnumber","animaltypes","vegetationtypes","jingdu","weidu","podu","powei","poxiang","zuobiao","haiba","tianqi","jilusj","qita","did","baohdj"\};  
18.             ByteArrayOutputStream os = **new** ByteArrayOutputStream();  
19.             **try** \{  
20.                 //填充内容 判断字段 列名  
21.                 ExcelUtil.createWorkBook(list,keys,columnNames).write(os);  
22.             \} **catch** (IOException e) \{  
23.                 e.printStackTrace();  
24.             \}  
25.             **byte**\[\] content = os.toByteArray();  
26.             InputStream is = **new** ByteArrayInputStream(content);  
27.             // 设置response参数，可以打开下载页面  
28.             response.reset();  
29.             response.setContentType("application/vnd.ms-excel;charset=utf-8");  
30.             response.setHeader("Content-Disposition", "attachment;filename="\+ **new** String((fileName + ".xls").getBytes(), "iso-8859-1"));  
31.             ServletOutputStream out = response.getOutputStream();  
32.             BufferedInputStream bis = **null**;  
33.             BufferedOutputStream bos = **null**;  
34.             **try** \{  
35.                 bis = **new** BufferedInputStream(is);  
36.                 bos = **new** BufferedOutputStream(out);  
37.                 **byte**\[\] buff = **new** **byte**\[2048\];  
38.                 **int** bytesRead;  
39.                 // Simple read/write loop.  
40.                 **while** (-1 != (bytesRead = bis.read(buff, 0, buff.length))) \{  
41.                     bos.write(buff, 0, bytesRead);  
42.                 \}  
43.             \} **catch** (**final** IOException e) \{  
44.                 **throw** e;  
45.             \} **finally** \{  
46.                 **if** (bis != **null**)  
47.                     bis.close();  
48.                 **if** (bos != **null**)  
49.                     bos.close();  
50.             \}  
51.             **return** **null**;  
52.         \}  
53.           
54.           
55.         **private** List<YsdwAnimal> createData(YsdwAnimal ysdwAnimal) \{  
56.             // TODO Auto-generated method stub  
57.             //自己实现  
58.             **return** ysdwAnimalService.getysdwAnimalList(ysdwAnimal);  
59. //            if(list.size() == 0)\{   
60. //              return null;  
61. //            \}  
62. //            //将  
63. //            for(int i = 0; i < list.size(); i++)\{   
64. //                
65. //            \}  
66.         \}  
67.           
68.         //获取内容  
69.           
70.         **private** List<Map<String, Object>> createExcelRecord(List<YsdwAnimal> ysdwAnimalList) \{  
71.             List<Map<String, Object>> listmap = **new** ArrayList<Map<String, Object>>();  
72.             Map<String, Object> map = **new** HashMap<String, Object>();  
73.             map.put("sheetName", "sheet1");  
74.             listmap.add(map);  
75.             YsdwAnimal ysdwAnimal = **null**;  
76.             **for** (**int** j = 0; j < ysdwAnimalList.size(); j++) \{  
77.                 ysdwAnimal=ysdwAnimalList.get(j);  
78.                 Map<String, Object> mapValue = **new** HashMap<String, Object>();  
79.                 mapValue.put("animalname", ysdwAnimal.getAnimalname());  
80.                 mapValue.put("animalnumber", ysdwAnimal.getAnimalnumber());  
81.                 mapValue.put("animaltypes", ysdwAnimal.getAnimaltypes());  
82.                 mapValue.put("vegetationtypes", ysdwAnimal.getVegetationtypes());  
83.                 mapValue.put("jingdu", ysdwAnimal.getJingdu());  
84.                 mapValue.put("weidu", ysdwAnimal.getWeidu());  
85.                 mapValue.put("podu", ysdwAnimal.getPodu());  
86.                 mapValue.put("powei", ysdwAnimal.getPowei());  
87.                 mapValue.put("poxiang", ysdwAnimal.getPoxiang());  
88.                 mapValue.put("zuobiao", ysdwAnimal.getZuobiao());  
89.                 mapValue.put("haiba", ysdwAnimal.getHaiba());  
90.                 mapValue.put("tianqi", ysdwAnimal.getTianqi());  
91.                 mapValue.put("jilusj", ysdwAnimal.getJilusj());  
92.                 mapValue.put("qita", ysdwAnimal.getQita());  
93.                 mapValue.put("lujing", ysdwAnimal.getLujing());  
94.                 mapValue.put("baohdj", ysdwAnimal.getBaohdj());  
95.                 listmap.add(mapValue);  
96.             \}  
97.             **return** listmap;  
98.         \}

control层的三个连续方法依次实现：1将po里的数据取出，放到一个listMap种2.将listmap交给底层方法createWorkBook（创建工作博）进行创建。

底层方法：

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

1.   /\*\* 
2.       \* 创建excel文档， 
3.       \* @param list 数据 
4.       \* @param keys list中map的key数组集合 
5.       \* @param columnNames excel的列名 
6.       \* \*/  
7.      **public** **static** Workbook createWorkBook(List<Map<String, Object>> list,String \[\]keys,String columnNames\[\]) \{  
8.          // 创建excel工作簿  
9.          Workbook wb = **new** HSSFWorkbook();  
10.         // 创建第一个sheet（页），并命名  
11.         Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());  
12.         // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。  
13.         **for**(**int** i=0;i<keys.length;i++)\{  
14.             sheet.setColumnWidth((**short**) i, (**short**) (35.7 \* 150));  
15.         \}  
16.   
17.         // 创建第一行  
18.         Row row = sheet.createRow((**short**) 0);  
19.   
20.         // 创建两种单元格格式  
21.         CellStyle cs = wb.createCellStyle();  
22.         CellStyle cs2 = wb.createCellStyle();  
23.   
24.         // 创建两种字体  
25.         Font f = wb.createFont();  
26.         Font f2 = wb.createFont();  
27.   
28.         // 创建第一种字体样式（用于列名）  
29.         f.setFontHeightInPoints((**short**) 10);  
30.         f.setColor(IndexedColors.BLACK.getIndex());  
31.         f.setBoldweight(Font.BOLDWEIGHT\_BOLD);  
32.   
33.         // 创建第二种字体样式（用于值）  
34.         f2.setFontHeightInPoints((**short**) 10);  
35.         f2.setColor(IndexedColors.BLACK.getIndex());  
36.   
37. //        Font f3=wb.createFont();  
38. //        f3.setFontHeightInPoints((short) 10);  
39. //        f3.setColor(IndexedColors.RED.getIndex());  
40.   
41.         // 设置第一种单元格的样式（用于列名）  
42.         cs.setFont(f);  
43.         cs.setBorderLeft(CellStyle.BORDER\_THIN);  
44.         cs.setBorderRight(CellStyle.BORDER\_THIN);  
45.         cs.setBorderTop(CellStyle.BORDER\_THIN);  
46.         cs.setBorderBottom(CellStyle.BORDER\_THIN);  
47.         cs.setAlignment(CellStyle.ALIGN\_CENTER);  
48.   
49.         // 设置第二种单元格的样式（用于值）  
50.         cs2.setFont(f2);  
51.         cs2.setBorderLeft(CellStyle.BORDER\_THIN);  
52.         cs2.setBorderRight(CellStyle.BORDER\_THIN);  
53.         cs2.setBorderTop(CellStyle.BORDER\_THIN);  
54.         cs2.setBorderBottom(CellStyle.BORDER\_THIN);  
55.         cs2.setAlignment(CellStyle.ALIGN\_CENTER);  
56.         //设置列名  
57.         **for**(**int** i=0;i<columnNames.length;i++)\{  
58.             Cell cell = row.createCell(i);  
59.             cell.setCellValue(columnNames\[i\]);  
60.             cell.setCellStyle(cs);  
61.         \}  
62.         //设置每行每列的值  
63.         **for** (**short** i = 1; i < list.size(); i++) \{  
64.             // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的  
65.             // 创建一行，在页sheet上  
66.             Row row1 = sheet.createRow((**short**) i);  
67.             // 在row行上创建一个方格  
68.             **for**(**short** j=0;j<keys.length;j++)\{  
69.                 Cell cell = row1.createCell(j);  
70.                 cell.setCellValue(list.get(i).get(keys\[j\]) == **null**?" ": list.get(i).get(keys\[j\]).toString());  
71.                 cell.setCellStyle(cs2);  
72.             \}  
73.         \}  
74.         **return** wb;  
75.     \}  
76.

这是导出excel的方法链。

文章出处源自于[http://blog.csdn.net/zh964822413/article/details/68953280][http_blog.csdn.net_zh964822413_article_details_68953280]

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