NPOI导出Excel

 

首先在官网去下载NPOI，把dll引用到项目中，然后获取列表调用下面的方法就可以导出

后台代码：

/// <summary>
/// NPOI导出Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="fileName"></param>
public static void ExportExcel(DataTable dt, string fileName = "")
{
//生成Excel
IWorkbook book = BuildWorkbook(dt);

//web 下载
if (fileName == "")
fileName = string.Format("{0:yyyyMMddHHmmssffff}", DateTime.Now);
fileName = fileName.Trim();
string ext = Path.GetExtension(fileName);

if (ext.ToLower() == ".xls" || ext.ToLower() == ".xlsx")
fileName = fileName.Replace(ext, string.Empty);

HttpResponse httpResponse = HttpContext.Current.Response;
httpResponse.Clear();
httpResponse.Buffer = true;
httpResponse.Charset = Encoding.UTF8.BodyName;
httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
httpResponse.ContentEncoding = Encoding.UTF8;
httpResponse.ContentType = "application/vnd.ms-excel; charset=UTF-8";
book.Write(httpResponse.OutputStream);
httpResponse.End();
}
//高版本
public static XSSFWorkbook BuildWorkbook(DataTable dt)
{
var book = new XSSFWorkbook();
ISheet sheet = book.CreateSheet("机构联系信息");
//Data Rows
NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("机构名称");
row1.CreateCell(1).SetCellValue("联系人姓名");
row1.CreateCell(2).SetCellValue("办公电话");
row1.CreateCell(3).SetCellValue("手机号");

row1.CreateCell(4).SetCellValue("邮箱");
row1.CreateCell(5).SetCellValue("传真号");
row1.CreateCell(6).SetCellValue("QQ号码");
row1.CreateCell(7).SetCellValue("微信号码");
row1.CreateCell(8).SetCellValue("地址");
row1.CreateCell(9).SetCellValue("邮编");
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow drow = sheet.CreateRow(i+1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = drow.CreateCell(j, CellType.String);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//自动列宽
for (int i = 0; i <= dt.Columns.Count; i++)
sheet.AutoSizeColumn(i, true);

return book;
}

 

前台代码：

//导出
function exportExcel(type) {
searchName = $("#txt_name").val();
if (type == "0") {
if (total == 0) {
layerAlert("没有数据可以导出", 1);
return;
}
}
else {
if (total2 == 0) {
layerAlert("没有数据可以导出", 1);
return;
}
}
var form = $("<form>"); //定义一个form表单
form.attr('style', 'display:none'); //在form表单中添加查询参数
form.attr('target', '');
form.attr('method', 'post');
var num = $("#hidNum").val();
if (type == "0") {
form.attr('action', "ContactAction.ashx?action=ExportLeaderExcel&name=" + searchName);
}
$('body').append(form); //将表单放置在web中
form.submit(); //表单提交
}