core6 EPPlus 导入下载

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;


namespace SW163.Infrastructure
{
    public class EPPlusHelpe
    {
        private static int i;

        /// <summary>
        /// 导入数据到Excel中
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="ds"></param>
        public static bool ImportExcel(string fileName, DataSet ds)
        {
            if (ds == null || ds.Tables.Count == 0)
            {
                return false;
            }
            FileInfo file = new FileInfo(fileName);
            if (file.Exists)
            {
                file.Delete();
                file = new FileInfo(fileName);
            }
            //在using语句里面我们可以创建多个worksheet，ExcelPackage后面可以传入路径参数
            //命名空间是using OfficeOpenXml
            using (ExcelPackage package = new ExcelPackage(file))
            {
                foreach (DataTable dt in ds.Tables)
                {
                    //创建工作表worksheet
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(dt.TableName);
                    //给单元格赋值有两种方式
                    //worksheet.Cells[1, 1].Value = "单元格的值";直接指定行列数进行赋值
                    //worksheet.Cells["A1"].Value = "单元格的值";直接指定单元格进行赋值
                    worksheet.Cells.Style.Font.Name = "微软雅黑";
                    worksheet.Cells.Style.Font.Size = 12;
                    worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            worksheet.Cells[i + 1, j + 1].Value = dt.Rows[i][j].ToString();
                        }
                    }
                    using (var cell = worksheet.Cells[1, 1, 1, dt.Columns.Count])
                    {
                        //设置样式:首行居中加粗背景色
                        cell.Style.Font.Bold = true; //加粗
                        cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中
                        cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;     //垂直居中
                        cell.Style.Font.Size = 14;
                        cell.Style.Fill.PatternType = ExcelFillStyle.Solid;  //背景颜色
                        cell.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));//设置单元格背景色
                    }
                }
                //保存
                package.Save();
            }
            return true;
        }

        /// <summary>
        /// 读取Excel数据
        /// </summary>
        /// <param name="fileName"></param>
        public static string ReadExcel(string fileName)
        {
            StringBuilder sb = new StringBuilder();
            FileInfo file = new FileInfo(fileName);
            try
            {
                using (ExcelPackage package = new ExcelPackage(file))
                {
                    var count = package.Workbook.Worksheets.Count;
                    for (int k = 1; k <= count; k++)  //worksheet是从1开始的
                    {
                        var workSheet = package.Workbook.Worksheets[k];
                        sb.Append(workSheet.Name);
                        sb.Append(Environment.NewLine);
                        int row = workSheet.Dimension.Rows;
                        int col = workSheet.Dimension.Columns;
                        for (int i = 1; i <= row; i++)
                        {
                            for (int j = 1; j <= col; j++)
                            {
                                sb.Append(workSheet.Cells[i, j].Value.ToString() + "\t");
                            }
                            sb.Append(Environment.NewLine);
                        }
                        sb.Append(Environment.NewLine);
                        sb.Append(Environment.NewLine);
                    }
                }
            }
            catch (Exception ex)
            {
                return "An error had Happen";
            }
            return sb.ToString();
        }
    
  }
}

  public ExportController(ISignRepository signRepository
            ,IUserRepository userRepository
            ,Microsoft.AspNetCore.Hosting.IHostingEnvironment hostingEnvironment )
        {
            SignRepository = signRepository;
            UserRepository = userRepository;
            HostingEnvironment = hostingEnvironment;
        }

        public ISignRepository SignRepository { get; }
        public IUserRepository UserRepository { get; }
        public Microsoft.AspNetCore.Hosting.IHostingEnvironment HostingEnvironment { get; }

        public IActionResult Index()
        {
            return View();
        }

        public async Task<IActionResult> QueryMethod()
        {
            if (!string.IsNullOrEmpty(Request.Form["startingTime"]) && !string.IsNullOrEmpty(Request.Form["EndTime"]))
            {
                var starTime = Request.Form["startingTime"].ToString();
                var EndTime = Request.Form["EndTime"].ToString();
                var EndTimes = EndTime + " " + "23:59:59";
                var model = await SignRepository.GetListEntitiesAsync(it => it.Workstatus == 1 && it.Searchtime >= Convert.ToDateTime(starTime) &&it.Searchtime<= Convert.ToDateTime(EndTimes));
                if (model.Count > 0)
                {
                    //创建数据列表
                    DataTable dataTable = new DataTable();
                    dataTable.Columns.Add("会员名称", typeof(string));
                    dataTable.Columns.Add("开始时间", typeof(string));
                    dataTable.Columns.Add("结束时间", typeof(string));
                    dataTable.Columns.Add("打卡次数", typeof(string));

                    DataRow row1 = dataTable.NewRow();
                    row1["会员名称"] = "会员名称";
                    row1["开始时间"] = "开始时间";
                    row1["结束时间"] = "结束时间";
                    row1["打卡次数"] = "打卡次数";
                    dataTable.Rows.Add(row1);

                    var usermodel = await UserRepository.GetListEntitiesAsync(it => true);
                    if (usermodel.Count > 0) {

                        foreach (var item in usermodel)
                        {
                            int Count = 0;
                            foreach (var items in model)
                            {
                                if (items.Searchuser == item.Id) {
                                    Count += 1;
                                }
                            }
                            DataRow row = dataTable.NewRow();
                            row["会员名称"] = item.UserName;
                            row["开始时间"] = starTime;
                            row["结束时间"] = EndTime;
                            row["打卡次数"] = Count;
                            dataTable.Rows.Add(row);
                        }

                    }
                    // 创建 DataSet 对象
                    DataSet dataSet = new DataSet("MyDataSet");
                    dataSet.Tables.Add(dataTable);

                    string folder = HostingEnvironment.WebRootPath;
                    string year = Convert.ToDateTime(starTime).ToString("yyyy");
                    string moon = Convert.ToDateTime(starTime).ToString("MM");
                    string xlsname = year + "年" + moon + "月打卡记录生成日" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                    string fileName = Path.Combine(folder, "Excel", xlsname);
                    bool result = EPPlusHelpe.ImportExcel(fileName, dataSet);
                    if (result) {
                        return Content("<script >parent.layer.msg('生成成功！');window.location.href ='/Export/Index';</script >", "text/html");
                    } 
                }
            }

            return Content("<script >parent.layer.msg('操作失败!');history.go(-1);</script >", "text/html");

        }

 //列表
        public IActionResult GetList()
        {
            try
            {
                System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(HostingEnvironment.WebRootPath + "/Excel/");
                DataTable dt = new DataTable();
                dt.Columns.Add("Id", typeof(string));
                dt.Columns.Add("Name", typeof(string));
                dt.Columns.Add("Stime", typeof(System.DateTime));
                dt.Columns.Add("NameSize", typeof(string));
                DataRow dr = dt.NewRow();
                if (Directory.Exists(HostingEnvironment.WebRootPath + "/Excel/"))
                {

                    foreach (System.IO.FileInfo fi in dir.GetFiles())
                    {

                        if (fi.Extension.ToLower() == ".xlsx")
                        {
                            dr = dt.NewRow();
                            dr[0] = fi.Name;
                            dr[1] = fi;
                            dr[2] = fi.CreationTime;
                            dr[3] = (fi.Length / 1024).ToString() + "KB";
                            dt.Rows.Add(dr);
                        }
                    }

                    DataView dv = dt.DefaultView;
                    dv.Sort = "Stime DESC";
                    dt = dv.ToTable();
                }
                List<xlsModel> list = new List<xlsModel>();
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        xlsModel model = new xlsModel();
                        model.Id = dt.Rows[i]["Id"].ToString();
                        model.Name = dt.Rows[i]["Name"].ToString();
                        model.NameSize = dt.Rows[i]["NameSize"].ToString();
                        model.Stime = Convert.ToDateTime(dt.Rows[i]["Stime"].ToString());
                        list.Add(model);
                    }
                }

                return Json(new { code = 0, msg = "成功", count = dt.Rows.Count, data = list });

            }
            catch (System.Exception ex)
            {

                return Json(new { code = 200, msg = "获取数据失败！", count = 0, data = ex });
            }

        }


        //删除
        public IActionResult Del(string Ids)
        {
            bool ret = false;
            if (!string.IsNullOrEmpty(Ids))
            {
                string[] msg = Ids.Split(',');
                dynamic[] id = null;
                if (msg.Length > 0)
                {
                    id = new dynamic[msg.Length];
                    for (int i = 0; i < msg.Length; i++)
                    {
                        id[i] = HostingEnvironment.WebRootPath + "/Excel/" + msg[i];
                        System.IO.FileInfo file = new System.IO.FileInfo(id[i]);
                        if (file.Exists)
                        {
                            file.Delete();
                            ret = true;
                        }
                    }
                }
            }
            if (ret)
            {
                return Json(new { Msg = "操作成功！" });
            }

            return Json(new { Msg = "操作失败！" });
        }

public class xlsModel
{
    public string Id { get; set; }

    public string Name { get; set; }

    public string NameSize { get; set; }
    public DateTime Stime { get; set; }
}