Excel导出

这篇博客主要介绍的是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,其次就是调用你之前写好的查询语句，最后用循环进行取值即可，当然，这是很简单的导出功能，对于后面的学习希望和大家一起交流。最后附上我的截图。

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