private static void ExportCsvPerfTest(int recordCount, int repeatTimes = 10)
{
if (recordCount <= 0)
{
recordCount = 100_000;
}
var testData = new List <TestEntity>(recordCount);
for (int i = 1; i <= recordCount; i++)
{
testData.Add(new TestEntity()
{
Amount = 1000,
Username = "******",
CreateTime = DateTime.UtcNow.AddDays(-3),
PKID = i,
});
}
var stopwatch = new Stopwatch();
var excelFilePath = $@"{Environment.GetEnvironmentVariable("USERPROFILE")}\Desktop\temp\test\test.fx.csv";
var elapsedList = new List <long>(repeatTimes);
for (int i = 0; i < repeatTimes; i++)
{
stopwatch.Restart();
testData.ToCsvFile(excelFilePath);
stopwatch.Stop();
elapsedList.Add(stopwatch.ElapsedMilliseconds);
Console.WriteLine($"{stopwatch.ElapsedMilliseconds} ms");
}
Console.WriteLine($"Average: {elapsedList.Average()} ms");
}
public static void Main(string[] args)
{
FluentSettingsForExcel();
//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>(@"C:\Users\liweihan\Desktop\temp\tempFiles\mapping.xlsx");
//var mappingTemp = ExcelHelper.ToEntityList<ProductPriceMapping>(@"C:\Users\liweihan\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()}");
//}
var entities = new List <TestEntity>()
{
new TestEntity()
{
SettingId = Guid.NewGuid(),
SettingName = "Setting1",
SettingValue = "Value1"
},
new TestEntity()
{
SettingId = Guid.NewGuid(),
SettingName = "Setting2",
SettingValue = "Value2"
},
};
var csvFilePath = @"C:\Users\liweihan\Desktop\temp\test\test.csv";
entities.ToCsvFile(csvFilePath);
var entities1 = CsvHelper.ToEntityList <TestEntity>(csvFilePath);
entities.ToExcelFile(csvFilePath.Replace(".csv", ".xlsx"));
Console.ReadLine();
}
public static void Main(string[] args)
{
FluentSettingsForExcel();
var tempDirPath = $@"{Environment.GetEnvironmentVariable("USERPROFILE")}\Desktop\temp\test";
//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();
}
public FileResult GetRawCsvFile(string packetId)
{
var packet = PacketRepository.GetByPacketId(packetId);
var theImpactArray = packet.ToMessage();
var results = new List<PacketViewModel>();
var packetVM = new PacketViewModel
{
PacketId = packet.PacketId,
Seq = packet.Seq,
MessageType = theImpactArray.MessageType.ToString(),
RawData = packet.RawPacket,
};
results.Add(packetVM);
byte[] csvFile = results.ToCsvFile();
return File(csvFile, "application/CSV", "rawdata_" + packetId + ".csv");
}
public FileResult GetImpactCsvFile(string packetId)
{
var packet = PacketRepository.GetByPacketId(packetId);
var theImpactArray = packet.ToMessage() as ImpactAlert1ArrayMessage;
var theMsgs = (from m in theImpactArray.ToSingleMessages() select (ImpactAlert1Message)m).ToList();
var theFirst = theMsgs.FirstOrDefault() as ImpactAlert1Message;
int theKey = theFirst.ImpactUniqueID + theFirst.NumberOfDataPointInArray;
var packets = PacketRepository.GetPacketIdAfterByHeaderTime(packetId, new TimeSpan(0, 0, 10));
packets = packets.OrderByDescending(p => p.HeaderTime).ToList();
List<ImpactAlert1Message> results = new List<ImpactAlert1Message>();
packets.ForEach(p =>
{
var impactArray = p.ToMessage() as ImpactAlert1ArrayMessage;
var msgs = (from m in impactArray.ToSingleMessages() select (ImpactAlert1Message)m).ToList();
var first = msgs.FirstOrDefault() as ImpactAlert1Message;
if (first != null)
{
int key = first.ImpactUniqueID + first.NumberOfDataPointInArray;
if (key == theKey)
{
results.AddRange(msgs);
}
}
});
results = results.OrderBy(m => m.Index).ToList();
double delTime = 1d / 800;
List<double> times = new List<double>();
List<double> ax = new List<double>();
List<double> ay = new List<double>();
List<double> az = new List<double>();
for (int i = 0; i < results.Count; i++)
{
times.Add(delTime * i);
ax.Add(results[i].ValueOfXAxisAcceleration);
ay.Add(results[i].ValueOfYAxisAcceleration);
az.Add(results[i].ValueOfZAxisAcceleration);
}
byte[] csvFile = results.ToCsvFile();
return File(csvFile, "application/CSV", "imppact_" + packetId + ".csv");
}
public static void Main(string[] args)
{
FluentSettingsForExcel();
//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()}");
//}
var entities = new List <TestEntity>()
{
new TestEntity()
{
PKID = 1,
SettingId = Guid.NewGuid(),
SettingName = "Setting1",
SettingValue = "Value1"
},
new TestEntity()
{
PKID = 2,
SettingId = Guid.NewGuid(),
SettingName = "Setting2",
SettingValue = "Value2"
},
};
var csvFilePath = $@"{Environment.GetEnvironmentVariable("USERPROFILE")}\Desktop\temp\test\test.csv";
entities.ToCsvFile(csvFilePath);
entities.ToExcelFile(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();
}
/// <summary>
/// Converts a System.Collections.Generic.List<T> to comma delimiter string by using CsvHelper.
/// </summary>
/// <typeparam name="T">The type of the objects to convert.</typeparam>
/// <param name="items">A System.Collections.Generic.List<T>.</param>
/// <returns>Comma delimiter string.</returns>
public static string ToCsvFile <T>(this List <T> items)
{
return(items.ToCsvFile(new Configuration {
ShouldQuote = (field, context) => true
}));
}
public static void Main(string[] args)
{
//var conn = new SqlConnection("server=.;uid=liweihan;pwd=Admin888;database=AccountingApp");
//var entityList = conn.Select<TestEntity>("select * from Users").ToArray();
//entityList[0].Amount = 0;
//entityList[0].PasswordHash = "";
////var dataTable = entityList.ToDataTable();
//var result = entityList.ToExcelFile(ApplicationHelper.MapPath("test.xlsx"));
//var result1 = ExcelHelper.ToEntityList<TestEntity>(ApplicationHelper.MapPath("test.xlsx"));
//// 找不到文件
////var aaa = ExcelHelper.ToEntityList<TestEntity>("");
//var entityList1 = conn.Select<TestEntity2>("select * from Bills");
//var result2 = entityList1.ToExcelFile(ConfigurationHelper.MapPath("test1.xls"));
//entityList1 = ExcelHelper.ToEntityList<TestEntity2>(ConfigurationHelper.MapPath("test1.xls"));
//var entityList2 = ExcelHelper.ToEntityList<Model>(FilePath).Where(_ => !string.IsNullOrWhiteSpace(_.HotelId)).ToArray();
//if (entityList2.Length > 0)
//{
// var dir = Path.GetDirectoryName(FilePath);
// foreach (var group in entityList2.GroupBy(e => new
// {
// HotelId = e.HotelId.Trim(),
// HotelName = e.HotelName.Trim()
// }))
// {
// var path = $"{dir}\\sub\\{group.Key.HotelName}-1月对账单.xlsx";
// group.ToArray().ToExcelFile(path);
// }
// Console.WriteLine("Success");
//}
//var table = ExcelHelper.ToDataTable(FilePath);
////Console.WriteLine(table.Rows.Count);
////Console.WriteLine(table.ToExcelFile(FilePath.Replace("AllStores", "AllStores1")));
//using (var connection = new SqlConnection("server=.;uid=liweihan;pwd=Admin888;database=TestDb"))
//{
// Console.WriteLine($"导入结果：{connection.BulkCopy(table, "testBulkCopy")}");
//}
//var setting = ExcelHelper.SettingFor<TestEntity>();
//// ExcelSetting
//setting.HasAuthor("WeihanLi")
// .HasTitle("WeihanLi.Npoi test")
// .HasDescription("")
// .HasSubject("");
//setting.HasFilter(0, 1)
// .HasFreezePane(0, 1, 2, 1);
//setting.Property(_ => _.Amount)
// .HasColumnTitle("Amount")
// .HasColumnIndex(2);
//setting.Property(_ => _.Username)
// .HasColumnTitle("Username")
// .HasColumnIndex(0);
//setting.Property(_ => _.CreateTime)
// .HasColumnTitle("CreateTime")
// .HasColumnFormatter("yyyy-MM-dd HH:mm:ss");
//setting.Property(_ => _.PasswordHash)
// .Ignored();
//var entities = ExcelHelper.ToEntityList<TestEntity>(ApplicationHelper.MapPath("test.xlsx"));
//Console.WriteLine(entities.Count);
////entities = conn.Select<TestEntity>("select * from Users").ToList();
//entities.ToExcelFile(ApplicationHelper.MapPath("test_1.xlsx"));
//Console.WriteLine("Success");
//Console.WriteLine($"WorkingSet size: {Process.GetCurrentProcess().WorkingSet64 / 1024} kb");
//// ExportExcelViaEpplusPerfTest();
//// ExportExcelViaEpplusPerfTest(1_000_000, 5);
//// ExportExcelPerfTest(100_000, 10);
//// ExportExcelPerfTest(1_000_000, 5);
//ExportCsvPerfTest(100_000, 10);
//// ExportCsvPerfTest(1_000_000, 5);
//Console.WriteLine($"WorkingSet size: {Process.GetCurrentProcess().WorkingSet64 / 1024} kb");
//GC.Collect(2, GCCollectionMode.Forced);
//Console.WriteLine($"WorkingSet size: {Process.GetCurrentProcess().WorkingSet64 / 1024} kb");
var testData = new List <TestEntity>(10);
for (int i = 1; i <= 10; i++)
{
testData.Add(new TestEntity()
{
Amount = 1000,
Username = "******",
CreateTime = DateTime.UtcNow.AddDays(-3),
PKID = i,
PasswordHash = SecurityHelper.SHA1($"_x_{i}")
});
}
var excelFilePath = $@"{Environment.GetEnvironmentVariable("USERPROFILE")}\Desktop\temp\test\test123.fx.xlsx";
testData.ToExcelFile(excelFilePath);
testData.ToCsvFile(excelFilePath.Replace(".xlsx", ".csv"));
var list = ExcelHelper.ToEntityList <TestEntity>(excelFilePath);
Console.WriteLine("complete");
Console.ReadLine();
}
