public void ExcelImportWithFormula(ExcelFormat excelFormat)
{
var setting = FluentSettings.For <ExcelFormulaTestModel>();
setting.HasSheetConfiguration(0, "Test", 0);
setting.Property(x => x.Num1).HasColumnIndex(0);
setting.Property(x => x.Num2).HasColumnIndex(1);
setting.Property(x => x.Sum).HasColumnIndex(2);
var workbook = ExcelHelper.PrepareWorkbook(excelFormat);
var sheet = workbook.CreateSheet();
var row = sheet.CreateRow(0);
row.CreateCell(0, CellType.Numeric).SetCellValue(1);
row.CreateCell(1, CellType.Numeric).SetCellValue(2);
row.CreateCell(2, CellType.Formula).SetCellFormula("$A1+$B1");
var excelBytes = workbook.ToExcelBytes();
var list = ExcelHelper.ToEntityList <ExcelFormulaTestModel>(excelBytes, excelFormat);
Assert.NotNull(list);
Assert.NotEmpty(list);
Assert.NotNull(list[0]);
Assert.Equal(1, list[0] !.Num1);
Assert.Equal(2, list[0] !.Num2);
Assert.Equal(3, list[0] !.Sum);
}
public void Configure()
{
// ---------- notice fluent excel settings ----------------
var noticeSetting = FluentSettings.For <Notice>();
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());
}
private static void SheetNameTest()
{
List <ExcelExportDTO> exprotDataList = new List <ExcelExportDTO>();
for (int i = 0; i < 10; i++)
{
var temp = new ExcelExportDTO
{
Name = "张三" + i,
Address = "北京海淀" + i,
Birthday = DateTime.Now,
Remark = "Remark" + i
};
exprotDataList.Add(temp);
}
var setting = FluentSettings.For <ExcelExportDTO>();
setting.HasSheetConfiguration(1, "我是一个Sheet_111", true);
setting.HasSheetSetting(s =>
{
s.SheetName = "Shee-0000";
});
var deskTopFullPath = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
var exportFileName = Path.Combine(deskTopFullPath, "Test_for_weihanli.xlsx");
exprotDataList.ToExcelFile(exportFileName);
}
public void SheetNameTest_ToExcelBytes(ExcelFormat excelFormat)
{
IReadOnlyList <Notice> list = Enumerable.Range(0, 10).Select(i => new Notice()
{
Id = i + 1,
Content = $"content_{i}",
Title = $"title_{i}",
PublishedAt = DateTime.UtcNow.AddDays(-i),
Publisher = $"publisher_{i}"
}).ToArray();
var settings = FluentSettings.For <Notice>();
lock (settings)
{
settings.HasSheetSetting(s =>
{
s.SheetName = "Test";
});
var excelBytes = list.ToExcelBytes(excelFormat);
var excel = ExcelHelper.LoadExcel(excelBytes, excelFormat);
Assert.Equal("Test", excel.GetSheetAt(0).SheetName);
settings.HasSheetSetting(s =>
{
s.SheetName = "NoticeList";
});
}
}
private static void FluentSettingsForExcel()
{
var setting = FluentSettings.For <TestEntity>();
// 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;
});
// 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();
}
public void BasicImportExportWithoutHeaderTest(ExcelFormat excelFormat)
{
var list = new List <Notice?>();
for (var i = 0; i < 10; i++)
{
list.Add(new Notice()
{
Id = i + 1,
Content = $"content_{i}",
Title = $"title_{i}",
PublishedAt = DateTime.UtcNow.AddDays(-i),
Publisher = $"publisher_{i}"
});
}
list.Add(new Notice()
{
Title = "nnnn"
});
list.Add(null);
var noticeSetting = FluentSettings.For <Notice>();
lock (noticeSetting)
{
noticeSetting.HasSheetConfiguration(0, "test", 0);
var excelBytes = list.ToExcelBytes(excelFormat);
var importedList = ExcelHelper.ToEntityList <Notice>(excelBytes, excelFormat);
Assert.Equal(list.Count, importedList.Count);
for (var i = 0; i < list.Count; i++)
{
if (list[i] == null)
{
Assert.Null(importedList[i]);
}
else
{
Assert.NotNull(importedList[i]);
var sourceItem = list[i] !;
var item = importedList[i] !;
Assert.Equal(sourceItem.Id, item.Id);
Assert.Equal(sourceItem.Title, item.Title);
Assert.Equal(sourceItem.Content, item.Content);
Assert.Equal(sourceItem.Publisher, item.Publisher);
Assert.Equal(sourceItem.PublishedAt.ToStandardTimeString(), item.PublishedAt.ToStandardTimeString());
}
}
noticeSetting.HasSheetConfiguration(0, "test", 1);
}
}
public void ImportWithNotSpecificColumnIndex(ExcelFormat excelFormat)
{
IReadOnlyList <Notice> list = Enumerable.Range(0, 10).Select(i => new Notice()
{
Id = i + 1,
Content = $"content_{i}",
Title = $"title_{i}",
PublishedAt = DateTime.UtcNow.AddDays(-i),
Publisher = $"publisher_{i}"
}).ToArray();
//
var noticeSetting = FluentSettings.For <Notice>();
lock (noticeSetting)
{
var excelBytes = list.ToExcelBytes(excelFormat);
noticeSetting.Property(_ => _.Publisher)
.HasColumnIndex(4);
noticeSetting.Property(_ => _.PublishedAt)
.HasColumnIndex(3);
var importedList = ExcelHelper.ToEntityList <Notice>(excelBytes, excelFormat);
Assert.Equal(list.Count, importedList.Count);
for (var i = 0; i < list.Count; i++)
{
if (list[i] == null)
{
Assert.Null(importedList[i]);
}
else
{
Assert.NotNull(importedList[i]);
var sourceItem = list[i] !;
var item = importedList[i] !;
Assert.Equal(sourceItem.Id, item.Id);
Assert.Equal(sourceItem.Title, item.Title);
Assert.Equal(sourceItem.Content, item.Content);
Assert.Equal(sourceItem.Publisher, item.Publisher);
Assert.Equal(sourceItem.PublishedAt.ToStandardTimeString(), item.PublishedAt.ToStandardTimeString());
}
}
noticeSetting.Property(_ => _.Publisher)
.HasColumnIndex(3);
noticeSetting.Property(_ => _.PublishedAt)
.HasColumnIndex(4);
}
}
public void DataValidationTest(ExcelFormat excelFormat)
{
IReadOnlyList <Notice> list = Enumerable.Range(0, 10).Select(i => new Notice()
{
Id = i + 1,
Content = $"content_{i}",
Title = $"title_{i}",
PublishedAt = DateTime.UtcNow.AddDays(-i),
Publisher = $"publisher_{i}"
}).ToArray();
var excelBytes = list.ToExcelBytes(excelFormat);
var settings = FluentSettings.For <Notice>();
lock (settings)
{
settings.WithDataValidation(x => x?.Id > 5);
var importedList = ExcelHelper.ToEntityList <Notice>(excelBytes, excelFormat);
Assert.Equal(list.Count(x => x.Id > 5), importedList.Count);
int i = 0, k = 0;
while (list[k].Id != importedList[i]?.Id)
{
k++;
}
for (; i < importedList.Count; i++, k++)
{
if (list[k] == null)
{
Assert.Null(importedList[i]);
}
else
{
Assert.NotNull(importedList[i]);
var sourceItem = list[k] !;
var item = importedList[i] !;
Assert.Equal(sourceItem.Id, item.Id);
Assert.Equal(sourceItem.Title, item.Title);
Assert.Equal(sourceItem.Content, item.Content);
Assert.Equal(sourceItem.Publisher, item.Publisher);
Assert.Equal(sourceItem.PublishedAt.ToStandardTimeString(), item.PublishedAt.ToStandardTimeString());
}
}
settings.WithDataValidation(null);
}
}
public void ExcelImportWithCellFilter(ExcelFormat excelFormat)
{
IReadOnlyList <Notice> list = Enumerable.Range(0, 10).Select(i => new Notice()
{
Id = i + 1,
Content = $"content_{i}",
Title = $"title_{i}",
PublishedAt = DateTime.UtcNow.AddDays(-i),
Publisher = $"publisher_{i}"
}).ToArray();
var excelBytes = list.ToExcelBytes(excelFormat);
var settings = FluentSettings.For <Notice>();
lock (settings)
{
settings.HasSheetSetting(setting =>
{
setting.CellFilter = cell => cell.ColumnIndex == 0;
});
var importedList = ExcelHelper.ToEntityList <Notice>(excelBytes, excelFormat);
Assert.Equal(list.Count, importedList.Count);
for (var i = 0; i < list.Count; i++)
{
if (list[i] == null)
{
Assert.Null(importedList[i]);
}
else
{
Assert.NotNull(importedList[i]);
var item = importedList[i] !;
Assert.Equal(list[i].Id, item.Id);
Assert.Null(item.Title);
Assert.Null(item.Content);
Assert.Null(item.Publisher);
Assert.Equal(default(DateTime).ToStandardTimeString(), item.PublishedAt.ToStandardTimeString());
}
}
settings.HasSheetSetting(setting =>
{
setting.CellFilter = _ => true;
});
}
}
public void InputOutputColumnFormatterTest(ExcelFormat excelFormat)
{
IReadOnlyList <Notice> list = Enumerable.Range(0, 10).Select(i => new Notice()
{
Id = i + 1,
Content = $"content_{i}",
Title = $"title_{i}",
PublishedAt = DateTime.UtcNow.AddDays(-i),
Publisher = $"publisher_{i}"
}).ToArray();
var settings = FluentSettings.For <Notice>();
lock (settings)
{
settings.Property(x => x.Id)
.HasColumnOutputFormatter(x => $"{x}_Test")
.HasColumnInputFormatter(x => Convert.ToInt32(x?.Split(new[] { '_' }, StringSplitOptions.RemoveEmptyEntries)[0]));
var excelBytes = list.ToExcelBytes(excelFormat);
var importedList = ExcelHelper.ToEntityList <Notice>(excelBytes, excelFormat);
Assert.Equal(list.Count, importedList.Count);
for (var i = 0; i < list.Count; i++)
{
if (list[i] == null)
{
Assert.Null(importedList[i]);
}
else
{
Assert.NotNull(importedList[i]);
var sourceItem = list[i] !;
var item = importedList[i] !;
Assert.Equal(sourceItem.Id, item.Id);
Assert.Equal(sourceItem.Title, item.Title);
Assert.Equal(sourceItem.Content, item.Content);
Assert.Equal(sourceItem.Publisher, item.Publisher);
Assert.Equal(sourceItem.PublishedAt.ToStandardTimeString(), item.PublishedAt.ToStandardTimeString());
}
}
settings.Property(x => x.Id)
.HasColumnOutputFormatter(null)
.HasColumnInputFormatter(null);
}
}
public void IgnoreInheritPropertyTest(ExcelFormat excelFormat)
{
IReadOnlyList <Notice> list = Enumerable.Range(0, 10).Select(i => new Notice()
{
Id = i + 1,
Content = $"content_{i}",
Title = $"title_{i}",
PublishedAt = DateTime.UtcNow.AddDays(-i),
Publisher = $"publisher_{i}"
}).ToArray();
var settings = FluentSettings.For <Notice>();
lock (settings)
{
settings.Property(x => x.Id).Ignored();
var excelBytes = list.ToExcelBytes(excelFormat);
// list.ToExcelFile($"{Directory.GetCurrentDirectory()}/ttt.xls");
var importedList = ExcelHelper.ToEntityList <Notice>(excelBytes, excelFormat);
Assert.Equal(list.Count, importedList.Count);
for (var i = 0; i < list.Count; i++)
{
if (list[i] == null)
{
Assert.Null(importedList[i]);
}
else
{
Assert.NotNull(importedList[i]);
var sourceItem = list[i] !;
var item = importedList[i] !;
//Assert.Equal(sourceItem.Id, item.Id);
Assert.Equal(sourceItem.Title, item.Title);
Assert.Equal(sourceItem.Content, item.Content);
Assert.Equal(sourceItem.Publisher, item.Publisher);
Assert.Equal(sourceItem.PublishedAt.ToStandardTimeString(), item.PublishedAt.ToStandardTimeString());
}
}
settings.Property(_ => _.Id)
.Ignored(false)
.HasColumnIndex(0);
}
}
public void BasicImportExportTestWithEmptyValue(ExcelFormat excelFormat)
{
var list = new List <Notice>();
for (var i = 0; i < 10; i++)
{
list.Add(new Notice()
{
Id = i + 1,
Content = i < 3 ? $"content_{i}" : string.Empty,
Title = $"title_{i}",
PublishedAt = DateTime.UtcNow.AddDays(-i),
Publisher = i < 3 ? $"publisher_{i}" : null
});
}
list.Add(new Notice()
{
Title = "nnnn"
});
list.Add(null);
var noticeSetting = FluentSettings.For <Notice>();
lock (noticeSetting)
{
var excelBytes = list.ToExcelBytes(excelFormat);
var importedList = ExcelHelper.ToEntityList <Notice>(excelBytes, excelFormat);
Assert.Equal(list.Count, importedList.Count);
for (var i = 0; i < list.Count; i++)
{
if (list[i] == null)
{
Assert.Null(importedList[i]);
}
else
{
Assert.Equal(list[i].Id, importedList[i].Id);
Assert.Equal(list[i].Title, importedList[i].Title);
Assert.Equal(list[i].Content, importedList[i].Content);
Assert.Equal(list[i].Publisher, importedList[i].Publisher);
Assert.Equal(list[i].PublishedAt.ToStandardTimeString(), importedList[i].PublishedAt.ToStandardTimeString());
}
}
}
}
public void ShadowPropertyTest(ExcelFormat excelFormat)
{
IReadOnlyList <Notice> list = Enumerable.Range(0, 10).Select(i => new Notice()
{
Id = i + 1,
Content = $"content_{i}",
Title = $"title_{i}",
PublishedAt = DateTime.UtcNow.AddDays(-i),
Publisher = $"publisher_{i}"
}).ToArray();
var noticeSetting = FluentSettings.For <Notice>();
lock (noticeSetting)
{
noticeSetting.Property <string>("ShadowProperty")
.HasOutputFormatter((x, _) => $"{x?.Id}...")
;
var excelBytes = list.ToExcelBytes(excelFormat);
// list.ToExcelFile($"{Directory.GetCurrentDirectory()}/output.xlsx");
var importedList = ExcelHelper.ToEntityList <Notice>(excelBytes, excelFormat);
Assert.Equal(list.Count, importedList.Count);
for (var i = 0; i < list.Count; i++)
{
if (list[i] == null)
{
Assert.Null(importedList[i]);
}
else
{
Assert.NotNull(importedList[i]);
var sourceItem = list[i] !;
var item = importedList[i] !;
Assert.Equal(sourceItem.Id, item.Id);
Assert.Equal(sourceItem.Title, item.Title);
Assert.Equal(sourceItem.Content, item.Content);
Assert.Equal(sourceItem.Publisher, item.Publisher);
Assert.Equal(sourceItem.PublishedAt.ToStandardTimeString(), item.PublishedAt.ToStandardTimeString());
}
}
}
}
private void ExcelSettings()
{
var settings = FluentSettings.For <ReservationListViewModel>();
settings
.HasAuthor("WeihanLi")
.HasTitle("活动室预约信息")
.HasDescription("活动室预约信息")
.HasSheetConfiguration(0, "活动室预约信息", true)
;
settings.Property(r => r.ReservationId).Ignored();
settings.Property(r => r.ReservationPlaceName)
.HasColumnTitle("活动室名称")
.HasColumnIndex(0);
settings.Property(r => r.ReservationForDate)
.HasColumnTitle("预约使用日期")
.HasColumnIndex(1);
settings.Property(r => r.ReservationForTime)
.HasColumnTitle("预约使用的时间段")
.HasColumnIndex(2);
settings.Property(r => r.ReservationUnit)
.HasColumnTitle("预约单位")
.HasColumnIndex(3);
settings.Property(r => r.ReservationActivityContent)
.HasColumnTitle("预约活动内容")
.HasColumnIndex(4);
settings.Property(r => r.ReservationPersonName)
.HasColumnTitle("预约人姓名")
.HasColumnIndex(5);
settings.Property(r => r.ReservationPersonPhone)
.HasColumnTitle("预约人手机号")
.HasColumnIndex(6);
settings.Property(r => r.ReservationTime)
.HasColumnTitle("预约时间")
.HasColumnFormatter("yyyy-MM-dd HH:mm:ss")
.HasColumnIndex(7);
settings.Property(r => r.ReservationStatus)
.HasColumnTitle("审核状态")
.HasColumnOutputFormatter(status => status.GetDescription())
.HasColumnIndex(8);
}
public void ColumnInputFormatterTest(ExcelFormat excelFormat)
{
IReadOnlyList <Notice> list = Enumerable.Range(0, 10).Select(i => new Notice()
{
Id = i + 1,
Content = $"content_{i}",
Title = $"title_{i}",
PublishedAt = DateTime.UtcNow.AddDays(-i),
Publisher = $"publisher_{i}"
}).ToArray();
var excelBytes = list.ToExcelBytes(excelFormat);
var settings = FluentSettings.For <Notice>();
lock (settings)
{
settings.Property(x => x.Title).HasColumnInputFormatter(x => $"{x}_Test");
var importedList = ExcelHelper.ToEntityList <Notice>(excelBytes, excelFormat);
Assert.Equal(list.Count, importedList.Count);
for (var i = 0; i < list.Count; i++)
{
if (list[i] == null)
{
Assert.Null(importedList[i]);
}
else
{
Assert.NotNull(importedList[i]);
var item = importedList[i] !;
Assert.Equal(list[i].Id, item.Id);
Assert.Equal(list[i].Title + "_Test", item.Title);
Assert.Equal(list[i].Content, item.Content);
Assert.Equal(list[i].Publisher, item.Publisher);
Assert.Equal(list[i].PublishedAt.ToStandardTimeString(), item.PublishedAt.ToStandardTimeString());
}
}
settings.Property(_ => _.Title).HasColumnInputFormatter(null);
}
}
public void BasicImportExportTest()
{
var list = new List <Notice>();
for (var i = 0; i < 10; i++)
{
list.Add(new Notice()
{
Id = i + 1,
Content = $"content_{i}",
Title = $"title_{i}",
PublishedAt = DateTime.UtcNow.AddDays(-i),
Publisher = $"publisher_{i}"
});
}
list.Add(new Notice()
{
Id = 11,
Content = $"content",
Title = $"title",
PublishedAt = DateTime.UtcNow.AddDays(1),
});
var noticeSetting = FluentSettings.For <Notice>();
lock (noticeSetting)
{
var csvBytes = list.ToCsvBytes();
var importedList = CsvHelper.ToEntityList <Notice>(csvBytes);
Assert.Equal(list.Count, importedList.Count);
for (var i = 0; i < list.Count; i++)
{
Assert.NotNull(importedList[i]);
var item = importedList[i] !;
Assert.Equal(list[i].Id, item.Id);
Assert.Equal(list[i].Title ?? "", item.Title);
Assert.Equal(list[i].Content ?? "", item.Content);
Assert.Equal(list[i].Publisher ?? "", item.Publisher);
Assert.Equal(list[i].PublishedAt.ToStandardTimeString(), item.PublishedAt.ToStandardTimeString());
}
}
}
private void FluentSettingsConfigure()
{
// ---------- notice npoi settings ----------------
var noticeSetting = FluentSettings.For <Notice>();
noticeSetting.HasAuthor("WeihanLi")
.HasTitle("WeihanLi.Npoi test")
.HasSheetConfiguration(0, "NoticeList", 1)
;
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)
.HasOutputFormatter((entity, x) => x.ToStandardTimeString());
}
public void ImportWithNotSpecificColumnIndex()
{
IReadOnlyList <Notice> list = Enumerable.Range(0, 10).Select(i => new Notice()
{
Id = i + 1,
Content = $"content_{i}",
Title = $"title_{i}",
PublishedAt = DateTime.UtcNow.AddDays(-i),
Publisher = $"publisher_{i}"
}).ToArray();
//
var noticeSetting = FluentSettings.For <Notice>();
lock (noticeSetting)
{
var excelBytes = list.ToCsvBytes();
noticeSetting.Property(_ => _.Publisher)
.HasColumnIndex(4);
noticeSetting.Property(_ => _.PublishedAt)
.HasColumnIndex(3);
var importedList = CsvHelper.ToEntityList <Notice>(excelBytes);
Assert.Equal(list.Count, importedList.Count);
for (var i = 0; i < list.Count; i++)
{
Assert.Equal(list[i].Id, importedList[i].Id);
Assert.Equal(list[i].Title ?? "", importedList[i].Title);
Assert.Equal(list[i].Content ?? "", importedList[i].Content);
Assert.Equal(list[i].Publisher ?? "", importedList[i].Publisher);
Assert.Equal(list[i].PublishedAt.ToStandardTimeString(), importedList[i].PublishedAt.ToStandardTimeString());
}
noticeSetting.Property(_ => _.Publisher)
.HasColumnIndex(3);
noticeSetting.Property(_ => _.PublishedAt)
.HasColumnIndex(4);
}
}
private static void FluentSettingsForExcel()
{
var setting = FluentSettings.For <TestEntity>();
// 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();
}
public void Configure()
{
// ---------- load excel mapping profiles ----------------
FluentSettings.LoadMappingProfiles(typeof(NoticeProfile).Assembly);
}
public static void Main(string[] args)
{
LogHelper.ConfigureLogging(x => x.WithMinimumLevel(LogHelperLogLevel.Info).AddConsole());
SheetNameTest();
FluentSettings.LoadMappingProfile <TestEntity, TestEntityExcelMappingProfile>();
var tempDirPath = $@"{Environment.GetEnvironmentVariable("USERPROFILE")}\Desktop\temp\test";
// image export/import test
//var imageExcelPath = @"C:\Users\Weiha\Desktop\temp\test\imageTest.xls";
//var imgaeModelList = ExcelHelper.ToEntityList<ImportImageTestModel>(imageExcelPath);
//Console.WriteLine(imgaeModelList.Count(x => x?.Image != null));
//imgaeModelList.ToExcelFile(imageExcelPath + ".1.xls");
//var imgModeList2 = ExcelHelper.ToEntityList<ImportImageTestModel>(imageExcelPath + ".1.xls");
//Console.WriteLine($"{imgaeModelList[0]?.Image?.Length},{imgModeList2[0]?.Image?.Length}");
//imgaeModelList.ToExcelFile(imageExcelPath + ".1.xlsx");
//Console.ReadLine();
//FluentSettings.For<ppDto>()
// .HasSheetSetting(sheet =>
// {
// sheet.CellFilter = cell => cell.ColumnIndex <= 10;
// });
//var tempExcelPath = Path.Combine(tempDirPath, "testdata.xlsx");
//var t_list = ExcelHelper.ToEntityList<ppDto>(tempExcelPath);
//var tempTable = ExcelHelper.ToDataTable(tempExcelPath);
//using (var conn = new SqlConnection("server=.;uid=liweihan;pwd=Admin888;database=Reservation"))
//{
// var list = conn.Select<TestEntity>(@"SELECT * FROM [Reservation].[dbo].[tabSystemSettings]").ToArray();
// list.ToExcelFile(ApplicationHelper.MapPath("test.xlsx"));
//}
//var entityList = ExcelHelper.ToEntityList<TestEntity>(ApplicationHelper.MapPath("test.xlsx"));
//Console.WriteLine("Success!");
//var mapping = ExcelHelper.ToEntityList<ProductPriceMapping>($@"{Environment.GetEnvironmentVariable("USERPROFILE")}\Desktop\temp\tempFiles\mapping.xlsx");
//var mappingTemp = ExcelHelper.ToEntityList<ProductPriceMapping>($@"{Environment.GetEnvironmentVariable("USERPROFILE")}\Desktop\temp\tempFiles\mapping_temp.xlsx");
//Console.WriteLine($"-----normal({mapping.Count}【{mapping.Select(_ => _.Pid).Distinct().Count()}】)----");
//foreach (var shop in mapping.GroupBy(_ => _.ShopCode).OrderBy(_ => _.Key))
//{
// Console.WriteLine($"{shop.Key}---{shop.Count()}---distinct pid count:{shop.Select(_ => _.Pid).Distinct().Count()}");
//}
//Console.WriteLine($"-----temp({mappingTemp.Count}【{mappingTemp.Select(_ => _.Pid).Distinct().Count()}】)----");
//foreach (var shop in mappingTemp.GroupBy(_ => _.ShopCode).OrderBy(_ => _.Key))
//{
// Console.WriteLine($"{shop.Key}---{shop.Count()}---distinct pid count:{shop.Select(_ => _.Pid).Distinct().Count()}");
//}
//Console.WriteLine("Press Enter to continue...");
//Console.ReadLine();
//var list2 = new List<TestEntity2?>();
//list2.Add(null);
//for (var i = 0; i < 100_000; i++)
//{
// list2.Add(new TestEntity2
// {
// Id = i + 1,
// Title = $"Title_{i}",
// Description = $"{Enumerable.Range(1, 200).StringJoin(",")}__{i}",
// });
//}
//list2.Add(new TestEntity2()
//{
// Id = 999,
// Title = $"{Enumerable.Repeat(1, 10).StringJoin(",")}",
// Description = null
//});
//var watch = Stopwatch.StartNew();
//list2.ToExcelFile($@"{tempDirPath}\testEntity2.xls");
//watch.Stop();
//Console.WriteLine($"ElapsedMilliseconds: {watch.ElapsedMilliseconds}ms");
////var listTemp = ExcelHelper.ToEntityList<TestEntity2>($@"{tempDirPath}\testEntity2.xlsx");
//var dataTableTemp = ExcelHelper.ToDataTable($@"{tempDirPath}\testEntity2.xlsx");
//Console.WriteLine("Press Enter to continue...");
//Console.ReadLine();
var entities = new List <TestEntity>()
{
new TestEntity()
{
PKID = 1,
SettingId = Guid.NewGuid(),
SettingName = "Setting1",
SettingValue = "Value1",
DisplayName = "dd\"d,1"
},
new TestEntity()
{
PKID = 2,
SettingId = Guid.NewGuid(),
SettingName = "Setting2",
SettingValue = "Value2",
Enabled = true,
CreatedBy = "li\"_"
},
};
var csvFilePath = $@"{tempDirPath}\test.csv";
//entities.ToExcelFileByTemplate(
// Path.Combine(ApplicationHelper.AppRoot, "Templates", "testTemplate.xlsx"),
// ApplicationHelper.MapPath("templateTestEntities.xlsx"),
// extraData: new
// {
// Author = "WeihanLi",
// Title = "Export Result"
// }
//);
entities.ToExcelFile(csvFilePath.Replace(".csv", ".xlsx"));
entities.ToCsvFile(csvFilePath);
var entitiesT0 = ExcelHelper.ToEntityList <TestEntity>(csvFilePath.Replace(".csv", ".xlsx"));
var dataTable = entities.ToDataTable();
dataTable.ToCsvFile(csvFilePath.Replace(".csv", ".datatable.csv"));
var dt = CsvHelper.ToDataTable(csvFilePath.Replace(".csv", ".datatable.csv"));
Console.WriteLine(dt.Columns.Count);
var entities1 = CsvHelper.ToEntityList <TestEntity>(csvFilePath);
entities1[1] !.DisplayName = ",tadadada";
entities1[0] !.SettingValue = "value2,345";
entities1.ToCsvFile(csvFilePath.Replace(".csv", ".1.csv"));
entities1.ToDataTable().ToCsvFile(csvFilePath.Replace(".csv", ".1.datatable.csv"));
var list = CsvHelper.ToEntityList <TestEntity>(csvFilePath.Replace(".csv", ".1.csv"));
dt = CsvHelper.ToDataTable(csvFilePath.Replace(".csv", ".1.datatable.csv"));
Console.WriteLine(dt.Columns.Count);
var entities2 = CsvHelper.ToEntityList <TestEntity>(csvFilePath.Replace(".csv", ".1.csv"));
entities.ToExcelFile(csvFilePath.Replace(".csv", ".xlsx"));
var vals = new[] { 1, 2, 3, 5, 4 };
vals.ToCsvFile(csvFilePath);
var numList = CsvHelper.ToEntityList <int>(csvFilePath);
Console.WriteLine(numList.StringJoin(","));
Console.ReadLine();
}
