/// <summary>
/// Configure excel author
/// </summary>
/// <param name="configuration">excel configuration</param>
/// <param name="title">excel document title</param>
/// <returns>current excel configuration<see cref="IExcelConfiguration"/></returns>
public static IExcelConfiguration HasTitle(this IExcelConfiguration configuration, string title)
{
return(configuration.HasExcelSetting(setting =>
{
setting.Title = title;
}));
}
/// <summary>
/// Configure excel author
/// </summary>
/// <param name="configuration">excel configuration</param>
/// <param name="category">excel document category</param>
/// <returns>current excel configuration<see cref="IExcelConfiguration"/></returns>
public static IExcelConfiguration HasCategory(this IExcelConfiguration configuration, string category)
{
return(configuration.HasExcelSetting(setting =>
{
setting.Category = category;
}));
}
/// <summary>
/// Configure excel author
/// </summary>
/// <param name="configuration">excel configuration</param>
/// <param name="author">excel document author name</param>
/// <returns>current excel configuration<see cref="IExcelConfiguration"/></returns>
public static IExcelConfiguration HasAuthor(this IExcelConfiguration configuration, string author)
{
return(configuration.HasExcelSetting(setting =>
{
setting.Author = author;
}));
}
/// <summary>
/// Configure excel author
/// </summary>
/// <param name="configuration">excel configuration</param>
/// <param name="company">excel document company</param>
/// <returns>current excel configuration<see cref="IExcelConfiguration"/></returns>
public static IExcelConfiguration HasCompany(this IExcelConfiguration configuration, string company)
{
return(configuration.HasExcelSetting(setting =>
{
setting.Company = company;
}));
}
/// <summary>
/// Configure excel author
/// </summary>
/// <param name="configuration">excel configuration</param>
/// <param name="subject">excel document subject</param>
/// <returns>current excel configuration<see cref="IExcelConfiguration"/></returns>
public static IExcelConfiguration HasSubject(this IExcelConfiguration configuration, string subject)
{
return(configuration.HasExcelSetting(setting =>
{
setting.Subject = subject;
}));
}
/// <summary>
/// Configure excel author
/// </summary>
/// <param name="configuration">excel configuration</param>
/// <param name="description">excel document description</param>
/// <returns>current excel configuration<see cref="IExcelConfiguration"/></returns>
public static IExcelConfiguration HasDescription(this IExcelConfiguration configuration, string description)
{
return(configuration.HasExcelSetting(setting =>
{
setting.Description = description;
}));
}
/// <summary>
/// Sheet Configuration
/// </summary>
/// <param name="configuration">excel configuration</param>
/// <param name="sheetIndex">sheetIndex</param>
/// <param name="sheetName">sheetName</param>
/// <param name="startRowIndex">startRowIndex</param>
/// <param name="enableAutoColumnWidth">enable auto column width if true otherwise false</param>
/// <param name="endRowIndex">endRowIndex, set this if you wanna control where to end(included)</param>
/// <returns>current excel configuration<see cref="IExcelConfiguration"/></returns>
public static IExcelConfiguration HasSheetConfiguration(this IExcelConfiguration configuration, int sheetIndex, string sheetName, int startRowIndex,
bool enableAutoColumnWidth, int?endRowIndex = null) => configuration.HasSheetSetting(config =>
{
config.SheetName = sheetName;
config.StartRowIndex = startRowIndex;
config.AutoColumnWidthEnabled = enableAutoColumnWidth;
config.EndRowIndex = endRowIndex;
}, sheetIndex);
/// <summary>
/// Generic extension method yielding objects of specified type from the ExcelWorksheet
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="worksheet"></param>
/// <param name="onCaught"></param>
/// <param name="configurationAction"></param>
/// <returns></returns>
public static IEnumerable <T> AsEnumerable <T>(this ExcelWorksheet worksheet, OnCaught <T> onCaught = null, Action <IExcelConfiguration <T> > configurationAction = null) where T : class, new()
{
IExcelConfiguration <T> configuration = DefaultExcelConfiguration <T> .Instance;
configurationAction?.Invoke(configuration);
return(worksheet.AsExcelTable(configuration.HasHeaderRow).AsEnumerable(onCaught, configurationAction));
}
/// <summary>
/// Generic extension method yielding objects of specified type from table.
/// </summary>
/// <remarks>
/// Exceptions are not catched. It works on all or nothing basis.
/// Only primitives and enums are supported as property.
/// Currently supports only tables with header.
/// </remarks>
/// <typeparam name="T">Type to map to. Type should be a class and should have parameterless constructor.</typeparam>
/// <param name="table">Table object to fetch</param>
/// <param name="onCaught"></param>
/// <param name="configurationAction"></param>
/// <returns>An enumerable of the generating type</returns>
public static IEnumerable <T> AsEnumerable <T>(this ExcelTable table, OnCaught <T> onCaught = null, Action <IExcelConfiguration <T> > configurationAction = null) where T : class, new()
{
IExcelConfiguration <T> configuration = DefaultExcelConfiguration <T> .Instance;
configurationAction?.Invoke(configuration);
List <KeyValuePair <int, PropertyInfo> > mapping = PrepareMappings <T>(table);
ExcelAddress bounds = table.GetDataBounds();
// Parse table
for (int row = bounds.Start.Row; row <= bounds.End.Row; row++)
{
var item = (T)Activator.CreateInstance(typeof(T));
foreach (KeyValuePair <int, PropertyInfo> map in mapping)
{
object cell = table.WorkSheet.Cells[row, map.Key + table.Address.Start.Column].Value;
PropertyInfo property = map.Value;
try
{
TrySetProperty(item, property, cell);
}
catch (Exception ex)
{
if (!configuration.SkipCastingErrors)
{
var exceptionArgs = new ExcelTableExceptionArgs
{
ColumnName = table.Columns[map.Key].Name,
ExpectedType = property.PropertyType,
PropertyName = property.Name,
CellValue = cell,
CellAddress = new ExcelCellAddress(row, map.Key + table.Address.Start.Column)
};
throw new ExcelTableConvertException($"The expected type of '{exceptionArgs.PropertyName}' property is '{exceptionArgs.ExpectedType.Name}', but the cell [{exceptionArgs.CellAddress.Address}] contains an invalid value.",
ex, exceptionArgs
);
}
}
}
onCaught?.Invoke(item, row);
// TODO:
if (!configuration.SkipValidationErrors)
{
// Validate parsed object according to data annotations
item.Validate(row);
}
yield return(item);
}
}
/// <summary>
/// Adds a column mapping. If no column mappings are specified all public properties will be used
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="worksheet"></param>
/// <param name="map"></param>
/// <param name="columnHeader"></param>
/// <param name="configurationAction"></param>
/// <returns></returns>
public static WorksheetWrapper <T> WithColumn <T>(this WorksheetWrapper <T> worksheet, Func <T, object> map,
string columnHeader, Action <IExcelConfiguration <T> > configurationAction = null)
{
IExcelConfiguration <T> configuration = DefaultExcelConfiguration <T> .Instance;
configurationAction?.Invoke(configuration);
worksheet.Columns.Add(new WorksheetColumn <T>
{
Map = map,
ConfigureHeader = configuration.ConfigureHeader,
ConfigureColumn = configuration.ConfigureColumn,
Header = columnHeader,
ConfigureCell = configuration.ConfigureCell
});
return(worksheet);
}
/// <summary>
/// Generates an Excel worksheet from a list
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="rows"></param>
/// <param name="name"></param>
/// <param name="configurationAction"></param>
/// <returns></returns>
public static WorksheetWrapper <T> ToWorksheet <T>(this IEnumerable <T> rows, string name, Action <IExcelConfiguration <T> > configurationAction = null)
{
IExcelConfiguration <T> configuration = DefaultExcelConfiguration <T> .Instance;
configurationAction?.Invoke(configuration);
var worksheet = new WorksheetWrapper <T>
{
Name = name,
Package = new ExcelPackage(),
Rows = rows,
Columns = new List <WorksheetColumn <T> >(),
ConfigureHeader = configuration.ConfigureHeader,
ConfigureColumn = configuration.ConfigureColumn,
ConfigureHeaderRow = configuration.ConfigureHeaderRow,
ConfigureCell = configuration.ConfigureCell
};
return(worksheet);
}
/// <summary>
/// Starts new worksheet on same Excel package
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="K"></typeparam>
/// <param name="previousSheet"></param>
/// <param name="rows"></param>
/// <param name="name"></param>
/// <param name="configurationAction"></param>
/// <returns></returns>
public static WorksheetWrapper <T> NextWorksheet <T, K>(this WorksheetWrapper <K> previousSheet, IEnumerable <T> rows, string name, Action <IExcelConfiguration <T> > configurationAction = null)
{
previousSheet.AppendWorksheet();
IExcelConfiguration <T> configuration = DefaultExcelConfiguration <T> .Instance;
configurationAction?.Invoke(configuration);
var worksheet = new WorksheetWrapper <T>
{
Name = name,
Package = previousSheet.Package,
Rows = rows,
Columns = new List <WorksheetColumn <T> >(),
ConfigureHeader = configuration.ConfigureHeader ?? previousSheet.ConfigureHeader,
ConfigureColumn = configuration.ConfigureColumn ?? previousSheet.ConfigureColumn,
ConfigureHeaderRow = configuration.ConfigureHeaderRow ?? previousSheet.ConfigureHeaderRow,
ConfigureCell = configuration.ConfigureCell
};
return(worksheet);
}
public void Configure(IExcelConfiguration <Notice> noticeSetting)
{
noticeSetting
.HasAuthor("WeihanLi")
.HasTitle("WeihanLi.Npoi test")
.HasSheetSetting(setting =>
{
setting.SheetName = "NoticeList";
setting.AutoColumnWidthEnabled = true;
})
;
noticeSetting.Property(_ => _.Id)
.HasColumnIndex(0);
noticeSetting.Property(_ => _.Title)
.HasColumnIndex(1);
noticeSetting.Property(_ => _.Content)
.HasColumnIndex(2);
noticeSetting.Property(_ => _.Publisher)
.HasColumnIndex(3);
noticeSetting.Property(_ => _.PublishedAt)
.HasColumnIndex(4)
.HasColumnOutputFormatter(x => x.ToStandardTimeString());
}
/// <summary>
/// property configuration
/// </summary>
/// <typeparam name="TEntity">TEntity</typeparam>
/// <param name="excelConfiguration">excelConfiguration</param>
/// <param name="propertyName">propertyName</param>
/// <returns>PropertyConfiguration</returns>
public static IPropertyConfiguration <TEntity, string> Property <TEntity>(
this IExcelConfiguration <TEntity> excelConfiguration, string propertyName) =>
excelConfiguration.Property <string>(propertyName);
/// <summary>
/// Sheet Configuration
/// </summary>
/// <param name="configuration">excel configuration</param>
/// <param name="sheetIndex">sheetIndex</param>
/// <param name="sheetName">sheetName</param>
/// <param name="startRowIndex">startRowIndex</param>
/// <returns>current excel configuration</returns>
public static IExcelConfiguration HasSheetConfiguration(this IExcelConfiguration configuration, int sheetIndex,
string sheetName, int startRowIndex) => configuration.HasSheetSetting(config =>
{
config.SheetName = sheetName;
config.StartRowIndex = startRowIndex;
}, sheetIndex);
/// <summary>
/// Sheet Configuration
/// </summary>
/// <param name="configuration">excel configuration</param>
/// <param name="sheetIndex">sheetIndex</param>
/// <param name="sheetName">sheetName</param>
/// <param name="startRowIndex">startRowIndex</param>
/// <returns>current excel configuration</returns>
public static IExcelConfiguration HasSheetConfiguration(this IExcelConfiguration configuration, int sheetIndex,
string sheetName, int startRowIndex) => configuration.HasSheetConfiguration(sheetIndex, sheetName, startRowIndex, false);
/// <summary>
/// Sheet Configuration
/// </summary>
/// <param name="configuration">excel configuration</param>
/// <param name="sheetIndex">sheetIndex</param>
/// <param name="sheetName">sheetName</param>
/// <param name="enableAutoColumnWidth">enable auto column width if true otherwise false</param>
/// <returns>current excel configuration</returns>
public static IExcelConfiguration HasSheetConfiguration(this IExcelConfiguration configuration, int sheetIndex,
string sheetName, bool enableAutoColumnWidth) => configuration.HasSheetConfiguration(sheetIndex, sheetName, 1, enableAutoColumnWidth);
public void Configure(IExcelConfiguration <TestEntity> setting)
{
// ExcelSetting
setting.HasAuthor("WeihanLi")
.HasTitle("WeihanLi.Npoi test")
.HasDescription("WeihanLi.Npoi test")
.HasSubject("WeihanLi.Npoi test");
setting.HasSheetSetting(config =>
{
config.StartRowIndex = 1;
config.SheetName = "SystemSettingsList";
config.AutoColumnWidthEnabled = true;
config.RowAction = row =>
{
if (row.RowNum == 0)
{
var style = row.Sheet.Workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
var font = row.Sheet.Workbook.CreateFont();
font.FontName = "JetBrains Mono";
font.IsBold = true;
font.FontHeight = 200;
style.SetFont(font);
row.Cells.ForEach(c => c.CellStyle = style);
}
};
});
// setting.HasFilter(0, 1).HasFreezePane(0, 1, 2, 1);
setting.Property(_ => _.SettingId)
.HasColumnIndex(0);
setting.Property(_ => _.SettingName)
.HasColumnTitle("SettingName")
.HasColumnIndex(1);
setting.Property(_ => _.DisplayName)
.HasOutputFormatter((entity, displayName) => $"AAA_{entity?.SettingName}_{displayName}")
.HasInputFormatter((entity, originVal) => originVal?.Split(new[] { '_' })[2])
.HasColumnTitle("DisplayName")
.HasColumnIndex(2);
setting.Property(_ => _.SettingValue)
.HasColumnTitle("SettingValue")
.HasColumnIndex(3);
setting.Property(_ => _.CreatedTime)
.HasColumnTitle("CreatedTime")
.HasColumnIndex(4)
.HasColumnWidth(10)
.HasColumnFormatter("yyyy-MM-dd HH:mm:ss");
setting.Property(_ => _.CreatedBy)
.HasColumnInputFormatter(x => x += "_test")
.HasColumnIndex(4)
.HasColumnTitle("CreatedBy");
setting.Property(x => x.Enabled)
.HasColumnInputFormatter(val => "Enabled".EqualsIgnoreCase(val))
.HasColumnOutputFormatter(v => v ? "Enabled" : "Disabled");
setting.Property("HiddenProp")
.HasOutputFormatter((entity, val) => $"HiddenProp_{entity?.PKID}");
setting.Property(_ => _.PKID).Ignored();
setting.Property(_ => _.UpdatedBy).Ignored();
setting.Property(_ => _.UpdatedTime).Ignored();
}
/// <summary> /// 初始化 /// </summary> /// <param name="configuration">Excel配置</param> public abstract void Init(IExcelConfiguration <TEntity> configuration);
/// <summary>
/// Sheet Configuration
/// </summary>
/// <param name="configuration">excel configuration</param>
/// <param name="sheetIndex">sheetIndex</param>
/// <param name="sheetName">sheetName</param>
/// <param name="enableAutoColumnWidth">enable auto column width if true otherwise false</param>
/// <returns>current excel configuration</returns>
public static IExcelConfiguration HasSheetConfiguration(this IExcelConfiguration configuration, int sheetIndex,
string sheetName, bool enableAutoColumnWidth) => configuration.HasSheetSetting(config =>
{
config.SheetName = sheetName;
config.AutoColumnWidthEnabled = enableAutoColumnWidth;
}, sheetIndex);
