private void DoReadWorkButton_Click(object sender, RoutedEventArgs e)
{
try
{
string filePath = FilePath.Text;
if (File.Exists(filePath))
{
LogMessage($"Loading data from {filePath}");
// Passing in LogServiceMessage in optional. I'm using it here to help debug in issues while reading a file.
var readerService = new ClassToExcelReaderService <Person>(LogServiceMessage);
var worksheetName = "People";
List <Person> people = readerService.ReadWorksheet(filePath, worksheetName, true);
if (people.Count > 0)
{
foreach (var person in people)
{
LogMessage(person.ToString());
}
}
else
{
LogMessage($"No data found on the {worksheetName} work sheet!");
}
}
else
{
LogMessage("The file does not exists: " + filePath);
}
}
catch (Exception ex)
{
LogError(ex);
}
}
/// <summary>
/// Same as above but now we are returning a DataTable
/// </summary>
/// <param name="pFileName">Existing Excel file</param>
/// <returns></returns>
public DataTable ClassToExcelReaderServiceReaderAsDataTable(string pFileName)
{
var customers = new List <Customer>();
var readerService = new ClassToExcelReaderService <Customer>();
customers = readerService.ReadWorksheet(pFileName, "Customers", true);
readerService = null;
return(customers.ToDataTable());
}
/// <summary>
/// Uses a wrapper library based on OpenXml
/// https://github.com/madcodemonkey/ClassToExcel.OpenXml
///
/// The reason for using this library is because when using
/// OpenXml by itself requires a great deal of code while this
/// library requires two lines of code. It's meant for reading
/// a worksheet whith a known structure.
/// </summary>
/// <param name="pFileName">Existing Excel file</param>
/// <returns></returns>
public List <Customer> ClassToExcelReaderServiceReader(string pFileName)
{
List <Customer> customers = new List <Customer>();
var readerService = new ClassToExcelReaderService <Customer>();
customers = readerService.ReadWorksheet(pFileName, "Customers", true);
readerService = null;
return(customers);
}
public List <LargerTable> ClassToExcelReaderServiceReaderForLargeTable(string pFileName)
{
List <LargerTable> customers = new List <LargerTable>();
var readerService = new ClassToExcelReaderService <LargerTable>();
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
customers = readerService.ReadWorksheet(pFileName, "Example1", true, 1, 100);
readerService = null;
// Stop.
stopwatch.Stop();
// Write hours, minutes and seconds.
Console.WriteLine("Time elapsed: {0:hh\\:mm\\:ss}", stopwatch.Elapsed);
foreach (LargerTable row in customers)
{
Console.WriteLine($"{row.id}, {row.FirstName}, {row.LastName}, {row.LastName}, {row.Street}, {row.City}, {row.State}, {row.Country}, {row.Value1}, {row.Date1}");
}
return(customers);
}
public List <U> SaveAndRead(List <T> expectedList, bool hasHeaderRow,
Action <ClassToExcelMessage> writeCallback = null,
Action <ClassToExcelMessage> readCallback = null)
{
const string workSheetName = "Test";
// WRITE FIRST
IClassToExcelWriterService writer = new ClassToExcelWriterService(writeCallback);
writer.AddWorksheet(expectedList, workSheetName, hasHeaderRow);
MemoryStream ms = writer.SaveToMemoryStream();
// READ AFTERWARDS
List <U> actualList;
using (ms)
{
var reader = new ClassToExcelReaderService <U>(readCallback);
actualList = reader.ReadWorksheet(ms, workSheetName, hasHeaderRow);
}
return(actualList);
}
