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,其次就是调用你之前写好的查询语句,最后用循环进行取值即可,当然,这是很简单的导出功能,对于后面的学习希望和大家一起交流。最后附上我的截图。
至于字体居中,大小,样式等等,大家就可以对照着去摸索,做完发现挺有意思的。。(LZ事后诸葛亮哈。。)
推荐阅读