Handles locked Excel files by creating copies

Sébastien Geiser · Sébastien Geiser · commit 156cda603154 · 2025-05-06T08:52:11.000+02:00
Addresses the issue where the application fails to open Excel files if they are locked by another process.

Implements a mechanism to create a temporary copy of the Excel file if the original is locked, allowing the application to proceed without interruption. This ensures continued functionality even when the Excel file is in use elsewhere.
diff --git a/RegexDialog/Model/ExcelSheetSelection.cs b/RegexDialog/Model/ExcelSheetSelection.cs
@@ -1,6 +1,7 @@
 ﻿using ClosedXML.Excel;
 using DocumentFormat.OpenXml.Bibliography;
 using Newtonsoft.Json;
+using RegexDialog.Utils;
 using System;
 using System.Collections.Generic;
 using System.Linq;
@@ -25,7 +26,7 @@ public class ExcelSheetSelection : NotifyPropertyChangedBaseClass
         [JsonIgnore]
         public ICommand EvaluateCommand => new RelayCommand(_ =>
         {
-            using(IXLWorkbook workbook = new XLWorkbook(Config.Instance.TextSourceExcelPath))
+            using(IXLWorkbook workbook = new XLWorkbook(Config.Instance.TextSourceExcelPath.MakeCopyIfLocked()))
             {
                 try
                 {
diff --git a/RegexDialog/RegExToolDialog.xaml.cs b/RegexDialog/RegExToolDialog.xaml.cs
@@ -8,6 +8,7 @@
 using Newtonsoft.Json;
 using Ookii.Dialogs.Wpf;
 using RegexDialog.Behaviors;
+using RegexDialog.Utils;
 using System;
 using System.Collections.Generic;
 using System.Collections.ObjectModel;
@@ -702,7 +703,7 @@ List<RegexResult> GetMatchesFor(string text, string fileName = "", int selection
                     }
                     else if (Config.Instance.TextSourceOn == RegexTextSource.Excel)
                     {
-                        using (XLWorkbook workbook = new(Config.Instance.TextSourceExcelPath))
+                        using (XLWorkbook workbook = new(Config.Instance.TextSourceExcelPath.MakeCopyIfLocked()))
                         {
                             var elementNb = 0;
                             MatchResultsTreeView.ItemsSource = Config.Instance.ExcelSheets
@@ -1143,7 +1144,7 @@ void Extract(string text, string fileName = "")
                 }
                 else if (Config.Instance.TextSourceOn == RegexTextSource.Excel)
                 {
-                    using (XLWorkbook workbook = new(Config.Instance.TextSourceExcelPath))
+                    using (XLWorkbook workbook = new(Config.Instance.TextSourceExcelPath.MakeCopyIfLocked()))
                     {
                         Config.Instance.ExcelSheets
                           .FindAll(sheetSelection => sheetSelection.IsSelected)
@@ -1257,7 +1258,7 @@ private void IsMatch()
                 }
                 else if (Config.Instance.TextSourceOn == RegexTextSource.Excel)
                 {
-                    using (XLWorkbook workbook = new(Config.Instance.TextSourceExcelPath))
+                    using (XLWorkbook workbook = new(Config.Instance.TextSourceExcelPath.MakeCopyIfLocked()))
                     {
                         bool found = false;
                         string sheetName = string.Empty;
diff --git a/RegexDialog/Utils/Config.cs b/RegexDialog/Utils/Config.cs
@@ -1,6 +1,7 @@
 ﻿using ClosedXML.Excel;
 using Newtonsoft.Json;
 using Newtonsoft.Json.Converters;
+using RegexDialog.Utils;
 using System;
 using System.Collections.Generic;
 using System.Collections.ObjectModel;
@@ -188,7 +189,7 @@ public void OnTextSourceExcelPathChanged()
                 isInit = false;
                 try
                 {
-                    using (XLWorkbook workbook = new(TextSourceExcelPath))
+                    using (XLWorkbook workbook = new(TextSourceExcelPath.MakeCopyIfLocked()))
                     {
                         ExcelSheets.ForEach(sheetSelection => sheetSelection.PropertyChanged -= SubPropertyChanged_PropertyChanged);
 
diff --git a/RegexDialog/Utils/PotentiallyLockedFileOpener.cs b/RegexDialog/Utils/PotentiallyLockedFileOpener.cs
@@ -0,0 +1,44 @@
+﻿using System.IO;
+
+namespace RegexDialog.Utils
+{
+    public static class PotentiallyLockedFileCopier
+    {
+        public static string MakeCopyIfLocked(this string sourceFile)
+        {
+            if(IsFileLocked(new FileInfo(sourceFile)))
+            {
+                string destinationFile = Path.Combine(Path.GetTempPath(), Path.GetFileName(sourceFile));
+
+                if (!File.Exists(destinationFile) || File.GetLastWriteTime(sourceFile) != File.GetLastWriteTime(destinationFile))
+                {
+                    File.Copy(sourceFile, destinationFile);                    
+                }
+
+                return destinationFile;
+
+            }
+            else
+            {
+                return sourceFile;
+            }
+        }
+
+        private static bool IsFileLocked(FileInfo file)
+        {
+            try
+            {
+                using (FileStream stream = file.Open(FileMode.Open, FileAccess.Read, FileShare.None))
+                {
+                    stream.Close();
+                }
+            }
+            catch (IOException)
+            {
+                return true;
+            }
+
+            return false;
+        }
+    }
+}

Original file line number	Diff line number	Diff line change
`@@ -1,6 +1,7 @@`
`1`	`1`	`using ClosedXML.Excel;`
`2`	`2`	`using DocumentFormat.OpenXml.Bibliography;`
`3`	`3`	`using Newtonsoft.Json;`
	`4`	`+using RegexDialog.Utils;`
`4`	`5`	`using System;`
`5`	`6`	`using System.Collections.Generic;`
`6`	`7`	`using System.Linq;`
`@@ -25,7 +26,7 @@ public class ExcelSheetSelection : NotifyPropertyChangedBaseClass`
`25`	`26`	`[JsonIgnore]`
`26`	`27`	`public ICommand EvaluateCommand => new RelayCommand(_ =>`
`27`	`28`	`{`
`28`		`- using(IXLWorkbook workbook = new XLWorkbook(Config.Instance.TextSourceExcelPath))`
	`29`	`+ using(IXLWorkbook workbook = new XLWorkbook(Config.Instance.TextSourceExcelPath.MakeCopyIfLocked()))`
`29`	`30`	`{`
`30`	`31`	`try`
`31`	`32`	`{`
Original file line number	Diff line number	Diff line change
`@@ -8,6 +8,7 @@`
`8`	`8`	`using Newtonsoft.Json;`
`9`	`9`	`using Ookii.Dialogs.Wpf;`
`10`	`10`	`using RegexDialog.Behaviors;`
	`11`	`+using RegexDialog.Utils;`
`11`	`12`	`using System;`
`12`	`13`	`using System.Collections.Generic;`
`13`	`14`	`using System.Collections.ObjectModel;`
`@@ -702,7 +703,7 @@ List<RegexResult> GetMatchesFor(string text, string fileName = "", int selection`
`702`	`703`	`}`
`703`	`704`	`else if (Config.Instance.TextSourceOn == RegexTextSource.Excel)`
`704`	`705`	`{`
`705`		`- using (XLWorkbook workbook = new(Config.Instance.TextSourceExcelPath))`
	`706`	`+ using (XLWorkbook workbook = new(Config.Instance.TextSourceExcelPath.MakeCopyIfLocked()))`
`706`	`707`	`{`
`707`	`708`	`var elementNb = 0;`
`708`	`709`	`MatchResultsTreeView.ItemsSource = Config.Instance.ExcelSheets`
`@@ -1143,7 +1144,7 @@ void Extract(string text, string fileName = "")`
`1143`	`1144`	`}`
`1144`	`1145`	`else if (Config.Instance.TextSourceOn == RegexTextSource.Excel)`
`1145`	`1146`	`{`
`1146`		`- using (XLWorkbook workbook = new(Config.Instance.TextSourceExcelPath))`
	`1147`	`+ using (XLWorkbook workbook = new(Config.Instance.TextSourceExcelPath.MakeCopyIfLocked()))`
`1147`	`1148`	`{`
`1148`	`1149`	`Config.Instance.ExcelSheets`
`1149`	`1150`	`.FindAll(sheetSelection => sheetSelection.IsSelected)`
`@@ -1257,7 +1258,7 @@ private void IsMatch()`
`1257`	`1258`	`}`
`1258`	`1259`	`else if (Config.Instance.TextSourceOn == RegexTextSource.Excel)`
`1259`	`1260`	`{`
`1260`		`- using (XLWorkbook workbook = new(Config.Instance.TextSourceExcelPath))`
	`1261`	`+ using (XLWorkbook workbook = new(Config.Instance.TextSourceExcelPath.MakeCopyIfLocked()))`
`1261`	`1262`	`{`
`1262`	`1263`	`bool found = false;`
`1263`	`1264`	`string sheetName = string.Empty;`