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]publicclassBook: Controller
{[HttpPost]public ActionResult ExportBookData([FromBody] BookModel model)// 下载文件{
BookDal bll =newBookDal();
DataSet ds = bll.ExportBookData(model.BookId);
Dictionary<string, List<ExcelColumn>> dc =newDictionary<string, List<ExcelColumn>>();
ds.Tables[0].TableName ="sheet1";
List<ExcelColumn> listColumns = bll.ExportBookColumns();// xlsx文件的表头
dc.Add("sheet1", listColumns);
MemoryStream ms =newExcelUtils().GetStreamByData(ds, dc);
byte[] buffer = ms.ToArray();//转化为byte格式存储
MemoryStream ms1 =newMemoryStream(buffer);returnFile(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
{publicclassBook{
BookDal bll =newBookDal();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
{publicclassBook{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 =newSqlCommand();
sqlcomm.CommandType = CommandType.Text;
sqlcomm.Connection = sqlconn;
sqlcomm.CommandText = sql;
sqlcomm.Parameters.Add(newSqlParameter("@BookId", BookId));
SqlDataAdapter sqlda =newSqlDataAdapter(sqlcomm);
DataSet ds =newDataSet();
sqlda.Fill(ds);return ds;}catch(Exception ex){thrownewException("查询失败");}finally{
sqlconn.Close();}}public List<ExcelColumn>ExportBookColumns(){
string[] columns ={"BookId","BookName","BookDetail","BookDate","SaleNumber","BookPrice","SaleCity"};
List<ExcelColumn> listColumns =newList<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(newExcelColumn(headName, column,200));}return listColumns;}}}