Excel导出

最新推荐文章于 2021-05-12 15:51:55 发布

孙小伤

最新推荐文章于 2021-05-12 15:51:55 发布

阅读量205

点赞数

分类专栏： JAVA

本文链接：https://blog.csdn.net/qq_33266657/article/details/79695793

版权

JAVA 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

excel导出功能：前台用的bootstrap

直接贴代码：

button按钮：

<button id="downloadTemplateBtn" class="btn  btn-sm"
	οnclick="exportExcle();">
	<i class="glyphicon glyphicon-download"></i> 模板下载
</button>

然后是js方法：

url +="?cost_type="+"cost_electricity"; 费用类型，自己项目需要的，如果不需要则删了

url +="&user_login="+securityContext.username; 登录用户名，自己项目需要，权限验证，如果不需要删了

url +="&token=" + securityContext.token：前后端分离，跨域使用，如果不需要删了

function exportExcle(){  
		 window.top.bootbox.dialog({
         title: "提示信息",
         message: "请确认是否下载模板",
         size: "middle",
         buttons: {
             OkType: {
                 label: "确定",
                 className: "btn-"+bootstrapSkin,
                 callback: function() {
                	 var url = path + "api/sys/cost/exportexcel";
             		url +="?cost_type="+"cost_electricity";
             		url +="&user_login="+securityContext.username;
             		url +="&token=" + securityContext.token
                   $.ajax({  
                       type:"POST",  
                       url:url,  
                       success:function(data){  
                           window.open(url);  
                       }  
                   });  
             },
             },
             cancelType: {
                 label: "取消",
                 className: "btn-"+bootstrapSkin,
                 callback: function() {
                     return true;
                 }
             }
        }
     });
	}

后台代码：Controller

@RequestMapping(value = "exportexcel")跳转地址

@RequestMapping(value = "exportexcel")
	public @ResponseBody void getExportExcel(HttpServletRequest request, HttpServletResponse response){
		try {
			String msg = null;
			String user_login = request.getParameter("user_login");
			String cost_type = request.getParameter("cost_type");
			List<DmpCostUpload> list = costService.getTemplate(cost_type);
			 HSSFWorkbook workbook = costService.createExcel(list, request,cost_type);  
			 SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss"); // 定义文件名格式
			 if("cost_electricity".equals(cost_type)){
				  msg = new String(("电费信息_" + format.format(new Date()) + ".xls").getBytes(),"ISO-8859-1");  
			 }else{
				  msg = new String(("空调费信息_" + format.format(new Date()) + ".xls").getBytes(),"ISO-8859-1");  
			 }
	            // 以导出时间作为文件名  
	            response.setContentType("application/vnd.ms-excel");  
	            response.addHeader("Content-Disposition", "attachment;filename="+ msg);  
	            workbook.write(response.getOutputStream());  
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

List<DmpCostUpload> list = costService.getTemplate(cost_type);自己的业务操作，我的是生成一下list，具体看情况

public HSSFWorkbook createExcel(List<DmpCostUpload> dmpList,HttpServletRequest request,String cost_type) throws Exception {  
	  
	        // 创建一个webbook，对应一个excel文件  
	        HSSFWorkbook workbook = new HSSFWorkbook();  
	        // 在webbook中添加一个sheet,对应excel文件中的sheet  
	        HSSFSheet sheet = workbook.createSheet("模板信息");  
	        // 设置列宽  
	        sheet.setColumnWidth(0, 25 * 100);  
	        sheet.setColumnWidth(1, 35 * 100);  
	        sheet.setColumnWidth(2, 35 * 100);  
	        sheet.setColumnWidth(3, 40 * 100);  
	        sheet.setColumnWidth(4, 45 * 100);  
	        sheet.setColumnWidth(5, 45 * 100);  
	        sheet.setColumnWidth(6, 50 * 100);  
	        sheet.setColumnWidth(7, 80 * 100);  
	        sheet.setColumnWidth(8, 35 * 100);  
	        sheet.setColumnWidth(9, 40 * 100);  
	        // 在sheet中添加表头第0行  
	        HSSFRow row = sheet.createRow(0);  
	        // 创建单元格，并设置表头，设置表头居中  
	        HSSFCellStyle style = workbook.createCellStyle();  
	        // 创建一个居中格式  
	        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
	        // 带边框  
	        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
	        // 生成一个字体  
	        HSSFFont font = workbook.createFont();  
	        // 字体增粗  
	        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
	        // 字体大小  
	        font.setFontHeightInPoints((short) 12);  
	        // 把字体应用到当前的样式  
	        style.setFont(font);  
	  
	        // 单独设置整列居中或居左  
	        HSSFCellStyle style1 = workbook.createCellStyle();  
	        style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
	        HSSFCellStyle style2 = workbook.createCellStyle();  
	        style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);  
	  
	        HSSFCellStyle style3 = workbook.createCellStyle();  
	        style3.setAlignment(HSSFCellStyle.ALIGN_LEFT);  
	        HSSFFont hssfFont = workbook.createFont();  
	        hssfFont.setColor(HSSFFont.COLOR_RED);  
	        hssfFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
	        style3.setFont(hssfFont);  
	  
	        HSSFCellStyle style4 = workbook.createCellStyle();  
	        style4.setAlignment(HSSFCellStyle.ALIGN_LEFT);  
	        HSSFFont hssfFont1 = workbook.createFont();  
	        hssfFont1.setColor(HSSFFont.COLOR_NORMAL);  
	        hssfFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
	        style4.setFont(hssfFont1);  
	  
	        HSSFCell cell = row.createCell(0);  
	        cell.setCellValue("年度");  
	        cell.setCellStyle(style);  
	  
	        cell = row.createCell(1);  
	        cell.setCellValue("月度");  
	        cell.setCellStyle(style);  
	  
	        cell = row.createCell(2);  
	        cell.setCellValue("区域");  
	        cell.setCellStyle(style);  
	  
	        cell = row.createCell(3);  
	        cell.setCellValue("公寓");  
	        cell.setCellStyle(style);  
	        if("cost_electricity".equals(cost_type)){
	        	cell = row.createCell(4);  
	 	        cell.setCellValue("楼层");  
	 	        cell.setCellStyle(style);  
	 	  
	 	        cell = row.createCell(5);  
	 	        cell.setCellValue("房间编码");  
	 	        cell.setCellStyle(style);  
	 	  
	 	        cell = row.createCell(6);  
	 	        cell.setCellValue("房间名");  
	 	        cell.setCellStyle(style); 
	 	        
	 	        cell = row.createCell(7);  
	 	        cell.setCellValue("上月度数");  
	 	        cell.setCellStyle(style); 
	 	        
	 	        cell = row.createCell(8);  
	 	        cell.setCellValue("本月度数");  
	 	        cell.setCellStyle(style); 
	        }else{
	        	cell = row.createCell(4);  
	 	        cell.setCellValue("楼层编码");  
	 	        cell.setCellStyle(style); 
	 	        
	 	        cell = row.createCell(5);  
	 	        cell.setCellValue("楼层");  
	 	        cell.setCellStyle(style); 
	 	        
	 	        cell = row.createCell(6);  
	 	        cell.setCellValue("空调分摊度数");  
	 	        cell.setCellStyle(style);
	        }
	  
	       
	        
	        for (int i = 0; i < dmpList.size(); i++) {  
	            row = sheet.createRow(i + 1);  
	            DmpCostUpload costUpload = dmpList.get(i);  
	            // 创建单元格，并设置值  
	            // 编号列居左  
	            //设置年份
	            HSSFCell c1 = row.createCell(0);  
	            c1.setCellStyle(style2);  
	            c1.setCellValue(costUpload.getDmp_cost_year());  
	            //设置月份
	            HSSFCell c2 = row.createCell(1);  
	            c2.setCellStyle(style1);  
	            c2.setCellValue(costUpload.getDmp_cost_month());
	            
	            //设置区域
	            HSSFCell c3 = row.createCell(2);
	            c3.setCellStyle(style1);  
	            c3.setCellValue(costUpload.getDmp_region_name());  
	            //设置公寓  
	            HSSFCell c4 = row.createCell(3);
	            c4.setCellStyle(style1);  
	            c4.setCellValue(costUpload.getDmp_apart_name());  
	            
	            if("cost_electricity".equals(cost_type)){
	                //设置楼层
		            HSSFCell c5 = row.createCell(4);
		            c5.setCellStyle(style1);  
		            c5.setCellValue(costUpload.getDmp_floor_name()); 
		            
		            //设置房间编码
		            HSSFCell c6 = row.createCell(5);
		            c6.setCellStyle(style1);  
		            c6.setCellValue(costUpload.getDmp_room_id()); 
		            
		            //设置房间名
		            HSSFCell c7 = row.createCell(6);
		            c7.setCellStyle(style1);  
		            c7.setCellValue(costUpload.getDmp_room_name());  
		            
		            //上月度数
		            HSSFCell c8 = row.createCell(7);
		            c8.setCellStyle(style1);  
		            c8.setCellValue("0");  
		            
		            //本月度数
		            HSSFCell c9 = row.createCell(8);
		            c9.setCellStyle(style1);  
		            c9.setCellValue("0");
	            }else{
	            	 //设置楼层编码
		            HSSFCell c5 = row.createCell(4);
		            c5.setCellStyle(style1);  
		            c5.setCellValue(costUpload.getDmp_floor_id());
		            
		            
		          //设置楼层
		            HSSFCell c6 = row.createCell(5);
		            c6.setCellStyle(style1);  
		            c6.setCellValue(costUpload.getDmp_floor_name()); 
		            
		            //设置分摊度数
		            HSSFCell c7 = row.createCell(6);
		            c7.setCellStyle(style1);  
		            c7.setCellValue(costUpload.getDmp_air_cond());
	            }
	        }  
	        return workbook;  
	}

上面代码根据自己的需求进行修改

poi-3.9.jar
poi-examples-3.17.jar
poi-excelant-3.17.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar
poi-scratchpad-3.17.jar

这是需要的jar包，我在使用的过程中出现了一些问题，就是在设置样式的时候我一开始使用的poi-3.17.jar但是现在没有哪些样式，但是换成3.9以后就没事了，不知道什么原因！尴尬

孙小伤

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录