private static async Task ProcessSingleFile(string myPath, string outputDir, Standardizer standardizer)
{
if (File.Exists(myPath))
{
List <MembersInWorksheet> membersInSpreadsheet = GetMembersFromWorksheets(myPath, standardizer);
await ProcessMembersInSpreadsheet(membersInSpreadsheet);
ExportMembersToNewSpreadsheet(myPath, outputDir, standardizer, membersInSpreadsheet);
}
}
private static async Task ProcessFilesInInputFolder(string inputFolder, string outputFolder)
{
List <string> inputSpreadsheets = GetFilePaths(inputFolder);
Log.Information("Found {spreadsheetCount} spreadsheets", inputSpreadsheets.Count);
foreach (string path in inputSpreadsheets)
{
try {
Log.Information("Processing spreadsheet {path}", path);
Standardizer standardizer = new Standardizer();
await ProcessSingleFile(path, outputFolder, standardizer);
}
catch (Exception e) {
Log.Error(e, "Error processing spreadsheet {spreadsheetName}", path);
}
}
}
private static List <MembersInWorksheet> GetMembersFromWorksheets(string myPath, Standardizer standardizer)
{
FileInfo sourceFile = new FileInfo(myPath);
List <MembersInWorksheet> membersInSpreadsheet = new List <MembersInWorksheet>();
using (ExcelPackage package = new ExcelPackage(sourceFile)) {
Log.Information("Retrieving members from {sourceFile}", sourceFile.Name);
int worksheetCount = package.Workbook.Worksheets.Count;
for (int currentWorksheet = 0; currentWorksheet < worksheetCount; currentWorksheet++)
{
MembersInWorksheet membersInWorksheet = new MembersInWorksheet();
string worksheetName = GetWorksheetName(package.Workbook, currentWorksheet);
Log.Information("Retrieving members from worksheet {worksheetName}", worksheetName);
membersInWorksheet.NewWorksheetName = worksheetName;
membersInWorksheet.Members = standardizer.GetMembers(package, currentWorksheet);
membersInSpreadsheet.Add(membersInWorksheet);
}
}
return(membersInSpreadsheet);
}
private static void ExportMembersToNewSpreadsheet(string myPath, string outputDir, Standardizer standardizer, List <MembersInWorksheet> membersInSpreadsheet)
{
string newFilename = Path.GetFileNameWithoutExtension(myPath) + "_transformed.xlsx";
string targetPath = Path.Combine(outputDir, newFilename);
FileInfo targetFile = new FileInfo(targetPath);
using (ExcelPackage targetPackage = new ExcelPackage(targetFile)) {
foreach (MembersInWorksheet membersInWorksheet in membersInSpreadsheet)
{
standardizer.ExportMembers(targetPackage, membersInWorksheet.NewWorksheetName, membersInWorksheet.Members);
}
targetPackage.SaveAs(targetFile);
}
}