GPAL - Generally Positive Automation Library v1.0
GPAL The Fluent Automation LIbrary
Loading...
Searching...
No Matches
GPALExcel.cs
1// =============================================================================
2// GPAL - Generally Positive Automation Library
3// Copyright © 2026 Software Decisions, Inc. All rights reserved.
4//
5// This file is part of GPAL.
6// Licensed under the Business Source License 1.1
7//
8// Primary development, architecture, and vision by Michael B. Vederman,
9// CEO of Software Decisions, Inc., Texas.
10//
11// Internal development maintained privately.
12// Public releases appear on GitHub: https://github.com/SoftwareDecisionsInc/GPAL.
13//
14// See LICENSE for full terms, including Additional Use Grant.
15// =============================================================================
16
17using ClosedXML.Excel;
18using DocumentFormat.OpenXml.Spreadsheet;
19using System;
20using System.Collections.Generic;
21using System.IO;
22using System.Linq;
23using static GenerallyPositive.Enums;
24
25namespace GenerallyPositive
26{
45 public class GPALExcel : IGPALExcel
46 {
47 private GPALFile _gpalFile;
48 private List<string> _filePaths;
49 private Dictionary<string, XLWorkbook> _workbooks;
50 private string _currentSheetName;
51 private int _currentSheetIndex { get; set; }
52 List<ExcelHelper.CellDifference> _differences;
53 private int? _insertPosition;
54 private string _insertSeparator;
55 private string _replaceValue;
56 private string _currentCell;
57 private bool _operationPerformed;
58 private bool _isClosed;
59 private List<(string Sheet, string Range, IGPALGrid<string> Data)> _rangesData;
60
65 internal GPALExcel()
66 {
67 _filePaths = new List<string>();
68 _workbooks = new Dictionary<string, XLWorkbook>();
69 _rangesData = new List<(string Sheet, string Range, IGPALGrid<string> Data)>();
70 _differences = new List<ExcelHelper.CellDifference>();
71 _insertPosition = 0;
72 _insertSeparator = " ";
73 _operationPerformed = false;
74 }
75
83 private void ReopenIfClosed()
84 {
85 if (_isClosed)
86 {
87 _workbooks.Clear();
88 _filePaths.Clear();
89 _rangesData.Clear();
90 _differences.Clear();
91 _currentSheetName = null;
92 _currentSheetIndex = -1;
93 _insertPosition = 0;
94 _insertSeparator = " ";
95 _currentCell = null;
96 _isClosed = false;
97
98 WithFile(_gpalFile);
99
100 GPAL.PublishSimpleEvent(
101 GPALEventType.INFO,
102 $"Reopening already closed file [{_gpalFile?.Filenames?.FirstOrDefault() ?? "unknown"}]",
103 this,
104 GPALObjectType.Other);
105 }
106 }
107
114 private void LoadFile(string path)
115 {
116 if (!_workbooks.ContainsKey(path))
117 {
118 try
119 {
120 _workbooks[path] = new XLWorkbook(path);
121 GPAL.PublishSimpleEvent(
122 GPALEventType.INFO,
123 $"Loaded workbook [{path}]",
124 this,
125 GPALObjectType.GPALExcel);
126 }
127 catch (Exception ex)
128 {
129 GPAL.PublishSimpleEvent(
130 GPALEventType.ERROR,
131 $"Failed to load workbook [{path}]",
132 this,
133 GPALObjectType.GPALExcel, ex);
134 }
135 }
136 }
137
141 public IGPALExcel ToGPALObject() => this;
142
150 public IAllowExcelSheetSelection WithFile(GPALFile gpalFile)
151 {
152 if (gpalFile == null)
153 {
154 GPAL.PublishSimpleEvent(
155 GPALEventType.ERROR,
156 "GPALFile cannot be null",
157 this,
158 GPALObjectType.GPALExcel);
159 }
160 else
161 {
162 _gpalFile = gpalFile;
163 _filePaths.Clear();
164 _workbooks.Clear();
165 _rangesData.Clear();
166 _differences.Clear();
167 _currentSheetName = null;
168 _currentSheetIndex = -1;
169 _insertPosition = 0;
170 _insertSeparator = " ";
171 _currentCell = null;
172 _isClosed = false;
173 _operationPerformed = false;
174
175 foreach (var pattern in gpalFile.Filenames)
176 {
177 try
178 {
179 string directory = Path.GetDirectoryName(pattern) ?? Directory.GetCurrentDirectory();
180 string fileName = Path.GetFileName(pattern);
181 var matchingFiles = Directory.GetFiles(directory, fileName, SearchOption.TopDirectoryOnly);
182 _filePaths.AddRange(matchingFiles);
183 }
184 catch (Exception ex)
185 {
186 GPAL.PublishSimpleEvent(
187 GPALEventType.ERROR,
188 $"Failed to resolve wildcard pattern [{pattern}]",
189 this,
190 GPALObjectType.GPALExcel, ex);
191 }
192 }
193
194 _filePaths = _filePaths.Distinct().ToList();
195 foreach (var path in _filePaths)
196 {
197 LoadFile(path);
198 }
199 }
200
201 return this;
202 }
203
211 public IAllowExcelSheetSelection WithNewFile(GPALFile gpalFile)
212 {
213 if (gpalFile == null)
214 {
215 GPAL.PublishSimpleEvent(
216 GPALEventType.ERROR,
217 "GPALFile cannot be null",
218 this,
219 GPALObjectType.GPALExcel);
220 }
221 else
222 {
223 string path = gpalFile.Last ?? gpalFile.Filename;
224
225 _gpalFile = gpalFile;
226 _filePaths = new List<string> { path };
227 _workbooks.Clear();
228 _rangesData.Clear();
229 _differences.Clear();
230 _currentSheetName = null;
231 _currentSheetIndex = -1;
232 _insertPosition = 0;
233 _insertSeparator = " ";
234 _currentCell = null;
235 _isClosed = false;
236 _operationPerformed = false;
237
238 XLWorkbook made = new XLWorkbook();
239
240 made.Worksheets.Add("Sheet1");
241
242 _workbooks[path] = made;
243 _currentSheetIndex = 1;
244
245 GPAL.PublishSimpleEvent(
246 GPALEventType.INFO,
247 $"Started workbook [{path}] with one sheet",
248 this,
249 GPALObjectType.GPALExcel);
250 }
251
252 return this;
253 }
254
261 public IAllowExcelOperations WithNewSheet(string sheetName)
262 {
263 ReopenIfClosed();
264
265 if (true == string.IsNullOrWhiteSpace(sheetName))
266 {
267 GPAL.PublishSimpleEvent(
268 GPALEventType.ERROR,
269 "Sheet name cannot be empty",
270 this,
271 GPALObjectType.GPALExcel);
272 }
273 else if (!_workbooks.Any())
274 {
275 GPAL.PublishSimpleEvent(
276 GPALEventType.ERROR,
277 "No workbooks loaded",
278 this,
279 GPALObjectType.GPALExcel);
280 }
281 else
282 {
283 foreach (var kvp in _workbooks)
284 if (false == kvp.Value.TryGetWorksheet(sheetName, out IXLWorksheet _))
285 kvp.Value.Worksheets.Add(sheetName);
286
287 _currentSheetName = sheetName;
288 _currentSheetIndex = -1;
289 _currentCell = null;
290 _rangesData.Clear();
291 }
292
293 return this;
294 }
295
303 public IAllowExcelOperations InputFrom(IGPALGrid<string> grid)
304 {
305 ReopenIfClosed();
306
307 _operationPerformed = true;
308
309 if (null == grid || 0 == grid.Rows)
310 {
311 GPAL.PublishSimpleEvent(
312 GPALEventType.ERROR,
313 "No rows to write into the sheet",
314 this,
315 GPALObjectType.GPALExcel);
316 }
317 else if (!_workbooks.Any())
318 {
319 GPAL.PublishSimpleEvent(
320 GPALEventType.ERROR,
321 "No workbooks loaded",
322 this,
323 GPALObjectType.GPALExcel);
324 }
325 else
326 {
327 string startCell = _currentCell ?? "A1";
328
329 foreach (var kvp in _workbooks)
330 {
331 IXLWorksheet worksheet = GetWorksheet(kvp.Value);
332
333 if (null == worksheet)
334 GPAL.PublishSimpleEvent(
335 GPALEventType.ERROR,
336 $"No sheet selected in [{kvp.Key}], so there is nowhere to write the rows",
337 this,
338 GPALObjectType.GPALExcel);
339 else
340 {
341 IXLCell anchorCell = worksheet.Cell(startCell);
342 int firstRow = anchorCell.Address.RowNumber;
343 int firstColumn = anchorCell.Address.ColumnNumber;
344 int rowOffset = 0;
345
346 foreach (List<string> row in grid)
347 {
348 for (int column = 0; column < row.Count; column++)
349 worksheet.Cell(firstRow + rowOffset, firstColumn + column).Value = row[column];
350
351 rowOffset++;
352 }
353
354 GPAL.PublishSimpleEvent(
355 GPALEventType.INFO,
356 $"Wrote [{grid.Rows}] rows into sheet [{worksheet.Name}] of [{kvp.Key}] from [{startCell}]",
357 this,
358 GPALObjectType.GPALExcel);
359 }
360 }
361 }
362
363 return this;
364 }
365
371 public IAllowExcelOperations WithSheet(int sheetIndex)
372 {
373 ReopenIfClosed();
374
375 if (!_workbooks.Any())
376 {
377 GPAL.PublishSimpleEvent(
378 GPALEventType.ERROR,
379 "No workbooks loaded",
380 this,
381 GPALObjectType.GPALExcel);
382 }
383 else
384 {
385
386 int indexErrors = 0;
387
388 foreach (var kvp in _workbooks)
389 if (1 > sheetIndex || sheetIndex > kvp.Value.Worksheets.Count)
390 indexErrors += 1;
391
392 if (indexErrors == _workbooks.Count)
393 GPAL.PublishSimpleEvent(
394 GPALEventType.ERROR,
395 $"Invalid sheet index. No workbook contains sheet [{sheetIndex}]",
396 this,
397 GPALObjectType.GPALExcel);
398 else
399 {
400 _currentSheetName = null;
401 _currentSheetIndex = sheetIndex;
402 }
403 }
404
405 return this;
406 }
407
413 public IAllowExcelOperations WithSheet(string sheetName)
414 {
415 ReopenIfClosed();
416
417 if (string.IsNullOrEmpty(sheetName))
418 {
419 GPAL.PublishSimpleEvent(
420 GPALEventType.ERROR,
421 "Sheet name cannot be empty",
422 this,
423 GPALObjectType.GPALExcel);
424 }
425 else
426 {
427 int nameErrors = 0;
428
429 foreach (var kvp in _workbooks)
430 {
431 if (!kvp.Value.Worksheets.Any(ws => ws.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase)))
432 nameErrors += 1;
433 }
434
435 if (nameErrors == _workbooks.Count)
436 GPAL.PublishSimpleEvent(
437 GPALEventType.ERROR,
438 $"Sheet [{sheetName}] not found in any workbooks",
439 this,
440 GPALObjectType.GPALExcel);
441 else
442 {
443 _currentSheetName = sheetName;
444 _currentSheetIndex = -1;
445 }
446 }
447
448 return this;
449 }
450
458 public IAllowExcelOperations WithRange(string range)
459 {
460 ReopenIfClosed();
461
462 if (string.IsNullOrEmpty(range))
463 {
464 GPAL.PublishSimpleEvent(
465 GPALEventType.ERROR,
466 "Range cannot be empty",
467 this,
468 GPALObjectType.Other);
469 }
470 else
471 {
472 if (true == _operationPerformed)
473 {
474 _rangesData.Clear();
475 _operationPerformed = false;
476 _differences.Clear();
477 }
478
479 foreach (var kvp in _workbooks)
480 {
481 var worksheet = GetWorksheet(kvp.Value);
482 try
483 {
484 if (worksheet != null)
485 {
486 var rangeData = ExcelHelper.ReadRange(worksheet, range);
487 _rangesData.Add((_currentSheetName ?? _currentSheetIndex.ToString() ?? "Sheet1", range, rangeData));
488 }
489 else
490 {
491 GPAL.PublishSimpleEvent(
492 GPALEventType.ERROR,
493 $"Cannot read range [{range}] from [{worksheet?.Name ?? "unknown"}]: Invalid sheet",
494 this,
495 GPALObjectType.Other);
496 }
497 }
498 catch (Exception ex)
499 {
500 GPAL.PublishSimpleEvent(
501 GPALEventType.ERROR,
502 $"Failed to read range [{range}] from [{worksheet?.Name ?? "unknown"}]",
503 this,
504 GPALObjectType.Other, ex);
505 }
506 }
507 }
508
509 return this;
510 }
511
518 public IAllowExcelOperations WriteRange(string range)
519 {
520 ReopenIfClosed();
521
522 _operationPerformed = true;
523
524 if (!_rangesData.Any())
525 {
526 GPAL.PublishSimpleEvent(
527 GPALEventType.ERROR,
528 $"No data to write for range [{range}]",
529 this,
530 GPALObjectType.Other);
531 }
532 else if (string.IsNullOrEmpty(range))
533 {
534 GPAL.PublishSimpleEvent(
535 GPALEventType.ERROR,
536 "Range cannot be empty",
537 this,
538 GPALObjectType.Other);
539 }
540 else
541 {
542 foreach (var kvp in _workbooks)
543 {
544 try
545 {
546 var worksheet = GetWorksheet(kvp.Value);
547 if (worksheet != null)
548 {
549 foreach (var rangeData in _rangesData)
550 {
551 ExcelHelper.WriteRange(worksheet, range, rangeData.Data);
552 }
553 }
554 else
555 {
556 GPAL.PublishSimpleEvent(
557 GPALEventType.ERROR,
558 $"Cannot write range [{range}] to [{kvp.Key}]: Invalid sheet",
559 this,
560 GPALObjectType.Other);
561 }
562 }
563 catch (Exception ex)
564 {
565 GPAL.PublishSimpleEvent(
566 GPALEventType.ERROR,
567 $"Failed to write range [{range}] to [{kvp.Key}]",
568 this,
569 GPALObjectType.Other, ex);
570 }
571 }
572 }
573
574 return this;
575 }
576
582 public IAllowExcelOperations ClearRanges()
583 {
584 ReopenIfClosed();
585 _rangesData.Clear();
586 _differences.Clear();
587 GPAL.PublishSimpleEvent(
588 GPALEventType.INFO,
589 "Cleared range data and comparison differences",
590 this,
591 GPALObjectType.GPALExcel);
592
593 return this;
594 }
595
602 public IAllowExcelCellSettings WithCell(string cell)
603 {
604 ReopenIfClosed();
605
606 if (string.IsNullOrEmpty(cell))
607 {
608 GPAL.PublishSimpleEvent(
609 GPALEventType.ERROR,
610 "Cell address cannot be empty",
611 this,
612 GPALObjectType.GPALExcel);
613 }
614 else
615 {
616
617 _currentCell = cell;
618 }
619
620 return this;
621 }
622
628 public IAllowExcelSearchAndReplaceSettings WithSearchValue(string value)
629 {
630 ReopenIfClosed();
631
632 if (string.IsNullOrEmpty(value))
633 {
634 GPAL.PublishSimpleEvent(
635 GPALEventType.ERROR,
636 "Search value cannot be empty",
637 this,
638 GPALObjectType.GPALExcel);
639 }
640 else
641 _replaceValue = value;
642
643 return this;
644 }
645
654 public IAllowExcelOperations WithColumn(string column)
655 {
656 ReopenIfClosed();
657
658 if (true == _operationPerformed)
659 {
660 _rangesData.Clear();
661 _operationPerformed = false;
662 _differences.Clear();
663 }
664
665 if (string.IsNullOrEmpty(column))
666 {
667 GPAL.PublishSimpleEvent(
668 GPALEventType.ERROR,
669 "Column cannot be empty",
670 this,
671 GPALObjectType.GPALExcel);
672 }
673 else
674 {
675 foreach (var kvp in _workbooks)
676 {
677 try
678 {
679 var worksheet = GetWorksheet(kvp.Value);
680 if (worksheet != null)
681 {
682 var values = ExcelHelper.GetColumnValues(worksheet, column);
683 var grid = GPAL.GridForType<string>();
684 foreach (var value in values)
685 grid.AddRow(new List<string> { value });
686 _rangesData.Add((_currentSheetName ?? _currentSheetIndex.ToString() ?? "Sheet1", column, grid));
687 break;
688 }
689 GPAL.PublishSimpleEvent(
690 GPALEventType.ERROR,
691 $"Cannot read column [{column}] from [{kvp.Key}]: Invalid sheet",
692 this,
693 GPALObjectType.GPALExcel);
694 }
695 catch (Exception ex)
696 {
697 GPAL.PublishSimpleEvent(
698 GPALEventType.ERROR,
699 $"Failed to read column [{column}] from [{kvp.Key}]",
700 this,
701 GPALObjectType.GPALExcel, ex);
702 }
703 }
704 }
705
706 return this;
707 }
708
716 public IAllowExcelOperations WithRowNumber(int rowNumber)
717 {
718 ReopenIfClosed();
719
720 if (true == _operationPerformed)
721 {
722 _rangesData.Clear();
723 _operationPerformed = false;
724 _differences.Clear();
725 }
726
727 if (rowNumber < 1)
728 {
729 GPAL.PublishSimpleEvent(
730 GPALEventType.ERROR,
731 $"Invalid row number [{rowNumber}]",
732 this,
733 GPALObjectType.GPALExcel);
734 }
735 else
736 {
737 foreach (var kvp in _workbooks)
738 {
739 try
740 {
741 var worksheet = GetWorksheet(kvp.Value);
742 if (worksheet != null)
743 {
744 var values = ExcelHelper.GetRowValues(worksheet, rowNumber);
745 var grid = GPAL.GridForType<string>();
746 grid.AddRow(values.ToList());
747 _rangesData.Add((_currentSheetName ?? _currentSheetIndex.ToString() ?? "Sheet1", $"Row{rowNumber}", grid));
748 break;
749 }
750 else
751 {
752 GPAL.PublishSimpleEvent(
753 GPALEventType.ERROR,
754 $"Cannot read row [{rowNumber}] from [{kvp.Key}]: Invalid sheet",
755 this,
756 GPALObjectType.GPALExcel);
757 }
758 }
759 catch (Exception ex)
760 {
761 GPAL.PublishSimpleEvent(
762 GPALEventType.ERROR,
763 $"Failed to read row [{rowNumber}] from [{kvp.Key}]",
764 this,
765 GPALObjectType.GPALExcel, ex);
766 }
767 }
768 }
769
770 return this;
771 }
772
777 public IAllowExcelOperations CalculateSum(out string result)
778 {
779 result = "0";
780 ReopenIfClosed();
781
782 _operationPerformed = true;
783
784 if (!_rangesData.Any())
785 {
786 GPAL.PublishSimpleEvent(
787 GPALEventType.ERROR,
788 "No data to sum",
789 this,
790 GPALObjectType.GPALExcel);
791 }
792 else
793 {
794 try
795 {
796 var sum = _rangesData
797 .SelectMany(rd => rd.Data.SelectMany(row => row))
798 .Where(v => double.TryParse(v, out _))
799 .Sum(v => double.Parse(v));
800 result = sum.ToString();
801 }
802 catch (Exception ex)
803 {
804 GPAL.PublishSimpleEvent(
805 GPALEventType.ERROR,
806 $"Failed to calculate sum",
807 this,
808 GPALObjectType.GPALExcel, ex);
809 }
810 }
811
812 return this;
813 }
814
819 public IAllowExcelOperations CalculateCount(out string result)
820 {
821 result = "0";
822 ReopenIfClosed();
823
824 _operationPerformed = true;
825
826 if (!_rangesData.Any())
827 {
828 GPAL.PublishSimpleEvent(
829 GPALEventType.ERROR,
830 "No data to count",
831 this,
832 GPALObjectType.GPALExcel);
833 }
834 else
835 {
836 try
837 {
838 var count = _rangesData
839 .SelectMany(rd => rd.Data.SelectMany(row => row))
840 .Count(v => !string.IsNullOrEmpty(v));
841 result = count.ToString();
842 }
843 catch (Exception ex)
844 {
845 GPAL.PublishSimpleEvent(
846 GPALEventType.ERROR,
847 $"Failed to calculate count",
848 this,
849 GPALObjectType.GPALExcel, ex);
850 }
851 }
852
853 return this;
854 }
855
861 public IAllowExcelOperations CompareToGrid(IGPALGrid<string> grid)
862 {
863 ReopenIfClosed();
864 _operationPerformed = true;
865
866 if (!_rangesData.Any())
867 {
868 GPAL.PublishSimpleEvent(
869 GPALEventType.ERROR,
870 "No range defined for comparison",
871 this,
872 GPALObjectType.GPALExcel);
873 }
874 else
875 {
876 foreach (var kvp in _workbooks)
877 {
878 try
879 {
880 var data2 = grid;
881 foreach (var rangeData in _rangesData)
882 {
883 try
884 {
885 var ws1 = GetWorksheet(kvp.Value, rangeData.Sheet);
886 if (ws1 == null)
887 {
888 GPAL.PublishSimpleEvent(
889 GPALEventType.ERROR,
890 $"Invalid sheet for comparison in [{kvp.Key}]: [{rangeData.Sheet}]",
891 this,
892 GPALObjectType.GPALExcel);
893 continue;
894 }
895 var data1 = ExcelHelper.ReadRange(ws1, rangeData.Range);
896 var differences = ExcelHelper.CompareRanges(data1, data2);
897 foreach (var diff in differences)
898 {
899 _differences.Add(new ExcelHelper.CellDifference
900 {
901 CellAddress = diff.CellAddress,
902 OldValue = diff.OldValue,
903 NewValue = diff.NewValue,
904 SourceRange = rangeData.Range,
905 TargetRange = "Grid"
906 });
907 }
908 }
909 catch (Exception ex)
910 {
911 GPAL.PublishSimpleEvent(
912 GPALEventType.ERROR,
913 $"Failed to compare range [{rangeData.Range}] in [{kvp.Key}]",
914 this,
915 GPALObjectType.GPALExcel, ex);
916 }
917 }
918 }
919 catch (Exception ex)
920 {
921 GPAL.PublishSimpleEvent(
922 GPALEventType.ERROR,
923 $"Failed to compare with grid in [{kvp.Key}]",
924 this,
925 GPALObjectType.GPALExcel, ex);
926 }
927 }
928 }
929 return this;
930 }
931
939 public IAllowExcelOperations CompareToFile(GPALFile gpalFile)
940 {
941 ReopenIfClosed();
942 _operationPerformed = true;
943
944 if (gpalFile == null)
945 {
946 GPAL.PublishSimpleEvent(
947 GPALEventType.ERROR,
948 "Comparison file cannot be null",
949 this,
950 GPALObjectType.GPALExcel);
951 }
952 else if (!_rangesData.Any())
953 {
954 GPAL.PublishSimpleEvent(
955 GPALEventType.ERROR,
956 "No range defined for comparison",
957 this,
958 GPALObjectType.GPALExcel);
959 }
960 else
961 {
962 try
963 {
964 var comparePaths = new List<string>();
965 foreach (var pattern in gpalFile.Filenames)
966 {
967 try
968 {
969 string directory = Path.GetDirectoryName(pattern) ?? Directory.GetCurrentDirectory();
970 string fileName = Path.GetFileName(pattern);
971 var matchingFiles = Directory.GetFiles(directory, fileName, SearchOption.TopDirectoryOnly);
972 comparePaths.AddRange(matchingFiles);
973 }
974 catch (Exception ex)
975 {
976 GPAL.PublishSimpleEvent(
977 GPALEventType.ERROR,
978 $"Failed to resolve wildcard pattern [{pattern}]",
979 this,
980 GPALObjectType.GPALExcel, ex);
981 }
982 }
983 comparePaths = comparePaths.Distinct().ToList();
984
985 foreach (var kvp in _workbooks)
986 {
987 var worksheet = GetWorksheet(kvp.Value);
988 if (worksheet == null)
989 {
990 GPAL.PublishSimpleEvent(
991 GPALEventType.ERROR,
992 $"Invalid sheet in [{kvp.Key}]",
993 this,
994 GPALObjectType.GPALExcel);
995 continue;
996 }
997 foreach (var comparePath in comparePaths)
998 {
999 try
1000 {
1001 using var compareWorkbook = new XLWorkbook(comparePath);
1002 // asking by a name that was never set reports a failed lookup for a sheet nobody named
1003 var compareWorksheet = true == string.IsNullOrEmpty(_currentSheetName)
1004 ? GetWorksheet(compareWorkbook, _currentSheetIndex)
1005 : GetWorksheet(compareWorkbook, _currentSheetName) ?? GetWorksheet(compareWorkbook, _currentSheetIndex);
1006 if (compareWorksheet == null)
1007 {
1008 GPAL.PublishSimpleEvent(
1009 GPALEventType.ERROR,
1010 $"Sheet [{_currentSheetName ?? _currentSheetIndex.ToString()}] not found in [{comparePath}]",
1011 this,
1012 GPALObjectType.GPALExcel);
1013 continue;
1014 }
1015 foreach (var rangeData in _rangesData)
1016 {
1017 try
1018 {
1019 var data1 = ExcelHelper.ReadRange(worksheet, rangeData.Range);
1020 var data2 = ExcelHelper.ReadRange(compareWorksheet, rangeData.Range);
1021 var differences = ExcelHelper.CompareRanges(data1, data2);
1022 foreach (var diff in differences)
1023 {
1024 _differences.Add(new ExcelHelper.CellDifference
1025 {
1026 CellAddress = diff.CellAddress,
1027 OldValue = diff.OldValue,
1028 NewValue = diff.NewValue,
1029 SourceRange = rangeData.Range,
1030 TargetRange = $"{comparePath}:{rangeData.Range}"
1031 });
1032 }
1033 }
1034 catch (Exception ex)
1035 {
1036 GPAL.PublishSimpleEvent(
1037 GPALEventType.ERROR,
1038 $"Failed to compare range [{rangeData.Range}] in [{kvp.Key}] with [{comparePath}]",
1039 this,
1040 GPALObjectType.GPALExcel, ex);
1041 }
1042 }
1043 }
1044 catch (Exception ex)
1045 {
1046 GPAL.PublishSimpleEvent(
1047 GPALEventType.ERROR,
1048 $"Failed to compare [{kvp.Key}] with [{comparePath}]",
1049 this,
1050 GPALObjectType.GPALExcel, ex);
1051 }
1052 }
1053 }
1054 }
1055 catch (Exception ex)
1056 {
1057 GPAL.PublishSimpleEvent(
1058 GPALEventType.EXCEPTION,
1059 $"Failed to compare with files [{string.Join(", ", gpalFile.Filenames)}]",
1060 this,
1061 GPALObjectType.GPALExcel, ex);
1062 }
1063 }
1064 return this;
1065 }
1066
1073 public IAllowExcelOperations CompareToRange(string range)
1074 {
1075 ReopenIfClosed();
1076 _operationPerformed = true;
1077
1078 if (!_rangesData.Any())
1079 {
1080 GPAL.PublishSimpleEvent(
1081 GPALEventType.ERROR,
1082 "No initial range defined for comparison",
1083 this,
1084 GPALObjectType.GPALExcel);
1085 }
1086 else if (string.IsNullOrEmpty(range))
1087 {
1088 GPAL.PublishSimpleEvent(
1089 GPALEventType.ERROR,
1090 "Comparison range cannot be empty",
1091 this,
1092 GPALObjectType.GPALExcel);
1093 }
1094 else
1095 {
1096 foreach (var kvp in _workbooks)
1097 {
1098 try
1099 {
1100 var ws2 = GetWorksheet(kvp.Value);
1101 if (ws2 == null)
1102 {
1103 GPAL.PublishSimpleEvent(
1104 GPALEventType.ERROR,
1105 $"Invalid sheet for comparison in [{kvp.Key}]",
1106 this,
1107 GPALObjectType.GPALExcel);
1108 continue;
1109 }
1110 var data2 = ExcelHelper.ReadRange(ws2, range);
1111 foreach (var rangeData in _rangesData)
1112 {
1113 try
1114 {
1115 var ws1 = GetWorksheet(kvp.Value, rangeData.Sheet);
1116 if (ws1 == null)
1117 {
1118 GPAL.PublishSimpleEvent(
1119 GPALEventType.ERROR,
1120 $"Invalid sheet for comparison in [{kvp.Key}]: [{rangeData.Sheet}]",
1121 this,
1122 GPALObjectType.GPALExcel);
1123 continue;
1124 }
1125 var data1 = ExcelHelper.ReadRange(ws1, rangeData.Range);
1126 var differences = ExcelHelper.CompareRanges(data1, data2);
1127 foreach (var diff in differences)
1128 {
1129 _differences.Add(new ExcelHelper.CellDifference
1130 {
1131 CellAddress = diff.CellAddress,
1132 OldValue = diff.OldValue,
1133 NewValue = diff.NewValue,
1134 SourceRange = rangeData.Range,
1135 TargetRange = range
1136 });
1137 }
1138 }
1139 catch (Exception ex)
1140 {
1141 GPAL.PublishSimpleEvent(
1142 GPALEventType.ERROR,
1143 $"Failed to compare range [{rangeData.Range}] in [{kvp.Key}]",
1144 this,
1145 GPALObjectType.GPALExcel, ex);
1146 }
1147 }
1148 }
1149 catch (Exception ex)
1150 {
1151 GPAL.PublishSimpleEvent(
1152 GPALEventType.ERROR,
1153 $"Failed to compare ranges in [{kvp.Key}]",
1154 this,
1155 GPALObjectType.GPALExcel, ex);
1156 }
1157 }
1158 }
1159 return this;
1160 }
1161
1169 public IAllowExcelOperations GetCompareResults(out IGPALGrid<string> results)
1170 {
1171 ReopenIfClosed();
1172
1173 results = GPAL.GridForType<string>();
1174 results.AddRow(new List<string> { "File", "Sheet", "Cell", "SourceRange", "TargetRange", "OldValue", "NewValue" });
1175
1176 try
1177 {
1178 if (_differences.Any())
1179 {
1180 foreach (var diff in _differences)
1181 {
1182 results.AddRow(new List<string>
1183 {
1184 _filePaths.FirstOrDefault() ?? "unknown",
1185 _currentSheetName ?? _currentSheetIndex.ToString(), // int.ToString is never null, so the old "unknown" arm could not be reached
1186 diff.CellAddress,
1187 diff.SourceRange,
1188 diff.TargetRange,
1189 diff.OldValue,
1190 diff.NewValue
1191 });
1192 }
1193 }
1194 }
1195 catch (Exception ex)
1196 {
1197 GPAL.PublishSimpleEvent(
1198 GPALEventType.ERROR,
1199 $"Failed to get results",
1200 this,
1201 GPALObjectType.GPALExcel, ex);
1202 }
1203
1204 return this;
1205 }
1206
1212 public IAllowExcelCellSettings WithInsertPosition(int position)
1213 {
1214 ReopenIfClosed();
1215
1216 if (position < 0)
1217 {
1218 GPAL.PublishSimpleEvent(
1219 GPALEventType.ERROR,
1220 $"Invalid insert position [{position}]",
1221 this,
1222 GPALObjectType.GPALExcel);
1223 }
1224 else
1225 _insertPosition = position;
1226
1227 return this;
1228 }
1229
1236 public IAllowExcelCellSettings WithInsertSeparator(string separator)
1237 {
1238 ReopenIfClosed();
1239
1240 if (string.IsNullOrEmpty(separator))
1241 {
1242 GPAL.PublishSimpleEvent(
1243 GPALEventType.ERROR,
1244 "Insert separator cannot be empty",
1245 this,
1246 GPALObjectType.GPALExcel);
1247 }
1248 else
1249 _insertSeparator = separator;
1250
1251 return this;
1252 }
1253
1263 public IAllowExcelOperations InsertValue(string value)
1264 {
1265 ReopenIfClosed();
1266
1267 _operationPerformed = true;
1268
1269 if (_insertPosition == null || _insertSeparator == null || string.IsNullOrEmpty(_currentCell))
1270 {
1271 GPAL.PublishSimpleEvent(
1272 GPALEventType.ERROR,
1273 $"Missing insert position, separator, or cell for value [{value}]",
1274 this,
1275 GPALObjectType.GPALExcel);
1276 }
1277 else
1278 {
1279
1280 foreach (var kvp in _workbooks)
1281 {
1282 try
1283 {
1284 var worksheet = GetWorksheet(kvp.Value);
1285 if (worksheet != null)
1286 {
1287 var cell = worksheet.Cell(_currentCell);
1288 var currentValue = cell.GetString();
1289 var newValue = _insertPosition == 0 ? $"{value}{_insertSeparator}{currentValue}" : $"{currentValue}{_insertSeparator}{value}";
1290 cell.SetValue(newValue);
1291 }
1292 else
1293 {
1294 GPAL.PublishSimpleEvent(
1295 GPALEventType.ERROR,
1296 $"Cannot insert value [{value}] in [{kvp.Key}]: Invalid sheet",
1297 this,
1298 GPALObjectType.GPALExcel);
1299 }
1300 }
1301 catch (Exception ex)
1302 {
1303 GPAL.PublishSimpleEvent(
1304 GPALEventType.ERROR,
1305 $"Failed to insert value [{value}] at cell [{_currentCell}] in [{kvp.Key}]",
1306 this,
1307 GPALObjectType.GPALExcel, ex);
1308 }
1309 }
1310 }
1311
1312 return this;
1313 }
1314
1321 public IAllowExcelOperations SetValue(string value)
1322 {
1323 ReopenIfClosed();
1324
1325 _operationPerformed = true;
1326
1327 if (string.IsNullOrEmpty(_currentCell))
1328 {
1329 GPAL.PublishSimpleEvent(
1330 GPALEventType.ERROR,
1331 $"No cell specified for setting value [{value}]",
1332 this,
1333 GPALObjectType.GPALExcel);
1334 }
1335 else
1336 {
1337 foreach (var kvp in _workbooks)
1338 {
1339 try
1340 {
1341 var worksheet = GetWorksheet(kvp.Value);
1342 if (worksheet != null)
1343 {
1344 worksheet.Cell(_currentCell).SetValue(value);
1345 }
1346 else
1347 {
1348 GPAL.PublishSimpleEvent(
1349 GPALEventType.ERROR,
1350 $"Cannot set value [{value}] in [{kvp.Key}]: Invalid sheet",
1351 this,
1352 GPALObjectType.GPALExcel);
1353 }
1354 }
1355 catch (Exception ex)
1356 {
1357 GPAL.PublishSimpleEvent(
1358 GPALEventType.ERROR,
1359 $"Failed to set value [{value}] at cell [{_currentCell}] in [{kvp.Key}]",
1360 this,
1361 GPALObjectType.GPALExcel, ex);
1362 }
1363 }
1364 }
1365
1366 return this;
1367 }
1368
1375 public IAllowExcelOperations AppendValue(string value)
1376 {
1377 ReopenIfClosed();
1378
1379 _operationPerformed = true;
1380
1381 if (string.IsNullOrEmpty(_currentCell))
1382 {
1383 GPAL.PublishSimpleEvent(
1384 GPALEventType.ERROR,
1385 $"No cell specified for appending value [{value}]",
1386 this,
1387 GPALObjectType.GPALExcel);
1388 }
1389 else
1390 {
1391 foreach (var kvp in _workbooks)
1392 {
1393 try
1394 {
1395 var worksheet = GetWorksheet(kvp.Value);
1396 if (worksheet != null)
1397 {
1398 var cell = worksheet.Cell(_currentCell);
1399 var currentValue = cell.GetString();
1400 cell.SetValue(currentValue + value);
1401 }
1402 else
1403 {
1404 GPAL.PublishSimpleEvent(
1405 GPALEventType.ERROR,
1406 $"Cannot append value [{value}] in [{kvp.Key}]: Invalid sheet",
1407 this,
1408 GPALObjectType.GPALExcel);
1409 }
1410 }
1411 catch (Exception ex)
1412 {
1413 GPAL.PublishSimpleEvent(
1414 GPALEventType.ERROR,
1415 $"Failed to append value [{value}] at cell [{_currentCell}] in [{kvp.Key}]",
1416 this,
1417 GPALObjectType.GPALExcel, ex);
1418 }
1419 }
1420 }
1421
1422 return this;
1423 }
1424
1431 public IAllowExcelOperations PrependValue(string value)
1432 {
1433 ReopenIfClosed();
1434
1435 _operationPerformed = true;
1436
1437 if (string.IsNullOrEmpty(_currentCell))
1438 {
1439 GPAL.PublishSimpleEvent(
1440 GPALEventType.ERROR,
1441 $"No cell specified for prepending value [{value}]",
1442 this,
1443 GPALObjectType.GPALExcel);
1444 }
1445 else
1446 {
1447 foreach (var kvp in _workbooks)
1448 {
1449 try
1450 {
1451 var worksheet = GetWorksheet(kvp.Value);
1452 if (worksheet != null)
1453 {
1454 var cell = worksheet.Cell(_currentCell);
1455 var currentValue = cell.GetString();
1456 cell.SetValue(value + currentValue);
1457 }
1458 else
1459 {
1460 GPAL.PublishSimpleEvent(
1461 GPALEventType.ERROR,
1462 $"Cannot prepend value [{value}] in [{kvp.Key}]: Invalid sheet",
1463 this,
1464 GPALObjectType.GPALExcel);
1465 }
1466 }
1467 catch (Exception ex)
1468 {
1469 GPAL.PublishSimpleEvent(
1470 GPALEventType.ERROR,
1471 $"Failed to prepend value [{value}] at cell [{_currentCell}] in [{kvp.Key}]",
1472 this,
1473 GPALObjectType.GPALExcel, ex);
1474 }
1475 }
1476 }
1477
1478 return this;
1479 }
1480
1487 public IAllowExcelOperations ReplaceWith(string value)
1488 {
1489 ReopenIfClosed();
1490 _operationPerformed = true;
1491
1492 if (string.IsNullOrEmpty(_replaceValue))
1493 {
1494 GPAL.PublishSimpleEvent(
1495 GPALEventType.ERROR,
1496 $"No search value for replace with [{value}]",
1497 this,
1498 GPALObjectType.GPALExcel);
1499 }
1500 else if (!_rangesData.Any())
1501 {
1502 GPAL.PublishSimpleEvent(
1503 GPALEventType.ERROR,
1504 $"No range defined for replace with [{value}]",
1505 this,
1506 GPALObjectType.GPALExcel);
1507 }
1508 else
1509 {
1510 foreach (var kvp in _workbooks)
1511 {
1512 try
1513 {
1514 var worksheet = GetWorksheet(kvp.Value);
1515 if (worksheet == null)
1516 {
1517 GPAL.PublishSimpleEvent(
1518 GPALEventType.ERROR,
1519 $"Cannot replace in [{kvp.Key}]: Invalid sheet",
1520 this,
1521 GPALObjectType.GPALExcel);
1522 }
1523 else
1524 {
1525 foreach (var rangeData in _rangesData)
1526 {
1527 ExcelHelper.SearchAndReplaceByValue(worksheet, rangeData.Range, _replaceValue, value);
1528 }
1529 }
1530 }
1531 catch (Exception ex)
1532 {
1533 GPAL.PublishSimpleEvent(
1534 GPALEventType.ERROR,
1535 $"Failed to replace [{_replaceValue}] with [{value}] in [{kvp.Key}]",
1536 this,
1537 GPALObjectType.GPALExcel, ex);
1538 }
1539 }
1540 }
1541 return this;
1542 }
1543
1563 public IAllowExcelOperations GetSearchResults(out IGPALGrid<string> results)
1564 {
1565 ReopenIfClosed();
1566
1567 results = GPAL.GridForType<string>();
1568 results.AddRow(new List<string> { "File", "Sheet", "Cell", "Row", "Column" });
1569
1570 if (string.IsNullOrEmpty(_replaceValue))
1571 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No search value, so there is nothing to find. Call .WithSearchValue first", this, GPALObjectType.GPALExcel);
1572 else if (!_rangesData.Any())
1573 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No range defined, so there is nothing to search. Call .WithRange first", this, GPALObjectType.GPALExcel);
1574 else
1575 {
1576 int found = 0;
1577
1578 foreach (var kvp in _workbooks)
1579 {
1580 try
1581 {
1582 var worksheet = GetWorksheet(kvp.Value);
1583
1584 if (null == worksheet)
1585 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Cannot search in [{kvp.Key}]: Invalid sheet", this, GPALObjectType.GPALExcel);
1586 else
1587 foreach (var rangeData in _rangesData)
1588 foreach (var hit in ExcelHelper.SearchValue(worksheet, rangeData.Range, _replaceValue))
1589 {
1590 results.AddRow(new List<string>
1591 {
1592 kvp.Key,
1593 _currentSheetName ?? _currentSheetIndex.ToString(),
1594 hit.Address,
1595 hit.Row.ToString(),
1596 hit.Column.ToString()
1597 });
1598
1599 found++;
1600 }
1601 }
1602 catch (Exception ex)
1603 {
1604 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed searching for [{_replaceValue}] in [{kvp.Key}]", this, GPALObjectType.GPALExcel, ex);
1605 }
1606 }
1607
1608 GPAL.PublishSimpleEvent(GPALEventType.INFO, $"Searching for [{_replaceValue}] found [{found}] cell(s)", this, GPALObjectType.GPALExcel);
1609 }
1610
1611 return this;
1612 }
1613
1622 public IAllowExcelOperations SaveTo(GPALFile gpalFile)
1623 {
1624 _operationPerformed = true;
1625 gpalFile = gpalFile ?? _gpalFile;
1626
1627 if (gpalFile == null)
1628 {
1629 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No GPALFile specified for save", this, GPALObjectType.GPALExcel);
1630 }
1631 else if (!_workbooks.Any())
1632 {
1633 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No workbooks loaded to save", this, GPALObjectType.GPALExcel);
1634 }
1635 else
1636 {
1637 try
1638 {
1639 var targetPaths = new List<string>();
1640 foreach (var pattern in gpalFile.Filenames)
1641 {
1642 try
1643 {
1644 // a name with no wildcard in it is the file to write, whether or not one is there
1645 // already. only a pattern is matched against the files that exist
1646 if (false == pattern.Contains("*") && false == pattern.Contains("?"))
1647 {
1648 targetPaths.Add(pattern);
1649 continue;
1650 }
1651
1652 string directory = Path.GetDirectoryName(pattern) ?? Directory.GetCurrentDirectory();
1653 string fileName = Path.GetFileName(pattern);
1654 var matchingFiles = Directory.GetFiles(directory, fileName, SearchOption.TopDirectoryOnly);
1655 targetPaths.AddRange(matchingFiles);
1656 }
1657 catch (Exception ex)
1658 {
1659 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to resolve save path [{pattern}]", this, GPALObjectType.GPALExcel, ex);
1660 }
1661 }
1662 targetPaths = targetPaths.Distinct().ToList();
1663
1664 if (!targetPaths.Any())
1665 {
1666 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"No valid target paths resolved from [{string.Join(", ", gpalFile.Filenames)}]", this, GPALObjectType.GPALExcel);
1667 }
1668 else
1669 {
1670 foreach (var kvp in _workbooks)
1671 {
1672 try
1673 {
1674 if (targetPaths.Contains(kvp.Key, StringComparer.OrdinalIgnoreCase))
1675 {
1676 try
1677 {
1678 kvp.Value.SaveAs(kvp.Key);
1679 GPAL.PublishSimpleEvent(GPALEventType.INFO, $"Saved workbook [{kvp.Key}] to [{kvp.Key}]", this, GPALObjectType.GPALExcel);
1680 }
1681 catch (Exception ex)
1682 {
1683 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to save workbook [{kvp.Key}] to [{kvp.Key}]", this, GPALObjectType.GPALExcel, ex);
1684 }
1685 }
1686 else
1687 {
1688 foreach (var targetPath in targetPaths)
1689 {
1690 try
1691 {
1692 using var targetWorkbook = new XLWorkbook();
1693 foreach (var worksheet in kvp.Value.Worksheets)
1694 {
1695 var targetWorksheet = targetWorkbook.Worksheets.Add(worksheet.Name);
1696 ExcelHelper.CopyWorksheet(worksheet, targetWorksheet);
1697 }
1698 targetWorkbook.SaveAs(targetPath);
1699 GPAL.PublishSimpleEvent(GPALEventType.INFO, $"Saved unmatched workbook [{kvp.Key}] to [{targetPath}]", this, GPALObjectType.GPALExcel);
1700 }
1701 catch (Exception ex)
1702 {
1703 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to save unmatched workbook [{kvp.Key}] to [{targetPath}]", this, GPALObjectType.GPALExcel, ex);
1704 }
1705 }
1706 }
1707 }
1708 catch (Exception ex)
1709 {
1710 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to process workbook [{kvp.Key}]", this, GPALObjectType.GPALExcel, ex);
1711 }
1712 }
1713 }
1714 }
1715 catch (Exception ex)
1716 {
1717 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to resolve save paths for [{string.Join(", ", gpalFile.Filenames)}]", this, GPALObjectType.GPALExcel, ex);
1718 }
1719 }
1720
1721 return this;
1722 }
1723
1731 public IAllowExcelOperations SaveTo(ref IGPALGrid<string> grid)
1732 {
1733 if (null == grid)
1734 grid = GPAL.GridForType<string>();
1735
1736 {
1737 if (!_workbooks.Any() && _filePaths.Any())
1738 {
1739 foreach (var path in _filePaths)
1740 LoadFile(path);
1741 }
1742 if (!_rangesData.Any())
1743 {
1744 GPAL.PublishSimpleEvent(
1745 GPALEventType.ERROR,
1746 "No data to save to grid",
1747 this,
1748 GPALObjectType.GPALExcel);
1749 }
1750 else
1751 {
1752 _operationPerformed = true;
1753
1754 GPAL.PublishSimpleEvent(
1755 GPALEventType.INFO,
1756 $"Saving [{_rangesData.Sum(rangeData => rangeData.Data.Count())}] rows from [{_rangesData.Count}] ranges to grid.",
1757 this,
1758 GPALObjectType.GPALExcel);
1759
1760 try
1761 {
1762 foreach (var rangeData in _rangesData)
1763 {
1764 foreach (var row in rangeData.Data)
1765 grid.AddRow(row);
1766 }
1767 }
1768 catch (Exception ex)
1769 {
1770 GPAL.PublishSimpleEvent(
1771 GPALEventType.ERROR,
1772 $"Failed to save to grid",
1773 this,
1774 GPALObjectType.GPALExcel, ex);
1775 }
1776 }
1777 }
1778
1779 return this;
1780 }
1781
1789 public IAllowExcelOperations SaveTo(string sheetName)
1790 {
1791 if (!_workbooks.Any() && _filePaths.Any())
1792 {
1793 foreach (var path in _filePaths)
1794 LoadFile(path);
1795 }
1796 if (!_rangesData.Any())
1797 {
1798 GPAL.PublishSimpleEvent(
1799 GPALEventType.ERROR,
1800 $"No data to write to sheet [{sheetName}]",
1801 this,
1802 GPALObjectType.GPALExcel);
1803 }
1804 else if (string.IsNullOrEmpty(sheetName))
1805 {
1806 GPAL.PublishSimpleEvent(
1807 GPALEventType.ERROR,
1808 "Sheet name cannot be empty",
1809 this,
1810 GPALObjectType.GPALExcel);
1811 }
1812 else
1813 {
1814 _operationPerformed = true;
1815
1816 GPAL.PublishSimpleEvent(
1817 GPALEventType.INFO,
1818 $"Saving [{_rangesData.Count}] ranges to sheet [{sheetName}] of [{_workbooks.Count}] workbooks.",
1819 this,
1820 GPALObjectType.GPALExcel);
1821
1822 foreach (var kvp in _workbooks)
1823 {
1824 try
1825 {
1826 var worksheet = kvp.Value.Worksheets.Any(ws => ws.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase))
1827 ? kvp.Value.Worksheet(sheetName)
1828 : kvp.Value.Worksheets.Add(sheetName);
1829 string target;
1830 if (!string.IsNullOrEmpty(_currentCell))
1831 {
1832 target = _currentCell;
1833 worksheet.Cell(target).SetValue(_rangesData.Last().Data.SelectMany(row => row).FirstOrDefault() ?? "");
1834 }
1835 else
1836 {
1837 target = _rangesData.Last().Range;
1838 foreach (var rangeData in _rangesData)
1839 {
1840 ExcelHelper.WriteRange(worksheet, rangeData.Range, rangeData.Data);
1841 }
1842 }
1843 }
1844 catch (Exception ex)
1845 {
1846 GPAL.PublishSimpleEvent(
1847 GPALEventType.ERROR,
1848 $"Failed to write to sheet [{sheetName}] in [{kvp.Key}]",
1849 this,
1850 GPALObjectType.GPALExcel, ex);
1851 }
1852 }
1853 }
1854
1855 return this;
1856 }
1857
1865 public IAllowExcelOperations SaveTo(int sheetIndex)
1866 {
1867 if (!_workbooks.Any() && _filePaths.Any())
1868 {
1869 foreach (var path in _filePaths)
1870 LoadFile(path);
1871 }
1872 if (!_rangesData.Any())
1873 {
1874 GPAL.PublishSimpleEvent(
1875 GPALEventType.ERROR,
1876 $"No data to write to sheet index [{sheetIndex}]",
1877 this,
1878 GPALObjectType.GPALExcel);
1879 }
1880 else if (sheetIndex < 1)
1881 {
1882 GPAL.PublishSimpleEvent(
1883 GPALEventType.ERROR,
1884 $"Invalid sheet index [{sheetIndex}]",
1885 this,
1886 GPALObjectType.GPALExcel);
1887 }
1888 else
1889 {
1890 _operationPerformed = true;
1891
1892 GPAL.PublishSimpleEvent(
1893 GPALEventType.INFO,
1894 $"Saving [{_rangesData.Count}] ranges to sheet index [{sheetIndex}] of [{_workbooks.Count}] workbooks.",
1895 this,
1896 GPALObjectType.GPALExcel);
1897
1898 foreach (var kvp in _workbooks)
1899 {
1900 try
1901 {
1902 var worksheet = kvp.Value.Worksheets.ElementAtOrDefault(sheetIndex - 1) ?? kvp.Value.Worksheets.Add($"Sheet{sheetIndex}");
1903 string target;
1904 if (!string.IsNullOrEmpty(_currentCell))
1905 {
1906 target = _currentCell;
1907 worksheet.Cell(target).SetValue(_rangesData.Last().Data.SelectMany(row => row).FirstOrDefault() ?? "");
1908 }
1909 else
1910 {
1911 target = _rangesData.Last().Range;
1912 foreach (var rangeData in _rangesData)
1913 {
1914 ExcelHelper.WriteRange(worksheet, rangeData.Range, rangeData.Data);
1915 }
1916 }
1917 }
1918 catch (Exception ex)
1919 {
1920 GPAL.PublishSimpleEvent(
1921 GPALEventType.ERROR,
1922 $"Failed to write to sheet index [{sheetIndex}] in [{kvp.Key}]",
1923 this,
1924 GPALObjectType.GPALExcel, ex);
1925 }
1926 }
1927 }
1928
1929 return this;
1930 }
1931
1937 public void Close(bool saveOnClose)
1938 {
1939 if (_isClosed)
1940 {
1941 GPAL.PublishSimpleEvent(
1942 GPALEventType.INFO,
1943 $"GPALExcel instance already closed [{_gpalFile?.Filenames?.FirstOrDefault() ?? "unknown"}]",
1944 this,
1945 GPALObjectType.GPALExcel);
1946 }
1947 else
1948 {
1949 _operationPerformed = false;
1950
1951 foreach (var kvp in _workbooks)
1952 {
1953 try
1954 {
1955 if (saveOnClose)
1956 {
1957 kvp.Value.SaveAs(kvp.Key);
1958 }
1959 kvp.Value.Dispose();
1960 }
1961 catch (Exception ex)
1962 {
1963 GPAL.PublishSimpleEvent(
1964 GPALEventType.ERROR,
1965 $"Failed to close workbook [{kvp.Key}]",
1966 this,
1967 GPALObjectType.GPALExcel, ex);
1968 }
1969 }
1970 _workbooks.Clear();
1971 _filePaths.Clear();
1972 _rangesData.Clear();
1973 _currentSheetName = null;
1974 _currentSheetIndex = -1;
1975 _insertPosition = 0;
1976 _insertSeparator = " ";
1977 _currentCell = null;
1978 _isClosed = true;
1979 }
1980 }
1981
1989 private IXLWorksheet GetWorksheet(XLWorkbook workbook)
1990 {
1991 if (!string.IsNullOrEmpty(_currentSheetName))
1992 {
1993 return workbook.Worksheets.FirstOrDefault(ws => ws.Name.Equals(_currentSheetName, StringComparison.OrdinalIgnoreCase));
1994 }
1995 if (-1 != _currentSheetIndex)
1996 {
1997 // WithSheet names sheet one as 1, which is the sheet a workbook opens on
1998 return workbook.Worksheets.ElementAtOrDefault(_currentSheetIndex - 1);
1999 }
2000 return null;
2001 }
2002
2011 internal static IXLWorksheet GetWorksheet(XLWorkbook workbook, string sheetIdentifier)
2012 {
2013 try
2014 {
2015 if (workbook.TryGetWorksheet(sheetIdentifier, out IXLWorksheet worksheet))
2016 {
2017 return worksheet;
2018 }
2019
2020 if (int.TryParse(sheetIdentifier, out int sheetIndex) && sheetIndex > 0)
2021 {
2022 try
2023 {
2024 worksheet = workbook.Worksheet(sheetIndex);
2025 return worksheet;
2026 }
2027 catch
2028 {
2029 }
2030 }
2031
2032 GPAL.PublishSimpleEvent(Enums.GPALEventType.ERROR, $"Worksheet [{sheetIdentifier}] not found in workbook and no default name provided.");
2033 return null;
2034 }
2035 catch (Exception ex)
2036 {
2037 GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to get worksheet [{sheetIdentifier}]", null, Enums.GPALObjectType.None, ex);
2038 return null;
2039 }
2040 }
2041
2048 internal static IXLWorksheet GetWorksheet(XLWorkbook workbook, int sheetIndex)
2049 {
2050 try
2051 {
2052 if (null != workbook.Worksheet(sheetIndex))
2053 return workbook.Worksheet(sheetIndex);
2054
2055 GPAL.PublishSimpleEvent(Enums.GPALEventType.ERROR, $"Worksheet [{sheetIndex}] not found in workbook and no default name provided.");
2056 return null;
2057 }
2058 catch (Exception ex)
2059 {
2060 GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to get worksheet [{sheetIndex}]", null, Enums.GPALObjectType.None, ex);
2061 return null;
2062 }
2063 }
2064 }
2065}
GenerallyPositive.GPALExcel.WriteRange
IAllowExcelOperations WriteRange(string range)
Writes the range data previously read via WithRange(string) (or another data-producing operation) to ...
Definition GPALExcel.cs:518
GenerallyPositive.GPALExcel.ClearRanges
IAllowExcelOperations ClearRanges()
Clears any range data read via WithRange(string), WithColumn(string), or WithRowNumber(int),...
Definition GPALExcel.cs:582
GenerallyPositive.GPALExcel.WithSearchValue
IAllowExcelSearchAndReplaceSettings WithSearchValue(string value)
Sets the value to search for in subsequent ReplaceWith(string) operations.
Definition GPALExcel.cs:628
GenerallyPositive.GPALExcel.SaveTo
IAllowExcelOperations SaveTo(int sheetIndex)
Writes the previously read range data into the sheet at the given 1-based index of every loaded workb...
Definition GPALExcel.cs:1865
GenerallyPositive.GPALExcel.WithFile
IAllowExcelSheetSelection WithFile(GPALFile gpalFile)
Resets this instance and loads the workbook(s) matching gpalFile 's filenames (which may include wild...
Definition GPALExcel.cs:150
GenerallyPositive.GPALExcel.CompareToRange
IAllowExcelOperations CompareToRange(string range)
Compares each previously read range against range on the currently selected sheet of every loaded wo...
Definition GPALExcel.cs:1073
GenerallyPositive.GPALExcel.CalculateCount
IAllowExcelOperations CalculateCount(out string result)
Counts every non-empty value across all previously read range data.
Definition GPALExcel.cs:819
GenerallyPositive.GPALExcel.CompareToFile
IAllowExcelOperations CompareToFile(GPALFile gpalFile)
Compares each previously read range against the same range/sheet in the workbook(s) matching gpalFile...
Definition GPALExcel.cs:939
GenerallyPositive.GPALExcel.InsertValue
IAllowExcelOperations InsertValue(string value)
Inserts value into the cell selected via WithCell(string) on every loaded workbook,...
Definition GPALExcel.cs:1263
GenerallyPositive.GPALExcel.SetValue
IAllowExcelOperations SetValue(string value)
Sets the value of the cell selected via WithCell(string) on every loaded workbook,...
Definition GPALExcel.cs:1321
GenerallyPositive.GPALExcel.WithSheet
IAllowExcelOperations WithSheet(int sheetIndex)
Selects the sheet at the given 1-based index for subsequent Excel operations on every loaded workbook...
Definition GPALExcel.cs:371
GenerallyPositive.GPALExcel.Close
void Close(bool saveOnClose)
Closes every loaded workbook, optionally saving each back to its source path first,...
Definition GPALExcel.cs:1937
GenerallyPositive.GPALExcel.SaveTo
IAllowExcelOperations SaveTo(GPALFile gpalFile)
Saves the loaded workbook(s) to the file(s) matching gpalFile 's filenames (which may include wildcar...
Definition GPALExcel.cs:1622
GenerallyPositive.GPALExcel.ToGPALObject
IGPALExcel ToGPALObject()
Returns this instance as an IGPALExcel, completing the fluent configuration chain.
Definition GPALExcel.cs:141
GenerallyPositive.GPALExcel.GetSearchResults
IAllowExcelOperations GetSearchResults(out IGPALGrid< string > results)
Every cell in the range holding the search value, as a grid shaped the way GetCompareResults shapes i...
Definition GPALExcel.cs:1563
GenerallyPositive.GPALExcel.WithNewFile
IAllowExcelSheetSelection WithNewFile(GPALFile gpalFile)
Starts a workbook of the workflow's own at gpalFile , holding one sheet and nothing else....
Definition GPALExcel.cs:211
GenerallyPositive.GPALExcel.WithRange
IAllowExcelOperations WithRange(string range)
Reads the given cell range from the currently selected sheet of every loaded workbook and stores the ...
Definition GPALExcel.cs:458
GenerallyPositive.GPALExcel.ReplaceWith
IAllowExcelOperations ReplaceWith(string value)
Replaces every occurrence of the value set via WithSearchValue(string) with value ,...
Definition GPALExcel.cs:1487
GenerallyPositive.GPALExcel.WithNewSheet
IAllowExcelOperations WithNewSheet(string sheetName)
Adds a sheet named sheetName to every loaded workbook and works on it from here. A workbook already ...
Definition GPALExcel.cs:261
GenerallyPositive.GPALExcel.WithInsertSeparator
IAllowExcelCellSettings WithInsertSeparator(string separator)
Sets the separator that InsertValue(string) places between the inserted value and the cell's existing...
Definition GPALExcel.cs:1236
GenerallyPositive.GPALExcel.GetCompareResults
IAllowExcelOperations GetCompareResults(out IGPALGrid< string > results)
Returns the cell-level differences accumulated by the CompareTo* methods (CompareToGrid(IGPALGrid<str...
Definition GPALExcel.cs:1169
GenerallyPositive.GPALExcel.InputFrom
IAllowExcelOperations InputFrom(IGPALGrid< string > grid)
Writes grid into the current sheet, starting at the cell WithCell(string) named or A1....
Definition GPALExcel.cs:303
GenerallyPositive.GPALExcel.AppendValue
IAllowExcelOperations AppendValue(string value)
Appends value to the end of the existing content of the cell selected via WithCell(string) on every ...
Definition GPALExcel.cs:1375
GenerallyPositive.GPALExcel.WithInsertPosition
IAllowExcelCellSettings WithInsertPosition(int position)
Sets where InsertValue(string) places its value relative to the current cell content: 0 inserts befor...
Definition GPALExcel.cs:1212
GenerallyPositive.GPALExcel.WithCell
IAllowExcelCellSettings WithCell(string cell)
Selects a single cell address (e.g. "B3") on the currently selected sheet as the target for subsequen...
Definition GPALExcel.cs:602
GenerallyPositive.GPALExcel.SaveTo
IAllowExcelOperations SaveTo(ref IGPALGrid< string > grid)
Appends every row from the previously read range data into grid . The grid is taken by reference bec...
Definition GPALExcel.cs:1731
GenerallyPositive.GPALExcel.WithRowNumber
IAllowExcelOperations WithRowNumber(int rowNumber)
Reads every value in the given row from the currently selected sheet of the first loaded workbook tha...
Definition GPALExcel.cs:716
GenerallyPositive.GPALExcel.CompareToGrid
IAllowExcelOperations CompareToGrid(IGPALGrid< string > grid)
Compares each previously read range against grid , recording any cell-level differences for retrieval...
Definition GPALExcel.cs:861
GenerallyPositive.GPALExcel.WithSheet
IAllowExcelOperations WithSheet(string sheetName)
Selects the sheet with the given name (case-insensitive) for subsequent Excel operations on every loa...
Definition GPALExcel.cs:413
GenerallyPositive.GPALExcel.WithColumn
IAllowExcelOperations WithColumn(string column)
Reads every value in the given column from the currently selected sheet of the first loaded workbook ...
Definition GPALExcel.cs:654
GenerallyPositive.GPALExcel.SaveTo
IAllowExcelOperations SaveTo(string sheetName)
Writes the previously read range data into the sheet named sheetName of every loaded workbook,...
Definition GPALExcel.cs:1789
GenerallyPositive.GPALExcel.CalculateSum
IAllowExcelOperations CalculateSum(out string result)
Sums every numeric value across all previously read range data (non-numeric values are ignored).
Definition GPALExcel.cs:777
GenerallyPositive.GPALExcel.PrependValue
IAllowExcelOperations PrependValue(string value)
Prepends value to the beginning of the existing content of the cell selected via WithCell(string) on...
Definition GPALExcel.cs:1431
GenerallyPositive.GPALFile
GPAL File object instantied with GPAL.File Used to load tokens into a GPALGrid [rows/columns].
Definition GPALFile.cs:36
GenerallyPositive.GPALFile.Filenames
List< string > Filenames
Get the list of filenames.
Definition GPALFile.cs:540
GenerallyPositive.GPALFile.Filename
string Filename
We have only one file, accessing it.
Definition GPALFile.cs:468
GenerallyPositive.GPAL
Everything starts here, all the GPAL controls and global settings using fluent syntax are here....
Definition GPAL.cs:49
GenerallyPositive.GPAL.PublishSimpleEvent
static void PublishSimpleEvent(GPALEventType gPALEventType, string msg, dynamic gPALObject=null, Enums.GPALObjectType gPALObjectType=GPALObjectType.None, Exception ex=null)
Publish a message to either the information channel or exception channel (if exception passed in) Pub...
Definition GPAL.cs:2568