// Admin.NET 项目的版权、商标、专利和其他相关权利均受相应法律法规的保护。使用本项目应遵守相关法律法规和许可证的要求。
|
//
|
// 本项目主要遵循 MIT 许可证和 Apache 许可证（版本 2.0）进行分发和使用。许可证位于源代码树根目录中的 LICENSE-MIT 和 LICENSE-APACHE 文件。
|
//
|
// 不得利用本项目从事危害国家安全、扰乱社会秩序、侵犯他人合法权益等法律法规禁止的活动！任何基于本项目二次开发而产生的一切法律纠纷和责任，我们不承担任何责任！
|
|
using OfficeOpenXml;
|
|
namespace Admin.NET.Core;
|
|
public class ExcelHelper
|
{
|
/// <summary>
|
/// 数据导入
|
/// </summary>
|
/// <param name="file"></param>
|
/// <param name="action"></param>
|
/// <returns></returns>
|
public static IActionResult ImportData<IN, T>(IFormFile file, Action<List<IN>, Action<StorageableResult<T>, List<IN>, List<T>>> action) where IN : BaseImportInput, new() where T : EntityBaseId, new()
|
{
|
try
|
{
|
var result = CommonUtil.ImportExcelDataAsync<IN>(file).Result ?? throw Oops.Oh("有效数据为空");
|
result.ForEach(u => u.Id = YitIdHelper.NextId());
|
|
var tasks = new List<Task>();
|
action.Invoke(result, (storageable, pageItems, rows) =>
|
{
|
// 标记校验信息
|
tasks.Add(Task.Run(() =>
|
{
|
if (!storageable.TotalList.Any()) return;
|
|
// 通过Id标记校验信息
|
var itemMap = pageItems.ToDictionary(u => u.Id, u => u);
|
foreach (var item in storageable.TotalList)
|
{
|
var temp = itemMap.GetValueOrDefault(item.Item.Id);
|
if (temp != null) temp.Error ??= item.StorageMessage;
|
}
|
}));
|
});
|
|
// 等待所有标记验证信息任务完成
|
Task.WhenAll(tasks).GetAwaiter().GetResult();
|
|
// 仅导出错误记录
|
var errorList = result.Where(u => !string.IsNullOrWhiteSpace(u.Error)).ToList();
|
if (!errorList.Any())
|
return new JsonResult(AdminResultProvider.Ok("导入成功"));
|
return ExportData(errorList);
|
}
|
catch (Exception ex)
|
{
|
return new JsonResult(AdminResultProvider.Error(ex.Message));
|
}
|
}
|
|
/// <summary>
|
/// 导出Xlsx数据
|
/// </summary>
|
/// <param name="list"></param>
|
/// <param name="fileName"></param>
|
/// <returns></returns>
|
public static IActionResult ExportData(dynamic list, string fileName = "导入记录")
|
{
|
var exporter = new ExcelExporter();
|
var fs = new MemoryStream(exporter.ExportAsByteArray(list).GetAwaiter().GetResult());
|
return new XlsxFileResult(stream: fs, fileDownloadName: $"{fileName}-{DateTime.Now:yyyy-MM-dd_HHmmss}");
|
}
|
|
/// <summary>
|
/// 根据类型导出Xlsx模板
|
/// </summary>
|
/// <param name="list"></param>
|
/// <param name="filename"></param>
|
/// <param name="addListValidationFun"></param>
|
/// <returns></returns>
|
public static IActionResult ExportTemplate<T>(IEnumerable<T> list, string filename = "导入模板", Func<ExcelWorksheet, PropertyInfo, IEnumerable<string>> addListValidationFun = null)
|
{
|
using var package = new ExcelPackage((ExportData(list, filename) as XlsxFileResult)!.Stream);
|
var worksheet = package.Workbook.Worksheets[0];
|
|
// 创建一个隐藏的sheet，用于添加下拉列表
|
var dropdownSheet = package.Workbook.Worksheets.Add("下拉数据");
|
dropdownSheet.Hidden = eWorkSheetHidden.Hidden;
|
|
var sysDictTypeService = App.GetService<SysDictTypeService>();
|
foreach (var prop in typeof(T).GetProperties())
|
{
|
var propType = prop.PropertyType;
|
|
var headerAttr = prop.GetCustomAttribute<ExporterHeaderAttribute>();
|
var isNullableEnum = propType.IsGenericType && propType.GetGenericTypeDefinition() == typeof(Nullable<>) && Nullable.GetUnderlyingType(propType).IsEnum();
|
if (isNullableEnum) propType = Nullable.GetUnderlyingType(propType);
|
if (headerAttr == null) continue;
|
|
// 获取列序号
|
var columnIndex = 0;
|
foreach (var item in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])
|
if (++columnIndex > 0 && item.Text.Equals(headerAttr.DisplayName)) break;
|
if (columnIndex <= 0) continue;
|
|
// 优先从代理函数中获取下列列表，若为空且字段为枚举型，则填充枚举项为下列列表，若为字典字段，则填充字典值value列表为下列列表
|
var dataList = addListValidationFun?.Invoke(worksheet, prop)?.ToList();
|
if (dataList == null)
|
{
|
// 填充枚举项为下列列表
|
if (propType.IsEnum())
|
{
|
dataList = propType.EnumToList()?.Select(it => it.Describe).ToList();
|
}
|
else
|
{
|
// 获取字段上的字典特性
|
var dict = prop.GetCustomAttribute<DictAttribute>();
|
if (dict != null)
|
{
|
// 填充字典值value为下列列表
|
dataList = sysDictTypeService.GetDataList(new GetDataDictTypeInput { Code = dict.DictTypeCode })
|
.Result?.Select(x => x.Label).ToList();
|
}
|
}
|
}
|
|
if (dataList != null)
|
{
|
// 添加下拉列表
|
AddListValidation(dropdownSheet, columnIndex, dataList);
|
dropdownSheet.Cells[1, columnIndex, dataList.Count, columnIndex].LoadFromCollection(dataList);
|
}
|
}
|
|
package.Save();
|
package.Stream.Position = 0;
|
return new XlsxFileResult(stream: package.Stream, fileDownloadName: $"{filename}-{DateTime.Now:yyyy-MM-dd_HHmmss}");
|
|
void AddListValidation(ExcelWorksheet dropdownSheet, int columnIndex, List<string> dataList)
|
{
|
var validation = worksheet.DataValidations.AddListValidation(worksheet.Cells[2, columnIndex, ExcelPackage.MaxRows, columnIndex].Address);
|
validation!.Formula.ExcelFormula = "=" + dropdownSheet.Cells[1, columnIndex, dataList.Count, columnIndex].FullAddressAbsolute;
|
validation.ShowErrorMessage = true;
|
validation.ErrorTitle = "无效输入";
|
validation.Error = "请从列表中选择一个有效的选项";
|
}
|
}
|
}
|
