Tutorials

Data and Files

Data and Files

Working with Spreadsheets: GPAL.Excel End to End

GPAL.Excel: Spreadsheet Operations Units of Work GPALFile: Files as Objects GPALGrid: Working with Tabular Data Why ToGPALObject()

Complete Program

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

using GenerallyPositive;


GPALFile file = "C:\data\sales.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(ref 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:\data\sales_new.xlsx"))

.GetCompareResults(out differencesGrid)

.SaveTo(GPAL.FileFor("C:\data\sales_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:\data\sales.xlsx";

IGPALExcel excel = GPAL.Excel.ToGPALObject();


excel

.WithFile(file)

.WithSheet(1)

.WithRange("A1:B10")

.CalculateSum(out string sum)

.CalculateCount(out string count);

NOTE

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(ref 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:\data\sales_new.xlsx"))

.GetCompareResults(out differencesGrid)

.SaveTo(GPAL.FileFor("C:\data\sales_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.

Building a Workbook From Nothing

Everything above starts from a workbook that already exists. When the workbook is the output rather than the input, WithNewFile starts one, WithNewSheet adds each tab, and InputFrom writes your rows into the sheet you are on. Nothing is written until SaveTo or Close(true).

// a workbook of two sheets, built from grids and saved

GPALFile book = @"C: eportsmonth.xlsx";


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


sales.AddRow(new List<string> { "Name", "Tally" });

sales.AddRow(new List<string> { "row one", "10" });


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


places.AddRow(new List<string> { "Code", "City" });

places.AddRow(new List<string> { "TX", "Austin" });


GPAL.Excel.ToGPALObject()

.WithNewFile(book)

.WithSheet(1)

.InputFrom(sales)

.WithNewSheet("Places")

.InputFrom(places)

.SaveTo(book)

.Close(true);

NOTE

GPAL.Converter writes a single sheet, since the tab layout it uses comes from a workbook it read. A workbook of several tabs is authored with WithNewSheet.

On this page

Data and Files
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
  • Building a Workbook From Nothing
    • 💬 Ask GPAL