Tutorials

Tutorials

Working with Spreadsheets: GPAL.Excel End to End

GPAL.Excel turns Excel ranges, cells, rows, and columns into a fluent unit of work: read data out, run sums and counts, edit cell values, compare ranges against each other or against another file, and save results to a grid, a sheet, or a new file.

Complete Program

Here is the whole workflow, start to finish. Each piece is broken down and explained below.

using GenerallyPositive;

GPALFile file = @"C:datasales.xlsx";

IGPALGrid<string> comparisonGrid = GPAL.GridForType<string>();

IGPALGrid<string> differencesGrid = GPAL.GridForType<string>();

IGPALExcel excel = GPAL.Excel.ToGPALObject();

excel

.WithFile(file)

.WithSheet(1)

.WithRange("A1:B10")

.CalculateSum(out string sum)

.CalculateCount(out string count);

excel

.WithCell("A1")

.SetValue("Updated Header")

.WithCell("A2")

.AppendValue("_Appended")

.WithCell("A3")

.PrependValue("Prepended_")

.WithCell("A4")

.WithInsertPosition(0)

.WithInsertSeparator("_");

excel

.InsertValue("Inserted")

.WithColumn("B")

.SaveTo("ColumnData")

.WithRowNumber(2)

.SaveTo(comparisonGrid);

excel

.WithRange("A1:A10")

.WithSearchValue("124")

.ReplaceWith("new")

.WithRange("A1:A5")

.WithRange("B1:B5")

.CompareToGrid(comparisonGrid)

.GetCompareResults(out differencesGrid);

excel

.WithRange("A1:B10")

.CompareToFile(GPAL.FileFor(@"C:datasales_new.xlsx"))

.GetCompareResults(out differencesGrid)

.SaveTo(GPAL.FileFor(@"C:datasales_output.xlsx"))

.Close(true);

Open a Workbook and Read a Range

WithFile loads the workbook, WithSheet selects a sheet by index, and WithRange defines the active range. CalculateSum and CalculateCount run against that range and hand results back through out parameters - the chain keeps going so you can immediately work with the same range or define a new one.

GPALFile file = @"C:datasales.xlsx";

IGPALExcel excel = GPAL.Excel.ToGPALObject();

excel

.WithFile(file)

.WithSheet(1)

.WithRange("A1:B10")

.CalculateSum(out string sum)

.CalculateCount(out string count);

TIP

GPAL.Excel follows the unit-of-work pattern: .WithRange.CompareToRange.WithRange defines a new range and drops the old one. Any operation that acts on a range - SaveTo, Compare, Calculate - resets the range on the next WithRange call, so chain related reads and writes together.

Edit Individual Cells

WithCell selects a single cell. SetValue overwrites it outright; AppendValue and PrependValue add text to the end or start of the existing value. WithInsertPosition and WithInsertSeparator configure where InsertValue will splice in new text and what separator to use.

excel

.WithCell("A1")

.SetValue("Updated Header")

.WithCell("A2")

.AppendValue("_Appended")

.WithCell("A3")

.PrependValue("Prepended_")

.WithCell("A4")

.WithInsertPosition(0)

.WithInsertSeparator("_");

excel.InsertValue("Inserted");

Save Columns and Rows

WithColumn and WithRowNumber select an entire column or row. SaveTo accepts either a sheet name (creating or overwriting that sheet in the workbook) or an IGPALGrid, depending on whether you want the data to stay in the workbook or move into memory for further processing in C#.

excel

.WithColumn("B")

.SaveTo("ColumnData")

.WithRowNumber(2)

.SaveTo(comparisonGrid);

Search, Replace, and Compare

WithSearchValue and ReplaceWith perform a find-and-replace across the active range. CompareToGrid and CompareToFile compare the active range against an in-memory grid or another workbook entirely, and GetCompareResults pulls the differences into a grid you can inspect or save.

excel

.WithRange("A1:A10")

.WithSearchValue("124")

.ReplaceWith("new")

.WithRange("A1:A5")

.WithRange("B1:B5")

.CompareToGrid(comparisonGrid)

.GetCompareResults(out differencesGrid);

excel

.WithRange("A1:B10")

.CompareToFile(GPAL.FileFor(@"C:datasales_new.xlsx"))

.GetCompareResults(out differencesGrid)

.SaveTo(GPAL.FileFor(@"C:datasales_output.xlsx"))

.Close(true);

WARNING

Passing true to Close saves any pending changes back to the file and closes the workbook. Pass false if you want to discard changes, or if another operation still needs the workbook open.

On this page

Tutorials
Working with Spreadsheets: GPAL.Excel End to End
  • Complete Program
  • Open a Workbook and Read a Range
  • Edit Individual Cells
  • Save Columns and Rows
  • Search, Replace, and Compare