public async Task createOutputFileForEndOfSeasonAsync(string validationPattern, Task <List <string> > allFilesXlsx,
string inputSeason, string fileName, string extension, string excelFilePath)
{
//start init the xlsx file
ExcelApp seasonStatisticApp;
//GetFiles
var allFiles = allFilesXlsx;
if (File.Exists(excelFilePath + fileName + extension) == false)
{
//create file
ExcelApp createNewApp = new ExcelApp();
createNewApp.CreateNewFile();
createNewApp.SaveAs($@"{excelFilePath}{fileName}{extension}");
createNewApp.Close();
//init and rename
seasonStatisticApp = new ExcelApp(excelFilePath + fileName + extension, 1);
seasonStatisticApp.RenameSheet(inputSeason);
}
else
{
//init
seasonStatisticApp = new ExcelApp(excelFilePath + fileName + extension, 1);
//check if sheet exists
bool isExist = seasonStatisticApp.ContainsSheet(inputSeason);
if (isExist == false)
{
//if not add
seasonStatisticApp.AddNewWorksheet(inputSeason);
}
else
{
//if yes select it ,clear and overwrite
seasonStatisticApp.SelectSheet(inputSeason);
seasonStatisticApp.ClearSheet();
Console.WriteLine($"--Overwriten tab: [{seasonStatisticApp.GetSheetId()}]");
}
}
//star writing in the xlsx file
seasonStatisticApp.Write(0, 0, "[ " + inputSeason + " ]");
seasonStatisticApp.Write(1, 0, "GARMENT ID");
seasonStatisticApp.Write(1, 1, "SKU");
seasonStatisticApp.Write(1, 2, "STYLE");
int row = 2;
int col = 0;
await Task.Run(() =>
{
foreach (var file in allFiles.Result)
{
var matches = Regex.Matches(file, validationPattern);
foreach (Match match in matches)
{
var currentGarmentId = match.Groups["garment"].ToString();
var currentStyle = match.Groups["skuStyle"].ToString();
var currentSku = currentStyle + "-" + match.Groups["colorCode"];
seasonStatisticApp.Write(row, col, currentGarmentId);
col++;
seasonStatisticApp.Write(row, col, currentSku);
col++;
seasonStatisticApp.Write(row, col, currentStyle);
col++;
//reset col
col = 0;
//increase row
row++;
}
}
});
//counter of skus
seasonStatisticApp.Write(0, 3, $"COUNTER[ {seasonStatisticApp.Counter()} ]");
//format cells and columns
seasonStatisticApp.Formatting();
//range to sort
var startColumn = "A";
var startRow = 3;
var endColumn = "C";
var endRow = seasonStatisticApp.Counter(); //last row number
bool isSorting = endRow > 1; // if true
if (isSorting)
{
seasonStatisticApp.SortAscending(startColumn, startRow, endColumn, endRow);
}
seasonStatisticApp.Save();
seasonStatisticApp.Close();
}