.net core 利用MiniExcel导出表格

下述代码中注释为利用File导出CSV格式表格（再利用excel/WPS打开）；

下述为using MiniExcelLibs进行xlsx文件导出，MiniExcel最好通过nuget安装或通过码云或Github获取

public IActionResult Csv()
        {
            var getType = Request.Query["type"];
            var getValue = Request.Query["value"];

            IQueryable<ReportModel> reports = GetList(getValue, getType);

            //以下注释为导出CSV文件，不推荐如此操作，主要是此需要将拿到的数据集进行再次组合为string;同时如此导出的csv文件，在用excel打开时，若csv中一列已经含有了导出时用的分隔符，会导致文件打开时列出现对应不上的情况
            /*        var builder = new StringBuilder();
                    builder.AppendLine("TagID;AssetID;Description;InserviceDate;UsefulLife;ProjectID;ProjectName;Name;SN;Location;Status;Comment;Quantity;Checked");
                     foreach (var item in reports)
                        {
                            string strCheck = item.Checked == true ? "Checked" : "";
                            string strUse = item.Status == true ? "InUse" : "";
                            builder.AppendLine($"{item.TagID};{item.AssetID};{item.Description};{item.InserviceDate};{item.UsefulLife};" +
                                $"{item.ProjectID};{item.ProjectName};{item.Name};{item.SN};{item.Location};{strUse};{item.Comment};{item.Quantity};{strCheck}");//item.Checked
                        }
            return File(Encoding.UTF8.GetBytes(builder.ToString()), "text/csv", "report.csv");*/

            var memoryStream = new MemoryStream();
            memoryStream.SaveAs(reports.Select(r =>
            new
            {
                r.TagID,
                r.AssetID,
                r.Description,
                r.InserviceDate,
                r.UsefulLife,
                r.ProjectID,
                r.ProjectName,
                r.Name,
                r.SN,
                r.Location,
                r.Status,
                r.Comment,
                r.Quantity,
                r.Checked
            }));
            memoryStream.Seek(0, SeekOrigin.Begin);
            return new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                FileDownloadName = "AssetReport.xlsx"
            };

        }

 

上述代码中使用的GetList() funciton:

public IQueryable<ReportModel> GetList(string input_query, string select_type)
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

            var query = from a in _context.Set<AssetInformation>()
                        join b in _context.Set<AssetUser>()
                        on a.AssetOwner equals b.OwnerID
                        join c in _context.Set<ProjectModel>()
                        on a.ProjectID equals c.ProjectID into ac
                        from d in ac.DefaultIfEmpty()
                        orderby (a.ID)
                        select new ReportModel
                        {
                            id = a.ID,

                            TagID = a.TagID,
                            AssetID = a.AssetID,
                            Description = a.Description,
                            InserviceDate = a.InserviceDate,
                            UsefulLife = a.UsefulLife,

                            ProjectID = a.ProjectID,
                            ProjectName = d.ProjectName,
                            Name = b.Name,
                            SN = a.SerialNO,
                            Location = a.Location,

                            Status = a.Status,
                            Comment = a.Comment,
                            Quantity = a.Quantity,
                            Checked = a.Checked,
                            AssetOwnerId = a.AssetOwner
                        };
            //除了下述两种情况外，全查询出所有结果

            if (select_type == "OwnerID" && !string.IsNullOrEmpty(input_query))
            {
                input_query = input_query.Trim();
                query = query.Where(a => a.AssetOwnerId.Equals(Convert.ToInt32(input_query)));
            }
            if (select_type == "Name" && !String.IsNullOrEmpty(input_query))
            {
                query = query.Where(i => i.Name.Contains(input_query.Trim()));
            }
            ViewBag.type = select_type;
            ViewBag.input_query = input_query;


            stopwatch.Stop();
            Console.WriteLine(stopwatch.ElapsedMilliseconds);
            return query;
        }