C#实现xlsx文件导出
2023-06-13 09:15:30 时间
本文最后更新于 128 天前,其中的信息可能已经有所发展或是发生改变。
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;
}
}
}
相关文章
- C# AntiForgeryToken防XSRF漏洞攻击
- RestSharp_C#
- C# 实现xlsx文件导入
- C#窗口调用---打开一个窗口关闭另一个窗口
- C#连接Access数据时总报找不到dbo.mdb的问题
- C#通用文件上传类
- C#数据导入/导出Excel文件及winForm导出Execl总结
- C#下载文件(TransmitFile/WriteFile/流方式)实例介绍
- C#连接Oracle数据库的实例方法
- C#中调用命令行cmd开启wifi热点的实例代码
- 浅析C#web访问mysql数据库-整理归纳总结
- C#调用动态unlha32.dll解压Lha后缀的打包文件分享
- C#判断文件路径是否存在或者判断文件是否存在的方法
- C#删除文件夹和文件到回收站示例
- c#文件下载示例的4种方法分享
- C#同步、异步远程下载文件实例
- C#文件加密方法汇总
- C#(WinForm)ComboBox和ListBox添加项及设置默认选择项
- C#文件管理类Directory实例分析
- C#类中static变量用法分析
- C#中委托和事件在观察者模式中的应用实例
- C#函数式编程中的缓存技术详解
- C#生成设置范围内的Double类型随机数的方法
- C#使用iCSharpcode进行文件压缩实现方法
- C#基于NPOI生成具有精确列宽行高的Excel文件的方法