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 未完成原因,plans 后续工作安排,remark 备注 from weekplans where time between '" + old + "' and '" + now + "'  order by time,name";            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.Encoding.UTF8)));                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;            }        }    }}

以上就是EPPlus.dll动态库的简单使用，感觉还特么可以。

三、这里有两个点需要记一下方便日后查阅

1.前台页面需要使用window.open("一般处理程序.ashx");代码使用浏览器下载后台一般处理程序输出的binary数据

2.捕获的“由于代码已经过优化...”异常，需要通过context.ApplicationInstance.CompleteRequest();代替context.Response.End()结束输出