欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Excel导出

程序员文章站 2022-06-25 12:02:13
...

这篇博客主要介绍的是excel导出数据的功能,将数据导入到excel表格中进行显示

1.准备相应的按钮和数据

给一个点击按钮,保证数据库里面有相应的数据,同时准备好查询语句。我这边准备的是一个查询所有数据的sql

<select id="getByList" resultType="TCompanyChargeOrder">
	  select * from t_company_charge_order t
	  <include refid="where" />
2.给按钮增加相应的ajax或者其他的事件,我这边是ajax

function toDownload(){
        	layer.confirm('确定导出购买记录?', {icon: 3, title:'提示'}, function(index){
			        		 $.ajax({
			                     url : '/tCompanyChargeOrders/getExcelResource',
			                     success : function(data) {
			                        if(data=='ok'){
			                        	 layer.msg("导出成功,请及时在桌面查看");
			                        }else{
			                        	 layer.msg("导出失败");
			                        }
			                     }
			                 });
							  layer.close(index);
							});
           
        }
大家应该看到了,我采用的是layui进行页面渲染,该代码是成功和失败后显示的相应的提示消息。接下来就是controller了,
@ApiOperation(value="导出信息")
	@RequestMapping(value="getExcelResource")
	public String excelResource(HttpServletResponse response){
		// 第一步,创建一个webbook,对应一个Excel文件 
		HSSFWorkbook wb = new HSSFWorkbook();  
        // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet("充值表");
        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        sheet.setColumnWidth(2, 20 * 256);
        sheet.setColumnWidth(3, 20 * 256);
        sheet.setColumnWidth(4, 20 * 256);
        sheet.setColumnWidth(5, 20 * 256);
        sheet.setColumnWidth(6, 20 * 256);
        sheet.setColumnWidth(7, 20 * 256);
        sheet.setColumnWidth(8, 20 * 256);
        sheet.setColumnWidth(9, 20 * 256);
        sheet.setColumnWidth(10, 20 * 256);
        sheet.setColumnWidth(11, 20 * 256);
        
        // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        HSSFRow row = sheet.createRow((int) 0);  
        // 第四步,创建单元格,并设置值表头 设置表头居中  
        HSSFCellStyle style = wb.createCellStyle();
        
        HSSFCell cell = row.createCell((short) 0);  
        cell.setCellValue("充值订单号");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 1);  
        cell.setCellValue("订单状态");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 2);
        cell.setCellValue("充值账户");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 3);  
        cell.setCellValue("企业编号");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 4);
        cell.setCellValue("企业名称");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 5);  
        cell.setCellValue("总金额");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 6);
        cell.setCellValue("充值金额");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 7);  
        cell.setCellValue("手续费");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 8);
        cell.setCellValue("**时间");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 9);  
        cell.setCellValue("备注");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 10);
        cell.setCellValue("创建时间");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 11);  
        //写入数据
        List excellist = tCompanyChargeOrderDao.getByList();
        //循环放值
        for(int i=0;i<excellist.size();i++){
        	row = sheet.createRow((int) i + 1);
        	TCompanyChargeOrder tcharge = (TCompanyChargeOrder)excellist.get(i);
        	//放入相应的数据
        	row.createCell((short) 0).setCellValue((double) tcharge.getId());
        	row.createCell((short) 1).setCellValue(tcharge.getOrderState());
        	row.createCell((short) 2).setCellValue(tcharge.getUsername());
        	row.createCell((short) 3).setCellValue(tcharge.getCompanyNo());
        	row.createCell((short) 4).setCellValue(tcharge.getCompanyName());
        	row.createCell((short) 5).setCellValue(tcharge.getTotalAmount().toString());
        	row.createCell((short) 6).setCellValue(tcharge.getChargeAmount().toString());
        	row.createCell((short) 7).setCellValue(tcharge.getChargeFee().toString());
        	row.createCell((short) 8).setCellValue(DateToStr(tcharge.getActiveTime()));
        	row.createCell((short) 9).setCellValue(tcharge.getRemark());
        	row.createCell((short) 10).setCellValue(DateToStr(tcharge.getCreatedTime()));
        	//存放位置
        	 try  
             {  
                 //获取当前用户桌面路径
        		 File desktopDir = FileSystemView.getFileSystemView().getHomeDirectory();
                 String desktopPath = desktopDir.getAbsolutePath();
                 System.out.println(desktopPath);
                 //保存到桌面上,固定名字
                 FileOutputStream fout = new FileOutputStream(desktopPath+"/tcharge.xls");  
                 wb.write(fout);  
                 fout.close();
             }  
             catch (Exception e)  
             {  
                 e.printStackTrace();
                 return "error";
             }  
        }
        return "ok";
	}
我总共要显示十二行的表头,在这里就要指定十二个HSSFCell,其次就是调用你之前写好的查询语句,最后用循环进行取值即可,当然,这是很简单的导出功能,对于后面的学习希望和大家一起交流。最后附上我的截图。

Excel导出
至于字体居中,大小,样式等等,大家就可以对照着去摸索,做完发现挺有意思的。。(LZ事后诸葛亮哈。。)

相关标签: excel 导出