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
209 public IAllowExcelOperations WithSheet(int sheetIndex)
210 {
211 ReopenIfClosed();
212
213 if (!_workbooks.Any())
214 {
215 GPAL.PublishSimpleEvent(
216 GPALEventType.ERROR,
217 "No workbooks loaded",
218 this,
219 GPALObjectType.GPALExcel);
220 }
221 else
222 {
223
224 int indexErrors = 0;
225
226 foreach (var kvp in _workbooks)
227 if (1 > sheetIndex || sheetIndex > kvp.Value.Worksheets.Count)
228 indexErrors += 1;
229
230 if (indexErrors == _workbooks.Count)
231 GPAL.PublishSimpleEvent(
232 GPALEventType.ERROR,
233 $"Invalid sheet index. No workbook contains sheet [{sheetIndex}]",
234 this,
235 GPALObjectType.GPALExcel);
236 else
237 {
238 _currentSheetName = null;
239 _currentSheetIndex = sheetIndex;
240 }
241 }
242
243 return this;
244 }
245
251 public IAllowExcelOperations WithSheet(string sheetName)
252 {
253 ReopenIfClosed();
254
255 if (string.IsNullOrEmpty(sheetName))
256 {
257 GPAL.PublishSimpleEvent(
258 GPALEventType.ERROR,
259 "Sheet name cannot be empty",
260 this,
261 GPALObjectType.GPALExcel);
262 }
263 else
264 {
265 int nameErrors = 0;
266
267 foreach (var kvp in _workbooks)
268 {
269 if (!kvp.Value.Worksheets.Any(ws => ws.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase)))
270 nameErrors += 1;
271 }
272
273 if (nameErrors == _workbooks.Count)
274 GPAL.PublishSimpleEvent(
275 GPALEventType.ERROR,
276 $"Sheet [{sheetName}] not found in any workbooks",
277 this,
278 GPALObjectType.GPALExcel);
279 else
280 {
281 _currentSheetName = sheetName;
282 _currentSheetIndex = -1;
283 }
284 }
285
286 return this;
287 }
288
296 public IAllowExcelOperations WithRange(string range)
297 {
298 ReopenIfClosed();
299
300 if (string.IsNullOrEmpty(range))
301 {
302 GPAL.PublishSimpleEvent(
303 GPALEventType.ERROR,
304 "Range cannot be empty",
305 this,
306 GPALObjectType.Other);
307 }
308 else
309 {
310 if (true == _operationPerformed)
311 {
312 _rangesData.Clear();
313 _operationPerformed = false;
314 _differences.Clear();
315 }
316
317 foreach (var kvp in _workbooks)
318 {
319 var worksheet = GetWorksheet(kvp.Value);
320 try
321 {
322 if (worksheet != null)
323 {
324 var rangeData = ExcelHelper.ReadRange(worksheet, range);
325 _rangesData.Add((_currentSheetName ?? _currentSheetIndex.ToString() ?? "Sheet1", range, rangeData));
326 }
327 else
328 {
329 GPAL.PublishSimpleEvent(
330 GPALEventType.ERROR,
331 $"Cannot read range [{range}] from [{worksheet?.Name ?? "unknown"}]: Invalid sheet",
332 this,
333 GPALObjectType.Other);
334 }
335 }
336 catch (Exception ex)
337 {
338 GPAL.PublishSimpleEvent(
339 GPALEventType.ERROR,
340 $"Failed to read range [{range}] from [{worksheet?.Name ?? "unknown"}]",
341 this,
342 GPALObjectType.Other, ex);
343 }
344 }
345 }
346
347 return this;
348 }
349
356 public IAllowExcelOperations WriteRange(string range)
357 {
358 ReopenIfClosed();
359
360 _operationPerformed = true;
361
362 if (!_rangesData.Any())
363 {
364 GPAL.PublishSimpleEvent(
365 GPALEventType.ERROR,
366 $"No data to write for range [{range}]",
367 this,
368 GPALObjectType.Other);
369 }
370 else if (string.IsNullOrEmpty(range))
371 {
372 GPAL.PublishSimpleEvent(
373 GPALEventType.ERROR,
374 "Range cannot be empty",
375 this,
376 GPALObjectType.Other);
377 }
378 else
379 {
380 foreach (var kvp in _workbooks)
381 {
382 try
383 {
384 var worksheet = GetWorksheet(kvp.Value);
385 if (worksheet != null)
386 {
387 foreach (var rangeData in _rangesData)
388 {
389 ExcelHelper.WriteRange(worksheet, range, rangeData.Data);
390 }
391 }
392 else
393 {
394 GPAL.PublishSimpleEvent(
395 GPALEventType.ERROR,
396 $"Cannot write range [{range}] to [{kvp.Key}]: Invalid sheet",
397 this,
398 GPALObjectType.Other);
399 }
400 }
401 catch (Exception ex)
402 {
403 GPAL.PublishSimpleEvent(
404 GPALEventType.ERROR,
405 $"Failed to write range [{range}] to [{kvp.Key}]",
406 this,
407 GPALObjectType.Other, ex);
408 }
409 }
410 }
411
412 return this;
413 }
414
420 public IAllowExcelOperations ClearRanges()
421 {
422 ReopenIfClosed();
423 _rangesData.Clear();
424 _differences.Clear();
425 GPAL.PublishSimpleEvent(
426 GPALEventType.INFO,
427 "Cleared range data and comparison differences",
428 this,
429 GPALObjectType.GPALExcel);
430
431 return this;
432 }
433
440 public IAllowExcelCellSettings WithCell(string cell)
441 {
442 ReopenIfClosed();
443
444 if (string.IsNullOrEmpty(cell))
445 {
446 GPAL.PublishSimpleEvent(
447 GPALEventType.ERROR,
448 "Cell address cannot be empty",
449 this,
450 GPALObjectType.GPALExcel);
451 }
452 else
453 {
454
455 _currentCell = cell;
456 }
457
458 return this;
459 }
460
466 public IAllowExcelSearchAndReplaceSettings WithSearchValue(string value)
467 {
468 ReopenIfClosed();
469
470 if (string.IsNullOrEmpty(value))
471 {
472 GPAL.PublishSimpleEvent(
473 GPALEventType.ERROR,
474 "Search value cannot be empty",
475 this,
476 GPALObjectType.GPALExcel);
477 }
478 else
479 _replaceValue = value;
480
481 return this;
482 }
483
492 public IAllowExcelOperations WithColumn(string column)
493 {
494 ReopenIfClosed();
495
496 if (true == _operationPerformed)
497 {
498 _rangesData.Clear();
499 _operationPerformed = false;
500 _differences.Clear();
501 }
502
503 if (string.IsNullOrEmpty(column))
504 {
505 GPAL.PublishSimpleEvent(
506 GPALEventType.ERROR,
507 "Column cannot be empty",
508 this,
509 GPALObjectType.GPALExcel);
510 }
511 else
512 {
513 foreach (var kvp in _workbooks)
514 {
515 try
516 {
517 var worksheet = GetWorksheet(kvp.Value);
518 if (worksheet != null)
519 {
520 var values = ExcelHelper.GetColumnValues(worksheet, column);
521 var grid = GPAL.GridForType<string>();
522 foreach (var value in values)
523 grid.AddRow(new List<string> { value });
524 _rangesData.Add((_currentSheetName ?? _currentSheetIndex.ToString() ?? "Sheet1", column, grid));
525 break;
526 }
527 GPAL.PublishSimpleEvent(
528 GPALEventType.ERROR,
529 $"Cannot read column [{column}] from [{kvp.Key}]: Invalid sheet",
530 this,
531 GPALObjectType.GPALExcel);
532 }
533 catch (Exception ex)
534 {
535 GPAL.PublishSimpleEvent(
536 GPALEventType.ERROR,
537 $"Failed to read column [{column}] from [{kvp.Key}]",
538 this,
539 GPALObjectType.GPALExcel, ex);
540 }
541 }
542 }
543
544 return this;
545 }
546
554 public IAllowExcelOperations WithRowNumber(int rowNumber)
555 {
556 ReopenIfClosed();
557
558 if (true == _operationPerformed)
559 {
560 _rangesData.Clear();
561 _operationPerformed = false;
562 _differences.Clear();
563 }
564
565 if (rowNumber < 1)
566 {
567 GPAL.PublishSimpleEvent(
568 GPALEventType.ERROR,
569 $"Invalid row number [{rowNumber}]",
570 this,
571 GPALObjectType.GPALExcel);
572 }
573 else
574 {
575 foreach (var kvp in _workbooks)
576 {
577 try
578 {
579 var worksheet = GetWorksheet(kvp.Value);
580 if (worksheet != null)
581 {
582 var values = ExcelHelper.GetRowValues(worksheet, rowNumber);
583 var grid = GPAL.GridForType<string>();
584 grid.AddRow(values.ToList());
585 _rangesData.Add((_currentSheetName ?? _currentSheetIndex.ToString() ?? "Sheet1", $"Row{rowNumber}", grid));
586 break;
587 }
588 else
589 {
590 GPAL.PublishSimpleEvent(
591 GPALEventType.ERROR,
592 $"Cannot read row [{rowNumber}] from [{kvp.Key}]: Invalid sheet",
593 this,
594 GPALObjectType.GPALExcel);
595 }
596 }
597 catch (Exception ex)
598 {
599 GPAL.PublishSimpleEvent(
600 GPALEventType.ERROR,
601 $"Failed to read row [{rowNumber}] from [{kvp.Key}]",
602 this,
603 GPALObjectType.GPALExcel, ex);
604 }
605 }
606 }
607
608 return this;
609 }
610
615 public IAllowExcelOperations CalculateSum(out string result)
616 {
617 result = "0";
618 ReopenIfClosed();
619
620 _operationPerformed = true;
621
622 if (!_rangesData.Any())
623 {
624 GPAL.PublishSimpleEvent(
625 GPALEventType.ERROR,
626 "No data to sum",
627 this,
628 GPALObjectType.GPALExcel);
629 }
630 else
631 {
632 try
633 {
634 var sum = _rangesData
635 .SelectMany(rd => rd.Data.SelectMany(row => row))
636 .Where(v => double.TryParse(v, out _))
637 .Sum(v => double.Parse(v));
638 result = sum.ToString();
639 }
640 catch (Exception ex)
641 {
642 GPAL.PublishSimpleEvent(
643 GPALEventType.ERROR,
644 $"Failed to calculate sum",
645 this,
646 GPALObjectType.GPALExcel, ex);
647 }
648 }
649
650 return this;
651 }
652
657 public IAllowExcelOperations CalculateCount(out string result)
658 {
659 result = "0";
660 ReopenIfClosed();
661
662 _operationPerformed = true;
663
664 if (!_rangesData.Any())
665 {
666 GPAL.PublishSimpleEvent(
667 GPALEventType.ERROR,
668 "No data to count",
669 this,
670 GPALObjectType.GPALExcel);
671 }
672 else
673 {
674 try
675 {
676 var count = _rangesData
677 .SelectMany(rd => rd.Data.SelectMany(row => row))
678 .Count(v => !string.IsNullOrEmpty(v));
679 result = count.ToString();
680 }
681 catch (Exception ex)
682 {
683 GPAL.PublishSimpleEvent(
684 GPALEventType.ERROR,
685 $"Failed to calculate count",
686 this,
687 GPALObjectType.GPALExcel, ex);
688 }
689 }
690
691 return this;
692 }
693
699 public IAllowExcelOperations CompareToGrid(IGPALGrid<string> grid)
700 {
701 ReopenIfClosed();
702 _operationPerformed = true;
703
704 if (!_rangesData.Any())
705 {
706 GPAL.PublishSimpleEvent(
707 GPALEventType.ERROR,
708 "No range defined for comparison",
709 this,
710 GPALObjectType.GPALExcel);
711 }
712 else
713 {
714 foreach (var kvp in _workbooks)
715 {
716 try
717 {
718 var data2 = grid;
719 foreach (var rangeData in _rangesData)
720 {
721 try
722 {
723 var ws1 = GetWorksheet(kvp.Value, rangeData.Sheet);
724 if (ws1 == null)
725 {
726 GPAL.PublishSimpleEvent(
727 GPALEventType.ERROR,
728 $"Invalid sheet for comparison in [{kvp.Key}]: [{rangeData.Sheet}]",
729 this,
730 GPALObjectType.GPALExcel);
731 continue;
732 }
733 var data1 = ExcelHelper.ReadRange(ws1, rangeData.Range);
734 var differences = ExcelHelper.CompareRanges(data1, data2);
735 foreach (var diff in differences)
736 {
737 _differences.Add(new ExcelHelper.CellDifference
738 {
739 CellAddress = diff.CellAddress,
740 OldValue = diff.OldValue,
741 NewValue = diff.NewValue,
742 SourceRange = rangeData.Range,
743 TargetRange = "Grid"
744 });
745 }
746 }
747 catch (Exception ex)
748 {
749 GPAL.PublishSimpleEvent(
750 GPALEventType.ERROR,
751 $"Failed to compare range [{rangeData.Range}] in [{kvp.Key}]",
752 this,
753 GPALObjectType.GPALExcel, ex);
754 }
755 }
756 }
757 catch (Exception ex)
758 {
759 GPAL.PublishSimpleEvent(
760 GPALEventType.ERROR,
761 $"Failed to compare with grid in [{kvp.Key}]",
762 this,
763 GPALObjectType.GPALExcel, ex);
764 }
765 }
766 }
767 return this;
768 }
769
777 public IAllowExcelOperations CompareToFile(GPALFile gpalFile)
778 {
779 ReopenIfClosed();
780 _operationPerformed = true;
781
782 if (gpalFile == null)
783 {
784 GPAL.PublishSimpleEvent(
785 GPALEventType.ERROR,
786 "Comparison file cannot be null",
787 this,
788 GPALObjectType.GPALExcel);
789 }
790 else if (!_rangesData.Any())
791 {
792 GPAL.PublishSimpleEvent(
793 GPALEventType.ERROR,
794 "No range defined for comparison",
795 this,
796 GPALObjectType.GPALExcel);
797 }
798 else
799 {
800 try
801 {
802 var comparePaths = new List<string>();
803 foreach (var pattern in gpalFile.Filenames)
804 {
805 try
806 {
807 string directory = Path.GetDirectoryName(pattern) ?? Directory.GetCurrentDirectory();
808 string fileName = Path.GetFileName(pattern);
809 var matchingFiles = Directory.GetFiles(directory, fileName, SearchOption.TopDirectoryOnly);
810 comparePaths.AddRange(matchingFiles);
811 }
812 catch (Exception ex)
813 {
814 GPAL.PublishSimpleEvent(
815 GPALEventType.ERROR,
816 $"Failed to resolve wildcard pattern [{pattern}]",
817 this,
818 GPALObjectType.GPALExcel, ex);
819 }
820 }
821 comparePaths = comparePaths.Distinct().ToList();
822
823 foreach (var kvp in _workbooks)
824 {
825 var worksheet = GetWorksheet(kvp.Value);
826 if (worksheet == null)
827 {
828 GPAL.PublishSimpleEvent(
829 GPALEventType.ERROR,
830 $"Invalid sheet in [{kvp.Key}]",
831 this,
832 GPALObjectType.GPALExcel);
833 continue;
834 }
835 foreach (var comparePath in comparePaths)
836 {
837 try
838 {
839 using var compareWorkbook = new XLWorkbook(comparePath);
840 var compareWorksheet = GetWorksheet(compareWorkbook, _currentSheetName) ?? GetWorksheet(compareWorkbook, _currentSheetIndex);
841 if (compareWorksheet == null)
842 {
843 GPAL.PublishSimpleEvent(
844 GPALEventType.ERROR,
845 $"Sheet [{_currentSheetName ?? _currentSheetIndex.ToString()}] not found in [{comparePath}]",
846 this,
847 GPALObjectType.GPALExcel);
848 continue;
849 }
850 foreach (var rangeData in _rangesData)
851 {
852 try
853 {
854 var data1 = ExcelHelper.ReadRange(worksheet, rangeData.Range);
855 var data2 = ExcelHelper.ReadRange(compareWorksheet, rangeData.Range);
856 var differences = ExcelHelper.CompareRanges(data1, data2);
857 foreach (var diff in differences)
858 {
859 _differences.Add(new ExcelHelper.CellDifference
860 {
861 CellAddress = diff.CellAddress,
862 OldValue = diff.OldValue,
863 NewValue = diff.NewValue,
864 SourceRange = rangeData.Range,
865 TargetRange = $"{comparePath}:{rangeData.Range}"
866 });
867 }
868 }
869 catch (Exception ex)
870 {
871 GPAL.PublishSimpleEvent(
872 GPALEventType.ERROR,
873 $"Failed to compare range [{rangeData.Range}] in [{kvp.Key}] with [{comparePath}]",
874 this,
875 GPALObjectType.GPALExcel, ex);
876 }
877 }
878 }
879 catch (Exception ex)
880 {
881 GPAL.PublishSimpleEvent(
882 GPALEventType.ERROR,
883 $"Failed to compare [{kvp.Key}] with [{comparePath}]",
884 this,
885 GPALObjectType.GPALExcel, ex);
886 }
887 }
888 }
889 }
890 catch (Exception ex)
891 {
892 GPAL.PublishSimpleEvent(
893 GPALEventType.EXCEPTION,
894 $"Failed to compare with files [{string.Join(", ", gpalFile.Filenames)}]",
895 this,
896 GPALObjectType.GPALExcel, ex);
897 }
898 }
899 return this;
900 }
901
908 public IAllowExcelOperations CompareToRange(string range)
909 {
910 ReopenIfClosed();
911 _operationPerformed = true;
912
913 if (!_rangesData.Any())
914 {
915 GPAL.PublishSimpleEvent(
916 GPALEventType.ERROR,
917 "No initial range defined for comparison",
918 this,
919 GPALObjectType.GPALExcel);
920 }
921 else if (string.IsNullOrEmpty(range))
922 {
923 GPAL.PublishSimpleEvent(
924 GPALEventType.ERROR,
925 "Comparison range cannot be empty",
926 this,
927 GPALObjectType.GPALExcel);
928 }
929 else
930 {
931 foreach (var kvp in _workbooks)
932 {
933 try
934 {
935 var ws2 = GetWorksheet(kvp.Value);
936 if (ws2 == null)
937 {
938 GPAL.PublishSimpleEvent(
939 GPALEventType.ERROR,
940 $"Invalid sheet for comparison in [{kvp.Key}]",
941 this,
942 GPALObjectType.GPALExcel);
943 continue;
944 }
945 var data2 = ExcelHelper.ReadRange(ws2, range);
946 foreach (var rangeData in _rangesData)
947 {
948 try
949 {
950 var ws1 = GetWorksheet(kvp.Value, rangeData.Sheet);
951 if (ws1 == null)
952 {
953 GPAL.PublishSimpleEvent(
954 GPALEventType.ERROR,
955 $"Invalid sheet for comparison in [{kvp.Key}]: [{rangeData.Sheet}]",
956 this,
957 GPALObjectType.GPALExcel);
958 continue;
959 }
960 var data1 = ExcelHelper.ReadRange(ws1, rangeData.Range);
961 var differences = ExcelHelper.CompareRanges(data1, data2);
962 foreach (var diff in differences)
963 {
964 _differences.Add(new ExcelHelper.CellDifference
965 {
966 CellAddress = diff.CellAddress,
967 OldValue = diff.OldValue,
968 NewValue = diff.NewValue,
969 SourceRange = rangeData.Range,
970 TargetRange = range
971 });
972 }
973 }
974 catch (Exception ex)
975 {
976 GPAL.PublishSimpleEvent(
977 GPALEventType.ERROR,
978 $"Failed to compare range [{rangeData.Range}] in [{kvp.Key}]",
979 this,
980 GPALObjectType.GPALExcel, ex);
981 }
982 }
983 }
984 catch (Exception ex)
985 {
986 GPAL.PublishSimpleEvent(
987 GPALEventType.ERROR,
988 $"Failed to compare ranges in [{kvp.Key}]",
989 this,
990 GPALObjectType.GPALExcel, ex);
991 }
992 }
993 }
994 return this;
995 }
996
1004 public IAllowExcelOperations GetCompareResults(out IGPALGrid<string> results)
1005 {
1006 ReopenIfClosed();
1007
1008 results = GPAL.GridForType<string>();
1009 results.AddRow(new List<string> { "File", "Sheet", "Cell", "SourceRange", "TargetRange", "OldValue", "NewValue" });
1010
1011 try
1012 {
1013 if (_differences.Any())
1014 {
1015 foreach (var diff in _differences)
1016 {
1017 results.AddRow(new List<string>
1018 {
1019 _filePaths.FirstOrDefault() ?? "unknown",
1020 _currentSheetName ?? _currentSheetIndex.ToString() ?? "unknown",
1021 diff.CellAddress,
1022 diff.SourceRange,
1023 diff.TargetRange,
1024 diff.OldValue,
1025 diff.NewValue
1026 });
1027 }
1028 }
1029 }
1030 catch (Exception ex)
1031 {
1032 GPAL.PublishSimpleEvent(
1033 GPALEventType.ERROR,
1034 $"Failed to get results",
1035 this,
1036 GPALObjectType.GPALExcel, ex);
1037 }
1038
1039 return this;
1040 }
1041
1047 public IAllowExcelCellSettings WithInsertPosition(int position)
1048 {
1049 ReopenIfClosed();
1050
1051 if (position < 0)
1052 {
1053 GPAL.PublishSimpleEvent(
1054 GPALEventType.ERROR,
1055 $"Invalid insert position [{position}]",
1056 this,
1057 GPALObjectType.GPALExcel);
1058 }
1059 else
1060 _insertPosition = position;
1061
1062 return this;
1063 }
1064
1071 public IAllowExcelCellSettings WithInsertSeparator(string separator)
1072 {
1073 ReopenIfClosed();
1074
1075 if (string.IsNullOrEmpty(separator))
1076 {
1077 GPAL.PublishSimpleEvent(
1078 GPALEventType.ERROR,
1079 "Insert separator cannot be empty",
1080 this,
1081 GPALObjectType.GPALExcel);
1082 }
1083 else
1084 _insertSeparator = separator;
1085
1086 return this;
1087 }
1088
1098 public IAllowExcelOperations InsertValue(string value)
1099 {
1100 ReopenIfClosed();
1101
1102 _operationPerformed = true;
1103
1104 if (_insertPosition == null || _insertSeparator == null || string.IsNullOrEmpty(_currentCell))
1105 {
1106 GPAL.PublishSimpleEvent(
1107 GPALEventType.ERROR,
1108 $"Missing insert position, separator, or cell for value [{value}]",
1109 this,
1110 GPALObjectType.GPALExcel);
1111 }
1112 else
1113 {
1114
1115 foreach (var kvp in _workbooks)
1116 {
1117 try
1118 {
1119 var worksheet = GetWorksheet(kvp.Value);
1120 if (worksheet != null)
1121 {
1122 var cell = worksheet.Cell(_currentCell);
1123 var currentValue = cell.GetString();
1124 var newValue = _insertPosition == 0 ? $"{value}{_insertSeparator}{currentValue}" : $"{currentValue}{_insertSeparator}{value}";
1125 cell.SetValue(newValue);
1126 }
1127 else
1128 {
1129 GPAL.PublishSimpleEvent(
1130 GPALEventType.ERROR,
1131 $"Cannot insert value [{value}] in [{kvp.Key}]: Invalid sheet",
1132 this,
1133 GPALObjectType.GPALExcel);
1134 }
1135 }
1136 catch (Exception ex)
1137 {
1138 GPAL.PublishSimpleEvent(
1139 GPALEventType.ERROR,
1140 $"Failed to insert value [{value}] at cell [{_currentCell}] in [{kvp.Key}]",
1141 this,
1142 GPALObjectType.GPALExcel, ex);
1143 }
1144 }
1145 }
1146
1147 return this;
1148 }
1149
1156 public IAllowExcelOperations SetValue(string value)
1157 {
1158 ReopenIfClosed();
1159
1160 _operationPerformed = true;
1161
1162 if (string.IsNullOrEmpty(_currentCell))
1163 {
1164 GPAL.PublishSimpleEvent(
1165 GPALEventType.ERROR,
1166 $"No cell specified for setting value [{value}]",
1167 this,
1168 GPALObjectType.GPALExcel);
1169 }
1170 else
1171 {
1172 foreach (var kvp in _workbooks)
1173 {
1174 try
1175 {
1176 var worksheet = GetWorksheet(kvp.Value);
1177 if (worksheet != null)
1178 {
1179 worksheet.Cell(_currentCell).SetValue(value);
1180 }
1181 else
1182 {
1183 GPAL.PublishSimpleEvent(
1184 GPALEventType.ERROR,
1185 $"Cannot set value [{value}] in [{kvp.Key}]: Invalid sheet",
1186 this,
1187 GPALObjectType.GPALExcel);
1188 }
1189 }
1190 catch (Exception ex)
1191 {
1192 GPAL.PublishSimpleEvent(
1193 GPALEventType.ERROR,
1194 $"Failed to set value [{value}] at cell [{_currentCell}] in [{kvp.Key}]",
1195 this,
1196 GPALObjectType.GPALExcel, ex);
1197 }
1198 }
1199 }
1200
1201 return this;
1202 }
1203
1210 public IAllowExcelOperations AppendValue(string value)
1211 {
1212 ReopenIfClosed();
1213
1214 _operationPerformed = true;
1215
1216 if (string.IsNullOrEmpty(_currentCell))
1217 {
1218 GPAL.PublishSimpleEvent(
1219 GPALEventType.ERROR,
1220 $"No cell specified for appending value [{value}]",
1221 this,
1222 GPALObjectType.GPALExcel);
1223 }
1224 else
1225 {
1226 foreach (var kvp in _workbooks)
1227 {
1228 try
1229 {
1230 var worksheet = GetWorksheet(kvp.Value);
1231 if (worksheet != null)
1232 {
1233 var cell = worksheet.Cell(_currentCell);
1234 var currentValue = cell.GetString();
1235 cell.SetValue(currentValue + value);
1236 }
1237 else
1238 {
1239 GPAL.PublishSimpleEvent(
1240 GPALEventType.ERROR,
1241 $"Cannot append value [{value}] in [{kvp.Key}]: Invalid sheet",
1242 this,
1243 GPALObjectType.GPALExcel);
1244 }
1245 }
1246 catch (Exception ex)
1247 {
1248 GPAL.PublishSimpleEvent(
1249 GPALEventType.ERROR,
1250 $"Failed to append value [{value}] at cell [{_currentCell}] in [{kvp.Key}]",
1251 this,
1252 GPALObjectType.GPALExcel, ex);
1253 }
1254 }
1255 }
1256
1257 return this;
1258 }
1259
1266 public IAllowExcelOperations PrependValue(string value)
1267 {
1268 ReopenIfClosed();
1269
1270 _operationPerformed = true;
1271
1272 if (string.IsNullOrEmpty(_currentCell))
1273 {
1274 GPAL.PublishSimpleEvent(
1275 GPALEventType.ERROR,
1276 $"No cell specified for prepending value [{value}]",
1277 this,
1278 GPALObjectType.GPALExcel);
1279 }
1280 else
1281 {
1282 foreach (var kvp in _workbooks)
1283 {
1284 try
1285 {
1286 var worksheet = GetWorksheet(kvp.Value);
1287 if (worksheet != null)
1288 {
1289 var cell = worksheet.Cell(_currentCell);
1290 var currentValue = cell.GetString();
1291 cell.SetValue(value + currentValue);
1292 }
1293 else
1294 {
1295 GPAL.PublishSimpleEvent(
1296 GPALEventType.ERROR,
1297 $"Cannot prepend value [{value}] in [{kvp.Key}]: Invalid sheet",
1298 this,
1299 GPALObjectType.GPALExcel);
1300 }
1301 }
1302 catch (Exception ex)
1303 {
1304 GPAL.PublishSimpleEvent(
1305 GPALEventType.ERROR,
1306 $"Failed to prepend value [{value}] at cell [{_currentCell}] in [{kvp.Key}]",
1307 this,
1308 GPALObjectType.GPALExcel, ex);
1309 }
1310 }
1311 }
1312
1313 return this;
1314 }
1315
1322 public IAllowExcelOperations ReplaceWith(string value)
1323 {
1324 ReopenIfClosed();
1325 _operationPerformed = true;
1326
1327 if (string.IsNullOrEmpty(_replaceValue))
1328 {
1329 GPAL.PublishSimpleEvent(
1330 GPALEventType.ERROR,
1331 $"No search value for replace with [{value}]",
1332 this,
1333 GPALObjectType.GPALExcel);
1334 }
1335 else if (!_rangesData.Any())
1336 {
1337 GPAL.PublishSimpleEvent(
1338 GPALEventType.ERROR,
1339 $"No range defined for replace with [{value}]",
1340 this,
1341 GPALObjectType.GPALExcel);
1342 }
1343 else
1344 {
1345 foreach (var kvp in _workbooks)
1346 {
1347 try
1348 {
1349 var worksheet = GetWorksheet(kvp.Value);
1350 if (worksheet == null)
1351 {
1352 GPAL.PublishSimpleEvent(
1353 GPALEventType.ERROR,
1354 $"Cannot replace in [{kvp.Key}]: Invalid sheet",
1355 this,
1356 GPALObjectType.GPALExcel);
1357 }
1358 else
1359 {
1360 foreach (var rangeData in _rangesData)
1361 {
1362 ExcelHelper.SearchAndReplaceByValue(worksheet, rangeData.Range, _replaceValue, value);
1363 }
1364 }
1365 }
1366 catch (Exception ex)
1367 {
1368 GPAL.PublishSimpleEvent(
1369 GPALEventType.ERROR,
1370 $"Failed to replace [{_replaceValue}] with [{value}] in [{kvp.Key}]",
1371 this,
1372 GPALObjectType.GPALExcel, ex);
1373 }
1374 }
1375 }
1376 return this;
1377 }
1378
1386 public IAllowExcelOperations SaveTo(GPALFile gpalFile)
1387 {
1388 _operationPerformed = true;
1389 gpalFile = gpalFile ?? _gpalFile;
1390
1391 if (gpalFile == null)
1392 {
1393 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No GPALFile specified for save", this, GPALObjectType.GPALExcel);
1394 }
1395 else if (!_workbooks.Any())
1396 {
1397 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No workbooks loaded to save", this, GPALObjectType.GPALExcel);
1398 }
1399 else
1400 {
1401 try
1402 {
1403 var targetPaths = new List<string>();
1404 foreach (var pattern in gpalFile.Filenames)
1405 {
1406 try
1407 {
1408 string directory = Path.GetDirectoryName(pattern) ?? Directory.GetCurrentDirectory();
1409 string fileName = Path.GetFileName(pattern);
1410 var matchingFiles = Directory.GetFiles(directory, fileName, SearchOption.TopDirectoryOnly);
1411 targetPaths.AddRange(matchingFiles);
1412 }
1413 catch (Exception ex)
1414 {
1415 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to resolve save path [{pattern}]", this, GPALObjectType.GPALExcel, ex);
1416 }
1417 }
1418 targetPaths = targetPaths.Distinct().ToList();
1419
1420 if (!targetPaths.Any())
1421 {
1422 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"No valid target paths resolved from [{string.Join(", ", gpalFile.Filenames)}]", this, GPALObjectType.GPALExcel);
1423 }
1424 else
1425 {
1426 foreach (var kvp in _workbooks)
1427 {
1428 try
1429 {
1430 if (targetPaths.Contains(kvp.Key, StringComparer.OrdinalIgnoreCase))
1431 {
1432 try
1433 {
1434 kvp.Value.SaveAs(kvp.Key);
1435 GPAL.PublishSimpleEvent(GPALEventType.INFO, $"Saved workbook [{kvp.Key}] to [{kvp.Key}]", this, GPALObjectType.GPALExcel);
1436 }
1437 catch (Exception ex)
1438 {
1439 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to save workbook [{kvp.Key}] to [{kvp.Key}]", this, GPALObjectType.GPALExcel, ex);
1440 }
1441 }
1442 else
1443 {
1444 foreach (var targetPath in targetPaths)
1445 {
1446 try
1447 {
1448 using var targetWorkbook = new XLWorkbook();
1449 foreach (var worksheet in kvp.Value.Worksheets)
1450 {
1451 var targetWorksheet = targetWorkbook.Worksheets.Add(worksheet.Name);
1452 ExcelHelper.CopyWorksheet(worksheet, targetWorksheet);
1453 }
1454 targetWorkbook.SaveAs(targetPath);
1455 GPAL.PublishSimpleEvent(GPALEventType.INFO, $"Saved unmatched workbook [{kvp.Key}] to [{targetPath}]", this, GPALObjectType.GPALExcel);
1456 }
1457 catch (Exception ex)
1458 {
1459 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to save unmatched workbook [{kvp.Key}] to [{targetPath}]", this, GPALObjectType.GPALExcel, ex);
1460 }
1461 }
1462 }
1463 }
1464 catch (Exception ex)
1465 {
1466 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to process workbook [{kvp.Key}]", this, GPALObjectType.GPALExcel, ex);
1467 }
1468 }
1469 }
1470 }
1471 catch (Exception ex)
1472 {
1473 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to resolve save paths for [{string.Join(", ", gpalFile.Filenames)}]", this, GPALObjectType.GPALExcel, ex);
1474 }
1475 }
1476
1477 return this;
1478 }
1479
1484 public IAllowExcelOperations SaveTo(IGPALGrid<string> grid)
1485 {
1486 if (null == grid)
1487 {
1488 GPAL.PublishSimpleEvent(
1489 GPALEventType.ERROR,
1490 "Grid cannot be null.",
1491 this,
1492 GPALObjectType.GPALExcel);
1493 }
1494 else
1495 {
1496 if (!_workbooks.Any() && _filePaths.Any())
1497 {
1498 foreach (var path in _filePaths)
1499 LoadFile(path);
1500 }
1501 if (!_rangesData.Any())
1502 {
1503 GPAL.PublishSimpleEvent(
1504 GPALEventType.ERROR,
1505 "No data to save to grid",
1506 this,
1507 GPALObjectType.GPALExcel);
1508 }
1509 else
1510 {
1511 _operationPerformed = true;
1512
1513 GPAL.PublishSimpleEvent(
1514 GPALEventType.INFO,
1515 $"Saving [{_rangesData.Sum(rangeData => rangeData.Data.Count())}] rows from [{_rangesData.Count}] ranges to grid.",
1516 this,
1517 GPALObjectType.GPALExcel);
1518
1519 try
1520 {
1521 foreach (var rangeData in _rangesData)
1522 {
1523 foreach (var row in rangeData.Data)
1524 grid.AddRow(row);
1525 }
1526 }
1527 catch (Exception ex)
1528 {
1529 GPAL.PublishSimpleEvent(
1530 GPALEventType.ERROR,
1531 $"Failed to save to grid",
1532 this,
1533 GPALObjectType.GPALExcel, ex);
1534 }
1535 }
1536 }
1537
1538 return this;
1539 }
1540
1548 public IAllowExcelOperations SaveTo(string sheetName)
1549 {
1550 if (!_workbooks.Any() && _filePaths.Any())
1551 {
1552 foreach (var path in _filePaths)
1553 LoadFile(path);
1554 }
1555 if (!_rangesData.Any())
1556 {
1557 GPAL.PublishSimpleEvent(
1558 GPALEventType.ERROR,
1559 $"No data to write to sheet [{sheetName}]",
1560 this,
1561 GPALObjectType.GPALExcel);
1562 }
1563 else if (string.IsNullOrEmpty(sheetName))
1564 {
1565 GPAL.PublishSimpleEvent(
1566 GPALEventType.ERROR,
1567 "Sheet name cannot be empty",
1568 this,
1569 GPALObjectType.GPALExcel);
1570 }
1571 else
1572 {
1573 _operationPerformed = true;
1574
1575 GPAL.PublishSimpleEvent(
1576 GPALEventType.INFO,
1577 $"Saving [{_rangesData.Count}] ranges to sheet [{sheetName}] of [{_workbooks.Count}] workbooks.",
1578 this,
1579 GPALObjectType.GPALExcel);
1580
1581 foreach (var kvp in _workbooks)
1582 {
1583 try
1584 {
1585 var worksheet = kvp.Value.Worksheets.Any(ws => ws.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase))
1586 ? kvp.Value.Worksheet(sheetName)
1587 : kvp.Value.Worksheets.Add(sheetName);
1588 string target;
1589 if (!string.IsNullOrEmpty(_currentCell))
1590 {
1591 target = _currentCell;
1592 worksheet.Cell(target).SetValue(_rangesData.Last().Data.SelectMany(row => row).FirstOrDefault() ?? "");
1593 }
1594 else
1595 {
1596 target = _rangesData.Last().Range;
1597 foreach (var rangeData in _rangesData)
1598 {
1599 ExcelHelper.WriteRange(worksheet, rangeData.Range, rangeData.Data);
1600 }
1601 }
1602 }
1603 catch (Exception ex)
1604 {
1605 GPAL.PublishSimpleEvent(
1606 GPALEventType.ERROR,
1607 $"Failed to write to sheet [{sheetName}] in [{kvp.Key}]",
1608 this,
1609 GPALObjectType.GPALExcel, ex);
1610 }
1611 }
1612 }
1613
1614 return this;
1615 }
1616
1624 public IAllowExcelOperations SaveTo(int sheetIndex)
1625 {
1626 if (!_workbooks.Any() && _filePaths.Any())
1627 {
1628 foreach (var path in _filePaths)
1629 LoadFile(path);
1630 }
1631 if (!_rangesData.Any())
1632 {
1633 GPAL.PublishSimpleEvent(
1634 GPALEventType.ERROR,
1635 $"No data to write to sheet index [{sheetIndex}]",
1636 this,
1637 GPALObjectType.GPALExcel);
1638 }
1639 else if (sheetIndex < 1)
1640 {
1641 GPAL.PublishSimpleEvent(
1642 GPALEventType.ERROR,
1643 $"Invalid sheet index [{sheetIndex}]",
1644 this,
1645 GPALObjectType.GPALExcel);
1646 }
1647 else
1648 {
1649 _operationPerformed = true;
1650
1651 GPAL.PublishSimpleEvent(
1652 GPALEventType.INFO,
1653 $"Saving [{_rangesData.Count}] ranges to sheet index [{sheetIndex}] of [{_workbooks.Count}] workbooks.",
1654 this,
1655 GPALObjectType.GPALExcel);
1656
1657 foreach (var kvp in _workbooks)
1658 {
1659 try
1660 {
1661 var worksheet = kvp.Value.Worksheets.ElementAtOrDefault(sheetIndex - 1) ?? kvp.Value.Worksheets.Add($"Sheet{sheetIndex}");
1662 string target;
1663 if (!string.IsNullOrEmpty(_currentCell))
1664 {
1665 target = _currentCell;
1666 worksheet.Cell(target).SetValue(_rangesData.Last().Data.SelectMany(row => row).FirstOrDefault() ?? "");
1667 }
1668 else
1669 {
1670 target = _rangesData.Last().Range;
1671 foreach (var rangeData in _rangesData)
1672 {
1673 ExcelHelper.WriteRange(worksheet, rangeData.Range, rangeData.Data);
1674 }
1675 }
1676 }
1677 catch (Exception ex)
1678 {
1679 GPAL.PublishSimpleEvent(
1680 GPALEventType.ERROR,
1681 $"Failed to write to sheet index [{sheetIndex}] in [{kvp.Key}]",
1682 this,
1683 GPALObjectType.GPALExcel, ex);
1684 }
1685 }
1686 }
1687
1688 return this;
1689 }
1690
1696 public void Close(bool saveOnClose)
1697 {
1698 if (_isClosed)
1699 {
1700 GPAL.PublishSimpleEvent(
1701 GPALEventType.INFO,
1702 $"GPALExcel instance already closed [{_gpalFile?.Filenames?.FirstOrDefault() ?? "unknown"}]",
1703 this,
1704 GPALObjectType.GPALExcel);
1705 }
1706 else
1707 {
1708 _operationPerformed = false;
1709
1710 foreach (var kvp in _workbooks)
1711 {
1712 try
1713 {
1714 if (saveOnClose)
1715 {
1716 kvp.Value.SaveAs(kvp.Key);
1717 }
1718 kvp.Value.Dispose();
1719 }
1720 catch (Exception ex)
1721 {
1722 GPAL.PublishSimpleEvent(
1723 GPALEventType.ERROR,
1724 $"Failed to close workbook [{kvp.Key}]",
1725 this,
1726 GPALObjectType.GPALExcel, ex);
1727 }
1728 }
1729 _workbooks.Clear();
1730 _filePaths.Clear();
1731 _rangesData.Clear();
1732 _currentSheetName = null;
1733 _currentSheetIndex = -1;
1734 _insertPosition = 0;
1735 _insertSeparator = " ";
1736 _currentCell = null;
1737 _isClosed = true;
1738 }
1739 }
1740
1748 private IXLWorksheet GetWorksheet(XLWorkbook workbook)
1749 {
1750 if (!string.IsNullOrEmpty(_currentSheetName))
1751 {
1752 return workbook.Worksheets.FirstOrDefault(ws => ws.Name.Equals(_currentSheetName, StringComparison.OrdinalIgnoreCase));
1753 }
1754 if (-1 != _currentSheetIndex)
1755 {
1756 return workbook.Worksheets.ElementAtOrDefault(_currentSheetIndex);
1757 }
1758 return null;
1759 }
1760
1769 internal static IXLWorksheet GetWorksheet(XLWorkbook workbook, string sheetIdentifier)
1770 {
1771 try
1772 {
1773 if (workbook.TryGetWorksheet(sheetIdentifier, out IXLWorksheet worksheet))
1774 {
1775 return worksheet;
1776 }
1777
1778 if (int.TryParse(sheetIdentifier, out int sheetIndex) && sheetIndex > 0)
1779 {
1780 try
1781 {
1782 worksheet = workbook.Worksheet(sheetIndex);
1783 return worksheet;
1784 }
1785 catch
1786 {
1787 }
1788 }
1789
1790 GPAL.PublishSimpleEvent(Enums.GPALEventType.ERROR, $"Worksheet [{sheetIdentifier}] not found in workbook and no default name provided.");
1791 return null;
1792 }
1793 catch (Exception ex)
1794 {
1795 GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to get worksheet [{sheetIdentifier}]", null, Enums.GPALObjectType.None, ex);
1796 return null;
1797 }
1798 }
1799
1806 internal static IXLWorksheet GetWorksheet(XLWorkbook workbook, int sheetIndex)
1807 {
1808 try
1809 {
1810 if (null != workbook.Worksheet(sheetIndex))
1811 return workbook.Worksheet(sheetIndex);
1812
1813 GPAL.PublishSimpleEvent(Enums.GPALEventType.ERROR, $"Worksheet [{sheetIndex}] not found in workbook and no default name provided.");
1814 return null;
1815 }
1816 catch (Exception ex)
1817 {
1818 GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to get worksheet [{sheetIndex}]", null, Enums.GPALObjectType.None, ex);
1819 return null;
1820 }
1821 }
1822 }
1823}
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:356
GenerallyPositive.GPALExcel.ClearRanges
IAllowExcelOperations ClearRanges()
Clears any range data read via WithRange(string), WithColumn(string), or WithRowNumber(int),...
Definition GPALExcel.cs:420
GenerallyPositive.GPALExcel.WithSearchValue
IAllowExcelSearchAndReplaceSettings WithSearchValue(string value)
Sets the value to search for in subsequent ReplaceWith(string) operations.
Definition GPALExcel.cs:466
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:1624
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:908
GenerallyPositive.GPALExcel.CalculateCount
IAllowExcelOperations CalculateCount(out string result)
Counts every non-empty value across all previously read range data.
Definition GPALExcel.cs:657
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:777
GenerallyPositive.GPALExcel.InsertValue
IAllowExcelOperations InsertValue(string value)
Inserts value into the cell selected via WithCell(string) on every loaded workbook,...
Definition GPALExcel.cs:1098
GenerallyPositive.GPALExcel.SaveTo
IAllowExcelOperations SaveTo(IGPALGrid< string > grid)
Appends every row from the previously read range data into grid .
Definition GPALExcel.cs:1484
GenerallyPositive.GPALExcel.SetValue
IAllowExcelOperations SetValue(string value)
Sets the value of the cell selected via WithCell(string) on every loaded workbook,...
Definition GPALExcel.cs:1156
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:209
GenerallyPositive.GPALExcel.Close
void Close(bool saveOnClose)
Closes every loaded workbook, optionally saving each back to its source path first,...
Definition GPALExcel.cs:1696
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:1386
GenerallyPositive.GPALExcel.ToGPALObject
IGPALExcel ToGPALObject()
Returns this instance as an IGPALExcel, completing the fluent configuration chain.
Definition GPALExcel.cs:141
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:296
GenerallyPositive.GPALExcel.ReplaceWith
IAllowExcelOperations ReplaceWith(string value)
Replaces every occurrence of the value set via WithSearchValue(string) with value ,...
Definition GPALExcel.cs:1322
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:1071
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:1004
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:1210
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:1047
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:440
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:554
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:699
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:251
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:492
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:1548
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:615
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:1266
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:536
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:2406