using Furion;
|
using Furion.HttpRemote;
|
using MiniExcelLibs;
|
using MiniExcelLibs.Attributes;
|
using MiniExcelLibs.OpenXml;
|
using Newtonsoft.Json;
|
using System;
|
using System.Collections.Generic;
|
using System.IO;
|
using System.Linq;
|
using System.Reflection;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace FlexJobApi.Core
|
{
|
/// <summary>
|
/// Excel工具
|
/// </summary>
|
public static class ExcelUtils
|
{
|
public static async Task<List<T>> ImportExcelFromOSS<T>(this string url)
|
where T : class, new()
|
{
|
var config = new OpenXmlConfiguration();
|
var xmlDoc = await XmlDocUtils.GetXmlDocAsync();
|
config.DynamicColumns = typeof(T).GetProperties().Select(it =>
|
{
|
var column = new DynamicExcelColumn(it.Name)
|
{
|
Name = it.GetXmlDocMemberAsync(xmlDoc).Result.Summary,
|
Ignore = it.GetCustomAttribute<JsonIgnoreAttribute>() != null
|
};
|
return column;
|
}).ToArray();
|
url = AliyunOSSUtils.GetUrl(url);
|
var stream = await App.GetRequiredService<IHttpRemoteService>().GetAsStreamAsync(url);
|
|
var query = await MiniExcel.QueryAsync<T>(stream, configuration: config);
|
return query.ToList();
|
}
|
|
public static async Task<string> ExportExcelToOSS<T>(this List<T> models, string scene, string filename)
|
where T : class, new()
|
{
|
var xmlDoc = await XmlDocUtils.GetXmlDocAsync();
|
var config = new OpenXmlConfiguration();
|
config.TableStyles = TableStyles.None;
|
config.AutoFilter = false;
|
config.FastMode = true;
|
config.EnableAutoWidth = true;
|
config.StyleOptions = new OpenXmlStyleOptions
|
{
|
WrapCellContents = false
|
};
|
config.FreezeRowCount = 0;
|
|
config.DynamicColumns = typeof(T).GetProperties().Select(it =>
|
{
|
var propertyType = it.PropertyType;
|
if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
|
{
|
propertyType = propertyType.GetGenericArguments()[0];
|
}
|
var column = new DynamicExcelColumn(it.Name)
|
{
|
Name = it.GetXmlDocMemberAsync(xmlDoc).Result.Summary,
|
Ignore = it.GetCustomAttribute<JsonIgnoreAttribute>() != null,
|
CustomFormatter = v =>
|
{
|
if (propertyType.IsEnum)
|
{
|
var models = EnumUtils.GetModel(propertyType).Result;
|
var enumValue = (int)v;
|
var model = models.Items.FirstOrDefault(i => i.Value == enumValue);
|
return model?.Description;
|
}
|
return v;
|
},
|
};
|
if (propertyType == typeof(DateTime))
|
{
|
column.Format = "yyyy-MM-dd HH:mm:ss";
|
}
|
return column;
|
}).ToArray();
|
var stream = new MemoryStream();
|
await MiniExcel.SaveAsAsync(stream, models, configuration: config);
|
stream.Seek(0, SeekOrigin.Begin);
|
return AliyunOSSUtils.Upload(scene, stream, filename).Url;
|
}
|
}
|
}
|
