C#使用EPPlus.dll动态库在一般处理程序中实现将datatable导出到excel-CSDN博客

本文链接：https://blog.csdn.net/CGS_______/article/details/72654983

一、.如何安装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动态库的简单使用，感觉还特么可以。