本文最后更新于 776 天前,其中的信息可能已经有所发展或是发生改变。
model 实体
namespace Test.Models.Book
{
public class BookModel
{
public int BookId { get; set; }
public string FileName { get; set; }
}
}
controller 控制器层
using Test.BLL.Book;
using Test.Models.Book;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System.Threading.Tasks;
namespace Test.Controllers.Book
{
[Route("api/book/[controller]/[action]")]
[ApiController]
public class Book : Controller
{
[HttpPost]
public ActionResult ExportBookData([FromBody] BookModel model) // 下载文件
{
BookDal bll = new BookDal();
DataSet ds = bll.ExportBookData(model.BookId);
Dictionary<string, List<ExcelColumn>> dc = new Dictionary<string, List<ExcelColumn>>();
ds.Tables[0].TableName = "sheet1";
List<ExcelColumn> listColumns = bll.ExportBookColumns(); // xlsx文件的表头
dc.Add("sheet1", listColumns);
MemoryStream ms = new ExcelUtils().GetStreamByData(ds, dc);
byte[] buffer = ms.ToArray(); //转化为byte格式存储
MemoryStream ms1 = new MemoryStream(buffer);
return File(ms1, System.Net.Mime.MediaTypeNames.Application.Octet, model.FileName);
}
}
}
bll 业务逻辑层
using Test.DAL.Book;
using Test.Models.Book;
using System.Threading.Tasks;
using System;
using System.Collections.Generic;
namespace Test.BLL.Book
{
public class Book
{
BookDal bll = new BookDal();
public DataSet ExportBookData(int BookId) // 导出的数据
{
return bll.ExportBookData(BookId);
}
public List<ExcelColumn> ExportBookColumns() // 导出的表头
{
return bll.ExportBookColumns();
}
}
}
dal 数据操作层
using Test.BLL;
using Test.Models.Book;
using System.Threading.Tasks;
using System;
using System.Collections.Generic;
namespace Test.DAL.Book
{
public class Book
{
public DataSet ExportBookData(int BookId)
{
IDbConnection sqlconn = SqlUtil.SqlConnection(ConfigUtil.GetSysSettings().Book);
try
{
string sql = @"SELECT * FROM book WHERE BookId = @BookId"
sqlconn.Open();
SqlCommand sqlcomm = new SqlCommand();
sqlcomm.CommandType = CommandType.Text;
sqlcomm.Connection = sqlconn;
sqlcomm.CommandText = sql;
sqlcomm.Parameters.Add(new SqlParameter("@BookId", BookId));
SqlDataAdapter sqlda = new SqlDataAdapter(sqlcomm);
DataSet ds = new DataSet();
sqlda.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw new Exception("查询失败");
}
finally
{
sqlconn.Close();
}
}
public List<ExcelColumn> ExportBookColumns()
{
string[] columns =
{
"BookId"
,"BookName"
,"BookDetail"
,"BookDate"
,"SaleNumber"
,"BookPrice"
,"SaleCity"
};
List<ExcelColumn> listColumns = new List<ExcelColumn>();
foreach (var column in columns)
{
string headName = column;
switch (column)
{
case "BookId":
headName = "BookId";
break;
case "BookName":
headName = "BookName";
break;
case "BookDetail":
headName = "BookDetail";
break;
case "BookDate":
headName = "BookDate";
break;
case "SaleNumber":
headName = "SaleNumber";
break;
case "BookPrice":
headName = "BookPrice";
break;
case "SaleCity":
headName = "SaleCity";
break;
}
listColumns.Add(new ExcelColumn(headName, column, 200));
}
return listColumns;
}
}
}