C#使⽤EPPlus.dll动态库在⼀般处理程序中实现将datatable导
出到excel
⼀、.如何安装EPPLus
1.在Visual Studio的解决⽅案的引⽤上右键选择管理NeGet程序包
2.在管理NuGet程序包中搜索EPPlus，点击进⾏安装，安装后便直接添加到引⽤了
⼆、在⼀般处理程序中使⽤EPPlus的OfficeOpenXml命名空间，进⾏datatable到excel的处理
先上张将datatable的数据导出到excel效果图
下⾯贴代码：
using dbhelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
namespace MyBlog
{
/// <summary>
/// explore 的摘要说明
/// </summary>
public class explore : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
//string filecode = "系统软件室周计划执⾏情况记录表";
string now = DateTime.Now.ToString("yyyy-MM-dd");
string old = DateTime.Now.AddDays(-6d).ToString("yyyy-MM-dd");
string fileName = "系统软件室周计划" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
string sql = "select name 姓名,title 项⽬名称,text ⼯作内容,yutime 预计完成时间,problem 存在的问题,stext ⼯作内容,execution 完成情况,reason 未完成原因,            DataTable dt = DataBase.QueryTable(sql);
try
{
ExportExcelByEPPlus(context,dt, fileName);
}
catch (Exception ex)
{
context.Response.Write(ex.ToString());
context.Response.End();
throw;
}
}
public static void ExportExcelByEPPlus(HttpContext context,DataTable dt, string fileName)
{
using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
{
string sheetName = string.IsNullOrEmpty(dt.TableName) ? "sheet1" : dt.TableName;
OfficeOpenXml.ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);
ws.Cells["A4"].LoadFromDataTable(dt, true);//从A4的单元格加载datatable中的数据
OfficeOpenXml.Style.ExcelBorderStyle borderStyle = OfficeOpenXml.Style.ExcelBorderStyle.Thin;                System.Drawing.Color borderColor = System.Drawing.Color.FromArgb(0, 0, 0);
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, dt.Rows.Count + 6, dt.Columns.Count])                {
rng.Style.Font.Name = "宋体";
rng.Style.Font.Size = 11;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(255, 255, 255));
rng.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
rng.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
rng.Style.Border.Top.Style = borderStyle;
rng.Style.Border.Top.Color.SetColor(borderColor);
rng.Style.Border.Bottom.Style = borderStyle;
rng.Style.Border.Bottom.Color.SetColor(borderColor);
rng.Style.Border.Right.Style = borderStyle;
rng.Style.Border.Right.Color.SetColor(borderColor);
}
//Format the header row
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, dt.Columns.Count])//1⾏1列到1⾏n列                {
rng.Merge = true;//合并单元格
rng.Style.Font.Bold = true;
rng.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(255, 255, 255));
rng.Style.Font.Color.SetColor(System.Drawing.Color.FromArgb(0, 0, 0));
rng.Value = "系统软件室周计划执⾏情况记录表";
}
using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 1])
{
rng.Value = "时间";
}
using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 2, 2, 10])
{
rng.Merge = true;
rng.Value = "系统研发室";
}
using (OfficeOpenXml.ExcelRange rng = ws.Cells[3, 1, 4, 1])
{
rng.Merge = true;
rng.Value = "姓名";
}
using (OfficeOpenXml.ExcelRange rng = ws.Cells[3, 2, 4, 2])
{
rng.Merge = true;
rng.Value = "项⽬名称";
}
using (OfficeOpenXml.ExcelRange rng = ws.Cells[3, 10, 4, 10])
{
rng.Merge = true;
rng.Value = "备注";
}
using (OfficeOpenXml.ExcelRange rng = ws.Cells[3, 3, 3, 5])
{
rng.Merge = true;
rng.Value = "本周计划";
}
using (OfficeOpenXml.ExcelRange rng = ws.Cells[3, 6, 3, 9])
{
rng.Merge = true;
rng.Value = "上周计划完成情况";
}
using (OfficeOpenXml.ExcelRange rng = ws.Cells[5, 1, dt.Rows.Count + 4, dt.Columns.Count])
{
rng.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(255, 255, 255));
rng.Style.Font.Color.SetColor(System.Drawing.Color.FromArgb(0, 0, 0));
}
//返回到客户端
context.Response.Clear();
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
context.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xlsx", HttpUtility.UrlEncode(fileName, System.Text.Encodin                context.Response.ContentEncoding = System.Text.Encoding.UTF8;
context.Response.BinaryWrite(pck.GetAsByteArray());
context.Response.Flush();
context.ApplicationInstance.CompleteRequest();//解决捕获的“由于代码已经过优化...”的try catch异常
/
/HttpContext.Current.Response.End();
}
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
htmlborder以上就是EPPlus.dll动态库的简单使⽤，感觉还特么可以。
三、这⾥有两个点需要记⼀下⽅便⽇后查阅
1.前台页⾯需要使⽤window.open("⼀般处理程序.ashx")；代码使⽤浏览器下载后台⼀般处理程序输出的binary数据
2.捕获的“由于代码已经过优化...”异常，需要通过context.ApplicationInstance.CompleteRequest()；代替context.Response.End()结
束输出

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。