使用动态 Linq 和 EPPlus 导出到 Excel
本文关键字：Excel EPPlus 动态 Linq | 更新日期: 2024-11-08 22:25:19
作为概述，我正在尝试向我的应用程序添加Export()功能 - 允许用户指定某些模型字段，并且仅通过使用 LINQ 查询并使用 EPPlus 库导出来导出这些字段中的值。我正在尝试基于此示例在我的 MVC5/EF 代码优先应用程序中实现动态 LINQ 功能，但到目前为止运气不佳。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using InventoryTracker.DAL;
using OfficeOpenXml;
using InventoryTracker.Models;
using System.Linq.Dynamic;
namespace InventoryTracker.Controllers
{
public class ExportController : Controller
{
InventoryTrackerContext _db = new InventoryTrackerContext();
public static List<DynamicColumns> DynamicColumnsCollection = new List<DynamicColumns>();
[HttpPost]
public ActionResult ExportUsingEPPlus(ExportAssetsViewModel model)
{
//FileInfo newExcelFile = new FileInfo(output);
ExcelPackage package = new ExcelPackage();
var ws = package.Workbook.Worksheets.Add("TestExport");
var exportFields = new List<string>();
foreach(var selectedField in model.SelectedFields)
{
// Adds selected fields to [exportFields] List<string>
exportFields.Add(model.ListOfExportFields.First(s => s.Key == selectedField).Value);
}
//int cnt = 0;
//foreach(var column in exportFields)
for (int cnt = 0; cnt < 10; cnt++ )
{
DynamicColumnsCollection.Add(new DynamicColumns()
{
Id = cnt,
ip_address = "ip_address" + cnt,
mac_address = "mac_address" + cnt,
note = "note" + cnt,
owner = "owner" + cnt,
cost = "cost" + cnt,
po_number = "po_number" + cnt,
description = "description" + cnt,
invoice_number = "invoice_number" + cnt,
serial_number = "serial_number" + cnt,
asset_tag_number = "asset_tag_number" + cnt,
acquired_date = "acquired_date" + cnt,
disposed_date = "disposed_date" + cnt,
verified_date = "verified_date" + cnt,
created_date = "created_date" + cnt,
created_by = "created_by" + cnt,
modified_date = "modified_date" + cnt,
modified_by = "modified_by" + cnt
});
}
//var selectStatement = DynamicSelectionColumns(exportFields);
IQueryable collection = DynamicSelectionColumns(new List<string>() {
"id",
"owner",
"note"
});
// Loops to insert column headings into Row 1 of Excel
for (int i = 0; i < exportFields.Count(); i++ )
{
ws.Cells[1, i + 1].Value = exportFields[i].ToString();
}
// Process data from [collectin] into Excel???
ws.Cells["A2"].LoadFromCollection(collection.ToString());
// ws.Cells["A2"].LoadFromCollection(selectStatement.ToString());
var memoryStream = new MemoryStream();
package.SaveAs(memoryStream);
string fileName = "Exported-InventoryAssets-" + DateTime.Now + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
memoryStream.Position = 0;
return File(memoryStream, contentType, fileName);
}
public IQueryable DynamicSelectionColumns(List<string> fieldsForExport)
{
using (var db = new InventoryTrackerContext())
{
string fieldIds = "," + "4,5,3,2,6,17,11,12" + ",";
//var taskColum = Enum.GetValues(typeof(EnumTasks)).Cast<EnumTasks>().Where(e => fieldIds.Contains("," + ((int)e).ToString() + ",")).Select(e => e.ToString().Replace("_", ""));
var taskColum = Enum.GetValues(typeof(EnumTasks)).Cast<EnumTasks>().Where(e => fieldIds.Contains("," + ((int)e).ToString() + ",")).Select(e => e.ToString());
////string select = "new ( TaskId, " + (taskColum.Count() > 0 ? string.Join(", ", taskColum) + ", " : "") + "Id )";
//string select = "new ( " + string.Join(", ", fieldsForExport) + ")";
////return db.INV_Assets.ToList().Select(t => new DynamicColumns() { Id = t.Id, TaskId = Project != null ? Project.Alias + "-" + t.Id : t.Id.ToString(),
if (!fieldsForExport.Any())
{
return null;
}
string select = string.Format("new ( {0} )", string.Join(", ", fieldsForExport.ToArray()));
var collection = DynamicColumnsCollection.Select(t => new DynamicColumns()
{
Id = t.Id,
//Manufacturer = Convert.ToString(t.Manufacturer.manufacturer_description),
//Type = t.Type.type_description,
//Location = t.Location.location_room,
//Vendor = t.Vendor.vendor_name,
//Status = t.Status.status_description,
ip_address = t.ip_address,
mac_address = t.mac_address,
note = t.note,
owner = t.owner,
cost = t.cost,
po_number = t.po_number,
description = t.description,
invoice_number = t.invoice_number,
serial_number = t.serial_number,
asset_tag_number = t.asset_tag_number,
acquired_date = t.acquired_date,
disposed_date = t.disposed_date,
verified_date = t.verified_date,
created_date = t.created_date,
created_by = t.created_by,
modified_date = t.modified_date,
modified_by = t.modified_by
}).ToList().AsQueryable().Select(select);
return collection;
}
}
}
public class DynamicColumns : INV_Assets
{
public string Model { get; set; }
public string Manufacturer { get; set; }
public string Type { get; set; }
public string Location { get; set; }
public string Vendor { get; set; }
public string Status { get; set; }
public string ip_address { get; set; }
public string mac_address { get; set; }
public string note { get; set; }
public string owner { get; set; }
public string cost { get; set; }
public string po_number { get; set; }
public string description { get; set; }
public string invoice_number { get; set; }
public string serial_number { get; set; }
public string asset_tag_number { get; set; }
public string acquired_date { get; set; }
public string disposed_date { get; set; }
public string verified_date { get; set; }
public string created_date { get; set; }
public string created_by { get; set; }
public string modified_date { get; set; }
public string modified_by { get; set; }
}
public enum EnumTasks
{
Model = 1,
Manufacturer = 2,
Type = 3,
Location = 4,
Vendor = 5,
Status = 6,
ip_address = 7,
mac_address = 8,
note = 9,
owner = 10,
cost = 11,
po_number = 12,
description = 13,
invoice_number = 14,
serial_number = 15,
asset_tag_number = 16,
acquired_date = 17,
disposed_date = 18,
verified_date = 19,
created_date = 20,
created_by = 21,
modified_date = 22,
modified_by = 23
}
//https://stackoverflow.com/questions/5796151/export-model-data-to-excel-mvc
//https://landokal.wordpress.com/2011/04/28/asp-net-mvc-export-to-excel-trick/
}
代码正在将我所选的列值从我的视图上的MultiSelectList导出到 Excel 电子表格的第 1 行，但我的动态 linq 查询有些问题，因为无论我为输出指定多少字段，获得输出的数据都只是A2:A180中0的值。
任何有更多经验或使用过System.Linq.Dynamic的人都可以对此发表意见吗？
这是我使用一段时间的东西：
public class ExcelExportHelper
{
public static string ExcelContentType { get { return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; } }
public static DataTable ToDataTable<T>(List<T> data)
{
PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor prop = props[i];
//table.Columns.Add(prop.Name, prop.PropertyType);
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType); // to avoid nullable types
}
object[] values = new object[props.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
return table;
}
public static byte[] ExportExcel(DataTable dt, string Heading = "", params string[] IgnoredColumns)
{
byte[] result = null;
using (ExcelPackage pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Exported Data");
int StartFromRow = String.IsNullOrEmpty(Heading) ? 1: 3;
// add the content into the Excel file
ws.Cells["A" + StartFromRow].LoadFromDataTable(dt, true);
// autofit width of cells with small content
int colindex = 1;
foreach (DataColumn col in dt.Columns)
{
ExcelRange columnCells = ws.Cells[ws.Dimension.Start.Row, colindex, ws.Dimension.End.Row, colindex];
int maxLength = columnCells.Max(cell => cell.Value.ToString().Count());
if (maxLength < 150)
ws.Column(colindex).AutoFit();
colindex++;
}
// format header - bold, yellow on black
using (ExcelRange r = ws.Cells[StartFromRow, 1, StartFromRow, dt.Columns.Count])
{
r.Style.Font.Color.SetColor(System.Drawing.Color.Yellow);
r.Style.Font.Bold = true;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Black);
}
// format cells - add borders
using (ExcelRange r = ws.Cells[StartFromRow + 1, 1, StartFromRow + dt.Rows.Count, dt.Columns.Count])
{
r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
}
// removed ignored columns
for (int i = dt.Columns.Count - 1; i >= 0; i--)
{
if (IgnoredColumns.Contains(dt.Columns[i].ColumnName))
{
ws.DeleteColumn(i + 1);
}
}
// add header and an additional column (left) and row (top)
if (!String.IsNullOrEmpty(Heading))
{
ws.Cells["A1"].Value = Heading;
ws.Cells["A1"].Style.Font.Size = 20;
ws.InsertColumn(1, 1);
ws.InsertRow(1, 1);
ws.Column(1).Width = 5;
}
result = pck.GetAsByteArray();
}
return result;
}
public static byte[] ExportExcel<T>(List<T> data, string Heading = "", params string[] IgnoredColumns)
{
return ExportExcel(ToDataTable<T>(data), Heading, IgnoredColumns);
}
}
有了这些，您可以将对象列表或数据表导出到 Excel 文件。此外，您可以指定要忽略的标题和列/属性（如 ID ）。此外，还添加了一些格式，并且小单元格将自动适应宽度。
使用此代码，您可以在需要FileResult的控制器操作中使用它：
byte[] filecontent = ExcelExportHelper.ExportExcel(...);
return File(filecontent, ExcelExportHelper.ExcelContentType, "FileName.xlsx");
我希望这有帮助！