BDF3/XLimport.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using Spire.Xls;

namespace bdf
{
    class CXLFile
    {
        public static List<Spire.Xls.Workbook> S_ServiceOrders = new List<Spire.Xls.Workbook> { };


        public static void S_getXLS(string task, string dir)
        {
            //string folderPath = dir;
            string filePrefix1 = "ROGERS_";
            string filePrefix2 = task + "_SO";
            var excelFiles = getLatestVersionFiles(dir, filePrefix1, filePrefix2);

            if ( (excelFiles.Length == 0) || (excelFiles == null) )
            {
                Console.WriteLine("No Excel files found with prefix: " + filePrefix1 + "*" + filePrefix2);
                //throw new System.ArgumentNullException("filePaths");
                return;
            }

            foreach (string path in excelFiles)
            {
                if (string.IsNullOrWhiteSpace(path))
                    continue;

                if (!System.IO.File.Exists(path))
                    throw new System.IO.FileNotFoundException(
                        "Excel file not found.", path);

                Logger.Log(1, "Scanning: {0}", Path.GetFileName(path));

                // Create a new workbook and load the file
                Spire.Xls.Workbook wb = new Spire.Xls.Workbook();

                //straight up load from file
                //wb.LoadFromFile(path);  // FreeSpire autodetects .xls vs .xlsx

                //more network savvy way that handles sharepoint's idiosyncrasies
                FileStream stream = null;

                // OneDrive sync engine frequently locks files briefly:
                const int maxRetries = 12;
                const int retryDelayMs = 150;

                for (int i = 0; i < maxRetries; i++)
                {
                    try
                    {
                        stream = new FileStream(
                            path,
                            FileMode.Open,
                            FileAccess.Read,
                            FileShare.ReadWrite | FileShare.Delete
                        );
                        break; // opened successfully
                    }
                    catch (IOException)
                    {
                        if (i == maxRetries - 1)
                            throw;

                        System.Threading.Thread.Sleep(retryDelayMs);
                    }
                }
                wb.LoadFromStream(stream);

                Logger.Log(5, "  -loaded SO={0}", wb.Worksheets[1][2, 1].Value.ToString() );

                S_ServiceOrders.Add(wb);
            }
            Logger.Log("All SO Excel files processed successfully.");
            return; // S_ServiceOrders;
        }


        // This routine is wholly dependent on the filename structure being ROGERS*LDF*_SO*VER*number
        // in future should just open each workbook and read exact version from the first worksheet.
        public static string[] getLatestVersionFiles(string folder, string prefixA, string prefixB)
        {
            var files = Directory.GetFiles(folder);

            // Build wildcard pattern:
            // file must contain prefixA ... anything ... prefixB
            Func<string, bool> matchesPrefix = name =>
                name.IndexOf(prefixA, StringComparison.OrdinalIgnoreCase) >= 0 &&
                name.IndexOf(prefixB, StringComparison.OrdinalIgnoreCase) >
                    name.IndexOf(prefixA, StringComparison.OrdinalIgnoreCase);

            var filtered = files
                .Where(f => matchesPrefix(Path.GetFileName(f)))
                .ToList();

            // Serial = 6 digits
            // After "VER" allow ANY non-digit characters, then the version number
            var regex = new Regex(
                @"(?<serial>\d{6}).*?VER\D*(?<ver>\d+)",
                RegexOptions.IgnoreCase
            );

            Dictionary<string, (int version, string file)> best =
                new Dictionary<string, (int, string)>();

            foreach (var file in files)     //todo should this be filtered instead?
            {
                string name = Path.GetFileName(file);
                var match = regex.Match(name);

                if (!match.Success)
                {
                    Logger.Log(1, " Did not find SO and Version for file {0}", name);
                    continue;
                }

                string serial = match.Groups["serial"].Value;

                if (!int.TryParse(match.Groups["ver"].Value, out int ver))
                {
                    Logger.Log(1, " Did not extract Version for file {0}", name);
                    continue;
                }

                // Keep highest version per serial
                if (!best.ContainsKey(serial)) // || ver > best[serial].version)
                {
                    best[serial] = (ver, file);
                    Logger.Log(5, " Adding: {0} ver {1}", serial, ver); //todo open up file and verify so version from internals?
                }
                else if (ver > best[serial].version)
                {
                    Logger.Log(2, " Replacing: {0} ver {1} with ver {2}", serial, best[serial].version, ver);
                    best[serial] = (ver, file);
                }
                else
                {
                    Logger.Log(4, " Ignoring: {0} v.{1}", serial, ver);
                }
            }

            return best.Values.Select(v => v.file).ToArray();
        }
    }
}