예제 #1
0
        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();
        }