 |
GPAL - Generally Positive Automation Library v1.0
GPAL The Fluent Automation LIbrary
|
|
GPAL - Generally Positive Automation Library v1.0
GPAL The Fluent Automation LIbrary
|
Provides fluent, file-based automation of Excel workbooks: opening one or more workbooks (including wildcard-matched sets of files), selecting a sheet, reading/writing ranges, columns, rows and individual cells, performing simple aggregate calculations (sum/count), comparing ranges against a grid, another file, or another range, and saving results back to a file, grid, or sheet. All operations apply to every workbook currently loaded via WithFile(GPALFile), so a single chain can drive many files at once. Errors are reported via GPAL.PublishSimpleEvent rather than thrown. More...
Public Member Functions | |
| IGPALExcel | ToGPALObject () |
| Returns this instance as an IGPALExcel, completing the fluent configuration chain. | |
| IAllowExcelSheetSelection | WithFile (GPALFile gpalFile) |
| Resets this instance and loads the workbook(s) matching gpalFile 's filenames (which may include wildcard patterns), opening each matched file with ClosedXML. Replaces any previously loaded workbooks, range data, and comparison results. | |
| IAllowExcelSheetSelection | WithNewFile (GPALFile gpalFile) |
| Starts a workbook of the workflow's own at gpalFile , holding one sheet and nothing else. Nothing reaches the disk until SaveTo(GPALFile) or Close(bool) with true, the same as a workbook that was loaded. | |
| IAllowExcelOperations | WithNewSheet (string sheetName) |
| Adds a sheet named sheetName to every loaded workbook and works on it from here. A workbook already holding a sheet of that name keeps the one it has. | |
| IAllowExcelOperations | InputFrom (IGPALGrid< string > grid) |
| Writes grid into the current sheet, starting at the cell WithCell(string) named or A1. This is how a workflow's own data gets into a workbook: everything else here moves data that the workbook already held. | |
| IAllowExcelOperations | WithSheet (int sheetIndex) |
| Selects the sheet at the given 1-based index for subsequent Excel operations on every loaded workbook. | |
| IAllowExcelOperations | WithSheet (string sheetName) |
| Selects the sheet with the given name (case-insensitive) for subsequent Excel operations on every loaded workbook. | |
| IAllowExcelOperations | WithRange (string range) |
| Reads the given cell range from the currently selected sheet of every loaded workbook and stores the data for use by subsequent operations (e.g. SaveTo(IGPALGrid<string>), WriteRange(string), comparisons). If a previous read/write operation has already been performed on this instance, prior range data and comparison differences are cleared first. | |
| IAllowExcelOperations | WriteRange (string range) |
| Writes the range data previously read via WithRange(string) (or another data-producing operation) to range on the currently selected sheet of every loaded workbook. | |
| IAllowExcelOperations | ClearRanges () |
| Clears any range data read via WithRange(string), WithColumn(string), or WithRowNumber(int), along with any comparison differences accumulated by the CompareTo* methods. | |
| IAllowExcelCellSettings | WithCell (string cell) |
| Selects a single cell address (e.g. "B3") on the currently selected sheet as the target for subsequent cell operations such as SetValue(string), InsertValue(string), AppendValue(string), and PrependValue(string). | |
| IAllowExcelSearchAndReplaceSettings | WithSearchValue (string value) |
| Sets the value to search for in subsequent ReplaceWith(string) operations. | |
| IAllowExcelOperations | WithColumn (string column) |
| Reads every value in the given column from the currently selected sheet of the first loaded workbook that has the selected sheet, storing the result (one value per row) for use by subsequent operations. If a previous read/write operation has already been performed on this instance, prior range data and comparison differences are cleared first. | |
| IAllowExcelOperations | WithRowNumber (int rowNumber) |
| Reads every value in the given row from the currently selected sheet of the first loaded workbook that has the selected sheet, storing the result (a single row of values) for use by subsequent operations. If a previous read/write operation has already been performed on this instance, prior range data and comparison differences are cleared first. | |
| IAllowExcelOperations | CalculateSum (out string result) |
| Sums every numeric value across all previously read range data (non-numeric values are ignored). | |
| IAllowExcelOperations | CalculateCount (out string result) |
| Counts every non-empty value across all previously read range data. | |
| IAllowExcelOperations | CompareToGrid (IGPALGrid< string > grid) |
| Compares each previously read range against grid , recording any cell-level differences for retrieval via GetCompareResults(out IGPALGrid<string>). | |
| IAllowExcelOperations | CompareToFile (GPALFile gpalFile) |
| Compares each previously read range against the same range/sheet in the workbook(s) matching gpalFile 's filenames (which may include wildcard patterns), recording any cell-level differences for retrieval via GetCompareResults(out IGPALGrid<string>). | |
| IAllowExcelOperations | CompareToRange (string range) |
| Compares each previously read range against range on the currently selected sheet of every loaded workbook, recording any cell-level differences for retrieval via GetCompareResults(out IGPALGrid<string>). | |
| IAllowExcelOperations | GetCompareResults (out IGPALGrid< string > results) |
| Returns the cell-level differences accumulated by the CompareTo* methods (CompareToGrid(IGPALGrid<string>), CompareToFile(GPALFile), CompareToRange(string)) as a grid with columns: File, Sheet, Cell, SourceRange, TargetRange, OldValue, NewValue. | |
| IAllowExcelCellSettings | WithInsertPosition (int position) |
| Sets where InsertValue(string) places its value relative to the current cell content: 0 inserts before the existing content, any other value inserts after. | |
| IAllowExcelCellSettings | WithInsertSeparator (string separator) |
| Sets the separator that InsertValue(string) places between the inserted value and the cell's existing content. Defaults to a single space. | |
| IAllowExcelOperations | InsertValue (string value) |
| Inserts value into the cell selected via WithCell(string) on every loaded workbook, combined with the cell's existing content using the separator from WithInsertSeparator(string) and positioned according to WithInsertPosition(int). Requires WithCell(string), WithInsertPosition(int), and WithInsertSeparator(string) to have been called first. | |
| IAllowExcelOperations | SetValue (string value) |
| Sets the value of the cell selected via WithCell(string) on every loaded workbook, replacing any existing content. | |
| IAllowExcelOperations | AppendValue (string value) |
| Appends value to the end of the existing content of the cell selected via WithCell(string) on every loaded workbook. | |
| IAllowExcelOperations | PrependValue (string value) |
| Prepends value to the beginning of the existing content of the cell selected via WithCell(string) on every loaded workbook. | |
| IAllowExcelOperations | ReplaceWith (string value) |
| Replaces every occurrence of the value set via WithSearchValue(string) with value , within each previously read range, on the currently selected sheet of every loaded workbook. | |
| IAllowExcelOperations | GetSearchResults (out IGPALGrid< string > results) |
| Every cell in the range holding the search value, as a grid shaped the way GetCompareResults shapes its answer: a header row, then one row per hit. The find half of search and replace, on the same .WithRange and .WithSearchValue that ReplaceWith acts on. Every match rather than the first, because a range holding exactly one is the exception, and the file and sheet ride on each row because the search walks every workbook that is loaded. | |
| IAllowExcelOperations | SaveTo (GPALFile gpalFile) |
| Saves the loaded workbook(s) to the file(s) matching gpalFile 's filenames (which may include wildcard patterns), or to _gpalFile if gpalFile is null. A workbook whose source path is among the resolved targets is saved in place; otherwise its sheets are copied into a new workbook saved at each resolved target path. | |
| IAllowExcelOperations | SaveTo (ref IGPALGrid< string > grid) |
| Appends every row from the previously read range data into grid . The grid is taken by reference because it is what the read lands in: the workflow reads it back afterwards, and anything already in it, including a IGPALGrid<T>.WithDedupeData setting, is kept. Pass null and GPAL makes one, the same as GetGrid does. | |
| IAllowExcelOperations | SaveTo (string sheetName) |
| Writes the previously read range data into the sheet named sheetName of every loaded workbook, creating the sheet if it does not exist. If a cell was selected via WithCell(string), only the first value of the last read range is written to that cell; otherwise the full data is written starting at the last read range's address. | |
| IAllowExcelOperations | SaveTo (int sheetIndex) |
| Writes the previously read range data into the sheet at the given 1-based index of every loaded workbook, creating the sheet (named "Sheet{sheetIndex}") if it does not exist. If a cell was selected via WithCell(string), only the first value of the last read range is written to that cell; otherwise the full data is written starting at the last read range's address. | |
| void | Close (bool saveOnClose) |
| Closes every loaded workbook, optionally saving each back to its source path first, and clears all cached state. A subsequent operation on this instance will reload the workbooks via ReopenIfClosed. If this instance is already closed, this is a no-op. | |
Provides fluent, file-based automation of Excel workbooks: opening one or more workbooks (including wildcard-matched sets of files), selecting a sheet, reading/writing ranges, columns, rows and individual cells, performing simple aggregate calculations (sum/count), comparing ranges against a grid, another file, or another range, and saving results back to a file, grid, or sheet. All operations apply to every workbook currently loaded via WithFile(GPALFile), so a single chain can drive many files at once. Errors are reported via GPAL.PublishSimpleEvent rather than thrown.
Definition at line 45 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.AppendValue | ( | string | value | ) |
Appends value to the end of the existing content of the cell selected via WithCell(string) on every loaded workbook.
| value | The value to append. |
Implements GenerallyPositive.IAllowExcelCellSettings.
Definition at line 1375 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.CalculateCount | ( | out string | result | ) |
Counts every non-empty value across all previously read range data.
| result | The count as a string, or "0" if no range data is available or the count fails. |
Implements GenerallyPositive.IAllowExcelOperations.
Definition at line 819 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.CalculateSum | ( | out string | result | ) |
Sums every numeric value across all previously read range data (non-numeric values are ignored).
| result | The sum as a string, or "0" if no range data is available or the sum fails. |
Implements GenerallyPositive.IAllowExcelOperations.
Definition at line 777 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.ClearRanges | ( | ) |
Clears any range data read via WithRange(string), WithColumn(string), or WithRowNumber(int), along with any comparison differences accumulated by the CompareTo* methods.
Implements GenerallyPositive.IAllowExcelOperations.
Definition at line 582 of file GPALExcel.cs.
| void GenerallyPositive.GPALExcel.Close | ( | bool | saveOnClose | ) |
Closes every loaded workbook, optionally saving each back to its source path first, and clears all cached state. A subsequent operation on this instance will reload the workbooks via ReopenIfClosed. If this instance is already closed, this is a no-op.
| saveOnClose | true to save each workbook to its original path before closing; otherwise false. |
Implements GenerallyPositive.IAllowExcelCore.
Definition at line 1937 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.CompareToFile | ( | GPALFile | gpalFile | ) |
Compares each previously read range against the same range/sheet in the workbook(s) matching gpalFile 's filenames (which may include wildcard patterns), recording any cell-level differences for retrieval via GetCompareResults(out IGPALGrid<string>).
| gpalFile | The file (or wildcard pattern) identifying the workbook(s) to compare against. |
Implements GenerallyPositive.IAllowExcelCompareSettings.
Definition at line 939 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.CompareToGrid | ( | IGPALGrid< string > | grid | ) |
Compares each previously read range against grid , recording any cell-level differences for retrieval via GetCompareResults(out IGPALGrid<string>).
| grid | The grid to compare each read range against. |
Implements GenerallyPositive.IAllowExcelCompareSettings.
Definition at line 861 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.CompareToRange | ( | string | range | ) |
Compares each previously read range against range on the currently selected sheet of every loaded workbook, recording any cell-level differences for retrieval via GetCompareResults(out IGPALGrid<string>).
| range | The cell range to compare each previously read range against (e.g. "A1:B10"). |
Implements GenerallyPositive.IAllowExcelCompareSettings.
Definition at line 1073 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.GetCompareResults | ( | out IGPALGrid< string > | results | ) |
Returns the cell-level differences accumulated by the CompareTo* methods (CompareToGrid(IGPALGrid<string>), CompareToFile(GPALFile), CompareToRange(string)) as a grid with columns: File, Sheet, Cell, SourceRange, TargetRange, OldValue, NewValue.
| results | The comparison results, with a header row even if no differences were found. |
Implements GenerallyPositive.IAllowExcelOperations.
Definition at line 1169 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.GetSearchResults | ( | out IGPALGrid< string > | results | ) |
Every cell in the range holding the search value, as a grid shaped the way GetCompareResults shapes its answer: a header row, then one row per hit.
The find half of search and replace, on the same .WithRange and .WithSearchValue that ReplaceWith acts on. Every match rather than the first, because a range holding exactly one is the exception, and the file and sheet ride on each row because the search walks every workbook that is loaded.
| results | Receives the hits, holding only the header row when nothing matched |
Implements GenerallyPositive.IAllowExcelSearchAndReplaceSettings.
Definition at line 1563 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.InputFrom | ( | IGPALGrid< string > | grid | ) |
Writes grid into the current sheet, starting at the cell WithCell(string) named or A1. This is how a workflow's own data gets into a workbook: everything else here moves data that the workbook already held.
| grid | The rows to write. |
Implements GenerallyPositive.IAllowExcelOperations.
Definition at line 303 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.InsertValue | ( | string | value | ) |
Inserts value into the cell selected via WithCell(string) on every loaded workbook, combined with the cell's existing content using the separator from WithInsertSeparator(string) and positioned according to WithInsertPosition(int). Requires WithCell(string), WithInsertPosition(int), and WithInsertSeparator(string) to have been called first.
| value | The value to insert. |
Implements GenerallyPositive.IAllowExcelCellSettings.
Definition at line 1263 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.PrependValue | ( | string | value | ) |
Prepends value to the beginning of the existing content of the cell selected via WithCell(string) on every loaded workbook.
| value | The value to prepend. |
Implements GenerallyPositive.IAllowExcelCellSettings.
Definition at line 1431 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.ReplaceWith | ( | string | value | ) |
Replaces every occurrence of the value set via WithSearchValue(string) with value , within each previously read range, on the currently selected sheet of every loaded workbook.
| value | The replacement value. |
Implements GenerallyPositive.IAllowExcelSearchAndReplaceSettings.
Definition at line 1487 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.SaveTo | ( | GPALFile | gpalFile | ) |
Saves the loaded workbook(s) to the file(s) matching gpalFile 's filenames (which may include wildcard patterns), or to _gpalFile if gpalFile is null. A workbook whose source path is among the resolved targets is saved in place; otherwise its sheets are copied into a new workbook saved at each resolved target path.
| gpalFile | The file (or wildcard pattern) identifying where to save, or null to save back to the originally loaded file(s). |
Implements GenerallyPositive.IAllowExcelCore.
Definition at line 1622 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.SaveTo | ( | int | sheetIndex | ) |
Writes the previously read range data into the sheet at the given 1-based index of every loaded workbook, creating the sheet (named "Sheet{sheetIndex}") if it does not exist. If a cell was selected via WithCell(string), only the first value of the last read range is written to that cell; otherwise the full data is written starting at the last read range's address.
| sheetIndex | The 1-based index of the destination sheet. |
Implements GenerallyPositive.IAllowExcelCore.
Definition at line 1865 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.SaveTo | ( | ref IGPALGrid< string > | grid | ) |
Appends every row from the previously read range data into grid .
The grid is taken by reference because it is what the read lands in: the workflow reads it back afterwards, and anything already in it, including a IGPALGrid<T>.WithDedupeData setting, is kept. Pass null and GPAL makes one, the same as GetGrid does.
| grid | The grid to append the read range data to, or null for GPAL to make one. |
Implements GenerallyPositive.IAllowExcelCore.
Definition at line 1731 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.SaveTo | ( | string | sheetName | ) |
Writes the previously read range data into the sheet named sheetName of every loaded workbook, creating the sheet if it does not exist. If a cell was selected via WithCell(string), only the first value of the last read range is written to that cell; otherwise the full data is written starting at the last read range's address.
| sheetName | The name of the destination sheet. |
Implements GenerallyPositive.IAllowExcelCore.
Definition at line 1789 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.SetValue | ( | string | value | ) |
Sets the value of the cell selected via WithCell(string) on every loaded workbook, replacing any existing content.
| value | The value to set. |
Implements GenerallyPositive.IAllowExcelCellSettings.
Definition at line 1321 of file GPALExcel.cs.
| IGPALExcel GenerallyPositive.GPALExcel.ToGPALObject | ( | ) |
Returns this instance as an IGPALExcel, completing the fluent configuration chain.
Implements GenerallyPositive.IAllowToGPALObject< TResult >.
Definition at line 141 of file GPALExcel.cs.
| IAllowExcelCellSettings GenerallyPositive.GPALExcel.WithCell | ( | string | cell | ) |
Selects a single cell address (e.g. "B3") on the currently selected sheet as the target for subsequent cell operations such as SetValue(string), InsertValue(string), AppendValue(string), and PrependValue(string).
| cell | The cell address to target. |
Implements GenerallyPositive.IAllowExcelOperations.
Definition at line 602 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.WithColumn | ( | string | column | ) |
Reads every value in the given column from the currently selected sheet of the first loaded workbook that has the selected sheet, storing the result (one value per row) for use by subsequent operations. If a previous read/write operation has already been performed on this instance, prior range data and comparison differences are cleared first.
| column | The column letter(s) to read (e.g. "A"). |
Implements GenerallyPositive.IAllowExcelOperations.
Definition at line 654 of file GPALExcel.cs.
| IAllowExcelSheetSelection GenerallyPositive.GPALExcel.WithFile | ( | GPALFile | gpalFile | ) |
Resets this instance and loads the workbook(s) matching gpalFile 's filenames (which may include wildcard patterns), opening each matched file with ClosedXML. Replaces any previously loaded workbooks, range data, and comparison results.
| gpalFile | The file (or wildcard pattern) identifying the workbook(s) to load. |
Implements GenerallyPositive.IAllowExcelFileSelection.
Definition at line 150 of file GPALExcel.cs.
| IAllowExcelCellSettings GenerallyPositive.GPALExcel.WithInsertPosition | ( | int | position | ) |
Sets where InsertValue(string) places its value relative to the current cell content: 0 inserts before the existing content, any other value inserts after.
| position | The insert position (0 = before existing content, otherwise after). |
Implements GenerallyPositive.IAllowExcelCellSettings.
Definition at line 1212 of file GPALExcel.cs.
| IAllowExcelCellSettings GenerallyPositive.GPALExcel.WithInsertSeparator | ( | string | separator | ) |
Sets the separator that InsertValue(string) places between the inserted value and the cell's existing content. Defaults to a single space.
| separator | The separator string to use. |
Implements GenerallyPositive.IAllowExcelCellSettings.
Definition at line 1236 of file GPALExcel.cs.
| IAllowExcelSheetSelection GenerallyPositive.GPALExcel.WithNewFile | ( | GPALFile | gpalFile | ) |
Starts a workbook of the workflow's own at gpalFile , holding one sheet and nothing else. Nothing reaches the disk until SaveTo(GPALFile) or Close(bool) with true, the same as a workbook that was loaded.
| gpalFile | The name the workbook is to be saved under. |
Implements GenerallyPositive.IAllowExcelFileSelection.
Definition at line 211 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.WithNewSheet | ( | string | sheetName | ) |
Adds a sheet named sheetName to every loaded workbook and works on it from here. A workbook already holding a sheet of that name keeps the one it has.
| sheetName | The name for the new sheet. |
Implements GenerallyPositive.IAllowExcelSheetSelection.
Definition at line 261 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.WithRange | ( | string | range | ) |
Reads the given cell range from the currently selected sheet of every loaded workbook and stores the data for use by subsequent operations (e.g. SaveTo(IGPALGrid<string>), WriteRange(string), comparisons). If a previous read/write operation has already been performed on this instance, prior range data and comparison differences are cleared first.
| range | The cell range to read (e.g. "A1:B10"). |
Implements GenerallyPositive.IAllowExcelOperations.
Definition at line 458 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.WithRowNumber | ( | int | rowNumber | ) |
Reads every value in the given row from the currently selected sheet of the first loaded workbook that has the selected sheet, storing the result (a single row of values) for use by subsequent operations. If a previous read/write operation has already been performed on this instance, prior range data and comparison differences are cleared first.
| rowNumber | The 1-based row number to read. |
Implements GenerallyPositive.IAllowExcelOperations.
Definition at line 716 of file GPALExcel.cs.
| IAllowExcelSearchAndReplaceSettings GenerallyPositive.GPALExcel.WithSearchValue | ( | string | value | ) |
Sets the value to search for in subsequent ReplaceWith(string) operations.
| value | The value to search for. |
Implements GenerallyPositive.IAllowExcelOperations.
Definition at line 628 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.WithSheet | ( | int | sheetIndex | ) |
Selects the sheet at the given 1-based index for subsequent Excel operations on every loaded workbook.
| sheetIndex | The 1-based index of the sheet to select. |
Implements GenerallyPositive.IAllowExcelSheetSelection.
Definition at line 371 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.WithSheet | ( | string | sheetName | ) |
Selects the sheet with the given name (case-insensitive) for subsequent Excel operations on every loaded workbook.
| sheetName | The name of the sheet to select. |
Implements GenerallyPositive.IAllowExcelSheetSelection.
Definition at line 413 of file GPALExcel.cs.
| IAllowExcelOperations GenerallyPositive.GPALExcel.WriteRange | ( | string | range | ) |
Writes the range data previously read via WithRange(string) (or another data-producing operation) to range on the currently selected sheet of every loaded workbook.
| range | The destination cell range to write to (e.g. "A1:B10"). |
Implements GenerallyPositive.IAllowExcelOperations.
Definition at line 518 of file GPALExcel.cs.
1.17.0