本文介绍了一种使用Java POI库从数据库查询结果中导出Excel文件的方法，并提供了完整的代码示例。该示例展示了如何配置HTTP请求参数、执行SQL查询、构造Excel表格并将其发送给客户端。
Java 利用poi 导出Excel
/**
* 导出全部查询结果
* @param request
* @param response
*/
@RequestMapping(value = "export/excel", method = {RequestMethod.POST,RequestMethod.GET})
public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
String specialId = ServletRequestUtils.getStringParameter(request, "specialId","");
String creatorId = ServletRequestUtils.getStringParameter(request, "creatorId","");
String authorId = ServletRequestUtils.getStringParameter(request, "authorId","");
String title = ServletRequestUtils.getStringParameter(request, "title","");
Integer startViewCount = ServletRequestUtils.getIntParameter(request, "startViewCount",0);
Integer endViewCount = ServletRequestUtils.getIntParameter(request, "endViewCount",10*10*1000);
String startTime = ServletRequestUtils.getStringParameter(request, "startTime","");
String endTime = ServletRequestUtils.getStringParameter(request, "endTime","");
String viewOrder = ServletRequestUtils.getStringParameter(request, "viewOrder","1");
StringBuffer sb = new StringBuffer(""); // 返回结果
int page = 1;
int pageSize = 10;
String timeStr ="",havingStr="";
StringBuffer sqlSb = new StringBuffer("");
sqlSb = querySql(specialId, creatorId, authorId, title, startViewCount, endViewCount, startTime, endTime, viewOrder, timeStr, havingStr, sqlSb);
logger.info("query sql：");
logger.info(sqlSb.toString());
List<ArticleStatistics> list = articleStatisticsSrv.listSql(sqlSb.toString(),page,pageSize,ArticleStatistics.class,false);
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HHmmss");
String fileName = sdf.format(new Date())+".xls";
//导出Excel
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet 1");
HSSFRow row = sheet.createRow((int) 0);
HSSFCell cell = row.createCell(0);
cell.setCellValue("文章ID");
cell = row.createCell(1);
cell.setCellValue("标题");
cell = row.createCell(2);
cell.setCellValue("阅读数");
cell = row.createCell(3);
cell.setCellValue("转发数");
cell = row.createCell(4);
cell.setCellValue("点赞数");
cell = row.createCell(5);
cell.setCellValue("收藏数");
cell = row.createCell(6);
cell.setCellValue("发布时间");
for (int i = 0; i < list.size(); i++){
row = sheet.createRow((i+1));
ArticleStatistics as = (ArticleStatistics) list.get(i);
row.createCell(0).setCellValue(as.getArticleId());
row.createCell(1).setCellValue(as.getTitle());
BigInteger bi = new BigInteger("0");
bi = as.getViewCount();
row.createCell(2).setCellValue(bi.intValue());
bi = as.getShareCount();
row.createCell(3).setCellValue(bi.intValue());
bi = as.getLikesCount();
row.createCell(4).setCellValue(bi.intValue());
bi = as.getCollectCount();
row.createCell(5).setCellValue(bi.intValue());
row.createCell(6).setCellValue(as.getFactTimeStr());
//cell = row.createCell(3);
//cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(stu.getBirth()));
}
// 把相应的Excel 工作簿写入到reponse的输出流中
String mimetype = "application/x-msdownload";
response.setContentType(mimetype);
String inlineType = "attachment"; // 是否内联附件
response.setHeader("Content-Disposition", inlineType + ";filename=\"" + fileName + "\"");
OutputStream out=response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}catch (Exception e){
e.printStackTrace();
}
}页面：
<%@ page contentType="text/html; charset=UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<head>
<title>时代财经管理后台 - 文章数据图表</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<!-- bootstrap -->
<link href="/wamei/pages/css/bootstrap/bootstrap.css" rel="stylesheet" />
<link href="/wamei/pages/css/bootstrap/bootstrap-responsive.css" rel="stylesheet" />
<link href="/wamei/pages/css/bootstrap/bootstrap-overrides.css" type="text/css"
rel="stylesheet" />
<link href="/wamei/pages/css/lib/bootstrap-datetimepicker.css" type="text/css" rel="stylesheet" />
<!-- global styles -->
<link rel="stylesheet" type="text/css" href="/wamei/pages/css/elements.css" />
<link rel="stylesheet" type="text/css" href="/wamei/pages/css/icons.css" />
<!-- libraries -->
<link href="/wamei/pages/css/lib/font-awesome.css" type="text/css"
rel="stylesheet" />
<!-- this page specific styles -->
<link rel="stylesheet" href="/wamei/pages/css/compiled/tables.css" type="text/css"
media="screen" />
<link rel="stylesheet" href="/wamei/pages/css/kkpager_blue.css" type="text/css"
media="screen" />
<!-- open sans font -->
<link href='/wamei/pages/css/open-sans-font.css' rel='stylesheet' type='text/css' />
<!--external css-->
<link href="/wamei/pages/css/font-awesome/css/font-awesome.css" rel="stylesheet" />
<!--[if lt IE 9]>
<script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
<![endif]-->
<style type="text/css">
.row-fluid .pull-left span{
padding-left:5px;
}
.row-fluid .pull-left input{
height:30px;
width:130px;
}
.ui-select{
height:30px;
margin-bottom:10px;
}
.row-fluid .pull-left a{
width:40px;height:20px;font-size: 15px;text-align: center;line-height: 20px;
}
.text-overflow{
width:200px !important;
line-height: 30px;
text-overflow:ellipsis;//让超出的用...实现
white-space:nowrap;//禁止换行
overflow:hidden;//超出的隐藏
}
.table-wrapper .table td a{
text-decoration: none !important;
}
</style>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<div class="container-fluid">
<div id="pad-wrapper">
<!-- products table-->
<!-- the script for the toggle all checkboxes from header is located in js/theme.js -->
<div class="table-wrapper products-table section">
<div class="row-fluid head">
<div class="span12">
<h4>文章数据图表</h4>
</div>
</div>
<div class="row-fluid filter-block">
<div class="pull-left">
<span>所属频道：
<div class="ui-select">
<select id="spSelect">
<option value="">请选择</option>
</select>
</div>
</span>
<span>编辑：
<div class="ui-select">
<select id="editSelect">
<option value="">请选择</option>
</select>
</div>
</span>
<span>作者：
<div class="ui-select">
<select id="authorSelect">
<option value="">请选择</option>
</select>
</div>
</span>
<span>文章标题：<input type="text" id="searchTitle"/></span>
<br>
<span>阅读数：
<input type="text" id="startViewCount"/>
-
<input type="text" id="endViewCount"/>
</span>
<span>时间：
<div class="input-append date form_st_datetime">
<input type="text" value="" class="input-large datetimepicker" readonly id="startFactTime"/>
<span class="add-on"><i class="icon-remove"></i></span>
<span class="add-on"><i class="icon-calendar"></i></span>
</div>
-
<div class="input-append date form_en_datetime">
<input type="text" class="input-large datetimepicker" readonly id="endFactTime"/>
<span class="add-on"><i class="icon-remove"></i></span>
<span class="add-on"><i class="icon-calendar"></i></span>
</div>
</span>
</div>
<div class="pull-left" style="margin-left:50px;">
<a class="btn-flat success new-product" onclick="query(true)">查询</a>
<a class="btn-flat info new-product" style="width: 120px;" onclick="exportExcel(true)">导出全部查询结果</a>
</div>
</div>
<div class="row-fluid">
<table class="table table-hover" id="dataList">
<thead>
<tr>
<th class="span1">序号</th>
<th class="span4"><span class="line"></span>标题</th>
<th class="span1"><span class="line"></span>阅读数</th>
<th class="span1"><span class="line"></span>转发数</th>
<th class="span1"><span class="line"></span>点赞数</th>
<th class="span1"><span class="line"></span>收藏数</th>
<th class="span2"><span class="line"></span>操作</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
<div class="pagination pull-right" id="kkpager"></div>
</div>
<!-- end products table -->
</div>
<!-- scripts -->
<script src="/wamei/pages/js/jquery.min.js"></script>
<script src="/wamei/pages/js/bootstrap-datetimepicker.js"></script>
<script src="/wamei/pages/js/kkpager.min.js"></script>
<script src="/wamei/pages/js/bootstrap.min.js"></script>
<script src="/wamei/pages/js/theme.js"></script>
<script type="text/javascript">
var pageNo = 1, pageSize = 20, totalRecords = 10, totalPage = 1, articleStatus = 0;
var conditions = {status:0,title:'',specialId:'',creatorId:'',authorId:'',startViewCount:0,endViewCount:0,startFactTime:'',endFactTime:''};
var stFtTime,endFtTime; // 开始结束时间
var rvMoId = "",rvStatus = "";
var reviewRecordList = {};
$(function() {
articleStatus = '${param.status}';
if (!articleStatus || articleStatus == "") {
articleStatus = 0;
}
articleStatus = isNaN(articleStatus) ? 0 : articleStatus;
query();
initSearchConditions();
$(".form_st_datetime").datetimepicker({
autoclose: true,
todayBtn: true,
minuteStep: 10,
pickerPosition: "bottom-left"
}).on('changeDate',function(ev){
stFtTime = ev.date.valueOf();
if(endFtTime && stFtTime && endFtTime < stFtTime){
alert("开始时间 不能大于 结束时间！");
$('#startFactTime').val("");
stFtTime = undefined;
}
});
$(".form_en_datetime").datetimepicker({
autoclose: true,
todayBtn: true,
minuteStep: 10,
pickerPosition: "bottom-left"
}).on('changeDate',function(ev){
endFtTime = ev.date.valueOf();
if(endFtTime && stFtTime && endFtTime < stFtTime){
alert("结束时间 不能早于 开始时间 ！");
$('#endFactTime').val("");
endFtTime = undefined;
}
});
});
//查询角色数据
function query(btnFlag) {
//查询条件验证
searchCheck(btnFlag);
$.ajax({
url : "/wamei/articleStatisticsController/query.htm",
data : conditions,
type : "POST",
async : false,
success : function($data) {
var data = eval("(" + $data + ")");
if (data && data.statusCode == 1) {
loadDatas(data.result.data);
totalRecords = data.result.totalRecords;
totalPage = data.result.totalPage;
pageTools();
} else {
alert(data.msg);
}
}
});
}
//导出全部查询结果
function exportExcel(btnFlag) {
//查询条件验证
searchCheck(btnFlag);
var str = parseParam(conditions);
var url = "/wamei/articleStatisticsController/export/excel.htm?"+str;
window.location.href=url;
}
//将对象转成url 参数
var parseParam=function(param, key){
var paramStr="";
if(param instanceof String||param instanceof Number||param instanceof Boolean){
paramStr+="&"+key+"="+encodeURIComponent(param);
}else{
$.each(param,function(i){
var k=key==null?i:key+(param instanceof Array?"["+i+"]":"."+i);
paramStr+='&'+parseParam(this, k);
});
}
return paramStr.substr(1);
};
// 校验查询条件
function searchCheck(btnFlag){
if(btnFlag){pageNo = 1;}
conditions.status = articleStatus;
conditions.page = pageNo;
conditions.pageSize = pageSize;
conditions.title = $("#searchTitle").val();
conditions.specialId = $("#spSelect").val();
conditions.creatorId = $("#editSelect").val();
conditions.authorId = $("#authorSelect").val();
conditions.startViewCount = $("#startViewCount").val();
conditions.endViewCount = $("#endViewCount").val();
conditions.startTime = $("#startFactTime").val();
conditions.endTime = $("#endFactTime").val();
}
// 加载数据
function loadDatas(datas) {
var tb = $("#dataList tbody");
tb.html("");
for (var i = 0; i < datas.length; i++) {
var mo = datas[i];
//表格数据展示
var tr = "<tr>"
+ "<td>"+(((pageNo-1)*pageSize)+i+1)+"</td>"
+ "<td><a href='/wamei/articleController/preview/"+mo.articleId+".htm' target='_blank'>"+mo.title+"</a></td>"
+ "<td>"+mo.viewCount+"</td>"
+ "<td>"+mo.shareCount+"</td>"
+ "<td>"+mo.likesCount+"</td>"
+ "<td>"+mo.collectCount+"</td>"
+ "<td><a href='/wamei/pages/articles/article_statistics_day.jsp?articleId="+mo.articleId+"&title="+mo.title+"'>数据详情</a></td>";
tb.append(tr);
}
}
//分页
function pageTools() {
kkpager.init({
pno : pageNo,
//总数据条数
totalRecords : totalRecords,
//总页码
total : totalPage,
//链接前部
hrefFormer : 'query',
//链接尾部
hrefLatter : '.htm',
getLink : function(n) {
return this.hrefFormer + this.hrefLatter + "?pno=" + n;
},
mode : 'click',//默认值是link，可选link或者click
click : function(n) {
this.selectPage(n);
pageNo = n;
query();
return false;
}
});
kkpager.generPageHtml();
}
//初始化查询条件
function initSearchConditions(){
//频道数据加载
$.ajax({
url : "/wamei/userController/queryUserSpecials.htm",
data:{"page":1, "pageSize":100},
async : true,
success : function($data) {
var data = eval("(" + $data + ")");
if (data && data.statusCode == 1) {
var list = data.result;
if(list && list.length>0){
for(var i=0;i<list.length;i++){
var mo = list[i];
$("#spSelect").append("<option value='"+mo.id+"'>"+mo.name+"</option>");
}
}
} else {
alert(data.msg);
}
}
});
//加载编辑者数据
$.ajax({
url : "/wamei//userController/query.htm",
data:{"page":1, "pageSize":1000},
async : true,
success : function($data) {
var data = eval("(" + $data + ")");
if (data && data.statusCode == 1) {
var list = data.result.data;
if(list && list.length>0){
for(var i=0;i<list.length;i++){
var mo = list[i];
$("#editSelect").append("<option value='"+mo.id+"'>"+mo.userName+"</option>");
}
}
} else {
alert(data.msg);
}
}
});
//加载作者数据 （数据过多，待优化）
$.ajax({
url : "/wamei/authorController/queryAll.htm",
data:{"page":1, "pageSize":1000},
async : true,
success : function($data) {
var data = eval("(" + $data + ")");
if (data && data.statusCode == 1) {
var list = data.result.data;
if(list && list.length>0){
for(var i=0;i<list.length;i++){
var mo = list[i];
$("#authorSelect").append("<option value='"+mo.id+"'>"+mo.nickName+"</option>");
}
}
} else {
alert(data.msg);
}
}
});
}
</script>
扫一扫
629
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
