poi 匯出Excel java程式碼

做你的貓發表於2018-11-26
ExcelJava

js:

 1 function initBatchExport(url,sub_key,current_sub_num){
 2     var btn_id="#btn_char"+current_sub_num;
 3     if(Number(current_sub_num)==1){
 4         btn_id="#btn_char";
 5     }
 6 //    查詢條件
 7     //var paramObj=new Object();
 8     //var result_Data ={};
 9     month_value=$("#month_value").val();
10     dim_key = sub_key;
11     type = "量";
12     var exportUrl=basePath+'/amount/initBatchExport.do?month_value='+month_value+'&dim_key='+dim_key+'&type='+type+'&current_sub_num='+current_sub_num;
13     for (var i = 1; i <=Number(current_sub_num); i++) {
14         var parent_key = "parent_dim_"+(i);
15         exportUrl+='&'+parent_key+'='+$("#"+parent_key).val();
16     }
17     $(btn_id).on("click",function(){
18         
19     //    var excelurl ="<%=basePath%>/hfx/unwantExport.action?impId="+impId;
20         
21         location.href=exportUrl;
22     });
23 }

controller:

 1 @RequestMapping(value = "/initBatchExport", method = {RequestMethod.GET,RequestMethod.POST})
 2     @ResponseBody
 3     public void initBatchExport(Locale locale, Model model,HttpServletRequest request,HttpServletResponse response){
 4         ResultMap data =ResultMap.defaultResultMap(); //new HashMap<String,Object>();
 5         //Map<String,String>  param = RequestUtils.getParamsMap(request);
 6         Map<String,String>  param =new HashMap<String,String>();
 7         Map map = request.getParameterMap();
 8         String current_sub_num = request.getParameter("current_sub_num");
 9         String month_value = request.getParameter("month_value");
10         String dim_key = request.getParameter("dim_key");
11         String type = request.getParameter("type");
12         for (int i = 1; i <=Integer.parseInt(current_sub_num); i++) {
13             String parent_key = "parent_dim_"+(i);
14             param.put(parent_key, request.getParameter(parent_key));
15         }
16         param.put("current_sub_num", current_sub_num);
17         param.put("month_value", month_value);
18         param.put("dim_key", dim_key);
19         param.put("type", type);
20         //需匯出的資料
21         List<ZdResult8BrandD> list = amonutService.initJqGrid(param);
22         //初始化匯出Excel
23         // 第一步，建立一個webbook，對應一個Excel檔案 
24           HSSFWorkbook wb = new HSSFWorkbook(); 
25           // 第二步，在webbook中新增一個sheet,對應Excel檔案中的sheet 
26           HSSFSheet sheet = wb.createSheet("Sheet1"); 
27           // 第三步，在sheet中新增表頭第0行,注意老版本poi對Excel的行數列數有限制short 
28           HSSFRow row = sheet.createRow(0); 
29           // 第四步，建立單元格，並設定值表頭 設定表頭居中 
30           HSSFCellStyle style = wb.createCellStyle(); 
31           style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 建立一個居中格式 
32 
33           HSSFCell cell = row.createCell(0);
34           //'賬期', '地市', '品牌','機型', '價格段', '客戶','分類客戶', 'subtype','value'
35           cell.setCellValue("賬期"); 
36           cell.setCellStyle(style); 
37           cell = row.createCell(1); 
38           cell.setCellValue("地市"); 
39           cell.setCellStyle(style); 
40           cell = row.createCell(2); 
41           cell.setCellValue("品牌"); 
42           cell.setCellStyle(style); 
43           cell = row.createCell(3); 
44           cell.setCellValue("機型"); 
45           cell.setCellStyle(style);
46           cell = row.createCell(4); 
47           cell.setCellValue("價格段"); 
48           cell.setCellStyle(style);
49           cell = row.createCell(5); 
50           cell.setCellValue("客戶"); 
51           cell.setCellStyle(style);
52           cell = row.createCell(6); 
53           cell.setCellValue("分類客戶"); 
54           cell.setCellStyle(style);
55           cell = row.createCell(7); 
56           cell.setCellValue("子類"); 
57           cell.setCellStyle(style);
58           cell = row.createCell(8); 
59           cell.setCellValue("值"); 
60           cell.setCellStyle(style);
61 
62           // 第五步，寫入實體資料
63           for (int i = 0; i < list.size(); i++){ 
64                row = sheet.createRow(i + 1); 
65                ZdResult8BrandD zdResult = list.get(i); 
66                // 第四步，建立單元格，並設定值 
67                row.createCell(0).setCellValue(zdResult.getStatDay()); 
68                row.createCell(1).setCellValue(zdResult.getAreaName()); 
69                row.createCell(2).setCellValue(zdResult.getBrandName()); 
70                row.createCell(3).setCellValue(zdResult.getSpec()); 
71                row.createCell(4).setCellValue(zdResult.getPriceRange()); 
72                row.createCell(5).setCellValue(zdResult.getCustName()); 
73                row.createCell(6).setCellValue(zdResult.getCustStage()); 
74                row.createCell(7).setCellValue(zdResult.getSubType()); 
75                row.createCell(8).setCellValue(zdResult.getValue()); 
76 //               cell = row.createCell(3); 
77 //               cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(stu.getTime())); 
78               }
79 
80         //第六步,輸出Excel檔案
81           try {
82           OutputStream output=response.getOutputStream();
83           response.reset();
84           SimpleDateFormat df = new SimpleDateFormat("yyyy_MM_dd HHmmss");//設定日期格式
85           String fileName = dim_key+df.format(new Date());// new Date()為獲取當前系統時間
86           response.setHeader("Content-disposition", "attachment; filename="+fileName+".xls");
87           response.setContentType("application/msexcel");  
88         
89             wb.write(output);
90             output.flush();
91         } catch (IOException e) {
92             e.printStackTrace();
93         }
94           
95 
96         //return resultMap;
97     }

 

相關文章