sgcSpreadsheet: een spreadsheet in Excel-stijl voor Delphi en .NET

Vroeg of laat heeft een bedrijfsapplicatie een spreadsheet nodig. Gebruikers plannen een budget, berekenen een offerte, rekenen kosten door of controleren de cijfers die een klant in een Excel-bestand heeft gestuurd, en ze verwachten daarbij formules, meerdere werkbladen en grafieken. Een gewoon raster is daarvoor niet genoeg.

sgcSpreadsheet is een spreadsheet-besturingselement in Excel-stijl, gebouwd op sgcGrid en op een formule-engine die in sgcUICore wordt gedeeld. Het bewaart een echte werkmap met werkbladen, gedefinieerde namen en dynamische arrays, berekent alleen de cellen opnieuw die door een wijziging worden geraakt, en laadt en bewaart XLSX. Dezelfde engine stuurt de VCL- en FireMonkey-besturingselementen voor Delphi en C++ Builder aan, en de WinForms-, WPF- en Avalonia-besturingselementen voor .NET.

sgcSpreadsheet in actie

Formules, arrays die overlopen, meerdere werkbladen, live grafieken en XLSX-bestanden op elk platform. Ook op YouTube.

Formules die je gebruikers al kennen

Wie met cijfers werkt, denkt in Excel-formules. In sgcSpreadsheet is een tekst die met = begint een formule, en de engine berekent 211 werkbladfuncties: wiskunde, statistiek, tekst, datum en tijd, opzoeken, logica, informatie en functies voor dynamische arrays. De engine zit in sgcUICore, dus formules in sgcGrid geven dezelfde resultaten.

Werkbladen, namen en arrays die overlopen

Een spreadsheet is een werkmap met een of meer werkbladen. De tabbladen onder het raster wisselen van werkblad en het +-tabblad voegt er een toe, en een hernoemd werkblad wordt in elke formule die ernaar verwijst ook hernoemd. Vanuit code beheren AddSheet, ActiveSheet en SheetCount de werkbladen, en Grid.Sheets.Names bewaart de gedefinieerde namen van de werkmap.

De onderstaande methode vult een prijslijst, geeft de bereiken een naam en bouwt een overzichtswerkblad dat de lijst leest. SORT geeft de hele lijst producten terug, die in de cellen eronder overloopt, en A1# verwijst naar dat overgelopen bereik, hoe lang de lijst ook wordt. XLOOKUP zoekt een prijs op en de naam VAT voegt de btw toe.

uses
  sgcSpreadsheet_VCL;

procedure TForm1.BuildPriceList;
var
  vSummary: Integer;
begin
  // Sheet1: a price list, row and column are 0-based, (0, 0) is A1
  sgcVCLSpreadsheet1.SetCellInput(0, 0, 'Product');
  sgcVCLSpreadsheet1.SetCellInput(0, 1, 'Price');
  sgcVCLSpreadsheet1.SetCellInput(1, 0, 'Monitor');
  sgcVCLSpreadsheet1.SetCellInput(1, 1, '219');
  sgcVCLSpreadsheet1.SetCellInput(2, 0, 'Keyboard');
  sgcVCLSpreadsheet1.SetCellInput(2, 1, '49');
  sgcVCLSpreadsheet1.SetCellInput(3, 0, 'Mouse');
  sgcVCLSpreadsheet1.SetCellInput(3, 1, '25');
  // defined names: formulas use them instead of cell addresses
  sgcVCLSpreadsheet1.Grid.Sheets.Names.Add('Products', '=Sheet1!$A$2:$A$4');
  sgcVCLSpreadsheet1.Grid.Sheets.Names.Add('Prices', '=Sheet1!$B$2:$B$4');
  sgcVCLSpreadsheet1.Grid.Sheets.Names.Add('VAT', '0.21');
  // a second sheet that reads the first one
  vSummary := sgcVCLSpreadsheet1.AddSheet('Summary');
  sgcVCLSpreadsheet1.ActiveSheet := vSummary;
  // a dynamic array: the sorted names spill into A1:A3
  sgcVCLSpreadsheet1.SetCellInput(0, 0, '=SORT(Products)');
  // A1# is the whole spilled range, however long the list grows
  sgcVCLSpreadsheet1.SetCellInput(0, 2, 'Items');
  sgcVCLSpreadsheet1.SetCellInput(0, 3, '=ROWS(A1#)');
  // XLOOKUP finds the price of a product, VAT included
  sgcVCLSpreadsheet1.SetCellInput(1, 2, 'Monitor');
  sgcVCLSpreadsheet1.SetCellInput(1, 3, '=XLOOKUP(C2,Products,Prices)*(1+VAT)');
  // every sheet, formula and name goes to a file that Excel opens
  sgcVCLSpreadsheet1.SaveToXLSX('prices.xlsx');
end;

Het overzicht toont de drie producten op volgorde, 3 items en 264.99 voor de monitor. Wijzig een prijs op het eerste werkblad en alleen de cellen die ervan afhangen worden opnieuw berekend. FreezePanes(1, 0) houdt een koprij op zijn plaats terwijl de rest van het werkblad scrolt, en de formulebalken TsgcVCLSpreadsheetFormulaBar en TsgcFMXSpreadsheetFormulaBar geven je gebruikers een naamvak en de formule van de huidige cel.

Je eigen functies

Applicaties voor techniek, financiën en laboratoria hebben eigen berekeningen nodig. De formule-engine heeft een functieregister, en een functie die daar is geregistreerd werkt in elke formule van de spreadsheet, van sgcGrid en van een TsgcCalcEvaluator, precies zoals een ingebouwde functie. De handler is een gewone functie, geregistreerd vanuit de initialization-sectie van een unit.

unit MyFunctions;

interface

implementation

uses
  sgcUICore_Calc;

{ =MARKUP(price, [rate]): the price plus a margin, 20% by default }
function DoMarkup(aCall: TsgcCalcCall): Variant;
var
  vPrice, vRate: Double;
  vError: string;
begin
  vError := aCall.Number(0, vPrice);
  vRate := 0.2;
  if (vError = '') and (aCall.Count > 1) then
    vError := aCall.Number(1, vRate);
  if vError <> '' then
    Result := vError
  else
    Result := vPrice * (1 + vRate);
end;

initialization
  // MARKUP takes 1 or 2 arguments
  sgcCalcRegisterFunction('MARKUP', DoMarkup, 1, 2);

end.

Voeg de unit aan het project toe en je gebruikers kunnen =MARKUP(B2) typen. Met dynamische arrays roept de engine de functie aan voor elk element van een arrayargument, dus =MARKUP(B2:B4, 0.3) laat één resultaat per prijs overlopen en =SUM(MARKUP(B2:B4)) werkt zonder extra code. Een functie die de cellen van een bereik nodig heeft, wordt geregistreerd met de vlag cfRanges en loopt ze af met een iterator.

Grafieken uit celbereiken

Een grafiek toont de waarden van een celbereik en zweeft boven het werkblad, zoals een grafiek in Excel. Hij wordt getekend door sgcCharts, en als een cel van het bereik verandert, of een formule in het bereik een nieuwe waarde geeft, volgt de grafiek. Gebruikers verplaatsen grafieken en wijzigen hun formaat met de muis, en elk werkblad heeft zijn eigen grafieken.

• 7 grafiektypen: kolom, staaf, lijn, vlak, cirkel, donut en spreiding.
• AddChart met een bereik zoals 'A1:D10' of 'Sheet1!A1:D10', waarbij de reeksnamen en categorieën uit de koprij en de kopkolom worden gelezen.
• TsgcSpreadsheetCharts bevat de grafieken van elk werkblad, en SaveToXLSX schrijft ze als grafieken die Excel opent.

XLSX erin, XLSX eruit

Klanten sturen XLSX-bestanden en verwachten XLSX-bestanden terug. LoadFromXLSX leest elk werkblad van een werkmap en SaveToXLSX schrijft elk werkblad, vanaf een bestandsnaam of een stream, zodat een bestand heen en weer kan tussen je applicatie en Excel.

• Waarden, formules en celopmaak.
• Gedefinieerde namen en dynamische arrays.
• Vastgezette deelvensters en kolombreedtes.
• Grafieken, opgeslagen als grafieken die Excel opent.

VCL, FireMonkey en .NET

Delphi en C++ Builder krijgen TsgcVCLSpreadsheet voor VCL en TsgcFMXSpreadsheet voor FireMonkey, elk met een eigen formulebalk. Voor .NET zijn er TsgcWinFormsSpreadsheet, TsgcWpfSpreadsheet en TsgcAvaloniaSpreadsheet, ook met formulebalken, in de NuGet-pakketten esegece.sgcSpreadsheet, esegece.sgcSpreadsheet.WinForms, esegece.sgcSpreadsheet.Wpf en esegece.sgcSpreadsheet.Avalonia. De .NET-besturingselementen gebruiken dezelfde namen als die van Delphi, en een eigen functie kan een lambda zijn.

using esegece.sgcSpreadsheet;
using esegece.sgcUICore;

// once at startup, before the first formula is parsed
sgcUICore_Calc.sgcCalcRegisterFunction("MARKUP", aCall =>
{
    double vPrice;
    double vRate = 0.2;
    string vError = aCall.Number(0, out vPrice);
    if ((vError.Length == 0) && (aCall.Count > 1))
        vError = aCall.Number(1, out vRate);
    if (vError.Length > 0)
        return vError;
    return vPrice * (1 + vRate);
}, 1, 2);

// sheet is a TsgcWinFormsSpreadsheet: open a workbook made in Excel
sheet.LoadFromXLSX("prices.xlsx");
sheet.ActiveSheet = 0;
// the custom function spills over the whole price column
sheet.SetCellInput(0, 2, "=MARKUP(B2:B4, 0.3)");
// a column chart of the range, drawn by sgcCharts
TsgcSpreadsheetChart chart = sheet.AddChart(TsgcSpreadsheetChartType.sctColumn, "A1:B4");
chart.Title = "Prices";
sheet.SaveToXLSX("prices-markup.xlsx");

Probeer de demo's

De Delphi- en C++ Builder-demo's worden geïnstalleerd met de sgcUI-trial, en de .NET-demo's worden meegeleverd met de Registered .NET-download.

• Basic: een verkoopwerkmap die in code is opgebouwd met SUM, AVERAGE, IF en XLOOKUP, een overzichtswerkblad dat de werkmap leest, een vastgezette koprij, de formulebalk, en Openen en Opslaan als XLSX.
• Functions: wiskunde-, tekst-, datum- en tijd-, opzoek- en dynamische arrayfuncties, één werkblad per categorie, met elke formule naast het resultaat.
• Charts: kolom-, lijn- en cirkeldiagrammen uit celbereiken. Wijzig een omzet of een kost en de grafieken volgen.
• CustomFunction, BasicFMX en ChartsFMX: functies die door de applicatie worden geregistreerd, en de Basic- en Charts-demo's op FireMonkey. Basic en Charts worden ook als C++ Builder-projecten meegeleverd.
• .NET: SpreadsheetBasic voor WinForms, SpreadsheetBasicWpf, SpreadsheetBasicAvalonia, SpreadsheetFunctions, SpreadsheetCharts en SpreadsheetCustomFunction.

Waar sgcSpreadsheet in zit

sgcSpreadsheet is inbegrepen in sgcUI Enterprise en in de All-Access-bundel, en wordt los verkocht als enkel pakket voor Delphi en C++ Builder en voor .NET. Bij het installeren van sgcSpreadsheet worden ook sgcGrid en sgcCharts geïnstalleerd, zodat het raster en de grafieken altijd werken. Er is geen gratis Community-editie. Bekijk de prijspagina voor de details.

Vragen of feedback? Neem contact op, je krijgt antwoord van de mensen die de code hebben geschreven.