在一般的系统开发中,将表格数据导出为Excel是一项常见的功能。导出Excel分为三种情况,第一种情况是导出当前页的数据,第二种情况是导出当前页已选择的数据,第三种情况是导出全部数据。前两种情况大致相同,本质上可归为一类,因为它们都只是针对当前页的数据进行操作,一般可在前端直接进行JSON到Excel的转换。而第三种情况则略微复杂一些,因为大多数情况下我们会使用服务器端分页对数据进行展示。下面以Bootstrap-Table为例进行说明,我在这里做了一个例子,如下图所示:
测试数据
前端代码
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta charset="utf-8" />
<title>Bootstrap-Table导出Excel</title>
<link href="lib/bootstrap/css/bootstrap.min.css" rel="stylesheet" />
<link href="lib/bootstrap-table/bootstrap-table.min.css" rel="stylesheet" />
<script src="lib/bootstrap/js/jquery-3.4.1.min.js"></script>
<script src="lib/bootstrap/js/bootstrap.min.js"></script>
<script src="lib/bootstrap-table/bootstrap-table.min.js"></script>
<script src="lib/bootstrap-table/bootstrap-table-zh-CN.min.js"></script>
</head>
<body>
<div style="width:1000px;margin:200px auto;">
<div id="toolbar" class="btn-group">
<a class="btn btn-primary" href="ashx/ExportHandler.ashx">导出全部数据</a>
</div>
<table id="table"></table>
</div>
<script>
$(document).ready(function () {
$('#table').bootstrapTable({
url: "ashx/GetDataHandler.ashx", // URL
method: "post", // 请求类型
contentType: "application/x-www-form-urlencoded", // post请求必须要有,否则后台接受不到参数
toolbar: "#toolbar", // 工具条
sidePagination: "server", // 设置在服务端还是客户端分页
showRefresh: false, // 是否刷新按钮
sortStable: true, // 是否支持排序
cache: false, // 是否使用缓存
pagination: true, // 是否显示分页
search: false, // 是否有搜索框
clickToSelect: true, // 是否点击选中行
pageNumber: 1, // 首页页码,默认为1
pageSize: 5, // 页面数据条数
pageList: [5, 10, 20, 30],
queryParamsType: "",
queryParams: function (params) {
return {
pageSize: params.pageSize, // 每页记录条数
pageNumber: params.pageNumber, // 当前页索引
};
},
columns: [{
field: 'Id',
title: '编号',
align: "center",
halign: "center",
sortable: true
},
{
field: 'Name',
title: '姓名',
align: "center",
halign: "center"
},
{
field: 'Gender',
title: '性别',
align: "center",
halign: "center"
},
{
field: 'Age',
title: '年龄',
align: "center",
halign: "center"
}]
})
});
</script>
</body>
</html>
后台代码
分页查询
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Newtonsoft.Json;
namespace WebApplication1.ashx
{
/// <summary>
/// GetDataHandler 的摘要说明
/// </summary>
public class GetDataHandler : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
// 获取EasyUI参数
int pageNumber = int.Parse(context.Request.Params["pageNumber"]);
int pageSize = int.Parse(context.Request.Params["pageSize"]);
int total = 0;
// 获取数量
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
{
connection.Open();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "select count(*) from [TPerson]";
total = Convert.ToInt32(command.ExecuteScalar());
}
// 分页查询
DataTable dataTable = new DataTable();
string commandText = "select * from(select row_number() over(order by Id) as RowId, *from [TPerson]) as b where b.RowId between (@pageNumber - 1) * @pageSize + 1 and @pageNumber * @pageSize order by Id";
using (SqlDataAdapter adapter = new SqlDataAdapter(commandText, ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@pageNumber", pageNumber),
new SqlParameter("@pageSize", pageSize)
};
adapter.SelectCommand.Parameters.AddRange(parameters);
adapter.Fill(dataTable);
}
// 传递给前端的数据
var data = new { total = total, rows = dataTable };
context.Response.Write(JsonConvert.SerializeObject(data));
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
导出Excel
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using Newtonsoft.Json;
namespace WebApplication1.ashx
{
/// <summary>
/// ExportHandler 的摘要说明
/// </summary>
public class ExportHandler : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "application/octet-stream";
context.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("测试文档.xlsx"));
// 查询全部数据
DataTable dataTable = new DataTable();
string commandText = "select * from [TPerson]";
using (SqlDataAdapter adapter = new SqlDataAdapter(commandText, ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
{
adapter.Fill(dataTable);
}
// 创建工作簿
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("属性表");
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
// 写入字段名
cell = row.CreateCell(0);
cell.SetCellValue("编号");
cell = row.CreateCell(1);
cell.SetCellValue("姓名");
cell = row.CreateCell(2);
cell.SetCellValue("性别");
cell = row.CreateCell(3);
cell.SetCellValue("年龄");
// 写入字段值
for (int i = 0; i < dataTable.Rows.Count; i++)
{
row = sheet.CreateRow(i + 1);
for (int j = 0; j < dataTable.Columns.Count; j++)
{
cell = row.CreateCell(j);
cell.SetCellValue(dataTable.Rows[i][j].ToString());
}
}
// 输出文件流
workbook.Write(context.Response.OutputStream);
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
点击导出全部数据按钮后可以发现已经能下载Excel文件了,如下图所示:
文件内容如下图所示: